MySQL中DQL數據查詢語句怎么寫

這篇文章將為大家詳細講解有關MySQL中DQL數據查詢語句怎么寫，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

目錄

• 一、基礎查詢

• 二、條件查詢

• 三、排序查詢

• 四、常見函數

• 五、分組查詢

• 六、連接查詢

• 七、子查詢

• 八、分頁查詢

• 九、聯合查詢

樣本數據準備

進行DQL查詢語句實驗前，首先準備對應的數據，作為樣本以供查詢使用

使用SQLyog中導入該SQL腳本，可以看到準備好的樣本表：

該樣本是某跨國企業員工管理的4張表，下圖對每張表的各個字段做了介紹：

一、基礎查詢

語法：select 查詢列表 from 表名;

特點：
查詢列表可以是:表中的字段、常量值、表達式、函數。
查詢的結果是一個虛擬的表格。

執行順序：from > select (先找到表，再開始查詢)

注意:`是著重號，當某張表中的字段與關鍵字沖突時，可以在該字段兩邊加上著重號，以標明其是一個字段，而不是關鍵字(如`name`)。

【基礎查詢】# 選中樣本庫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. 查詢函數SELECT VERSION();# 7.起別名(mysql中建議將起別名使用雙引號引起來"別名")/*
優點:便于理解;連接查詢時,如果要查詢的字段有重名情況,可以使用起別名來區分
*/# 方式一,使用asSELECT 7%6 AS 結果;SELECT last_name AS 姓,first_name AS 名 FROM employees;# 方式二,使用空格SELECT 7%6 結果;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; 兩個操作數都是數值型,自動做加法運算
其中一個為字符型,則將字符型轉換為數值型
	select '13'+1; 	   轉換成功,做加法運算
	select 'hello'+1;  轉換失敗,將字符型轉換為0
select null+10; 只要其中一方為null,結果就為null
	補充ifnull函數:SELECT IFNULL(commission_pct,0) AS 獎金率,commission_pct FROM employees;
mysql中用來拼接的不是+號,而是concat函數
*/SELECT CONCAT(last_name,first_name) AS "姓名" FROM employees;

學完了基礎查詢，嘗試完成下面的練習題

答案：
1.正確
2.正確
3.應在英文狀態下使用引號
4.DESC departments;；SELECT * FROM departments;
5.SELECT CONCAT(first_name,',',last_name,',',IFNULL(email,0)) AS "out_put" FROM employees;

二、條件查詢

語法：select 查詢列表 from 表名 where 篩選條件;

執行順序：from > where > select (先定位到表,然后開始篩選,最后走查詢)

分類:

(1)按條件運算符篩選

條件運算符有： >  <    =   >=   <=   !=(或<>)

(2)按邏輯表達式篩選

支持&& || !,但推薦使用and or not 
邏輯表達式作用：用于連接條件表達式
&&或and: 兩個都為true,結果為true,反之為false
||或or : 只要有一個條件為true,結果即為true,反之為false
!或not : 取反

(3)模糊查詢

模糊查關鍵字：like、between and、in、is null
(1)like關鍵字
	可以判斷字符型或數值型
	  like一般和通配符搭配使用,通配符有
		%:代表任意多個字符,包含0個
		_:代表任意單個字符
(2)between...and關鍵字
	  可以提高語句簡潔度
	  包含臨界值
	  兩個臨界值不能調換順序
(3)in關鍵字
	  可以提高語句簡潔度
	  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關鍵字# 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.查詢員工名中第二個字符為_的員工名(轉義)SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';# (2)between...and關鍵字# 1.查詢員工編號在100到120的員工信息SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;# (3)in關鍵字# 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;

學完了條件查詢，嘗試完成下面的練習題
答案：
一、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子句支持單個字段、多個字段、表達式、函數、別名

執行順序：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.按照姓名的長度,顯示員工的姓名和工資【按函數排序】SELECT LENGTH(last_name) AS 字節長度, last_name,salary FROM employees ORDER BY 字節長度 DESC;# 5.查詢員工信息,先按工資升序,再按員工編號降序SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;

學完了排序查詢，嘗試完成下面的練習題
答案：
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;

四、常見函數

調用語法：select 函數名(實參列表) from 表;

概念：類似于Java中的方法，將一組邏輯語句封裝在方法體中，對外暴露接口。

好處：
1.隱藏了實現細節
2.提高代碼重用性

分類：分為單行函數和分組函數。其中單行函數又分為：字符函數、數學函數、日期函數、系統函數、流程控制函數。；分組函數用來做統計功能，又稱為統計函數、聚合函數、組函數。

單行函數	說明
(1)字符函數	參數類型為字符型
獲取參數值的字節個數	select length('字符串')
拼接	concat(字段1,字段2)
大小寫轉換	upper、lower
截取	substr(index,end)
查找	instr(主串,子串)
清除兩邊空格	trim(a from'aaaa字符串1aa')
左右填充	lpad('字符串1',左填充個數n,填充字符'a')、lpad('字符串1',右填充個數n,填充字符'a')
替換	replace('字符串1','被更換的字符串','新的字符串')
(2)數學函數	參數類型為數值
四舍五入	round(小數,保留位數)
截取	truncate(小數,保留位數)
向上取整	ceil(被向上取整的數值)
向下取整	floor(被向下取整的數值)
取余	mod(n,m);結果的正負和被取余數n相同
隨機數	rand();返回0-1之間的小數
(3)日期函數	參數為日期
返回當前完整日期	select now();
返回當前年月日	select curdate();
返回當前時分秒	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日')
返回兩個日期相差的天數	datediff(日期1,日期2)
(4)系統函數	系統自帶
查看當前版本	select version();
查看當前數據庫	select database();
查看當前用戶	select user();
自動加密	password('字符');或md5('字符');
(5)流程控制函數	類比Java
if	if(獎金 IS NULL,'沒獎金','有獎金')

【單行函數】# (1)字符函數-[參數類型為字符型]# 1.length 獲取參數值的字節個數SELECT LENGTH('john');SELECT LENGTH('張三豐');SHOW VARIABLES LIKE '%char%' # 查看字符集# 2.concat拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 from employees;# 3.upper、lower 大小寫轉換SELECT UPPER('tom');SELECT LOWER('TOM')# 將姓變大寫,名變小寫,然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;# 4.substr 拼接函數# 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 字符查找函數# 返回子串在主串中的起始索引,沒有返回零SELECT INSTR('凡塵阿涼','阿涼') AS out_put;# 6.trim 清除空格函數# 將字符兩邊的空格移除SELECT LENGTH(TRIM('    凡塵    ')) AS out_put;SELECT TRIM('a' FROM  'aaaaaa凡aaa塵aaaa') AS out_put;# 7.lpad 左填充函數# 用指定的字符實現左填充指定長度SELECT LPAD('凡塵',10,'*') AS out_put;# 8.rpad 右填充函數# 用指定的字符實現右填充指定長度SELECT RPAD('凡塵',10,'*') AS out_put;# 9.replace 替換函數SELECT REPLACE('我的偶像是魯迅','魯迅','周冬雨') AS oup_put;---------------------------------------------------------------------------------------------------------# (2)數學函數-[參數類型為數值]# 1.round 四舍五入函數SELECT ROUND(1.65);SELECT ROUND(1.567,2);# 2.ceil 向上取整函數# 返回>=該參數的最小整數SELECT CEIL(1.00);# 3.floor 向下取整函數# 返回<=該參數的最大整數SELECT FLOOR(-9.99);# 4.truncate 截取函數 # 保留小數點后幾位SELECT TRUNCATE(1.65,1);# 5,mod 取余函數# 結果的正負和被除數相同:a-a/b*bSELECT MOD(10,-3);---------------------------------------------------------------------------------------------------------# (3)日期函數-[參數為日期]# 1.now 返回當前完整日期SELECT NOW();# 2.curdate 返回當前年月日SELECT CURDATE();# 3.curtime 返回當前時分秒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 將日期格式的字符轉換為指定格式的日期SELECT STR_TO_DATE('2020-7-7','%Y-%m-%d') AS out_put;# 查詢入職日期為1992-4-3的員工信息SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');# 6.date_format 將日期轉換為字符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)系統函數# 1.查看當前版本SELECT VERSION()# 2.查看當前數據庫SELECT DATABASE();# 3.查看當前用戶SELECT USER();---------------------------------------------------------------------------------------------------------# (5)流程控制函數# 1.if函數SELECT IF(10>5,'大于','小于');SELECT last_name,commission_pct, IF(commission_pct IS NULL,'沒獎金','有獎金') AS out_put FROM employees;# 2.case函數/*
方式一：類似于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;

【分組函數】/*
      SUM 求和
      AVG 平均值
      MAX 最大值
      MIN 最小值
      COUNT 計算個數
*/# 綜合使用SELECT SUM(salary) "和",AVG(salary) "平均數",MAX(salary) "最大值",MIN(salary) "最小值",COUNT(salary) "總個數" FROM employees;/*
分組函數的特點:
   1.sum、avg一般用于處理數值型；max、min、count可以處理任何類型
   2.分組函數都忽略null值,都可以和distinct搭配去重
   3.和分組函數一同查詢的字段要求是group by后的字段
   4.count函數經常用來統計行數,使用count(*)或count(1)或count(常量)
效率問題:
	MYISAM存儲引擎下,count(*)效率高
	INNODB存儲引擎下,count(*)和count(1)效率差不多,但比count(字段)要高
*/

學完了單行函數，嘗試完成下面的練習題
答案:
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”;

學完了分組函數，嘗試完成下面的練習題：

答案：
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 "員工個數" FROM employees WHERE department_id=90;

五、分組查詢

語法:
select 分組函數,查詢列表(要求出現在group by的后面)
from 表
【where 篩選條件】
group by 分組的列表
【having 分組后的篩選】
【order by 子句】

執行順序：from > where > group by > having > select > order by

注意：
1.查詢列表必須是分組函數和group by后出現的字段。
2.分組函數做條件一定放在having子句中。
3.能用分組前篩選的優先使用分組前篩選。
4.group by子句支持單個字段分組、多個字段分組(多個字段之間用逗號隔開，沒有順序要求)、表達式或函數。
5.可以添加排序(排序放在整個分組查詢最后)
6.一般不在group by和having后使用別名。

# 1.查詢每個工種的最高工資SELECT MAX(salary) "最高工資",job_id "工種" FROM employees GROUP BY job_id;# 2.查詢每個位置上的部門個數SELECT COUNT(*) "部門個數",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.查詢每個領導手下的有獎金的員工的最高工資SELECT MAX(salary) "最高工資",manager_id "領導編號" FROM employees WHERE NOT ISNULL(commission_pct)  GROUP BY manager_id;# 5.查詢哪個部門的員工個數>2# 思路:查詢每個部門的個數,再根據結果哪個部門的員工個數>2SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;# 6.查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資SELECT MAX(salary) "最高工資",job_id "工種" FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY job_id HAVING MAX(salary)>12000;# 7.查詢領導編號>102的每個領導手下員工的最低工資>5000的領導編號是哪個,以及其最低工資SELECT MIN(salary) "最低工資",manager_id "領導編號" FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;# 8.按員工姓名的長度分組,查詢每一組的員工個數,篩選員工個數>5的有哪些SELECT COUNT(*) "員工個數",LENGTH(last_name) "姓名長度" FROM employees GROUP BY LENGTH (last_name) HAVING COUNT(*)>5;# 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;

學完了分組查詢，嘗試完成下面的練習題
答案：
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(*) "個數",job_id FROM employees GROUP BY job_id;

六、連接查詢

概念：查詢的字段來自多個表。

分類：安裝年代可以分為sql92和sq99，按照功能分為內連接(交集)、外連接(一個表中有,另一個表中沒有)、交叉連接；其中sql92僅支持內連接，sql99除全外連接其他全都支持。

注意：
1.如果為表起了別名,則查詢的字段就不能使用原來的表名去限定。
2.當涉及到多表查詢時,為表起別名可以有效提高語句簡潔度,方便區分多個重名的字段。

連接查詢分為下面三類：
(1)內連接inner

• 等值連接

• 非等值連接

• 自連接

(2)外連接

• 左外連接left outer

• 右外連接right outer

• 全外連接full outer

(3)交叉連接cross

【sql92標準】# 1.等值連接# 查詢女神名和對應的男朋友名# SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;# 1.查詢員工名和對應的部門名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.查詢每個城市的部門個數SELECT COUNT(*) "部門個數",city "城市" FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` GROUP BY city;# 6.查詢有獎金的每個部門的部門名、部門的領導編號、該部門最低工資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.查詢每個工種的工種名、員工的個數并按員工的個數降序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)非等值連接/*
先執行下面的語句,在myemployees數據庫中創建新的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 "經理id",m.last_name "經理姓名" FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

學完了sql92標準的連接查詢，嘗試完成下面的練習題
答案：
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()>2;
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標準】# (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.查詢部門個數>3的城市名和部門個數(分組+篩選)SELECT city,COUNT(*) "部門個數"FROM departments dINNER JOIN locations lON d.location_id=l.location_idGROUP BY cityHAVING COUNT(*)>3;# 4.查詢哪個部門的部門員工個數>3的部門名和員工個數,并按個數降序(排序)SELECT department_name "部門名",COUNT(*) "員工個數"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;# 查詢每個工資級別的個數>20的個數,并且按照工資級別降序排列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標準實現的笛卡爾乘積,使用cross代替了92中的,SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo

學完了sql99標準的連接查詢，嘗試完成下面的練習題
答案：
一、
SELECT b.name,bo.*
FROM beauty b LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;
二、
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’);

七、子查詢

含義：出現在其他語句中的select語句，稱為子查詢或內查詢；外部的查詢語句，稱為主查詢或外查詢。

按子查詢出現的位置分類：

• select后面 (僅支持標量子查詢)

• from后面 (支持表子查詢)

• where或having后面 (支持標量、行、列子查詢)☆☆☆

• exists后面 (又叫相關子查詢，支持表子查詢)

按結果集的行列數分類：

• 標量子查詢(結果集只有一行一列)

• 列子查詢(結果集只有一列多行)

• 行子查詢(結果集有一行多列)

• 表子查詢(結果集一般為多行多列)

【where和having后的子查詢】(支持標量、行、列子查詢)# 1.單個標量子查詢# 查詢工資比Abel工資高的員工名SELECT last_name,salary 
FROM employees 
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');# 2。多個標量子查詢# 返回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。標量子查詢+分組函數# 返回工資最少的員工的last_name、job_id和salarySELECT last_name,job_id,salary 
FROM employees 
WHERE salary=(SELECT MIN(salary) FROM employees);# 4。標量子查詢+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后的子查詢】：(僅支持標量子查詢)# 1.查詢每個部門的員工個數SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id) "員工個數"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.查詢每個部門的平均工資的工資等級,即將子查詢后的結果充當一張表,要求必須起別名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后面的子查詢】(又叫相關子查詢,可以用in代替)# exists結果只會是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);

學完了子查詢，嘗試完成下面的習題

答案：
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);

如果覺得做得不過癮，可以繼續挑戰下面的子查詢經典案例:

答案：
1、思路:①查詢最低工資②查詢工資=①的last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees)
2、思路一:①查詢各部門的平均工資;②查詢①結果上的最低平均工資③查詢哪個部門的平均工資=②;④查詢部門信息
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
)

八、分頁查詢

應用場景:當要顯示的數據一頁顯示不全,需要分頁提交sql請求。

語法:
select 查詢列表
from 表
[join type join 表2
on 連接條件
where 篩選條件
group by 分組字段
having 分組后的篩選
order by]
limit offset,size;

offset:從0開始的起始索引,若省略默認從第一條開始
size:要顯示的條目個數

執行順序:from > join > on > where > group by > having > select > order by > limit (limit語法和執行都在最后)

limit分頁公式：

select * from 表 limit (page-1)*size,size;(要顯示的頁數為page,每頁的條目數為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;

九、聯合查詢

定義：將多條查詢語句的結果合并成一個結果。

語法：查詢語句1 union 查詢語句2 unin ... 查詢語句n

應用場景：當要查詢的結果來自多個沒有連接關系的表,但查詢的信息一致時，最適合使用union。

注意事項：

• 要求多條查詢語句的查詢列數是一致的

• 要求多條查詢語句查詢的每一列的類型和順序最好一致.

• union默認去重,如果使用union all可以關閉去重

• 大多數情況下，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%';

關于“MySQL中DQL數據查詢語句怎么寫”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。