建表与数据准备

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条。这种连表方式极少用到，因为这样生成太多冗余数据。
常用的连表方式是根据某个共同字段将两个表进行连表，一共有以下四种类型：

一、内连表

语法案例：

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

运行结果：

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

二、左外表

语法案例：

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

运行结果：

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

三、右外连表

语法案例：

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

运行结果：

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

四、全外连表

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

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

运行结果：

注意事项：
全外连表不会舍弃任何数据！
请认真看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语句的子句执行顺序，请参照子句执行的顺序书写子句。