SQL

SELECT E1.EMPNO'员工编号',E1.ENAME'员工姓名',D.DNAME'部门姓名',D.LOC'部门位置',A.C'部门人数' FROM EMP01 E1,EMP01 E2,DEPT01 D,( SELECT DEPTNO , COUNT(*) C FROM EMP01 GROUP BY DEPTNO)A WHERE E1.DEPTNO = D.DEPTNO AND D.DEPTNO = A.DEPTNO AND E1.MGR = E2.EMPNO AND CONVERT(VARCHAR(15), E1.HIREDATE,111)<CONVERT(VARCHAR(15), E2.HIREDATE,111)

--5.列出部门名称和这些部门的员工的信息（数量、平均工资），同时列出那些没有员工的部门

SELECT D.DNAME'部门名称',A.C'部门人数',A.S'平均工资' FROM DEPT01 D LEFT JOIN ( SELECT DEPTNO , COUNT(*) C ,AVG(SAL)S FROM EMP01 GROUP BY DEPTNO)A ON A.DEPTNO = D.DEPTNO GROUP BY D.DNAME,A.C ,A.S

--6.列出所有"clerk"（办事员）的姓名及其部门的名称，部门的人数

SELECT E.ENAME'员工姓名',D.DNAME'所在部门',A.C'部门人数' FROM EMP01 E,DEPT01 D,( SELECT DEPTNO , COUNT(*) C FROM EMP01 GROUP BY DEPTNO)A WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=A.DEPTNO AND E.JOB='CLERK'

--7.列出最低薪金大于的各种工作及此从事工作的全部雇员人数及所在部门名称、位置、平均工资

SELECT J.JOB,D.DNAME,D.LOC,J.C,AVG(E.SAL) FROM EMP01 E,DEPT01 D,(SELECT DEPTNO,JOB,COUNT(*)C FROM EMP01 WHERE SAL>1500 GROUP BY DEPTNO ,JOB)J WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO = J.DEPTNO GROUP BY J.JOB,D.DNAME,D.LOC,J.C

--8.列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期，部门名称，假定不

SELECT E.ENAME'员工姓名',E.SAL'基本工资',E.HIREDATE'雇佣日期',D.DNAME'部门名称' FROM EMP01 E,DEPT01 D WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'SALES'

--9.列出薪金高于公司平均薪金的所有员工，所在的部门，上级领导

SELECT E1.EMPNO,E1.ENAME,D.DNAME,E2.ENAME FROM EMP01 E1,EMP01 E2 ,DEPT01 D WHERE E1.DEPTNO = D.DEPTNO AND E1.MGR = E2.EMPNO AND E1.SAL >(SELECT AVG(SAL) FROM EMP01 )

--10.列出与‘SCOTT’从事相同工作的所有员工及部门名称，部门人数

SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,E.DEPTNO,D.DNAME,D.LOC ,A.T '部门人数' FROM EMP01 E , DEPT01 D ,(SELECT DEPTNO , COUNT(*) T FROM EMP01 GROUP BY DEPTNO)A WHERE E.DEPTNO = D.DEPTNO AND A.DEPTNO = D.DEPTNO

AND E.JOB = (SELECT JOB FROM EMP01 WHERE ENAME = 'SCOTT')

---查部门人数

SELECT E.ENAME ,D.DEPTNO , A.T FROM EMP01 E,DEPT01 D ,(SELECT DEPTNO , COUNT(*) T FROM EMP01 GROUP BY DEPTNO)A WHERE E.DEPTNO = D.DEPTNO AND A.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO ,E.ENAME, A.T

--12.列出薪金高于部门工作的所有员工的薪金的员工姓名和薪金、部门名称

SELECT E.ENAME'员工姓名',E.SAL'员工薪金',D.DNAME'所在部门' FROM EMP01 E,DEPT01 D WHERE E.DEPTNO = D.DEPTNO AND E.SAL>ALL( SELECT SAL FROM EMP01 WHERE DEPTNO = 30 )

--13.列出每个部门工作的员工数量、平均工资和平均服务期限

--14.列出所有员工的姓名、部门名称和工资

SELECT E.ENAME'员工姓名',D.DNAME'部门名称',(E.SAL+ISNULL(E.COMM,0))'工资' FROM EMP01 E,DEPT01 D WHERE E.DEPTNO = D.DEPTNO

--15.列出所有部门的详细信息和部门人数

SELECT D.DEPTNO'部门编号',D.DNAME'部门名称',D.LOC'部门地址',A.C'部门人数' FROM DEPT01 D LEFT JOIN ( SELECT DEPTNO,COUNT(*)C FROM EMP01 GROUP BY DEPTNO )A ON A.DEPTNO = D.DEPTNO

--16.列出各种工作的最低工资及从事此工作的雇员姓名

SELECT J.JOB'工作',E.ENAME'姓名' ,J.M'工资' FROM EMP01 E,(SELECT JOB,MIN(SAL)M FROM EMP01 GROUP BY JOB)J WHERE E.JOB =J.JOB AND E.SAL = J.M

SELECT JOB,MIN(SAL)M FROM EMP01 GROUP BY JOB

--18.列出所有员工的年工资，所在部门名称，按年薪从低到高排列

SELECT E.DEPTNO'部门编号',D.DNAME'部门名称' ,E.ENAME'员工姓名',(E.SAL+ISNULL(E.COMM,0))*12'年薪' FROM EMP01 E,DEPT01 D WHERE E.DEPTNO = D.DEPTNO ORDER BY SAL asc

--19.查出某个员工的上级主管级所在部门名称，并要求出这些主管中的薪水超过

--20.求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数

SELECT D.DNAME,A.C,A.S FROM DEPT01 D LEFT JOIN (SELECT DEPTNO, COUNT(*)C ,SUM(SAL)S FROM EMP01 GROUP BY DEPTNO )A ON D.DNAME LIKE'%S%' AND D.DEPTNO = A.DEPTNO

2014年11月03日

第一,声明变量

方法:声明变量用declare @变量名类型,@变量名类型.....

--变量赋值用set(或者select) @变量名= 值;

--查询变量:select @变量名;

--每条语句用';'结尾

1,定义变量

DECLARE

@NAME VARCHAR(15),

@AGE INT

2,变量赋值

SET @NAME = '张三';

SET @AGE = 18;

select @AGE=20;

3,查询

select @NAME;

select @AGE;

4,把某一个变量的值赋给别人

declare

@eid int

set @eid = 1001;

select * from emp where Eid = @eid;

5,把查询的的值赋给变量

declare

@avg decimal(8,2)

select @avg = AVG(esal) from emp;

select @avg'平均工资';

第二循环语句

1,数据库中的代码块用BEGIN ... END 包含起来(类似与java中{}的意思)执行时是一个整体

BEGIN

SELECT * FROM EMP

END

2,数据库中的if语句

declare

@eid int

select @eid = 1111;

if exists(select * from emp where Eid = @eid)

begin

select *from emp where Eid=@eid;

end

else

select '当前编号对应的用户不存在'

3,消除重复

---distinct 去除重复的记录显示,根据指定的字段

--相同的标准:distinct后面的查询字段都相同时才算重复

select distinct ename from emp

--查询总共记录(消除重复的记录)

select COUNT(distinct Ename)from emp

select *from emp

4,练习if语句

--题目:输入用户名,密码,判断当前的用户名是否存在

---如果用户名错误就提示用户名错误

---如果密码错误就提示密码错误

---否则就提示登陆成功

declare

@ename varchar(20),

@eid int

set @ename = '张三大';

set @eid = 1001;

if exists(select * from emp where Ename = @ename)

begin

if exists(select * from emp where Ename = @ename and Eid = @eid)

select '登陆成功'

else

select '密码错误'

end

else

select '用户名错误'

-----------老师答案---

--创建用户表

create table users(

username varchar(20),

userpass varchar(20)

)

insert into users values ('呵呵','123456')

declare

@name1 varchar(20),

@pass varchar(20),

@msg varchar(20)

set @name1 ='呵呵';

set @pass = '123456';

if exists(select * from users where username = @name1)

begin

if exists(select * from users where username = @name1 and userpass = @pass)

begin

set @msg = '登陆成功'

select @msg;

end

else begin

set @msg = '输入的用户密码错误'

select @msg;

end

else begin

set @msg = '输入的用户名不存在'

select @msg;

end

5,goto 是指定程序到某一个标记处执行

--定义标记:标记名+':'

练习:

(1)用if打印出-10的和

declare

@i int = 1,

@sum int =0

bc:--定义一个标签

if @i<=10

begin

set @sum+=@i;

set @i= @i+1;

goto bc;

end

select @sum;

(2)求-100的偶数和

declare

@i int = 1,

@sum int = 0;

--用if

bc:

if @i<=100

begin if @i%2 =0

begin

set @sum +=@i;

set @i +=2;

goto bc;

end else

begin

set @i +=1;

goto bc;

end

select @sum

(3)用while求-100偶数和

declare

@i int = 1,

@sum int = 0

while @i<=100

begin if @i%2 =0

begin

set @sum +=@i;

set @i +=2;

end else

set @i +=1;

end

select @sum

第三,设置等级以及在指定的时间去执行

1,判断设置等级

--语法: case when 条件then ‘等级名称’ when 条件then .....end

select * from EMP01

select ename,JOB,DEPTNO,'等级'=case

when SAL>=4000 then '白领'

when SAL>=3000 and sal <4000 then '蓝领'

when SAL<3000 then '黑领' end from EMP01

2,waitfor delay 让sql在指定时间后再去执行

--语法: waitfor delay :设置sql语句等待指定的时间后执行

--时间格式:’00:00:00’

declare

@deptno int =20

waitfor delay '00:00:05';

select * from EMP01 where DEPTNO = @deptno

3,waitfor time 让sql在指定时间(0-24小时)点再去执行

--语法: waitfor time :设置sql语句等待指定的时间点执行

declare

@deptno int =20

waitfor time '10:05:00';

select * from EMP01 where DEPTNO = @deptno

第四,事物与异常

1,方法:

--开启事物:begin transaction

--开启异常处理机制:begin try

--需要处理的内容代码

--提交事物:commit transaction

--end try

--处理异常:begin catch

--出现异常回滚数据:rollback transaction

--end catch

2,例题:

declare

@name2 varchar(20)='张三',

@pass2 varchar(20)='1234',

@num int

--开启事物

begin transaction

--开启异常处理机制

begin try

--制造一个会出现异常的代码

set @num = 10/0;

--添加数据

insert into users values(@name2,@pass2);

--提交事物

commit transaction

end try

--处理异常

begin catch

--回滚数据

rollback transaction

end catch

3,练习--往银行里进行转账

2-1,自己所写

create table yinhang(

name varchar(15),

pass varchar(15),

money decimal(10,2)

)

insert into yinhang values('李氏','123456',50000);

insert into yinhang values('张三','12345',4000);

declare

@name3 varchar(15),

@pass3 varchar(15),

@tonum varchar(20),

@len int,

@cm int

set @name3 = '李氏';

set @pass3 = '123456';

set @tonum ='张三'

set @len = 1000;

if exists(select * from yinhang where name = @name3)

begin if exists(select * from yinhang where name = @name3 and pass = @pass3 )

begin

begin try

begin transaction

if exists(select money from yinhang where name = @name3 and pass = @pass3 and money >@len)

begin

update yinhang set money=(select money from yinhang where name = @name3 and pass = @pass3)-@len where name = @name3 and pass = @pass3;

update yinhang set money=(select money from yinhang where name = @tonum )+@len where name =@tonum ;

--自己制造一个异常

--余额不会改变

set @cm = 10/0;

end

else

begin

select '余额不足'

--提交事物

end commit transaction

end try

begin catch

select '出现异常'

--回滚数据

rollback transaction

end catch

end else

select '密码错误'

end else

select '账户不存在'

select * from yinhang

2-2老师答案

create table zhanghu(

num varchar(20)not null unique,

yue decimal(9,2)

)

insert into zhanghu values ('100001',5000);

insert into zhanghu values ('100002',5000);

declare

@fromnum varchar(20),

@tonum varchar(20),

@m decimal(9,2),

@c int

set @fromnum = '100001';

set @tonum = '100002';

set @m = 1000;

begin try

begin transaction

update zhanghu set yue = yue-@m where num = @fromnum;

set @c = 10/0;

update zhanghu set yue = yue+@m where num = @tonum;

commit transaction

end try

begin catch

rollback transaction

end catch

select * from zhanghu

第五,存储过程

1,创建一个没有参数的存储过程

--语法:create procedure 名字as 实现的代码

CREATE PROCEDURE STU_EMP

SELECT * FROM EMP;

2,查看用户定义的存储过程

select*from SYSOBJECTS WHERE TYPE= 'P'

3,删除存储过程

drop proc stu_emp

4,调用存储过程

exec STU_EMP

5,查看定义的存储过程实现的代码

exec sp_helptext 'stu_emp'

6,创建一个加密的存储过程用:with encryption

create procedure stu_test

with encryption

select * from emp;

--查看定义的存储过程实现的代码

exec sp_helptext 'stu_test' --对象'stu_test' 的文本已加密。

7,创建有参数的过程

--创建方法:create procedure 名字(@变量类型) as 实现方法

--调用方法:exec 名字变量

create procedure stu_input(

@eid int

)

select * from emp where Eid = @eid;

--调用有参数的存储过程

declare

@eid int

set @eid = 1001;

exec stu_input @eid

8,创建一个有输入和输出的过程

--输入后加:in(也可以不加)

--输出后加:out

--既可以输入有可以输出用:output

create procedure stu_output(

@eid int,

@esal decimal(7,2) out

)as

select @esal = esal from emp where Eid = @eid;

--调用stu_output存储过程函数

declare

@re decimal(7,2)

exec stu_output '1001',@re out;

select @re;

第六,用存储过程实现分页

1,常见的分页方法

1-1分页语句一:

--当前页数(page),每页显示的记录个数(size)

--语法:select top size * from emp where Eid not in (

--select top((page-1)*size) Eid from emp )

select top 2 * from emp where Eid not in(select top((2-1)*2)Eid from emp)

1-2分页语句二:

--当前页数(page),每页显示的记录个数(size)

--语法:select * from (select ROW_NUMBER()over(order by 主键或者要分页的键值) rowid,* from 表名)temp

--where rowid between (( page-1 )*size + 1) and page*size

--between ...and..之间是显示的从哪条记录到哪条记录,所以分页时要计算出当前页的值

select * from (select ROW_NUMBER()over(order by eid) rowid,* from emp)temp where rowid between 1 and 5

2,创建一个分页存储过程

--分析:因为要传送表名,但是只能定义成字符行的,所以不能让它直接执行,要先转换成字符串然后调用时进行执行

--创建分页存储

--create procedure fenye(

--@tablename varchar(20),--表名

--@currpage int,--页数

--@pagesize int--每页显示的个数

--)as

--创建接受方法语句的变量

--declare

--@sql varchar(200)--方法变量字符串

--set @sql='方法实现的字符串'

--exec(@sql);--运行该字符串语句

--检查指定的存储过程是否存在,存在删除

if exists(select * from sysobjects where type = 'fenye')

drop proc fenye;

3,第一种分页

create procedure fenye(

@tablename varchar(20),

@currpage int,

@pagesize int

)as

declare

@sql varchar(200)

set @sql='select top '+cast(@pagesize as varchar(15))+' * from '+@tablename+' where Eid not in (

select top (('+cast(@pagesize as varchar(15))+'-1)*'+cast(@pagesize as varchar(15))+') Eid from '+ @tablename+');'

exec(@sql);

--删除fenye

drop proc fenye;

--查询

exec fenye 'emp',2 ,3;

4,第二种分页

create procedure fenye1(

@tablename varchar(20),

@begin int,

@end int

)as

declare

@sql varchar(200)

set @sql=

'select * from ( select ROW_NUMBER()over(order by eid)rowid,* from '+@tablename+')temp where rowid between '+cast( (((@begin-1)*@end)+1) as varchar(15))+' and '+convert(varchar(15),(@begin*@end))+' ; '

exec(@sql);

--删除分页存储函数fenye1

drop proc fenye1;

--使用fenye1进行分页

exec fenye1 'emp',2 ,4;

select * from emp

5,分页语句三::通用的

select * from (select ROW_NUMBER()over (order by eid)rowid ,* from emp )temp where rowid between 1 and 5

create proc sp_paging(

@tbname varchar(20),--表名

@sortcolumn varchar(10),--排序列段

@column varchar(200) ,--列表

@selection varchar(200),--查询条件

@currentpage int,--当前列

@pagesize int --每页的记录数

)

declare

@sql varchar(1000)

if @column=''or @column is null

begin

set @column = '*';

end

if @selection != '' and @selection is not null

begin

set @selection = ' where '+@selection;

end

--select * from (select ROW_NUMBER()over (order by eid)rowid ,* from emp )temp where rowid between 1 and 5

set @sql ='select '+@column+' from (select ROW_NUMBER()over (order by '+@sortcolumn+' )rowid ,* from '+@tbname+' '+

@selection+' )temp where rowid between '+convert(varchar(15),((@currentpage-1)*@pagesize+1))+' and '+convert(varchar(15),(@currentpage*@pagesize+1))

exec (@sql);

drop proc sp_paging;

exec sp_paging 'emp','eid','*','',1,3;

6,转换函数:cast( 名as 类型),convert(类型,名)

Case( 12 , varchar(15) )

Convert( varchar(15) , 12)

7,有默认值的过程

create proc getname(

@name varchar(10) = '%张%'

)as

select * from emp where Ename like @name;

exec getname;

exec getname '%李%';

2014年11月04日

第一,游标

1,游标:对表进行遍历

--定义,开启,关闭,释放

步骤:(1)创建游标

create procedure 游标名 as

declare--定义一个游标

游标名 cursor for select 列名 from 表名

declare --创建变量接收列名值

@变量名

(2)开启游标

open 游标名 ;

(3)获取游标指定的值

fetch next from 游标名 into @变量名;

(4)必要的语句

(5)关闭游标

close stu_curso

(6)释放游标

deallocate stu_cursor

2,删除游标

drop proc stu_test01;

第二,用游标创建等级

1,创建学生表和分数等级表并添加数据

create table student01(

stu_id int primary key,

stu_name varchar(15),

stu_score decimal(3,1)

)

create table stu_grade(

stu_id int not null,

stu_name varchar(15),

grade varchar(10)

)

insert into student01 values(10001,'小明',65);

insert into student01 values(10002,'小红',70);

insert into student01 values(10003,'小张',80);

insert into student01 values(10004,'小李',90);

insert into student01 values(10005,'小笨',55);

2,创建游标stu_test01实现功能

create procedure stu_test01

declare

--定义一个游标

stu_cursor cursor for select stu_id,stu_name,stu_score from student01

declare

@stu_id int ,@stu_name varchar(15),@stu_score decimal(3,1)

--开启游标

open stu_cursor;

--获取游标指定的值

fetch next from stu_cursor into @stu_id,@stu_name,@stu_score;

while @@FETCH_STATUS = 0

begin

if @stu_score>=60 and @stu_score<70

begin

insert into stu_grade values(@stu_id,@stu_name,'D');

end else if @stu_score>=70 and @stu_score<80

begin

insert into stu_grade values(@stu_id,@stu_name,'C');

end else if @stu_score>=80 and @stu_score<90

begin

insert into stu_grade values(@stu_id,@stu_name,'B');

end else if @stu_score>=90 and @stu_score<100

begin

insert into stu_grade values(@stu_id,@stu_name,'A');

end

else

insert into stu_grade values(@stu_id,@stu_name,'E');

fetch next from stu_cursor into @stu_id,@stu_name,@stu_score

end

--关闭游标

close stu_cursor

--释放游标

deallocate stu_cursor

第三,创建实现查找学生分数等级的游标

create proc stu_test02

declare

stucurcor cursor for select stu_id,stu_name from student01

declare

@stuid int ,@stuname varchar(15)

open stucurcor;

fetch next from stucurcor into @stuid,@stuname;

while @@FETCH_STATUS = 0

begin

select * from stu_grade where stu_id = @stuid and stu_name = @stuname;

fetch next from stucurcor into @stuid,@stuname;

end

close stucurcor;

deallocate stucurcor;

exec stu_test02;

第四,创建一个可以自由移动的游标

create proc stu_test03

declare cursor03 cursor scroll for select stu_id ,stu_name from student01

declare

@stu_id int ,@stu_name varchar(15)

--开启游标

open cursor03;

--定位最后一行

fetch last from cursor03 into @stu_id ,@stu_name;

select @stu_id;

select @stu_name;

--关闭游标

close cursor03;

--释放游标

deallocate cursor03;

--自由定位

--fetch last[first, next,last,absolute,relative] from....

--定位到第行

--fetch absolute 3 from cursor03 into @stu_id ,@stu_name;

drop proc stu_test03;

exec stu_test03;

第五,触发器

1,触发器介绍以及分类

--为某个表的insert或update或delete语句绑定一个事件,当我们操作该表时候,就会触发该事件

--分类:alter:后触发器.在指定的sql语句执行完毕后才触发

--instead of:替代触发器.替代指定的sql语句,这时不会执行替代的sql语句只执行该触发器

--每个触发器都有系统提供的两张临时表inserted,deleted,触发器结束后也消失

--insert触发器:当前表如果执行insert语句的时候会在当前表中添加一条记录,同时也会把

--该记录添加到inserted表中.

--update触发器:当前表执行update语句的时候,会把旧记录存入deleted表中,然后把新纪录

--添加到当前表中,最后会把新纪录也添加到inserted表中

--delete触发器:当前表执行delete语句时,会把记录存入deleted表中.

2,创建实现触发器的学生表和借书表

2-1创建学生表

create table stu(

stu_id int primary key ,

stu_name varchar(15)

)

drop table stu

2-2借书表

create table borrowrecord(

stu_id int primary key ,

borrownum int,

rebook int

)

insert into stu values(10001,'老王')

insert into stu values(10002,'老李')

insert into borrowrecord values(10001,2,1)

insert into borrowrecord values(10002,3,2)

3,后触发器alter

--需求:在修改学生表中的id时也修改借书表中对应学生的id

create trigger updatestuid

on stu after update

declare

@oldid int,

@newid int

--找到旧记录中的stu_id

select @oldid=stu_id from deleted;

--找到新记录中的stu_id

select @newid=stu_id from inserted;

--修改借书表中对应的学生的stu_id

update borrowrecord set stu_id = @newid where stu_id = @oldid;

--修改stu_id触发update触发器

update stu set stu_id = 10003 where stu_id = 10001;

--查询是否触发

select * from stu

select * from borrowrecord

4,替代触发器instead of

--需求:往stu表中添加数据,但是不会添加进去,添加进去指定的数据

create trigger insterstu

on stu instead of insert

insert into stu values('10001','隔壁韩大爷')

--添加数据出发替代触发器instead of

insert into stu values('10005','隔壁老王')

select * from stu

5,删除触发器

drop trigger insterstu;

6,查看当前用户定义的触发器

--type = 'tr(触发器)'[p(存储过程),u(当前用户的表)]

select * from sysobjects where type = 'tr';

荏苒夕阳

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SQL

SQL基础 42014年10月30日 4第一,创建一个数据库文件 41,创建数据库 42,创建主文件 43,创建日志文件 5第二,备份.还原,删除数据库 51,数据库的备份 52,还原数据库 53,删除数据库--删除原有的结构 5-第三,查询数据库 51,查询所有的数据库 52,查询所有的表 63,-查询当前用户的表 64,查询表结构
复制链接

扫一扫

专栏目录