mysql-多表查询

准备工作：准备两张表，部门表（department）、员工表（employee）

mysql> create database db2;

Query OK, 1 row affected (0.00 sec)

mysql> use db2;

Database changed

创建表

mysql> create table department(id int,name varchar(20));

Query OK, 0 rows affected (0.17 sec)

mysql> create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);

Query OK, 0 rows affected (0.16 sec)

插入数据

mysql> insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');

Query OK, 4 rows affected (0.06 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into employee(name,sex,age,dep_id) values('zhangsan','male',18,200),('lisi','female',48,201),('wangwu','male',38,201),('zhuliu','female',28,202),('chenqi','male',18,200),('wangba','female',18,204);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

查看表结构和数据

mysql> desc department;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> select * from department;

+------+--------------+

| id   | name         |

+------+--------------+

|  200 | 技术         |

|  201 | 人力资源     |

|  202 | 销售         |

|  203 | 运营         |

+------+--------------+

4 rows in set (0.00 sec)

mysql> desc employee;

+--------+-----------------------+------+-----+---------+----------------+

| Field  | Type                  | Null | Key | Default | Extra          |

+--------+-----------------------+------+-----+---------+----------------+

| id     | int(11)               | NO   | PRI | NULL    | auto_increment |

| name   | varchar(20)           | YES  |     | NULL    |                |

| sex    | enum('male','female') | NO   |     | male    |                |

| age    | int(11)               | YES  |     | NULL    |                |

| dep_id | int(11)               | YES  |     | NULL    |                |

+--------+-----------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

mysql> select * from  employee;

+----+----------+--------+------+--------+

| id | name     | sex    | age  | dep_id |

+----+----------+--------+------+--------+

|  1 | zhangsan | male   |   18 |    200 |

|  2 | lisi     | female |   48 |    201 |

|  3 | wangwu   | male   |   38 |    201 |

|  4 | zhuliu   | female |   28 |    202 |

|  5 | chenqi   | male   |   18 |    200 |

|  6 | wangba   | female |   18 |    204 |

+----+----------+--------+------+--------+

6 rows in set (0.00 sec)

ps:观察两张表，发现department表中id=203部门在employee中没有对应的员工，发现employee中id=6的员工在department表中没有对应关系

多表连接查询

两张表的准备工作已完成，比如现在我要查询的员工信息以及该员工所在的部门。从该题中，我们看出既要查员工又要查该员工的部门，肯定要将两张表进行连接查询，多表连接查询

重点：外链接语法

语法：

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

（1）先看第一种情况交叉连接：不适用任何匹配条件。生成笛卡尔积（关于笛卡尔积的含义，大家百度自行补脑）

mysql> select * from employee,department;

+----+----------+--------+------+--------+------+--------------+

| id | name     | sex    | age  | dep_id | id   | name         |

+----+----------+--------+------+--------+------+--------------+

|  1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|  1 | zhangsan | male   |   18 |    200 |  201 | 人力资源     |

|  1 | zhangsan | male   |   18 |    200 |  202 | 销售         |

|  1 | zhangsan | male   |   18 |    200 |  203 | 运营         |

|  2 | lisi     | female |   48 |    201 |  200 | 技术         |

|  2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|  2 | lisi     | female |   48 |    201 |  202 | 销售         |

|  2 | lisi     | female |   48 |    201 |  203 | 运营         |

|  3 | wangwu   | male   |   38 |    201 |  200 | 技术         |

|  3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|  3 | wangwu   | male   |   38 |    201 |  202 | 销售         |

|  3 | wangwu   | male   |   38 |    201 |  203 | 运营         |

|  4 | zhuliu   | female |   28 |    202 |  200 | 技术         |

|  4 | zhuliu   | female |   28 |    202 |  201 | 人力资源     |

|  4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|  4 | zhuliu   | female |   28 |    202 |  203 | 运营         |

|  5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|  5 | chenqi   | male   |   18 |    200 |  201 | 人力资源     |

|  5 | chenqi   | male   |   18 |    200 |  202 | 销售         |

|  5 | chenqi   | male   |   18 |    200 |  203 | 运营         |

|  6 | wangba   | female |   18 |    204 |  200 | 技术         |

|  6 | wangba   | female |   18 |    204 |  201 | 人力资源     |

|  6 | wangba   | female |   18 |    204 |  202 | 销售         |

|  6 | wangba   | female |   18 |    204 |  203 | 运营         |

+----+----------+--------+------+--------+------+--------------+

24 rows in set (0.11 sec)

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

找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了匹配的结果,department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;

+----+----------+------+--------+--------------+

| id | name     | age  | sex    | name         |

+----+----------+------+--------+--------------+

|  1 | zhangsan |   18 | male   | 技术         |

|  2 | lisi     |   48 | female | 人力资源     |

|  3 | wangwu   |   38 | male   | 人力资源     |

|  4 | zhuliu   |   28 | female | 销售         |

|  5 | chenqi   |   18 | male   | 技术         |

+----+----------+------+--------+--------------+

5 rows in set (0.01 sec)

上述sql等同于

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

+----+----------+------+--------+--------------+

| id | name     | age  | sex    | name         |

+----+----------+------+--------+--------------+

|  1 | zhangsan |   18 | male   | 技术         |

|  2 | lisi     |   48 | female | 人力资源     |

|  3 | wangwu   |   38 | male   | 人力资源     |

|  4 | zhuliu   |   28 | female | 销售         |

|  5 | chenqi   |   18 | male   | 技术         |

+----+----------+------+--------+--------------+

5 rows in set (0.00 sec)

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

以左表为准，即找出所有员工信息，当然包括没有部门的员工 ,本质就是：在内连接的基础上增加左边有，右边没有的结果

mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

+----+----------+--------------+

| id | name     | depart_name  |

+----+----------+--------------+

|  1 | zhangsan | 技术         |

|  5 | chenqi   | 技术         |

|  2 | lisi     | 人力资源     |

|  3 | wangwu   | 人力资源     |

|  4 | zhuliu   | 销售         |

|  6 | wangba   | NULL         |

+----+----------+--------------+

6 rows in set (0.00 sec)

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

以右表为准，即找出所有部门信息，包括没有员工的部门

本质就是：在内连接的基础上增加右边有，左边没有的结果

mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

+------+----------+--------------+

| id   | name     | depart_name  |

+------+----------+--------------+

|    1 | zhangsan | 技术         |

|    2 | lisi     | 人力资源     |

|    3 | wangwu   | 人力资源     |

|    4 | zhuliu   | 销售         |

|    5 | chenqi   | 技术         |

| NULL | NULL     | 运营         |

+------+----------+--------------+

6 rows in set (0.00 sec)

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

外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

注意：mysql不支持全外连接 full JOIN

强调：mysql可以使用此种方式间接实现全外连接

mysql> 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;

+------+----------+--------+------+--------+------+--------------+

| id   | name     | sex    | age  | dep_id | id   | name         |

+------+----------+--------+------+--------+------+--------------+

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    6 | wangba   | female |   18 |    204 | NULL | NULL         |

| NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |

+------+----------+--------+------+--------+------+--------------+

7 rows in set (0.00 sec)

mysql> select * from employee left join department on employee.dep_id = department.id union all select * from employee right join department on employee.dep_id = department.id;

+------+----------+--------+------+--------+------+--------------+

| id   | name     | sex    | age  | dep_id | id   | name         |

+------+----------+--------+------+--------+------+--------------+

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    6 | wangba   | female |   18 |    204 | NULL | NULL         |

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

| NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |

+------+----------+--------+------+--------+------+--------------+

12 rows in set (0.00 sec)

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

符合条件连接查询

示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出年龄大于25岁的员工以及员工所在的部门

mysql> select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;

+--------+--------------+

| name   | name         |

+--------+--------------+

| lisi   | 人力资源     |

| wangwu | 人力资源     |

| zhuliu | 销售         |

+--------+--------------+

3 rows in set (0.00 sec)

示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示

mysql> select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;

+----+--------+------+--------------+

| id | name   | age  | name         |

+----+--------+------+--------------+

|  4 | zhuliu |   28 | 销售         |

|  3 | wangwu |   38 | 人力资源     |

|  2 | lisi   |   48 | 人力资源     |

+----+--------+------+--------------+

3 rows in set (0.00 sec)

子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等

例子：

（1）带in关键字的子查询

查询平均年龄在25岁以上的部门名

mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); 

+------+--------------+

| id   | name         |

+------+--------------+

|  201 | 人力资源     |

|  202 | 销售         |

+------+--------------+

2 rows in set (0.00 sec)

查看技术部员工姓名

mysql> select name from employee where dep_id in (select id from department where name='技术');

+----------+

| name     |

+----------+

| zhangsan |

| chenqi   |

+----------+

2 rows in set (0.05 sec)

查看不足1人的部门名

mysql> select name from department where id not in (select dep_id from employee group by dep_id);

+--------+

| name   |

+--------+

| 运营   |

+--------+

1 row in set (0.00 sec)

（2）带比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>

查询大于所有人平均年龄的员工名与年龄

mysql> select name,age from employee where age > (select avg(age) from employee);

+--------+------+

| name   | age  |

+--------+------+

| lisi   |   48 |

| wangwu |   38 |

+--------+------+

2 rows in set (0.00 sec)

查询大于部门内平均年龄的员工名、年龄

思路：

       (1)先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。

       (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。

       (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。

mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

+------+------+

| name | age  |

+------+------+

| lisi |   48 |

+------+------+

1 row in set (0.00 sec)

(3)带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 | zhangsan | male   |   18 |    200 |

|  2 | lisi     | female |   48 |    201 |

|  3 | wangwu   | male   |   38 |    201 |

|  4 | zhuliu   | female |   28 |    202 |

|  5 | chenqi   | male   |   18 |    200 |

|  6 | wangba   | female |   18 |    204 |

+----+----------+--------+------+--------+

6 rows in set (0.00 sec)

department表中存在dept_id=205，False

mysql> select * from employee  where exists (select id from department where id=204);

Empty set (0.00 sec)

小练习：

查询每个部门最新入职的那位员工

创建表

mysql> create table info(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') nott null default 'male',age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment vaarchar(100),salary double(15,2),office int, depart_id int);

Query OK, 0 rows affected (0.10 sec)

查看表结构

mysql> desc info;

+--------------+-----------------------+------+-----+---------+----------------+

| Field        | Type                  | Null | Key | Default | Extra          |

+--------------+-----------------------+------+-----+---------+----------------+

| id           | int(11)               | NO   | PRI | NULL    | auto_increment |

| name         | varchar(20)           | NO   |     | NULL    |                |

| sex          | enum('male','female') | NO   |     | male    |                |

| age          | int(3) unsigned       | NO   |     | 28      |                |

| hire_date    | date                  | NO   |     | NULL    |                |

| post         | varchar(50)           | YES  |     | NULL    |                |

| post_comment | varchar(100)          | YES  |     | NULL    |                |

| salary       | double(15,2)          | YES  |     | NULL    |                |

| office       | int(11)               | YES  |     | NULL    |                |

| depart_id    | int(11)               | YES  |     | NULL    |                |

+--------------+-----------------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

插入记录

三个部门：教学，销售，运营

mysql> insert into info(name,sex,age,hire_date,post,salary,office,depart_id) values

     ('zhangsan','male',18,'20170301','公关部',7300.33,401,1), 

     ('lisi','male',78,'20150302','teacher',1000000.31,401,1),

     ('wangwu','male',81,'20130305','teacher',8300,401,1),

     ('zhuliu','male',73,'20140701','teacher',3500,401,1),

     ('chenqi','male',28,'20121101','teacher',2100,401,1),

     ('wangba','female',18,'20110211','teacher',9000,401,1),

     ('tianjiu','male',18,'19000301','teacher',30000,401,1),

     ('成龙','male',48,'20101111','teacher',10000,401,1),

     ('歪歪','female',48,'20150311','sale',3000.13,402,2),

     ('丫丫','female',38,'20101101','sale',2000.35,402,2),

     ('丁丁','female',18,'20110312','sale',1000.37,402,2),

     ('星星','female',18,'20160513','sale',3000.29,402,2),

     ('格格','female',28,'20170127','sale',4000.33,402,2),

     ('张野','male',28,'20160311','operation',10000.13,403,3), 

     ('程咬金','male',18,'19970312','operation',20000,403,3),

     ('程咬银','female',18,'20130311','operation',19000,403,3),

     ('程咬铜','male',18,'20150411','operation',18000,403,3),

     ('程咬铁','female',18,'20140512','operation',17000,403,3)

     ;

Query OK, 18 rows affected (0.00 sec)

Records: 18  Duplicates: 0  Warnings: 0

mysql> select t1.post,t1.name,t1.hire_date from info as t1 inner join (select post,max(hire_date) as new_date from info group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_date;

+-----------+----------+------------+

| post      | name     | hire_date  |

+-----------+----------+------------+

| 公关部    | zhangsan | 2017-03-01 |

| teacher   | lisi     | 2015-03-02 |

| sale      | 格格     | 2017-01-27 |

| operation | 张野     | 2016-03-11 |

+-----------+----------+------------+

4 rows in set (0.01 sec)