Mysql中常用函式 分組,連線查詢

ihav2carryon發表於2024-10-17
MySql函式

函式

在Mysql中函式是一組預定義的指令,用於執行特定的操作並返回結果,可類比Java中的方法.在SQL中函式根據其作用範圍和返回結果方法分為兩大類:單行函式,分組函式

單行函式

單行函式的特點為對一行資料進行操作,並只返回一種結果.單行函式通常用於處理單個記錄資料

  • 單行函式又可分為:字元函式,數學函式,其他函式,流程控制函式

字元函式

  • CHAR_LENGTH(S),LENGTH(S):返回字串的長度

eg:查詢員工姓名，姓名字數。

SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;
  • CONCAT(S1,S2,…Sn):將兩個以上的字串連線

eg:將字串'aaa','bbb','ccc'進行拼接。

SELECT CONCAT('aaa','bbb','ccc');
  • UPPER(),LOWER():對字元進行大小寫轉化

eg:：查詢員工郵箱，並轉為大寫顯示

SELECT UPPER(email) FROM emplyees;
  • substr,substring(S, start, length):提取字串S從start位置開始,長度為length的字字串

eg:提取hello world中的hello

SELECT substr('hello world',1,5);`
  • replace(S, old, new): 在字串S中將所有的old替換為new

eg:查詢員工電話號碼，要求去除中間的橫線 ’-’

SELECT REPLACE(phone_number, '-', '') FROM emplyees;

數學函式

  • ROUND(X):對浮點數X進行四捨五入

    eg:查詢員工工資，和其四捨五入的整數值

    SELECT salary,ROUND(salary) FROM employees;

  • CEIL(X):對浮點數X向上取整,即返回≥X的最小整數

    eg:查詢員工工資，並且向上取整

    SELECT salary,CEIL(salary) FROM employees;

  • FLOOR(X):對浮點數X向下取整,即返回≤X的最大整數

    eg:查詢員工工資，並且向下取整

    SELECT salary,FLOOR(salary) FROM employees;

  • TRUNCATE(X,length):對浮點數的小數部分進行擷取→常用於進行保留小數操作

    SELECT TRUNCATE(1.9999,2);->1.99

  • MOD(X,Y):對兩個數進行區域操作即X%Y

日期函式

  • NOW(),SYSDATE():返回當前系統日期+時間
  • CURDATE():返回當前系統日期,不包括時間
  • CURTIME():返回當前系統時間,不包括日期
  • DATE_FORMAT(date,format): 用於格式化日期,date是要格式化的資料,format是格式化的模式,格式化的萬用字元號如下表:
格式符 功能
%Y 4位年份
%y 2位年份
%m 月份（01，02,…,11,12）
%c 月份（1,2,…,11,12）
%d 日（01，02，…）
%H 小時（24小時制）
%h 小時（12小時制）
%i 分鐘（00，01，…,58,59）
%s 秒（00，01，…,58,59）

eg:查詢員工姓名、入職時間，入職時間按照xxxx年xx月xx日輸出

SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日') FROM employees;

流程控制函式

流程控制函式在SQL中根據條件選擇性地返回不同的結果,其允許在查詢過程中實現條件邏輯

  • IF(expr,true_val,false_val): 若表示式expr為真,則返回結果true_val,否則返回false_val的結果

eg: 如果查詢的年紀大於18則返回adult,否則返回minor

SELECT age,IF(age>=18,'adult','minor');
  • CASE語法結構:類似於switch…case結構,用於實現多支路條件選擇
SELECT exper1,exper2...,
CASE exper1
		 WHEN value1 THEN result1
		 WHEN value2 THEN result2
		 WHEN value3 THEN result3
		 ...
		 ELSE result
END
FROM table_name;

eg:根據查詢的部門號返回部門名稱

SELECT department_id
CASE department_id
		 WHEN 1 THEN '經理辦公室'
		 WHEN 2 THEN '財務部'
		 WHEN 3 THEN '後勤部'
		 ELSE 'unkown'
END AS department_name
FROM departments;
  • 搜尋CASE:語法結構與樸素CASE類似,但搜尋CASE可根據多種不同的表示式條件返回不同值
SELECT exper1,exper2...,
CASE 
		 WHEN condition 1 THEN result1
		 WHEN condition 2 THEN result2
		 ...
		 ELSE result
END 
FROM table_name;

eg:查詢員工姓名以及工資，工資按照一定規則發放，入職時間在2015-01-01之前的員工工資*2，入職時間在2018-01-01之前的員工工資*1.5，其他不變

SELECT 
employee_name,hiredate,salary 原工資,
CASE
    WHEN hiredate<'2015-01-01' THEN salary*2
		WHEN hiredate<'2018-01-01' THEN salary*1.3
		ELSE salary
END AS 新工資
FROM employees;

分組函式

分組函式也稱為聚合函式,用於對一組值進行操作,並返回單個結構

  • COUNT(*):計算指定列中非NULL值的數量,SUM(column):計算指定列之和,AVG(column):計算指定列平均數,MAX(colum):取出指定列最大值,MIN(colum):取出指定列最小值

eg:查詢所有員工工資總和、平均值、最大值、最小值、員工個數;

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(*) FROM employees;

分組查詢

分組查詢可根據某個或某些列對資料進行分組

按單個欄位分組

  • 透過GROUP BY關鍵字:按指定列進行分組
SELECT 列表
FROM 表
[WHERE 篩選條件]
GROUP BY 分組
[ORDER BY 排序]

eg:查詢每個部門的最高工資

SELECT MAX(salary) 
FROM employees 
GROUP BY department_id;

在分組前進行條件篩選

在GROUP BY語句之間使用 WHERE語句對查詢結果降序篩選

eg:查詢每個部門入職時間在2010-01-01之後，並且工資最高的員工資訊

SELECT *
FROM employees
WHERE hiredate >'2010-01-01'
GROUP BY department_id;

在分組之後進行條件篩選

  • 透過HAVING語句可以在GRUOP BY語句之後進行條件篩選

    eg:查詢員工人數大於120的部門

    SELECT *
FROM employees
GROUP BY department_id
HAVING COUNT(*)>120;

按多欄位分組

  • 在GRUOP BY語句後可接多個欄位實現多欄位分組

eg:查詢每個部門，男女員工的平均工資

SELECT department_id,sex,AVG(salary) AS 平均工資
FROM employees
GROUP BY department_id,sex;

連線查詢

連線查詢是SQL中十分重要的知識點,就有”連線不會,通宵也白搭”,連線查詢也稱為多表查詢,用於將兩個以上的表的資料基於某些相關條件組合在一起,透過連線查詢,可以從表中提取資料,生成一個新的結果集

  • 笛卡爾積現象:假設有兩個表,表1有n行資料,表2有m行資料,在使用連線查詢時會產生n*m條資料,因此在條件查詢時需要假設連線的條件

內連線(INNER JOIN)

內連線用於返回兩個表中所有滿足連線條件的所有行資料,內連線可分為:等值連線,非等值連線,自連線

等值連線

等值連線是一種常見的連線方式,其基於兩表中某一列的相等條件進行連線

其語法結構如下:

SELECT colum1,colum2,....,
FROM table1 
INNER JOIN table2
ON table1.colum = table.colum;

eg:查詢員工姓名以及所在的部門名稱

SELECT employee_name AS 員工名,department_name AS 部門名
FROM employees e	
INNER JOIN departments d ON 
e.department_id=d.department_id;

非等值連線

非等值連線基於兩表中某一列的不等條件進行連線.如大於,小於,不等於等等

語法結構:

SELECT colum1,colum2,....,
FROM table1 
INNER JOIN
ON table1.colum <operator> table2.colum;

其中operator可以是>,<,≥,≤,≠,BETWEEN…AND等等;

eg:查詢員工工資及工資等級

SELECT e.salary,j.grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.higest_sal;

自連線

自連線是指同一個表的連線.這種連線通常用於處理表中有層次結構或函式遞迴關係的資料

eg:查詢員工姓名以及對應的直系領導

SELECT t1.employee_name AS 員工,t2.employee_name AS 領導
FROM employees t1
INNER JOIN employees t2
ON t1.manager_id=t2.employee_id;

外連線

外連線用於返回主表中滿足連線條件的行,同時保留另一個表中沒有匹配的行

左/右外連線

  • 左(右)連線顧名思義即根據表位置區分主表,即在左連線即左表,右連線即右側

語法結構:

SELECT colum1,colum2...,
FROM table1 [LEFT|RIGHT]
JOIN ON [連線條件];

eg:查詢員工姓名以及所在的部門名稱，沒有部門資訊的員工也要查詢出來

SELECT employee_name AS 員工姓名,department_name AS 部門名稱
FROM employees e LEFT
JOIN departments d
ON e.department_id=d.department_id;

相關文章