《数据库概论》实验指导书
FROM Course
WHERE Cname = '信息系统' ) );
也可以使用连接查询来完成上述查询：
SELECT Student.Sno, Sname FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = '信息系统';
（6）嵌套查询（带有比较运算符的子查询）
例如：找出每个学生超过他所选修课程平均成绩的课程号。
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade) FROM SC y
WHERE y.Sno = x.Sno); （7）嵌套查询（带有ANY或ALL谓词的子查询）
例如：查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄。
SELECT Sname, Sage FROM Student
WHERE Sage WHERE Sdept = 'CS') AND Sdept <> 'CS'; 本查询也可以使用聚集函数来实现： SELECT Sname, Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept = 'CS') AND Sdept <> 'CS'; 又如：查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage WHERE Sdept = 'CS') AND Sdept <> 'CS'; 也可以使用聚集函数来实现： SELECT Sname, Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student 11 《数据库概论》实验指导书 WHERE Sdept = 'CS') AND Sdept <> 'CS'; （8）嵌套查询（带有EXISTS谓词的子查询） 例如：查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 又如：查询所有未选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 可以使用带有EXISTS谓词的子查询实现全称量词或蕴涵逻辑运算功能： 例如：查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno)); 又如：查询至少选修了学生200215122选修的全部课程的学生号码。 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=SCY.Cno)); （9）集合查询 例如：查询计算机系的学生以及年龄不大于19岁的的学生。 SELECT * 12 《数据库概论》实验指导书 FROM Student WHERE Sdept='CS' UNION /*并集运算*/ SELECT * FROM Student WHERE Sage<=19; 可以改用多重条件查询： SELECT * FROM Student WHERE Sdept='CS' OR Sage<=19; 又如：查询既选修了课程1又选修了课程2的学生（交集运算）。 SELECT Sno FROM SC WHERE Cno='1' INTERSECT /*交集运算*/ SELECT Sno FROM SC WHERE Cno='2'; 可以使用嵌套查询： SELECT Sno FROM SC WHERE Cno='1' AND Sno IN (SELECT Sno FROM SC WHERE Cno='2'); 思考：能不能改用多重条件查询？ SELECT Sno FROM SC WHERE Cno='1' AND Cno='2'; 再如：查询计算机系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept='CS' EXCEPT /*差集运算*/ SELECT * FROM Student WHERE Sage<=19; 可以改用多重条件查询： SELECT * FROM Student WHERE Sdept='CS' AND Sage>19; 13 《数据库概论》实验指导书 扩展练习（要求写出并执行SQL语句完成以下各种操作，记录查询结果） （1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）。 （2）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少3种方法求解） （3）查询选修了3号课程而且成绩为良好（80~89分）的所有学生的学号和姓名。 （4）查询学生200215122选修的课程的课程号、课程名 （思考：如何查询学生200215122选修的课程的课程号、课程名及成绩？） （5）找出每个学生低于他所选修课程平均成绩5分以上的课程号。（输出学号和课程号） （6）查询比所有男生年龄都小的女生的学号、姓名和年龄。 （7）查询所有选修了2号课程的学生姓名及所在系。 实验三SQL的数据更新（2学时） 1、实验目的 (1) 掌握DBMS的数据查询功能 (2) 掌握SQL语言的数据更新功能 2、实验内容 (1) update 语句用于对表进行更新 (2) delete 语句用于对表进行删除 (3) insert 语句用于对表进行插入 3、实验要求 (1) 熟练掌握SQL的数据更新语句INSERT、UPDATE、DELETE (2) 写出实验报告 4、实验步骤 4.1 认真阅读S_T.sql，理解其中插入和修改语句的作用和用法；执行S_T.sql，建立示例数据库S_T。 4.2 对学生关系Student、课程关系Course和选修关系SC进行数据插入、修改和删除元组（记录）的更新操作。 基本练习 （1）插入一个元组 练习1：将一个新学生元组（200215140，田刚，男，计算机系，19岁）插入Student表中。 INSERT INTO Student (Sno,Sname,Ssex,Sdept, Sage) VALUES('200215140','田刚','男'，'CS',19); 练习2：插入一条选课记录（‘200215140’, ‘3’）。 INSERT INTO SC VALUES('200215140','3',NULL); 也可以使用如下SQL语句： INSERT INTO SC(Sno,Cno) 14 《数据库概论》实验指导书 VALUES('200215140','3'); 思考：这两种用法有什么区别？（但最终效果一样！） （2）插入子查询结果 练习3：对每一个学生，求其平均成绩，并把结果存入新表Sno_grade。 /*首先创建新表Sno_grade：*/ CREATE TABLE Sno_grade ( Sno char(9) primary key, Avg_grade SMALLINT); go /*然后对SC表按学号分组求平均成绩，再把学号和平均成绩存入新表中：*/ INSERT INTO Sno_grade(Sno,Avg_grade) SELECT Sno,AVG(grade) FROM SC GROUP BY Sno; （3）修改某一个元组的值 练习4：将学生200215131的姓名改为“张莉莉” UPDATE Student SET Sname='张莉莉' WHERE Sno='200215131'; （4）修改多个元组的值 练习5：将所有计算机系学生的年龄统一增加1岁。 UPDATE Student SET Sage=Sage+1 WHERE Sdept='CS'; （5）带子查询的修改语句 练习6：将计算机系全体学生的成绩统一增加2分。 UPDATE SC SET Grade=Grade+2 WHERE 'CS'= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno); （6）删除某一个元组的值 练习7：删除学号为200215133的学生记录。 DELETE FROM Student WHERE Sno='200215133'; （7）删除多个元组的值 练习8：删除表Sno_grade中平均成绩低于90分（不含90分）的记录。 DELETE FROM Sno_grade WHERE Avg_grade<90; 练习9：删除表Sno_grade中全部记录。 15