本篇博客（第五篇）重点：

掌握 MySQL 查询数据语句（数据查询语言（DQL））掌握 MySQL 查询子句多表查询、分页查询

内容较多，以项目实例形式学习：

MySchool 数据库： myschool数据库

数据表设计，了解表之间的业务逻辑关系： myschool数据表设计

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 | out | inner join  table_name2 ]                       #联合查询 
    [ WHERE … ]                                                    #指定结果需满足的条件 
    [ GROUP BY … ]                                                 #指定结果按照哪几个字段来分组 
    [ HAVING … ]                                                   #过滤分组的记录必须满足的次要条件 
    [ ORDER BY … ]                                                  #指定查询记录按一个或者多个条件排序 
    [ LIMIT { [ offset, ] row_count | row_count OFFSET offset } ]; #指定查询的记录从哪条至哪条

[] 括号代表可选的；
{} 括号代表必须的；
# MySQL语句中的注释符，也可以用 /*该处为注释*/

SELECT 语法1_SELECT ... FROM TABLE

SELECT   [ALL | DISTINCT]
{ * | table.* | [ table.field1 [as alias1] [,table.field2 [as alias2]] [, …] ] } 
FROM table_name [ as table_ alias ]                                # 本 part 讲解这部分语句
    [ left | out | inner join  table_name2 ]
    [ WHERE … ]
    [ GROUP BY … ]
    [ HAVING … ]
    [ ORDER BY … ]
    [ LIMIT { [ offset, ] row_count | row_count OFFSET offset } ];

指定查询字段

查询表中所有的数据列结果，采用 “*” 符号（不建议使用，效率较低）
- 如： select * from student;
指定查询的结果数据列
- 如只查询 student 表中的学号、姓名、电话： SELECT StudentNo , StudentName , Phone FROM student;
- 如区分连接查询时两个表有同名的字段，可指定该字段属于哪个表（student.StudentNo）： SELECT student.StudentNo , StudentName, StudentResult FROM student , result ;

AS 子句作为别名

AS子句作用：

可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另外一个新名称来代替

AS子句用法示例：

SELECT StudentNo AS “学号” FROM student;
SELECT a.StudentNo FROM student AS a;
SELECT Phone+1  AS Tel FROM student;

注意 AS 可省略不写

DISTINCT 关键字的使用

DISTINCT 关键字作用：去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条

语法：

SELECT DISTINCT 字段名1, 字段名2... FROM 表名

注意 ALL 关键字是默认的，返回所有的记录，与 DISTINCT 关键字相反

示例

#查询成绩表中的所包含的课程ID 
SELECT DISTINCT SubjectNo FROM result;

使用表达式的列

数据库中的表达式

表达式一般由文本值、列值、NULL、函数和操作符等组成
应用场景：
- SELECT 语句返回结果列中使用
- SELECT 语句的 ORDER BY、 HAVING 等子句中使用
- DML 语句中的 where 条件语句中使用表达式

在 SQL 语句中使用表达式

返回的列中使用，如：

SELECT version() , 100*3 #返回MySQL版本和计算结果
SELECT SubjectName “课程名称”, ClassHour+10 AS “新学时” FROM subject; #给返回结果中的课时都加10个课时

避免 SQL 返回结果中包含 “.” , “*” 和括号等干扰开发语言程序，如：

SELECT version() as MySQL_V , 123.44*100 AS EXPRESSION;
#返回结果不会与后台开发程序发生混淆

查询课程表（subject）的所有记录

返回字段名称使用别称
返回课程名称(SujectName) ,总课时(SubjectHour)

返回10天上完课程的均课时（ClassHour/10）

#查询课程名称、总课时及每天10课时计算的天数
SELECT  
SubjectName as "课程名称",
ClassHour as "总课时", 
(ClassHour/10)  as "均课时/天" 
FROM  subject;

SELECT语法2_where条件语句

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 } ];

用于检索数据表中符合条件的记录
搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
搜索条件的组成
- 逻辑操作符
- 比较操作符

实例要求：查询在80-90分之间的所有成绩记录：

SELECT * FROM result
WHERE StudentResult >= 80 AND StudentResult <= 90;

逻辑操作符

操作符名称	语法	描述
AND（或&&）	a AND b （或 a && b）	逻辑与，同时为真结果才为真
OR（或双竖杆）	a OR b (或 a双竖杆b)	逻辑或，只要一个为真，则结果为真
XOR（或^）	a XOR b （或 a^b）	逻辑异或，不同为真,相同为假
NOT（或!）	NOT a （或 !a）	逻辑非，若操作数为假，结果则为真

比较操作符

操作符名称	语法	描述
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... 中的某一个，则结果为真

注意

数值数据类型的记录之间才能进行算术运算
相同数据类型的数据之间才能进行比较

NULL空值条件查询

NULL：

NULL 代表“无值”
区别于零值0和空符串“”
只能出现在定义允许为NULL的字段
须使用 IS NULL 或 IS NOT NULL 比较操作符去比较

实例查找地址不为空的学生信息：

SELECT * FROM  student
WHERE Address IS NOT NULL;

BETWEEN AND 范围查询

根据一个范围值来检索语法：

SELECT 字段列1，字段2 , … FROM 表名 WHERE 字段X BETWEEN 值1 AND 值2

等同于 >= 和 <= 联合使用：

#查询课程表中课时在110和120之间的所有记录 
SELECT * FROM subject WHERE ClassHour BETWEEN 110 AND 120; 
#等同于: 
SELECT * FROM subject WHERE ClassHour >= 110 AND ClassHour <=120;

使用 IN 进行范围查询

在 WHERE 子句中使用 IN 进行范围查询

语法：

SELECT 字段列1，字段2 , … FROM 表名 WHERE 字段X IN (值1,值2,值3, … )

查询的字段X 的值，至少与括号中的一个值是相同的
多个值之间用英文逗号隔开

示例:

SELECT * FROM subject where ClassHour=100 OR ClassHour=110 OR ClassHour=120;    #普通组处理方式 
SELECT * FROM subject where ClassHour IN ( 100, 110,120 ); #使用IN进行查询方式，更为简洁，效率更高

LIKE 模糊查询

在 WHERE 子句中，使用 LIKE 关键字进行模糊查询

与 “%” 一起使用，表示匹配0或任意多个字符
与 “_” 一起使用，表示匹配单个字符

示例一：

#查询包含“数学”的所有课程 
SELECT * FROM subject WHERE SubjectName LIKE "%数学%"; 
#查询所有姓名为 “李**” 三个字的学生信息 
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE "李__";

示例二：

#查询所有姓“李”的学生所有成绩
SELECT StudentNo  AS '"李"同学的学号', StudentResult AS '"李"同学的成绩' FROM result
WHERE StudentNo IN (SELECT StudentNo FROM student where StudentName like "李%");

查询结果：

“李”同学的学号	"李"同学的成绩
1001	76
1001	93
……	……

SELECT语法3_连接查询（多表查询）

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 } ];

连接查询（多表查询）

连接查询：如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询

分类包括：

内连接( inner join )
- 等值和非等值的连接查询
- 自身连接查询
外连接( out join )：
- 左连接（ LEFT JOIN ）
- 右连接（ RIGHT JOIN )

1.内连接查询：

INNER JOIN 内连接在表中至少一个匹配时，则返回记录

SELECT 字段1,字段2, … FROM table_1
INNER JOIN table_2 ON table_1.字段x = table_2.字段y; 
# INNER JOIN 与 JOIN 是相同的
# 如table_1 中的行在 table_2 中没有匹配，则不返回

实例：

#要求：从 subject 和 grade 数据表查询课程名称和所属年级名称
SELECT SubjectName,GradeName FROM subject INNER JOIN grade On
subject.GradeID = grade.GradeID;

2.等值和非等值的连接查询：

与单表查询类似，都是 SELECT 语句
把多个表放到 FROM 后，并用逗号隔开
可使用 AS 关键字取别名，便于引用
如无重名查询字段则可省略数据表的指定

实例：从 subject 和 grade 数据表查询课程名称和所属年级名称

#非等值连接查询 
SELECT SubjectName,GradeName FROM subject,grade; #返回记录数为两表记录数的乘积  
#等值查询 
SELECT SubjectName,GradeName FROM subject,grade WHERE subject.GradeID = grade.GradeID; #等效于内连接

3.自身连接查询

自连接查询数据表与自身进行连接

实例从一个包含栏目 ID,栏目名称和父栏目 ID 的表中，查询父栏目名称和其子栏目名称。

#表结构语句
create table if not exists category(
    categoryId int(10)  unsigned  not null  auto_increment,
    pid  int(10) not null,
    categoryName varchar(32) not null,
    primary key(categoryId)
);

insert into category values
(2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"Photoshop基础"),
(6,2,"色彩搭配学"),
(7,3,"PHP基础"),
(8,3,"一起学JAVA");

SELECT  c1.categoryName AS "父栏目名称",c2.categoryName AS  "子栏目名称" 
FROM category AS c1,category AS c2 
WHERE c1.categoryId = c2.pid;

4.左外连接（LEFT JOIN）

从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行；

SELECT 字段1,字段2, … FROM table_1  
LEFT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

5.右外连接(RIGHT JOIN)

从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行；

SELECT 字段1,字段2, … FROM table_1
RIGHT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

实例：外连接查询课程表

#LEFT JOIN 左连接
SELECT SubjectName,ClassHour,GradeName FROM subject as s left join grade as g  ON  g.GradeID = S.GradeID; 
#RIGHT JOIN 右连接
SELECT SubjectName,ClassHour,GradeName FROM subject as s Right join grade as g  ON  g.GradeID = S.GradeID;

不同的SQL JOIN对比

操作符名称	描述
INNER JOIN ( JOIN )	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表中返

实例：查询学生表（student)的学号(studentNo),姓名(StudentName)和所在年级 (GradeName)
分别使用：左连接查询方式，右连接查询方式，内连接查询方式

#查询学生的学号,姓名和所在年级；
#左连接
SELECT s.StudentNo,s.StudentName,g.GradeName FROM  student AS  s LEFT JOIN grade AS  g  ON s.GradeID = G.GradeID;  
#右连接
SELECT s.StudentNo,s.StudentName,g.GradeName FROM  student AS  s RIGHT JOIN grade AS  g  ON s.GradeID = G.GradeID;  
#内连接
SELECT s.StudentNo,s.StudentName,g.GradeName FROM  student AS s INNER JOIN grade AS  g  ON s.GradeID = G.GradeID;

SELECT语法4_查询记录按条件排序

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 } ];

ORDER BY 排序查询：

对 SELECT 语句查询得到的结果，按某些字段进行排序
与 DESC 或 ASC 搭配使用，默认为 ASC

实例查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列

SELECT studentresult,subjectname
FROM result INNER JOIN subject 
ON result.SubjectNo = subject.SubjectNo
WHERE subject.SubjectName='数据库结构-1'
ORDER BY studentresult DESC;

SELECT语法5_分页查询、子查询、MySQL的统计函数

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 } ];#指定查询的记录从哪条至哪条

MySQL的LIMIT

LIMIT [m,]n 或 LIMIT n OFFSET m

限制SELECT返回结果的行数
m 制定第一个返回记录行的偏移量
n 制定返回记录行的最大数目

注意：

m不指定则偏移量为0，从第一条开始返回前n条记录
LIMIT 常用于分页显示

示例：

SELECT * FROM `result` LIMIT 5    #返回前5条记录 
SELECT * FROM `result` LIMIT 5,10 #返回6-15条记录（5:第一个返回记录行为5；10:每页显示10条数据）

分页查询 实例一：查询所有课程《数据库结构-2》的考试成绩，并按照由高到低显示，同时把该成绩对应的学生的学号、姓名打印出来要求：第一次查询前5条记录，第二次查询6-10条记录

SELECT s.studentno,s.studentname,c.subjectname,r.studentresult
FROM student s JOIN result r
ON s.StudentNo=r.StudentNo
JOIN SUBJECT c
ON r.SubjectNo=c.SubjectNo
WHERE c.subjectname="数据库结构-2"
ORDER BY r.StudentResult DESC LIMIT 0,5;  #或 limit 5,5

子查询

什么是子查询？

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句。

实例：查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名

SELECT    
s.StudentNo,StudentName  
FROM  student AS s  
WHERE   
studentNo  
IN 
 (  
    SELECT  
    StudentNO 
    FROM  
        result 
    WHERE  
    SubjectNo =  (SELECT SubjectNo FROM subject WHERE SubjectName = "高等数学-2") 
    AND   
    StudentResult  >=  80  
 );

注意：

嵌套查询可由多个子查询组成，求解的方式是由里及外
子查询返回的结果一般都是集合，故而建议使用 IN 关键字

MySQL的统计函数

常用统计函数：

函数名称	描述
COUNT()	返回满足 SELECT 条件的记录总和数，如 SELECT COUNT(*)
SUM()	返回数字字段或表达式列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN()	可以为数值字段、字符字段或表达式列作统计，返回最小的值

实例：

#常用统计函数：COUNT(),MAX(),MIN(),AVG(),SUM

SELECT COUNT(StudentResult) as "成绩总个数", MAX(StudentResult) as "最高分",MIN(StudentResult) as "最低分",AVG(StudentResult) as "平均分"
FROM result;

SELECT语法6_GROUP BY分组和 HAVING筛选

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 结合使用，进行分组后的数据筛选

实例按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示

SELECT 
    s.SubjectName as "课程名", MAX(StudentResult) as "最高分" , MIN(StudentResult) as "最低分" , AVG(StudentResult) AS "平均分" 
FROM 
    result as r 
LEFT JOIN 
    subject as s ON s.SubjectNo = r.SubjectNo
GROUP BY r.SubjectNo 
HAVING AVG(StudentResult) >= 60 ;

——END

Qingquan-Li / blog

MySQL_5 使用DQL命令查询数据_DQL（SELECT） #24