SQL语句 第5章 子查询和集合操作

5.1  子查询

5.1.1  单行子查询

1．在WHERE 子句中使用子查询
例5.1_1

工资低于平均工资的教师

SELECT * FROM Teachers
  WHERE wage <
  (SELECT AVG(wage) 

   FROM Teachers);

 
例5.1_2

和王天仪同专业的学生

SELECT * FROM Students 
  WHERE specialty =
  (SELECT specialty FROM Students 
     WHERE name = '王天仪');

 
 

2．在HAVING子句中使用子查询
例5.1_4

部门平均工资高于部门最低工资的部门号和平均工资

SELECT department_id, AVG(wage) AS 平均工资

 FROM Teachers 
  GROUP BY department_id 
    HAVING AVG(wage) >
    (SELECT MIN(AVG(wage))
       FROM Teachers 
       GROUP BY department_id);

 

3．在FROM子句(Inline Views)中使用子查询
例5.1_5

计算机专业的男学生

SELECT * FROM (SELECT * FROM Students WHERE sex ='男')
  WHERE specialty = '计算机';

 

 

 

5.1.2  多行子查询

 

1．在多行子查询使用IN/NOT IN操作符
例5.1_6

查询王某的学号和姓名

SELECT student_id, name FROM Students 
  WHERE student_id IN
    (SELECT student_id 

      FROM Students 
       WHERE name LIKE '王%');

 

例5.1_7

未被学生选的课程

SELECT course_id, course_name

 FROM Courses
  WHERE course_id NOT IN
    (SELECT course_id
       FROM Students_grade);

 

2．在多行子查询使用ANY操作符
例5.1_8

平均工资最低的部门里的教师信息

SELECT * FROM Teachers
  WHERE wage < ANY
    (SELECT AVG(wage) 

     FROM Teachers

    GROUP BY department_id);

 

3．在多行子查询使用ALL操作符
例5.1_9

平均工资最高的部门里的教师信息

SELECT * FROM Teachers
  WHERE wage > ALL
    (SELECT AVG(wage)
       FROM Teachers GROUP BY department_id);

 

 

5.1.3  多列子查询

例5.1_10

和王天仪同专业同生日的学生信息

SELECT * FROM Students
  WHERE (specialty, dob)=
    (SELECT specialty, dob 
       FROM Students WHERE name='王天仪');

 

例5.1_11

部门里工资最低的教师信息

SELECT * FROM Teachers
  WHERE (department_id, wage) IN
    (SELECT department_id, MIN(wage)
       FROM Teachers 

       GROUP BY department_id);

 

 

5.1.4  相关子查询

 

例5.1_12

工资高于所在部门的平均工资的教师信息

SELECT * FROM teachers t1
  WHERE wage >
    (SELECT AVG(wage) FROM teachers t2
       WHERE t2.department_id = t1.department_id);

 

1．在相关子查询中使用EXISTS/NOT EXISTS
例5.1_13 

以被学生选的课程号和课程名

SELECT course_id, course_name 

FROM courses c
  WHERE EXISTS
    (SELECT 2 FROM students_grade sg
       WHERE sg.course_id = c.course_id);

 

3．IN/NOT IN
例5.1_15

有老师的部门

SELECT department_id, department_name

 FROM departments
  WHERE department_id IN
    (SELECT department_id FROM teachers);

 

 

5.2  集合操作(UNION UNION ALL INTERSECT MINUS)

5.2.1  分别使用集合操作符

1．使用集合操作符UNION ALL
例5.2_1

主修课并副修课，可重复

SELECT course_id, course_name, credit_hour
  FROM Courses
UNION ALL
SELECT minor_id, minor_name, credit_hour
  FROM Minors;

 

 

2．使用集合操作符UNION

例5.2_6 学生()*专业 男生

计算机专业学生并男学生

SELECT * FROM Students WHERE specialty='计算机'
UNION
SELECT * FROM Students WHERE sex='男';

 

3．使用集合操作符INTERSECT

例5.2_8学生()*专业 男生

SELECT *
  FROM students WHERE specialty='计算机'
INTERSECT
SELECT *
  FROM students WHERE sex='男';

 

4．使用集合操作符MINUS

例5.2_10学生()*专业 男生

计算机专业的女学生

SELECT *
  FROM Students WHERE specialty='计算机'
MINUS
SELECT *
  FROM Students WHERE sex='男';

 

 同类型不同宽度可自动转换，不同类型可先转成字符串型再集合操作