第五章 - 数据检索
第五章 - 数据检索
5.1 基本查询语句
5.1.1 查询语句格式
数据检索是指从数据库中按照预定条件查询数据，及引用相关数据进行计算而获取所需信息的过程。
查询数据是数据库操作中最常用、最重要的操作。MySQL是通过select语句查询实现数据检索的。
本章将介绍利用select语句进行单表查询、多表连接和子查询的详细操作。
select 语句是SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称之为结果集（result set）。
select语句的基本的语法格式可归纳如下：
SELECT [ALL | DISTINCT] 输出列表达式, ...
[FROM 表名1 [ , 表名2] …] /*FROM子句*/
[WHERE 条件] /*WHERE子句*/
[GROUP BY {列名 | 表达式 | 列编号}
[ASC | DESC], ... /* GROUP BY 子句*/
[HAVING 条件] /* HAVING 子句*/
[ORDER BY {列名 | 表达式 | 列编号}
[ASC | DESC] , ...] /*ORDER BY子句*/
[LIMIT {[偏移量,] 行数|行数OFFSET偏移量}] /*LIMIT子句*/
顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。
5.1.2 指定列查询
1. 使用select语句查询一个数据表
使用select语句时，首先要确定所要查询的列。当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。
SELECT 字段列表 FROM 表名
各列名之间要以逗号分隔。
【例5.1】查询teaching数据库course表中的所有数据。
mysql> use teaching;
mysql> select * from course;
查询表中的指定列。针对表中的多列进行查询，只要在select后面指定要查询的列名即可，多列之间用“，”分隔。
【例5.2】查询student表中的studentno、sname和phone数据。
mysql> select studentno,sname,phone from student;
2. 可以从一个或多个表中获取数据。
使用select语句进行查询，需要确定所要查询的数据在哪个表中或在哪些表中，在对多个表进行查询时，同样使用“，”对多个表进行分隔。进行多表查询，主要采用多表连接或子查询的方式，也可以通过where子句中使用连接运算来确定表之间的联系，然后根据这个条件返回查询结果。
5.1.3 数据准备
将以下数据导入到对应的数据表中
点击查看代码
-- 附：本书中常用的数据库teaching中的表结构和表记录。
-- （1）学生信息表student
-- student表结构
create table if not exists student (
studentno char(11) not null comment'学号',
sname char(8) not null comment'姓名',
sex enum('男', '女') default '男' comment'性别',
birthdate date not null comment'出生日期',
entrance int(3) null comment'入学成绩',
phone varchar(12) not null comment'电话',
Email varchar(20) not null comment'电子信箱',
primary key (studentno)
);
-- student表常用数据
/*
studentno sname sex birthdate entrance phone Email
18122210009 许东山 男 1999/11/5 789 13623456778 qwe@163.com
18122221324 何白露 女 2000/12/4 879 13178978999 heyy@sina.com
18125111109 敬横江 男 2000/3/1 789 15678945623 jing@sina.com
18125121107 梁一苇 女 1999/9/3 777 13145678921 bing@126.com
18135222201 凌浩风 女 2001/10/6 867 15978945645 tang@163.com
18137221508 赵临江 男 2000/2/13 789 12367823453 ping@163.com
19111133071 崔依歌 女 2001/6/6 787 15556845645 cui@126.com
19112100072 宿沧海 男 2002/2/4 658 12545678998 su12@163.com
19112111208 韩山川 男 2001/2/14 666 15878945612 han@163.com
19122203567 封月明 女 2002/9/9 898 13245674564 jiao@126.com
19123567897 赵既白 女 2002/8/4 999 13175689345 pingan@163.com
19126113307 梅惟江 女 2003/9/7 787 13245678543 zhu@163.com
*/
mysql> insert into student values
('18122221324','何白露','女','2000/12/4','879','13178978999','heyy@sina.com '),
('18125111109','敬横江','男','2000/3/1','789','15678945623','jing@sina.com '),
('18125121107','梁一苇','女','1999/9/3','777','13145678921','bing@126.com '),
('18135222201','凌浩风','女','2001/10/6','867','15978945645','tang@163.com '),
('18137221508','赵临江','男','2000/2/13','789','12367823453','ping@163.com '),
('19111133071','崔依歌','女','2001/6/6','787','15556845645','cui@126.com '),
('19112100072','宿沧海','男','2002/2/4','658','12545678998','su12@163.com'),
('19112111208','韩山川','男','2001/2/14','666','15878945612','han@163.com '),
('19122203567','封月明','女','2002/9/9','898','13245674564','jiao@126.com'),
('19123567897','赵既白','女','2002/8/4','999','13175689345','pingan@163.com'),
('19126113307','梅惟江','女','2003/9/7','787','13245678543','zhu@163.com');
-- （2）课程信息表course
-- #course表结构
create table if not exists course (
courseno char(6) not null,
cname char(6) not null,
type char(8) not null,
period int(2) not null,
exp int(2) not null,
term int(2) not null,
primary key (courseno)
);
-- #course表常用数据
/*
Courseno cname type period exp term
c05103 电子技术 必修 64 16 2
c05109 C语言 必修 48 16 2
c05127 数据结构 必修 64 16 2
c05138 软件工程 选修 48 8 5
c06108 机械制图 必修 60 8 2
c06127 机械设计 必修 64 8 3
c06172 铸造工艺 选修 42 16 6
c08106 经济法 必修 48 0 7
c08123 金融学 必修 40 0 5
c08171 会计软件 选修 32 8 8
*/
mysql> replace into course values
-> ('c05103','电子技术','必修','64','16','2'),
-> ('c05109','C语言','必修','48','16','2'),
-> ('c05127','数据结构','必修','64','16','2'),
-> ('c05138','软件工程','选修','48','8','5'),
-> ('c06108','机械制图','必修','60','8','2'),
-> ('c06127','机械设计','必修','64','8','3'),
-> ('c06172','铸造工艺','选修','42', '16','6'),
-> ('c08106','经济法','必修','48','0','7'),
-> ('c08123','金融学','必修','40','0','5'),
-> ('c08171','会计软件','选修','32','8','8');
-- （3）成绩信息表score
-- #score表结构
create table if not exists score(
studentno char(11) not null,
courseno char(6) not null,
daily float(3,1) default 0,
final float(3,1) default 0,
primary key (studentno , courseno)
);
-- #score表常用数据
/*
studentno courseno daily final
18122210009 c05103 87 82
18122210009 c05109 77 91
18122221324 c05103 88 62
18122221324 c05109 91 77
18125111109 c08106 79 99
18125111109 c08123 85 92
18125111109 c08171 77 92
18125121107 c05103 74 91
18125121107 c05109 89 62
18135222201 c05109 99 92
18135222201 c08171 95 82
18137221508 c08106 78 95
18137221508 c08123 78 89
18137221508 c08171 88 98
19111133071 c05103 82 69
19111133071 c05109 77 82
19112100072 c05109 87 86
19112100072 c06108 97 97
19112111208 c05109 85 91
19112111208 c06108 89 95
19122111208 c06127 78 67
19122203567 c05103 65 98
19122203567 c05108 88 89
19122203567 c06127 79 88
19123567897 c05103 85 77
19123567897 c06127 99 99
19126113307 c06108 66 82
19126113307 c08171 88 79
*/
insert into score values
('18122210009','c05103',87,82),
('18122210009','c05109',77,91),
('18122221324','c05103',88,62),
('18122221324','c05109',91,77),
('18125111109','c08106',79,99),
('18125111109','c08123',85,92),
('18125111109','c08171',77,92),
('18125121107','c05103',74,91),
('18125121107','c05109',89,62),
('18135222201','c05109',99,92),
('18135222201','c08171',95,82),
('18137221508','c08106',78,95),
('18137221508','c08123',78,89),
('18137221508','c08171',88,98),
('19111133071','c05103',82,69),
('19111133071','c05109',77,82),
('19112100072','c05109',87,86),
('19112100072','c06108',97,97),
('19112111208','c05109',85,91),
('19112111208','c06108',89,95),
('19122111208','c06127',78,67),
('19122203567','c05103',65,98),
('19122203567','c05108',88,89),
('19122203567','c06127',79,88),
('19123567897','c05103',85,77),
('19123567897','c06127',99,99),
('19126113307','c06108',66,82),
('19126113307','c08171',88,79);
--（4）教师信息表teacher
-- #teacher表结构
create table if not exists teacher (
teacherno char(6) not null comment '教师编号',
tname char(8) not null comment'教师姓名',
major char(10) not null comment '专业',
prof char(10) not null comment '职称',
department char(16) not null comment '部门',
primary key (teacherno)
);
-- # teacher表常用数据
/*
teacherno tname major prof department
t05001 苏超然 软件工程 教授 计算机学院
t05002 常杉 会计学 助教 管理学院
t05003 孙释安 网络安全 教授 计算机学院
t05011 卢敖治 软件工程 副教授 计算机学院
t05017 茅佳峰 软件测试 讲师 计算机学院
t06011 夏南望 机械制造 教授 机械学院
t06023 葛庭宇 铸造工艺 副教授 材料学院
t07019 韩既乐 经济管理 讲师 管理学院
t08017 时观 金融管理 副教授 管理学院
*/
insert into teacher values
('t05001','苏超然','软件工程','教授','计算机学院'),
('t05002','常杉','会计学','助教','管理学院'),
('t05003','孙释安','网络安全','教授','计算机学院'),
('t05011','卢敖治','软件工程','副教授','计算机学院'),
('t05017','茅佳峰','软件测试','讲师','计算机学院'),
('t06011','夏南望','机械制造','教授','机械学院'),
('t06023','葛庭宇','铸造工艺','副教授','材料学院'),
('t07019','韩既乐','经济管理','讲师','管理学院'),
('t08017','时观','金融管理','副教授','管理学院');
--（5）纽带表teach_course
-- # teach_course表结构
create table if not exists teach_course (
teacherno char(6) not null,
courseno char(6) not null,
primary key (teacherno,courseno)
);
-- # teach_course表常用数据
/*
teacherno courseno
t05001 c05109
t05002 c05127
t05003 c05127
t05011 c05138
t05017 c05127
t06011 c06127
t06023 c06172
t07019 c08123
t08017 c08106
*/
insert into teach_course values
('t05001','c05109'),
('t05002','c05127'),
('t05003 ','c05127'),
('t05011','c05138'),
('t05017','c05127'),
('t06011','c06127'),
('t06023','c06172'),
('t07019','c08123'),
('t08017','c08106');
--（6）选课信息表se_course
-- # se_course表结构
create table se_course
(sc_no int(6) not null auto_increment,
studentno char(11) not null,
courseno char(6) not null,
teacherno char(6) not null,
sc_time timestamp not null default now(),
primary key (sc_no)
);
-- # se_course表常用数据
/*
sc_no studentno courseno teacherno score sc_time
1 19120000111 co1236 t01237 NULL 2017-09-01 18:40:23
2 19120000222 co1237 t01239 NULL 2017-09-05 18:24:22
*/
insert into se_course (sc_no,studentno,courseno,teacherno,sc_time) values
(1,'19120000111' ,'co1236', 't01237', '2017-09-01 18:40:23'),
(2,'19120000222 ', 'co1237' ,'t01239','2017-09-05 18:24:22');
5.2 单表查询
5.2.1 select …… from …… 基本子句的使用
SELECT子句的主要功能是输出字段或表达式的值，FORM子句的主要功能是指定数据源。这两个子句在进行数据库表查询时，都是必选项。
1. 为字段取别名
利用select语句查询数据时，输出项一般显示创建表时定义的字段名。当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，MySQL可以列名之后使用AS子句来更改查询结果的列别名，为查询显示的每个输出字段或表达式取一个别名，以增加结果集的可读性。例如，可以用as关键字给字段取一个中文名。实现给select子句中的各项取别名其语法格式为：
SELECT 字段列表 [AS] 别名
【例5.3】在student表中查询出生日期在2001年以后的学生的学号、姓名、电话和年龄。
分析：可以通过as为列或表达式更改名称，增加可读性。
mysql> select studentno as '学号',sname as '姓名',
-> phone as '手机号',year(now())-year(birthdate) as '年龄'
-> from student
-> where year(birthdate)>2001;
注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。
2. 使用谓词过滤记录
如果希望一个列表没有重复值，可以利用distinct子句从结果集中除去重复的行。当使用distinct子句时，需要注意以下事项：
(1) 选择列表的行集中，所有值的组合决定行的唯一性。
(2) 数据检索包含任何唯一值组合的行，如果不指定distinct子句则将所有行返回到结果集中。
【例5.4】 在score表中查询期末成绩中有高于95的学生的学号和课程号,并按照学号排序。
分析：不管学生有几门课的成绩高于95，只要有一门就可以显示，利用distinct子句可将重复行消除。
mysql> select distinct studentno,courseno from score where final > 95 order by studentno;
2. 使用where子句过滤结果集
| 运算符名称 | 符号及格式 | 说 明 |
|---|---|---|
| 算术比较判断 | <表达式1> θ <表达式2> θ代表的符号有：<、<=、>、>=、<>或!=、= |
比较两个表达式的值 |
| 逻辑比较判断 | <比较表达式1> θ <比较表达式2> θ代表的符号按其优先级由高到低的顺序为：NOT、AND、OR |
两个比较表达式进行非、与、或的运算 |
| 之间判断 | <表达式> [NOT] BETWEEN <值1> AND <值2> | 搜索（不）在给定范围内的数据 |
| 字符串模糊判断 | <字符串> [NOT] LIKE <匹配模式> | 查找（不）包含给定模式的值 |
| 空值判断 | <表达式> IS [NOT] NULL | 判断某值是否为空值 |
| 之内判断 | <表达式> [NOT] IN (<集合>) | 判断表达式的值是否在集合内 |
(1) 查询符合指定条件的记录数据
如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应用的是where子句，通过where子句可以实现很多复杂的条件查询。在使用where子句时，需要使用一些比较运算符来确定查询的条件。
【例5.5】查询表student中入学成绩在800分以上的学生的学号、姓名和电话信息。
分析：本例中要求输出学号、姓名和电话信息，即为select子句输出表列数据源为表student，条件为入学成绩在800分以上。
mysql> select studentno,sname,phone from student where entrance > 80;
(2) 带in关键字的查询
in关键字可以判断某个字段的值是否在于指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。实际上，使用in搜索条件相当于用or连接两个比较条件，如“ x in（10，15）” 相当于表达式“ x=10 or x=15 ”。也可以使用not in关键词查询不在某取值范围内的记录行数据。
【例5.6】查询学号分别为的18135222201、18137221508和19123567897的学生学号、课程号、平时成绩和期末成绩。
分析：检索条件中枚举某些确定值的范围，一般可以利用 in关键字实现。
mysql> select studentno,courseno,daily,final from score where studentno in ('18135222201','18137221508','19123567897');
(3) 带between and的范围查询
用于范围比较的关键字有两个：BETWEEN和IN。
在where子句中，可以使用between搜索条件检索指定范围内的行。使用between搜索条件时，使用between搜索条件相当于用and连接两个比较条件，如 x between 10 and 27 相当于表达式 x>=10 and x<=27 。由此可见，在生成结果集中，边界值也是符合条件的。检索条件指定排除某个范围的值，一般可以利用 not between关键字实现。
当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：
表达式 [ NOT ] BETWEEN 表达式1 AND 表达式2
当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。
注意：expression1的值不能大于expression2的值。
【例5.7】 查询选修课程号为c05109 的学生学号和期末成绩，并且要求平时成绩在80到95。
分析：检索条件设置在某个的范围内，一般可以利用between关键字实现。
mysql> select studentno,final from score where courseno = 'c05109' and daily between 80 and 95;
(4) 带like的字符匹配查询
使用通配符结合的like搜索条件，通过进行字符串的比较来选择符合条件的行。当使用like搜索条件时，模式字符串中的所有字符都有意义，包括开头和结尾的空格。like主要用于字符类型数据。字符串内的英文字母和汉字都算一个字符。也可用通配符并使用 not like作为查询条件。
like属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%” 和下划线 “_”：
“%” 可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。
“_” 只匹配一个字符。
【例5.8】在student表中显示所有姓何或姓韩的学生的姓名、生日和Email。
分析：设置where条件实现上述要求，需要采用or和like等逻辑运算。Like 操作符可以和通配符一起将列的值与某个特定的模式作比较，列的数据类型可以是任何字符串类型。
mysql> select sname,birthdate,email from student where sname like '何%' or sname like '韩%';
(5) 用is null关键字查询空值
涉及空值的查询用null来表示。create table语句或alter table 语句中的null表明在列中允许存在被称为null的特殊数值，它不同于数据库中的其他任何值。在select语句中，where子句通常会返回比较的计算结果为真的行。那么，在where子句中，如何处理null的值的比较呢？为了取得列中含有null的行，MySQL语句包含了操作符功能is [not] null。
一个字段值是空值或者不是空值，要表示为：“is null”或“is not null”。不能表示为：“=null”或“<>null”。
如果写成 “字段=null”或“字段<>null”，系统的运行结果都直接处理为null值，按照false处理而不报错。
MySQL有一个特殊的等于运算符 <=>，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。
where子句有以下通用格式： column is [not] null
【例5.9】在se_score表中添加成绩字段score，查询se_score表中学生的学号、课程号和成绩。
分析：学生选修课程表se_course中的成绩是允许空值，以此是否成绩为空值作为查询条件，即可查到学生的选课情况。
mysql> alter table se_course add score float(3,1) null after teacherno;
mysql> select studentno,courseno,teacherno,score from se_course where score is null;
(6) 带and的多条件查询
where子句的主要功能是利用指定的条件选择结果集中的行。符合条件的行出现在结果集中，不符合条件的行将不出现在结果集中。利用where子句指定行时，条件表达式中的字符型和日期类型值要放到单引号内，数值类型的值直接出现在表达式中。
【例5.10】在score表中显示期中高于90分、期末成绩高于85分的学生学号、课程号和成绩。
分析：设置where条件实现上述要求，需要采用and逻辑运算，将两个比较运算表达式连接起来。
mysql> select studentno,courseno,final from score where daily >= 90 and final >= 85;
(7) 带or的多条件查询
带or的多条件查询，实际上是指只要符合多条件中的一个，记录就会被搜索出来；如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。or可以用来连接两个条件表达式。而且，可以同时使用多个or关键字连接多个条件表达式。
【例5.11】查询计算机学院的具有高级职称教师的教师号、姓名和从事专业。
分析：where子句设置的条件包括部门和职称，其中高级职称又包括教授和副教授两类，需要包括or 和and两种逻辑运算。
mysql> select teacherno,tname,major from teacher where department = '计算机学院' and (prof = '副教授' or prof = '教授');
3. 使用order by子句对结果集排序
利用order by子句可以对查询的结果进行升序（asc）或降序（desc）排列。排序可以依照某个列的值，若列值相等则根据第二个属性的值，依此类推。
利用order by子句进行排序，需要注意如下事项和原则：
(1) 默认情况下，结果集按照升序排列。也可以在输出项的后面加上关键字desc 来实现降序输出。如果对含有 null 值的列进行排序时，如果是按升序排列，null 值将出现在最前面，如果是按降序排列， null 值将出现在最后。
(2) order by 子句包含的列并不一定出现在选择列表中。
(3) order by 子句可以通过指定列名、函数值和表达式的值进行排序。
(4) order by 子句不可以使用text、ntext或image类型的列。
(5) 在 order by 子句中可以同时指定多个排序项。
(6) 当SELECT语句中同时包含多个子句，如WHERE、GROUP BY、HAVING、ORDER BY，ORDER BY 子句必须是最后一个子句。
(7) 可以使用列的别名、列的位置进行排序。
【例5.12】在student表中查询高于850分的学生学号、姓名和入学成绩，并按照入学成绩的降序排列。
分析：升序asc是默认值，而降序desc必须表明，也可以给字段取别名。
mysql> select studentno as "学号",sname as "姓名",entrance as "入学成绩" from student where entrance > 850 order by entrance desc;
【例5.13】在score表中查询总评成绩大于90分的学生的学号、课程号和总评成绩，并先按照课程号的升序、再按照总评成绩的降序排列。总评成绩计算公式如下：总评成绩=daily0.2+final0.8
分析：本例利用表达式作比较和排序的依据。
mysql> select studentno 学号, courseno 课程号, daily*0.2+final*0.8 总评成绩 from score where daily*0.2+final*0.8 > 90 order by courseno,daily*0.2+final*0.8 desc;
4. group by子句和having子句的使用
group by子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 group by子句，则只为 select语句报告一个聚合值。
将一列或多列定义成为一组，使组内所有的行在那些列中的数值相同。出现在查询的select 列表中的每一列都必须同时出现在group by子句中。
使用group by关键字来分组。单独使用group by关键字，查询结果只显示每组的一条记录。
【例5.14】利用group by子句对score表数据分组，显示每个学生的学号和平均总评成绩。总评成绩计算公式如下：
总评成绩=daily0.3+final0.7
分析：通过学号分组，可以求出每个学生的平均总评成绩。avg()函数用于求平均值，round()函数用于对平均值的某位数据进行四舍五入。
mysql> select studentno 学号, round(avg(daily*0.3+final*0.7)) 平均总评成绩 from score group by studentno;
group by关键字与group_concat()函数一起使用。使用group by关键字和group_concat()函数查询，可以将每个组中的所有字段值都显示出来。
该函数返回带有来自一个组的连接的非NULL值的字符串结果。group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。
【例5.15】 使用group by关键字和group_concat()函数对score表中的studentno字段进行分组查询。可以查看选学该门课程的学生学号。
mysql> select courseno "课程号",group_concat(studentno) 选课学号 from score group by courseno;
group by关键与having一起使用。select 语句中的 where和having子句控制用数据源表中的那些行来构造结果集。where和having是筛选，这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。
having 子句通常与 group by 子句结合使用，尽管指定该子句时也可以不带 group by。having 子句指定在应用 where 子句的筛选后要进一步应用的筛选。
【例5.16】查询选课在3门以上且各门课程期末成绩均高于75分的学生的学号及其总成绩，查询结果按总成绩降序列出。
分析：可以利用having 子句筛选分组结果，使之满足count(*)>=3的条件即可。
mysql> select studentno 学号,sum(daily*0.3 + final*0.7) as 总分 from score where final >= 75 group by studentno having count(*)>=3 order by sum(daily * 0.3 + final * 0.7) desc;
5. 用limit限制查询结果的数量
limit是用来限制查询结果的数量的子句。可以指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。Limit可以指定初始位置，也可以不指定初始位置。
【例5.17】查询student表的学号、姓名、出生日期和电话，按照entrance进行降序排列，显示前3条记录。
mysql> select studentno,sname,birthdate,phone from student order by entrance desc limit 3;
使用limit还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第1条记录的编号（注意在总查询结果中，第1条记录编号为0）；参数2是要查询记录的个数。
【例5.18】查询score表中，期末成绩final高于85分的，按照平时成绩daily进行升序排列，从编号2开始，查询5条记录。
mysql> select * from score where final >85 order by daily asc limit 2,5;
5.3 聚合函数查询
MySQL的常用聚合函数包括count()、sum()、avg()、max()和min()等。
| 函 数 | 说 明 |
|---|---|
| COUNT(*) COUNT(<列名>) |
计算记录的个数 对一列中的值计算个数 |
| SUM(<列名>) | 求某一列值的总和 |
| AVG(<列名>) | 求某一列值的平均值 |
| MAX(<列名>) | 求一列值的最大值 |
| MIN(<列名>) | 求一列值的最小值 |
使用聚合函数时，需要注意以下几点：
(1) 聚合函数只能出现在所查询的列、ORDER BY子句、HAVING子句中，而不能出现在WHERE子句、GROUP BY子句中。
(2) 除了COUNT(*)之外，其他聚合函数（包括COUNT(<列名>)）都忽略对列值为NULL值的统计。
利用聚合函数可以满足表中记录的聚合运算。例如，需要计算学生成绩表中的平均成绩，可以使用avg()函数。group by关键字通常需要与聚合函数一起使用。
1. count()函数
count()函数，对于除*以外的任何参数，返回所选择聚合中非null值的行的数目；对于参数*，返回选择聚合所有行的数目，包含null值的行。没有where子句的count(*)是经过内部优化的，能够快速的返回表中所有的记录总数。
【例5.18】 通过查询求18级学生的总数。
分析：求学生数即为求符合要求的记录行数，一般利用count()函数实现。
mysql> select count(studentno) as '18级学生数' from student where substring(studentno ,1,2) = '18';
substring() 返回指定字符串。
2. sum()函数和avg()函数
sum()函数可以求出表中某个字段取值的总和。
avg()函数可以求出表中某个字段取值的平均值。
【例5.19】查询score表中学生的期末总成绩大于270分的学生学号、总成绩及平均成绩。
分析：先按照studentno对final值进行分组，再利用sum()函数和avg()函数分别期末总成绩和平均值，然后进行期末总成绩大于270分学生的筛选。
mysql> select studentno 学号,sum(final) 总分,avg(final) 平局分 from score group by studentno having sum(final) > 270 order by studentno;
3. max()函数和min()函数
max()函数可以求出表中某个字段取值的最大值。
min()函数可以求出表中某个字段取值的最小值。
【例5.20】查询选修课程号为c05109号课程的期末最高分、最低分及之间相差的分数。
分析：分别利用max()和min()函数可以求得final的最大最小值
mysql> select max(final) 最高分,min(final) 最低分,max(final) - min(final) 分差 from score where courseno = 'c05109';
4. 利用group by子句与with rollup一起进行统计
MySQL中的with rollup应用，可以在分组的统计数据的基础上再进行相同的总体统计。例如，对于成绩表中，查询某一门课的平均值和所有成绩的平均值，普通的group by 语句是不能实现的。
【例5.21】查询score表中每一门课的期末平均值和所有成绩的平均值。
分析：如果使用有with rollup 子句的 group by语句，则可以实现这个要求。
mysql> select courseno 课程号,avg(final) 课程期末平均分 from score group by courseno with rollup;
5.4 多表连接
5.4.1 多表查询连接
连接是关系型数据库中常用的多表查询数据的模式，连接可以根据各个表之间的逻辑关系来利用一个表中的数据选择另外的表中的行实现数据的关联操作。要在数据库中完成复杂的查询，必须将两个或两个以上的表连接起来。
连接条件可在 from 或 where 子句中指定。连接条件与where和having搜索条件组合，用于控制from子句引用的数据源中所选定的行。
MySQL处理连接时，查询引擎从多种可能的方法中选择最高效的方法处理连接。尽管不同连接的物理执行可以采用多种不同的优化，但逻辑序列都是通过应用from 、where 和having子句中的连接条件和搜索条件实现。
连接条件中用到的字段虽然不必具有相同的名称或相同的数据类型，但是如果数据类型不相同，则必须兼容或可进行隐性转换。
MySQL显式定义了连接操作，增强了查询的可读性。被显式定义的与连接有关的关键字如下：
(1) inner join：内连接，结果只包含满足条件的列。
(2) left outer join：左外连接，结果包含满足条件的行及左侧表中的全部行。
(3) right outer join：右外连接，结果包含满足条件的行及右侧表中的全部行。
(4) cross join：结果只包含两个表中所有行的组合，指明两表间的笛卡儿操作。
连接查询时的注意事项：
(1) 要连接的表都要放在FROM子句中，表名之间用逗号分开，比如FROM detp,emp。
(2) 为了书写方便，可以为表起别名，表的别名在FROM子句中定义，别名放在表名之后，它们之间用空格隔开。注意，别名一经定义，在整个查询语句中就只能使用表的别名而不能再使用表名。
(3) 连接的条件放在WHERE子句中，比如WHERE emp.deptno=dept.deptno。
(4) 如果多个表中有相同列名的列时，在使用这些列时，必须在这些列的前面冠以表名来区别，表名和列名之间用句号隔开。比如 SELECT emp.detpno。
5.4.2 全连接
当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。
全连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连接可能得到的行数为每个表中行数之积。
如表A有3行，表B有2行，表A和B全连接后得到6行(3x2=6)的表
5.4.3 等值连接
FROM子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。
若表A和B进行等值连接（T1=T3 ），则如下表所示，只有两行。
5.4.4 内连接
内连接(inner join) 查询是通过比较数据源表间共享列的值，从多个源表检索符合条件的行的操作。可以使用等号运算符=的连接，也可以连接两个不相等的列中的值。
表名1 INNER JOIN 表名2 ON 条件 | USING (列名)
【例5.22】查询选修课程号为c05109的学生的学号、姓名和期末成绩。
分析：本例中要求所输出的列分别在student表和score表中，可以通过studentno列、使用内连接的方式连接两个表，找出选修课程号为c05109的行。程序中两个表存在相同的列studentno，引用时需要标明该列所属的源表。
mysql> select student.studentno,sname,final from student inner join score on student.studentno = score.studentno where score.courseno = 'c05109';
内连接：指定了INNER关键字的连接是内连接。
该语句根据ON关键字后面的连接条件，合并两个表，返回满足条件的行。
内连接是系统默认的，可以省略INNER关键字。
使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。
mysql> select student.studentno,sname,final from student join score on student.studentno=score.studentno where final > 70;
还有一种方法，就是直接通过where子句的复合条件查询，可以实现与内连接的同样结果。代码如下:
mysql> select s.studentno,s.sname,c.final from student s,score c where s.studentno = c.studentno and c.courseno = 'c05109';
5.4.5 自连接
作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。
【例5.23】查找score表中选同一门课，分数同的学生学号，分数，课程号。
mysql> select distinct s.studentno,s.final,s.courseno from score as s join score as c on s.courseno = c.courseno where s.final != c.final;
5.4.2 外连接
外连接（outer join）包括满足搜索条件的连接表中的所有行，甚至包括在其他连接表中没有匹配行的一个表中的行。对于当一个表中的行与其他表中的行不匹配时返回的结果集行，为解析为不存在相应行的表的所有结果集列提供null值。
外连接会返回from子句中提到的至少一个表或视图中的所有行，只要这些行符合任何where或having 搜索条件。将检索通过左外部连接引用的左表中的所有行，以及通过右外部连接引用的右表中的所有行。
外连接是使用outer join 关键字将两个表连接起来。外连接生成的结果集不仅包含符合连接条件的行数据，而且还包括左表（左外连接时的表）、右表（右外连接时的表）中所有的数据行。
1. 左外连接
左外连接（left outer join）是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加null值。
左外连接的格式是：
FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列=表2.列
【例5.24】在mysqltest数据库中利用左外连接方式查询学生的学号、姓名、平时成绩和期末成绩。
分析：左外连接方式将会对右表中的行与左表中的行不匹配时，将右表的所有结果集列赋以null 值。
mysql> use mysqltest;
mysql> select student02.studentno,sname,daily,final
-> from student02 left join score1
-> on student02.studentno=score1.studentno;
2. 右外连接
右外连接(right outer join)。也是外部连接的一种，其中包含 join 子句中最右侧表的所有行。如果右侧表中的行与左侧表中的行不匹配，则将为结果集中来自左侧表的所有列分配 null 值。
【例5.25】利用右外连接方式查询教师的排课情况。
分析：右外连接方式将会对左表中的行与右表中的行不匹配时，将左表的所有结果集列赋以null 值。
mysql> select teacher.teacherno,tname, major, courseno
-> from teacher right join teach_course
-> on teacher.teacherno = teach_course.teacherno;
5.4.3 交叉连接
交叉连接(Cross Join )是在没有where子句的情况下,产生的表的笛卡儿积。两个表作交叉连接时，结果集大小为二者行数之积。该种方式在实际过程中用的很少。
【例5.26】显示student 表和score表的笛卡儿积。
分析：其结果集336行数据，应是student 表数据行数与score表行数的乘积数。
mysql> select student.studentno,sname,score.*
-> from student cross join score;
5.4.4 连接多个表
从理论上说，对于使用select语句进行连接的表数目没有上限。但在一条select语句中连接的表多于10个，那么数据库就很可能达不到最优化设计，MySQL引擎的执行计划会变得非常繁琐 。
需要注意的是对于3个以上关系表的连接查询，一般遵循下列规则：连接n个表至少需要n-1个连接条件，以避免笛卡儿积的出现。为了缩小结果集，采用多于n-1个连接条件或使用其他条件都是允许的。
【例5.27】 查询18级学生的学号、姓名、课程名、期末成绩及学分。
分析：本例要求输出的各项分别存在于student、 course和 score等3个表中，因此至少需要创建2个连接条件。每16个学时，计为1学分。
mysql> select student.studentno,sname,cname,final,round(period/16,1)
-> from score join student on student.studentno = score.studentno
-> join course on score.courseno = course.courseno
-> where substring(student.studentno,1,2) = '18';
5.4.5 合并多个结果集
union操作符可以将多个select语句的返回结果组合到一个结果集中。当要检索的数据在不同的结果集中，并且不能够利用一个单独的查询语句得到时，可以使用union合并多个结果集。
将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。union 运算不同于使用联接合并两个表中的列的运算。
使用 union 合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。
union操作符基本语法格式如下：
select_statement union [all] select_statement
其中，格式中的参数说明如下：
（1）select_statement：select语句。
（2）union：指定组合多个结果集并返回为单个结果集。
（3）All：将所有行合并到结果中，包括重复的行。如果不指定，将删除重复的行。
【例5.28】在mysqltest数据库中利用student表创建student01，将student01和student表的部分查询结果集合并。
分析：虽然2个表的结构不同，但需要合并的两个结果集结构和列的数据类型兼容。
mysql> createtable student01 as
-> select studentno,sname,phone from teaching.student;
mysql> select studentno,sname,phone from student01
-> where phone like '%131%'
-> union
-> select studentno,sname,phone from teaching.student
-> where phone like '%132%';
5.5 子查询
5.5.1 子查询
子查询就是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。部分子查询和连接可以相互替代，使用子查询也可以替代表达式。通过子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问题。
子查询的执行过程。MySQL对嵌套查询的处理过程是从内层向外层处理，即先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。
子查询和连接的关系。一般情况下，包含子查询的查询语句可以写成连接查询的方式。因此，通过子查询也可以实现多表之间的查询。在有些方面，多表连接的性能要优于子查询，原因是连接不需要查询优化器执行排序等额外的操作。
子查询中的常见运算。子查询中可能包括in、not in、any、all、exists、not exists等逻辑运算符，也可以包含比较运算符，如“=”、“!=”、“>”和“<”等。
子查询的类型。根据子查询的结果又可以将MySQL子查询分为4种类型。
(1) 返回一个表的子查询是表子查询。
(2) 返回带有一个或多个值的一行的子查询是行子查询。
(3) 返回一行或多行，但每行上只有一个值的是列子查询
(4) 只返回一个值的是标量子查询。从定义上讲，每个标量子查询都是一个列子查询和行子查询。
使用子查询时应该注意如下的事项。
(1)子查询需要用括号括起来。子查询中也可以再包含子查询，嵌套可以多至32层。
(2)当需要返回一个值或一个值列表时，可以利用子查询代替一个表达式。也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能。
(3)子查询不能够检索数据类型为varchar(max)、nvarchar(max) 和varbinary(max)的列。
(4)子查询使用order by时，只能在外层使用，不能在内层使用。
5.5.2 利用子查询做表达式
在MySQL语句中，可以把子查询的结果当成一个普通的表达式来看待，用在其外查询的选择条件中。此时子查询必须返回一个值或单个列值列表，此时的子查询可以替换where子句中包含in关键字的表达式。
【例5.29】 查询学号为18125121107的学生的入学成绩、所有学生的平均入学成绩及该学生成绩与所有学生的平均入学成绩的差。
分析：利用子查询求学生的平均入学成绩，作为select语句的输出项表达式。
mysql> select studentno,sname,entrance ,
-> (select avg(entrance) from student ) as 平均成绩,
-> entrance -(select avg(entrance) from student ) as 分差
-> from student
-> where studentno='18125121107';
5.5.3 利用子查询生成派生表
select的数据源由from子句指定，from子句可以指定单个表或者多个表，还可以查询来自视图、临时表或结果集的数据源。即可以利用子查询可以生成一个派生表，用于替代from子句中的数据源表，派生表可以定义一个别名，即子查询的结果集可以作为外层查询的源表。实际上是在from子句中使用子查询作为派生表数据源。
【例5.30】查询期末成绩高于85分、总评成绩高于90分的学生的学号、课程号和总评成绩。
分析：利用子查询过滤出期末成绩高于85分的结果集，以TT命名，然后再对结果集TT中的数据进行查询。
mysql> select a.studentno,a.courseno,a.final*0.8+a.daily*0.2
-> from (select * from score where final > 85) as a
-> where a.final*0.8+a.daily*0.2 > 90;
5.5.4 where子句中的子查询
where语句中的子查询实际上是将子查询的结果作为该语句条件中的一部分，然后利用这个条件过滤本层查询的数据。
1.带比较运算符的子查询
子查询可以作为动态表达式，该表达式可以随着外层查询的每一行的变化而变化。使得动态执行的子查询与外部查询有一个非常有效的连接，从而将复杂的查询分解为多个简单而相互关联的查询。
查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=等。比较运算符在子查询时使用的非常广泛。
创建关联子查询时，外部查询有多少行，子查询就执行多少次。
【例5.31】查询期末成绩比选修该课程平均期末成绩低的学生的学号、课程号和期末成绩。
分析：在本例中，对score表采用别名形式，一个表就相当于2个表。子查询执行时使用的a.courseno相当于一个常量。在别名为b的表中根据分组计算平均分。然后与外层查询的值进行比较。该过程很费时间。
mysql> select studentno,courseno,final
-> from score as a
-> where final < (select avg(final) from score as b where a.courseno = b.courseno group by courseno);
2. 带in关键字的子查询
当子查询返回的结果列包含一个值时，利用比较运算符就适用查询要求。假如一个子查询返回的结果集是值的列表，这时比较运算符就可以用in运算符代替。
in运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。not in的作用与in刚好相反。
【例5.32】 获取期末成绩中含有高于93分的学生的学号、姓名、电话和Email。
分析：利用操作符in可以允许指定一个表达式（或常量）集合，可以利用select语句的子查询输出表达式（或常量）集合。
mysql> select studentno,sname,phone,email
from student
where studentno in (select studentno from score where final > 93);
3. 带exists关键字的子查询
使用exists关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。
not exists 与 exists 的工作方式类似，即当not exists与exists刚好相反，使用not exists关键字时，当返回的值是true时，外层查询语句不执行查询；当返回值是false时，外层查询语句将执行查询。
【例5.33】查询student表中是否存在2001年12月12日以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话。
分析：只要存在一行数据符合条件，则where条件就返回TURE，于是输出所有行。
mysql> select studentno,sname,birthdate,phone
from student
where exists (select * from student where birthdate < '2001-12-12');
4. 对比较运算进行限制的子查询
all、some和any运算都是对比较运算的进一步限制。
all指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回true，否则返回false。
some或any是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回true，否则返回false。
【例5.34】 查找score表中所有比c05109课程期末成绩都高的学号、姓名、电话和期末成绩。
分析：本题输出项是学号、姓名、电话和期末成绩，分别存在于student表和 score表，因此外层查询先做一个内连接。在此基础上，从外层查询数据源中找出每一个期末成绩final的值，让该值分别与子查询中的c05109课程的每一个值进行比较，当该外层final值比内层的每一个c05109课程成绩都高时，即为查询结果集中的一行记录。以此类推，即可得到本题的结果集。
mysql> select student.studentno,sname,phone,final
from score inner join student on score.studentno = student.studentno
where final > all(select final from score where courseno = 'c05109');
5.5.5 利用子查询插入、更新与删除数据
利用子查询修改表数据，就是利用一个嵌套在insert、update或delete语句的子查询成批的添加、更新和删除表中的数据。
1. 利用子查询插入纪录
insert 语句中的 select 子查询可用于将一个或多个其他的表或视图的值添加到表中。使用 select 子查询可同时插入多行。
【例5.35】将student表中2001年以后出生的学生记录添加到student02表中。
分析：子查询的选择列表必须与 insert 语句列的列表匹配。如果insert 语句没有指定列的列表，则选择列表必须与正向其插入的表或视图的列匹配且顺序一致。
mysql> insert into mysqltest.student02
-> (select * from student where birthdate>='2001-12-31');
2. 利用子查询更新数据
update语句中的select子查询可用于将一个或多个其他的表或视图的值进行更新。使用 select子查询可同时更新多行数据。实际上是通过将子查询的结果作为更新条件表达式中的一部分。
【例5.36】 将student表中入学成绩低于800的所有学生的期末成绩增加5%。
分析：利用update成批修改表数据，可以在where子句的利用子查询实现。
mysql> update score
-> set final= final*1.05
-> where studentno in
-> (select studentno from student where entrance <800);
同样在delete语句中利用子查询可以删除符合条件的数据行。实际上是通过将子查询的结果作为删除条件表达式中的一部分。
5.6 使用正则表达式进行模糊查询
5.6.1 正则表达式
正则表达式通常用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些词语等。正则表达式强大而且灵活，可以应用于非常复杂的查询。
正则表达式的查询能力比通配字符的查询能力更强大，而且更加的灵活。正则表达式可以应用于非常复杂查询。MySQL中，使用regexp关键字来匹配查询正则表达式。
正则表达式的基本语法格式如下:
where 字段名 regexp '操作符'
MySQL中使用regexp操作符指定正则表达式的字符匹配模式，regexp操作符 中常用字符匹配选项如表所示。
正则表达式中常用的字符匹配选项
| 选项 | 说明 | 示例 |
|---|---|---|
| ^ | 匹配文本的开始字符 | ^b：匹配以字母b为开头的字符串，如big |
| $ | 匹配文本的结束字符 | st$：匹配以st结尾的字符串，如test |
| . | 匹配任何单个字符 | b.t：匹配任何b和t之间有一个字符，如bit |
| * | 匹配零个或多个在它前面的字符 | *n：匹配字符n前面有任意个字符，如fn |
| + | 匹配前面的字符1次或多次 | ba+：匹配以b开头后面紧跟至少有一个a，如bay、bare、battle |
| <字符串> | 匹配包含指定的字符串的文本 | fa：字符串至少要包含fa，如fan |
| [字符集合] | 匹配字符集合中的任何一个字符 | [xz]：匹配x或z，如dizzy |
| [^] | 匹配不在括号中的任何字符 | [^abc]：匹配任何不包含a、b或c的字符串 |
| 字符串 | 匹配单面的字符串至少n次 | b{2,}：匹配两个或更多的b，如bb、bbb |
| 字符串 | 匹配前面的字符串至少m次，至多n次。如果n为0，m为可选参数 | b{2,4}：匹配至少2个b，最多4个b，如bb、bbbb、bbb |
5.6.2 使用正则查询
1. 查询以特定字符或字符串开头的记录
(1) 使用字符“^”可以匹配以特定字符或字符串开头的记录。
【例5.37】 查询student表中姓“赵”的学生的部分信息。
mysql> select studentno,sname,birthdate, phone
-> from student where sname regexp '^赵';
(2) 使用字符“$”可以匹配以特定字符或字符串结尾的记录。
【例5.38】查询student表中学生电话号码尾数为5的学生部分信息。
mysql> select studentno, sname, phone, Email
-> from student where phone regexp '5$';
用符号“.”来替代字符串中的任意一个字符。用正则表达式来查询时，可以用“.”来替代字符串中的任意一个字符。
【例5.39】 要实现查询学生姓名sname字段中以“赵”开头，以“江”结束的，中间包含两个字符的学生信息，可以通过正则表达式查询来实现，其中正则表达式中，^表示字符串的开始位置，$表示字符串的结束位置，.表示除“\n”以外的任何单个字符（此例中汉字按2个字符计算）。
mysql> select studentno, sname, phone
-> from student where sname regexp '^赵..江$';
(3) 匹配指定字符串
正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。如果指定多个字符串时，需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可。
【例5.40】 查询学生电话号码出现131或132数字的学生信息。
mysql> select studentno, sname, phone, Email
-> from student where phone regexp '131|132';
