MySQL學習04（DQL查詢）

2021 年 2 月 19 日
筆記
資料庫

DQL查詢

DQL語言

DQL( Data Query Language 數據查詢語言 )

查詢資料庫數據 , 如SELECT語句
簡單的單表查詢或多表的複雜查詢和嵌套查詢
是資料庫語言中最核心,最重要的語句
使用頻率最高的語句

SELECT語法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
 [left | right | inner join table_name2]  -- 聯合查詢
 [WHERE ...]  -- 指定結果需滿足的條件
 [GROUP BY ...]  -- 指定結果按照哪幾個欄位來分組
 [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
 [ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
 [LIMIT {[offset,]row_count | row_countOFFSET offset}];
  -- 指定查詢的記錄從哪條至哪條

[ ] 括弧代表可選的 , { }括弧代表必選得

指定查詢欄位

-- 查詢所有學生資訊
SELECT * FROM student;

-- 查詢指定列(學號 , 姓名)
SELECT studentno,studentname FROM student;

AS子句作為別名

作用：

可給數據列取一個新別名
可給表取一個新別名
可把經計算或總結的結果用另一個新名稱來代替

-- 這裡是為列取別名(當然as關鍵詞可以省略)
SELECT studentno AS 學號,studentname AS 姓名 FROM student;

-- 使用as也可以為表取別名
SELECT studentno AS 學號,studentname AS 姓名 FROM student AS s;

-- 使用as,為查詢結果取一個新名字
-- CONCAT()函數拼接字元串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT關鍵字的使用

作用 : 去掉SELECT查詢返回的記錄結果中重複的記錄 ( 返回所有列的值都相同 ) , 只返回一條

-- 查看哪些同學參加了考試(學號) 去除重複項
SELECT * FROM result; -- 查看考試成績
SELECT studentno FROM result; -- 查看哪些同學參加了考試
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重複項 , (默認是ALL)

使用表達式的列

資料庫中的表達式 : 一般由文本值 , 列值 , NULL , 函數和操作符等組成

應用場景 :

SELECT語句返回結果列中使用
SELECT語句中的ORDER BY , HAVING等子句中使用
DML語句中的 where 條件語句中使用表達式

-- selcet查詢中可以使用表達式
SELECT @@auto_increment_increment; -- 查詢自增步長
SELECT VERSION(); -- 查詢版本號
SELECT 100*3-1 AS 計算結果; -- 表達式

-- 學員考試成績集體提分一分查看
SELECT studentno,StudentResult+1 AS '提分後' FROM result;

避免SQL返回結果中包含 ‘ . ‘ , ‘ * ‘ 和括弧等干擾開發語言程式.

where條件語句

作用：用於檢索數據表中符合條件的記錄

搜索條件可由一個或多個邏輯表達式組成 , 結果一般為真或假.

邏輯操作符

操作符名稱	語法	描述
AND 或 &&	a AND b 或 a && b	邏輯與，同時為真結果才為真
OR 或 \|\|	a OR b 或 a\|\|b	邏輯或，只要一個為真，則結果為真
NOT 或！	NOT a 或！a	邏輯非，若操作數為假，則結果為真！

-- 滿足條件的查詢(where)
SELECT Studentno,StudentResult FROM result;

-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

-- AND也可以寫成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;

-- 模糊查詢(對應的詞:精確查詢)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000號同學,要其他同學的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查詢：比較操作符

操作符名稱	語法	描述
IS NULL	a IS NULL	若操作符為NULL，則結果為真
IS NOT NULL	a IS NOT NULL	若操作符不為NULL，則結果為真
BETWEEN	a BETWEEN b AND c	若 a 範圍在 b 與 c 之間，則結果為真
LIKE	a LIKE b	SQL 模式匹配，若a匹配b，則結果為真
IN	a IN (a1，a2，a3，……)	若 a 等於 a1,a2….. 中的某一個，則結果為真

注意：

數值數據類型的記錄之間才能進行算術運算 ;
相同數據類型的數據之間才能進行比較 ;

-- 模糊查詢 between and \ like \ in \ null
-- LIKE

-- 查詢姓劉的同學的學號及姓名
-- like結合使用的通配符 : % (代表0到任意個字元) _ (一個字元)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉%';

-- 查詢姓劉的同學,後面只有一個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉_';

-- 查詢姓劉的同學,後面只有兩個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉__';

-- 查詢姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';

-- 查詢姓名中含有特殊字元的需要使用轉義符號 '\'
-- 自定義轉義符關鍵字: ESCAPE ':'
-- IN

-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查詢地址在北京,南京,河南洛陽的學生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛陽');

-- NULL 空

-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查詢出生日期填寫的同學
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查詢沒有寫家庭住址的同學(空字元串不等於null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

連接查詢

JOIN 對比

操作符名稱	描述
INNER JOIN	如果表中有至少一個匹配，則返回行
LEFT JOIN	即使右表中沒有匹配，也從左表中返回所有的行
RIGHT JOIN	即使左表中沒有匹配，也從右表中返回所有的行

/*
連接查詢
  如需要多張數據表的數據進行查詢,則可通過連接運算符實現多個查詢
內連接 inner join
  查詢兩個表中的結果集中的交集
外連接 outer join
  左外連接 left join
    (以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
  右外連接 right join
    (以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
   
等值連接和非等值連接
自連接
*/
-- 查詢參加了考試的同學資訊(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源於兩個類,student result,連接查詢
(2):確定使用哪種連接查詢?(內連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右連接(也可實現)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左連接 (查詢了所有同學,不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同學(左連接應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自連接

數據表與自身進行連接

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中，查詢父欄目名稱和其他子欄目名稱

-- 創建一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入數據
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');

-- 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'

排序和分頁

排序

語法 : ORDER BY

ORDER BY 語句用於根據指定的列對結果集進行排序。
ORDER BY 語句默認按照ASC升序對記錄進行排序。
如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
-- 按成績降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC

分頁

語法 :

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

好處 : (用戶體驗,網路傳輸,查詢壓力)

推導:
第一頁 : limit 0,5
第二頁 : limit 5,5
第三頁 : limit 10,5
……
第N頁 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:頁碼,pageSize:單頁面顯示條數]

-- 每頁顯示5條數據
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

-- 查詢 JAVA第一學年 課程成績前10名並且分數大於80的學生資訊(學號,姓名,課程名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10

子查詢

什麼是子查詢?

在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;

-- 查詢 資料庫結構-1 的所有考試結果(學號,科目編號,成績),並且成績降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '資料庫結構-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查詢(執行順序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
  SELECT subjectno FROM `subject`
  WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

-- 查詢課程為 高等數學-2 且分數不小於80分的學生的學號和姓名
-- 方法一:使用連接查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80

-- 方法二:使用連接查詢+子查詢
-- 分數不小於80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基礎上,添加需求:課程為 高等數學-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
  SELECT subjectno FROM `subject`
  WHERE subjectname = '高等數學-2'
)

-- 方法三:使用子查詢
-- 分步寫簡單sql語句,然後將其嵌套起來
SELECT studentno,studentname FROM student WHERE studentno IN(
  SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
 )
)

Tags: 資料庫

MySQL學習04（DQL查詢）

DQL查詢

DQL語言

SELECT語法

指定查詢欄位

AS子句作為別名

DISTINCT關鍵字的使用

使用表達式的列

where條件語句

邏輯操作符

模糊查詢：比較操作符

連接查詢

JOIN 對比

自連接

排序和分頁

排序

分頁

子查詢

什麼是子查詢?

VirMach 便宜 VPS

QNews

MySQL學習04（DQL查詢）

DQL查詢

DQL語言

SELECT語法

指定查詢欄位

AS子句作為別名

DISTINCT關鍵字的使用

使用表達式的列

where條件語句

邏輯操作符

模糊查詢 ： 比較操作符

連接查詢

JOIN 對比

自連接

排序和分頁

排序

分頁

子查詢

什麼是子查詢?

分享此文：

Related Posts

mxget 的Python實現，優雅地下載你喜歡的音樂

用Windows打造自己精簡便捷的滲透工具集

四川一景點設「單向透明」玻璃廁所 網友直呼太刺激

java的單例模式小知識點

VirMach 便宜 VPS

QNews

熱門搜尋

模糊查詢：比較操作符

四川一景點設「單向透明」玻璃廁所網友直呼太刺激