函數(shù)

在Mysql中函數(shù)是一組預(yù)定義的指令,用于執(zhí)行特定的操作并返回結(jié)果,可類比Java中的方法.在SQL中函數(shù)根據(jù)其作用范圍和返回結(jié)果方法分為兩大類:單行函數(shù),分組函數(shù)

單行函數(shù)

單行函數(shù)的特點(diǎn)為對(duì)一行數(shù)據(jù)進(jìn)行操作,并只返回一種結(jié)果.單行函數(shù)通常用于處理單個(gè)記錄數(shù)據(jù)

• 單行函數(shù)又可分為:字符函數(shù),數(shù)學(xué)函數(shù),其他函數(shù),流程控制函數(shù)

字符函數(shù)

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

eg:查詢員工姓名，姓名字?jǐn)?shù)。

SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;

• CONCAT(S1,S2,…Sn):將兩個(gè)以上的字符串連接

eg:將字符串'aaa','bbb','ccc'進(jìn)行拼接。

SELECT CONCAT('aaa','bbb','ccc');

• UPPER(),LOWER():對(duì)字符進(jìn)行大小寫轉(zhuǎn)化

eg:：查詢員工郵箱，并轉(zhuǎn)為大寫顯示

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:查詢員工電話號(hào)碼，要求去除中間的橫線 ’-’

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

數(shù)學(xué)函數(shù)

ROUND(X):對(duì)浮點(diǎn)數(shù)X進(jìn)行四舍五入

eg:查詢員工工資，和其四舍五入的整數(shù)值

SELECT salary,ROUND(salary) FROM employees;

CEIL(X):對(duì)浮點(diǎn)數(shù)X向上取整,即返回≥X的最小整數(shù)

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

SELECT salary,CEIL(salary) FROM employees;

FLOOR(X):對(duì)浮點(diǎn)數(shù)X向下取整,即返回≤X的最大整數(shù)

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

SELECT salary,FLOOR(salary) FROM employees;

TRUNCATE(X,length):對(duì)浮點(diǎn)數(shù)的小數(shù)部分進(jìn)行截取→常用于進(jìn)行保留小數(shù)操作

SELECT TRUNCATE(1.9999,2);->1.99

• MOD(X,Y):對(duì)兩個(gè)數(shù)進(jìn)行區(qū)域操作即X%Y

日期函數(shù)

• NOW(),SYSDATE():返回當(dāng)前系統(tǒng)日期+時(shí)間
• CURDATE():返回當(dāng)前系統(tǒng)日期,不包括時(shí)間
• CURTIME():返回當(dāng)前系統(tǒng)時(shí)間,不包括日期
• DATE_FORMAT(date,format): 用于格式化日期,date是要格式化的數(shù)據(jù),format是格式化的模式,格式化的通配符號(hào)如下表:

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

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

流程控制函數(shù)

流程控制函數(shù)在SQL中根據(jù)條件選擇性地返回不同的結(jié)果,其允許在查詢過程中實(shí)現(xiàn)條件邏輯

• IF(expr,true_val,false_val): 若表達(dá)式expr為真,則返回結(jié)果true_val,否則返回false_val的結(jié)果

eg: 如果查詢的年紀(jì)大于18則返回adult,否則返回minor

SELECT age,IF(age>=18,'adult','minor');

• CASE語法結(jié)構(gòu):類似于switch…case結(jié)構(gòu),用于實(shí)現(xiàn)多支路條件選擇

SELECT exper1,exper2...,
CASE exper1
		 WHEN value1 THEN result1
		 WHEN value2 THEN result2
		 WHEN value3 THEN result3
		 ...
		 ELSE result
END
FROM table_name;

eg:根據(jù)查詢的部門號(hào)返回部門名稱

SELECT department_id
CASE department_id
		 WHEN 1 THEN '經(jīng)理辦公室'
		 WHEN 2 THEN '財(cái)務(wù)部'
		 WHEN 3 THEN '后勤部'
		 ELSE 'unkown'
END AS department_name
FROM departments;

• 搜索CASE:語法結(jié)構(gòu)與樸素CASE類似,但搜索CASE可根據(jù)多種不同的表達(dá)式條件返回不同值

SELECT exper1,exper2...,
CASE 
		 WHEN condition 1 THEN result1
		 WHEN condition 2 THEN result2
		 ...
		 ELSE result
END 
FROM table_name;

eg:查詢員工姓名以及工資，工資按照一定規(guī)則發(fā)放，入職時(shí)間在2015-01-01之前的員工工資*2，入職時(shí)間在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;

分組函數(shù)

分組函數(shù)也稱為聚合函數(shù),用于對(duì)一組值進(jìn)行操作,并返回單個(gè)結(jié)構(gòu)

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

eg:查詢所有員工工資總和、平均值、最大值、最小值、員工個(gè)數(shù);

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

分組查詢

分組查詢可根據(jù)某個(gè)或某些列對(duì)數(shù)據(jù)進(jìn)行分組

按單個(gè)字段分組

• 通過GROUP BY關(guān)鍵字:按指定列進(jìn)行分組

SELECT 列表
FROM 表
[WHERE 篩選條件]
GROUP BY 分組
[ORDER BY 排序]

eg:查詢每個(gè)部門的最高工資

SELECT MAX(salary) 
FROM employees 
GROUP BY department_id;

在分組前進(jìn)行條件篩選

在GROUP BY語句之間使用 WHERE語句對(duì)查詢結(jié)果降序篩選

eg:查詢每個(gè)部門入職時(shí)間在2010-01-01之后，并且工資最高的員工信息

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

在分組之后進(jìn)行條件篩選

通過HAVING語句可以在GRUOP BY語句之后進(jìn)行條件篩選

eg:查詢員工人數(shù)大于120的部門

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

按多字段分組

• 在GRUOP BY語句后可接多個(gè)字段實(shí)現(xiàn)多字段分組

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

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

連接查詢

連接查詢是SQL中十分重要的知識(shí)點(diǎn),就有”連接不會(huì),通宵也白搭”,連接查詢也稱為多表查詢,用于將兩個(gè)以上的表的數(shù)據(jù)基于某些相關(guān)條件組合在一起,通過連接查詢,可以從表中提取數(shù)據(jù),生成一個(gè)新的結(jié)果集

• 笛卡爾積現(xiàn)象:假設(shè)有兩個(gè)表,表1有n行數(shù)據(jù),表2有m行數(shù)據(jù),在使用連接查詢時(shí)會(huì)產(chǎn)生n*m條數(shù)據(jù),因此在條件查詢時(shí)需要假設(shè)連接的條件

內(nèi)連接(INNER JOIN)

內(nèi)連接用于返回兩個(gè)表中所有滿足連接條件的所有行數(shù)據(jù),內(nèi)連接可分為:等值連接,非等值連接,自連接

等值連接

等值連接是一種常見的連接方式,其基于兩表中某一列的相等條件進(jìn)行連接

其語法結(jié)構(gòu)如下:

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;

非等值連接

非等值連接基于兩表中某一列的不等條件進(jìn)行連接.如大于,小于,不等于等等

語法結(jié)構(gòu):

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

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

eg:查詢員工工資及工資等級(jí)

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;

自連接

自連接是指同一個(gè)表的連接.這種連接通常用于處理表中有層次結(jié)構(gòu)或函數(shù)遞歸關(guān)系的數(shù)據(jù)

eg:查詢員工姓名以及對(duì)應(yīng)的直系領(lǐng)導(dǎo)

SELECT t1.employee_name AS 員工,t2.employee_name AS 領(lǐng)導(dǎo)
FROM employees t1
INNER JOIN employees t2
ON t1.manager_id=t2.employee_id;

外連接

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

左/右外連接

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

語法結(jié)構(gòu):

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;

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

最新評(píng)論