多表查询
将两张表一起查询，得出笛卡尔积，通过on来增加链表的条件，达到你想要的效果。
内链接
select * from employee inner join department on employee.dep_id=department.id;
左链接
select * from employee left join department on employee.dep_id=department.id;
右链接
select * from employee right join department on employee.dep_id=department.id;
全外连接
SELECT
*
FROM
employee
LEFT JOIN department ON employee.dep_id = department.id
UNION
SELECT
*
FROM
employee
RIGHT JOIN department ON employee.dep_id = department.id;
子查询
带in关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理，如下
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | xxx | male | 19 | NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
# 子查询中存在null
mysql> select * from dep where id not in (select distinct dep_id from emp);
Empty set (0.00 sec)
# 解决方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+
1 row in set (0.00 sec)
！！！注意not in
带any关键字的子查询
注意：括号里的必须时子查询语句。
# =any等价与in
# </>等价于not in
# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义
例如：使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);
select * from employee where salary in (
select max(salary) from employee group by depart_id);
结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符
# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005)
带all关键字的子查询
注意：括号里的必须时子查询语句。
# all同any类似，只不过all表示的是所有，any表示任一
erase
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工select * from employee where salary < any ( select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);
带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
带exists关键字的子查询
exists关键字表示存在，在使用exists关键字的时候，内层查询语句不返回查询的记录。而是返回一个真假值，True或是False，当返回是True时，查询，反之则不查询。
#department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
练习题
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
cname,
tname
FROM
course
INNER JOIN teacher
WHERE
course.teacher_id = teacher.tid
2、查询学生表中男女生各有多少人
SELECT
gender,
COUNT( sid )
FROM
student
GROUP BY
gender
3、查询物理成绩等于100的学生的姓名
SELECT
sname
FROM
student
INNER JOIN score ON student.sid = score.student_id
AND score.num = 100
INNER JOIN course ON course.cid = score.course_id
WHERE
cid =2
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) avg_num FROM score GROUP BY student_id HAVING avg_num > 80 ) AS t1 ON t1.student_id = student.sid;
5、查询所有学生的学号，姓名，选课数，总成绩
SELECT
student.sid,
t1.sname,
t1.选课数,
t1.总成绩
FROM
student
INNER JOIN (
SELECT
sname,
count(course_id) 选课数,
sum(num) 总成绩
FROM
student
INNER JOIN score ON student.sid = score.student_id
GROUP BY
sname
) AS t1 ON student.sname = t1.sname;
6、 查询姓李老师的个数
select count(tname) 个数 from teacher where tname regexp "^李";
7、 查询没有报李平老师课的学生姓名
SELECT
*
FROM
student
WHERE
sid NOT IN ( SELECT sid FROM score INNER JOIN ( SELECT * FROM teacher INNER JOIN course ON tid = teacher_id ) AS t1 ON cid = 4 );
8、 查询物理课程比生物课程高的学生的学号
SELECT
t1.student_id
FROM
( SELECT * FROM score WHERE course_id = 1 ) AS t1
INNER JOIN ( SELECT * FROM score WHERE course_id = 2 ) AS t2 ON t1.student_id = t2.student_id
WHERE
t1.num < t2.num;
9、 查询没有同时选修物理课程和体育课程的学生姓名
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
t1.student_id
FROM
( SELECT * FROM score WHERE course_id = 2 ) AS t1
INNER JOIN ( SELECT * FROM score WHERE course_id = 3 ) AS t2 ON t1.student_id = t2.student_id
);
10、查询挂科超过两门(包括两门)的学生姓名和班级
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
11 、查询选修了所有课程的学生姓名
SELECT
sname,
caption
FROM
(
SELECT
*
FROM
student
INNER JOIN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 ) AS t1 ON student.sid = t1.student_id
) AS t2
INNER JOIN class ON t2.class_id = class.cid;
12、查询李平老师教的课程的所有成绩记录
SELECT
student_id,
cname,
num
FROM
score
INNER JOIN ( SELECT * FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE tid = 2 ) AS t1 ON score.course_id = t1.cid;
13、查询全部学生都选修了的课程号和课程名
SELECT
sname,
cname,
course_id
FROM
student
INNER JOIN ( SELECT cname, course_id, student_id FROM course INNER JOIN score ON course.cid = score.course_id ) AS t2 ON t2.student_id = student.sid;
14、查询每门课程被选修的次数
SELECT
cname,次数
FROM
course
INNER JOIN ( SELECT course_id, COUNT( course_id ) 次数 FROM score GROUP BY course_id ) AS t1 ON t1.course_id = course.cid
15、查询只选修了一门课程的学生姓名和学号
SELECT
sname,
sid
FROM
student
INNER JOIN ( SELECT student_id FROM score GROUP BY student_id HAVING COUNT( course_id )= 1 ) AS t1 ON student.sid = t1.student_id
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT
*
FROM
score
ORDER BY
num DESC
17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
sname,
avg_num
FROM
student
INNER JOIN ( SELECT student_id, AVG( num ) avg_num FROM score GROUP BY student_id HAVING AVG( num ) > 85 ) AS t1 ON student.sid = t1.student_id
18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname,
num
FROM
student
INNER JOIN ( SELECT * FROM score WHERE course_id = 1 AND num < 60 ) AS t1 ON student.sid = t1.student_id
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
sname,
avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg( num ) avg_num
FROM
score
INNER JOIN ( SELECT * FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE tid = 2 ) AS t1 ON score.course_id = t1.cid
GROUP BY
student_id
) AS t2 ON student.sid = t2.student_id
ORDER BY
avg_num DESC
LIMIT 1;
20、查询每门课程成绩最好的前两名学生姓名
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num;