Mysql Join-連接查詢(中)_ZenDei技術網路在線

Mysql Join 連接查詢(中) 認識就我平時的數據接觸來看, 連接查詢也沒有很複雜,不夠是非常需要耐心和邏輯的, 一點點將數據查出來, 拼接等. 沒有什麼技巧, 多練習就會了. 無非就是表之間根據共同key來連接, 查詢時結合union, where, 子查詢這些... , 真的不難的, ...

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 + 聚合 這些吧...