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='男';
同类型不同宽度可自动转换,不同类型可先转成字符串型再集合操作