共計 24415 個字符，預(yù)計需要花費 62 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章將為大家詳細講解有關(guān) MySQL 中 DQL 數(shù)據(jù)查詢語句怎么寫，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
目錄
一、基礎(chǔ)查詢
二、條件查詢
三、排序查詢
四、常見函數(shù)
五、分組查詢
六、連接查詢
七、子查詢
八、分頁查詢
九、聯(lián)合查詢
樣本數(shù)據(jù)準(zhǔn)備
進行 DQL 查詢語句實驗前，首先準(zhǔn)備對應(yīng)的數(shù)據(jù)，作為樣本以供查詢使用
使用 SQLyog 中導(dǎo)入該 SQL 腳本，可以看到準(zhǔn)備好的樣本表：
該樣本是某跨國企業(yè)員工管理的 4 張表，下圖對每張表的各個字段做了介紹：
一、基礎(chǔ)查詢
語法：select 查詢列表 from 表名;
特點：
查詢列表可以是: 表中的字段、常量值、表達式、函數(shù)。
查詢的結(jié)果是一個虛擬的表格。
執(zhí)行順序：from select (先找到表，再開始查詢)
注意:` 是著重號，當(dāng)某張表中的字段與關(guān)鍵字沖突時，可以在該字段兩邊加上著重號，以標(biāo)明其是一個字段，而不是關(guān)鍵字(如 `name`)。【基礎(chǔ)查詢】# 選中樣本庫 USER myemployees;# 1. 查詢表中的單個字段 SELECT last_name FROM employees;# 2. 查詢表中的多個字段 SELECT last_name,salary,email FROM employees;# 3. 查詢表中所有的字段 SELECT * FROM employees;# 4. 查詢常量值 SELECT Tom # 5. 查詢表達式 SELECT 7%6;# 6. 查詢函數(shù) SELECT VERSION();# 7. 起別名(mysql 中建議將起別名使用雙引號引起來 別名)/*
優(yōu)點: 便于理解; 連接查詢時, 如果要查詢的字段有重名情況, 可以使用起別名來區(qū)分
*/# 方式一, 使用 asSELECT 7%6 AS 結(jié)果;SELECT last_name AS 姓,first_name AS 名 FROM employees;# 方式二, 使用空格 SELECT 7%6 結(jié)果;SELECT last_name 姓,first_name 名 FROM employees;# 查詢員工號為 176 的員工的姓名、部門、nianxinSELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees; # 8. 去重 SELECT DISTINCT department_id FROM employees;# 9.+ 號的作用 /*
select 13+21; 兩個操作數(shù)都是數(shù)值型, 自動做加法運算
其中一個為字符型, 則將字符型轉(zhuǎn)換為數(shù)值型
select 13 轉(zhuǎn)換成功, 做加法運算
select hello 轉(zhuǎn)換失敗, 將字符型轉(zhuǎn)換為 0
select null+10; 只要其中一方為 null, 結(jié)果就為 null
補充 ifnull 函數(shù):SELECT IFNULL(commission_pct,0) AS 獎金率,commission_pct FROM employees;
mysql 中用來拼接的不是 + 號, 而是 concat 函數(shù)
*/SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
基礎(chǔ)查詢總結(jié)說明 1. 查詢表中的單個字段 select 字段 1 from 表;2. 查詢表中的多個字段 select 字段 1, 字段 2,… 字段 n from 表;3. 查詢表中的所有字段 select * from 表;4. 查詢常量值 select 常量值; 5. 查詢表達式 select 數(shù)值 1 表達式 數(shù)值 2;6. 查詢函數(shù) select f();7. 起別名 as8. 去重 distinct9. 拼接使用 concat 函數(shù), 而不是 + concat(last_name,first_name)
學(xué)完了基礎(chǔ)查詢，嘗試完成下面的練習(xí)題
答案：
1. 正確
2. 正確
3. 應(yīng)在英文狀態(tài)下使用引號
4.DESC departments;；SELECT * FROM departments;
5.SELECT CONCAT(first_name, , ,last_name, , ,IFNULL(email,0)) AS out_put FROM employees;
二、條件查詢
語法：select 查詢列表 from 表名 where 篩選條件;
執(zhí)行順序：from where select (先定位到表, 然后開始篩選, 最后走查詢)
分類:
(1)按條件運算符篩選
條件運算符有： = = = !=(或)(2)按邏輯表達式篩選
支持 || !, 但推薦使用 and or not
邏輯表達式作用：用于連接條件表達式
或 and: 兩個都為 true, 結(jié)果為 true, 反之為 false
|| 或 or : 只要有一個條件為 true, 結(jié)果即為 true, 反之為 false
! 或 not : 取反
(3)模糊查詢
模糊查關(guān)鍵字：like、between and、in、is null
(1)like 關(guān)鍵字
可以判斷字符型或數(shù)值型
like 一般和通配符搭配使用, 通配符有
%: 代表任意多個字符, 包含 0 個
_: 代表任意單個字符
(2)between...and 關(guān)鍵字
可以提高語句簡潔度
包含臨界值
兩個臨界值不能調(diào)換順序
(3)in 關(guān)鍵字
可以提高語句簡潔度
in 列表的值類型必須一致
(4)is null
取反是 is not null【條件查詢】(1)按條件運算符篩選 # 1. 查詢工資 12000 的員工 SELECT * FROM employees WHERE salary 12000 ;# 2. 查詢部門編號不等于 90 的員工名和部門編號 SELECT department_name, department_id FROM departments WHERE department_id 90;---------------------------------------------------------------------------------------------------------------------(2)按邏輯表達式篩選 # 1. 查詢工資在 10000 到 20000 之間的員工名、工資以及獎金率 SELECT last_name,salary,commission_pct FROM employees WHERE salary =10000 AND salary =20000;# 2. 查詢部門編號不是在 90 到 110 之間的, 或工資高于 15000 的員工信息 SELECT * FROM employees WHERE NOT(department_id =90 AND department_id =110) OR salary 15000;---------------------------------------------------------------------------------------------------------------------(3)模糊查詢 # (1)like 關(guān)鍵字# 1. 查詢員工名中包含字符 a 的員工的信息 SELECT * FROM employees WHERE last_name LIKE %a% # 2. 查詢員工名中第三個字符為 n, 第五個字符為 l 的員工名和工資 SELECT last_name,salary FROM employees WHERE last_name LIKE __n_l% # 3. 查詢員工名中第二個字符為_的員工名(轉(zhuǎn)義)SELECT last_name FROM employees WHERE last_name LIKE _$_% ESCAPE $ # (2)between...and 關(guān)鍵字 # 1. 查詢員工編號在 100 到 120 的員工信息 SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;# (3)in 關(guān)鍵字# 1. 查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES 中的員工名和工種編號 SELECT last_name,job_id FROM employees WHERE job_id IN(IT_PROG , AD_VP , AD_PRES # (4)is null# 1. 查詢沒有獎金的員工名和獎金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;# is null 僅僅可以用來判斷 null 值; 安全等于 = 既可以用來判斷 null 值, 又可以用來判斷普通值 # is null 的可讀性高于 = , 建議使用 is nullSELECT last_name,commission_pct FROM employees WHERE commission_pct = NULL;條件查詢總結(jié)說明 (1) 按條件運算符篩選 = = = !=(或)(2)按邏輯表達式篩選 || ! 或 and or not(3)模糊查詢關(guān)鍵字:like、between…and、in、is null
學(xué)完了條件查詢，嘗試完成下面的練習(xí)題
答案：
一、SELECT salary,last_name FROM employees WHERE commission_pct IS NULL AND salary 18000;
二、SELECT * FROM employees WHERE job_id IT OR salary=12000;
三、DESC departments;
四、SELECT DISTINCT location_id FROM departments;
五、不一定, 考慮字段有 null 的情況.
三、排序查詢
語法：select 查詢列表 from 表 where 篩選條件 order by 排序列表 asc|desc
特點：
1.asc 代表升序，esc 代表降序，不寫默認是升序。
2.order by 子句支持單個字段、多個字段、表達式、函數(shù)、別名
執(zhí)行順序：from where select order by (order by 一般放在查詢語句的最后面，limit 子句除外(后面會講到))
【排序查詢】# 1. 查詢員工信息, 要求工資從高到低排序 SELECT * FROM employees ORDER BY salary DESC;# 2. 查詢部門編號 =90 的員工信息, 按入職時間的先后進行排序【添加篩選條件】SELECT * FROM employees WHERE department_id =90 ORDER BY hiredate ASC;# 3. 按照年薪的高低顯示員工的信息和年薪【添加表達式排序】SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; # ORDER BY 后支持別名 # 4. 按照姓名的長度, 顯示員工的姓名和工資【按函數(shù)排序】SELECT LENGTH(last_name) AS 字節(jié)長度, last_name,salary FROM employees ORDER BY 字節(jié)長度 DESC;# 5. 查詢員工信息, 先按工資升序, 再按員工編號降序 SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;學(xué)完了排序查詢，嘗試完成下面的練習(xí)題
答案：
1、SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;
2、SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
3、SELECT * FROM employees WHERE email LIKE %e% ORDER BY LENGTH(email) DESC,department_id ASC;
排序查詢總結(jié)說明升序 order by asc 降序 order by desc
四、常見函數(shù)
調(diào)用語法：select 函數(shù)名(實參列表) from 表;
概念：類似于 Java 中的方法，將一組邏輯語句封裝在方法體中，對外暴露接口。
好處：
1. 隱藏了實現(xiàn)細節(jié)
2. 提高代碼重用性
分類：分為單行函數(shù)和分組函數(shù)。其中單行函數(shù)又分為：字符函數(shù)、數(shù)學(xué)函數(shù)、日期函數(shù)、系統(tǒng)函數(shù)、流程控制函數(shù)。；分組函數(shù)用來做統(tǒng)計功能，又稱為統(tǒng)計函數(shù)、聚合函數(shù)、組函數(shù)。
單行函數(shù)說明 (1) 字符函數(shù)參數(shù)類型為字符型獲取參數(shù)值的字節(jié)個數(shù) select length(字符串)拼接 concat(字段 1, 字段 2)大小寫轉(zhuǎn)換 upper、lower 截取 substr(index,end)查找 instr(主串, 子串)清除兩邊空格 trim(a from aaaa 字符串 1aa)左右填充 lpad(字符串 1 , 左填充個數(shù) n, 填充字符 a)、lpad(字符串 1 , 右填充個數(shù) n, 填充字符 a)替換 replace(字符串 1 , 被更換的字符串 , 新的字符串)(2)數(shù)學(xué)函數(shù)參數(shù)類型為數(shù)值四舍五入 round(小數(shù), 保留位數(shù))截取 truncate(小數(shù), 保留位數(shù))向上取整 ceil(被向上取整的數(shù)值)向下取整 floor(被向下取整的數(shù)值)取余 mod(n,m); 結(jié)果的正負和被取余數(shù) n 相同隨機數(shù) rand(); 返回 0 - 1 之間的小數(shù) (3) 日期函數(shù)參數(shù)為日期返回當(dāng)前完整日期 select now(); 返回當(dāng)前年月日 select curdate(); 返回當(dāng)前時分秒 select curtime(); 截取指定部分 select YEAR(now()) as 年,MONTH(now()) as 月,DAY(now()) as 日; 字符串→日期 STR_TO_DATE(2020-7-7 , %Y-%m-%d)日期→字符串 DATE_FORMAT(NOW(), %Y 年 %m 月 %d 日 )返回兩個日期相差的天數(shù) datediff(日期 1, 日期 2)(4)系統(tǒng)函數(shù)系統(tǒng)自帶查看當(dāng)前版本 select version(); 查看當(dāng)前數(shù)據(jù)庫 select database(); 查看當(dāng)前用戶 select user(); 自動加密 password( 字符 或 md5( 字符 (5)流程控制函數(shù)類比 Javaifif(獎金 IS NULL, 沒獎金 , 有獎金)
分組函數(shù)說明 sum 求和 avg 平均值 max 最大值 min 最小值 count 計算個數(shù)
【單行函數(shù)】# (1)字符函數(shù) -[參數(shù)類型為字符型]# 1.length 獲取參數(shù)值的字節(jié)個數(shù) SELECT LENGTH(john SELECT LENGTH( 張三豐 SHOW VARIABLES LIKE %char% # 查看字符集 # 2.concat 拼接字符串 SELECT CONCAT(last_name, _ ,first_name) 姓名 from employees;# 3.upper、lower 大小寫轉(zhuǎn)換 SELECT UPPER(tom SELECT LOWER( TOM)# 將姓變大寫, 名變小寫, 然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;# 4.substr 拼接函數(shù) # mysql 中的索引從 1 開始 SELECT SUBSTR(若負平生意, 何名作莫愁 ,7) AS out_put;SELECT SUBSTR(若負平生意, 何名作莫愁 ,1,3) AS out_put;# 案例: 姓名中首字符大寫, 其他字符小寫, 用_拼接并顯示出來 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), _ ,LOWER(SUBSTR(last_name,2))) oup_put FROM employees; # 5.instr 字符查找函數(shù) # 返回子串在主串中的起始索引, 沒有返回零 SELECT INSTR(凡塵阿涼 , 阿涼) AS out_put;# 6.trim 清除空格函數(shù) # 將字符兩邊的空格移除 SELECT LENGTH(TRIM( 凡塵 )) AS out_put;SELECT TRIM(a FROM aaaaaa 凡 aaa 塵 aaaa) AS out_put;# 7.lpad 左填充函數(shù) # 用指定的字符實現(xiàn)左填充指定長度 SELECT LPAD(凡塵 ,10, *) AS out_put;# 8.rpad 右填充函數(shù) # 用指定的字符實現(xiàn)右填充指定長度 SELECT RPAD(凡塵 ,10, *) AS out_put;# 9.replace 替換函數(shù) SELECT REPLACE(我的偶像是魯迅 , 魯迅 , 周冬雨) AS oup_put;---------------------------------------------------------------------------------------------------------# (2)數(shù)學(xué)函數(shù) -[參數(shù)類型為數(shù)值]# 1.round 四舍五入函數(shù) SELECT ROUND(1.65);SELECT ROUND(1.567,2);# 2.ceil 向上取整函數(shù) # 返回 = 該參數(shù)的最小整數(shù) SELECT CEIL(1.00);# 3.floor 向下取整函數(shù) # 返回 = 該參數(shù)的最大整數(shù) SELECT FLOOR(-9.99);# 4.truncate 截取函數(shù) # 保留小數(shù)點后幾位 SELECT TRUNCATE(1.65,1);# 5,mod 取余函數(shù) # 結(jié)果的正負和被除數(shù)相同:a-a/b*bSELECT MOD(10,-3);---------------------------------------------------------------------------------------------------------# (3)日期函數(shù) -[參數(shù)為日期]# 1.now 返回當(dāng)前完整日期 SELECT NOW();# 2.curdate 返回當(dāng)前年月日 SELECT CURDATE();# 3.curtime 返回當(dāng)前時分秒 SELECT CURTIME();# 4. 獲取指定的部分 SELECT YEAR(NOW()) AS 年;SELECT YEAR(1998-12-12) AS 年;SELECT YEAR(hiredate) 年 FROM employees;SELECT MONTH(NOW()) 月 ;SELECT MONTHNAME(NOW()) 月 ;# 5.str_to_date 將日期格式的字符轉(zhuǎn)換為指定格式的日期 SELECT STR_TO_DATE(2020-7-7 , %Y-%m-%d) AS out_put;# 查詢?nèi)肼毴掌跒?1992-4- 3 的員工信息 SELECT * FROM employees WHERE hiredate = STR_TO_DATE(4-3 1992 , %m-%d %Y # 6.date_format 將日期轉(zhuǎn)換為字符 SELECT DATE_FORMAT(NOW(), %Y 年 %m 月 %d 日 # 查詢有獎金的員工和入職日期 (xx 月 /xx 日 xx 年)SELECT last_name,DATE_FORMAT(hiredate, %m 月 /%d 日 %Y 年) 入職日期 FROM employees WHERE commission_pct IS NOT NULL;---------------------------------------------------------------------------------------------------------# (4) 系統(tǒng)函數(shù) # 1. 查看當(dāng)前版本 SELECT VERSION()# 2. 查看當(dāng)前數(shù)據(jù)庫 SELECT DATABASE();# 3. 查看當(dāng)前用戶 SELECT USER();---------------------------------------------------------------------------------------------------------# (5)流程控制函數(shù)# 1.if 函數(shù) SELECT IF(10 5, 大于 , 小于 SELECT last_name,commission_pct, IF(commission_pct IS NULL, 沒獎金 , 有獎金) AS out_put FROM employees;# 2.case 函數(shù) /*
方式一：類似于 Java 中的 switch-case：案例: 查詢員工工資, 要求
部門號 =30, 顯示的工資為 1.1 倍
部門號 =40, 顯示的工資為 1.2 倍
部門號 =50, 顯示的工資為 1.3 倍
其他部門, 顯示的工資為原工資
*/SELECT salary 原始工資,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工資 FROM employees;/*
方式二：類似于 Java 中的多重 if:
案例: 查詢員工的工資情況
工資 20000, 顯示 A 級別
工資 15000, 顯示 B 級別
工資 10000, 顯示 C 級別
否則, 顯示 D 級別
*/SELECT salary,CASEWHEN salary 20000 THEN A WHEN salary 15000 THEN B WHEN salary 10000 THEN C ELSE D END AS 工資級別 FROM employees;
【分組函數(shù)】/*
SUM 求和
AVG 平均值
MAX 最大值
MIN 最小值
COUNT 計算個數(shù)
*/# 綜合使用 SELECT SUM(salary) 和 ,AVG(salary) 平均數(shù) ,MAX(salary) 最大值 ,MIN(salary) 最小值 ,COUNT(salary) 總個數(shù) FROM employees;/*
分組函數(shù)的特點:
1.sum、avg 一般用于處理數(shù)值型；max、min、count 可以處理任何類型
2. 分組函數(shù)都忽略 null 值, 都可以和 distinct 搭配去重
3. 和分組函數(shù)一同查詢的字段要求是 group by 后的字段
4.count 函數(shù)經(jīng)常用來統(tǒng)計行數(shù), 使用 count(*)或 count(1)或 count(常量)
效率問題:
MYISAM 存儲引擎下,count(*)效率高
INNODB 存儲引擎下,count(*)和 count(1)效率差不多, 但比 count(字段)要高
*/學(xué)完了單行函數(shù)，嘗試完成下面的練習(xí)題
答案:
1、SELECT NOW();
2、SELECT employee_id,last_name,salary*1.2 new salary FROM employees;
3、SELECT LENGTH(last_name) 長度 ,SUBSTR(last_name,1) 首字符 ,last_name FROM employees ORDER BY 首字符;
4、SELECT CONCAT(last_name, earns),salary, monthly but wants ,salary*3 AS Dream Salary FROM employees WHERE salary=24000;
5、
SELECT last_name,job_id AS job,
CASE job_id
WHEN‘AD_PRES’THEN‘A’
WHEN‘ST_MAN’THEN‘B’
WHEN‘IT_PROG’THEN‘C’
WHEN‘SA_PRE’THEN‘D’
WHEN‘ST_CLERK’THEN‘E’
END AS“Grade”
FROM employees
WHERE job_id =“AD_PRES”;
學(xué)完了分組函數(shù)，嘗試完成下面的練習(xí)題：
答案：
1.SELECT ROUND(MAX(salary),2) 最大值 ,ROUND(MIN(salary),2) 最小值 ,ROUND(AVG(salary),2) 平均值 ,ROUND(SUM(salary),2) 總和 FROM employees;
2.SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFERNCE FROM employees;
3.SELECT COUNT(*) AS 員工個數(shù) FROM employees WHERE department_id=90;
五、分組查詢
語法:
select 分組函數(shù), 查詢列表(要求出現(xiàn)在 group by 的后面)
from 表
【where 篩選條件】
group by 分組的列表
【having 分組后的篩選】
【order by 子句】
執(zhí)行順序：from where group by having select order by
分類篩選源位置關(guān)鍵字分組前篩選原始表 group bywhere 分組后篩選分組后的結(jié)果集 group byhaving
注意：
1. 查詢列表必須是分組函數(shù)和 group by 后出現(xiàn)的字段。
2. 分組函數(shù)做條件一定放在 having 子句中。
3. 能用分組前篩選的優(yōu)先使用分組前篩選。
4.group by 子句支持單個字段分組、多個字段分組(多個字段之間用逗號隔開，沒有順序要求)、表達式或函數(shù)。
5. 可以添加排序(排序放在整個分組查詢最后)
6. 一般不在 group by 和 having 后使用別名。
# 1. 查詢每個工種的最高工資 SELECT MAX(salary) 最高工資 ,job_id 工種 FROM employees GROUP BY job_id;# 2. 查詢每個位置上的部門個數(shù) SELECT COUNT(*) 部門個數(shù) ,location_id 位置 id FROM departments GROUP BY location_id;# 3. 查詢郵箱中包含 a 字符的, 每個部門的平均工資 SELECT AVG(salary) 平均工資 ,department_id 部門 id FROM employees WHERE email LIKE %a% GROUP BY department_id;# 4. 查詢每個領(lǐng)導(dǎo)手下的有獎金的員工的最高工資 SELECT MAX(salary) 最高工資 ,manager_id 領(lǐng)導(dǎo)編號 FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY manager_id;# 5. 查詢哪個部門的員工個數(shù) 2# 思路: 查詢每個部門的個數(shù), 再根據(jù)結(jié)果哪個部門的員工個數(shù) 2SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) # 6. 查詢每個工種有獎金的員工的最高工資 12000 的工種編號和最高工資 SELECT MAX(salary) 最高工資 ,job_id 工種 FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY job_id HAVING MAX(salary) 12000;# 7. 查詢領(lǐng)導(dǎo)編號 102 的每個領(lǐng)導(dǎo)手下員工的最低工資 5000 的領(lǐng)導(dǎo)編號是哪個, 以及其最低工資 SELECT MIN(salary) 最低工資 ,manager_id 領(lǐng)導(dǎo)編號 FROM employees WHERE manager_id 102 GROUP BY manager_id HAVING MIN(salary) 5000;# 8. 按員工姓名的長度分組, 查詢每一組的員工個數(shù), 篩選員工個數(shù) 5 的有哪些 SELECT COUNT(*) 員工個數(shù) ,LENGTH(last_name) 姓名長度 FROM employees GROUP BY LENGTH (last_name) HAVING COUNT(*) # 9. 查詢每個部門每個工種的員工的平均工資 SELECT AVG(salary) 平均工資 ,department_id 部門 ,job_id 工種 FROM employees GROUP BY department_id,job_id; # 10. 查詢每個部門每個工種的員工的平均工資, 并按平均工資的高低顯示 SELECT AVG(salary) 平均工資 ,department_id 部門 ,job_id 工種 FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;學(xué)完了分組查詢，嘗試完成下面的練習(xí)題
答案：
1、SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id ASC;
2、SELECT MAX(salary)-MIN(salary) DIFFERENCE FROM employees;
3、SELECT MIN(salary) ,manager_id FROM employees WHERE NOT ISNULL(manager_id) GROUP BY manager_id HAVING MIN(salary) =6000;
4、SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_name ORDER BY AVG(salary) DESC;
5、SELECT COUNT(*) 個數(shù) ,job_id FROM employees GROUP BY job_id;
六、連接查詢
概念：查詢的字段來自多個表。
分類：安裝年代可以分為 sql92 和 sq99，按照功能分為內(nèi)連接(交集)、外連接(一個表中有, 另一個表中沒有)、交叉連接；其中 sql92 僅支持內(nèi)連接，sql99 除全外連接其他全都支持。
注意：
1. 如果為表起了別名, 則查詢的字段就不能使用原來的表名去限定。
2. 當(dāng)涉及到多表查詢時, 為表起別名可以有效提高語句簡潔度, 方便區(qū)分多個重名的字段。
連接查詢分為下面三類：
(1)內(nèi)連接 inner
等值連接
非等值連接
自連接
(2)外連接
左外連接 left outer
右外連接 right outer
全外連接 full outer
(3)交叉連接 cross
【sql92 標(biāo)準(zhǔn)】# 1. 等值連接 # 查詢女神名和對應(yīng)的男朋友名# SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;# 1. 查詢員工名和對應(yīng)的部門名 SELECT last_name 員工名 ,department_name 部門名 FROM employees,departments
WHERE employees.department_id=departments.department_id;# 2. 查詢員工名、工種號、工種名 SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;# 3. 查詢有獎金的員工名、部門名 SELECT last_name,department_name,commission_pct FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;# 等值連接 + 篩選 # 4. 查詢城市中第二個字符為 o 的部門名和城市名 SELECT department_name 部門名 ,city 城市名 FROM departments d,locations l
WHERE d.`location_id`=l.`location_id` AND city LIKE _o% # 等值連接 + 分組 # 5. 查詢每個城市的部門個數(shù) SELECT COUNT(*) 部門個數(shù) ,city 城市 FROM departments d,locations l
WHERE d.`location_id`=l.`location_id` GROUP BY city;# 6. 查詢有獎金的每個部門的部門名、部門的領(lǐng)導(dǎo)編號、該部門最低工資 SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e
WHERE d.`department_id`=e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;# 7. 查詢每個工種的工種名、員工的個數(shù)并按員工的個數(shù)降序 SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GOUP BY job_title ORDER BY COUNT(*) DESC;# 8. 支持三表連接 # 查詢員工名、部門名、所在的城市 SELECT last_name,department_name,city FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;# (2)非等值連接 /*
先執(zhí)行下面的語句, 在 myemployees 數(shù)據(jù)庫中創(chuàng)建新的 job_grades 表。CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES (A , 1000, 2999);
INSERT INTO job_grades
VALUES (B , 3000, 5999);
INSERT INTO job_grades
VALUES(C , 6000, 9999);
INSERT INTO job_grades
VALUES(D , 10000, 14999);
INSERT INTO job_grades
VALUES(E , 15000, 24999);
INSERT INTO job_grades
VALUES(F , 25000, 40000);
*/# 1. 查詢員工的工資和工資級別 SELECT salary,grade_level FROM employees e,job_grades j WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;# (3)自連接 # 1. 查詢員工名和其上級的名稱.SELECT e.employee_id 員工 id ,e.last_name 員工姓名 ,m.employee_id 經(jīng)理 id ,m.last_name 經(jīng)理姓名 FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;
學(xué)完了 sql92 標(biāo)準(zhǔn)的連接查詢，嘗試完成下面的練習(xí)題
答案：
1、
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
2、
SELECT e.job_id,d.location_id FROM employees e,departments d
WHERE d.department_id=e.department_id
AND e.department_id=90;
3、
SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND e.commission_pct IS NOT NULL;
4、
SELECT last_name , job_id , d.department_id , department_name
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.city=‘Toronto’;
5、
SELECT department_name,job_title,MIN(salary)
FROM departments d,employees e,jobs j
WHERE d.department_id=e.department_id AND e.job_id = j.job_id
GROUP BY job_title,department_name;
6、
SELECT COUNT(),country_id FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING COUNT()
7、
SELECT e1.last_name“employees”,e1.employee_id“Emp#”,e2.last_name“manager”,e2.employee_id“Mgr#”
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id AND e1.last_name=‘kochhar’;
【sql99 標(biāo)準(zhǔn)】# (1)等值連接 # 1. 查詢員工名, 部門名 SELECT last_name,department_name
FROM employees eINNER JOIN departments dON e.department_id=d.department_id;# 2. 查詢名字中包含 e 的員工名和工種名 (添加篩選)SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.job_id=j.job_idWHERE last_name LIKE %e% OR job_title LIKE %e% # 3. 查詢部門個數(shù) 3 的城市名和部門個數(shù)(分組 + 篩選)SELECT city,COUNT(*) 部門個數(shù) FROM departments dINNER JOIN locations lON d.location_id=l.location_idGROUP BY cityHAVING COUNT(*) # 4. 查詢哪個部門的部門員工個數(shù) 3 的部門名和員工個數(shù), 并按個數(shù)降序(排序)SELECT department_name 部門名 ,COUNT(*) 員工個數(shù) FROM departments dINNER JOIN employees eON d.department_id=e.department_idGROUP BY department_nameHAVING COUNT(*) 3ORDER BY COUNT(*) DESC;# 5. 查詢員工名、部門名、工種名、并按部門名排序 SELECT last_name 員工名 ,department_name 部門名 ,job_title 工種名 FROM employees eINNER JOIN departments d ON d.department_id=e.department_idINNER JOIN jobs j ON e.job_id=j.job_idORDER BY department_name ;# (2) 非等值連接 # 查詢員工工資級別 SELECT salary,grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;# 查詢每個工資級別的個數(shù) 20 的個數(shù), 并且按照工資級別降序排列 SELECT COUNT(*),grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_salGROUP BY grade_levelHAVING COUNT(*) 20ORDER BY grade_level DESC;# (3)自連接 # 查詢員工的名字、上級的名字 SELECT e1.last_name 員工名 ,e2.last_name 上級名 FROM employees e1JOIN employees e2 ON e1.manager_id=e2.employee_id;--------------------------------------------------------------------------------------------------------------# 二、外連接 # 1. 查詢男朋友不在男神表的女神名# 左外連接 SELECT NAME
FROM beauty LEFT OUTER JOIN boys ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 右外連接 SELECT NAMEFROM boys RIGHT OUTER JOIN beauty ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 2. 查詢沒有員工的部門 SELECT d.department_name,e.employee_idFROM departments d LEFT JOIN employees e ON d.department_id=e.department_idWHERE e.manager_id IS NULL;SELECT * FROM employees WHERE employee_id=100;# 3. 全外連接(不支持)# 全外連接就是就并集 USE girls;SELECT b.*,bo.*FROM beauty bFULL JOIN boys boON b.boyfriend_id=bo.id;# 三. 交叉連接 # 使用 99 標(biāo)準(zhǔn)實現(xiàn)的笛卡爾乘積, 使用 cross 代替了 92 中的,SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo
學(xué)完了 sql99 標(biāo)準(zhǔn)的連接查詢，嘗試完成下面的練習(xí)題
答案：
一、
SELECT b.name,bo.*
FROM beauty b LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id
二、
SELECT city“城市”,department_name“城市名”
FROM departments d RIGHT JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;
三、
SELECT d.department_name,e.*
FROM departments d LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE d.department_name IN (‘SAL’,‘IT’);
七、子查詢
含義：出現(xiàn)在其他語句中的 select 語句，稱為子查詢或內(nèi)查詢；外部的查詢語句，稱為主查詢或外查詢。
按子查詢出現(xiàn)的位置分類：
select 后面 (僅支持標(biāo)量子查詢)
from 后面 (支持表子查詢)
where 或 having 后面 (支持標(biāo)量、行、列子查詢)☆☆☆
exists 后面 (又叫相關(guān)子查詢，支持表子查詢)
按結(jié)果集的行列數(shù)分類：
標(biāo)量子查詢(結(jié)果集只有一行一列)
列子查詢(結(jié)果集只有一列多行)
行子查詢(結(jié)果集有一行多列)
表子查詢(結(jié)果集一般為多行多列)
【where 和 having 后的子查詢】(支持標(biāo)量、行、列子查詢)# 1. 單個標(biāo)量子查詢 # 查詢工資比 Abel 工資高的員工名 SELECT last_name,salary
FROM employees
WHERE salary (SELECT salary FROM employees WHERE last_name= Abel # 2。多個標(biāo)量子查詢 # 返回 job_id 與 141 號相同,salary 比 143 號員工多的員工的姓名、job_id、工資。SELECT last_name,job_id,salary
FROM employeesWHERE job_id=(SELECT job_id FROM employees WHERE employee_id=141)
AND salary (SELECT salary FROM employees WHERE employee_id=143);# 3。標(biāo)量子查詢 + 分組函數(shù) # 返回工資最少的員工的 last_name、job_id 和 salarySELECT last_name,job_id,salary
FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);# 4。標(biāo)量子查詢 +having 子句 # 查詢最低工資 50 號部門最低工資的 部門 id 和其最低工資 SELECT department_id,MIN(salary) FROM employees GROUP BY department_idHAVING MIN(salary) (SELECT MIN(salary) FROM employees WHERE department_id=50);
# 5. 列子查詢(多行子查詢)# 返回 location_id 是 1400 或 1700 的部門中的所有員工姓名.SELECT last_name FROM employees
WHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));# 返回其它工種中比 job_id 為 IT_PROG 工種中任一工資低的員工的工號、姓名、job_id、以及 salarySELECT employee_id,last_name,job_id,salary FROM employeesWHERE salary ANY(SELECT DISTINCT salary FROM employees WHERE job_id= IT_PROG) AND job_id IT_PROG # 返回其它工種中比 job_id 為 IT_PROG 工種中所有工資低的員工的工號、姓名、job_id、以及 salarySELECT employee_id,last_name,job_id,salary FROM employeesWHERE salary ALL(SELECT DISTINCT salary FROM employees WHERE job_id= IT_PROG) AND job_id IT_PROG # 6. 行子查詢(一行多列或多列多行子查詢)# 查詢出員工編號最小并且工資最高的員工信息 # 方式一 SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);# 方式二 SELECT * FROM employees
WHERE employee_id=(SELECT MIN(employee_id) FROM employees)AND salary=(SELECT MAX(salary) FROM employees)
【select 后的子查詢】：(僅支持標(biāo)量子查詢)# 1. 查詢每個部門的員工個數(shù) SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id) 員工個數(shù) FROM departments d;# 2. 查詢員工號 =102 的部門名 SELECT (SELECT department_name FROM departments d INNER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102) 部門名;【from 后面的子查詢】(支持表子查詢)# 1. 查詢每個部門的平均工資的工資等級, 即將子查詢后的結(jié)果充當(dāng)一張表, 要求必須起別名 SELECT a.*,g.grade_level 工資等級 FROM (SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) aINNER JOIN job_grades gON a.ag BETWEEN lowest_sal AND highest_sal;【existts 后面的子查詢】(又叫相關(guān)子查詢, 可以用 in 代替)# exists 結(jié)果只會是 1 或 0 ：SELECT EXISTS(SELECT * FROM employees);# 1. 查詢有員工名的部門名 SELECT department_nameFROM departments dWHERE EXISTS(SELECT * FROM employees e WHERE d.department_id=e.department_id);學(xué)完了子查詢，嘗試完成下面的習(xí)題
答案：
1、思路:①查詢 Zlotkey 的部門②查詢部門號 =①的員工姓名和工資
SELECT last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name=‘Zlotkey’
)
2、思路:①查詢平均工資②查詢工資比①高的員工的工號、姓名、工資
SELECT employee_id,last_name,salary
FROM employees
WHERE salary (SELECT AVG(salary) FROM employees);
3、思路:①查詢各部門平均工資②查詢各部門工資比①高的員工的員工號、姓名、工資
SELECT employee_id,last_name,salary,e.department_id
FROM (
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY department_id
)a INNER JOIN employees e
ON a.department_id=e.department_id
WHERE e.salary a.ag
4、思路:①查詢姓名中包含字母 u 的員工的部門②查詢部門號 =①中任意一個的員工的工號和姓名
SELECT employee_id“員工號”,last_name“姓名”
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE‘%u%’
);
5、思路:①查詢 location_id=1700 的部門②查詢在①部門中工作的員工的員工號
SELECT employee_id
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id=1700
);
6、思路:①查詢姓名為 K_ing 的員工編號②查詢 manager_id=①的姓名和工資
SELECT last_name“員工姓名”,salary“工資”
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name=‘K_ing’
);
7、思路:①查詢最高工資②查詢工資 =①的姓. 名
SELECT CONCAT(first_name,last_name)“姓. 名”
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees);
如果覺得做得不過癮，可以繼續(xù)挑戰(zhàn)下面的子查詢經(jīng)典案例:
答案：
1、思路:①查詢最低工資②查詢工資 =①的 last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees)
2、思路一:①查詢各部門的平均工資;②查詢①結(jié)果上的最低平均工資③查詢哪個部門的平均工資 =②;④查詢部門信息
SELECT d.*
FROM departments d
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) a
)
);
思路二:①使用排序求出最低平均工資的部門編號②查詢部門信息
SELECT * FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
);
3、思路:①查詢各部門平均工資;②查詢最低平均工資的部門編號
SELECT d.* ,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) a
ON d.department_id=a.department_id;
4、思路:①查詢 job 的平均工資最高的 job_id;②查詢 job 信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
5、思路:①查詢公司平均工資;②查詢每個部門的平均工資; 最后篩選②中平均工資 ①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) (
SELECT AVG(salary)
FROM employees
);
6、思路:①查詢有 manager 的員工編號;②查詢編號在①中的詳細信息
SELECT * FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees);
7、思路:①查詢各部門最高工資中最低的那個部門 id;②查詢①部門的最低工資啊
SELECT MIN(salary) FROM employees WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
);
8、思路:①查詢平均工資最高的部門編號;②將 employees 和 departments 連接查詢, 篩選條件是①
SELECT last_name, d.department_id, email,salary
FROM employees e
INNER JOIN departments d ON d.manager_id=e.employee_id
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
八、分頁查詢
應(yīng)用場景: 當(dāng)要顯示的數(shù)據(jù)一頁顯示不全, 需要分頁提交 sql 請求。
語法:
select 查詢列表
from 表
[join type join 表 2
on 連接條件
where 篩選條件
group by 分組字段
having 分組后的篩選
order by]
limit offset,size;
offset: 從 0 開始的起始索引, 若省略默認從第一條開始
size: 要顯示的條目個數(shù)
執(zhí)行順序:from join on where group by having select order by limit (limit 語法和執(zhí)行都在最后)
limit 分頁公式：
select * from 表 limit (page-1)*size,size;(要顯示的頁數(shù)為 page, 每頁的條目數(shù)為 size)
# 1. 查詢前五條員工信息 SELECT * FROM employees LIMIT 5;# 2. 查詢第 11 條到第 25 條 SELECT * FROM employees LIMIT 10,15# 3. 有獎金的員工信息, 并且顯示出工資較高的前 10 名 SELECT * FROM employeesWHERE commission_pct IS NOT NULLORDER BY salaryLIMIT 10;九、聯(lián)合查詢
定義：將多條查詢語句的結(jié)果合并成一個結(jié)果。
語法：查詢語句 1 union 查詢語句 2 unin … 查詢語句 n
應(yīng)用場景：當(dāng)要查詢的結(jié)果來自多個沒有連接關(guān)系的表, 但查詢的信息一致時，最適合使用 union。
注意事項：
要求多條查詢語句的查詢列數(shù)是一致的
要求多條查詢語句查詢的每一列的類型和順序最好一致.
union 默認去重, 如果使用 union all 可以關(guān)閉去重
大多數(shù)情況下，union 的查詢效率比 or 高。
# 1. 查詢部門編號 90 或郵箱包含 a 的員工信息 SELECT * FROM employees WHERE department_id 90 OR email LIKE %a% SELECT * FROM employees WHERE department_id 90 UNION SELECT * FROM employees WHERE email LIKE %a%關(guān)于“MySQL 中 DQL 數(shù)據(jù)查詢語句怎么寫”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
向 AI 問一下細節(jié)
