前言
简单的数据我们可以直接从一个表中获取,但在真实的项目中查询符合条件的数据通常需要牵扯到多张表,这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上是多表连接查询+过滤条件。子查询是将一个查询语句嵌套在另一个查询语句中,内层查询语句的查询结果作为外层查询语句的数据源。
准备
# 建表
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 | 法律 |