实 验  综合查询

一、实验目的:

        熟练掌握数据表的连接查询、嵌套查询和集合查询，为后续学习作准备。

二、实验属性（验证性）

        熟练掌握SQL管理控制台的使用;

        掌握表的复杂查询操作。

三、 实验仪器环境与要求

         PC机，MySQL SQLyog。

四、  实验要求

1．复习教材第三章，熟悉SQL语句；

2．掌握多表查询语句的一般格式；

3．掌握多表无条件、有条件查询及查询结果排序与分组；

4．掌握多表连接、嵌套和集合查询语句的一般格式及各种使用方法。

五、实验原理

        SQL语言基本应用。

六、 实验步骤：

1、启动MySQL SQLyog；

2、附加数据库；

（1）表Student:

（2）表Course:

（3）表SC:

3、 验证如下例子：

1 不同表之间的连接查询

1. 查询每个学生及其选修课程的情况包括没选课的同学。

   SELECT  Student.*, SC.*
   
   FROM     Student, SC
   
   WHERE  Student.Sno = SC.Sno;

    

2. 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)并按照学号升序，课程号降序排列。

   SELECT  Student.Sno, Student.Sname, Course.Cname, SC.Grade
   
   FROM     Student, SC, Course
   
   WHERE  Student.Sno = SC.Sno AND Sc.Cno = Course.Cno
   
   ORDER BY Sc.Sno, Sc.Cno DESC;

    

2 自身连接

1. 查询每一门课的先修课的课程名和学分。

   SELECT  FIRST.Cno, FIRST.Cpno, SECOND.Cname, SECOND.Ccredit
   
   FROM  Course  FIRST, Course  SECOND
   
   WHERE FIRST.Cpno = SECOND.Cno;

    

2. 查询与”张林林”年龄一样大的计算机系的同学姓名。

   SELECT  SECOND.Sname
   
   FROM  Student  FIRST, Student  SECOND
   
   WHERE FIRST.Sage = SECOND.Sage
   
         AND First.Sname = ‘张林林‘
   
         AND SECOND.Sdept = ‘计算机系‘;

    

3 基本的嵌套查询

1. 查询王位同学没修的课程的课程号。

   SELECT Cno
   
   FROM Course
   
   WHERE Cno NOT IN
   
          ( SELECT Cno
   
            FROM Student, SC
   
            WHERE Student.Sname = ‘王位‘ AND Student.Sno = SC.Sno);

    

2. 求选修4号课程的学生的年龄，姓名。

   SELECT Sage, Sname
   
   FROM Student
   
   WHERE Sno IN
   
          ( SELECT Sno
   
            FROM Course, SC
   
            WHERE Course.Cno = ‘4‘ AND Course.Cno = SC.Cno);

    

3. 求年龄大于所有女同学年龄的男学生的姓名和年龄。

   SELECT Sname,Sage
   
   FROM Student
   
   WHERE Sage>ALL
   
           (SELECT Sage
   
            FROM Student
   
            WHERE Ssex=‘女‘)
         AND Ssex=‘男‘;

    

4 带谓词ANY或ALL的嵌套查询

1. 查询非数学系的不超过数学系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。

   SELECT Sname,Sage
   
   FROM Student
   
   WHERE Sage<=ALL
   
           (SELECT Sage
   
            FROM Student
   
            WHERE Sdept=‘数学系‘)
   
         AND Sdept<>‘数学系‘ ;

    

5 带谓词EXISTS的嵌套查询

1. 查询所有选修了编号为“1”课程的学生姓名(Sname)和所在系(Sdept)。

   SELECT Sname,Sdept
   
   FROM Student,Sc
   
   WHERE Sc.Cno=‘1‘ AND Student.Sno=Sc.Sno;

    

2. 查询选修了所有课程的学生姓名(Sname)和所在系。

   SELECT Sname,Sdept
   
   FROM Student
   
   WHERE NOT EXISTS
   
             (SELECT*
   
              FROM Course
   
              WHERE NOT EXISTS
   
               (SELECT*
   
                FROM Sc
   
                WHERE Sno=Student.Sno AND Cno=Course.Cno));

    该数据库里没有学生选修了所有课程。

6 集合查询

1. 查询计算机科学系的学生或年龄不大于20岁的学生信息。

SELECT *

FROM Student

WHERE Sdept=‘计算机科学系‘

UNION

SELECT *

FROM Student

WHERE Sage<=20;

2. 查询数学系中年龄不大于20岁的学生。

SELECT*

FROM Student

WHERE Sdept =‘数学系‘ AND Sage<=20;

3. 查询数学系的学生与年龄不大于20岁的学生的差集。

SELECT *

FROM Student

WHERE Sdept=‘数学系‘

EXCEPT

SELECT *

FROM Student

WHERE Sage<=20;

由于MySQL 不支持 EXCEPT 运算符，所以在sqlyog中报错

故可有如下更改，有同样的效果

SELECT *

FROM Student

WHERE Sdept=‘数学系‘ AND Sage>20;

 以上均已试验且查询成功，欢迎各位大佬留言指正！

MySQL 综合查询部分习题