多表查询

一.准备工作:准备两张表(department),员工表(employee)

# 建立部门表(department)
mysql> create table department(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.75 sec)

#建立员工表(employee)
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.23 sec)

#插入数据
mysql> insert into department values
    -> (101,"IT技术部"),
    -> (102,"人力资源"),
    -> (103,"销售部"),
    -> (104,"运营部");
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into employee(name,sex,age,dep_id)values
    -> ("wahaha","male",18,101),
    -> ("ruwawa","female",48,102),
    -> ("shuangww","male",38,103),
    -> ("binghongc","female",28,103),
    -> ("lvcha","male",18,101),
    -> ("huacha","female",18,105);
Query OK, 6 rows affected (0.08 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> 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 department;
+------+--------------+
| id   | name         |
+------+--------------+
|  101 | IT技术部     |
|  102 | 人力资源     |
|  103 | 销售部       |
|  104 | 运营部       |
+------+--------------+
4 rows in set (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | wahaha    | male   |   18 |    101 |
|  2 | ruwawa    | female |   48 |    102 |
|  3 | shuangww  | male   |   38 |    103 |
|  4 | binghongc | female |   28 |    103 |
|  5 | lvcha     | male   |   18 |    101 |
|  6 | huacha    | female |   18 |    105 |
+----+-----------+--------+------+--------+
6 rows in set (0.00 sec)

PS:观察两张表,发闲department表中的id = 104部门在employee中没有对应的员工,发现employee中id = 6的员工在department表中没有对应的部门

二.多表连接查询

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

1.重点:外链接语法

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

(1).先看第一种情况交叉连接:不适用任何匹配条件,生成笛卡尔积(笛卡尔积:https://www.baidu.com/)

mysql> select * from employee,department;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | wahaha    | male   |   18 |    101 |  101 | IT技术部     |
|  1 | wahaha    | male   |   18 |    101 |  102 | 人力资源     |
|  1 | wahaha    | male   |   18 |    101 |  103 | 销售部       |
|  1 | wahaha    | male   |   18 |    101 |  104 | 运营部       |
|  2 | ruwawa    | female |   48 |    102 |  101 | IT技术部     |
|  2 | ruwawa    | female |   48 |    102 |  102 | 人力资源     |
|  2 | ruwawa    | female |   48 |    102 |  103 | 销售部       |
|  2 | ruwawa    | female |   48 |    102 |  104 | 运营部       |
|  3 | shuangww  | male   |   38 |    103 |  101 | IT技术部     |
|  3 | shuangww  | male   |   38 |    103 |  102 | 人力资源     |
|  3 | shuangww  | male   |   38 |    103 |  103 | 销售部       |
|  3 | shuangww  | male   |   38 |    103 |  104 | 运营部       |
|  4 | binghongc | female |   28 |    103 |  101 | IT技术部     |
|  4 | binghongc | female |   28 |    103 |  102 | 人力资源     |
|  4 | binghongc | female |   28 |    103 |  103 | 销售部       |
|  4 | binghongc | female |   28 |    103 |  104 | 运营部       |
|  5 | lvcha     | male   |   18 |    101 |  101 | IT技术部     |
|  5 | lvcha     | male   |   18 |    101 |  102 | 人力资源     |
|  5 | lvcha     | male   |   18 |    101 |  103 | 销售部       |
|  5 | lvcha     | male   |   18 |    101 |  104 | 运营部       |
|  6 | huacha    | female |   18 |    105 |  101 | IT技术部     |
|  6 | huacha    | female |   18 |    105 |  102 | 人力资源     |
|  6 | huacha    | female |   18 |    105 |  103 | 销售部       |
|  6 | huacha    | female |   18 |    105 |  104 | 运营部       |
+----+-----------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)

(2).内连接:只连接匹配的行

#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了匹配的结果
#department没有104这个部门,因而employee表中关于104这条员工信息没有匹配出来
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 | wahaha    |   18 | male   | IT技术部     |
|  2 | ruwawa    |   48 | female | 人力资源     |
|  3 | shuangww  |   38 | male   | 销售部       |
|  4 | binghongc |   28 | female | 销售部       |
|  5 | lvcha     |   18 | male   | IT技术部     |
+----+-----------+------+--------+--------------+
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 | wahaha    | IT技术部     |
|  5 | lvcha     | IT技术部     |
|  2 | ruwawa    | 人力资源     |
|  3 | shuangww  | 销售部       |
|  4 | binghongc | 销售部       |
|  6 | huacha    | 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 | wahaha    | IT技术部     |
|    2 | ruwawa    | 人力资源     |
|    3 | shuangww  | 销售部       |
|    4 | binghongc | 销售部       |
|    5 | lvcha     | IT技术部     |
| NULL | NULL      | 运营部       |
+------+-----------+--------------+
6 rows in set (0.00 sec)

(5).全外连接:显示左右两个表全部记录(了解)

#外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：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;

#查询:
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 | wahaha    | male   |   18 |    101 |  101 | IT技术部     |
|    5 | lvcha     | male   |   18 |    101 |  101 | IT技术部     |
|    2 | ruwawa    | female |   48 |    102 |  102 | 人力资源     |
|    3 | shuangww  | male   |   38 |    103 |  103 | 销售部       |
|    4 | binghongc | female |   28 |    103 |  103 | 销售部       |
|    6 | huacha    | female |   18 |    105 | NULL | NULL         |
| NULL | NULL      | NULL   | NULL |   NULL |  104 | 运营部       |
+------+-----------+--------+------+--------+------+--------------+
7 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         |
+-----------+--------------+
| ruwawa    | 人力资源     |
| shuangww  | 销售部       |
| binghongc | 销售部       |
+-----------+--------------+
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 order by age asc;
+----+-----------+------+--------------+
| id | name      | age  | name         |
+----+-----------+------+--------------+
|  1 | wahaha    |   18 | IT技术部     |
|  5 | lvcha     |   18 | IT技术部     |
|  4 | binghongc |   28 | 销售部       |
|  3 | shuangww  |   38 | 销售部       |
|  2 | ruwawa    |   48 | 人力资源     |
+----+-----------+------+--------------+
5 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         |
+------+--------------+
|  102 | 人力资源     |
|  103 | 销售部       |
+------+--------------+
2 rows in set (0.00 sec)

#查看IT技术部员工姓名
mysql> select name from employee where dep_id in(select id from department where name = "IT技术部");
+--------+
| name   |
+--------+
| wahaha |
| lvcha  |
+--------+
2 rows in set (0.00 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  |
+----------+------+
| ruwawa   |   48 |
| shuangww |   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 emp.name,emp.age from employee as emp inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t1 on emp.dep_id = t1.dep_id where emp.age > t1.avg_age;
+----------+------+
| name     | age  |
+----------+------+
| shuangww |   38 |
+----------+------+
1 row in set (0.00 sec)

3.带EXISTS关键字的子查询

#EXISTS关键字表示存在,在使用EXISTS关键字时,内层查询语句不返回查询的记录,而是返回一个真假值,True或False
#当返回True是,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
#department表中存在dept_id = 203,True
mysql> select * from employee where exists (select id from department where id = 101);
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | wahaha    | male   |   18 |    101 |
|  2 | ruwawa    | female |   48 |    102 |
|  3 | shuangww  | male   |   38 |    103 |
|  4 | binghongc | female |   28 |    103 |
|  5 | lvcha     | male   |   18 |    101 |
|  6 | huacha    | female |   18 |    105 |
+----+-----------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from employee where exists (select id from department where id = 105);
Empty set (0.00 sec)