数据库实验之多表查询与外键

一、需求

利用前2次实验中的【学生表】和【成绩表】进行如下操作；
【学生表】结构如下：
序号，int类型，自增；主键；
学号，char(10);唯一索引；
姓名：varchar(20);
性别：枚举类型，取值为男或女；
【学生表】数据如下：
(1,101,张三,男)
(2,102,李四,女)
(3,103,王五,男)
【成绩表】结构如下：
序号，int类型，自增；主键；
学号，char(10)；
课程号：char(10);
成绩：int；
绩点：dec(3,1);
【成绩表】数据如下：
(1,101,201,80,0)
(2,101,202,75,0)
(3,101,203,84,0)
(4,102,201,89,0)
(5,102,202,90,0)
(6,102,203,65,0)
(7,104,201,89,0)
(8,104,202,69,0)
多表：

1. 查询每个同学的201课程的成绩，显示如下字段信息：学号、姓名、课程号、成绩、绩点；
2. 查询每个同学的201课程的成绩(包括没有参加201课程考试的同学)，显示如下字段信息：学号、姓名、课程号、成绩、绩点；
3. 查询每个同学的所有课程的平均成绩(包括没有参加考试的同学)，显示如下字段信息：学号、姓名、平均成绩；
4. 查询所有成绩对应的学生信息，没有对应学生信息的显示为空；
5. 创建学生表和成绩的全外连接；
6. 查询201课程的最高分学生记录，使用max函数和一行多列的子查询两种方式；
7. 查询参加了202课程考试成绩中，比所有的201课程分数都低的成绩信息；
8. 使用exists，完成如下操作：如果103同学参加了201课程的考试，则修改成绩为87；
9. 使用in完成如下功能，查询所有男生的考试成绩；

外键：

1. 删除序号为3的学生记录
2. 为学生表的学号添加唯一索引；
3. 为成绩表添加外键约束；主键表为学生表，关联字段为学号；并设置为删除时RESTRICT，更新时CASCADE；
4. 删除【学生表】序号为2的学生记录；
5. 更新【学生表】序号为2的学号为3；
6. 查询两个表中的内容；
7. 修改【成绩表】外键约束；并设置为删除时SET NULL，更新时RESTRICT；
8. 更新【学生表】序号为3的记录学号为5；
9. 删除【学生表】学号为3的记录；
10. 查询两个表中的内容；

二、实现

查询每个同学的201课程的成绩，显示如下字段信息：学号、姓名、课程号、成绩、绩点；

select stu.学号,stu.姓名,cj.课程号,cj.成绩,cj.绩点 from 学生表 stu inner join 成绩表 cj on stu.学号 = cj.学号 and cj.课程号 = 201;

查询每个同学的201课程的成绩(包括没有参加201课程考试的同学)，显示如下字段信息：学号、姓名、课程号、成绩、绩点；

select stu.学号,stu.姓名,cj.课程号,cj.成绩,cj.绩点 from 学生表 stu left join 成绩表 cj on stu.学号 = cj.学号 and cj.课程号 = 201;

查询每个同学的所有课程的平均成绩(包括没有参加考试的同学)，显示如下字段信息：学号、姓名、平均成绩；

select stu.学号,stu.姓名,avg(cj.成绩) 平均成绩 from 学生表 stu left join 成绩表 cj on stu.学号 = cj.学号 group by 学号;

查询所有成绩对应的学生信息，没有对应学生信息的显示为空；

select * from 学生表 stu left join 成绩表 cj on stu.学号 = cj.学号;

创建学生表和成绩的全外连接；

select * from 学生表 stu left join 成绩表 cj on stu.学号 = cj.学号
union
select * from 学生表 stu right join 成绩表 cj on stu.学号 = cj.学号;

查询201课程的最高分学生记录，使用max函数和一行多列的子查询两种方式；

方式1：
select max(cj.成绩) from 成绩表 cj where cj.课程号=201;
方式2：
select cj.成绩 from 成绩表 cj
where (cj.课程号,cj.成绩)=
(select 课程号,成绩 from 成绩表 where 课程号=201 order by 成绩 desc limit 1)
limit 1;

查询参加了202课程考试成绩中，比所有的201课程分数都低的成绩信息；

select * from 成绩表
where 成绩<(select min(成绩) from 成绩表 where 课程号=201) and 课程号 = 202;

使用exists，完成如下操作：如果103同学参加了201课程的考试，则修改成绩为87；

update 成绩表 set 成绩 = 87
where exists(select * from (select * from 成绩表) a where a.学号=103 and a.课程号=201);

使用in完成如下功能，查询所有男生的考试成绩；

select 成绩 from 成绩表
where 学号 in
(select 学号 from 学生表 where 性别='男');

删除序号为3的学生记录

delete from 学生表 where 序号=3;   

为学生表的学号添加唯一索引；

alter table 学生表 add unique(学号);

为成绩表添加外键约束；主键表为学生表，关联字段为学号；并设置为删除时RESTRICT，更新时CASCADE；

delete from 成绩表 where id in(7,8);（不删除这个外键将创建失败）
alter table 成绩表
add constraint fk_id foreign key(学号) references 学生表(学号)
on delete restrict on update cascade;

删除【学生表】序号为2的学生记录；

delete from 学生表 where 序号=2;
结果：删除操作被拒绝

更新【学生表】序号为2的学号为3；

update 学生表 set 学号=3 where 序号=2;

查询两个表中的内容；

select * from 学生表;
select * from 成绩表;
结果：从表（成绩表）中对应的也更新为3

修改【成绩表】外键约束；并设置为删除时SET NULL，更新时RESTRICT；

alter table 成绩表 drop foreign key fk_id;删除外键
alter table 成绩表 drop key fk_id;删除系统为外键创建的普通索引
alter table 成绩表
add constraint fk_id foreign key(学号) references 学生表(学号)
on delete set null on update restrict;

更新【学生表】序号为3的记录学号为5；

update 学生表 set 学号=5 where 学号=3;
结果：更新被拒绝

删除【学生表】学号为3的记录；

delete from 学生表 where 学号=3;   

查询两个表中的内容；

select * from 学生表;
select * from 成绩表;
结果:成绩表中学号为3的值变为null