一、需求
利用前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)
多表：
- 查询每个同学的201课程的成绩，显示如下字段信息：学号、姓名、课程号、成绩、绩点；
- 查询每个同学的201课程的成绩(包括没有参加201课程考试的同学)，显示如下字段信息：学号、姓名、课程号、成绩、绩点；
- 查询每个同学的所有课程的平均成绩(包括没有参加考试的同学)，显示如下字段信息：学号、姓名、平均成绩；
- 查询所有成绩对应的学生信息，没有对应学生信息的显示为空；
- 创建学生表和成绩的全外连接；
- 查询201课程的最高分学生记录，使用max函数和一行多列的子查询两种方式；
- 查询参加了202课程考试成绩中，比所有的201课程分数都低的成绩信息；
- 使用exists，完成如下操作：如果103同学参加了201课程的考试，则修改成绩为87；
9.使用in完成如下功能，查询所有男生的考试成绩；
外键： - 删除序号为3的学生记录
- 为学生表的学号添加唯一索引；
- 为成绩表添加外键约束；主键表为学生表，关联字段为学号；并设置为删除时RESTRICT，更新时CASCADE；
- 删除【学生表】序号为2的学生记录；
- 更新【学生表】序号为2的学号为3；
- 查询两个表中的内容；
- 修改【成绩表】外键约束；并设置为删除时SET NULL，更新时RESTRICT；
- 更新【学生表】序号为3的记录学号为5；
- 删除【学生表】学号为3的记录；
- 查询两个表中的内容；
二、实现
-
查询每个同学的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