函數

在Mysql中函數是一組預定義的指令,用于執(zhí)行特定的操作并返回結果,可類比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):對兩個數進行區(qū)域操作即X%Y

日期函數

• NOW(),SYSDATE():返回當前系統日期+時間
• CURDATE():返回當前系統日期,不包括時間
• CURTIME():返回當前系統時間,不包括日期
• DATE_FORMAT(date,format): 用于格式化日期,date是要格式化的數據,format是格式化的模式,格式化的通配符號如下表:

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:查詢員工姓名以及工資，工資按照一定規(guī)則發(fā)放，入職時間在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;

外連接

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

左/右外連接

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

語法結構:

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;

到此這篇關于Mysql中常用函數 分組,連接查詢 的文章就介紹到這了,更多相關Mysql分組,連接查詢 內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論