SQL server常用SQL语句总结
数据库实验报告（SQL server）
一、数据库操作
1、数据库建立和删除：学生-课程数据库
-- 建立数据库
create database S_T;
-- 删除数据库
drop database S_T;
2、表格建立、删除、修改
创建表格：
1 create table Student(
2 Sno varchar(9) primary key not null,
3 Sname varchar(10) not null,
4 Ssex varchar(4),
5 Sage int not null,
6 Sdept varchar(10));
7 create table Course
8 (Cno varchar(4) primary key not null,
9 Cname varchar(40),
10 Cpno varchar(4),
11 Ccredit int,
12 -- cpno是外码，参照Course表格的Cno（可以参照自身！）
13 foreign key(Cpno) references Course(Cno)
14 );
15 create table SC
16 (Sno varchar(9),
17 Cno varchar(4),
18 Grade int,
19 primary key(Sno,Cno),
20 foreign key(Sno) references Student(Sno),
21 foreign key(Cno) references Course(Cno)
22 );
删除表：
drop table student;
更改表：
alter table userinfo/*修改表中的字段*/
alter column name varchar(100);
alter table userinfo/*增加主键*/
add constraint KID primary key (ID);
alter table userinfo/*添加表中的字段*/
add grade2 varchar(10) not null;
alter table userinfo/*删除表中字段*/
drop column age;
exec sp_rename 'user','userinfo';/*更改表名*/
exec sp_rename 'userinfo.username', 'age', 'column';/*更改表中的字段名*/
3、索引的建立和删除
-- 创建索引
create unique index stusno on Student(sno);
CREATE unique index SCno ON SC(Sno ASC,Cno DESC);/*asc表示升序desc表示降序*/
--禁用名为 stusno 的索引
alter index stusno on Student disable
--查看指定表 Student 中的索引
exec sp_helpindex Student
--删除指定表 Student 中名为 stusno 的索引
drop index Student.stusno
二、数据查询
1、增加、更新、删除
插入数据：
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS'),
('201215122','刘晨','女',19,'CS'),
('201215123','王敏','女',18,'MA'),
('201215125','张立','男',19,'IS');
更新数据：
update Student
set Sage=22
where Sno='201215121';
删除数据：
delete from Student
where sno='201215128';
2、连接（自然、自身、外、嵌套、相关、exists）
1 select student.*,sc.*/*连接查询*/
2 from student,sc
3 where student.Sno=sc.Sno;
4
5 select Student.Sno,sname,ssex,sage,sdept,cno,Grade/*自然连接*/
6 from student,SC
7 where Student.Sno=SC.Sno;
8 /*选修了2号课程且成绩在80分以上的学生姓名和学号*/
9 select Student.Sno,sname
10 from Student,SC
11 where Student.Sno=sc.Sno and sc.Cno='2'and sc.Grade>80;
12 /*自身连接，使用别名前缀（空格加别名）first,second或者自己取名*/
13 select* from Course
14 select 课程.cname,先修课.cname
15 from Course 课程,Course 先修课
16 where 课程.Cpno=先修课.cno;
17 /*外连接 from...on;left(right) join*/
18 select Student.Sno,sname,ssex,sage,cno,grade
19 /*from Student,SC*/
20 /*where Student.Sno (+)= SC.Sno;*/
21 from Student left join SC
22 on( Student.sno=sc.Sno);
23 /*多表连接*/
24 select Student.Sno,sname,cname,grade
25 from Student,SC,Course
26 where Student.sno=sc.Sno and SC.Cno=course.cno;
27 /*嵌套查询*/
28 select*from student;
29 select sno,sname,sdept/*不相关子查询*/
30 from Student
31 where Sdept in(
32 select sdept
33 from Student
34 where Sname='刘晨');
35 /*用自身连接转换上述查询*/
36 select s1.sno,s1.sname,s1.sdept/*子连接都要说明属性所在的表*/
37 from Student s1,Student s2
38 where s1.Sdept=s2.Sdept and s1.Sname='刘晨';
39 /*两重嵌套*/
40 select sno,sname
41 from Student
42 where sno in (
43 select Sno
44 from SC
45 where Cno in (
46 select Cno
47 from Course
48 where Cname='信息系统'));
49 /*用连接修改上述嵌套*/
50 select Student.sno,sname
51 from student,SC,Course
52 where student.sno=SC.sno
53 and sc.cno=Course.Cno
54 and course.Cname='信息系统';
55 /*相关子查询 子查询要用到父查询的条件*/
56 select*from SC;
57 select sno,cno
58 from sc x
59 where grade>=(
60 select avg(grade)
61 from sc y
62 where y.sno=x.Sno);
63 /*where y.cno=x.cno);*/
64 /*groupby的属性，最后要输出出来，比如groupby Cno,要select Cno*/
65 select sname,cno,Student.sno
66 from Student,sc
67 where student.Sno=sc.Sno and SC.cno in(
68 select cno
69 from SC
70 group by Cno
71 having avg(Grade)<90
72 );
73 /*带有谓词的子查询 <>和any some 常成对出现*/
74 select sname,sage
75 from Student x
76 where sage<any(
77 select sage
78 from Student y
79 where y.Sdept='CS'and x.Sdept<>'CS');
80
81 /*带有exists谓词的子查询*/
82 select sname
83 from Student
84 where exists(
85 select *
86 from SC
87 where sno=student.sno and cno='1');
88 /*并操作--union 系统自动去重；union all 系统不会去重
89 交操作--intersect
90 差操作--except*/
91 select*from Student
92 where Sdept='CS'
93 union
94 select*from Student
95 where Sage<=19;
96
97 select *from Student
98 where Sdept='CS'
99 intersect
100 select*from Student
101 where Sage<=19;
102
103 select *from Student
104 where Sdept='CS'
105 except
106 select *from Student
107 where Sage<=19;
108 /*插入子查询结果*/
109 create table dept_age
110 (sdept varchar(15),
111 avg_age smallint);
112 insert into dept_age(sdept,avg_age)
113 select sdept,avg(sage)
114 from Student
115 group by Sdept;
3、单表（聚集函数、匹配、排列、分组、聚集函数定义、查询）
/*排列***************************************************************/
SELECT Sno, Grade /*order by 排序操作desc 降序 asc升序*/
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
/*匹配***************************************************************/
SELECT Sno,Cno /*涉及空值查询，“is”不能用“=”代替*/
FROM SC
WHERE Grade IS NOT NULL;
SELECT Sname 'name', Sno, Ssex /*模糊查询,''中间的为显示的名字*/
FROM Student
/*where Sname like '刘%';*/
WHERE Sname like'刘_';
SELECT *
FROM Course
--WHERE Cname LIKE 'PA\_s%'escape'\';/*ESCAPE '\'表示“\”后的一个字符为普通字符*/
SELECT Sname, Ssex
FROM Student
where Sdept in('CS','MA');/*in为或的关系*/
-- 等价于
WHERE Sdept= 'CS'OR Sdept='MA' OR Sdept='[IS]';
SELECT Sname, Sdept, Sage /*between...and表示范围*/
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
/*分组***************************************************************/
SELECT Cno,COUNT(Sno) /*groupby分组函数，求各个课程号及相应的选课人数*/
FROM SC
GROUP BY Cno;
SELECT Sno /*查询选修了3门及以上课程的学生学号*/
FROM SC
GROUP BY Sno
HAVING COUNT(*)>=3;
/*分组中的条件要用having，相当于where*/
SELECT Sno, AVG(Grade) /*查询平均成绩大于等于80分的学生学号和平均成绩*/
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=80;/*不能用where*/
SELECT Sdept, Ssex,COUNT(Sno) /*按系并区分男女统计各系学生人数、并按照降序排列*/
FROM Student
GROUP BY Sdept,Ssex
ORDER BY COUNT(Sno) DESC;
/*聚集函数**************************************************************/
SELECT COUNT(*) /*count统计元组个数*/
FROM Student;
SELECT COUNT(DISTINCT Sno) /*distinct去重*/
FROM SC;
SELECT Cno,COUNT(Sno) /*groupby分组函数，求各个课程号及相应的选课人数*/
FROM SC
GROUP BY Cno;
exists整理：
双重否定等于肯定！不存在一个人没有干一个事情就等于任何一个人都干了一个事情
任何一个人选的课程号都等于‘201215122’选的课程号，并且任何一个人的学号都等于选课表中的学号（所有的表都要用上！），要用到去重关键字
4、视图
1 /*视图*/
2 -- 视图相当于虚拟化的表，所以进行插入删除等操作尽量在原表进行
3 go
4 --drop view is_student;
5 create view is_student
6 as
7 select sno,sname,sage
8 from Student
9 where Sdept='IS'
10 with check option;
11 go
12 create view is_s1(sno,sname,grade)
13 as
14 select Student.sno,sname,Grade
15 from Student,SC
16 where Sdept='IS'and
17 Student.Sno=sc.Sno and
18 sc.Cno='1';
19 go
20 create view is_s2
21 as
22 select sno,sname,grade
23 from is_s1
24 where grade>=90;
25 go
26 create view bt_s(sno,sname,sbirth)
27 as
28 select sno,sname,2014-sage
29 from Student;
30 go
31 create view s_g(sno,gavg)
32 as
33 select sno,avg(grade)
34 from SC
35 group by Sno;
36 go
37 create view f_student(f_sno,name,sex,age,dept)
38 as
39 select sno,sname,ssex,sage,Sdept
40 from Student
41 where Ssex='女';
42 go
43 /*删除视图*/
44 drop view bt_s;
45 drop view is_s1;/*不加cascade也不报错？*/
46 /*查询视图*/
47 select sno,sage
48 from is_student
49 where sage<20;
50
51 select is_student.Sno,sname
52 from is_student,sc
53 where is_student.sno=sc.sno and sc.Cno='1';
54
55 select*
56 from s_g
57 --having gavg>85;/*error？*/
58 where gavg>85;/*正确的？*/
59 /*更新视图*/
60 update is_student
61 set Sname='刘辰'
62 where Sno='201215125';
63
64 insert into is_student/*插入失败，有了with check option，不满足sdept=IS的条件*/
65 values('201215131','赵新',20);
66 insert into Student/*插入成功*/
67 values('201215130','赵新新','男',20,'IS');
三、数据库安全性和完整性
1、授权与回收、角色
1 /*安全性*/
2 grant all privileges
3 on sc/*不能加table*/
4 TO u1;
5
6 grant select
7 on sc
8 to public;/*所有人*/
9
10 grant update(sno),select
11 on student
12 to u4;
13
14 grant insert
15 on sc
16 to u5
17 with grant option;/*允许u5将权限转授给其他用户*/
18 /*revoke回收权限*/
19 revoke update(sno)
20 on student
21 from u4;
22
23 revoke select
24 on sc
25 from public;
26
27 revoke insert
28 on sc
29 from u5 cascade;/*级联回收（全部回收）*/
30
31 /*创建角色,将角色授予其他角色或用户，简化操作*/
32 create role r1;
33 grant select,update,insert
34 on student
35 to r1;
36 grant r1 to r2,r3,r4;
37
38 revoke r1 from r2;/*一次性收回r2的三个权限*/
2、实体、参照完整性、用户定义完整性
1 /*数据库完整性*/
2 --实体完整性，定义码
3 create table sc(
4 sno char(9),
5 cno char(4),
6 grade smallint,
7 primary key (sno,cno)--只能在表级定义主码
8 );
9 --参照完整性 外码
10
11 .. sdept char(20) foreign key references dept(deptno)
12 ..--deptno 必须是dept 的主码
13 create table sc(
14 sno varchar(9) not null,
15 cno varchar(4) not null,
16 grade smallint,
17 primary key(sno,cno),
18 foreign key (sno) references student(sno)
19 on delete cascade--级联删除
20 on update cascade,--级联更新
21 foreign key (cno) references course(cno)
22 on delete no action--拒绝删除
23 );
24 --用户定义完整性
25 create table ssc(
26 sno varchar(9) unique not null,
27 /*unique not null相当于primary key的作用*/
28 cno varchar(4) not null,
29 grade smallint check (grade>=0 and grade<=100),
30 ssex char(2) check (ssex in ('男','女'))
31 /*check规范输入，给定范围*/
32 );
33 --元组上的约束条件
34 create table student(
35 sno char(9),
36 sname char(8)not null,
37 ssex char(2),
38 sage smallint,
39 sdept char(20),
40 primary key(sno),
41 check (ssex='女'or sname not like'Ms.%')
42 /*当学生的性别是男时，其名子不能以Ms.开头*/
43 );
44 /*完整性约束命名->便于修改约束*/
45 create table student_s(
46 sno numeric(6,0)constraint c1 check(sno between 90000 and 99999),
47 sname char(20) constraint c2 not null,
48 sage numeric(3,0) constraint c3 check(sage<30),
49 ssex char(2) constraint c4 check(ssex in ('男','女')),
50 constraint studentkey primary key(sno)
51 );
52 --修改约束(要先删除再增加新的)
53 alter table student_s
54 drop constraint c4;
55 alter table student_s
56 drop constraint c1;
57 alter table student_s
58 add constraint c1 check(sno between 900000 and 900000);
3、触发器：改写例题5.21、22、23
1 /*触发器练习*/
2 --1.当对表SC的Grade属性进行修改时，若分数增加了10%
3 --则将此次操作记录到下面表中：SC_t（Sno,Cno,Oldgrade,Newgrade）
4 go
5 create trigger sc_4 on sc
6 for update
7 as
8 if update (grade)
9 begin
10 insert into sc_t(sno,cno,oldgrade,newgrade)
11 select deleted.sno,deleted.cno,deleted.grade,inserted.grade
12 from deleted,inserted
13 where (inserted.Grade>=1.1*deleted.Grade);
14 end
15 --测试
16 drop trigger sc_4;
17 update sc set grade=85
18 where sno='201215121'and Cno='2';
19 --right
20 --2.将每次对表Student的插入操作所增加的学生个数
21 --记录到表StudentInsertLog中
22 create table studentinsertlog(
23 count smallint
24 );
25 go
26 create trigger student_1 on student
27 for insert
28 as
29 insert into studentinsertlog select COUNT(*)
30 from inserted;
31 --测试
32 insert into Student
33 values ('201790396','张耀元','男',20,' IS');
34 delete from student where Sno='201790396';
35 --right
36 --3.定义一个BEFORE行级触发器，为教师表Teacher 定义完整性规则
37 --“教授的工资不得低于4000元，如果低 于4000元，自动改为4000元”。
38 create table sal(
39 position varchar(10),
40 salary int
41 );
42 go
43 create trigger sal_1 on sal
44 for insert , update
45 as
46 update sal set sal.salary=4000
47 from sal,inserted
48 where inserted.salary<4000 and inserted.position='教授';
49 drop trigger sal_1;
50 --测试
51 insert into sal values('教授',200);
52 update sal set salary=500 where position='教授';
53 --right1 /*触发器练习*/
2 --1.当对表SC的Grade属性进行修改时，若分数增加了10%
3 --则将此次操作记录到下面表中：SC_t（Sno,Cno,Oldgrade,Newgrade）
4 go
5 create trigger sc_4 on sc
6 for update
7 as
8 if update (grade)
9 begin
10 insert into sc_t(sno,cno,oldgrade,newgrade)
11 select deleted.sno,deleted.cno,deleted.grade,inserted.grade
12 from deleted,inserted
13 where (inserted.Grade>=1.1*deleted.Grade);
14 end
15 --测试
16 drop trigger sc_4;
17 update sc set grade=85
18 where sno='201215121'and Cno='2';
19 --right
20 --2.将每次对表Student的插入操作所增加的学生个数
21 --记录到表StudentInsertLog中
22 create table studentinsertlog(
23 count smallint
24 );
25 go
26 create trigger student_1 on student
27 for insert
28 as
29 insert into studentinsertlog select COUNT(*)
30 from inserted;
31 --测试
32 insert into Student
33 values ('201790396','张耀元','男',20,' IS');
34 delete from student where Sno='201790396';
35 --right
36 --3.定义一个BEFORE行级触发器，为教师表Teacher 定义完整性规则
37 --“教授的工资不得低于4000元，如果低 于4000元，自动改为4000元”。
38 create table sal(
39 position varchar(10),
40 salary int
41 );
42 go
43 create trigger sal_1 on sal
44 for insert , update
45 as
46 update sal set sal.salary=4000
47 from sal,inserted
48 where inserted.salary<4000 and inserted.position='教授';
49 drop trigger sal_1;
50 --测试
51 insert into sal values('教授',200);
52 update sal set salary=500 where position='教授';
53 --right
4、创建用户
1 drop login zyy_1;
2
3 create login zyy_1/*创建登录用户*/
4 with password=' ',
5 default_database=master,/*默认数据库*/
6 check_expiration=off,/*实施强制密码过期，默认off*/
7 check_policy=off;/*强制实施Windows密码策略，默认on*/
8
9
10 create user zyy_1 for login zyy_1 with /*给登陆账户创建一个数据库账户*/
11 default_schema=dbo;
12
13 drop user zyy_1; --删除数据库用户：
14 --exec sp_addrolemember 'db_owner','zyy_1';
15
16 alter role db_owner/*删除角色的用户*/
17 drop member zyy_1;
18 alter role db_owner/*给角色赋予用户*/
19 add member zyy_1;
20
21 grant select on student to zyy_1;/*给用户授权以及收回权力*/
22 revoke select on student from zyy_1;
23
24 --禁用登陆帐户
25
26 alter login zyy_1 disable;
27 --启用登陆帐户
28
29 alter login zyy_1 enable;
30 --登陆帐户改名
31
32 alter login zyy_1 with name=zyy_3;
33 --登陆帐户改密码：
34
35 alter login zyy_1 with password='123456';
36 alter login zyy_1 with password=' ';
37 --数据库用户改名：
38
39 alter user zyy_1 with name=zyy_3;
40 --更改数据库用户 defult_schema：
41
42 alter user zyy_1 with default_schema=sales;
43 --删除数据库用户：
44
45 drop user zyy_1;
46 --删除 SQL Server登陆帐户：
47
48 drop login zyy_3;
四、数据库编程
1、过程：改写例8.9，P270第二题
1 --转账
2 create database ZYY_ACCOUNT;
3
4 create TABLE account (
5 accountnum int primary key,
6 total float
7 );
8 insert into account
9 values(01003813828,10000),(01003815868,10000);
10 select * from account;
11
12 drop proc transfer
13 go
14 create proc transfer(@accountin int,@accountout int, @money float)
15 as
16 begin
17 begin tran
18 /*定义变量*/
19 declare @totalDepositOut float;
20 declare @totalDepositln float;
21 declare @inAccountnum int;
22 /*当账户为outAccount时，检测转出账户的余额total*/
23 set @totalDepositOut = (select total from account where accountnum = @accountout);
24 if @totalDepositOut is null
25 begin
26 rollback tran
27 return
28 end
29 if @totalDepositOut < @money
30 begin
31 rollback tran
32 return
33 end
34 /*检测转入账户是否存在*/
35 set @inAccountnum = (select accountnum from account where accountnum = @accountin)
36 if @inAccountnum is null
37 begin
38 rollback tran
39 return
40 end
41
42 update account set total = total - @money
43 where accountnum = @accountout;/*修改转出账户余额，减去转出额*/
44 update account set total = total +@money
45 where accountnum = @accountin;/*修改转入余额，增加转入额*/
46 commit
47 end
48 go
49
50
51
52 exec transfer 1003813828,1003815868,500;
53
54 select *from account;
55 --课后习题
56 /*
57 1.统计离散数学的成绩分布情况，即按照各分段统计人数
58 2.统计任意一门课的平均成绩
59 3.将学生选课成绩从百分制改为等级制（即ABCDE）.
60
61 */
62 /*1.统计离散数学的成绩分布情况，即按照各分段统计人数*/
63 --插入统计数据
64 insert into Student values
65 ('202090001','tom_1','男',20,'IS'),
66 ('202090002','tom_2','男',20,'IS'),
67 ('202090003','tom_3','男',20,'IS'),
68 ('202090004','tom_4','女',20,'IS'),
69 ('202090005','tom_5','女',20,'IS'),
70 ('202090006','tom_6','女',20,'IS');
71 insert into Course values(8,'离散数学',null,null);
72 insert into SC values
73 ('202090001',8,40),
74 ('202090002',8,50),
75 ('202090003',8,60),
76 ('202090004',8,70),
77 ('202090005',8,80),
78 ('202090006',8,90);
79 --创建一个表用来存放成绩分布情况
80 create table GradeTJ(scoreLow60 int,scoreBetween60And80 int,scoreUp80 int);
81 --创建一个存储过程
82 go
83 create proc TongJi(@courseNumber int)
84 as
85 declare @container int;
86 --因为返回值不唯一，需要用到游标
87 declare my_cursor cursor for select Grade from SC where Cno = @courseNumber;
88 declare @count1 int;set @count1 = 0;
89 declare @count2 int;set @count2 = 0;
90 declare @count3 int;set @count3 = 0;
91 begin
92 open my_cursor;--打开游标
93 fetch my_cursor into @container;--获取my_cursor的下一条数据，赋值给@container
94 while(@@FETCH_STATUS = 0)--@@fetch_status 指针：0 FETCH 语句成功;
95 --1 FETCH 语句失败或此行不在结果集中;2 被提取的行不存在。
96 begin
97 if @container < 60 set @count1 = @count1+1;
98 if (@container>=60 and @container <80) set @count2 = @count2+1;
99 if (@container >= 80) set @count3 = @count3 + 1;
100 fetch my_cursor into @container;--没有这条语句就变成死循环了
101 end
102 close my_cursor;--关闭游标
103 deallocate my_cursor;--释放游标引用
104 insert into GradeTJ values(@count1,@count2,@count3);
105 end
106 go
107 drop proc TongJi;
108 --delete from GradeTJ;
109 exec TongJi 8;
110
111 /*2.统计任意一门课的平均成绩*/
112 --在统计表中加一个平均数字段
113 alter table GradeTJ add averageScore int;
114 --创建一个存储过程
115 go
116 create proc PingJun(@courseNum int)
117 as
118 --创建游标
119 declare my_cursor_2 cursor for select Grade from SC where Cno = @courseNum;
120 declare @container int;
121 declare @sumNumber int;
122 declare @count int;
123 begin
124 set @count = 0;
125 set @container = 0;
126 set @sumNumber = 0;
127 open my_cursor_2;
128 fetch my_cursor_2 into @container
129 while(@@FETCH_STATUS = 0)
130 begin
131 set @sumNumber = @sumNumber+ @container;
132 set @count = @count +1;
133 fetch my_cursor_2 into @container
134 end
135 insert into GradeTJ(averageScore) values (@sumNumber/@count);
136 close my_cursor_2;--关闭游标
137 deallocate my_cursor_2;--释放游标引用
138 end
139 go
140 drop proc PingJun;
141 exec PingJun 8;
142
143 /*3.将学生选课成绩从百分制改为等级制（即ABCDE）*/
144 --在sc表后加一列。代表等级制
145 alter table sc add scoreOrder varchar(2);
146 --创建一个存储过程
147 go
148 create proc DengJi
149 as
150 declare myCursor_3 cursor for select grade from sc;
151 declare @container int;
152 begin
153 open myCursor_3;
154 fetch myCursor_3 into @container;
155 while(@@FETCH_STATUS = 0)
156 begin
157 if @container < 60
158 update sc set scoreOrder = 'E' where Grade = @container;
159 if @container >= 60 and @container <70
160 update sc set scoreOrder = 'D' where Grade = @container;
161 if @container >= 70 and @container < 80
162 update sc set scoreOrder = 'C' where Grade = @container;
163 if @container >= 80 and @container < 90
164 update sc set scoreOrder = 'B' where Grade = @container;
165 if @container >= 90
166 update sc set scoreOrder = 'A' where Grade = @container;
167 fetch myCursor_3 into @container;
168 end
169 close myCursor_3;--关闭游标
170 deallocate myCursor_3;--释放游标引用
171 end
172 go
173 drop proc DengJi;
174 exec DengJi;
175
176 select *from Student;
177 select *from SC;
178 select *from Course;
179 select *from GradeTJ;
2、建立一个过程，用游标获取指定记录，并将取出的记录输出
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
