数据库查询SQL 实验报告.docx

1、数据库查询SQL 实验报告实 验 报 告课程名称： 数据库系统概论 学 院： 工程学院 专 业： 年 级： 班 级： 姓 名： 学 号： 指导教师： 年 月 日教务处 制实验名称： 数据库查询1时间：2015-11-26地点：三教205机房班组： 教 师 评 语成绩教师签名实验报告1 实验目的 1 熟悉数据库的交互式SQL工具。2 熟悉通过SQL对数据库进行单表查询、连接查询的操作。2 实验工具KingbaseES利用KingbaseES及其交互式查询工具ISQLW来熟悉SQL。3 实验内容和要求1.在实验二中建立数据库的基础上，完成课本例3.16-60的各种查询；2. 根据以下要求认真填写实

2、验报告，记录所有的实验用例。4 实验方法及步骤例3.16 /*查询全体学生的学号和姓名*/SELECT SNO, SNAME FROMS-C.STUDENT注意：SELECT SNO, SNAME FROM 这些的大小写随意，但是S-C.STUDENT必须是指定大小写，要是改变大小写则可以用S-C.student 加分号，引号会自动变红色. 在SQL语句中用 /* 注释内容 */注释。例3.17 查询全体学生的姓名、学号、所在系SELECT SNAME,SNO,SDEPT FROMS-C.STUDENT例3.18 查询全体学生的详细记录SELECT* FROMS-C.STUDENT例3.19

3、查询全体学生的姓名及其出生年份SELECT SNAME, 2014-Sage FROMS-C.STUDENT注意：因为2014-Sage在生成表格的时候出现了“？”无法显示，改错方法就是在2014-Sage后面直接加“空格键和别名”如在下题给它取别名WSELECT SNAME, 2014-Sage W FROMS-C.STUDENT例3.20 查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示系名SELECT SNAME,YEAR OF BIRTH: ,2014-SAGE ,LOWER(SDEPT)FROMS-C.STUDENT指定别名：SELECT SNAME name,YEAR O

4、F BIRTH: birth,2014-SAGE birthday,LOWER(SDEPT) departmentFROMS-C.STUDENT例3.21 查询选修了课程的学生学号SELECT SNO FROMS-C.SC;注意：查询结果中有许多重复行，若想去掉重复，则指定DISTINCT。若没有指定，则默认为all。SELECT SNO FROMS-C.SC;等价于SELECT all SNO FROMS-C.SC;SELECT Distinct SNOFROMS-C.SC;例3.22 查询计算机科学系全体学生的名单SELECT SNAMEFROMS-C.STUDENTWHERE SDEPT

5、=CS;例3.23 查询所有年龄在20岁以下的学生姓名及其年龄SELECT SNAME,SAGeFROMS-C.STUDENTWHERE sage20;例3.24 查询考试成绩不及格的学生学号SELECT DISTINCT sno FROMS-C.SC WHERE grade60;这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。这里表明没有学生不及格。例3.25 查询年龄在20-23岁之间的学生姓名、系别和年龄SELECT SNAME,SDEPT,SAGE FROMS-C.STUDENT WHERE sage BETWEEN 20 AND 23;例3.26查询年

6、龄不在20-23岁之间的学生姓名、系别和年龄SELECT SNAME,SDEPT,SAGE FROMS-C.STUDENT WHERE sage NOT BETWEEN 20 AND 23;例3.27 查询计算机科学系（CS）、数学系(MA)和信息系(IS)学生的姓名和性别SELECT SNAME,SsexFROMS-C.STUDENTWHERE sdept in (CS,MA,IS);例3.28查询既不是计算机科学系（CS）、数学系(MA)也不是信息系(IS)学生的姓名和性别SELECT SNAME,SsexFROMS-C.STUDENTWHERE sdept not in (CS,MA,I

7、S);例3.29 查询学号为200215121的学生的详细情况SELECT* FROMS-C.STUDENT WHERE SNO LIKE 200215121;例3.30 查询所有姓刘的学生的姓名、学号和性别SELECT sname,sno,ssex FROMS-C.STUDENT where sname like 刘%;注意：（百分号）代表任意长度（长度可以为0）的字符串。例3.31查询姓“欧阳”且全名为三个汉字的的学生的姓名SELECT sname FROMS-C.STUDENT where sname like 欧阳-;注意：_（下划线）代表任意单个字符例3.32 查询名字中第二个字为“

8、阳”的学生的姓名和学号SELECT sname,snoFROMS-C.STUDENTwhere sname like -阳%;例3.33查询所有不姓刘的学生的姓名、学号和性别SELECT sname,sno,ssexFROMS-C.STUDENTwhere sname not like 刘%;例3.34 查询DB_DESIGN课程的课程号和学分SELECT cno,ccreditFROMS-C.COURSEwhere Cname likeDB_DESIGN ESCAPE;SELECT cno,ccreditFROMS-C.COURSEwhere Cname=数据库例3.35查询以“DB_”开头

9、，且倒数第三个字符为i的课程的详细情况SELECT*FROMS-C.COURSEwhere Cname likeDB_%i_ _ESCAPE;例3.36 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生学号和相应的课程号。SELECT sno,cnoFROMS-C.SCwhere grade is null;例3.37 查询所以有成绩学生的学号和课程号SELECT sno,cnoFROMS-C.SCwhere grade is NOT null;例3.38 查询计算机科学系年龄在20岁以下的学生姓名SELECT snameFROMS-C.STUDENTwher

10、e SDEPT=CS AND SAGE2;例3.48 查询平均成绩大于等于80分的学生学号和平均成绩SELECT SNO,AVG(GRADE) FROM S-C.SC WHERE AVG(GRADE)=80 GROUP BY SNO;服务器消息: KingbaseES ServerERROR: 在WHERE子句中不能使用聚集函数SELECT SNO,AVG(GRADE) FROM S-C.SC GROUP BY SNO HAVING AVG(GRADE)=80;例3.49 查询每个学生及其选修课程的情况SELECT STUDENT.*,SC.* FROM S-C.STUDENT,S-C.SC

11、WHERE STUDENT.SNO=SC.SNO;例3.50 对例3.49用自然连接完成SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADEFROM S-C.STUDENT,S-C.SC WHERE STUDENT.SNO=SC.SNO;例3.51 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名SELECT STUDENT.sno,sname FROM S-C.STUDENT,S-C.SC WHERE STUDENT.SNO=SC.SNO AND SC.CNO=2AND SC.GRADE=90;例3.52 查询每一门课的间接先修课（即先

12、修课的先修课）SELECT FIRST.CNO,SECOND.CPNOFROM S-C.COURSE FIRST,S-C.COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO;例3.53 外连接（每个学生的基本情况及其选课情况）SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADEFROM S-C.STUDENT left outer join S-C.SC on (student.sno=sc.sno);/* 也可以使用USING 来去掉结果中重复的值*/SELECT STUDENT.SNO,SNAME,SSEX,S

13、AGE,SDEPT,CNO,GRADE FROM S-C.STUDENT left outer join S-C.SC USING(SNO);例3.54 查询每个学生的学号、姓名、选修的课程名及其成绩SELECT STUDENT.SNO,SNAME,CNAME,GRADE FROM S-C.STUDENT,S-C.SC,S-C.COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO;例3.55 查询与“刘晨”在同一个系学习的学生分步查询SELECT SDEPT FROM S-C.STUDENT WHERE SNAME=刘晨; /*确定“刘晨”

14、所在系名*/ SELECT SNO,SNAME,SDEPT FROM S-C.STUDENT WHERE SDEPT=CS;查询所有在CS系的学生法1. 嵌套查询 SELECT SNO,SNAME,SDEPT FROM S-C.STUDENT WHERE SDEPT in (SELECT SDEPT FROM S-C.STUDENT WHERE SNAME=刘晨);法 2.自身连接 SELECT S1.SNO,S1.SNAME,S1.SDEPT FROM S-C.STUDENT S1,S-C.STUDENT S2 WHERE S1.SDEPT=S2.SDEPT AND S2.SNAME=刘晨;

15、法3. 带有比较运算符的子查询 SELECT SNO,SNAME,SDEPT FROM S-C.STUDENT WHERE SDEPT=(SELECT SDEPT FROM S-C.STUDENT WHERE SNAME=刘晨);例3.56 查询选修了课程名为“信息系统”的学生学号和姓名SELECT SNO,SNAME /*3.最后在student关系中查询sno和sname*/FROM S-C.STUDENT WHERE SNO IN (SELECT SNO /*2.然后在SC关系中找出选修了3号课程的学生学号*/ FROM S-C.SC WHERE CNO IN (SELECT CNO /

16、* 1.首先在course关系中找出信息系统的课程，结果为3号*/ FROM S-C.COURSE WHERE CNAME=信息系统 ) );连接查询SELECT STUDENT.SNO,SNAME FROM S-C.STUDENT,S-C.SC,S-C.COURSEWHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND COURSE.CNAME=信息系统;例3.57 查询每个学生超过他自己选修课程平均成绩的课程号SELECT SNO,CNO FROM S-C.SC xWHERE Grade=(select AVG(Grade) FROM S-C.

17、SC y WHERE y.sno=x.sno);注意：x是SC的别名，这题是相关子查询。求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。例3.58 查询非计算机系中比计算机科学系任意一个学生年龄小的学生姓名和年龄SELECT SName,sage FROM S-C.STUDENTWHERE sageANY(select sage FROM S-C.student WHERE sdept=CS)AND SDEPTCS;聚集函数求法：SELECT SName,sage FROM S-C.STUDENTWHERE sage(

18、select MAX(sage) FROM S-C.student WHERE sdept=CS)AND SDEPTCS;例3.59查询非计算机系中比计算机科学系所有学生年龄都小的学生姓名和年龄SELECT SName,sage FROM S-C.STUDENTWHERE sageALL (select sage FROM S-C.student WHERE sdept=CS)AND SDEPTCS;或者用聚集函数：SELECT SName,sage FROM S-C.STUDENTWHERE sage (select min(sage) FROM S-C.student WHERE sdep

19、t=CS)AND SDEPTCS;注意：事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。例3.60 查询所有选修了1号课程的学生姓名SELECT SNAMEFROMS-C.STUDENTWHERE EXISTS (SELECT*FROM S-C.SCWHERE SNO=STUDENT.SNO AND CNO=1);注意：带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。5 实验结果及总结1.SELECT SNO, SNAME FROM

20、这些的大小写随意，但是S-C.STUDENT必须是指定大小写，要是改变大小写则可以用S-C.student 加分号，引号会自动变红色. 在SQL语句中用 /* 注释内容 */注释。2. 查询结果中有许多重复行，若想去掉重复，则指定DISTINCT。若没有指定，则默认为all。3.（百分号）代表任意长度（长度可以为0）的字符串。_（下划线）代表任意单个字符4.desc是降序，ASC是升序，系统默认升序。 5. 求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。6.用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。7. 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。