MySQL查詢進階操作從函數到表連線的使用

2022-08-02 14:07:23

前言

書接上文，今天學習查詢的進階操作，包括函數、分組、分頁、子查詢和表連線。內容比較實用，建議收藏或者訂閱專欄方便學習和複習。

一、MySQL函數的使用

mysql中內建了很多函數，每個函數都代表一個特定功能

1、單行函數

特點：每行資料都會產生一個對應的結果

表中有多少行，就會有多少行的結果

位置：select之後，from之前或作為篩選條件

1.concat（…,列名，…,列名）
用來拼接多列的值

例如：以firstname+lastname的形式顯示所有員工的姓名

select concat(first_name,last_name) as 全名 from employees

2.mod(值1，值2)
求值1和值2的取餘結果，相當於數學運算中的 %（值1%值2）

標準SQL規範中不存在%取餘的寫法，所以取餘操作應該使用mod()
例如：select mod(10,3) from dual

注： dual為虛擬表，作用為使當前SQL符合語法規範，不寫時會做自動填充

3.length(列名|資料)
獲取長度

查詢所有firstname長度>6的員工資訊

select * from employees where length(first_name)>6

4.now() | sysdate()
獲取系統當前時間

select now()
select SYSDATE()
select now() from dual

2、多行函數(組函數)

以組為單位獲取結果，一組得到一個結果
如果未手動進行分組，則預設整張表為一組

1.sum(列名)

求某一列的總和

2.avg(列名)

求某一列的平均值

3.max(列名)

求某一列的最大值

4.min(列名)

求某一列的最小值

5.count(列名)

求某一列值的個數(非空)

範例：

-- 組函數操作salary
select 
SUM(salary) 總和,AVG(salary) 平均值,MAX(salary) 最大值,min(salary) 最小值,COUNT(salary) 非空值的數量
from employees
-- 計算表中績效列不為空的員工數量
select COUNT(commission_pct) from employees
-- 求表中的行數
select COUNT(*) from employees
select COUNT(employee_id) from employees

二、MySQL的分組

在MySQL中一個組就是一個操作單位

1.簡單分組

select 列名 from 表名 group by 列名

以部門為單位求每個部門的平均薪資

select department_id,AVG(salary) 平均薪資 from employees group by department_id

2.where + 分組

-- 查詢部門id為10、20、30的部門的平均薪資、
select department_id,AVG(salary) 平均薪資 
from employees
where department_id in(10,20,30) -- 先篩選
GROUP BY department_id -- 後分組查詢

3.having + 分組

select department_id,AVG(salary) 平均薪資 
from employees
group by department_id -- 先分組
having department_id in(10,20,30)  -- 後篩選

4.分組中 where 和 having 的區別

where是在分組前執行，having是在分組後執行
where不能在分組時使用組函數，但是having可以
當兩者都可使用時，優先使用where，效率更高

三、分頁以及查詢關鍵字的執行順序

1.limit：用來限制顯示的查詢結果條數，通常用於分頁查詢

select 列名 from 表名 limit 顯示的起始下標,顯示條數

查詢所有員工資料的前十條

select * from employees limit 0,10
select * from employees limit 10 下標為0時可以省略

下標為0時可以省略

當limit和其他查詢關鍵字聯用時，limit一定最後執行

查詢的執行順序：

語法順序		執行順序
select			5
from			1
where			2
group by		3
having			4
order by		6
limit			7

四、子查詢

當一個SQL需要藉助於另一個SQL的執行結果，在當前SQL中巢狀另一個SQL，該寫法稱為子查詢

1、where子查詢

1.1、單值子查詢

巢狀的子SQL只會返回一行一列的結果

-- 查詢員工id為100的員工的部門id
select department_id from employees where employee_id=100

-- 子查詢：：查詢員工id為100的員工所在的部門資訊
select * from departments 
where department_id=(select department_id from employees where employee_id=100)

優先執行內層SQL，再執行外層SQL
子SQL需寫入小括號

1.2、多值子查詢巢狀的子SQL會返回多個結果

--查詢firstname中包含s的員工所在的部門資訊
select * from departments 
where department_id in(select department_id from employees where first_name like '%s%')

2、from子查詢

將子SQL的查詢結果臨時看做一張表進行後續操作

-- 獲取薪資最高的前十個員工的平均薪資
-- 先獲取薪資最高的前十名員工的薪資
select salary from employees ORDER BY salary desc LIMIT 10
-- 子查詢：再根據子SQL的查詢結果臨時通過別名構建一張資料表進行查詢
select avg(salary) from (select salary from employees ORDER BY salary desc LIMIT 10) as e

五、表連線

當查詢結果需要從多張表中獲取時，則需要將多張表連線起來進行查新操作

表連線的前提：

多張表之間必須存在關聯關係（外來鍵）
外來鍵通常連線的是另一張表的主鍵
在進行表連線時作為連線條件使用

1、內連線

語法：

select 表1.列名，表2.列名,... 
from 表1 inner join 表2
on 連線條件

特點：對兩張表同時進行約束，只有當所有表都符合連線條件，才會顯示資訊
使用：inner可省

2、(左)外連線

語法：

select 表1.列名，表2.列名,... 
from 表1(左表) left outer join 表2（右表）
on 連線條件

特點：只對右表做約束，左表中的資料都會顯示，右表中只有符合連線條件的才會顯示
使用：outer可省
左外連線和右外連線就是left和right的不同，位置不同，通常用左外連線
使用：

-- 查詢所有的員工資訊及符合連線條件的部門資訊
select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id

全外連線：

將兩個查詢結果進行合併顯示

查詢語句1 union 查詢語句2

使用：

-- 內連線
select e.*,d.*  
from employees e inner join departments d
on  e.department_id=d.department_id

union -- 對內連線和左連線的查詢結果進行合併

-- 左連線
select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id

union會對合並結果進行去重
union all不會對結果去重
特點：合併雙方都不做約束
合併雙方的查詢結果欄位的個數、順序必須一致

實際開發中，使用頻率較高的是內連線和左外連線

3、自連線

概念：是特殊的表連線，參與連線的是同一張表

具體使用：

表中的某兩個欄位之間存在關聯關係

-- 查詢：員工id、員工姓名-firstname、直接領導的id、直接領導的姓名
select e1.employee_id 員工id,e1.first_name 員工姓名,e2.employee_id 領導id,e2.first_name 領導姓名
from employees e1 LEFT JOIN employees e2-- e1代表獲取員工資訊的表，e2代表獲取領導資訊的表
ON e1.manager_id=e2.employee_id -- 把兩個有關聯關係的欄位作為連線條件

判斷同一欄位，作為連線條件

-- 查詢工資相同的員工資訊
select e1.employee_id 員工1的id,e1.salary 員工1的工資,e2.employee_id 員工2的id,e2.salary 員工2的工資
from employees e1 LEFT JOIN employees e2
on e1.salary=e2.salary -- 連線條件
where e1.employee_id<e2.employee_id -- 防止判斷的雙方是同一個人，並去重

到此這篇關於MySQL查詢進階從函數到表連線的使用的文章就介紹到這了,更多相關MySQL函數表連線內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！