MySQL之DQL查詢數(shù)據(jù)

四、DQL查詢數(shù)據(jù)

1.DQL（Data Query Language）：數(shù)據(jù)查詢語言

所有的查詢操作都要用它 Select。
簡單的查詢，復(fù)雜的查詢它都能做。
是數(shù)據(jù)庫中最核心的語言，最重要的語句；
也是使用頻率最高的語句。
school測試數(shù)據(jù)庫及grade、result、student、subject表的創(chuàng)建及添加數(shù)據(jù)的代碼，可下載使用：https://share.weiyun.com/4QiyVQfd，也可以直接復(fù)制下方。

-- 創(chuàng)建一個(gè)school數(shù)據(jù)庫
create database if not exists `school`;
use `school`;
-- 創(chuàng)建學(xué)生表
drop table if exists `student`;
create table `student`(
  `StudentNo` int(4) not null comment '學(xué)號',
  `LoginPwd` varchar(20) default null,
  `StudentName` varchar(20) default null comment '學(xué)生姓名',
  `sex` tinyint(1) default null comment '性別，0或1',
  `GradeId` int(11) default null comment '年級編號',
  `phone` varchar(50) not null comment '聯(lián)系電話，允許為空',
  `address` varchar(255) not null comment '地址，允許為空',
  `BornDate` datetime default null comment '出生時(shí)間',
  `email` varchar (50) not null comment '郵箱賬號，允許為空',
  `IdentityCard` varchar(18) default null comment '身份證號',
  primary key (`StudentNo`),
  unique key `identitycard`(`IdentityCard`),
  key `email` (`email`)
)engine=myisam default charset=utf8;

-- 創(chuàng)建年級表
drop table if exists `grade`;
create table `grade`(
  `GradeId` int(11) not null auto_increment comment '年級編號',
  `GradeName` varchar(50) not null comment '年級名稱',
  primary key (`GradeId`)
) engine=innodb auto_increment = 6 default charset = utf8;

-- 創(chuàng)建科目表
drop table if exists `subject`;
create table `subject`(
  `SubjectNo`int(11) not null auto_increment comment '課程編號',
  `SubjectName` varchar(50) default null comment '課程名稱',
  `ClassHour` int(4) default null comment '學(xué)時(shí)',
  `GradeId` int(4) default null comment '年級編號',
  primary key (`SubjectNo`)
)engine = innodb auto_increment = 19 default charset = utf8;

-- 創(chuàng)建成績表
drop table if exists `result`;
create table `result`(
  `StudentNo` int(4) not null comment '學(xué)號',
  `SubjectNo` int(4) not null comment '課程編號',
  `ExamDate` datetime not null comment '考試日期',
  `StudentResult` int (4) not null comment '考試成績',
  key `SubjectNo` (`SubjectNo`)
)engine = innodb default charset = utf8;

-- 插入學(xué)生數(shù)據(jù)（ 這里只添加了2行，其余自行添加）
insert into `student` (`StudentNo`,`LoginPwd`,`StudentName`,`sex`,`GradeId`,`phone`,`address`,`BornDate`,`email`,`IdentityCard`)
values(1000,'123456','張偉',0,2,'13800001234','北京朝陽','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','趙強(qiáng)',1,3,'13800002222','廣東深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成績數(shù)據(jù) （這里僅插入了一組，其余自行添加）
insert into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
values(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年級數(shù)據(jù)
insert into `grade` (`GradeId`,`GradeName`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'預(yù)科班');

-- 插入科目數(shù)據(jù)
insert into `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeId`) values(1,'高等數(shù)學(xué)-1',110,1),
(2,'高等數(shù)學(xué)-2',110,2),
(3,'高等數(shù)學(xué)-3',100,3),
(4,'高等數(shù)學(xué)-4',130,4),
(5,'C語言-1',110,1),
(6,'C語言-2',110,2),
(7,'C語言-3',100,3),
(8,'C語言-4',130,4),
(9,'Java程序設(shè)計(jì)-1',110,1),
(10,'Java程序設(shè)計(jì)-2',110,2),
(11,'Java程序設(shè)計(jì)-3',100,3),
(12,'Java程序設(shè)計(jì)-4',130,4),
(13,'數(shù)據(jù)庫結(jié)構(gòu)-1',110,1),
(14,'數(shù)據(jù)庫結(jié)構(gòu)-2',110,2),
(15,'數(shù)據(jù)庫結(jié)構(gòu)-3',100,3),
(16,'數(shù)據(jù)庫結(jié)構(gòu)-4',130,4),
(17,'C#基礎(chǔ)',130,1);

2.指定查詢字段

1）語法：

SELECT 字段1,字段2,… FROM 表

例：

--查詢學(xué)術(shù)表的全部信息
SELECT * FROM student
--查詢表中的指定字段
SELECT `StudentNo`,`StudentName` FROM student
--用AS給結(jié)果取別名
SELECT `StudentNo`AS 學(xué)號,`StudentName` AS 學(xué)生姓名 FROM student AS 學(xué)生表
--運(yùn)用函數(shù) Concat(a,b)
SELECT Concat('姓名',StudentName) AS 新名字 FROM student

有的時(shí)候，列名字不是那么見名知意，可以用AS起別名：字段名/表名 as 別名。
2）去重：distinct
作用：去除SELECT查詢出來的結(jié)果中重復(fù)的數(shù)據(jù)，重復(fù)的數(shù)據(jù)只顯示一條。
例：查詢參加考試的同學(xué)及其成績。

SELECT * FROM result --查詢?nèi)康目荚嚦煽?SELECT `StudentNo` FROM result --查詢參加考試的同學(xué)
SELECT DISTINCT `StudentNo` FROM result --查詢參加考試的同學(xué)，去除重復(fù)的數(shù)據(jù)

3）數(shù)據(jù)庫的列（表達(dá)式）
例：

SELECT VERSION() --查詢系統(tǒng)版本（函數(shù)）
SELECT 100*3-1 AS 計(jì)算結(jié)果 --用來計(jì)算（表達(dá)式）
SELECT @@auto_increment_increment --查詢自增步數(shù)（變量）
--使學(xué)生的考試成績+1
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FORM result

數(shù)據(jù)庫中的表達(dá)式：文本值、列、NULL、函數(shù)、計(jì)算表達(dá)式、系統(tǒng)變量······
語法：

select 表達(dá)式 from 表

3.where 條件子句

作用：檢索數(shù)據(jù)中符合條件的值。
搜索的條件由一個(gè)或多個(gè)表達(dá)式組成，結(jié)果為布爾值。
1）邏輯運(yùn)算符

運(yùn)算符 語法 描述
and && a and b a&&b 邏輯與，兩個(gè)都為真，結(jié)果為真。
or a or b 邏輯或，其中一個(gè)為真，則結(jié)果為真。
Not ! not a !a 邏輯非，真為假，假為真。

注：or也可以用兩個(gè)|來表示，盡量使用英文字母而不是符號。
例：

--查詢考試成績在95-100之間的學(xué)生學(xué)號
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
--模糊查詢（區(qū)間）
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
--除了1000號學(xué)生之外的同學(xué)成績
SELECT StudentNo,`StudentResult` FROM result
WHERE NOT StudentNo=1000

2）模糊查詢：比較運(yùn)算符

運(yùn)算符 語法 描述
IS NULL a is null 如果操作符為NULL，結(jié)果為真。
IS NOT NULL a is not null 如果操作符不為NULL，結(jié)果為真。
BETWEEN a between b and c 若a在b和c之間，則結(jié)果為真。
LIKE a like b SQL匹配，如果a匹配b，則結(jié)果為真。
IN a in (a1,a2,…) 假設(shè)a在a1或a2或…其中的某一個(gè)值中，結(jié)果為真

例：

--查詢姓劉的同學(xué)
--與like結(jié)合，%代表0到任意個(gè)字符，_代表一個(gè)字符
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉%'
--查詢姓劉的同學(xué)，名字后面只有一個(gè)字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉_'
--查詢姓劉的同學(xué)，名字后面有兩個(gè)字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉__'
--查詢姓名中有嘉字的同學(xué)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%'
--in用于查詢具體的一個(gè)或多個(gè)值
--查詢1001，1002，1003號學(xué)生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
--查詢地址為空的同學(xué)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=' ' OR address IS NULL
--查詢沒有出生日期的同學(xué)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

4.聯(lián)表查詢

1）JOIN對比

SQL JOIN圖示總結(jié)

百度搜索七種join理論

七種JOIN理論

例：查詢參加了考試的同學(xué)（學(xué)號、姓名、科目編號、分?jǐn)?shù)）。
思路：Ⅰ分析需求，分析查詢的字段來自哪些表（連接查詢）。
Ⅱ確定使用哪種查詢。共7種
確定交叉點(diǎn)（student和result這兩個(gè)表中哪個(gè)數(shù)據(jù)是相同的）。
判斷的條件：學(xué)生表中的StudentNo = 成績表中的StudentNo。

--Inner Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
--Right Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo
--Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo

注：有時(shí)使用AS可省略。

操作 描述
Inner join 如果表中至少有一個(gè)匹配，就返回匹配的值。
left join 會從左表中返回所有的值，即使右表中沒有匹配。
right join 會從右表中返回所有的值，即使左表中沒有匹配。

例：查詢?nèi)笨嫉耐瑢W(xué)。

SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL

on與where：
join (連接的表) on (判斷的條件)：連接查詢
where：等值查詢

思考題：查詢了參加考試的同學(xué)信息（學(xué)號、學(xué)生姓名、科目名、分?jǐn)?shù)）。
SELECT s.StudentNo,StudentName,SubjectName,`StudentResult`
FROM student
RIGHT JOIN result r
ON r.SttudentNo = s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo

總結(jié)：Ⅰ要查詢哪些數(shù)據(jù)。select…
Ⅱ從哪幾個(gè)表中查。FROM 表 XXX Join 連接的表 on 交叉條件
Ⅲ假設(shè)存在一種多張表查詢，先查詢兩張表然后再慢慢增加。
2）自連接
school數(shù)據(jù)庫中的分類表category的創(chuàng)建和導(dǎo)入數(shù)據(jù)的代碼，可下載使用：https://share.weiyun.com/Wp0LerVu，也可以直接復(fù)制下方。

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', '信息技術(shù)'),
('3', '1', '軟件開發(fā)'),
('5', '1', '美術(shù)設(shè)計(jì)'),
('4', '3', '數(shù)據(jù)庫'),
('8', '2', '辦公信息'),
('6', '3', 'web開發(fā)'),
('7', '5', 'ps技術(shù)');
上面代碼執(zhí)行結(jié)果

自己的表和自己的表連接，核心：一張表拆為兩張一樣的表即可。
父表：

categoryId categoryName
2 信息技術(shù)
3 軟件開發(fā)
5 美術(shù)設(shè)計(jì)

子表：

pid categoryId categoryName
3 4 數(shù)據(jù)庫
2 8 辦公信息
3 6 web開發(fā)
5 7 ps技術(shù)

操作：查詢父類對應(yīng)的子類關(guān)系。

--查詢父子信息：把一張表看成兩個(gè)一模一樣的表
SELECT a.`categoryName` AS '父欄目',b.`categoryName` AS '子欄目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryId` = b.`pid`

結(jié)果表的樣式：

父類 子類
信息技術(shù) 辦公信息
軟件開發(fā) 數(shù)據(jù)庫
軟件開發(fā) web開發(fā)
美術(shù)設(shè)計(jì) ps技術(shù)

3）聯(lián)表查詢練習(xí)
①查詢科目所屬的年級（科目名稱、年級名稱）。

SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeId` = g.`GradeId`

②查詢學(xué)員所屬的年級（學(xué)號、學(xué)生姓名、年級名稱）。

SELECT StudentNo,StudentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeId` = g.`GradeId`
思考題改：查詢參加了數(shù)據(jù)庫結(jié)構(gòu)-1考試的學(xué)生信息（學(xué)號、學(xué)生姓名、科目名、分?jǐn)?shù)）。
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE SubjectName = '數(shù)據(jù)庫結(jié)構(gòu)-1'

5.分頁和排序

1）排序
方式：升序ASC，降序DESC。
語法：

ORDER BY 通過哪個(gè)字段排序,排序方式

例：以上例思考題改為基礎(chǔ)，將查詢的信息按成績排序。

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE SubjectName = '數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY StudentResult ASC

1）分頁
分頁的原因：緩解數(shù)據(jù)庫的壓力，給用戶的體驗(yàn)更好。用瀑布流的話可以不分頁。
語法：

limit 起始值,頁面的大小

例：以上例思考題改為基礎(chǔ)，頁面大小為5，顯示第一頁的內(nèi)容。

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE SubjectName = '數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY StudentResult ASC
LIMIT 0,5

網(wǎng)頁應(yīng)用顯示：當(dāng)前頁,總的頁數(shù),頁面的大小
公式：ⅠpageSize：頁面大小
Ⅱ(n-1)*pageSize：初始值
Ⅲn：當(dāng)前頁 = 數(shù)據(jù)總數(shù)/頁面大小

思考題：查詢JAVA第一學(xué)年課程成績排名前十，并且分?jǐn)?shù)要大于80的學(xué)生信息（學(xué)號、姓名、課程名稱、分?jǐn)?shù)）。
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE SubjectName = 'JAVA第一學(xué)年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10

6.子查詢

where（這個(gè)值是計(jì)算出來的）
本質(zhì)：在where語句中嵌套一個(gè)子查詢語句。
例：查詢數(shù)據(jù)庫結(jié)構(gòu)-1的所有考試結(jié)果（學(xué)號、科目編號、成績），降序排列。
方式一：使用查詢連接

SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON  r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY StudentResult DESC

方式二：使用子查詢（由里及外）

SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` 
WHERE SubjectNo = (
  SELECT SubjectNo FROM `subject`
  WHERE SubjectName = '數(shù)據(jù)庫結(jié)構(gòu)-1'
)
ORDER BY StudentResult DESC

例：查詢課程為高等數(shù)學(xué)-2且分?jǐn)?shù)不小于80的同學(xué)的學(xué)號和姓名。
方式一：

SELECT s.StudentNo,StudentName
FORM student s
INNER JOIN result r
ON s.StuentNo = r.StuentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `StudentName` = '高等數(shù)學(xué)-2' AND StudentResult>=80

方式二：

SELECT s.`StudentNo`,`StudentName`
FORM student s
INNER JOIN result r
ON r.StuentNo = s.StuentNo
WHERE `StudentResult`>=80 AND `SubjectNo` = (
  SELECT SubjectNo FROM `subject`
  WHERE `SubjectName` = '高等數(shù)學(xué)-2'                                                                                                                                                                    
)
--進(jìn)一步改變
SELECT s.StudentNo,StudentName FORM student WHERE StudentNo IN (
  SELECT StudentNo FROM result WHERE StudentResult>=80 AND SubjectNo = （
    SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等數(shù)學(xué)-2'
  ）
)

練習(xí)：查詢C語言-1課程中前5名同學(xué)的成績信息（學(xué)號、姓名、分?jǐn)?shù)）。
SELECT完整語法總結(jié)：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1] [,table.field2[as alias2]] [,…]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2] --聯(lián)合查詢
  [WHERE…] --指定結(jié)果需滿足的條件
  [GROUP BY…] --指定結(jié)果按照哪幾個(gè)字段來分組
  [HAVING] --過濾分組的記錄必須滿足的次要條件
  [ORDER BY…] --指定查詢記錄按一個(gè)或多個(gè)條件排序
  [LIMIT {[offest.] row_count | row_countOFFEST offset}]
  --指定查詢的記錄從哪條至哪條

注：[]括號代表可選的,{}括號代表必選的。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容