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 + 聚合 這些吧...
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109146.html
標籤：MySQL
上一篇：MySQL之架構簡單分析
下一篇：如何開啟MySQL慢查詢日志