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;