前言
简单的数据我们可以直接从一个表中获取，但在真实的项目中查询符合条件的数据通常需要牵扯到多张表，这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上是多表连接查询+过滤条件。子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果作为外层查询语句的数据源。
准备
# 建表
create table department(
id int,
name varchar(20)
);
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
);
# 插入数据
insert into department values
(1,'技术'),
(2,'财务'),
(3,'法律’);
# 此处省略员工表数据...
多表连接查询
语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
交叉连接
在介绍多表查询的时候，有必要先介绍下交叉连接，如下select * from employee, department;查询语句就是交叉连接查询，可以看出，同一个数据在在employee表和department表交叉连接之后产生了重复记录，其重复个数取决于department表的记录个数。所以最后交叉连接之后的记录个数是：count(employee) * count(department)，即笛卡尔积。通常情况下，笛卡尔积的结果在工作中无实际意义，我们需要在笛卡尔积的基础上进行筛选，找到employee.dep_id = department.id的那条记录。
mysql> select * from employee, department;
+----+-------+------+--------+----------+-----------+--------+------+--------+
| id | name | age | sex | position | salary | dep_id | id | name |
+----+-------+------+--------+----------+-----------+--------+------+--------+
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 1 | 技术 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 2 | 财务 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 1 | 技术 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 2 | 财务 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 1 | 技术 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 2 | 财务 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 1 | 技术 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 2 | 财务 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 1 | 技术 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 2 | 财务 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技术 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 2 | 财务 |
| 6 | tony | 35 | male | RD | 100000000 | 1 | 3 | 法律 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技术 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 2 | 财务 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 3 | 法律 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 1 | 技术 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 财务 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 3 | 法律 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 1 | 技术 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 财务 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 3 | 法律 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 1 | 技术 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 财务 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 3 | 法律 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 1 | 技术 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 2 | 财务 |
| 11 | james | 18 | male | NULL | 3000 | NULL | 3 | 法律 |
+----+-------+------+--------+----------+-----------+--------+------+--------+
33 rows in set (0.00 sec)
内连接
内连接只会连接两张表匹配的行，即取交集。找两张表公共部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
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 | jack | 20 | male | 法律 |
| 2 | mark | 22 | male | 法律 |
| 3 | hank | 25 | male | 法律 |
| 4 | nick | 39 | male | 法律 |
| 5 | jenny | 26 | female | 法律 |
| 6 | tony | 35 | male | 技术 |
| 7 | emmy | 27 | female | 技术 |
| 8 | emmy | 23 | female | 财务 |
| 9 | lucy | 45 | female | 财务 |
| 10 | emmi | 20 | female | 财务 |
+----+-------+------+--------+--------+
10 rows in set (0.00 sec)
上述内连接查询语句等同于：
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name
from employee,department
where employee.dep_id=department.id;
外连接
外连接分为左连接、右连接、全外连接
左连接
左连接在内连接的基础上优先显示左表全部记录。即左连接=内连接+左表未符合条件的记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
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 |
+----+-------+-------------+
| 6 | tony | 技术 |
| 7 | emmy | 技术 |
| 8 | emmy | 财务 |
| 9 | lucy | 财务 |
| 10 | emmi | 财务 |
| 1 | jack | 法律 |
| 2 | mark | 法律 |
| 3 | hank | 法律 |
| 4 | nick | 法律 |
| 5 | jenny | 法律 |
| 11 | james | NULL |
+----+-------+-------------+
11 rows in set (0.00 sec)
右连接
又连接在内连接的基础上优先显示右表的内容。即右连接==内连接+右表未符合条件的记录
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
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 | jack | 法律 |
| 2 | mark | 法律 |
| 3 | hank | 法律 |
| 4 | nick | 法律 |
| 5 | jenny | 法律 |
| 6 | tony | 技术 |
| 7 | emmy | 技术 |
| 8 | emmy | 财务 |
| 9 | lucy | 财务 |
| 10 | emmi | 财务 |
+------+-------+-------------+
10 rows in set (0.00 sec)
可以发现，左表(employee表)的第11条记录没有被查询出来
全外连接
全外连接会在内连接查询的基础上显示左表和右表的全部记录
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 | age | sex | position | salary | dep_id | id | name |
+------+-------+------+--------+----------+-----------+--------+------+--------+
| 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技术 |
| 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技术 |
| 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 财务 |
| 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 财务 |
| 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 财务 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 |
| 11 | james | 18 | male | NULL | 3000 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 人力 |
+------+-------+------+--------+----------+-----------+--------+------+--------+
12 rows in set (0.00 sec)
符合条件链接查询
mysql> select employee.name, employee.age, department.name from employee inner join department
-> on employee.dep_id=department.id
-> where age > 30
-> order by age asc;
+------+------+--------+
| name | age | name |
+------+------+--------+
| tony | 35 | 技术 |
| nick | 39 | 法律 |
| lucy | 45 | 财务 |
+------+------+--------+
3 rows in set (0.00 sec)
子查询
子查询是将一个查询语句的嵌套在另一个查询语句中
内层查询语句的查询结果作为外层查询语句的数据源
子查询中可以包含 IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字
注意被嵌套的查询语句需要用( )包裹
查询员工部门平均年龄大于30的部门信息
mysql> select * from department
-> where id in
-> (select dep_id from employee group by dep_id having avg(age) > 30);
+------+--------+
| id | name |
+------+--------+
| 1 | 技术 |
+------+--------+
1 row in set (0.00 sec)
查询技术部员工姓名
mysql> select name from employee
-> where dep_id in
-> (select id from department where name="技术");
+------+
| name |
+------+
| tony |
| emmy |
+------+
2 rows in set (0.00 sec)
查询无员工的部门名（子查询得到的是所有人的部门id，需要disctinct去除）
mysql> select name from department
-> where id not in
-> (select distinct dep_id from employee);
带比较运算符的子查询
查询大于所有人平均年龄的员工名和年龄
mysql> select * from employee where age > (select avg(age) from employee);
+----+------+------+--------+----------+-----------+--------+
| id | name | age | sex | position | salary | dep_id |
+----+------+------+--------+----------+-----------+--------+
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 6 | tony | 35 | male | RD | 100000000 | 1 |
| 9 | lucy | 45 | female | finance | 10000 | 2 |
+----+------+------+--------+----------+-----------+--------+
3 rows in set (0.00 sec)
不能这样：在前面没有group by的时后面不能使用分组函数
mysql> select * from employee where age > avg(age);
ERROR 1111 (HY000): Invalid use of group function
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
MYSQL中可以把一个查询语句用括号括起来使用as起一个别名当做一个表使用
查询每个职位最新入职的员工
SELECT
*
FROM
emp AS t1
INNER JOIN (
SELECT
post,
max(hire_date) max_date
FROM
emp
GROUP BY
post
) AS t2 ON t1.post = t2.post
WHERE
t1.hire_date = t2.max_date;
查询语句关键字执行顺序
一个完整的mysql的查询语句如下：
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
其关键字执行顺序如下：
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
网友评论