数据库原理及应用实验指导★---实验4-SQL语言——SELECT查询操作.doc

实验4 SQL语言SELECT查询操作实验4 SQL语言SELECT查询操作实验示例实验示例中要使用包括如下三个表的“教学管理”数据库JXGL：(1)学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno。(2)课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno。(3)学生选课SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(SNO,CNO)。1、在SQL SERVER查询分析器或企业管理器(以具有相应操作权限的某用户登录)的SQL操作窗口中执行如下命令创建数据库。需要说明的是不同数据库系统其创建数据库的命令或方式有所不同。CREATE DATABASE JXGL2、刷新数据库目录后，选择新出现的JXGL数据库，在SQL操作窗口中，创建Student、SC、Course三表及表记录插入命令如下：Create Table Student( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20),Sage SMALLINT CHECK(Sage=15 AND Sage=90;这里使用了DISTINCT短语，当一个学生有多门课程成绩大于等于90时，他的学号也只列一次。执行结果如图4-1所示。图4-1 在查询分析器中的查询执行情况例2 查年龄大于18，并不是信息系（IS）与数学系（MA）的学生的姓名和性别。SELECT Sname,Ssex FROM StudentWHERE Sage18 AND Sdept NOT IN (IS, MA);在企业管理器中的执行情况如图4-2所示。图4-2 在企业管理器中的查询执行情况说明（1）在企业管理器中打开子窗口的方法：先选中数据库选中表在任一表上，按鼠标右键从快捷菜单中选择“打开表”点击“返回所有行”子菜单项在打开返回表内容的子窗口后，按企业管理器工具栏上的“SQL”图标，即能把子窗口分为上下两部分，上面部分能输入不同的SQL命令来执行，执行时按企业管理器工具栏上的“运行”图标即可；（2）限于篇幅，其它查询命令的执行窗口与运行情况类似于上两图，原则将不再列出。例3 查以”MIS_”开头，且倒数第二个汉字为“导”字的课程的详细情况。SELECT * FROM CourseWHERE Cname LIKE MIS#_%导_ ESCAPE #;例4 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数 */FROM SC;例5 查询计算机系（CS）选修了2门及以上课程的学生的学号。SELECT Student.Sno FROM Student,SCWHERE Sdept=CS AND Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;例6 查询Student表与SC表的广义笛卡尔积。Select Student.*,SC.* From Student,SC;或 Select Student.*,SC.* From Student Cross Join SC;例7 查询Student表与SC表基于学号SNO的等值连接。Select *From Student,SCWHERE Student.Sno=SC.Sno;例8 查询Student表与SC表基于学号SNO的自然连接。SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SC WHERE Student.Sno=SC.Sno;或 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student INNER JOIN SC ON Student.Sno=SC.Sno;例9 查询课程之先修课的先修课(自身连接例)。SELECT FIRST.Cno, SECOND.PcnoFROM Course FIRST, Course SECONDWHERE FIRST.Pcno=SECOND.Cno;我们为Course表取两个别名FIRST与SECOND，这样就可以在SELECT子句和WHERE子句中的属性名前分别用这两个别名加以区分。例10 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno;例11 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均需全部列出）。SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Course.Cno, Grade, cname, cpno, ccreditFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on SC.cno=Co;例12 查询性别为男、课程成绩及格的学生信息及课程号、成绩。SELECT Student.*,Cno,GradeFROM STUDENT INNER JOIN ON Student.Sno=SC.SnoWHERE SSEX=男 AND GRADE =60例13 查询与“钱横”在同一个系学习的学生信息。SELECT * FROM StudentWHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname=钱横);或 SELECT * FROM StudentWHERE Sdept =( SELECT SdeptFROM StudentWHERE Sname=钱横); - 当子查询为单列单行值时可以用“=”或 SELECT S1.*FROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=钱横;一般来说，连接查询可以替换大多数的嵌套子查询。SQL-92支持“多列成员”的属于(IN)条件表达，例：例14 找出同系、同年龄、同性别的学生。Select * from Student as TWhere (T.sdept,T.sage,T.ssex) IN (Select sdept,sage,ssexFrom student as SWhere S.snoT.sno); - SQL Server 2000可能不支持它等价于逐个成员IN的方式表达，如下（能在SQL Server 2000中执行）：Select * from Student TWhere T.sdept IN( Select sdeptFrom student SWhere S.snoT.sno andT.sage IN ( Select sageFrom student XWhere S.sno=X.sno and X.snoT.sno andT.ssex IN ( Select ssexFrom student YWhere X.sno=Y.sno and Y.snoT.sno);例15 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。SELECT Sno,Sname,Sdept FROM Student - IN嵌套查询方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno IN( SELECT Cno FROM Course WHERE Cname=数据库系统);或 SELECT Sno,Sname,Sdept FROM Student - IN、= 嵌套查询方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno=( SELECT Cno FROM Course WHERE Cname=数据库系统);或 SELECT Student.Sno,Sname,Sdept -连接查询方法FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.CnoAND Course.Cname=数据库系统;或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Where Exists( Select * From SCWhere SC.Sno=Student.Sno AndExists( Select * From CourseWhere SC.Cno = Course.Cno And Cname=数据库系统);或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Where Exists( Select * From courseWhere Cname=数据库系统 and Exists( Select * From SC Where sc.sno=student.sno and SC.Cno = Course.Cno);例16 检索至少不学2和4两门课程的学生学号与姓名。SELECT Sno,Sname FROM StudentWHERE Sno NOT IN(SELECT Sno FROM SC WHERE Cno IN (2,4);例17 查询其他系中比信息系IS所有学生年龄均大的学生名单,并排序输出。SELECT Sname FROM StudentWHERE SageAll(SELECT Sage FROM StudentWHERE Sdept=IS) AND Sdept ISORDER BY Sname;本查询实际上也可以用集函数实现：SELECT Sname FROM Student WHERE Sage(SELECT MAX(Sage) FROM StudentWHERE Sdept=IS) AND SdeptISORDER BY Sname;例18 查询哪些课程只有女生选读。(本题有多于两种表达法)SELECT DISTINCT CNAMEFROM COURSE CWHERE 女=ALL( SELECT SSEX FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO);或 SELECT DISTINCT CNAME FROM COURSE CWHERE NOT EXISTS( SELECT * FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO AND STUDENT.SSEX=男);例19 查询所有未修1号课程的学生姓名。SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);或 SELECT Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=1);但如下是错的：SELECT Sname FROM Student,SCWHERE SC.Sno=Student.Sno AND Cno1;例20 查询选修了全部课程的学生姓名(为了有查询结果，自己可调整一些表的)。SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM CourseWHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=SC.Sno AND Cno=Course.Cno); 由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名没有一门课程是他不选的。本题的另一操作方法是：SELECT Sname FROM Student,SC WHERE Student.Sno=SC.SnoGroup by Student.Sno,Sname having count(*)=(SELECT count(*) FROM Course);例21 查询至少选修了学生98001选修的全部课程的学生号码。本题的查询要求可以做如下解释，不存在这样的课程y，学生98001选修了y，而要查询的学生x没有选。写成的SELECT语句为：SELECT SnoFROM Student SXWHERE NOT EXISTS( SELECT * FROM SC SCY WHERE SCY.Sno=98001 ANDNOT EXISTS( SELECT * FROM SC SCZWHERE SCZ.Sno=SX.Sno AND SCZ.Cno=SCY.Cno);例22 查询选修了课程1或者选修了课程2的学生学号集。SELECT Sno FROM SC WHERE Cno=1UNIONSELECT Sno FROM SC WHERE Cno=2;注意 扩展的SQL中有集合操作并(UNION)、集合操作交(INTERSECT)和集合操作差(EXCEPT或MINUS)等。SQL的集合操作要求相容即属性个数、类型必须一致，属性名无关，最终结果集采用第一个结果的属性名，缺省为自动去除重复元组，各子查询不带Order By，Order By放在整个语句的最后。例23 查询计算机科学系的学生与年龄不大于19岁的学生的交集。SELECT * FROM Student WHERE Sdept=CSINTERSECTSELECT * FROM Student WHERE Sage=19; - SQL Server 2000不支持运行本查询等价于“查询计算机科学系中年龄不大于19岁的学生。”，为此变通法为：SELECT * FROM Student WHERE Sdept=CS AND Sage85;SQL-92允许在 From中使用查询表达式，并必须为查询表达式取名。它等价于如下未使用查询表达式的形式：Select Student.Sno,Sname,AVG(Grade)From Student,SC Where Student.Sno = SC.SnoGroup By Student.Sno,Sname HAVING AVG(Grade)85;例26 查出课程成绩在90分以上的女学生的姓名、课程名和成绩。SELECT SNAME,CNAME,GRADEFROM (SELECT SNAME,CNAME,GRADEFROM STUDENT,SC,COURSEWHERE SSEX=女 AND STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO)AS TEMP(SNAME,CNAME,GRADE)WHERE GRADE90; - 特意用查询表达式实现，完全可用其它方式实现但如下使用查询表达式的查询，则不易改写为其它形式。例27 查询各不同平均成绩所对应的学生人数(给出平均成绩与其对应的人数)。Select avgr,COUNT(*)From (Select sno,avg(grade) From SCGroup By sno) as SG(Sno,avgr)Group By avgr;例28 查出学生、课程及成绩的明细信息及课程门数、总成绩及平均成绩。SELECT sno,cno,grade - COMPUTE的其它使用方法，请参阅帮助。FROM scORDER BY snoCOMPUTE count(cno),SUM(grade),avg(grade) BY sno 例29 建立信息系学生的视图（含有学号、姓名、年龄及性别），并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。通过视图查找年龄大于等于18岁的女学生。CREATE VIEW IS_StudentAS SELECT Sno,Sname,Sage,SsexFROM StudentWHERE Sdept=IS WITH CHECK OPTION;SELECT * FROM IS_Student WHERE Sage=18 AND Ssex=女;例30 设有学生-课程关系数据库，其数据库关系