建表与数据准备
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_id
union
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个员工。
连表练习
- 查询所有人事部的员工名单:
select e.name 姓名
from department d # 部门表重命名为d
inner join employee e #员工表重命名为e
on d.id=e.dep_id # 部门表的id字段与员工表的dep_id字段建立内连接
where d.name='人事'; # 筛选部门名字是'人事'的记录
- 查询人事部年龄大于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岁的记录
- 查询年龄大于20岁的员工及所在部门:
select e.name 姓名,d.name 部门
from department d
inner join employee e
on d.id=e.dep_id
where age>20;
- 查询所有部门的名字和员工人数:
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; # 按人数从高到低排序
子查询
子查询是指查询中嵌套查询。
具体看下列案例:
- 查询平均年龄在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的
);
- 查询不足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语句的子句执行顺序,请参照子句执行的顺序书写子句。