数据库期末速成100分训练，附练手数据库原件及教程

本文提供下面数据库代码的数据库原件，下载后可使用
教程如下：

1.打开sql sever

2.找到数据库

3.右键数据库点击“附加”，然后点击“添加”

4.导入数据库原件，点击确定

ps：如果没有sqlsever 或者页面编辑器，请参考此链接进行下载

--查询显示所有学生的信息
select * from s;

--查询选选修了课程的学生学号
--一般默认不去重，如果需要去重加distinc去重
select distinct sno from sc;--distinct写在目标列前面

--查询所有选修信息
select * from sc;

--查询所有选修信息，按照课程号升序排列
--升序参数ASC默认可省略，降序DESC不可省略
--如果要查的表里面有主键，默认是按主键升序排
select* from sc order by cno;

--统计学生的总人数
select count(*) from s;

--查询选修了课程的学生人数
select count(distinct  sno) from sc;

--查询所有选修信息，按照课程号升序排列，同一课程内的学生成绩降序排列
select* from sc order by cno,grade desc;--升降序写在目标列后面


--查询所有学生的学号和年龄，并按年龄升序显示
select sno , datediff(year,sb,getdate())+1 from s order by sb;
--datediff函数：第一个参数是你要的时间单位，第二个参数是从什么时间算起，第三个参数是到哪个时间结束

--关于重命名，在目标列后面加名字即可
select sno 学号, datediff(year,sb,getdate())+1 年龄 from s order by sb;

--查询选修了课程编号为co1的学生学号和成绩
select sno,grade from sc where CNO='C01';

--查询“计算机”系“女”同学的学生学号和姓名
select sno,sn from s where sd='计算机' and sex='女';

--查询出生时间在1998-01-01到1999-12-31之间的学生的姓名和出生时间
select sn ,sb from s where sb between '1998-01-01' and '1999-12-31'; 
select sn ,sb from s where sb>='1998-01-01' and sb<='1999-12-31';

--查询姓“王”的所有学生的学号和姓名
select sno,sn from s where sn like '王%';--字符串同一用单引号
--模糊查询用%或者_ 
--%表示长度任意的字符串，_表示任意单个字符

--查询课程后缀名为"_实验"的课程信息
select * from c where cn like  '%_实验' escape '\';
--_本身是通配符，需要对它进行转义变成 \ _，在字符串表达式后用ESCAPE来定义转义字符



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

--查询非数学系和非计算机系的学生学号、姓名、所在系
select sno ,sn, sd from s where sd !='数学' and sd !='计算机';--法一
select sno ,sn, sd from s where sd not in('数学','计算机');--法二，in可以判断一个值是否属于一个集合

--查询所有缺少选课成绩的学生学号和对应的课程号
select sno ,cno from sc where grade is null;--null适用于判断属性值是否为空

--查询选修了课程的学生学号和平均成绩
select sno,avg(grade) from sc group by sno;

--group by 按照某个规则分类，having对分组进行筛选
--加了group by则会以每一分组的元组为计算对象

--查询平均成绩在90分以上的学生学号和平均成绩
select sno,avg(grade) from sc group by sno having avg(grade)>90;--groupby 和having搭配使用，having只能对分组的属性进行操作

--查询男生人数超过2名的系的名称(重点理解！)
select sd from s where sex='男'group by sd having count(sd)>2 ;

--查询选修课程号为“CO1”的学生学号和成绩
select sno ,grade from  sc where cno='C01';

--查询选修课程号为“C01”的学生姓名和成绩
select sn,grade from s,sc where cno='C01'and s.sno=sc.sno;
--如果不加s.sno=sc.sno那么就是一个笛卡尔积运算，不符合题意

--查询选修“数据结构”课程的学生学号、姓名和成绩
select s.sno,sn,grade from s,c,sc where s.sno=sc.sno and c.cno=sc.cno and cn='数据结构';
--如果多个表有相同的属性名，比如都有sno，你需要指定是哪个表的sno

--左外连接：返回左表全部的记录，如果右表没有找到对应记录，则将相关位置置为NULL
--右外连接：返回右表全部的记录，如果左表没有找到对应记录，则将相关位置置为NULL

--查询所有学生姓名以及选修课程号为“C01”的成绩，没有选修该课程的学生，成绩显示为空
select sn ,grade from s ,sc where  cno='C01' and s.sno=sc.sno;--查询所有学生姓名以及选修课程号为“C01”的成绩,但不能显示没有选修该课程的学生
select sn ,grade from s left outer join sc on sc.sno=s.sno and cno='C01';--正解1：左外连接
select sn ,grade from sc right outer join s on sc.sno=s.sno and cno='C01';--正解2：右外连接


--查询选修“数据结构”课程的学生学号、姓名、成绩
select s.sno,sn,grade from s,c,sc where cn='数据结构'and s.sno=sc.sno and c.cno=sc.cno;

--自连接：一个表和自己连接，需要对表进行重命名，新表名作为表中属性的前缀
--查询每门课的间接先修课程号（即先修课程的先修课程号）
select c1.cno 课程,c2.pc 间接先修课程 from c c1 ,c c2 where c1.pc=c2.cno;

--下面都是嵌套查询对前面一些查询的例子的写法
--你选择嵌套查询或者前面的写法都可以

--查询选修‘C02’课程的学生姓名
select sn from s where sno in
(select sno from sc where cno='C02');--先查一下哪些学号选择了C02，再查哪些学生是在这些查到的学号里面

select sn from s,sc where s.sno=sc.sno and cno='C02';--效果和上面一样

--查询没有选修‘C02’课程的学生姓名
select sn from s where sno not in
(select sno from sc where cno='C02');

--查询选修‘C01’课程的成绩高于王玲的学生的学号和成绩
select s.sno , grade from s,sc where s.sno=sc.sno and cno ='C01'and grade>--看谁的成绩大于王玲
(select grade from s, sc where s.sno=sc.sno and cno='C01' and sn='王玲')--看王玲的c01成绩

--法二
select sno,grade from sc where cno='C01' and grade >--看谁C01比王玲高
(select grade from sc where cno='C01' and sno in--王玲C01成绩
(select sno from s where sn='王玲')
)

--查询每个学生所修课程成绩超过其所有选课平均成绩的学号和课程号
select sno ,cno from  sc sc1 where grade>--查询某个人的某科目成绩
(select avg(grade) from sc sc2 where sc1.sno=sc2.sno)--查询这个人的所选课平均成绩
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

--查询其他系中比计算机系学生年龄都小的学生
select * from s where sd!='计算机' and  sb>
all(select sb from s where sd='计算机');--量词any 或者 all加在子查询前

select * from s where sd!='计算机' and  sb>
(select max(sb) from s where sd='计算机');--量词any 或者 all加在子查询前

--查询其他系中比计算机系某一学生年龄小的学生
select * from s where sd!='计算机' and  sb>
any(select sb from s where sd='计算机');--量词any 或者 all加在子查询前

select * from s where sd!='计算机' and  sb>
(select min(sb) from s where sd='计算机');--法二

--查询选修了'C02'课程的学生姓名
select sn from s where sno in(
select sno from sc where cno='C02')

select sn  from s ,sc where s.sno=sc.sno and sc.cno='C02';--法二

select sn from s where exists(
select * from sc where sc.sno=s.sno and cno='C02');--法三
--exists用于判断一个子查询的结果是否为空，如果子查询结果为空，则exists为真，否则为假

--in和exists区别：
--in是用一个字段匹配多个值，exists则是用于检查子查询的结果是否为空
--使用exists关键字，它是先进行主查询，然后逐个的执行子查询进行匹配。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

注：嵌套查询的简单写法
一般问题问“查满足条件R的所有S”就是嵌套查询

解题套路如下：

select S.属性from S --查属性
where not exists
(select * from R --查条件
where not exists
(select * from RS where S与RS连，R与RS连)--连接条件
)
1
2
3
4
5
6

例1：查询选修了所有课程的学生

--查询选修了所有课程的学生的姓名
select sn from s where not exists--查属性：学生姓名
(
	select * from c where not exists--查条件：全部课程
(
	select * from sc where s.sno =sc.sno and c.cno=sc.cno--连接条件
	)
)
1
2
3
4
5
6
7
8

例2：查询所学课程包含学生“S03”所学课程的学生姓名

select sn from s where not exists--查属性：学生姓名
(select * from sc sc1 where sc1.sno='S03' --查条件：学生S03选了什么
and not exists
(select * from sc sc2 where s.sno=sc2.sno and sc1.cno=sc2.cno)--连接条件
)
1
2
3
4
5

--查询没有选修‘C02’课程的学生姓名
select sn from s where sno not in
(select sno from sc where s.sno=sc.sno and cno='C02');

select sn from s where not exists
(select * from sc where sc.sno=s.sno and cno='C02');--法二

--查询选修了全部课程的学生的姓名（经典的嵌套查询）
--选了全部课程，等价于，没有一门课不选
select sn from s where not exists
(select * from c where not exists--没有某课程的记录
(select * from sc where sc.sno =s.sno and sc.cno =c.cno)
)--没有该学生选修该课程的记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14

--并运算union，union会自动去重
--查询选修了课程号为"C01"或者"C02"的学生学号
select sno from sc where cno='C01'
union
select sno from sc where cno='C02'

select distinct sno from sc where cno='C01' or cno='C02'; --法二


--交运算intersect
--查询选修了课程号为"C01"且选修"C02"的学生学号
select sno from sc where cno='C01'
intersect
select sno from sc where cno='C02'

select sno from sc where cno='C01'and sno in
(select sno from sc where cno='C02')

--差运算except
--查询选修了课程号为"C01"但没有选修"C02"的学生学号
select sno from sc where cno='C01'
except
select sno from sc where cno='C02'

select sno from sc where cno='C01' and sno not in
(select sno from sc where cno='C02')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

--插入元祖
--将一个新学生记录，插入学生关系表S中
--('S31','王浩','计算机','1999-10-15','男')
select * from s;
insert into s(sno,sn,sd,sb,sex)values ('S31','王浩','计算机','1999-10-15','男')

--因为是插入了所有属性，所以也可以把属性列表省略
--法二：
insert into s values ('S32','林浩','计算机','1999-10-15','男')


--向表SC中插入一条选课元组（'S31','C01'）
insert into sc(sno,cno) values('S31','C01')--这里没有插入所有属性，所以属性名列表不能省略


--修改属性值 update 表名 set 属性=...where ...
--update只能对一个表操作，不能同时修改多个表

--修改学生表S中'许若'的所在系改成‘计算机’系
update s set sd='计算机'where sn='许若'


--将学号为'S02'的学生所学'高等数学'的成绩改为93.0

update sc set grade=93.0 where sno='S02' and cno =
(select cno from c where cn='高等数学')

update sc set grade=93.0 where sno='S02' and cno in
(select cno from c where cn='高等数学')--法二

--如果需要利用其他表信息进行更新，可以利用嵌套查询

--若课程成绩低于该课程平均成绩，则将成绩提高5%
update sc set grade=grade*1.05 where grade<
(select avg(grade) from sc sc1 where sc.cno=sc1.cno)


--删除元组 delete from 表名 where...
--delete也只能对一个表进行操作

--删除所有成绩为空值的选课记录
delete from sc where grade=NULL

--删除成绩低于所有课程平均成绩的选课元组
delete from sc where grade<
(select avg(grade) from sc )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

注意：写视图，那页查询就不能有别的代码了

--创建视图的一般格式：
--create view 视图名 属性列1 属性列2... as 子查询

--创建数学系学生视图
create view m_s(m_sno,m_sn,m_sb) as select sno,sn,sb from s where sd='数学'
1
2
3
4
5

--创建学生成绩视图
create view s_grade(sno,sn,cno,grade)
as select s.sno,sn,cno,grade from s,sc where s.sno =sc.sno
1
2
3

--定义由学号及该学生的平均成绩组成的视图
create view s_ave(sno,gave)--gave是列名grade average缩写
as select sno,avg(grade) from s_grade group by sno
1
2
3

--删除视图
--drop view 视图名
drop view m_s
1
2
3

如果要删除某个视图，及建立在它之上的视图，需要额外加一个cascade
不同DBMS对CASCADE支持程度不同

--查询学生的学号和平均成绩
select sno,avg(grade) from s_grade group by sno

--法二
select sno,gave from s_ave
1
2
3
4
5

--更新视图m_s数据
update m_s set m_sn='华婷'where m_sno='S16'

delete from m_s where m_sno='S08'
1
2
3
4

视图的作用：