超全MySQL学习笔记

落墨大大啊

已于 2023-05-04 00:13:01 修改

阅读量1.1k

点赞数 4

分类专栏： MySQL笔记文章标签： mysql 数据库笔记

于 2023-05-03 22:58:48 首次发布

本文链接：https://blog.csdn.net/luomomoa/article/details/130478284

版权

MySQL笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

0. 说明

本篇内容全部来自我学习MySQL时做的笔记，学习的过程中碰到过很多问题和疑问，也会查到很多资料，把这些汇总一下分享出来，一方面是给自己做个备份，另一方面也希望给后来者提供一点方便。

我做笔记比较重视知识的结构，文章侧边有生成的目录，初次学习可以先通过目录概览一下MySQL的整个学习内容，复查时通过目录精准到想要的内容即可。本篇笔记通过Markdown导入，个别语法可能无法很好适配，查看笔记时自动规避订正一下即可。

1. 数据库基本概念

数据（data）
数据是数据库中存储的基本对象，是用来描述事物的符号记录，数据有两个特点：

数据在不同的语境有不同的含义，也称语义
数据与数据的语义是不可分割的

数据库 （DataBase，DB）
数据库就是存储数据的仓库，是长期存储在计算机内的，有组织的，可共享的大量数据的集合

数据库管理系统 （DataBase Management System，DBMS）
数据库管理系统就是操纵和管理数据库的大型软件，是为了科学地组织和存储数据，高效地获取和维护数据而开发的专门软件，是一种位于数据库应用系统和操作系统之间的大型而复杂的软件系统

数据库系统（DataBase System，DBS）
数据库系统是指在计算机系统中引入数据库后的系统组成，包括数据库、数据库管理系统及其应用开发工具、应用程序（application，APP）、数据库管理员

SQL （Structured Query Lapguage）
操作关系型数据库的编程语言，是关系型数据库的统一标准

通过 SQL 语言操作 数据库管理系统，通过数据库管理系统管理 数据库及数据库里的数据。

2. 数据库存储结构

在存储数据的过程中会用到数据库服务器，所谓数据库服务器，是指在计算机上安装一个数据库管理系统（如MySQL），这个管理系统可以管理多个数据库。

一般开发人员会针对一个应用创建一个数据库，为保存应用中实体的数据，会在数据库中创建多个表（用于存储和描述数据的逻辑结构），每个表都记录着实体的相关信息。

数据库服务器、数据库和表的关系如图所示：
数据库服务器与数据库关系
数据库中包括表和索引，表则由表头、表的记录和表的字段组成。
数据存储在表中，表的横向称为行，每一行的内容为记录，表的纵向称为列，每一列的内容为字段。
表结构

3. SQL基本概念

3.1 SQL的概述

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

3.2 SQL的优点

简单易学，具有很强的操作性；
绝大多数重要的数据库管理系统均支持SQL；
高度非过程化，用SQL操作数据库时大部分的工作由DBMS自动完成；

3.3 SQL的分类

SQL语言共分为四大类：数据定义语言DDL，数据操纵语言DML，数据控制语言DCL，数据查询语言DQL

DDL(Data Definition Language)
数据定义语言，用来操作数据库、表、列等；常用语句：CREATE、ALTER、DROP
DML(Data Manipulation Language)
数据操纵语言，用来操作数据库中表的数据；常用语句：INSERT、UPDATE、DELETE、SELECT
DCL(Data Control Language)
数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT、REVOKE、DENY
DQL(Data Query Language)
数据查询语言，用来查询数据常用语句：SELECT

3.4 SQL的通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格或缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
MySQL注释：

单行注释：-- 注释内容 或 # 注释内容(MySQL特有)
多行注释：/* 注释内容 */

4. 数据库数据类型

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

4.1 整数类型

根据数值取值范围的不同，MySQL 中的整数类型可分为5种，分别是 TINYINT、SMALUNT、MEDIUMINT、INT 和 BIGINT，其中最常见的即为 INT 类型。

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32768
MEDIUMINT	3	0~16777215	-8388608~8388608
INT	4	0~4294967295	-2147483648~ 2147483648
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775808

4.2 浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数，浮点数的类型有两种：单精度浮点数类型（FLOAT）和双精度浮点数类型（DOUBLE）。而定点数类型只有一种即 DECIMAL 类型。

数据类型	字节数	无符号数的取值范围	有符号的取值范围
FLOAT	4	0 和 1.175494351E-38~3.402823466E+38	-3.402823466E+38~-1.175494351E-38
DOUBLE	8	0 和 2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308
DECIMAL（M,D）	M+2	0 和 2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308

其中，DECIMAL 类型的取值范围与 DOUBLE 类型相同，但是，DECIMAL类型的有效取值范围是由M和D决定的，其中，M表示的是数据的长度，D表示的是小数点后的长度，比如，将数据类型为 DECIMAL(6,2) 的数据 6.5243 插人数据库后显示的结果为 6.52。

4.3 字符串类型

在MySQL中常用 CHAR 和 VARCHAR 表示字符串。两者不同的是：CHAR 存储定长的字符串，VARCHAR 存储可变长度的字符串。
当数据为 CHAR(M) 类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而 VARCHAR(M) 所对应的数据所占用的字节数为实际长度加1，文本类型用于表示大文本数据，例如，文章内容、评论、详情等。

数据类型	储存范围	用途
CHAR	0~255	定长字符串
VARCHAR	0~65535	变长字符串
TINYBLOB	0~255	不超过255个字符的二进制字符串
TINYTEXT	0~255	短文本字符串
BLOB	0~65535	二进制形式的长文本数据
TEXT	0~65535	长文本数据
MEDIUMBLOB	0~16777215	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215	中等长度文本数据
LONGBLOB	0~4294967295	二进制形式的极大文本数据
LONGTEXT	0~4294967295	极大文本数据

4.5 日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值，在SQL语句中使用日期常量时，必须用英文的单引号或双引号引起来，否则执行结果不正确：

数据类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59~ 838:59:59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

YEAR类型
YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型的值。

使用 4 位字符串或数字表示，范围为 1901—2155 或 1901—2155。例如，输人 2019 或 2019 插人到数据库中的值均为2019。
使用 2 位字符串表示，范围为 00—99 。其中，00—69 范围的值会被转换为 2000—2069 范围的 YEAR 值，70—99 范围的值会被转换为 1970—1999 范围的YEAR 值。例如，输人 19 插人到数据库中的值为 2019。
使用 2 位数字表示，范围为 1—99。其中，1—69 范围的值会被转换为 2001— 2069 范围的YEAR值，70—99 范围的值会被转换为 1970—1999 范围的 YEAR 值。例如，输人19插入到数据库中的值为2019。
当使用YEAR类型时，一定要区分 0 和 0。因为字符串格式的 0 表示的 YEAR 值是 2000 而数字格式的 0 表示的 YEAR 值是 0000。

TIME类型
TIME类型用于表示时间值，它的显示形式一般为 HH:MM:SS，其中，HH表示小时， MM表示分，SS表示秒，在MySQL中，可以使用以下3种格式指定TIME类型的值。

以 D HH:MM:SS 字符串格式表示。其中，D 表示日可取 0—34 之间的值，插人数据时，小时的值等于 (DX24+HH)。例如，输入 2 11:30:50 插人数据库中的日期为 59:30:50。
以 HHMMSS 字符串格式或者 HHMMSS 数字格式表示。例如，输人 115454 或 115454，插入数据库中的日期为 11:54:54
使用 CURRENT_TIME 或 NOW() 输人当前系统时间。

DATETIME类型
DATETIME类型用于表示日期和时间，它的显示形式为 YYYY-MM-DD HH: MM:SS，其中，YYYY 表示年，MM 表示月，DD 表示日，HH表示小时，MM 表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。

以 YYYY-MM-DD HH:MM:SS 或者 YYYYMMDDHHMMSS 字符串格式表示的日期和时间，取值范围为 1000-01-01 00:00:00—9999-12-3 23:59:59。例如，输人 2019-01-22 09:01:23 或 20140122_0_90123 插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
以 YY-MM-DD HH:MM:SS 或者 YYMMDDHHMMSS 字符串格式表示的日期和时间，其中YY表示年，取值范围为 00—99。与DATE类型中的YY相同，00— 69 范围的值会被转换为 2000—2069 范围的值，70—99 范围的值会被转换为 1970—1999 范围的值。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，插入20190122090123 或者 190122090123，插人数据库中的DATETIME值都为 2019-01-22 09:01:23。
使用 NOW 来输人当前系统的日期和时间。

TIMESTAMP类型
TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式。

使用 CURRENT_TIMESTAMP 输人系统当前日期和时间。
输人 NULL 时系统会输人系统当前日期和时间。
无任何输人时系统会输入系统当前日期和时间。

4.6 二进制类型

在MySQL中常用 BLOB 类型数据存储二进制类型的数据，例如：图片、PDF文档等。

数据类型	储存范围
TINYBLOB	0~255
BLOB	0~65535
MEDIUMBLOB	0~16777215
LONGBLOB	0~4294967295

5. 数据库操作

5.1 创建数据库

create database [if not exists] 数据库名 [character set 字符集] [collate 校对规则];
[] 内的内容为可选值，其简洁写法如下：
create database 数据库名;

说明：
数据库名：即数据库的名称，MySQL的数据存储区将以目录方式表示MySQL数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义，在MySQL中不区分大小写；
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误；
CHARACTER SET 字符集：也写作 charset 字符集 ，指定数据库的字符集，指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况，如果在创建数据库时不指定字符集，那么就使用系统的默认（default）字符集，UTF8字符集长度为 3 字节，有些符号占 4 字节，所以推荐用 utf8mb4 字符集；
COLLATE 校对规则：指定字符集的默认校对规则，校对规则定义了比较字符串的方式；

5.2 选择/使用/切换数据库

use 数据库名;

5.3 查询指定数据库的建库语句

show create database 数据库名;

这里的表是指已经建好的数据库，使用 show create database 数据库名; 语句可以查看创建该数据库的过程

5.4 查看所有数据库

show databases;

5.5 查看数据库基本信息

show create database 数据库名;

5.6 查看当前使用的数据库名称

select database();

5.7 删除数据库

drop database [if exists] 数据库名;

5.8 修改数据库字符集

alter database 数据库名 character set 字符集;

6. 表操作

进行所有的表操作之前都要使用 use 数据库名; 语法进入数据库。

6.1 创建表

create table 表名 
	(
	字段1 字段1类型,
	字段2 字段2类型,
	字段3 字段3类型,
	...
	字段n 字段n类型
	);

创建表的语法中每个字段以 , 分隔，但最后一个字段后面没有逗号
创建表或字段的语句后可以使用 comment 注释 语句添加注释，如以上语法可以写为：

create table 表名 
(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释]
)[comment 表注释];

6.2 查询当前数据库所有表

show tables;

6.3 查询表结构

describe 表名;
或
desc 表名;

6.4 查询指定表的建表语句

show create table 表名;

这里的表是指已经建好的表，使用 show create table 表名; 语句可以查看创建该表的过程

6.5 修改表名

alter table 表名 rename to 新表名

6.6 删除表

drop table [if exists] 表名;

6.7 删除表并重新创建该表

truncate table 表名;

6.8 添加字段

alter table 表名 add 字段名 数据类型(数据长度) [comment 注释];

如，在 testtable 表中添加 testfield 字段，数据类型为 varchar(20)：
alter table testtable ADD testfield varchar(20) comment '注释';

6.9 删除字段

alter table 表名 drop 字段名;

6.10 修改字段数据类型

alter table 表名 modify 字段名 新数据类型(数据长度);

6.11 修改字段名和字段数据类型

alter table 表名 change 旧字段名 新字段名 数据类型(数据长度) [comment 注释];

如，将 testtable 表的 testfield 字段名修改为 testfield01，类型为 varchar(30)  ：
alter table testtable change testfield testfield01 varchar(30) comment '昵称';

7. 约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则，针对表中字段进行限制从而保证数据表中数据的正确性和唯一性，这些规则常称为表的约束，也即表的约束实际上就是表中数据的限制条件。常见约束如下：

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

MySQL中的约束分为为列级约束和表级约束，其中：
列级约束：NOT NULL、DEFAULT、PRIMARY KEY、UNIQUE、CHECK
表级约束：PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY
列级约束和表级约束在添加位置上也有所不同：
列约束：在 字段名数据类型 后面追加约束
表约束：在各个列字段的最下面，以CONSTRAINT 开头添加约束

CREATE TABLE 表名 (
字段名 字段类型,
字段名 字段类型 列级约束,
表级约束
);

7.1 主键约束

主键约束（PRIMARY KEY,PK）是指具有唯一标识表中每一行的值的一列或一组列的主键。被标识为主键的数据具有唯一性、非空性和最小化原则，用于强制实现表的实体完整性，类似于身份证号。

主键分为单字段主键和多字段联合主键，使用主键的时需要注意以下几点：

一个表只能定义一个主键；
主键值必须唯一标识表中的每一行，并且不能出现null的情况，即表中不能存在有相同主键的两行或两行以上数据，严格遵守唯一性原则；
一个字段名只能在联合主键字段表中出现一次；
联合主键不能包含不必要的多余字段，以满足最小化原则；

添加列级主键约束

# 创建表时添加约束
字段名 数据类型 primary key;

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 primary key;

示例：
# 创建表时添加约束
-- 方法一
create table student (
id int primary key,
name varchar(20)
);
-- 方法二
create table student (
id int,
name varchar(20),
primary key(id)
);

# 创建表后添加约束
alter table student modify column id int primary key;

添加表级主键约束

# 添加表级主键约束语法
create table 表名 (
字段名 字段类型,
字段名 字段类型,
primary key (字段名,字段名)
);

# 添加表级单字段主键（联合主键）
create table student (
id int,
name varchar(20),
primary key (id)
);

# 添加表级多字段联合主键
create table student (
id int,
name varchar(20),
primary key (id,name)
);

删除主键约束

alter table 表名 drop primary key;
示例：
alter table student drop primary key;

7.1.1 主键自增长

当主键定义为自增长后，主键的值就不需要自己再输入数据了，而是由数据库系统根据定义自动赋值，每增加一条记录，主键就会自动根据设置的步长进行增长。

在MySQL中，自增长的关键字是AUTO_INCREMENT，语法格式为：

字段名 数据类型 auto_increment;

例如：
create table student (
id int primary key auto_increment,
name varchar(20)
);

还可以设置自增起始数据：
create table student (
id int primary key auto_increment,
name varchar(20)
) auto_increment=0001 ;

7.2 非空约束

非空约束（NOT NULL）是指强制字段的值不能为空。

添加非空约束：

# 创建表时添加约束
字段名 数据类型 not null;

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 not null;

示例：
# 创建表时添加约束
create table student (
id int
name varchar(20) not null
);

# 创建表后添加约束
alter table student modify column name varchar(20) not null;

删除非空约束：

alter table 表名 modify 字段名 数据类型 null;
示例：
alter table student modify name varchar(20) null;

7.3 唯一约束

唯一约束（UNIQUE）用于保证数据表中字段的值具有唯一性，即表中字段的值不能重复出现，允许出现空值，但空值只能出现一次。

添加唯一约束：

# 创建表时添加约束
字段名 数据类型 unique;

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 unique;

示例：
# 创建表时添加约束
create table student (
id int,
name varchar(20) unique
);

# 创建表后添加约束
alter table student modify column name varchar(20) unique;

删除唯一约束：

alter table 表名 drop index 字段名;
示例：
alter table student drop index number;

7.4 默认约束

默认值约束（DEFAULT）用于指定数据表中字段的默认值，即当向表中插入一条新的记录时没有给该字段赋值，那么数据库系统会自动为这个字段插人默认值，默认约束常常用在某字段中数据存在大量重复值的情况。

添加默认约束：

# 创建表时添加约束
字段名 数据类型 default 默认值；

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 default 默认值;

示例：
# 创建表时添加约束
create table student (
id int,
name varchar(20),
gender varchar(10) default 'male'
);

# 创建表后添加约束
alter table student modify column gender varchar(10) default 'male';

删除默认约束：

# 方法一
alter table 表名 modify 字段名 数据类型;
示例：
alter table student modify gender varchar(10);

# 方法二
alter table 表名 alter column 字段名 drop default;
示例：
alter table student alter column gender drop default;

7.5 检查约束

7.6 外键约束

外键约束（FOREIGN KEY）用来在两个表的数据之间建立关联，可以是一列或者多列，一个表可以有一个或多个外键。

添加外键约束：

# 创建表时添加约束
constraint 外键名 foreign key (从表外键字段) references 主表 (主键字段)

# 创建表后添加约束
alter table 从表名 add constraint 外键名 foreign key (从表外键字段) references 主表 (主键字段);

示例：
-- 创建主表student，从表class，使class表的studentid字段作为外键关联student表的id字段

# 创建表时添加约束
create table student (
id int primary key,
name varchar(20)
);

create table class (
classid int primary key,
studentid int

constraint fk_class_studentid foreign key (studentid) references student(id)
);

# 创建表后添加约束
alter table class add constraint fk_class_studentid foreign key (studentid) references student(id);

删除外键约束：

alter table 从表名 drop foreign key 外键名;
示例：
alter table class drop foreign key fk_class_studentid;

7.6.1 外键与数据一致性

建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

7.6.2 外键约束注意事项

从表里的外键通常是主表的主键；
从表里外键的数据类型必须与主表中主键的数据类型一致；
主表发生变化时应注意主表与从表的数据一致性问题；

8. insert 数据插入

insert 语句用于向数据库中插入新的数据。

8.1 单行数据插入

用 insert 语句进行单行数据插入时，每个字段与其值是严格一一对应的，即每个值、值的顺序、值的类型必须与对应的字段相匹配。其语法如下：

insert into 表名 [(字段1,字段2 ...)] 
values (值1,值2 ...);

# 示例，在 student 表中插入数据
insert into student (id,name,age,sex) 
values (1,'张三',16,'男');

注意：

需要插入空值或碰到自增字段时，用 NULL 表示数据值；
插入语句中的各字段与其在表中定义的顺序、数量无需一致，只要与 VALUES 中值的顺序、数量相互匹配即可，也即可以让字段缺省实现部分数据插入；
当插入数据的顺序、数量与数据表中字段的顺序、数量一致时，参数 (字段1,字段2,...) 可以缺省不写；
使用 insert 插入语句时，要求插入的值与相应字段在数量和类型上完全匹配，否则插入操作失败；
使用 insert 插入语句时，除了需要正确的语法，还要求插入操作与数据表上的约束不冲突，否则正确的语法也可能插入失败；

8.2 多行数据插入

用 insert 语法进行多行数据插入，语法规则和单行数据一样，各数据值用 , 连接。其语法如下：

insert into 表名 [(字段1,字段2,字段3 ...)]
values (值11,值12,值13 ...),
(值21,值22,值23 ...),
(值31,值32,值33 ...);

# 示例，在 student 表中插入数据
insert into student (id,name,age,sex)
values (0001,'张三',16,'男'),
(0002,'李四',17,'男'),
(0003,'王二麻子',18,'女');

9. update 数据更新

update 语句用于更新数据表中已经存在的数据记录。

9.1 简单更新

用 update 语句进行数据插入的语法如下：

update 表名
set 字段名1 = 新值1/条件表达式, 字段名2 = 新值2/条件表达式
[where 条件表达式];

# 示例，在 student 表中 name 字段为 张三 的 数据行中，更改 age 字段为 20 ，sex 字段为 男
update student
set age=20,sex='男'
where name='张三';

其中，参数 字段名1 = 新值1/条件表达式 表示将要更新的数据，其中的新值既可以是具体值，也可以是表达式，参数 where 条件表达式 表示将要更新的数据所在位置。

注意：

用 insert 语句进行简单更新时，参数 where 条件表达式 是可选项，该参数省略时则说明更新指定字段的所有行的值；
使用 update 语句时，即使语法正确，如果操作的数据与其他表之间存在外键关系，更新操作也可能受限，或者需要设置级联更新，在实际操作中，一条存在外键的主键记录是不能被删除或修改的；

9.2 级联更新

级联更新是指更新父表主键值时，所有子表外键值自动与父表主键值匹配更新的现象，外键约束中，update 语句有如下四种属性：

CASCADE，级联操作属性：如果从父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则一并更新外键所在子表中的记录；
NO ACTION / RESTRICT，限制操作属性：在 MySQL 中两者等价，如果在父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则不允许更新，NO ACTION 来自标准的 SQL；
SET NULL，设置空值属性：如果在父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则设置子表中该外键值为空值(NULL)，这要求定义子表时该外键允许取空值（不为非空约束）；

要想设置级联更新，应该设置外键约束 update 属性为 CASCADE。

实现级联更新操作一般分为三个步骤：

检查子表的外键约束，并将其设置为级联更新属性；
检查主表及子表是否有相同信息记录；
如果有则从主表更新目标记录，子表即可由系统自动更新相应记录；

10. delete 数据删除

delete 语句用于删除数据表的一行数据或者多行数据，甚至所有数据。

10.1 简单删除

用 delete 语句进行数据删除的语法如下：

delete from 表名
[where 条件表达式]
[order by 字段名 [asc / desc]];
[limit 行数]

# 示例，删除 student 表中 age 字段值为 14 的所有数据行
delete from student
where age=14;

其中，参数 where 条件表达式 表示将要更新的数据所在位置；参数 order by 字段名 [asc / desc] 表示按照指定的顺序进行删除操作，asc 为升序（数值从小到大），desc 为降序（数值从大到小）；参数 limit 行数 用于告知服务器所限制删除的行数；order by 子句要和 limit 子句配合使用。

注意：

用 delete 语句进行简单删除时，参数 where 条件表达式 是可选项，该参数省略时则说明删除指定字段的所有数据行；
删除带有空值的数据行，where语句应写为 where 字段名 is null，如删除score字段为空值的数据行：where score is null ；
使用 delete 语句时，即使语法正确，如果操作的数据与其他表之间存在外键关系，更新操作也可能受限，或者需要设置级联更新，在实际操作中，一条存在外键的主键记录是不能被删除或修改的；

10.2 级联删除

级联删除是指删除父表主键值时，所有子表外键值自动与父表主键值匹配删除的现象，外键约束中，delete 语句有如下四种属性：

CASCADE，级联操作属性：如果从父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则一并删除外键所在子表中的记录；
NO ACTION / RESTRICT，限制操作属性：在 MySQL 中两者等价，如果在父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则不允许删除，NO ACTION 来自标准的 SQL；
SET NULL，设置空值属性：如果在父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则设置子表中该外键值为空值(NULL)，这要求定义子表时该外键允许取空值（不为非空约束）；

要想设置级联删除，应该设置外键约束 delete 属性为 CASCADE。

实现级联删除操作一般分为三个步骤：

检查子表的外键约束，并将其设置为级联删除属性；
检查主表及子表是否有相同信息记录；
如果有则从主表删除目标记录，子表即可由系统自动删除相应记录；

10.3 删除风险等级

DELETE 数据删除操作从语法来看比较简单，但因为删除操作会从当前数据库中清除数据，所以说是一种有风险的操作。删除操作有三个风险等级，具体如下：

删除数据表

DROP TABLE 表名;

该语句用于从数据库中删除已存在的数据表，包括数据表结构，且不可恢复，因此在使用时需特别小心。这是危险级别最高的删除命令，属于 DDL（数据定义语言）。

不可恢复的删除操作

TRUNCATE TABLE 表名;

该语句只能用于将数据表内全部数据删除，但空表结构不会被删除，因 TRUNCATE 删除操作后不记录 MySQL 日志，同样不可以恢复数据。这是危险级别次之的删除命令。

有条件恢复的删除操作

DELETE FROM table 表名
WHERE condition;

这是在特定条件下可恢复的删除操作，DELETE 语句会根据用户需求删除数据表内部分或全部数据记录，与 TRUNCATE 操作不同的是DELETE 操作记录 MySQL 日志，能有条件地实现数据回滚恢复。但从数据删除效率来说TRUNCATE 操作优于 DELETE 操作。

注意：

DELETE 语句后可跟 WHERE 子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录，TRUNCATE 语句则不能跟 WHERE 子句，因此只能用于删除表中的所有记录；
使用 TRUNCATE 语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用 DELETE 语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1；
DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句；
无论哪种删除操作都存在数据丢失的风险，所以在实际应用场景中执行删除命令是需要相应权限的；

11. 别名

别名就是一个字符串标识，使用别名可以让数据展示更加直观。

11.1 表的别名

在查询操作，特别是多表查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。其语法格式如下：

表名 [as] 表的别名 
或
表名 表的别名

# 例如，将 student 改为 stu 查询整表
select * from student as stu;

11.2 字段的别名

为字段取别名的语法格式如下：

字段名 [as] 字段的别名
或
字段名 字段的别名

# 例如，将 student 中的 name 取别名为“姓名”，age 取别名为 “年龄”，查询整表
select name as '姓名',age as '年龄'
from student;

注意：

别名字符串一般可以不加引号，但如果别名内有空格，则必须用单引号或双引号引起来；
字段名与别名之间也可以不用 as ，而直接用空格连接，如：select name 姓名,age 年龄 from student; ；
在 select 语句执行中，where 子句执行顺序在前，列别名生效在后，因此在 where 子句中不能用别名来判断，必须用列的原名判断，否则运行会报错；

12. 简单查询

select 语句用于从表中选取数据，并将结果存储在结果表中，这个结果表也常常称为结果集。

12.1 单字段/多字段查询

select 字段1,字段2,字段3...
from 表名;

# 示例，从 student 表中查询 name、age 字段
select name,age
from student;

12.2 去除重复记录查询

select distinct 字段1,字段2,字段3...
from 表名;

12.3 转义查询

转义即表示转义字符原来的语义，一个转义字符的目的是开始一个字符序列，使得转义字符开头的该字符序列具有不同于该字符序列单独出现时的语义。

select *
from 表名
where 条件表达式
escape '/';

# 示例，查询名字里带有_的人
select *
from student
where name like '/_%'
escape '/';

如上所述，_ 在MySQL中表示通配符，匹配一个字符，当要查询包含 _ 的字段时，就需要用 / 对 _ 进行转义， /_ 即表示字符 _ ，而非通配符；escape 关键字的主要作用就是指定一个字符替代 / 的作用。

注意：

select * from 表名; 表示查询该表的所有字段内容，即按照创建表时的顺序排列展示所有字段，但在实际应用中不建议使用 select * 语句，因为这会降低数据查询效率；
在 select 中除了书写列名，还可以书写字符串，这样书写可以用于标记，如查询日期标记字段： select stuid,stuname,'2023-03-20' from student; ;

13. 条件查询

在简单查询的基础上加上 where 子句，即可实现条件查询，其基本语法如下：

select 字段名1,字段名2... 
from 表名 
[where 条件表达式];

根据 where 子句的条件表达式不同，条件查询又可分为以下几种不同的类型

13.1 关系运算符查询

使用关系运算符构成 where 子句的条件表达式，可完成一类条件查询，MySQL中常用的关系运算符有：

关系运算符	说明
`=`	等于
`<=>`	等于
`<>`	不等于
`!=`	不等于
`<`	小于
`!<`	不小于
`<=`	小于等于
`>`	大于
`!>`	不大于
`>=`	大于等于

例如，查询 student 表中年龄等于或大于17的学生的信息：

select *
from student
where age >= 17;

注意：

= 和 <=> 几乎完全相同，区别在于 <=> 可以用来对 NULL 进行判断，被比较的两者都为 NULL 时，返回值为 1，即逻辑真；
<> 和 != 是完全等价的，都是进行不等于判断；

13.2 between and 关键字查询

between and 关键字用于判断某个字段的值是否在指定的范围之内，是SQL中专门关于闭区间判断的运算符，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 between 值1 and 值2;

# 例如，查询 student 表中字段 score 在 60 - 85 之间的学生的信息
select *
from student
where score between 60 and 85;

# 同时，between and 关键字 也可用关系运算符表示，即上述示例可表示为
select *
from student
where score >= 60 and score <= 85;

注意：

not between and 是 between and 运算的反运算，其格式为在 between and 运算前直接加 not，如：

# 查询 student 表中字段 score 在 60 - 85 以外的学生的信息
select *
from student
where score not between 60 and 85;

13.3 like 关键字和通配符查询

like 关键字用于判断两个字符串是否相匹配，可直接匹配字符串，也可配合通配符 % 或 _ 进行模糊查询，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 like 通配符表示的值;

# like 直接匹配字符串查询，查询 student 表中字段 name 为 陈 的学生的信息
select *
from student
where name like '陈';

# like 配合通配符进行模糊查询，查询 student 表中字段 name 姓为 陈 的学生的信息
select *
from student
where name like '陈%';

注意：

not like 是 like 运算的反运算，其格式为在 like 运算前直接加 not，如：

# 查询 student 表中字段 name 姓不为 陈 的学生的信息
select *
from student
where name not like '陈%';

13.4 and 关键字查询

and 关键字，也即逻辑与，用来关联两个或两个以上的条件，用 and 连接的条件需同时成立，其基本语法如下：

select 字段1,字段2...
from 表名
where 条件表达式1 and 条件表达式2;

# 例如，查询 student 表中 age 大于15 且 sex 为 male 的学生信息
select *
from student
where age > 15 and sex = 'male';

注意：

and 关键字所关联的各条件应在同一记录（同一行）中，而不是在同一字段（同一列）中，所以用 and 关键字连接的条件字段名应不相同；

13.5 or 关键字查询

or 关键字，也即逻辑或，用来关联两个或两个以上的条件，用 or 连接的条件只要满足其中任意一个即可，其基本语法如下：

select 字段1,字段2...
from 表名
where 条件表达式1 or 条件表达式2;

# 例如，查询 student 表中字段 addr 为新疆或西藏的学生的信息
select *
from student
where addr = 新疆 or addr = 西藏;

注意：

AND(与) 和 OR(或) 运算是有优先级的，AND 的优先级要高于 OR；
在同时有 AND 和 OR 的运算中，要使 OR 运算优先运行，可以用小括号 () 把 OR 连接的条件括起来，小括号的优先级是最高的；

13.6 in 关键字查询

in 关键字用于判断某个字段的值是否在指定集合中（两个或两个以上同一字段的条件时），其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 in (值1,值2...);

# 例如，查询 student 表中字段 addr 为新疆、西藏的学生的信息
select *
from student
where addr in ('新疆','西藏');

注意：

in 关键字和 and 关键字大体上是一样的，不同在于，当多个条件所在的字段不同时，只能用 and 关键字，当多个条件所在的字段相同时，使用 in 关键字写法更简洁；
not in 为不包含运算，是 in 运算的反运算，其格式为在 in 运算前直接加 not，如：

# 查询 student 表中字段 addr 为新疆、西藏之外的学生的信息
select *
from student
where addr not in ('新疆','西藏');

13.7 null 值查询

在MySQL中，使用 IS NULL 关键字来判断字段的值是否为空值 NULL，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 is null;

# 例如，查询 student 表中字段 score 为 null 的学生的信息
select *
from student
where score is null;

注意：

NULL 值是数据库中一个非常特殊的值，叫空值，它不是数值中的 0，也不是字符中的空格或空字符串，NULL 是 a missing unknownvalue，即不存在、不确定的意思；
要判断 NULL 值，必须有专门的比较运算符或函数，IS NULL 就是用于判断空值 NULL 是函数；
因为 is null 是一种函数，所以 字段名 is null 等同于 is null (字段名) ，两者效果一样；
IS NULL 专门用来判断空值，不能用 = null 表示，而是等同于 <=> null ，仅能用 <=> 判断NULL，这也是 = 和 <=> 的区别；
is not null 是 is null 运算的反运算，其格式为在 is null 运算间直接加 not，如：

# 查询 student 表中字段 score 不为 null 的学生的信息
select *
from student
where score is not null;

14. 统计查询

14.1 group by 分组查询

group by 子句会先将表中的数据进行分组，再进行查询等操作，可理解为将原来的表拆分成了几张小表，其基本语法如下：

select 字段1,字段2...
from 表名
group by 字段3,字段4...

# 例如，统计 student 表中每个班级的学生的平均年龄
select classid 班级, avg(stuage) 平均年龄
from student
group by classid;
# 即以 classid 为分组依据，查询 student 表中 classid 和 avg(stuage) 两个字段

注意：

select 语句的各子句的相对位置不能前后颠倒，如 group by 子句只能在 where 子句后面，having 子句只能在 where 子句后面；
group by 子句后可跟多个字段，即多级分组，在多级分组时，一级分组内有重复数据，二级分组才会有意义，以此类推；
group by 子句后跟的多个字段的顺序直接控制了数据源中数据的分组方式，即从前往后依次为一级、二级、三级…分组，这类似于 Excel 中的多级排序；
在具体应用中，group by 子句常与聚合函数一起使用；
一般在 select 语句中加入 group by 子句的分组依据列，以提高查询结果集的可读性；

14.2 聚合函数查询

聚合统计函数也叫组函数、聚簇函数，与数学函数、字符串函数等不同，在默认情况下，聚合函数会把当前表中的数据当做一个组进行统计，聚合函数有以下几种类型：

count()：计数函数，统计表的行数或某个列下值的个数。
max()：最大值函数，求某列值中的最大值。
min()：最小值函数，求某列值中的最小值。
sum()：求和函数，对表中某列的数值进行求和操作，显然这个列只能是数值类型列，否则报错。
avg()：平均值函数，对表的某列进行求平均值操作，显然这个列也只能是数值类型列，否则报错。

注意：

聚合函数具有自动滤空的功能，若字段中某一个值为NULL，那么会自动将其过滤使其不参与运算；
使用 count() 函数统计时，如果统计的字段没有空值，则可以写为 count(字段名) 或 count(*)，两种写法效果一样；
聚合函数是对结果集运算的，而 where 子句处于确定结果集的过程中，所以聚合函数是不能直接使用在 where 子句后面的，需要时可以利用子查询间接使用，如：查询 score 表中分数高于平均分的内容 select * from score where score > (select avg(score) from score);;

14.3 having 统计限制

having 子句以来实现对分组统计的条件限制，与 where 子句的条件限制功能有些相似，但如果限制条件发生在分组统计函数上，则必须通过 having 子句来实现，其基本语法如下：

select 字段1,字段2...
from 表名
group by 字段3,字段4...
having 条件表达式

# 例如，统计 score 表中平均分高于 80 的学生的学号和平均分
select stuid 学号,avg(score) 平均分
from score
group by stuid
having 平均分 > 80;

注意：

select 语句的各子句的相对位置不能前后颠倒，group by 子句只能在 where 子句后面，having 子句只能在 where 子句后面；

where 子句和 having 子句的区别：

where 子句和 having 子句都有过滤数据（行筛选）的功能，区别在于， where 子句在 group by 分组和聚合函数之前对数据行进行条件筛选，而 having 子句在 group by 分组和聚合函数之后的数据行进行条件过滤；
正因为 where 子句在 group by 分组和聚合函数之前对数据行进行过滤，所以 where 子句中不能使用聚合函数，反之，having 子句中不能使用除了分组字段（即 group by 子句后跟着的字段）和聚合函数之外的其他字段；
having 子句只能过滤 select 语句后面包含的字段，where 子句可以筛选表中所有字段，包括 select 语句后面包含的字段；
从性能的角度来说，having 子句中如果使用了分组字段作为过滤条件，应该替换成 where 子句；因为 where 子句可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好；

15. 排序查询

通过 select 语句查询的默认查询结果集中，数据是按照数据记录的物理顺序来显示的，当默认的查询结果顺序无法满足需求时，可通过添加 order by 子句来控制查询结果的顺序。

15.1 order by 排序查询

order by 子句用来对查询结果集进行排序，排序依据即其后指定的字段，排序方式由 asc 或 desc 控制，其基本语法如下：

select 字段1,字段2...
from 表名
order by 字段3,字段4...

# 例如，查询 score 表中的学号及分数，并按分数高低降序排列
select stuid,score
from score
order by score desc;

注意：

order by 子句中，asc 为升序（数值从小到大），desc 为降序（数值从大到小），排序方式在缺省状态下默认为升序 asc；
若 order by 子句后跟着多个字段，即指定了多个排序依据，产生多级排序，从前往后依次为一级、二级、三级…排序，同时，各个排序依据还能指定不同的排序方式，如 order by score desc,stuid asc ；
order by 子句后指定的排序依据除了能是字段名，还能是查询后的字段顺序的数值（即 select 语句后跟着的字段顺序，值为1,2,3…），如上述例子还可写为 select stuid,score from score order by 2 desc; ；
若排序字段为空值，则该值为最小值，即在降序排序中空值显示在最后，在升序排序中显示在最前；
当 order by 子句后的字段值为数字或字母时，即按照数字或字母的顺序进行排序，当字段值为中文汉字时，会根据对应字符的ASCII码排序，而不是汉字的首字母顺序，因此排序结果往往都是错误的，这时需要借助相应的函数才能实现想要的效果；

15.2 limit 限制行数

limit 子句常常应用于分页系统，控制在某一页中显示的数据记录数量，通常与 order by 子句一起使用，其基本语法如下：

select 字段1,字段2...
from 表名
order by 字段3,字段4...
limit 行数;

# 例如，查询 score 表中的学号及分数，只显示前十名
select stuid,score
from score
order by score desc
limit 10;

注意：

limit 子句格式为 limit a，如 limit 6 时，指显示查询结果集的前 6 行；
limit 子句格式为 limit a,b，如 limit 6,3 时，指从查询结果集的第 7 行开始，一共显示 3 行，当起始行 a 为 0 时，limit 6 等价于 limit 0,6；
当 limit a 中查询结果记录总数不足 a 行，则按实际结果集记录数量显示；
当 limit 子句和 offset 子句一起出现时，limit 子句决定显示行数，offset 子句决定显示起始点，如 limit 3 offset 6 表示从第 7 行开始，显示 3 行；

16. 内连接查询

所谓内连接(inner join)，就是在表关系的笛卡尔积(Cartesian product) 数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录，也就是在内连接查询中，只有满足条件的元组(行) 才能出现在结果关系中，不匹配的行不显示。根据不同的语法形式，内连接查询分为隐式内连接查询(SQL86 标准) 和显式内连接查询(SQL92 标准)。

16.1 隐式内连接查询

隐式内连接，就是使用 where 条件，消除不符合条件的无用数据，也即消除笛卡尔积的无用数据，其基本语法如下：

select 字段1,字段2...
from 表1,表2...
where 连接条件/筛选条件

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student,score
where student.stuid = score.stuid;

16.2 显式内连接查询

显式内连接，就是使用连接关键词 join 显式连接多个表数据，其基本语法如下：

select 字段1,字段2...
from 表1 [inner] join 表2 on 连接条件
[inner] join 表3 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student join score on student.stuid = score.stuid;

16.3 内连接注意事项

隐式内连接中，where 子句后面往往首先是多表之间的连接条件，需要的话也可用 and 运算符将筛选条件与之联合，where 子句之后的筛选条件语法与单表查询语法类似；
隐式内连接中，where 子句后面可以有 GROUP BY、HAVING、ORDER BY 等子句；
显式内连接中，[inner] join 的 inner 可以缺省，仅 join 就代表内连接；
显式内连接中，每个 join 子句之后必须有 on 子句，on 子句用来说明两表之间的内连接关系；
显式内连接中，where 子句在此仅用于控制数据筛选条件的限定，用法与表单查询类似，后面也可以有 GROUP BY、HAVING、ORDER BY 等子句；
显式内连接查询比隐式内连接查询应用更广泛；
内连接条件在很多情况下发生在有关系的数据表主外键之间；
多表查询时，如果关联的两个数据表中存在同名字段，则同名字段前必须加表名限定，其格式为 表名.字段名，此时为了书写方便，往往会给表取一个别名，该别名仅在当前 select 语句内部有效；
在连接查询中常用 表名.字段名 的形式引用字段，主要是引用同名字段的需要，再多表查询中，对于非同名字段也建议采用这种形式，这将有利于提升查询效率；

17. 外连接查询

内连接是将满足条件的记录进行连接，不满足条件的记录是被排除在结果集之外的，如果需要的数据还涉及不满足条件的记录，就需要进行外连接查询。MySQL目前不支持全外连接查询，但可以用其他方式替代解决，如可通过联合多个查询的方式达到全外连接查询的效果。外连接查询分为左外连接查询和右外连接查询。

17.1 左外连接查询

左外连接时以左表为基本表，左表的内容全部显示，根据连接条件，右表与左表匹配的内容正常连接(中间重合部分)，无法匹配的内容则用空值与左表连接，其基本语法如下：

select 字段1,字段2...
from 左表 left [inner] join 右表 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student left join score on student.stuid = score.stuid;

17.2 右外连接查询

右外连接与左连接查询语法相同，只是以右表为基本表，右表的内容全部显示，根据连接条件，左表与右表匹配的内容正常连接(中间重合部分)，无法匹配的内容则用空值与右表连接，其基本语法如下：

select 字段1,字段2...
from 左表 left [inner] join 右表 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from score right join student on student.stuid = score.stuid;

17.3 外连接注意事项

左外连接和右外连接的形式和作用基本一样，唯一区别就是所关联数据源的顺序不同，两种类型的外连接查询语句可互换，具体使用哪种形式没有特别要求，根据方便程度决定即可；
外连接查询时可以通过 where 子句筛选数据，where 子句后面可以有 GROUP BY、HAVING、ORDER BY 等子句；
外连接结果集是基本表的全部内容加上左右表的重复内容，也就是说外连接结果集包含内连接结果集；
左右外连接查询时，将左右表的位置互换，并更改 left join 或 right join 为对方，就可实现左右外连接互换；

18. 自连接查询

连接操作不仅可以在两个数据表之间进行，也可以是一个数据表与自己进行连接，称为表的自连接，也称为自身连接，自连接查询的语法同内连接查询的语法一样，只是多个数据表都是由同一个物理表形成的，其基本语法如下：

select 别名1.字段1,别名1.字段2...别名2.字段1,别名2.字段2...
from 表1 [as] 别名1 [inner] join 表2 [as] 别名2 on 连接条件
[where 筛选条件]

# 例如，查询 score 表中两门课成绩都大于 90 的学生学号及分数
select distinct t1.stuid,t1.score 成绩一,t2.score 成绩二
from score t1 join score t2 on t1.stuid = t2.stuid
where t1.cid != t2.cid and t1.score > 90 and t2.score > 90;

注意：

自连接是一种特殊的内连接，只要给数据表取两个不同的别名，就可以在逻辑上将一个物理表当成两个表来使用；
在自连接时，所有列都是两表共有的同名列，对列的运用必须使用 别名.列名 的形式，否则出现二义性；

19. 交叉连接查询

交叉连接是没有任何限制条件的连接，也称为笛卡尔连接，因为其产生的结果集与笛卡尔积范围一样，将产生两个数据源能连接的最大结果集，除了包含内连接，外连接的所有数据外，还有大量没有意义的数据，所以交叉连接几乎没有实际应用价值，其基本语法如下：

select 字段1,字段2...
from 表1 cross [inner] join 表2;

# 例如，将 score 和 student 表交叉连接后展示所有数据
select *
from score cross join student;

注意：

交叉连接查询中，join 关键字后面没有 on 子句，也就是不设连接条件；
交叉连接查询中，最大字段数（列数）= 左表字段数 + 右表字段数，最大记录数（行数）= 左表记录数 * 右表记录数；
交叉连接查询是从左表依次取出每一条记录，与右表中的全部记录逐个、无条件地连接，因此产生出两个数据表连接的最大结果集；

20. 合并查询

合并查询是用 union 或 union all 语句将两条或两条以上的 select 语句的查询结果集合并，其基本语法如下：

select 字段1,字段2...
from 表1
union / union all
select 字段1,字段2...
from 表2;

# 例如，查询 student 表中学生姓名和学号
select stuid
from student
union all
select stuname
from student;

注意：

UNION ALL 将两个查询结果合并，对于重复的数据不做消除重复的操作；
UNION 将两个查询结果合并，对于重复的数据会做消除重复的操作；
被合并的所有查询必须包含相同列数，对应列的数据类型必须兼容；

21. 子查询

在 SQL 中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块中的查询称为嵌套查询，嵌套在查询块中的内层查询称为子查询，相应的外层查询称为父查询。SQL 允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。根据子查询中查询到的结果集规模不同，可以将子查询分为单值子查询、列子查询、行子查询和表子查询。

21.1 单值子查询

当子查询结果集为单个值时，称之为单值子查询，也称为标量子查询，其基本语法如下：

select 字段1,字段2...
from 表1
where 字段3 = (select 字段3 from 表2 where 条件表达式);

# 例如，在 course 和 score 表中查询选修了 数据库技术及运用 的学生学号和成绩
select stuid,score
from score
where cid = (select cid from course where cname = '数据库技术及应用');

注意：

单值子查询一般表现为父查询与子查询之间用 = 、< = >、<、>、<=、>=、!=、<>、!< 、!> 等比较运算符进行连接，通常子查询的位置在关系比较运算符的右侧；
子查询必须用英文圆括号包围起来，否则语法会出错；
在这类子查询中，SQL 语句先运行嵌套在最内层的子查询，再逐层向外运行。在设计嵌套查询语句时，可以先测试内层子查询语句是否正确，再逐层向外测试，以增加嵌套查询正确率；
子查询一般都可以通过连接查询实现相同功能；
子查询可以嵌套多层，但嵌套级别越多效率越低，必要时可以通过连接查询来代替多层嵌套查询；
ORDER BY 子句不能用于子查询，只能对最外层的查询结果进行排序；

子查询的相关性

根据子查询的查询条件是否依赖父查询，可以把子查询分为非相关子查询和相关子查询。若子查询不依赖父查询，则称为不相关子查询，若子查询依赖父查询，则称为相关子查询。不相关子查询的子查询可以单独执行，相关子查询的子查询不能单独执行。

21.2 列子查询

当子查询的结果为一列值（同一字段的多个字段值）时，称为列子查询，其基本语法与单值子查询一致，如：

# 在 student 和 score 表中查询女生的学号，选修课程和成绩
select stuid,cid,score
from score
where stuid in (select stuid from student where stusex = '女');
# 在该查询语句中，内层查询的结果集为所有女生的学号（多个值），in 关键字表示外层查询的条件就是 以这些学号值 组成的集合，外层查询以这个集合为基准匹配查询对应的 score 表值

注意：

列子查询作为其父查询的查询条件时，一般通过 IN、ANY、SOME 和 ALL 等关键字进行关联，不能用关系比较运算符关联；

21.3 行子查询

当子查询的结果为一行值（同一记录的多个记录值）时，称为行子查询，一般这种情况很少出现，其基本语法如下：

select 字段1,字段2...
from 表1
where (字段3,字段4...) = (select 字段3,字段4... from 表2 where 条件表达式);

# 例如，在 student 表中查询与 学号为1511101的同学 班级和籍贯相同的学生信息
select *
from student
where (classid,addr) = (select classid,addr from student where stuid = 1511101);
# 在该查询语句中，先通过内层查询找出学号为 15111 的同学的 班级和籍贯，然后在所有学生信息中匹配查找符合这些情况的学生

注意：

行子查询和单值子查询都可以用比较运算符进行关联，不同的是前者返回的结果是单行值，后者返回的结果是单个值；
如果子查询返回的结果集不止一行，使用比较运算符进行关联时会报错，可根据需要用 IN、ANY、SOME 和 ALL 进行关联；

21.4 表子查询

当子查询的结果集为多行多列，称为表子查询，在 SELECT 语句中所有能使用数据源的地方，都能嵌入表子查询，比较常见的嵌入位置为 WHERE 和 FROM 子句，其基本语法与行子查询一致，如：

# 在 student 表中查询与 15111班的同学 年龄和籍贯相同的学生信息
select *
from student
where (stusex,addr) in (select stusex,addr from student where classid = 15111);
# 在该查询语句中，先通过内层查询找出 15111 班同学里 年龄和籍贯 的所有组合情况，然后在所有学生信息中匹配查找符合这些情况的学生

注意：

表子查询和列子查询一般通过 IN、ANY、SOME 和 ALL 等关键字与外层查询进行关联，不能用关系比较运算符关联，不同的是后者返回的结果是单列值，前者返回的结果是多列值；
表子查询嵌套在外层查询的FROM子句，必然要与其他数据表进行连接，为了能在外层查询中引用该虚拟表及其内部各列，必须为虚拟表设置表别名，如果虚拟表内部有统计列或计算列，同样需要设置列别名，否则无法引用；
几乎所有多表查询任务都可以分别通过嵌套查询或连接查询实现，在实际海量数据应用场景中，应该根据效率优先原则确定合适的查询语句；

21.5 子查询中各关键字查询

子查询中各关键字查询通常在列子查询和表子查询中使用，即用于内层查询返回的结果集有**多个值（这些值可以分布在一个或多个字段中，但每个字段都应有两个或两个的字段值）**的情况。

21.5.1 子查询之 in 关键字

子查询中 in 关键字表示外层查询的条件是以内层查询的结果集组成的集合，如：

# 在 student 和 score 表中查询女生的学号，选修课程和成绩
select stuid,cid,score
from score
where stuid in (select stuid from student where stusex = '女');
# 在该查询语句中，内层查询的结果集为所有女生的学号（多个值），in 关键字表示外层查询的条件就是 以这些学号值 组成的集合，外层查询以这个集合为基准匹配查询对应的 score 表值

21.5.2 子查询之 all 关键字

子查询中 all 关键字表示外层查询返回的结果要满足内层查询的结果中的所有值，如：

# 在 student 和 score 表中查询成绩高于 学号为1511204的同学 选修的每一门课程成绩的同学的选修信息
select *
from score
where score > all (select score from score where stuid = 1511204);
# 在该查询语句中，内层查询的结果集为学号为1511204的同学的所有选修课程成绩（多个值），all 关键字表示外层查询返回的结果集中的 成绩值 将大于内层查询的结果集中所有的成绩值

21.5.3 子查询之 any 关键字

子查询中 any 关键字表示外层查询返回的结果要满足内层查询的结果中的任意一个值，如：

# 在 score 表中查询成绩低于 30106 课程任一成绩的选修信息，不包括30106课程的选修信息
select *
from score
where score < any (select score from score where cid = 30106) and cid != 30106;
# 在该查询语句中，内层查询的结果集为 30106 课程的所有成绩（多个值），all 关键字表示外层查询返回的结果集中的 成绩值 将小于内层查询的结果集中任意一个成绩值

21.5.4 子查询之 exists 关键字

子查询中 exists 关键字表示存在，其后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，通过对内层查询进行运算判断其结果集是否为空，exists 关键字不产生任何数据，只返回 TRUE（子查询结果集不为空）或 FALSE（子查询结果集为空），当返回值为 TRUE 时外层查询才会执行，如：

# 若有计算机应用基础课程，则在 score 表中查询 30106 课程的选修信息
select *
from score
where exists (select * from course where cname = '计算机应用基础') and cid = '30106';
# 在该查询语句中，内层查询的结果集不为空，exists 关键字返回 TRUE，外层查询开始进行

注意：

EXISTS 返回的是 TRUE 或 FALSE，所以他所带的子查询一般直接用 select *，因为给出列名也没多少意义；
EXISTS 属于相关子查询，也就是说子查询的条件依赖于外层父查询的查个属性值；

22. 相关子查询与非相关子查询

嵌套查询语句根据子查询是否具有独立执行能力，分为非相关子查询和相关子查询。

22.1 非相关子查询

非相关子查询即常见的子查询，又叫嵌套子查询，即子查询可独立执行，不依赖于父查询表的查询，以上的例子基本都属于相关子查询，其基本语法如下：

select 字段1,字段2...
from 表1
where 字段3 = (select 字段3 from 表2 where 条件表达式);

# 在 student 和 score 表中查询女生的学号，选修课程和成绩
select stuid,cid,score
from score
where stuid in (select stuid from student where stusex = '女');

22.2 相关子查询

相关子查询的执行依赖于外部查询的数据，构造非相关子查询的查询条件时需要用到父查询的某一个属性列，相关子查询是无法独立执行的，因为它必然含有对外部查询中元组分量的引用，最常见的相关子查询是 EXISTS 关键字引导的子查询，其基本语法如下：

select 字段1,字段2...
from 表1
where exists (select 字段3 from 表2 where 表1.字段名 = 表2.字段名);

# 在 student 和 score 表中查询女生的学号，选修课程和成绩
select stuid,cid,score
from score t1
where exists (select * from student t2 where t1.stuid = t2.stuid and stusex = '女');
# 在该查询中，先执行外部查询，每次获取 score 表中的一行数据，并得到相同的字段 stuid 作为子查询的查询条件，然后执行内部查询，判断 stusex 的值是否为女，若是，返回 TRUE，外层循环数据保留，若不是，返回 FALSE，外层循环数据删除，依次执行这个过程，直至 score 表中所有数据行被执行判断，最后保留所有有效数据

注意：

相关子查询的条件依赖于外层父查询的查个属性值，即对外部查询中元组分量的引用，在上述例子中，该属性值即 stuid，引用形式为 t1.stuid = t2.stuid；

23. 查询的执行顺序

23.1 普通查询执行顺序

select 语句中，各子句的相对位置应固定，不能前后颠倒，其各子句书写顺序如下：

select - from - where - group by - having - order by - limit

普通查询如条件查询、统计查询、排序查询、连接查询等，其各子句执行顺序如下：

from - where - group by - having - select - order by - limit

示例：

# 在 student 表中查询各班级女生的平均年龄，并按平均年龄降序排序，只取前三行数据
select classid 班级, avg(stuage) 平均年龄
from student
where stusex = '女'
group by classid
order by avg(stuage) desc
limit 3;

23.2 子查询执行顺序

从执行顺序来说，相关子查询与非相关子查询是不一样的。

23.2.1 非相关子查询执行顺序

在非相关子查询中，子查询的优先级要高于父查询的优先值，子查询总共执行一次，执行完毕后将值传递给外部查询，其运行流程为：

先运行子查询，但子查询的结果不被显示，而是传递给外部查询，作为外部查询的条件使用；
根据子查询的结果，再运行父查询，并显示整个结果；

示例：

select stuid,cid,score
from score
where stuid in (select stuid from student where stusex = '女');
# 在该查询中，查询的执行顺序为：
# 1. 先执行内层查询，获得 student 表中 stusex 属性值为女的 stuid 属性的值，并把 stuid 属性值作为外层查询的条件；
# 2. 再运行外层查询，根据条件筛选出符合要求的结果集；

23.2.2 相关子查询执行顺序

在相关子查询中，外部查询执行一行，子查询就执行一次，其运行流程为：

从外层查询中取出一个元组（即一行数据），将元组相关列的值传给内层查询；
执行内层查询，得到子查询操作的值；
外查询根据子查询返回的结果或结果集得到满足条件的行；
然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕；

示例：

# 在 student 和 score 表中查询女生的学号，选修课程和成绩
select stuid,cid,score
from score t1
where exists (select * from student t2 where t1.stuid = t2.stuid and stusex = '女');
# 在该查询中，查询的执行顺序为：
# 1. 先执行外部查询，从外层查询中取出一个元组（获取 score 表中的一行数据），将元组相关列的值传给内层查询（得到外层查询和内层查询的相同的字段 stuid 作为子查询的查询条件）；
# 2. 然后执行内部查询，得到子查询操作的值（判断 stusex 的值是否为女，若是，返回 TRUE，若不是，返回 FALSE）；
# 3. 外查询根据子查询返回的结果得到满足条件的行（若内层查询返回 TRUE，外层循环数据保留，若返回 FALSE，外层循环数据删除）；
# 4. 依次上述 3 个流程，直至 score 表中所有数据行被执行判断，最后保留所有有效数据；