DQL命令查詢資料實現方法詳解

2022-09-26 14:06:19

一、SELECT語法-排序

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
	[ left|out|inner  join  table_name2 ]    //多表連線查詢
    [ WHERE  … ]   	//指定結果需滿足的條件
    [ GROUP BY …]	//指定結果按照哪幾個欄位來分組
    [ HAVING …]	//過濾分組的記錄必須滿足的次要條件
    [ ORDER BY… ]	//指定查詢記錄按一個或者多個條件排序
	[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  //分頁查詢

排序可以是升序（ASC）或者降序（DESC）,如果不指定ASC或DESC，結果集預設按ASC升序排序:

如以下

//把成績都降低10%後加5分，再查詢及格成績，並按照成績從高到低排序。
SELECT studentno AS 學生編號,(studentresult*0.9+5 ) AS 綜合成績
FROM `result`
WHERE (`studentresult`*0.9+5) >=60
ORDER BY studentresult DESC;

//把成績都降低10%後加5分，再查詢及格成績，並按照成績從高到低排序,如果成績
//相同，再按照課程編號進行排序。
SELECT studentno AS 學生編號,(studentresult*0.9+5 ) AS 綜合成績
FROM `result`
WHERE (`studentresult`*0.9+5) >=60
ORDER BY studentresult DESC,subjectno;

二、SELECT語法-分頁查詢

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
	[ left|out|inner  join  table_name2 ]    //多表連線查詢
    [ WHERE  … ]   	//指定結果需滿足的條件
    [ GROUP BY …]	//指定結果按照哪幾個欄位來分組
    [ HAVING …]	//過濾分組的記錄必須滿足的次要條件
    [ ORDER BY… ]	//指定查詢記錄按一個或者多個條件排序
    [ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  //分頁查詢
	offset://偏移量 
	row_count://顯示的記錄數
	LIMIT //LIMIT 子句：MySQL查詢語句中使用LIMIT子句限制結果集
	如：SELECT studentno,studentresult 
		FROM result
		WHERE subjectno=1 AND YEAR(examdate)=2019
		ORDER BY studentresult DESC
		LIMIT 5 //限制顯示的結果集的行數,只顯示5條

三、SELECT語法-分組查詢

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    //多表連線查詢
    [ WHERE  … ]   	//指定結果需滿足的條件
    [ GROUP BY …]	//指定結果按照哪幾個欄位來分組
    [ HAVING …]	//過濾分組的記錄必須滿足的次要條件
    [ ORDER BY… ]	//指定查詢記錄按一個或者多個條件排序
    [ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  //分頁查詢
GROUP BY作用：使用GROUP BY關鍵字對查詢結果分組
	對所有的資料進行分組統計
	分組的依據欄位可以有多個，並依次分組
	與HAVING結合使用，進行分組後的資料篩選
GROUP BY子句經常和聚合函數結合使用完成分組統計功能

四、WHERE與HAVING對比

WHERE子句

用來篩選 FROM 子句中指定的操作所產生的行

GROUP BY子句

用來分組 WHERE 子句的輸出

HAVING子句

用來從分組的結果中篩選行

五、子查詢

基本語法：

SELECT … FROM 表名 WHERE 欄位X 比較運運算元(子查詢)

1.外層查詢稱為父查詢，圓括號中的查詢稱為子查詢。

2.執行SQL語句時，先執行子查詢，返回所有來自子查詢的結果，再執行外圍的父查詢，返回查詢的最終結果。

3.將子查詢和比較運運算元聯合使用，必須保證子查詢返回的值不能多於一個。

1、IN子查詢

IN後面的子查詢可以返回多條記錄。

SELECT … FROM 表名 WHERE 欄位X IN(子查詢)

2、EXISTS子查詢

EXISTS 子查詢：帶有EXISTS的子查詢不返回任何記錄的資料，只返回邏輯值“True”或“False”。

有結果為“True”，執行語句；無結果為“False”，不執行語句。

子查詢有返回結果： EXISTS子查詢結果為TRUE，執行外層查詢；

子查詢無返回結果： EXISTS子查詢結果為FALSE, 外層查詢不執行；

SELECT …… FROM 表名 WHERE EXISTS(子查詢);

六、多表連線查詢

1、內連線

內連線使用比較運運算元根據每個表的通用列中的值匹配兩個表中的行。

SELECT   欄位列表   FROM   表1  INNER JOIN  表2 ON    表1.通用欄位名=表2.通用欄位名
SELECT   欄位列表 FROM   表1 ,表2 WHERE    表1.通用欄位名=表2.通用欄位名

需要注意的是：

如果兩個表中有相同的欄位名，必須在列名前加表名。否則將出現錯誤程式碼：1052 Column ‘studentno’ in field

list is ambiguous

查詢學生的學號、姓名和成績，並按照成績降序排列
//第一種寫法
SELECT s.studentno,s.studentname,r.studentresult FROM 
student AS s INNER JOIN  result AS r
ON s.studentno=r.studentno ORDER BY r.studentresult DESC
//第二種寫法
SELECT s.studentno,s.studentname,r.studentresult FROM 
student AS s,result AS r
WHERE s.studentno=r.studentno
ORDER BY r.studentresult DESC

2、多表內連線

ELECT   欄位列表 FROM   表1 join 表2 on 表1.通用欄位名=表2.通用欄位名 
join 表3 on 表1.通用欄位名=表3.通用欄位名
join 表4 on 表4.通用欄位名=表3.通用欄位名;

3、左外連線

左表(表1)的記錄將會全部表示出來，而右表(表2)只會顯示符合匹配條件的記錄。如圖：

匹配，返回到結果集

無匹配，NULL值返回到結果集

SELECT 欄位列表 FROM 表1 LEFT JOIN 表2 ON 表1.通用欄位名=表2.通用欄位名

4、右外連線

右外連線：右表(表2)的記錄將會全部表示出來，而左表(表1)只會顯示符合匹配條件的記錄。如圖：