本文共 5890 字，大约阅读时间需要 19 分钟。

建表与数据准备

create database day4;use day4;# 建表create table department(    id int unsigned not null unique,    name char(20)    );create table employee(    id int unsigned primary key auto_increment,    name char(12) not null,    gender enum('male','female') not null default 'male',    age int unsigned,    dep_id int unsigned not null    );# 写入数据insert into department values    (100,'技术'),    (101,'人事'),    (102,'销售'),    (103,'运营');# 免责声明：以下名字均是faker name生成的，请勿对号入座。insert into employee(name,gender,age,dep_id) values    ('唐建华','male',18,100),    ('黄淑珍','female',48,101),    ('李畅','male',38,101),    ('华桂珍','female',28,102),    ('钟涛','male',18,100),    ('廖婷婷','female',18,104);

连表

不加条件的连表就是笛卡尔积，案例如下：

select *    from employee,department;

这样生成了24条记录，即左表6条×右表4条。这种连表方式极少用到，因为这样生成太多冗余数据。

内联表相当于加了where条件的笛卡尔积：

select * from employee,department where employee.dep_id=department.id;

常用的连表方式是根据某个共同字段将两个表进行连表，一共有以下四种类型：

一、内连表

语法案例：

select *    from department    inner join employee    on department.id=employee.dep_id;

运行结果：

+-----+--------+----+-----------+--------+------+--------+| id  | name   | id | name      | gender | age  | dep_id |+-----+--------+----+-----------+--------+------+--------+| 100 | 技术   |  1 | 唐建华    | male   |   18 |    100 || 101 | 人事   |  2 | 黄淑珍    | female |   48 |    101 || 101 | 人事   |  3 | 李畅      | male   |   38 |    101 || 102 | 销售   |  4 | 华桂珍    | female |   28 |    102 || 100 | 技术   |  5 | 钟涛      | male   |   18 |    100 |+-----+--------+----+-----------+--------+------+--------+

注意事项：

内连表会舍弃左表和右表两边不匹配的数据！ 请认真看department表，这里有4个部门；employee表，这里有6个员工。 连表以后只有5条记录，其中department表中“运营”记录被舍弃，employee表中’廖婷婷’记录被舍弃。

二、左外表

语法案例：

select *    from department    left join employee    on department.id=employee.dep_id;

运行结果：

+-----+--------+------+-----------+--------+------+--------+| id  | name   | id   | name      | gender | age  | dep_id |+-----+--------+------+-----------+--------+------+--------+| 100 | 技术   |    5 | 钟涛      | male   |   18 |    100 || 100 | 技术   |    1 | 唐建华    | male   |   18 |    100 || 101 | 人事   |    3 | 李畅      | male   |   38 |    101 || 101 | 人事   |    2 | 黄淑珍    | female |   48 |    101 || 102 | 销售   |    4 | 华桂珍    | female |   28 |    102 || 103 | 运营   | NULL | NULL      | NULL   | NULL |   NULL |+-----+--------+------+-----------+--------+------+--------+

注意事项：

左外表会保留左表全部数据，舍弃右表不匹配的数据！ 请认真看department表，这里有4个部门；employee表，这里有6个员工。 连表以后只有6条记录，其中department表中的4条记录齐全，employee表中’廖婷婷’记录被舍弃。

三、右外连表

语法案例：

select *    from department    right join employee    on department.id=employee.dep_id;

运行结果：

+------+--------+----+-----------+--------+------+--------+| id   | name   | id | name      | gender | age  | dep_id |+------+--------+----+-----------+--------+------+--------+|  100 | 技术   |  1 | 唐建华    | male   |   18 |    100 ||  101 | 人事   |  2 | 黄淑珍    | female |   48 |    101 ||  101 | 人事   |  3 | 李畅      | male   |   38 |    101 ||  102 | 销售   |  4 | 华桂珍    | female |   28 |    102 ||  100 | 技术   |  5 | 钟涛      | male   |   18 |    100 || NULL | NULL   |  6 | 廖婷婷    | female |   18 |    104 |+------+--------+----+-----------+--------+------+--------+

注意事项：

右外表会保留右表全部数据，舍弃左表不匹配的数据！ 请认真看department表，这里有4个部门；employee表，这里有6个员工。 连表以后只有6条记录，其中department表中“运营”记录被舍弃，employee表中6条记录齐全。

四、全外连表

语法案例：

mysql不支持全外连表，但是可以使用union变通实现全外连表，代码如下：

select *    from department    left join employee    on department.id=employee.dep_idunion    select *        from department        right join employee        on department.id=employee.dep_id;

运行结果：

+------+--------+------+-----------+--------+------+--------+| id   | name   | id   | name      | gender | age  | dep_id |+------+--------+------+-----------+--------+------+--------+|  100 | 技术   |    5 | 钟涛      | male   |   18 |    100 ||  100 | 技术   |    1 | 唐建华    | male   |   18 |    100 ||  101 | 人事   |    3 | 李畅      | male   |   38 |    101 ||  101 | 人事   |    2 | 黄淑珍    | female |   48 |    101 ||  102 | 销售   |    4 | 华桂珍    | female |   28 |    102 ||  103 | 运营   | NULL | NULL      | NULL   | NULL |   NULL || NULL | NULL   |    6 | 廖婷婷    | female |   18 |    104 |+------+--------+------+-----------+--------+------+--------+

注意事项：

全外连表不会舍弃任何数据！ 请认真看department表，这里有4个部门；employee表，这里有6个员工。

连表练习

1. 查询所有人事部的员工名单：

select e.name 姓名    from department d  # 部门表重命名为d    inner join employee e  #员工表重命名为e    on d.id=e.dep_id  # 部门表的id字段与员工表的dep_id字段建立内连接    where d.name='人事';  # 筛选部门名字是'人事'的记录

2. 查询人事部年龄大于40岁的员工名单：

select e.name 姓名    from department d    inner join employee e    on d.id=e.dep_id    where d.name="人事" and e.age>40;  # 筛选部门名字是人事且员工年龄大于40岁的记录

3. 查询年龄大于20岁的员工及所在部门：

select e.name 姓名,d.name 部门    from department d    inner join employee e    on d.id=e.dep_id    where age>20;

4. 查询所有部门的名字和员工人数：

select d.name 部门,count(e.id) 人数    from department d    left join employee e    on d.id=e.dep_id    group by d.name;  # 按部门名字进行分组

注意：这里要用左外连表，因为所有的部门数据都要保留，不能遗漏没人的部门。

5. 查询所有部门的名字和员工人数，按人数从高到低排列：

select d.name 部门,count(e.id) 人数    from department d    left join employee e    on d.id=e.dep_id    group by d.name    order by 人数 desc;  # 按人数从高到低排序

子查询

子查询是指查询中嵌套查询。

具体看下列案例：

1. 查询平均年龄在25岁以上的部门：

select d.name 部门    from department d    where id in(  # 筛选部门id在平均年龄25岁以上的        select dep_id            from employee            group by dep_id  # 按部门id分组            having avg(age)>25  # 按平均年龄过滤部门，只保留平均年龄大于25的        );

2. 查询不足1人的部门名(子查询得到的是有人的部门id)

select name    from department d    where d.id not in(        select dep_id from employee            group by dep_id            having count(id)>=1        );  # 这种写法适用性更广，若将1改成2可找出人数仅1人或不足1人的部门

3. 查询大于单位平均年龄的员工

select name,age    from employee    where age>(  # 括号内子句中计算单位的平均年龄        select avg(age)            from employee        );

多表查询总结：

1. 遇到既可使用子查询实现也可使用连表查询实现的情况，优先考虑使用连表查询，因为连表查询效率比子查询高。
2. 写多表查询代码时要分层分步实现，每一步经过验证无误后，再将若干步骤拼接起来。
3. 注重书写格式，建议参考上述案例中select父句和子句缩进书写格式，既可以方便书写注释又方便识别父句与子句，大大提高代码可读性。
4. 注意select语句的子句执行顺序，请参照子句执行的顺序书写子句。
   

转载地址：http://rvrqz.baihongyu.com/