一、MySQL多表查询

（一）子查询

  1. # 子查询介绍及用法
  2. 1) 子查询是将一个查询语句嵌套在另一个查询语句中。
  3. 2) 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
  4. 3) 子查询中可以包含：in、not in、any、all、exists 和 not exists 等关键字。
  5. 4) 还可以包含比较运算符：= 、 != 、> 、 < 等。

1.带in关键字的子查询

  1. # 查询平均年龄在25岁以上的部门
  2. mysql> select id,name from department
  3.     where id in
  4.     (select dep_id from employee group by dep_id having avg(age) > 25);
  6. # 查看技术部员工姓名
  7. mysql> select name from employee 
  8.     where dep_id in
  9.     (select id from department where name='技术');
  11. # 查看不足1人的部门名(子查询得到的是有人的部门id)
  12. mysql> select name from department where id not in (select distinct dep_id from employee);

注意：not in 无法处理null的值，即子查询中如果存在null的值，not in 将无法处理，如下：

  1. mysql> insert emp(name,sex,age) values("xxx","male",66);
  2. Query OK, 1 row affected (0.01 sec)
  4. mysql> select * from emp;
  5. +----+------------+--------+------+--------+
  6. | id | name       | sex    | age  | dep_id |
  7. +----+------------+--------+------+--------+
  8. |  1 | egon       | male   |   18 |    200 |
  9. |  2 | alex       | female |   48 |    201 |
  10. |  3 | wupeiqi    | male   |   38 |    201 |
  11. |  4 | yuanhao    | female |   28 |    202 |
  12. |  5 | liwenzhou  | male   |   18 |    200 |
  13. |  6 | jingliyang | female |   18 |    204 |
  14. |  7 | xxx        | male   |   66 |   NULL |
  15. +----+------------+--------+------+--------+
  16. 7 rows in set (0.00 sec)
  18. mysql> select * from dep;
  19. +------+--------------+
  20. | id   | name         |
  21. +------+--------------+
  22. |  200 | 技术         |
  23. |  201 | 人力资源     |
  24. |  202 | 销售         |
  25. |  203 | 运营         |
  26. +------+--------------+
  27. 4 rows in set (0.00 sec)
  29. # 子查询中存在null
  30. mysql> select * from dep where id not in (select distinct dep_id from emp);
  31. Empty set (0.00 sec)
  33. # 解决方案如下
  34. mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
  35. +------+--------+
  36. | id   | name   |
  37. +------+--------+
  38. |  203 | 运营   |
  39. +------+--------+
  40. 1 row in set (0.00 sec)
  42. mysql>

2.带any关键字的子查询

  1. # 在SQL中。 any 和 some 是同义词，some 的用法、功能和 any 一摸一样。 
  3. # any 和 in 运算符不同之处1；
  4. any 必须和其他的比较运算符共同使用，而 any 必须将比较运算符放在 any 关键字之前，所以比较的值需要匹配子查询中的任意一个值，这就是 any 在英文中所表示的意义。
  5. mysql> select * from employee where salary = any(
  6.     select max(salary) from employee group by depart_id);
  8. mysql> select * from employee where salary in (
  9.     select max(salary) from employee group by depart_id);
  11. mysql> select * from employee where !(salary = any(
  12.     select max(salary) from employee group by depart_id));
  14. mysql> select * from employee where salary not in (
  15.     select max(salary) from employee group by depart_id);
  17. #### 结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符  ####
  19. # any 和 in 运算符不同之处2：
  20. ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
  22. SELECT
  23. *
  24. FROM
  25. T_Book
  26. WHERE
  27. FYearPublished < ANY (2001, 2003, 2005)

3.带all关键字的子查询

  1. # all 同 any 类似，只不过all表示的是所有， any 表示的是任一
  2. 查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
  3. mysql> select * from employee where salary > all (
  4.     select avg(salary) from employee group by depart_id);
  6. 查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
  7. mysql> select * from employee where salary < all (
  8.     select avg(salary) from employee group by depart_id);
  10. 查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
  11. mysql> select * from employee where salary < any (
  12.     select avg(salary) from employee group by depart_id);
  14. 查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
  15. mysql> select * from employee where salary > any (
  16.     select avg(salary) from employee group by depart_id);

4.带比较运算符的子查询

  1. # 比较运算符：=、!=、>、>=、<、<=、<>
  2. # 查询大于所有人平均年龄的员工名与年龄
  3. mysql> select name,age from employee where age > (select avg(age) from employee);
  4. +---------+------+
  5. | name    | age  |
  6. +---------+------+
  7. | alex    |   48 |
  8. | wupeiqi |   38 |
  9. +---------+------+
  11. # 查询大于部门内平均年龄的员工名、年龄
  12. mysql> select name,age from employee
  13.     inner join 
  14.     (select dep_id,avg(age) avg_age from employee group by dep_id) t2
  15.     on employee.dep_id = t2.dep_id
  16.     where employee.age > t2.avg_age;
  17. +------+------+
  18. | name | age  |
  19. +------+------+
  20. | alex |   48 |
  21. +------+------+

5.带exists关键字的子查询

  1.     exists关键字表示存在，在使用exists关键字时，内层循环语句不返回查询的记录。
  3.     而是返回一个真假值。True或False。
  5.     当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。
  1. # department表中存在dept_id=200，Ture
  2. mysql> select * from employee 
  3.     where exists
  4.     (select id from department where id = 200);
  5. +----+------------+--------+------+--------+
  6. | id | name       | sex    | age  | dep_id |
  7. +----+------------+--------+------+--------+
  8. |  1 | egon       | male   |   18 |    200 |
  9. |  2 | alex       | female |   48 |    201 |
  10. |  3 | wupeiqi    | male   |   38 |    201 |
  11. |  4 | yuanhao    | female |   28 |    202 |
  12. |  5 | liwenzhou  | male   |   18 |    200 |
  13. |  6 | jingliyang | female |   18 |    204 |
  14. +----+------------+--------+------+--------+
  16. # department表中存在dept_id=204，False
  17. mysql> select * from employee
  18.     where exists 
  19.     (select id from department where id = 204);
  20.     Empty set (0.00 sec)

6.in与exists

  1. ！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
  2. ==============================exists==============================
  3. # exists：
  4. exists 后面一般都是子查询，后面的子查询被称作相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回True。否则返回false，exists 是不返回列表的值的，exists 只在乎括号里的数据能不能查出来，是否存在这样的记录。
  6. # 示例：
  7. 查询出那些班级里有学生的班级
  8. select * from class where exists (select * from stu where stu.cid=class.id)；
  10. # exists的执行原理为：
  11. 1、依次执行外部查询：即select * from class 
  12. 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
  13. 3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
  15. ==============================  in  ==============================
  16. # in：
  17. in 后面跟的都是子查询，in()后面的子查询 是返回结果集里的
  19. # 示例：
  20. 查询和所有女生年龄相同的男生
  21. select * from stu where sex='男' and age in(select age from stu where sex='女')
  23. # in 的执行原理为：
  24. 1、 in()的执行次序和 exists()不一样， in()的子查询会先产生结果集，
  25. 2、 然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出，反之则不输出。

7.not in与 not exists

  1. !!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!
  2. ==============================  not in  ==============================
  3. not in()子查询的执行顺序是：
  4. 为了证明 not in 成立，即找不到，需要一条一条地查询，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
  6. ==============================not exists==============================
  7. not exists :
  8. 如果主查询表中记录少，子查询表中地记录多，并有索引。就使用 not exists.
  10. # 例如:查询那些班级中没有学生的班级
  11. select * from class where not exists
  12.     (select * from student where student.cid = class.cid);
  14. not exists的执行顺序是：
  15. 在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

8.应用示例

  1. # 准备数据
  2. create database db13;
  4. use db13
  6. create table student(
  7.     id int primary key auto_increment,
  8.     name varchar(16)
  9. );
  11. create table course(
  12.     id int primary key auto_increment,
  13.     name varchar(16),
  14.     comment varchar(20)
  15. );
  17. create table student2course(
  18.     id int primary key auto_increment,
  19.     sid int,
  20.     cid int,
  21.     foreign key(sid) references student(id),
  22.     foreign key(cid) references course(id)
  23. );
  26. insert into student(name) values
  27. ("egon"),
  28. ("lili"),
  29. ("jack"),
  30. ("tom");
  32. insert into course(name,comment) values
  33. ("数据库","数据仓库"),
  34. ("数学","根本学不会"),
  35. ("英语","鸟语花香");
  38. insert into student2course(sid,cid) values
  39. (1,1),
  40. (1,2),
  41. (1,3),
  42. (2,1),
  43. (2,2),
  44. (3,2);

代码示例：

  1. # 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）
  2. select * from student s where not exists
  3.     (select * from course c where not exists
  4.         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
  7. select s.name from student as s
  8. inner join student2course as sc
  9. on s.id=sc.sid
  10. group by s.name 
  11. having count(sc.id) = (select count(id) from course);
  13. # 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
  14. select * from student s where exists
  15.     (select * from course c where not exists
  16.         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
  18. # 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
  19. select * from student s where not exists
  20.     (select * from course c where exists
  21.         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
  23. # 4、查询至少选修了一门课程的学生。
  24. select * from student s where exists
  25.     (select * from course c where exists
  26.         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));