数据库实验二：数据高级查询与更新实验

1、实验目的

（1）熟悉数据库的数据更新操作，包括插入、修改、删除
（2）掌握SQL嵌套查询等各种高级查询的设计方法等
（3）熟练掌握常用SQL语句的基本语法，能够通过SQL对数据库进行操作。

2、实验平台

利用实验一中安装的RDBMS系统及其交互查询工具来操作SQL语言。

3、实验内容及要求

（1）针对TPCH数据库，正确用户查询要求一设计各种嵌套查询；
（2）针对TPCH数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。
（3）理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。

参考实验用例：

根据实验一建立的学生选课数据库，练习对数据库的更新操作和高级查询操作。

学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：

表1. Student表结构

列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 主键
Sname 姓名 char 20 唯一
Ssex 性别 char 2
Sage 年龄 smallint
Sdept 所在系 char 20

表2. Course表结构

列名称 用途 类型 长度 约束 备注
Cno 课程号 char 4 主键
Cname 课程名 char 40
Cpno 先行课 char 4 外键
Ccredit 学分 smallint

表3. SC表结构

学生选课表SC，由以下属性组成：学号Sno(char型，长度为9)，课程号Cno(char型，长度为4)，成绩Grade(smallint),其中Sno和Cno构成主码。
列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 外键
Cno 课程号 char 4
Grade 成绩 smallint

列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 外键
Cno 课程号 char 4
Grade 成绩 smallint

一、更新操作

1、 插入数据

（1）将一个新学生元祖(学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入到Student表中。
INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES(‘200215128’,‘陈冬’,‘男’,‘IS’,18)
（2）将学生张成民的信息插入到Student表中。
INSERT INTO Student VALUES(‘200215126’,‘张成民’,‘男’,18,‘IS’)；/要补上入学日期才能插入成功/
（3）插入一条选课记录：(‘200215128’，‘1’)。
INSERT INTO SC(Sno,Cno) VALUES(‘200215128’,‘1’)；

2、 修改数据

（1）将学生200215121的年龄改为22岁。
UPDATE student SET Sage = 22 WHERE Sno=‘200215121’;
（2）将所有学生的年龄增加一岁。
UPDATE Student SET Sage = Sage +1
（3）将计算机科学系全体学生的成绩置零。
UPDATE sc SET Grade= 0
WHERE ‘CS’=(select Sdept from student where student.Sno=sc.Sno);
（4）检查数据是否修改

3、 删除数据

（1）删除学号为200215128的学生记录。

DELETE FROM Student WHERE Sno=‘200215128’

（2）删除所有学生的选课记录。

DELETE FROM SC

（3）删除计算机科学系所有学生的选课记录。

DELETE FROM SC WHERE ‘CS’=(select Sdept from student where student.Sno=SC.Sno );

（4）检查数据是否删除

二、高级查询操作

1、 嵌套查询

（1）查询与“刘晨”在同一个系学习的学生。

SELECT Sno,Sname,Sdept FROM Student

WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= ‘刘晨’);

（2）查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname FROM Student

WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM course WHERE Cname= ‘信息系统’));

（3）找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno FROM SC x

WHERE Grade >= ( SELECT AVG(Grade) FROM SC y

             WHERE y.Sno=x.Sno);

（4）查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。

SELECT Sname,Sage FROM Student

WHERE Sage < ANY ( SELECT Sage FROM Student WHERE Sdept= ‘CS’)

   AND  Sdept <> 'CS' ;

（5）查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。

SELECT Sname,Sage FROM Student

WHERE Sage < ALL ( SELECT Sage FROM Student WHERE Sdept= ‘CS’)

   AND  Sdept <> 'CS' ;

（6）查询选修了1号课程的学生姓名。

SELECT Sname FROM Student

 WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');

（7）查询没有选修1号课程的学生姓名。

SELECT Sname FROM Student

 WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');

（8）查询选修了全部课程的学生姓名。

SELECT Sname FROM Student

 WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC 	  WHERE Sno=Student.Sno AND Cno= 
 Course.CnoSno));

（9）查询至少选修了学生200215121选修的全部课程的学生号码。

SELECT DISTINCT Sno FROM SC scx

 WHERE NOT EXISTS (SELECT * FROM SC scy WHERE scy.Sno='200215122' AND NOT EXISTS (SELECT * 	FROM SC scz WHERE 
 scz.Sno=scx.Sno AND scz.Cno= scx.Cno));

2、集合查询

（1）查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT * FROM Student WHERE Sdept= ‘CS’

UNION

SELECT * FROM Student WHERE Sage<=19;

（2）查询选修了课程1或课程2的学生。

SELECT Sno FROM SC WHERE Cno= ‘1’

UNION

SELECT Sno FROM SC WHERE cno= ‘2’;

（3）查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT * FROM Student WHERE Sdept=‘CS’

INTERSECT

SELECT * FROM Student WHERE Sage<=19;

（4）查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

SELECT Sno FROM SC WHERE Cno= ‘1’

INTERSECT

SELECT Sno FROM SC WHERE cno= ‘2’;

（5）查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT * FROM Student WHERE Sdept=‘CS’

EXCEPT

SELECT * FROM Student WHERE Sage <=19;