MySQL-複雜查詢及條件-起別名-多表查詢-04

• 2019 年 10 月 7 日
• 筆記

目錄

• 基本查詢語句及方法
• 測試數據創建
  • 創建資料庫與表
  • 插入表記錄數據
• 數據展示
  • 常見結果排版
  • 另一種結果排版 G
• 簡單查詢語句的書寫與執行順序
  • 查詢語句書寫
  • 執行順序
  • 科普– 起別名
    • 寫法
  • 可以對欄位做四則運算（加減乘數）
  • concat 格式化拼接欄位
  • concat_ws 用指定字元拼接欄位
• 訂製化查詢結果
  • 常見的數據訂製化關鍵字（非多表查詢）
  • where 結合過濾條件過濾結果
    • > < = != <= >= <> 比較運算符
    • and or not 與或非連接多個條件
    • is
    • 範圍
    • exists 是否存在
    • like 模糊匹配
    • regexp 正則
  • group by 分組
    • 應用場景
    • 分組嚴格模式（推薦開啟）
  • having 分組條件
    • 分組結合聚合函數
  • distinct 去重
  • order by 排序
  • limit 限制展示數據的條數
    • 練習
    • 應用場景
• 究極版執行順序書寫順序
  • 書寫順序(除了 select … from … 其他是可選的)
  • 執行順序
• 多表查詢
  • 前言
  • 測試數據創建
    • 創建資料庫與表
    • 插入表記錄數據
  • 笛卡爾集/積 — 科普
  • 聯表查詢
    • 內連接 inner join … on
    • 左連接 left join … on
    • 右連接 right join … on
    • 全連接 union … on
  • 子查詢
  • 練習小案例

基本查詢語句及方法

測試數據創建

如果在windows系統中，插入中文字元，select的結果為空白，可以將所有字元編碼統一設置成gbk（或者參照我安裝配置MySQL的部落格，將所有字元編碼設置為 utf8）

創建資料庫與表

create database db1;  use db1;    create table emp(    id int not null unique auto_increment,    # 表內沒有欄位是primary key，innodb的機制，一個表沒有primaryk key時會自動將 not null + unique的鍵自動升級為 primary key 主鍵    name varchar(20) not null,    sex enum('male','female') not null default 'male',  # 大部分是男的    age int(3) unsigned not null default 28,  # 這個3 只限制了顯示寬度，並不影響存儲    hire_date date not null,    post varchar(50),    post_comment varchar(100),    salary double(15,2),    office int,  # 一個部門一間辦公室，一個門牌號    depart_id int  # 暫不建立外鍵關係  );

插入表記錄數據

# 三個部門：教學，銷售，運營  # 以下是教學部  insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values  ('jason','male',18,'20170301','張江第一帥形象代言',7300.33,401,1),  ('egon','male',78,'20150302','teacher',1000000.31,401,1),  ('kevin','male',81,'20130305','teacher',8300,401,1),  ('tank','male',73,'20140701','teacher',3500,401,1),  ('owen','male',28,'20121101','teacher',2100,401,1),  ('jerry','female',18,'20110211','teacher',9000,401,1),  ('nick','male',18,'19000301','teacher',30000,401,1),  ('sean','male',48,'20101111','teacher',10000,401,1),    # 以下是銷售部門  ('歪歪','female',48,'20150311','sale',3000.13,402,2),  ('丫丫','female',38,'20101101','sale',2000.35,402,2),  ('丁丁','female',18,'20110312','sale',1000.37,402,2),  ('星星','female',18,'20160513','sale',3000.29,402,2),  ('格格','female',28,'20170127','sale',4000.33,402,2),    # 以下是運營部門  ('張野','male',28,'20160311','operation',10000.13,403,3),  ('程咬金','male',18,'19970312','operation',20000,403,3),  ('程咬銀','female',18,'20130311','operation',19000,403,3),  ('程咬銅','male',18,'20150411','operation',18000,403,3),  ('程咬鐵','female',18,'20140512','operation',17000,403,3);

數據展示

常見結果排版

select * from emp;

另一種結果排版 G

當表欄位特別多的時候，結果的排版可能會出現混亂的現象，你可以在語句最後加 G 來改變排版，方便查看

比較差的展示結果，這種情況就非常適合用 G 來看數據

簡單查詢語句的書寫與執行順序

查詢語句書寫

查詢出 emp 表中id 在 3~6 的員工詳細資訊

思路：從emp 表中，查 id 大於3 且 小於 6 的數據

語句

select * from emp where id > 3 and id < 6;

這裡僅為了演示書寫順序，不考慮其他寫法

執行順序

最先執行的是 from，來確定到底是哪張表

然後執行 where，根據條件篩選數據

最後執行 select，來拿篩選出來的數據中的（某些，select 後面跟的欄位名）欄位

科普– 起別名

關鍵字 as

• 可以給表起別名
• 可以給查詢出來的虛擬表（查詢結果）起別名
• 可以給欄位起別名
• 可以給函數的結果取別名（max、min 等）

寫法

要起別名的對象 as 別名 或者 直接 要起別名的對象 別名

不過盡量還是用as ，不用as 可能語義不明確

給函數結果起別名

... max(hire_date) as max_date ...

給表起別名

select ... from emp as t1 ....

給查詢出來的虛擬表取別名

... (select * from emp) as t2 ...

給欄位起別名

select name as '姓名', post '部門' from emp;

可以對欄位做四則運算（加減乘數）

查一下 jason 的年薪

select name as '姓名', salary * 12 as '年薪' from emp where name = 'jason';

concat 格式化拼接欄位

可以按指定格式拼接欄位

select concat('oldboy_', name, '_', id), sex, post, salary from emp;

concat_ws 用指定字元拼接欄位

select concat_ws(':', name, sex, age) from emp;

訂製化查詢結果

複雜查詢實現小竅門： 寫sql語句的時候，千萬不要急著一口氣寫完（切忌心浮氣躁） 前期按照歩鄹一步步寫，將前一步操作產生的結果都當成是一張新的表，然後基於該表再進行其他操作，寫一步查詢看一下結果然後基於當前結果再往後寫

我們查詢數據一般都需要做一些過濾，單純靠 select * from 表名; 就無法達到要求，此時我們可以通過

常見的數據訂製化關鍵字（非多表查詢）

• where 條件過濾數據 一般配合一堆聚合函數使用
• group by 對數據進行分組
  • having 對分組的結果再進行條件過濾（必須跟在 group by 語句後面）
• distinct 對查詢結果去重
• order by 對查詢結果排序
• limit 限制顯示數據條數

where 結合過濾條件過濾結果

> < = != <= >= <> 比較運算符

# 查詢出 emp 表中， id 大於3 的員工資訊  select * from emp where id > 3;

and or not 與或非連接多個條件

一般用來連接多個條件

and 並且

or 或

not 非

is

針對 null 判斷的時候只能用 is 不能用 =

案例

# and  # 1.查詢id大於等於3小於等於6的數據  select id,name from emp where id >= 3 and id <= 6;    # or  # 2.查詢薪資是20000或者18000或者17000的數據  select * from emp where salary = 20000 or salary = 18000 or salary = 17000;    # not  # 5.查詢id小於3或者大於6的數據  select *  from emp where id not between 3 and 6;    # is  # 7.查詢崗位描述為空的員工名與崗位名  針對null不能用等號，只能用is  select name,post from emp where post_comment = NULL;  # 查詢為空，不能用 = 判斷空！  select name,post from emp where post_comment is NULL;  select name,post from emp where post_comment is not NULL;

範圍

between … and … 表示範圍（整型欄位可用）

in

and or not 結果其他關鍵字組合

案例

# between ... and ...  # 1.查詢id大於等於3小於等於6的數據  select *  from emp where id between 3 and 6;    # in  # 2.查詢薪資是20000或者18000或者17000的數據  select * from emp where salary in (20000,18000,17000);    # and or not  # 在上一模組中有案例

exists 是否存在

# EXISTS 關鍵字表示存在  # 返回值是 True 或者 False  select * from emp where exists (select id from dep where id > 203);  # 用到了下面的子查詢（知識點）

like 模糊匹配

一般包含有 … 之類的查詢都會用 like 關鍵字，模糊匹配

小技巧

是否含有用 % 包圍起來

固定長度用 — 來佔位，一個 _ 表示一個字元

案例

# 3.查詢員工姓名中包含o字母的員工姓名和薪資  select name,salary from emp where name like '%o%';    # 4.查詢員工姓名是由四個字元組成的員工姓名與其薪資  # 方案一：用四個 _ 代替四個字元  select name,salary from emp where name like '____';  # 方案二：利用 char_length(欄位名) 來獲取欄位長度  select name,salary from emp where char_length(name) = 4;  # 注意 sql_mode 里 PAD_CHAR_TO_FULL_LENGTH 這麼個約束，否則 char類型定長可能會受影響

regexp 正則

可用正則規則匹配字元串作為查詢條件

select * from emp where name regexp '^j.*(n|y)$';

group by 分組

分組之後應該做到最小單位是組，而不應該再展示組內的單個資訊

MySQL 中分組之後，只能拿到分組的欄位資訊，無法直接獲取其他欄位資訊 但是你可以通過其他方法（如：聚合函數）間接地獲取

分組相當於打包，聚合函數可以對包里每一個元素進行處理，最終拿出想要的

剛開始查詢表，一定要按照最基本的步驟，先確定是哪張表，再確定查這張表也沒有限制條件，再確定是否需要分類，最後再確定需要什麼欄位對應的資訊

應用場景

查每個部門的平均薪資，男女比例等

分組嚴格模式（推薦開啟）

select * from emp group by post; 如果你的MySQL不報錯，說明分組的嚴格模式沒有設置

–> only_full_group_by 限制分組

非分組嚴格模式下

設置分組嚴格模式（其他的嚴格模式別忘寫了）

set global sql_mode='strict_trans_tables,pad_char_to_full_length,only_full_group_by';

pad_char_to_full_length 驗證 char varchar佔用空間用，會影響 char_length() 獲取的長度

strict_trans_tables 限制 sql 不能不合規則的直接報錯

分組嚴格模式下執行，直接報錯（day37.emp.id –> 資料庫day37.表emp.欄位id），欄位不在分組裡

having 分組條件

having 跟 where 是一模一樣的，也是用來篩選數據的，但是 having 必須在 group by 後面使用 where是對整體數據做一個初步的篩選，而having是對分組之後的數據再進行一次針對性的篩選

select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

統計各部門年齡在30歲以上的員工平均工資，並且保留平均工資大於10000的部門

分組結合聚合函數

只能在分組之後使用（如果沒有寫group by ，默認所有數據就是一組）

也可以說是 where 不能用聚合函數（執行順序過了 where之後就可也以算分組之後了–> 執行順序）

max min avg sum count

能夠獲取到分組之後除了分組依據以外的欄位，將該欄位作為函數的條件

# 強調:只要分組了，就不能夠再「直接」查找到單個數據資訊了，只能獲取到組名  # 2.獲取每個部門的最高工資  # 以組為單位統計組內數據>>>聚合查詢(聚集到一起合成為一個結果)  # 每個部門的最高工資  select post,max(salary) from emp group by post;  # 每個部門的最低工資  select post,min(salary) from emp group by post;  # 每個部門的平均工資  select post,avg(salary) from emp group by post;  # 每個部門的工資總和  select post,sum(salary) from emp group by post;    # 每個部門的人數  # 在統計分組內個數的時候，填寫任意非空欄位都可以完成計數（推薦使用能夠標識數據的欄位，比如id欄位）  select post,count(id) from emp group by post;

group_concat 分組拼接記錄欄位

能夠獲取到分組之後除了分組依據以外的欄位，還能做拼接操作

# 3.查詢分組之後的部門名稱和每個部門下所有的學生姓名  # group_concat（分組之後用）不僅可以用來顯示除分組外欄位還有拼接字元串的作用  select post,group_concat(name) from emp group by post;    select post,group_concat(name,"_SB") from emp group by post;    select post,group_concat(name,": ",salary) from emp group by post;    select post,group_concat(salary) from emp group by post;      # 4.補充concat（不分組時用）拼接字元串達到更好的顯示效果 as語法使用（前面有講到）  select name as 姓名,salary as 薪資 from emp;  select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪資 from emp;    /*  concat  在不分組情況下使用  group_concat  用在分組之後  */

distinct 去重

對整個查詢（查詢出的虛擬表）結果中重複的數據去重，重複必須數據是一模一樣的才能去重，只要有一個(欄位)不一樣都不能算是重複的數據 如果你查詢出來的數據中包含主鍵（非空且唯一），那麼不可能去重成功 個人推薦理解成作用於上一步查詢結果的（不要以為像order by一樣修飾某個欄位）

select distinct age, id from emp;

查詢結果有重複的情況下，會自動去除重複

select distinct sex, age, id from emp order by sex, age asc;

記錄沒有重複，distinct 無效

order by 排序

order by 有升序（ASC）、降序（DESC）兩種排序規則，默認升序 多個排序欄位時，放前面的作為優先排序條件，相同再按照後面的欄位排序

select post, avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary);

單個欄位排序

select * from emp order by sex, age asc;

多欄位排序

limit 限制展示數據的條數

select * from emp limit 3;

當limit 只有一個參數的時候，表示的是從第一條開始只展示幾條

select * from emp limit 5,5;

當limit 有兩個參數的時候，第一個參數表的起始位置，第二個參數表示從起始位置開始往後展示的條數

練習

查詢工資最高的人的詳細資訊

select * from emp order by salary desc limit 1;

應用場景

分頁數據展示，每頁只展示多少條，每頁展示的內容，是第幾條到第幾條

究極版執行順序書寫順序

書寫順序(除了 select … from … 其他是可選的)

select distinct 欄位1,欄位2（有分組時只能寫分組欄位或聚合函數） from 表名  where 條件（不能用聚合函數）  group by （單個）欄位  having 條件  order by 欄位1,欄位2 排序規則  limit 起始位置,條數;

執行順序

from  where  group by      having  # 必須跟在 group by 後面  # 後4個順序不太重要，也不一定對  order by  limit  distinct  select

多表查詢

前言

在昨天的知識點中，員工資訊全存為一張表不太合理，我們選擇了拆表，分析了表關係，最終拆分成了員工表與部門表兩張表，表示拆分好了，但怎麼去查詢數據呢？

要實現多表查詢，有下面兩種方式

• 聯表查詢
• 子查詢

每一次的查詢結果都是一張虛擬表，我們可以用 as 關鍵字給虛擬表取別名，然後將其當做普通表作為查詢條件使用

測試數據創建

創建資料庫與表

create database db2;  use db2;    create table dep(  id int,  name varchar(20)  );    create table emp(  id int primary key auto_increment,  name varchar(20),  sex enum('male','female') not null default 'male',  age int,  dep_id int  );

插入表記錄數據

insert into dep values  (200,'技術'),  (201,'人力資源'),  (202,'銷售'),  (203,'運營');    insert into emp(name,sex,age,dep_id) values  ('jason','male',18,200),  ('egon','female',48,201),  ('kevin','male',38,201),  ('nick','female',28,202),  ('owen','male',18,200),  ('jerry','female',18,204);    # 當初為什麼我們要分表，就是為了方便管理，在硬碟上確實是多張表，但是到了記憶體中我們應該把他們再拼成一張表進行查詢才合理

笛卡爾集/積 — 科普

笛卡爾集的列數為每個表的列數之和，笛卡爾集的行數為每個表的行數相乘。

我們經常做的多表查詢就是在笛卡爾集中通過篩選條件得出的數據，所以笛卡爾集是多表查詢的基礎。

select * from emp, dep; 結果是一個笛卡爾集/積

select * from emp;

select * from dep;

後面跟條件也可以達到多表查詢

select * from emp, dep where emp.dep_id = dep.id

聯表查詢

通過下面四種連接語句來實現多表查詢

inner/left/right join …左右是表 on … 後面可以跟條件

內連接 inner join … on

僅保留兩張表有對應關係的記錄

select * from emp inner join dep on emp.dep_id=dep.id;

左連接 left join … on

在內連接的基礎上保留左表沒有對應關係的記錄

select * from emp left join dep on emp.dep_id = dep.id;

右連接 right join … on

在內連接的基礎上保留右表沒有對應關係的記錄

select * from emp right join dep on emp.dep_id = dep.id;

全連接 union … on

不常用

在內連接的基礎上保留左、右面表沒有對應關係的的記錄

寫法：只需要在左連接和右連接的sql 語句中間加個union就變成了全連接

select * from emp left join dep on emp.dep_id = dep.id  # 左連接 sql，後面不要加分號  union  select * from emp right join dep on emp.dep_id = dep.id;  # 右連接 sql

子查詢

將一個查詢語句用括弧括起來，將查詢結果（虛擬表）作為另外一個 sql 語句的查詢條件

ps：表的查詢結果可以作為其他表的查詢條件，也可以通過起別名的方式把它作為一張虛擬表去跟其他表做關聯查詢

# 1.查詢部門是技術或者人力資源的員工資訊    # 思路  # 先獲取技術部和人力資源部的id號，再去員工表裡面根據前面的id篩選出符合要求的員工資訊  select * from emp where dep_id in (select id from dep where name = "技術" or name = "人力資源");

select * from emp;

select id from dep where name = "技術" or name = "人力資源";

# 2.每個部門最新入職的員工    # 思路  # 先查每個部門最新入職的員工，再按部門對應上聯表查詢    select t1.id, t1.name, t1.hire_date, t1.post from emp as t1  inner join  # 根據分組求出最新入職員工  (select post,max(hire_date) as max_date from emp group by post) as t2  on t1.post = t2.post  where t1.hire_date = t2.max_date;

完整表資訊

練習小案例

# 平均年齡在25歲以上的部門名    # 聯表  select dep.name from dep inner join emp on emp.dep_id = dep.id group by dep.name having avg(age) > 25;    # 子查詢  select name from dep where dep.id in (select dep_id from emp group by dep_id having avg(age) > 25);

聯表查詢思路

1.先把兩張表連起來，把結果查出來看看，再接著往下寫

select * from dep inner join emp on emp.dep_id = dep.id;

2.再根據部門分組，篩選出平均年齡大於25的部門名

group by dep.name having avg(age) > 25

合併上一步的語句（* –> dep.name）

select dep.name from dep inner join emp on emp.dep_id = dep.id group by dep.name having avg(age) > 25;

子查詢思路

1.先根據 部門id 分組，查出平均年齡大於 25多的部門id

select dep_id from emp group by dep_id having avg(age) > 25;

2.將上一步的 部門id 作為篩選條件 聯合上部門表查出來

select name from dep where dep.id in (select dep_id from emp group by dep_id having avg(age) > 25);