Mysql Join-連接查詢(中)

python_lover 2022-05-02 發(fā)布于北京

展開全文

認(rèn)識

就我平時的數(shù)據(jù)接觸來看, 連接查詢也沒有很復(fù)雜,不夠是非常需要耐心和邏輯的, 一點點將數(shù)據(jù)查出來, 拼接等. 沒有什么技巧, 多練習(xí)就會了. 無非就是表之間根據(jù)共同key來連接, 查詢時結(jié)合union, where, 子查詢這些..., 真的不難的, 就是有點煩, 有時會嵌套很多層,會暈.

實現(xiàn)

這里在網(wǎng)上找了一些培訓(xùn)班的查詢練習(xí)資料做演示, 其實市場面上的培訓(xùn)班資料, 怎么說的, 優(yōu)勢在于覆蓋面廣, 重視應(yīng)用, 缺點是很膚淺,只適合入門了解.

部門表dept (id, name)
員工表emp(id, name, gender, salary, join_date, dept_id)
關(guān)聯(lián)key: dept.id ~ dept_id (我不用外鍵)

準(zhǔn)備數(shù)據(jù)

-- mysql -u root -p ....
-- show database;
-- use cj;
-- show tables;
-- 新建表格測試數(shù)據(jù)
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
);

-- 插入測試數(shù)據(jù)
insert into dept(name) values
("市場部"), ("財務(wù)部"), ("研發(fā)部");

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 | 財務(wù)部 |
|  3 | 研發(fā)部 |

小試join 和子查詢

標(biāo)量子查詢

-- 查詢工資最高的員工信息
-- 子查詢(標(biāo)量): 先查出高的工資, 然后作為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 判斷

-- 查詢 市場部 和 研發(fā)部 的信息

-- 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 ("市場部", "研發(fā)部")
);

+----+------+--------+--------+------------+---------+
| 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 ("市場部", "研發(fā)部");
    
-- 方案3 改進2
select e.*, d.name as "部門"
from emp e, dept d
where (
    (e.dept_id = d.id) and
    (d.name in ("市場部", "財務(wù)部"))
);

查詢特定字段, 分別用 子查詢和join實現(xiàn)

-- 查詢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 | 研發(fā)部 |
+----+------+------------+--------+

-- 方案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練習(xí)

準(zhǔn)備數(shù)據(jù)

部門表 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)
關(guān)聯(lián)(堅持不用外鍵) 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), -- 直屬領(lǐng)導(dǎo)
    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 -- 上界
);

-- 添加測試數(shù)據(jù) 
INSERT INTO dept VALUES 
(10,'研發(fā)部','深圳'),
(20,'產(chǎn)品部','深圳'),
(30,'銷售部','廣州'),
(40,'財務(wù)部','上海');

 INSERT INTO job (id, j_name, description) VALUES
 (1, 'CEO', '管理整個公司，接單'),
 (2, '經(jīng)理', '管理部門員工'),
 (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,'關(guān)羽',4,1007,'2019-01-23','13000.00',NULL,10);

預(yù)覽一波測試數(shù)據(jù)

-- 預(yù)覽一波
mysql> select * from dept;
+----+--------+----------+
| id | d_name | location |
+----+--------+----------+
| 10 | 研發(fā)部 | 深圳     |
| 20 | 產(chǎn)品部 | 深圳     |
| 30 | 銷售部 | 廣州     |
| 40 | 財務(wù)部 | 上海     |
+----+--------+----------+

mysql> select * from job;
+----+--------+--------------------+
| id | j_name | description        |
+----+--------+--------------------+
|  1 | CEO    | 管理整個公司，接單 |
|  2 | 經(jīng)理   | 管理部門員工       |
|  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張表) 查詢員工信息, 包括員工編號, 姓名, 工資, 職位名稱, 職務(wù)描述

流程: 梳理好涉及的表及字段; 表的連接條件
技巧: from > where > group by 及聚合函數(shù) > having > order > select

分析:

emp表: id, e_name, salary
job表: j_name, description
**連接條件: left join 或內(nèi)連接, 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 | 經(jīng)理   | 管理部門員工       |
| 1006 | 宋江   | 28500.00 | 經(jīng)理   | 管理部門員工       |
| 1007 | 劉備   | 24500.00 | 經(jīng)理   | 管理部門員工       |
| 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 | 關(guān)羽   | 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  -- 繼續(xù)連
	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
	-- 繼續(xù)連接...
	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 | 經(jīng)理   | 深圳     |
| 1006 | 宋江   | 28500.00 | 經(jīng)理   | 廣州     |
+------+--------+----------+--------+----------+

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 | 產(chǎn)品部 | 深圳     |     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  | 文員   | 產(chǎn)品部 | 深圳     |     1 |
| 盧俊義 | 16000.00 | 銷售   | 銷售部 | 廣州     |     3 |
| 林沖   | 12500.00 | 銷售   | 銷售部 | 廣州     |     2 |
| 唐僧   | 29750.00 | 經(jīng)理   | 產(chǎn)品部 | 深圳     |     4 |
| 李逵   | 12500.00 | 文員   | 銷售部 | 廣州     |     2 |
+--------+----------+--------+--------+----------+-------+

case5: 查詢部門編號, 名稱, 位置, 人數(shù), join 再 group by, 聚合函數(shù)等

分析: 先連接, 然后再按部門分組+聚合即可

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 "人數(shù)"
from dept d
	left join emp e
	on d.id = e.dept_id
-- 按部門分組
group by d.d_name;

+----+--------+----------+------+
| id | d_name | location | 人數(shù) |
+----+--------+----------+------+
| 20 | 產(chǎn)品部 | 深圳     |    5 |
| 10 | 研發(fā)部 | 深圳     |    3 |
| 40 | 財務(wù)部 | 上海     |    0 |
| 30 | 銷售部 | 廣州     |    6 |
+----+--------+----------+------+

case6: (自連接) 查詢員工的姓名, 及其領(lǐng)導(dǎo)

分析: 通過as取別名進行自連接(id字段和leader 字段是自相關(guān)的哦)

emp表 e1: e_name
emp表 e2: e.leader
連接條件: 左連接, e1.leader = e2.id

select 
	e1.e_name as "員工姓名",
	e1.e_name as "直屬領(lǐng)導(dǎo)"
from emp as e1
	left join emp as e2
	on e1.leader = e2.id;
	
limit 5;
	
+----------+----------+
| 員工姓名 | 直屬領(lǐng)導(dǎo) |
+----------+----------+
| 孫悟空   | 孫悟空   |
| 盧俊義   | 盧俊義   |
| 林沖     | 林沖     |
| 唐僧     | 唐僧     |
| 李逵     | 李逵     |
+----------+----------+
14 rows in set (0.11 sec)

總結(jié)

表連接方式我用頻率 left join > inner join
連接字段on 等值或判斷條件均可
多表連接的關(guān)鍵在于分析需要哪些表, 及其字段, 確定連接方式, 是否需要嵌套或聚合...
技巧上, 盡量sql語句寫標(biāo)準(zhǔn)化方便閱讀, from > where > group by 及聚合函數(shù) > having > order > select
下篇再加強一波自連接(省市區(qū)聯(lián)動或粉絲互動) 和結(jié)合 union, group by + 聚合 這些吧...

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊一鍵舉報。

小男孩‘自慰网亚洲一区二区,亚洲一级在线播放毛片,亚洲中文字幕av每天更新,黄aⅴ永久免费无码,91成人午夜在线精品,色网站免费在线观看,亚洲欧洲wwwww在线观看

Mysql Join-連接查詢(中)

認(rèn)識

實現(xiàn)

準(zhǔn)備數(shù)據(jù)

小試join 和子查詢

強化join練習(xí)

準(zhǔn)備數(shù)據(jù)

總結(jié)