建表与数据准备
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 =employee.dep_id;运行结果：
MySQL数据库入门(七)多表查询及大量练习-1.jpg (33.83 KB, 下载次数: 0)
2021-2-16 02:32 上传
注意事项：
内连表会舍弃左表和右表两边不匹配的数据！
请认真看department表，这里有4个部门；employee表，这里有6个员工。
连表以后只有5条记录，其中department表中“运营”记录被舍弃，employee表中’廖婷婷’记录被舍弃。
二、左外表
语法案例：
select * from department left join employee on =employee.dep_id;运行结果：
MySQL数据库入门(七)多表查询及大量练习-2.jpg (37.36 KB, 下载次数: 0)
2021-2-16 02:32 上传
注意事项：
左外表会保留左表全部数据，舍弃右表不匹配的数据！
请认真看department表，这里有4个部门；employee表，这里有6个员工。
连表以后只有6条记录，其中department表中的4条记录齐全，employee表中’廖婷婷’记录被舍弃。
三、右外连表
语法案例：
select * from department right join employee on =employee.dep_id;运行结果：
MySQL数据库入门(七)多表查询及大量练习-3.jpg (36.14 KB, 下载次数: 0)
2021-2-16 02:32 上传
注意事项：
右外表会保留右表全部数据，舍弃左表不匹配的数据！
请认真看department表，这里有4个部门；employee表，这里有6个员工。
连表以后只有6条记录，其中department表中“运营”记录被舍弃，employee表中6条记录齐全。
四、全外连表
语法案例：
mysql不支持全外连表，但是可以使用union变通实现全外连表，代码如下：
select * from department left join employee on =employee.dep_idunion select * from department right join employee on =employee.dep_id;运行结果：
MySQL数据库入门(七)多表查询及大量练习-4.jpg (40.81 KB, 下载次数: 0)
2021-2-16 02:32 上传
注意事项：
全外连表不会舍弃任何数据！
请认真看department表，这里有4个部门；employee表，这里有6个员工。
连表练习
查询所有人事部的员工名单：select 姓名 from department d # 部门表重命名为d inner join employee e #员工表重命名为e on d.id=e.dep_id # 部门表的id字段与员工表的dep_id字段建立内连接 where ='人事'; # 筛选部门名字是'人事'的记录查询人事部年龄大于40岁的员工名单：select 姓名 from department d inner join employee e on d.id=e.dep_id where ="人事" and e.age>40; # 筛选部门名字是人事且员工年龄大于40岁的记录查询年龄大于20岁的员工及所在部门：select 姓名, 部门 from department d inner join employee e on d.id=e.dep_id where age>20;查询所有部门的名字和员工人数：select 部门,count(e.id) 人数 from department d left join employee e on d.id=e.dep_id group by ; # 按部门名字进行分组注意：这里要用左外连表，因为所有的部门数据都要保留，不能遗漏没人的部门。
5. 查询所有部门的名字和员工人数，按人数从高到低排列：
select 部门,count(e.id) 人数 from department d left join employee e on d.id=e.dep_id group by order by 人数 desc; # 按人数从高到低排序子查询
子查询是指查询中嵌套查询。
具体看下列案例：
查询平均年龄在25岁以上的部门：select 部门 from department d where id in( # 筛选部门id在平均年龄25岁以上的 select dep_id from employee group by dep_id # 按部门id分组 having avg(age)>25 # 按平均年龄过滤部门，只保留平均年龄大于25的 );查询不足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人的部门查询大于单位平均年龄的员工select name,age from employee where age>( # 括号内子句中计算单位的平均年龄 select avg(age) from employee );多表查询总结：
遇到既可使用子查询实现也可使用连表查询实现的情况，优先考虑使用连表查询，因为连表查询效率比子查询高。写多表查询代码时要分层分步实现，每一步经过验证无误后，再将若干步骤拼接起来。注重书写格式，建议参考上述案例中select父句和子句缩进书写格式，既可以方便书写注释又方便识别父句与子句，大大提高代码可读性。注意select语句的子句执行顺序，请参照子句执行的顺序书写子句。
