数据查询

数据查询

在MySQL中,使用select语句能从数据表或者视图中查询满足条件的记录。select语句的功能强大、使用灵活,其数学理论基础是关系数据模型中对表对象的一组关系运算,即选择(selection)、投影(projection)和连接(join)。

select语句

使用select语句能够在需要时从数据库中快捷方便地检索、统计或输出数据。该语句的执行过程是从数据库中选取匹配的特定行和列,并将这些数据组织成一个结果集,然后以一张临时表的形式返回。在MySQL中,select语句的语法内容较多,本书主要介绍一些简单的常用的语法。select语法的常用语法格式为:

1
2
3
4
5
6
select [all | distinct | distinctrow] <目标列表达式1> [, <目标列表达式2>] ...
from <表名1或视图名1> [, <表名2或视图名2>] ...
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc | desc]]
[limit [m,] n];

语法说明如下:

  1. all | distinct | distinctrow:为可选项,用于指定是否返回结果集中的重复行。如果没有指定,默认为all;如果指定了distinct或者distinctrow,则会消除结果集中的重复行,这两个词是同一词,且这两个关键字应用于select语句中所指定的所有列,故在select语句中只需要指定一次,不需要在每个目标前都指定。
  2. select子句:用于指定要显示的字段或者表达式。from子句用于指定数据来源于哪些表或视图;where子句为可选项,用于指定对记录的过滤条件;group by 子句为可选项,用于将查询结果集按指定的字段值分组;having子句为可选项,用于指定分组结果集的过滤条件;order by子句为可选项,用于将查询结果集按指定字段值的升序或降序排列;limit子句为可选项,用于指定查询结果集包含的记录数。

在select语句中,所有可选子句必须按照select语句的语法格式所罗列的顺序使用。下表总结了这些子句的顺序:

子句 说明 是否必须使用
select 指定返回的列或表达式
from 指定检索数据的表 仅在从表选择数据时使用
where 指定行级过滤条件
group by 指定分组字段 仅在按组计算计算聚合时使用
having 指定组级过滤条件
order by 指定排序字段和排序方式
limit 指定返回的记录数

select即可完成简单的单表查询,也可以实现复杂的连接查询和嵌套查询。

首先需要准备一些数据。为此我决定调整一下学习顺序,首先学一下数据插入。

一些数据准备工作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
# 准备数据库
drop schema if exists db_sp;
drop schema if exists db_school;
create database db_school
default character set gb2312
default collate gb2312_chinese_ci;
create table db_school.tb_class
(
classNo char(6) not null unique,
className varchar(30) not null,
department varchar(30) not null,
grade int,
classNum int,
constraint PK_class primary key (classNo)
)engine = InnoDB;

create table db_school.tb_student
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(30),
nation varchar(20) default '汉',
classNo char(6) not null,
constraint PK_student primary key (studentNo),
constraint FK_student foreign key (classNo)
references db_school.tb_class (classNo)
)engine = InnoDB;

create table db_school.tb_course
(
courseNo char(5) not null,
courseName varchar(30) not null,
credit tinyint not null,
courseHour int not null,
term tinyint not null,
priorCourse char(5),
constraint PK_course primary key (courseNo)
)engine = InnoDB;

create table db_school.tb_score
(
studentNo char(10) not null,
courseNo char(5) not null,
score int check (score >= 0 and score <= 100),
constraint FK_score1 foreign key (studentNo)
references db_school.tb_student (studentNo),
constraint FK_score2 foreign key (courseNo)
references db_school.tb_course (courseNo)
)engine = InnoDB;

# 插入数据
insert into db_school.tb_class
values
('AC1301', '会计13-1班', '会计学院', 2013, 35),
('AC1302', '会计13-2班', '会计学院', 2013, 35),
('CS1401', '计算机14-1班', '计算机学院', 2014, 35),
('IS1301', '信息系统13-1班', '信息学院', 2013, null),
('IS1401', '信息系统14-1班', '信息学院', null, 30);

insert into db_school.tb_student
values
('2013110101', '张晓勇', '男', '1997-12-11', '山西', default, 'AC1301'),
('2013110103', '王一敏', '女', '1996-03-25', '河北', default, 'AC1301'),
('2013110201', '江山', '女', '1996-03-25', '内蒙古', '锡伯', 'AC1302'),
('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302'),
('2013310101', '黄菊', '女', '1995-09-30', '北京', default, 'IS1301'),
('2013310103', '吴昊', '男', '1995-11-08', '河北', default, 'IS1301'),
('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401'),
('2014210102', '郭志坚', '男', '1997-02-21', '上海', default, 'CS1401'),
('2014310101', '王林', '男', '1996-10-09', '河南', default, 'IS1401'),
('2014310102', '李怡然', '女', '1996-12-31', '辽宁', default, 'IS1401');

insert into db_school.tb_course
values
('11003', '管理学', 2, 32, 2, null),
('11005', '会计学', 3, 48, 3, null),
('21001', '计算机基础', 3, 48, 1, null),
('21002', 'OFFICE高级应用', 3, 48, 2, '21001'),
('21004', '程序设计', 4, 64, 2, '21001'),
('21005', '数据库', 4, 64, 4, '21004'),
('21006', '操作系统', 4, 64, 5, '21001'),
('31001', '管理信息系统', 3, 48, 3, 21004),
('31002', '信息系统_分析与设计', 2, 32, 4, '31001'),
('31005', '项目管理', 3, 48, 5, '31001');

insert into db_school.tb_score
values
('2013110101', '11003', 90),
('2013110101', '21001', 86),
('2013110103', '11003', 89),
('2013110103', '21001', 88),
('2013110201', '11003', 78),
('2013110201', '21001', 92),
('2013110202', '11003', 82),
('2013110202', '21001', 85),
('2013310101', '21004', 83),
('2013310101', '31002', 68),
('2013310103', '21004', 80),
('2013310103', '31002', 76),
('2014210101', '21002', 93),
('2014210101', '21004', 89),
('2014210102', '21002', 95),
('2014210102', '21004', 88),
('2014310101', '21001', 79),
('2014310101', '21004', 80),
('2014310102', '21001', 91),
('2014310102', '21004', 87);

单表查询

选择字段

  1. 查询指定字段
    例如查询所在班级的班级编号、所属学院和班级名称
    1
    2
    3
    4
    5
    6
    7
    select classNo, department, className from db_school.tb_class;
    -- 结果
    AC1301 会计学院 会计13-1班
    AC1302 会计学院 会计13-2班
    CS1401 计算机学院 计算机14-1班
    IS1301 信息学院 信息系统13-1班
    IS1401 信息学院 信息系统14-1班

从班级表中查询出所有院系名称

1
2
3
4
5
6
7
select department from db_school.tb_class;
-- 结果
会计学院
会计学院
计算机学院
信息学院
信息学院

如果想去掉上面的重复记录:

1
2
3
4
5
select distinct department from db_school.tb_class;
-- 结果
会计学院
计算机学院
信息学院

  1. 查询所有字段
    查询一个表中的所有字段有两种方法:一种是在select关键字后面列出所有的字段名;另一种是在select关键字后面直接使用*通配符。
    例如,查询全体学生的详细信息:

    1
    select * from db_school.tb_student;
  2. 查询经过计算的值
    select子句的“<目标表达式>”不仅可以是表中的字段名,也可以是表达式,还可以是字符串常量、函数等。
    例如,查询全体学生的姓名、性别和年龄。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    select studentName, sex, 'Age:', year(now()) - year(birthday) from db_school.tb_student;
    -- 结果
    张晓勇 男 Age: 21
    王一敏 女 Age: 22
    江山 女 Age: 22
    李明 男 Age: 22
    黄菊 女 Age: 23
    吴昊 男 Age: 23
    刘涛 男 Age: 21
    郭志坚 男 Age: 21
    王林 男 Age: 22
    李怡然 女 Age: 22
  3. 定义字段的别名
    例如,查询全体学生的姓名、性别、年龄,要求给目标表达式取别名:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    select
    studentName as 姓名,
    sex as 性别,
    year(now()) - year(birthday) as 年龄
    from db_school.tb_student;
    -- 结果
    姓名 性别 年龄
    张晓勇 男 21
    王一敏 女 22
    江山 女 22
    李明 男 22
    黄菊 女 23
    吴昊 男 23
    刘涛 男 21
    郭志坚 男 21
    王林 男 22
    李怡然 女 22

需要注意的是:当自定义的别名中含有空格的时候,必须使用单引号将别名括起来。例如:

1
2
3
4
5
select
studentName '姓 名',
sex '性 别',
year(now()) - year(birthday) '年 龄'
from db_school.tb_student;

选择指定记录

数据表中包含大量的数据,用户查询时可能只需要查询表中的指定数据,即对数据进行过滤。在select语句中,可以使用where子句,并根据where子句中指定的过滤条件(也称为搜索条件或查询条件),从from子句的中间结果中选择适当的数据行,实现数据的过滤。其语法格式为:

1
2
3
select 目标列表达式1, 目标列表达式2, ···, 目标列表达式n
from 表名
where 查询条件;

where子句常用的查询条件如下:

查询条件 操作符
比较 =, <>, !=, <, <=, >, >=, !<, !>, not+含比较运算府的表达式
确定范围 between and, not between and
确定集合 in, not in
字符匹配 like, not like
空值 is null, is not null
多重条件 and, or

比较大小

比较运算符用于指定目标列表达式的值,当目标列表达式的值与指定的值相等时,返回true,否则返回false
例如,查询课时大于等于48学时课程名称学分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询少数民族学生的姓名、性别、籍贯和民族
select studentName, sex, native, nation
from db_school.tb_student
where nation != '汉';

# 或
select studentName, sex, native, nation
from db_school.tb_student
where not nation = '汉';

# 或
select studentName, sex, native, nation
from db_school.tb_student
where nation <> '汉';

带between···and关键字的范围查询

例如,查询出生日期在指定范围的:

1
2
3
4
5
6
7
select studentName, sex, birthday
from db_school.tb_student
where birthday between '1997-01-01' and '1997-12-31';

select studentName, sex, birthday
from db_school.tb_student
where birthday not between '1997-01-01' and '1997-12-31';

带in关键字的集合查询

例如,查询籍贯是北京、天津和上海的学生的信息:

1
2
3
4
5
6
7
select *
from db_school.tb_student
where native in ('北京', '天津', '上海');

select *
from db_school.tb_student
where native not in ('北京', '天津', '上海');

带like关键字的字符串匹配查询

例如,查询学号为‘2013110201’的学生的详细情况:

1
2
3
select *
from db_school.tb_student
where studentNo like '2013110201';

查询所有姓王的学生的学号、姓名和班号

1
2
3
select studentNo, studentName, classNo
from db_school.tb_student
where studentName like '王%';

查询所有不姓王的学生的学号、姓名和班号

1
2
3
select studentNo, studentName, classNo
from db_school.tb_student
where studentName not like '王%';

查询姓名中包含’林’字的学生的学号、姓名和班号

1
2
3
select studentNo, studentName, classNo
from db_school.tb_student
where studentName like '%林%';

查询所有姓王且名字长度为三个中文字的学生的学号、姓名和班号

1
2
3
select studentNo, studentName, classNo
from db_school.tb_student
where studentName like '王__';

查询所有课程名称中包含下划线的课程信息

1
2
3
select *
from db_school.tb_course
where courseName like '%#_%' escape '#';

由于通常情况下,下划线是一个通配符,所以这里使用#作为转义字符,然后再使用escape指定一个转义字符#
使用通配符的时候需要注意,MySQL默认是不区分大小写的,如若需要区分大小写,需要更换字符集的校对规则;另外百分号不能匹配null。

使用正则表达式

例如:
查询课程名称中带有中文”系统”的课程信息

1
2
3
4
5
6
7
8
select *
from db_school.tb_course
where courseName regexp '系统';

# 或者
select *
from db_school.tb_course
where courseName like '%系统%';

查询课程名称中含有”管理”、”信息”或”系统”中文字符的所有课程信息

1
2
3
select *
from db_school.tb_course
where courseName regexp '管理|信息|系统';

is null关键字的查询

例如,查询缺少和含有先行课的课程信息:

1
2
3
4
5
6
7
8
9
# 查询缺少先行课的课程信息
select *
from db_school.tb_course
where priorCourse is null;

# 查询所有含先行课的课程信息
select *
from db_school.tb_course
where priorCourse is not null;

注意,“is null”不能用 “=null”代替,“is not null”不能用 “!= null”代替。

带and或or的多条件查询

例如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询学分>=3且学时大于32的课程名称、学分和学时数
select *
from db_school.tb_course
where credit >= 3 and courseHour > 32;

# 查询北京或上海的学生的姓名、籍贯和民族
select studentName, native, nation
from db_school.tb_student
where native = '北京' or native = '上海';

# 查询籍贯是北京或湖南的少数民族男生的姓名、籍贯和民族
select studentName, native, nation
from db_school.tb_student
where (native = '北京' or native = '湖南') and nation != '汉' and sex = '男';

对查询结果排序

1
2
3
4
5
6
7
8
9
10
# 查询学生的姓名、籍贯和民族并将查询结果按姓名升序排列
select studentName, native, nation
from db_school.tb_student
order by studentName;

# 查询学生选课成绩大于85分的学号、课程号和成绩信息,并将查询结果先按学号升序排列,再按成绩降序排列。
select *
from db_school.tb_score
where score > 85
order by studentNo, score desc;

注意,当对空值进行排序时,order by会将该空值作为最小值对待。

限制查询结果的数量

limit的用法格式为:

1
limit [位置偏移量, ]行数

其中,行数是指定需要返回的记录数;“位置偏移量”是一个可选参数,指示MySQL从哪一行开始显示,第一条记录的位置偏移量为0,第二条记录的位置偏移量为1······以此类推。如果不指定位置偏移量,系统将会从表中的第一条记录开始显示。
例如:

1
2
3
4
5
# 查询成绩排名在第3至第5的学生学号、课程号和成绩
select studentNo, courseNo, score
from db_school.tb_score
order by score desc
limit 2, 3;

从MySQL5.0开始,可以使用limit的另一种语法:

1
limit 行数 offset 位置偏移量

例如上面的案例代码可以修改成:

1
2
3
4
select studentNo, courseNo, score
from db_school.tb_score
order by score desc
limit 3 offset 2;

分组聚合查询

分组聚合查询是通过把聚合函数(如count()、sum()等)添加到一个带有group by分组子句的select语句中实现的。

使用聚合函数查询

聚合函数是MySQL提供的一类系统内置函数,常用于对一组值进行计算,然后返回单个值。使用聚合函数可以对数据进行分析。下表列出来常用的聚合函数:

函数名 说明
count( [distinct \ all] * ) 统计数据表中的记录数
count( [distinct \ all] <列名> ) 统计数据表的一列中值的个数
max( [distinct \ all] <列名> ) 求数据表中一列值中的最大值
min( [distinct \ all] <列名> ) 求数据表中一列值中的最小值
sum( [distinct \ all] <列名> ) 求数据表中一列值的总和
avg( [distinct \ all] <列名> ) 求数据表中一列值的平均值

其中,如果指定关键字distinct,则表示在计算时要取消指定列中的重复值;如果不指定,默认不取消重复值。注意,除了count(*)外,其余的聚合函数,包括count(<列名>)都会忽略空值。
例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询学生总人数
select count(*) from db_school.tb_student;

# 查询选修了课的学生总人数
use db_school;
select count(distinct studentNo) from tb_score;

# 计算选修课程编号为21001的学生平均成绩
select avg(score) from tb_score
where courseNo = '21001';

# 计算选修课程编号为21001的学生最高分
select max(score) from tb_score
where courseNo = '21001';

分组聚合查询

在select语句中,允许使用group by子句对数据进行分组运算。分组运算的目的是为了细化聚合函数的作用对象。如果不对查询结果进行分组,聚合函数作用于整个查询结果,对查询结果分组后,聚合函数分别作用于每个组,查询结果就会按组聚合输出。
group by子句的语法格式是:

1
[group by 字段列表] [having <条件表达式>]

其中, group by对查询结果按字段列表进行分组,字段值相等的记录分为一组;指定用于分组的字段可以是一列,也可以是多个列,彼此之间使用逗号分隔;having短语对分组结果进行过滤,仅输出满足条件的组。

注意:使用group by子句后,select子句的目标列表达式中只能包含group by子句中的字段列表和聚合函数。

例如:

1
2
3
4
# 查询各个课程号及相应的选课人数
select courseNo as '课程编号', count(studentNo) as '选课人数'
from tb_score
group by courseNo;

可以看出,使用group by 的时候select子句中最好包含分组字段,否则,count()的值四级意义不明确。
对于group by的使用,需要注意下面几点:

  1. group by子句中列出的每个字段都必须是检索列或有效的表达式,但不能是聚合函数。如果在select语句中使用表达式,则必须在group by中使用相同的表达式,不能使用别名。
  2. 除聚合函数外,select子句中的每个列都必须在group by中给出。
  3. 如果英语分组的列中含有null值,则null作为一个单独的分组返回;如果该列中存在多个null值,则将这些null值所在的行分为一组。

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 查询每个学生的选课门数、平均分、最高分
select studentNo, count(*) 选课门数, avg(score) 平均分, max(score) 最高分
from tb_score
group by studentNo;

# 查询平均分在80分以上的每个同学的选课门数、平均分和最高分
select studentNo, count(*) 选课门数, avg(score) 平均分, max(score) 最高分
from tb_score
group by studentNo
having avg(score) >= 80;

# 查询有2门课以上(含2门)课程成绩大雨88分的学生学号、学生姓名、及88分以上的课程数
select studentNo, count(*) 课程数
from tb_score
where score > 88
group by studentNo
having count(*) >= 2;

# 查询所有学生选课的平均成绩,但只有当平均成绩大于80的情况下才输出
select avg(score) 平均分
from tb_score
having avg(score) >= 80;

where和having的主要区别在于作用对象不同:where子句作用于基本表或视图,主要用于过滤基本表或视图中的数据行,从中选择满足条件的记录;having短语作用于分组后的每个组,主要通过用于过滤分组,从中选择满足条件的组,即having短语作用于分组后的聚合值而不是特定行的值来过滤数据。
此外,having短语中的条件还可以包含聚合函数,而where子句中则不可以;where子句在数据分组钱进行过滤,having短语则在数据分组后进行过滤。因而,where子句排除的行不包含在分组中,这就有可能改变聚合值,从而影响having子句基于这些值过滤掉的分组。
如果一条select语句拥有一个having短语而没有group by子句,则会把表中的所有短语都聚合到一个分组中。

连接查询

前面介绍的查询都是针对一个表进行的。如果一个查询同时涉及两个或多个表,则称为连接查询。连接查询是关系数据库中重要的查询方式,其中包括交叉连接、内连接和外连接。当两个或多个表中存在相同意义的字段时,便可以通过这些字段对相关的表连接查询。

交叉连接

交叉连接又称笛卡尔积,即把一张表的每一行与另一个表的每一行连接起来,返回两张表的每一行相连接后所有可能的搭配,其连接结果会产生一个没有意义的记录,所以这种查询实际很少使用。
交叉连接所对应的SQL语句的语法结构为:

1
2
3
select * from1 cross join2;
-- 或
select * from1, 表2;

例如,查询学生表和成绩表的交叉连接:

1
select * from tb_student cross join tb_score;

交叉连接返回的查询结果集的记录数等于其所连接的两张表记录函数的乘积。 因此,对于存在大量数据的表,应该避免使用交叉连接。同时,也可以在from子句的交叉连接后面,使用where子句过滤条件,减少返回的结果集。

内连接

内连接通过在查询中设置连接条件来移除交叉连接查询结果集中某些数据行。具体而言,内连接就是使用比较运算符进行表间某些字段值的比较操作,并将相匹配的数据行组成新的记录,其目的是为了消除交叉连接中某些没有意义的数据行。也就是说,内连接查询中,只有满足条件的记录才会出现在结果集中。
内连接有两种表示形式:

  • 使用inner join的显式语法结构:

    1
    2
    3
    4
    select 目标列表达式1, 目标列表达式2, ······, 目标列表达式n
    from table1 [inner] join table2
    on 连接条件
    [where 过滤条件];
  • 使用where子句定义连接条件的隐式语法结构:

    1
    2
    3
    select 目标列表达式1, 目标列表达式2, ······, 目标列表达式n
    from table1 table2
    where 连接条件 [and 过滤条件];

上述两种表示形式的差别在于:使用inner join连接后,from子句中的on子句可用来设置连接表的连接条件,而其他过滤条件则可以在select语句中的where子句中指定;而使用where子句定义连接条件的形式,表于表之间的连接条件和查询时的过滤条件均在where子句中指定。

等值与非等值连接

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 查询每个学生选修课程的情况
use db_school;
select tb_student.*, tb_score.*
from tb_student, tb_score
where tb_student.studentNo = tb_score.studentNo;

# 查询会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称
select studentNo, studentName, native, tb_student.classNo, className
from tb_student, tb_class
where tb_student.classNo = tb_class.classNo and department = '会计学院';

# 查询选修了课程"程序设计"的学生学号,姓名和成绩
select a.studentNo, studentName, score
from tb_student as a, tb_course as b, tb_score as c
where a.studentNo = c.studentNo and b.courseNo = c.courseNo
and courseName = '程序设计';

-- 或
select a.studentNo, studentName, score
from tb_student as a join tb_course as b join tb_score as c
on a.studentNo = c.studentNo and b.courseNo = c.courseNo
where b.courseName = '程序设计';

自连接

若某个表与自身进行连接,称为自表连接或自身连接,简称自连接。使用自连接时,需要为表指定不同的别名,且对所有查询字段的应用都必须使用别别名限定,否则select操作会失败。
例如:

1
2
3
4
5
6
7
8
9
10
# 查询与"数据库"这门课学分相同的课程信息
select c1.*
from tb_course as c1, tb_course as c2
where c1.credit = c2.credit and c2.courseName = '数据库';

-- 或
select c1.*
from tb_course c1 join tb_course c2
on c1.credit = c2.credit
where c2.courseName = '数据库';

自然连接

自然连接只有当连接字段在两张表中的字段名都相同时才使用,否则返回的时笛卡尔积的结果。自然连接在from语句中使用关键字natural join。
例如:

1
2
3
# 查询每个学生及其选修课程的情况,要求显示学生学号、姓名、选修课程号和成绩
select a.studentNo, studentName, courseNo, score
from tb_student as a natural join tb_score as b;

外连接

连接查询是要查询多个表中相关联的行,内连接查询只返回查询结果集中符合查询条件(即过滤条件)和连接条件的行。但是有时候查询结果也需要显示不满足连接条件的记录,即返回查询结果集不仅仅包括符合连接条件的行,还包括两个两个连接表中不符合连接条件的行。

外连接首先将连接的两张表分为基表和参考表,然后再以基表为依据返回满足和不满足连接条件的记录,就好像在参考表中增加了一条全部由空值组成的万能行,它可以和基表中所有不满足连接条件的记录行进行连接。

外连接按照连接表的顺序可以分为左外连接和右外连接两种。

左外连接

左外连接,也被称为左连接,用于返回关键字左边表(基表)的所有记录,并用这些记录与该关键字右边表(参考表)中的记录进行匹配,如果左表的某些记录在右表中没有匹配的记录,就和右表的万能行连接,即右表对应的字段值均被设置为空值null。

例如:

1
2
3
4
5
6
7
8
9
10
# 使用左外连接查询所有学生及其选修课程的情况,包括没有选修课程的学生,
# 要求显示学号、姓名、性别、班号、选修课程号和成绩
# 首先在学生表中插入一条记录
insert into tb_student
values
('2013310102', '林海', '男', '1996-01-18', '北京', '满', 'IS1301');
# 然后进行左连接查询
select a.studentNo, studentName, sex, classNo, courseNo, score
from tb_student as a left join tb_score as b
on a.studentNo = b.studentNo;

右外连接

也称为右连接,以右表为基表。
例如,使用右外连接查询所有学生及其选修课程的情况,包括没有选修课程的学生,要求显示学号、姓名、性别、班号、选修的课程号和成绩。

1
2
3
select courseNo, score, b.studentNo, studentName, sex, classNo
from tb_score as a right join tb_student as b
on a.studentNo = a.studentNo;

子查询

子查询也被称为嵌套查询,是将一个查询语句嵌套在另一个查询语句的where子句或having短语中,前者被称为内层查询或子查询,后者被称为外层查询或父查询。在整个select语句中,先计算子查询,然后将子查询的结果作为父查询的过滤条件。嵌套查询可以用多个简单查询构成一个复杂的查询,从而增加SQL的查询能力。

带in的子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 查询选修了课的学生
select studentName
from tb_student
where tb_student.studentNo in
(select distinct tb_score.studentNo from tb_score);

# 或者不使用子查询
select distinct studentName
from tb_student, tb_score
where tb_student.studentNo = tb_score.studentNo;

# 查询没有选修课的学生
select studentName from tb_student
where tb_student.studentNo not in
(select distinct tb_score.studentNo from tb_score);

带比较运算符的子查询

例如,查询班级“计算机14-1班”所有学生的学号、姓名:

1
2
3
4
5
6
7
8
9
10
11
12
# 查询班级“计算机14-1班”所有学生的学号、姓名
use db_school;
select studentNo, studentName from tb_student
where classNo =
(select classNo from tb_class
where className = '计算机14-1班');

# 或者使用连接查询
select studentNo, studentName
from tb_student left join tb_class
on tb_student.classNo = tb_class.classNo
where className = '计算机14-1班';

查询和“李明”在同一个班级学习的学生学号、姓名和班号:

1
2
3
4
5
6
7
8
9
10
select studentNo, studentName, classNo from tb_student as s1
where classNo =
(select classNo from tb_student as s2
where s2.studentName = '李明') and s1.studentName != '李明';

# 或者使用连接查询
select s2.studentNo, s2.studentName, s2.classNo
from tb_student as s1, tb_student as s2
where s1.classNo = s2.classNo and s1.studentName = '李明'
having s2.studentName != '李明';

查询男生中比某个女生出生年份晚的学生姓名和出生年份。

1
2
3
4
select studentName as 姓名, year(birthday) as 出生年份 from tb_student
where sex = '男' and year(birthday) > any(
select year(birthday) from tb_student where sex = '女'
);

查询男生中比某个女生出生年份晚的学生姓名和出生年份

1
2
3
4
5
6
7
8
9
10
11
12
select studentName as 姓名, year(birthday) as 出生年份
from tb_student
where sex = '男' and year(birthday) > all(
select year(birthday) from tb_student where sex = '女'
);

# 或者使用聚合函数
select studentName as 姓名, year(birthday) as 出生年份
from tb_student
where sex = '男' and year(birthday) > (
select max(year(birthday)) from tb_student where sex = '女'
)

带exists关键字的子查询

使用关键字exists构造子查询时,系统会对子查询进行运算以判断它是否返回结果集。如果子查询的结果集不为空,则exists返回的结果为true,此时外层查询语句将进行查询;如果子查询的结果集为空,则exists返回的结果为false,此时外层查询语句将不再进行查询。
由于带exists的子查询只返回true或false,内层查询的select子句给出字段名没有实际意义,所以其目标列表达式通常用*
例如,查询选修了课程号为“31002”的学生的姓名:

1
2
3
4
select studentName from tb_student as a
where exists
(select * from tb_score as b
where a.studentNo = b.studentNo and courseNo = '31002');

查询没有选修课程号号为”31002”的学生姓名:

1
2
3
4
5
6
7
8
9
10
11
12
13
select studentName from tb_student as a
where not exists(
select *
from tb_score as b
where a.studentNo = b.studentNo and courseNo = '31002'
);
# 或者使用not in
select studentName
from tb_student
where studentNo not in (
select studentNo from tb_score
where courseNo = '31002'
);

查询选修了所有课程的学生姓名:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 也就是说,查询这样的学生,没有一门课程是他没有选修的(双重否定)
select studentName
from tb_student as x
where not exists(
select *
from tb_course as c
where not exists(
select *
from tb_score
where x.studentNo = x.studentNo
and c.courseNo = c.courseNo
)
);

联合查询

使用union关键字可以把来自多个select语句的结果组合到一个结果集中,这种查询方式称为并运算或者联合查询。合并时,多个select子句中对应的字段数和数据类型必须相同。其语法格式是:

1
2
3
4
5
select -from -where
union [all]
select -from -where
[... union [all]]
select -from -where

其中不使用关键字all,执行的时候去掉重复的记录,所有返回的行都是唯一的;使用关键字all的作用是不去掉重复的记录,也不对结果进行自动排序。
例如,使用union查询“管理学”或“计算机基础”的学生学号:

1
2
3
4
5
6
7
8
9
10
11
12
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '管理学'
union
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '计算机基础';
# 或者
select distinct studentNo
from tb_score, tb_course
where tb_course.courseNo = tb_score.courseNo
and (courseName = '管理学' or courseName = '计算机基础');

使用union查询选修了“管理学”或“计算机基础”的学生学号:

1
2
3
4
5
6
7
8
9
10
11
12
13
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '管理学'
union all
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '计算机基础';

# 或者
select studentNo
from tb_score, tb_course
where tb_course.courseNo = tb_score.courseNo
and (courseName = '管理学' or courseName = '计算机基础');

使用union查询的时候需要注意以下几点:

  1. union语句必须由两条或两条以上的select语句组成,且彼此之间使用关键字union分隔;
  2. union语句中的每个select子句必须包含相同的列、表达式或聚合函数;
  3. 每个select子句中对应的目标列的数据类型必须兼容。目标列的数据类型不必完全相同,但必须是MySQL可以隐含转换的类型,例如,不同的数值类型或不同的日期函数。
  4. 第一个select子句中的目标列名会被作为结果集的列名;
  5. 联合查询中只能使用一条order by子句或limit子句,且它们必须置于最后一条select子句之后;

使用联合查询是标准SQL直接支持的集合操作,相当于集合操作中的并运算。MySQL的当前版本只支持并运算,交运算和差运算只能用子查询来实现。

查询选修了计算机基础和管理学的学生学号:

1
2
3
4
5
6
7
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '计算机基础'
and studentNo in (
select studentNo from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '管理学'
);

查询选修了计算机基础但没有管理学的学生学号:

1
2
3
4
5
6
7
select studentNo
from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '计算机基础'
and studentNo not in (
select studentNo from tb_score, tb_course
where tb_score.courseNo = tb_course.courseNo and courseName = '管理学'
);

# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了607.9k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×