内容发布更新时间 : 2024/12/25 21:01:34星期一 下面是文章的全部内容请认真阅读。
《数据库概论》实验指导书
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