mysql join-连接查询(中)
认识
就我平时的数据接触来看, 连接查询也没有很复杂,不够是非常需要耐心和逻辑的, 一点点将数据查出来, 拼接等. 没有什么技巧, 多练习就会了. 无非就是表之间根据共同key来连接, 查询时结合union, where, 子查询这些..., 真的不难的, 就是有点烦, 有时会嵌套很多层,会晕.
实现
这里在网上找了一些培训班的查询练习资料做演示, 其实市场面上的培训班资料, 怎么说的, 优势在于覆盖面广, 重视应用, 缺点是很肤浅,只适合入门了解.
- 部门表dept (id, name)
- 员工表emp(id, name, gender, salary, join_date, dept_id)
- 关联key: dept.id ~ dept_id (我不用外键)
准备数据
-- mysql -u root -p ....
-- show database;
-- use cj;
-- show tables;
-- 新建表格测试数据
drop table if exists emp;
drop table if exists dept;
create table dept(
id int primary key auto_increment,
name varcahr(20)
);
-- 员工表
create table emp(
id int primary key auto_increment,
name varchar(20),
gender char(1),
salary double,
join_date date,
dept_id int
);
-- 插入测试数据
insert into dept(name) values
("市场部"), ("财务部"), ("研发部");
insert into emp values
(0, '老三','男',20000,'2016-02-24',1);
(0, '老二','男',18000,'2017-12-02',2);
(0, '亚洲','男',17000,'2018-08-08',1);
(0, '油油','女',14000,'2018-10-07',3);
(0, '老四','女',15000,'2014-03-14',1);
(0, '阳哥','男',3000,'2019-03-14',null);
-- 查看
select * from emp;
+----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+------+--------+--------+------------+---------+
| 1 | 老三 | 男 | 20000 | 2016-02-24 | 1 |
| 2 | 老二 | 男 | 18000 | 2017-12-02 | 2 |
| 3 | 亚洲 | 男 | 17000 | 2018-08-08 | 1 |
| 4 | 油油 | 女 | 14000 | 2018-10-07 | 3 |
| 5 | 老四 | 女 | 15000 | 2014-03-14 | 1 |
| 6 | 阳哥 | 男 | 3000 | 2019-03-14 | null |
+----+------+--------+--------+------------+---------+
select * from dept;
+----+--------+
| id | name |
+----+--------+
| 1 | 市场部 |
| 2 | 财务部 |
| 3 | 研发部 |
小试join 和子查询
标量子查询
-- 查询工资最高的员工信息
-- 子查询(标量): 先查出高的工资, 然后作为where的条件
select *
from emp e
where
e.salary = (
select max(salary)
from emp
);
+----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+------+--------+--------+------------+---------+
| 1 | 老三 | 男 | 20000 | 2016-02-24 | 1 |
+----+------+--------+--------+------------+---------+
列子查询,配合 in 判断
-- 查询 市场部 和 研发部 的信息
-- where (emp.dept_id) in (dept.id 中符合条件的ids)
select *
from emp e
where e.dept_id in (
select id
from dept d
where d.name in ("市场部", "研发部")
);
+----+------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+------+--------+--------+------------+---------+
| 1 | 老三 | 男 | 20000 | 2016-02-24 | 1 |
| 3 | 亚洲 | 男 | 17000 | 2018-08-08 | 1 |
| 4 | 油油 | 女 | 14000 | 2018-10-07 | 3 |
| 5 | 老四 | 女 | 15000 | 2014-03-14 | 1 |
+----+------+--------+--------+------------+---------+
-- 方案2 表连接inner join 再进行过滤
select *
from (
select e.*, d.name as "部门"
from emp e, dept d
where e.dept_id = d.id
) as a
where a.部门 in ("市场部", "研发部");
-- 方案3 改进2
select e.*, d.name as "部门"
from emp e, dept d
where (
(e.dept_id = d.id) and
(d.name in ("市场部", "财务部"))
);
查询特定字段, 分别用 子查询和join实现
-- 查询2018年后入职的员工id, 姓名, 日期, 部门名称
-- 技巧: 先连接表 as别名, 通过别名where... , 最后再写查询字段
select *
from
emp as e
dept d -- as 可省略
where
e.xxx
d.xxx
-- 最后再写要查询的字段
select e.xx, e.xx, d.xx
select e.id, e.name, e.join_date, d.name as "部门"
from
emp e,
dept d
where
e.dept_id = d.id -- 没有部门的不算哦
and e.join_date >= "2018/1/1";
+----+------+------------+--------+
| id | name | join_date | 部门 |
+----+------+------------+--------+
| 3 | 亚洲 | 2018-08-08 | 市场部 |
| 4 | 油油 | 2018-10-07 | 研发部 |
+----+------+------------+--------+
-- 方案2 子查询
select e.id, e.name, e.join_date, d.name
from (
-- 先提前筛选出来
select *
from emp
where join_date >="2018/1/1"
) as e,
dept as d
where e.dept_id = d.id;
强化join练习
准备数据
- 部门表 dept(id, d_ame, location)
- 岗位表 job(id, j_name, description)
- 员工表 emp(id, e_name, job_id, leader, join_date, salary, bonus, dept_id)
- 工资等级表 salary_grade(grade, s_min, s_max)
- 关联(坚持不用外键) job.id ~emp.job_id, emp.dept_id ~ dept.id
drop table if exists dept;
drop table if exists job;
drop table if exists emp;
drop table if exists salary_grade;
-- 部门表
create table dept(
id int primary key,
d_name varchar(20),
location varchar(30)
);
-- 岗位表
create table job(
id int primary key,
j_name varchar(20),
description varchar(30)
);
-- 员工表
create table emp(
id int primary key,
e_name varchar(10),
job_id int,
leader varchar(10), -- 直属领导
join_date date,
salary decimal(8,2),
bonus decimal(7,2), -- 奖金
dept_id int
);
-- 工资等级表
create table salary_grade(
grade int primary key, -- 级别
s_min int, -- 下界
s_max int -- 上界
);
-- 添加测试数据
insert into dept values
(10,'研发部','深圳'),
(20,'产品部','深圳'),
(30,'销售部','广州'),
(40,'财务部','上海');
insert into job (id, j_name, description) values
(1, 'ceo', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售', '向客户销售商品'),
(4, '文员', '职能管理');
insert into salary_grade values
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
insert into emp values
(1001,'孙悟空',4,1004,'2014-12-17','8000.00',null,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2018-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2017-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2015-05-01','28500.00',null,30),
(1007,'刘备',2,1009,'2019-09-01','24500.00',null,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,20),
(1009,'罗贯中',1,null,'2016-11-17','50000.00',null,10),
(1010,'吴用',3,1006,'2017-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2012-05-23','11000.00',null,20),
(1012,'李逵',4,1006,'2008-12-03','9500.00',null,30),
(1013,'小白龙',4,1004,'2016-12-03','30000.00',null,20),
(1014,'关羽',4,1007,'2019-01-23','13000.00',null,10);
预览一波测试数据
-- 预览一波 mysql> select * from dept; +----+--------+----------+ | id | d_name | location | +----+--------+----------+ | 10 | 研发部 | 深圳 | | 20 | 产品部 | 深圳 | | 30 | 销售部 | 广州 | | 40 | 财务部 | 上海 | +----+--------+----------+ mysql> select * from job; +----+--------+--------------------+ | id | j_name | description | +----+--------+--------------------+ | 1 | ceo | 管理整个公司，接单 | | 2 | 经理 | 管理部门员工 | | 3 | 销售 | 向客户销售商品 | | 4 | 文员 | 职能管理 | +----+--------+--------------------+ mysql> select * from salary_grade; +-------+-------+-------+ | grade | s_min | s_max | +-------+-------+-------+ | 1 | 7000 | 12000 | | 2 | 12010 | 14000 | | 3 | 14010 | 20000 | | 4 | 20010 | 30000 | | 5 | 30010 | 99990 | +-------+-------+-------+ mysql> select * from emp limit 5; +------+--------+--------+--------+------------+----------+----------+---------+ | id | e_name | job_id | leader | join_date | salary | bonus | dept_id | +------+--------+--------+--------+------------+----------+----------+---------+ | 1001 | 孙悟空 | 4 | 1004 | 2014-12-17 | 8000.00 | null | 20 | | 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | | 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | | 1004 | 唐僧 | 2 | 1009 | 2018-04-02 | 29750.00 | null | 20 | | 1005 | 李逵 | 4 | 1006 | 2017-09-28 | 12500.00 | 14000.00 | 30 | +------+--------+--------+--------+------------+----------+----------+---------+
case1: (2张表) 查询员工信息, 包括员工编号, 姓名, 工资, 职位名称, 职务描述
- 流程: 梳理好涉及的表及字段; 表的连接条件
- 技巧: from > where > group by 及 聚合函数 > having > order > select
分析:
- emp表: id, e_name, salary
- job表: j_name, description
- 连接条件: left join 或内连接, emp.job_id = jop.id
-- 用left join, 平时excel 的vlookup 就是这样玩的
select *
from emp e
left join job d
on e.job_id = d.id;
select
e.id,
e.e_name,
e.salary,
d.j_name,
d.description
from emp e
left join job d
on e.job_id = d.id;
-- out
+------+--------+----------+--------+--------------------+
| id | e_name | salary | j_name | description |
+------+--------+----------+--------+--------------------+
| 1009 | 罗贯中 | 50000.00 | ceo | 管理整个公司，接单 |
| 1004 | 唐僧 | 29750.00 | 经理 | 管理部门员工 |
| 1006 | 宋江 | 28500.00 | 经理 | 管理部门员工 |
| 1007 | 刘备 | 24500.00 | 经理 | 管理部门员工 |
| 1002 | 卢俊义 | 16000.00 | 销售 | 向客户销售商品 |
| 1003 | 林冲 | 12500.00 | 销售 | 向客户销售商品 |
| 1010 | 吴用 | 15000.00 | 销售 | 向客户销售商品 |
| 1001 | 孙悟空 | 8000.00 | 文员 | 职能管理 |
| 1005 | 李逵 | 12500.00 | 文员 | 职能管理 |
| 1008 | 猪八戒 | 30000.00 | 文员 | 职能管理 |
| 1011 | 沙僧 | 11000.00 | 文员 | 职能管理 |
| 1012 | 李逵 | 9500.00 | 文员 | 职能管理 |
| 1013 | 小白龙 | 30000.00 | 文员 | 职能管理 |
| 1014 | 关羽 | 13000.00 | 文员 | 职能管理 |
+------+--------+----------+--------+--------------------+
case2: (3张表) 查询员工编号, 姓名, 工资, 职位, 部门名称, 部门地点
- emp表: id, e_name, salary
- job表: j_name
- dept表: d_name, location
- 连接: 左连接, emp.job_id = job.id, emp.dept_id = dept.id
-- 我通常是先全部查出来, 再进行where
select *
from emp e
left join job j
on e.job_id = j.id
left join dept d -- 继续连
on e.dept_id = d.id;
select
e.id,
e.e_name,
e.salary,
j.j_name,
-- d.d_name,
d.location
from emp e
left join job j
on e.job_id = j.id
-- 继续连接...
left join dept d
on e.dept_id = d.id
limit 3;
+------+--------+----------+--------+----------+
| id | e_name | salary | j_name | location |
+------+--------+----------+--------+----------+
| 1009 | 罗贯中 | 50000.00 | ceo | 深圳 |
| 1004 | 唐僧 | 29750.00 | 经理 | 深圳 |
| 1006 | 宋江 | 28500.00 | 经理 | 广州 |
+------+--------+----------+--------+----------+
case3: 查询员工姓名, 工资, 工资等级 (between....and, 判断条件做 join)
分析
- emp表: e_name, salary
- salary_grade表: grade
- 连接条件: join, e.salary between s.s_min and s.s_max
select
e.e_name,
e.salary as "薪资",
s.grade as "薪资等级"
from emp e
join
salary_grade s
on e.salary between s.s_min and s.s_max
limit 5;
+--------+----------+----------+
| e_name | 薪资 | 薪资等级 |
+--------+----------+----------+
| 孙悟空 | 8000.00 | 1 |
| 卢俊义 | 16000.00 | 3 |
| 林冲 | 12500.00 | 2 |
| 唐僧 | 29750.00 | 4 |
| 李逵 | 12500.00 | 2 |
+--------+----------+----------+
5 rows in set (0.11 sec)
case4: (4张表) 查询员工姓名, 工资, 职位, 部门, 部门地点, 工资等级
分析
- emp表: e_name, salary
- job表: j_name
- dept表: d_name, location
- salary_grade表: grade
- 连接条件 e, j, d, s:
- e.job_id = j.id
- e.dept_id = d.id
- e.salary between s.s_min and s.s_max
select *
from emp e
left join job j -- 职位表
on e.job_id = j.id
left join dept d -- 部门表
on e.dept_id = d.id
join salary_grade s -- 薪资等级表
on e.salary between s.s_min and s.s_max
limit 1;
left 拼接了一张超级大表
+------+--------+--------+--------+------------+----------+---------+---------+----+--------+----------------+----+--------+----------+-------+-------+-------+ | id | e_name | job_id | leader | join_date | salary | bonus | dept_id | id | j_name | description | id | d_name | location | grade | s_min | s_max | +------+--------+--------+--------+------------+----------+---------+---------+----+--------+----------------+----+--------+----------+-------+-------+-------+ | 1001 | 孙悟空 | 4 | 1004 | 2014-12-17 | 8000.00 | null | 20 | 4 | 文员 | 职能管理 | 20 | 产品部 | 深圳 | 1 | 7000 | 12000 |
select
e.e_name,
e.salary,
j.j_name,
d.d_name,
d.location,
s.grade
from emp e
left join job j -- 职位表
on e.job_id = j.id
left join dept d -- 部门表
on e.dept_id = d.id
join salary_grade s -- 薪资等级表
on e.salary between s.s_min and s.s_max
limit 5;
+--------+----------+--------+--------+----------+-------+
| e_name | salary | j_name | d_name | location | grade |
+--------+----------+--------+--------+----------+-------+
| 孙悟空 | 8000.00 | 文员 | 产品部 | 深圳 | 1 |
| 卢俊义 | 16000.00 | 销售 | 销售部 | 广州 | 3 |
| 林冲 | 12500.00 | 销售 | 销售部 | 广州 | 2 |
| 唐僧 | 29750.00 | 经理 | 产品部 | 深圳 | 4 |
| 李逵 | 12500.00 | 文员 | 销售部 | 广州 | 2 |
+--------+----------+--------+--------+----------+-------+
case5: 查询 部门编号, 名称, 位置, 人数, join 再 group by, 聚合函数等
分析: 先连接, 然后再按 部门 分组+聚合即可
- dept表: id, d_name, location
- emp表: id (用来count的)
- 连接条件: 做连接 dept.id = emp.dept_id
select
d.id,
d.d_name,
d.location,
-- 不要count(*) null 不加入计算哦
count(e.id) as "人数"
from dept d
left join emp e
on d.id = e.dept_id
-- 按部门分组
group by d.d_name;
+----+--------+----------+------+
| id | d_name | location | 人数 |
+----+--------+----------+------+
| 20 | 产品部 | 深圳 | 5 |
| 10 | 研发部 | 深圳 | 3 |
| 40 | 财务部 | 上海 | 0 |
| 30 | 销售部 | 广州 | 6 |
+----+--------+----------+------+
case6: (自连接) 查询员工的姓名, 及其领导
分析: 通过as取别名进行自连接(id字段 和leader 字段是自相关的哦)
- emp表 e1: e_name
- emp表 e2: e.leader
- 连接条件: 左连接, e1.leader = e2.id
select
e1.e_name as "员工姓名",
e1.e_name as "直属领导"
from emp as e1
left join emp as e2
on e1.leader = e2.id;
limit 5;
+----------+----------+
| 员工姓名 | 直属领导 |
+----------+----------+
| 孙悟空 | 孙悟空 |
| 卢俊义 | 卢俊义 |
| 林冲 | 林冲 |
| 唐僧 | 唐僧 |
| 李逵 | 李逵 |
+----------+----------+
14 rows in set (0.11 sec)
总结
- 表连接方式我用频率 left join > inner join
- 连接字段on 等值或判断条件均可
- 多表连接的关键在于分析需要哪些表, 及其字段, 确定连接方式, 是否需要嵌套或聚合...
- 技巧上, 尽量sql语句写标准化方便阅读, from > where > group by 及 聚合函数 > having > order > select
- 下篇再加强一波自连接(省市区联动 或 粉丝互动) 和 结合 union, group by + 聚合 这些吧...
