mysql多表查询

一 多表连接查询

#重点：外连接语法

select 字段列表
	from 表1 inner|left|right join 表2
	on 表1.字段 = 表2.字段；

1 交差连接：不适用任何匹配条件，生成笛卡尔积。

mysql> select * from emp,dep;
+----+------------+--------+------+--------+------+--------+
| id | name       | sex    | age  | dep_id | id   | name   |
+----+------------+--------+------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |  200 | jishu  |
|  1 | egon       | male   |   18 |    200 |  201 | ziyuan |
|  1 | egon       | male   |   18 |    200 |  202 | sell   |
|  1 | egon       | male   |   18 |    200 |  203 | yunyin |
|  2 | alex       | female |   48 |    201 |  200 | jishu  |
|  2 | alex       | female |   48 |    201 |  201 | ziyuan |
|  2 | alex       | female |   48 |    201 |  202 | sell   |
|  2 | alex       | female |   48 |    201 |  203 | yunyin |
|  3 | wupeiqi    | male   |   38 |    201 |  200 | jishu  |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | ziyuan |
|  3 | wupeiqi    | male   |   38 |    201 |  202 | sell   |
|  3 | wupeiqi    | male   |   38 |    201 |  203 | yunyin |
|  4 | yuanhao    | female |   28 |    202 |  200 | jishu  |
|  4 | yuanhao    | female |   28 |    202 |  201 | ziyuan |
|  4 | yuanhao    | female |   28 |    202 |  202 | sell   |
|  4 | yuanhao    | female |   28 |    202 |  203 | yunyin |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | jishu  |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | ziyuan |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | sell   |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | yunyin |
|  6 | jingliyang | female |   18 |    204 |  200 | jishu  |
|  6 | jingliyang | female |   18 |    204 |  201 | ziyuan |
|  6 | jingliyang | female |   18 |    204 |  202 | sell   |
|  6 | jingliyang | female |   18 |    204 |  203 | yunyin |
+----+------------+--------+------+--------+------+--------+
24 rows in set (0.00 sec)

如图没有约束条件，则默认以多对多的形式出现，即左右两边重复对应。

2 内连接：只连接匹配的行

#找到两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确结果
#dep没有204这个部门，emp对应的部门没有203yunyin这个部门(这个部门没有员工)
#所以最后的虚拟表中都没有相关信息
mysql> select emp.* , dep.name from emp inner join dep on emp.dep_id=dep.id;
+----+-----------+--------+------+--------+--------+
| id | name      | sex    | age  | dep_id | name   |
+----+-----------+--------+------+--------+--------+
|  1 | egon      | male   |   18 |    200 | jishu  |
|  2 | alex      | female |   48 |    201 | ziyuan |
|  3 | wupeiqi   | male   |   38 |    201 | ziyuan |
|  4 | yuanhao   | female |   28 |    202 | sell   |
|  5 | liwenzhou | male   |   18 |    200 | jishu  |
+----+-----------+--------+------+--------+--------+

3 外连接之左连接：优先显示左表全部记录

#以左边为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的接触上增加了左边有右边没有的结果
mysql> select emp.* , dep.name from emp left join dep on emp.dep_id=dep.id;
+----+------------+--------+------+--------+--------+
| id | name       | sex    | age  | dep_id | name   |
+----+------------+--------+------+--------+--------+
|  1 | egon       | male   |   18 |    200 | jishu  |
|  5 | liwenzhou  | male   |   18 |    200 | jishu  |
|  2 | alex       | female |   48 |    201 | ziyuan |
|  3 | wupeiqi    | male   |   38 |    201 | ziyuan |
|  4 | yuanhao    | female |   28 |    202 | sell   |
|  6 | jingliyang | female |   18 |    204 | NULL   |
+----+------------+--------+------+--------+--------+

4 外连接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门的信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
+------+-----------+--------+------+--------+--------+
| id   | name      | sex    | age  | dep_id | name   |
+------+-----------+--------+------+--------+--------+
|    1 | egon      | male   |   18 |    200 | jishu  |
|    2 | alex      | female |   48 |    201 | ziyuan |
|    3 | wupeiqi   | male   |   38 |    201 | ziyuan |
|    4 | yuanhao   | female |   28 |    202 | sell   |
|    5 | liwenzhou | male   |   18 |    200 | jishu  |
| NULL | NULL      | NULL   | NULL |   NULL | yunyin |
+------+-----------+--------+------+--------+--------+

5 全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有做没有没有的结果
#注意：mysql不支持全外连接 full join
#强调：mysql可以使用此种方式间接实现全外连接
mysql> select emp.* , dep.name from emp left join dep on emp.dep_id=dep.id
    -> union
    -> select emp.* , dep.name from emp right join dep on emp.dep_id=dep.id;
+------+------------+--------+------+--------+--------+
| id   | name       | sex    | age  | dep_id | name   |
+------+------------+--------+------+--------+--------+
|    1 | egon       | male   |   18 |    200 | jishu  |
|    5 | liwenzhou  | male   |   18 |    200 | jishu  |
|    2 | alex       | female |   48 |    201 | ziyuan |
|    3 | wupeiqi    | male   |   38 |    201 | ziyuan |
|    4 | yuanhao    | female |   28 |    202 | sell   |
|    6 | jingliyang | female |   18 |    204 | NULL   |
| NULL | NULL       | NULL   | NULL |   NULL | yunyin |
+------+------------+--------+------+--------+--------+

#注意 union与union all的区别：union会去掉相同的记录

二 符合条件的连接查询

#实例1：以内连接的方式查询emp和dep表，并且emp表中的age字段必须大于25，即找出年龄大于25以及员工所在部门。
#以员工为主
select emp.name,dep.name from emp inner join dep on emp.dep_id=dep.id where emp.age>=25;

#示例2：以内连接的方式查询emp和dep表，并且以age字段的升序方式显示
select emp.*,dep.name from emp inner join dep on emp.dep_id=dep.id order by age asc;

三 子查询

(1):子查询是将一个查询语句嵌套在另一个查询语句中。

(2):内层查询语句的查询结果，可以为外层查询语句提供查询条件

(3):子查询中可以包含：in、not in、any、all、exists和 not exists等关键字

(4):还可以包含比较运算符：=、!=、>、<等

1 带in关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from dep 
where id in 
(select dep_id from emp group by dep_id having avg(age)>25);

#查看技术部员工姓名
select name from emp where
dep_id in
(select id from dep where name='jishu');

#查看员工数小于等于一人的部门名
select id,name from dep
where id in 
(select dep_id from emp group by dep_id having count(id)<=1);

注意：

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)

mysql>

2 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name ,age from emp where age>(select avg(age) from emp);
+---------+------+
| name    | age  |
+---------+------+
| alex    |   48 |
| wupeiqi |   38 |
+---------+------+

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age,t1.dep_id from emp as t1 
inner join 
(select dep_id,avg(age) as avg_age from emp group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 
+------+------+--------+
| name | age  | dep_id |
+------+------+--------+
| alex |   48 |    201 |
+------+------+--------+

select name,age,dep_id from emp group by dep_id having age>avg(age);

3 带ANY关键字的子查询

# any 和 in运算符不同之处1
any 必须和其他的比较运算符共同使用，而且any必须将比较运算符放在 any 关键字之前，所比较的值需要匹配子查询的任意一个值。
例如：使用 in 和 使用 any 运算符得到的结果是一致的
SELECT * FROM emp WHERE salary = ANY
(SELECT MAX(salary) FROM emp GROUP BY post);

SELECT * FROM emp WHERE salary IN
(SELECT MAX(salary) FROM emp GROUP BY post);

结论：也就是说“=any”等价于 in 运算符，而"<>any"则等价于 not in 运算符

# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
SELECT * FROM emp WHERE salary < ANY(10000,12000,20000);

4 带ALL关键字的子查询

# all 同 any 类似，只不过 all 表示的是所有， any 表示任一
查询出那些薪资比所有部门的平均薪资都高的员工（比all语句中最高薪资要高即可）
SELECT name FROM emp WHERE salary> ALL
(SELECT AVG(salary) FROM emp GROUP BY post);


同样将all换上any，则表示比任何一个员工平均薪资高即可满足条件(比any语句中的最低薪资高即可)
SELECT name FROM emp WHERE salary> ANY
(SELECT AVG(salary) FROM emp GROUP BY post);

查询出那些薪资比所有部门的平均薪资都低的员工（比all语句中最低薪资要低即可）
SELECT name FROM emp WHERE salary< ALL
(SELECT AVG(salary) FROM emp GROUP BY post);

同样将all换上any，则表示比任何一个员工平均薪资低即可满足条件(比any语句中的最高薪资低即可)
SELECT name FROM emp WHERE salary< ANY
(SELECT AVG(salary) FROM emp GROUP BY post);

5 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#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)

5.1 in与exists

当 in 和 exists 在查询效率上比较时，in 查询的效率快于 exists 的查询效率

#exists
exists后面一般都是子查询，后面的子查询被称作相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，否则返回false，exists是不返回列表的值的，exists只在乎括号内的数据能不能查找出来，是否存在这样的记录

#例
查询出那些班级里有学生的班级（只要学生列表中有一个学生存在该班级的id即可）
SELECT * FROM class where EXISTS
(SELECT * FROM student where class.cid=class_id);

#exists的执行原理：
1.依次执行外部查询：即select * from class
2.然后为外部查询返回每一行分别执行一次子查询
通俗的讲，在外部class表中拿一个字段下的记录class.cid到内部student表中与class_id下的每一个记录比对，比对成功则返回结果true,对比失败返回false（就是class.cid配对了student表class_id下的所有的记录都失败了）
3.子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录


# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
# 例
查询和所有女生年龄相同的男生
select * from student where sex='男' and age in(select age from stu where sex='女')

# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

相比之下exists每一次字段的访问都要执行一遍exists语句中的指令，
而in则是先执行完in里面的指令集并产生硬性的结果，然后再进行主查询的访问。

erists:
主查询执行调用一个字段下的记录去访问======>运行exists中的语句相应字段下的记录与主查询的记录进行配比直到成功或是指令运行完毕才结束=========>返回结果给子查询，判断该字段TRUE或FALSE(即该记录会不会出现在表中)

in:
先执行in中的语句产生一个结果集======>运行主查询，主查询调用与结果集相对应字段下的一个记录去与结果集进行配比=========>成功则返回，那该记录成功写入字段下，失败则相反。

5.2 not in 与 not exists

not exists 查询的效率远远高于not in查询效率

#not in
为了证明not in成立，即找不动，需要一条一条的查询表，符合要求才返回子查询的结果，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。


#not exists
如果主查询表中的记录少，子查询中的记录多，并有索引。
例如：查询那些班级中没有学生的班级
SELECT * FROM class where not EXISTS
(SELECT * FROM student where class.cid=class_id);

not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
只要有一个匹对失败，则条件成立。

前戏

create database dbtest;

use dbtset;

create table student(
    id int primary key auto_increment,
    name varchar(16)
);

create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);

create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);


insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");

insert into course(name,comment) values
("数据库","数据仓库"),
("数学","根本学不会"),
("英语","鸟语花香");


insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);

示例

# 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）

#不存在一门都没有选修的即是选修了所有门
SELECT * FROM student WHERE  not EXISTS
(SELECT * FROM course WHERE NOT EXISTS  #结果为一门都没有选修到返回上层并被取反即是都选修到
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 
#底层结果为真，上层有一行为假，上层每一行为假，则上上层有一行为真

# 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
SELECT * FROM student WHERE   EXISTS
(SELECT * FROM course WHERE NOT EXISTS  
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 

# 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
	SELECT * FROM student WHERE   not EXISTS
	(SELECT * FROM course WHERE  EXISTS  
	(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 


# 4、查询至少选修了一门课程的学生。
SELECT * FROM student WHERE   EXISTS
(SELECT * FROM course WHERE  EXISTS  
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 


#注意：
exists(): 有一个匹对成功,条件就成立即主查询的字段在子查询中有相对应的
not exists():有一个匹对失败,条件就成立即主查询的字段在子查询没有相对应的