sql语法速查

SQl语法

简单的select语句

使用 SELECT COLUMN 查询单个列

在使用 SELECT 语句检索表数据时，至少需要给出两条信息——想检索的列名（column_name）和被检索内容的表名（table_name）。

SELECT `column_name`
FROM `table_name`;

使用 SELECT COLUMN, COLUMN 查询多个列

当我们想要从一个表中查询多个列时，使用的 SELECT 语句与查询一个列时使用的语句相似，但是需要在 SELECT 关键字后给出多个列名，并且列名之间必须以逗号分隔。

SELECT `column_name_1`, `column_name_2`  
FROM `table_name`;

使用 SELECT * 查询所有列

SELECT 语句可以直接检索表中所有信息，即检索所有的列。

SELECT * FROM `table_name`;

使用 SELECT DISTINCT 查询不同行

SELECT 语句会返回所有匹配的行,会遇到数据相同的情况。如果我们只想知道有哪些不同的值，即希望查询的值都是唯一不重复的.这时候我们就需要用到 DISTINCT 关键字。

SELECT DISTINCT `column_name`
FROM `table_name`

使用 SELECT WHERE 对行进行筛选过滤

---

SELECT WHERE 语句是筛选查询很重要的操作，WHERE 关键字后面加上条件可以过滤掉我们不需要的信息，对查询效率有着很大的提高。在使用 SELECT WHERE 语句检索表数据时，需要给出检索的表名 (table_name)、检索的列名 (column_name) 和操作符 (operator) 。

SELECT `column_name1`,`column_name2`…
FROM `table_name`
WHERE `column_name` operator `value`;

其中：

• column_name 对应指定列的名称，或者是多列，用逗号（ , ）分隔开

• table_name 对应查询表的名称

• operator 为操作符，常用的有等于 = 、小于 < 、大于 > 、不等于<> 或 !=。

简单的insert语句

使用 INSERT INTO 在不指定列的情况下插入数据

INSERT INTO 语句用于向表中插入新记录，这边介绍两种编写形式，第一种形式无需指定列名，第二种形式需要指定列名。

INSERT INTO `table_name`
VALUES (value1, value2, value3,...);

value1, value2 …… 为对应插入数据表中的值，每个值的属性需要与对应表中的列名属性相匹配，而且需要把插入的信息填写完整，否则会报错。

使用 INSERT INTO 在指定的列中插入数据

INSERT INTO `table_name`
(`column1`, `column2`, `column3`,...)
VALUES (value1, value2, value3,...);

其中 column1, column2 ... 为指定的列名，value1, value2 …… 为对应插入数据表中的值，每个值的属性需要与对应的列名属性相匹配。

简单的update语句

使用 UPDATE 更新数据

---

在我们平时的使用中 UPDATE 语句，也是一种较常用的 SQL 语句，它可以用来更新表中已存在的记录。

UPDATE `table_name`
SET `column1`=value1,`column2`=value2,...
WHERE `some_column`=some_value;

注意

请注意 UPDATE 语句中的 WHERE 子句！WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

简单的delete语句

使用 DELETE 删除数据

---

需要用到 DELETE 关键字对原有的数据进行删除

DELETE FROM `table_name`
WHERE `some_column` = `some_value`;

• table_name 代表表名称

• some_column 代表列名称，如 id

• some_value 可以为任意值。some_column 和 some_value 构成 WHERE 子句中的搜索条件。

比较运算符

比较运算符

---

比较运算符用于比较运算，判断逻辑是否成立。

A operator B

其中 operator 是比较运算符，用于对 A 和 B 进行比较运算。

常用的比较运算符有 =（等于） 、!=（不等于）、 <>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于），其中 != 和 <> 在特殊情况下用法是不同的，这里暂时不提。

比较运算符常常与 WHERE 在一起使用。WHERE 用于逻辑判断，WHERE 后面写判断的条件，满足条件的语句会被筛选出来。

WHERE A operator B

逻辑运算符

使用 AND 连接多条件

---

使用 SQL 中的逻辑运算符 AND 可以将 WHERE 子句中两个或两个以上的条件结合起来，其结果是满足 AND 连接的所有条件的数据。

SELECT `column_name` 
FROM `table_name` 
WHERE condition1 AND condition2;

其中： condition 为设置的条件，最后返回的结果应为满足 condition1 和 condition2 的数据。

使用 OR 连接多个条件

---

使用 SQL 中的逻辑运算符 OR 与 AND 关键字不同，OR 关键字，只要记录满足任意一个条件，就会被查询出来。

语法

SELECT `column_name` 
FROM `table_name` 
WHERE condition1 or condition2;

其中： condition1 和 condition2 为设置的条件，最后返回的结果应满足 condition1 或 condition2 的数据。

使用 NOT 过滤不满足条件的数据

---

使用 SQL 中的逻辑运算符 NOT 可以过滤掉 WHERE 子句中不满足条件的结果集。

SELECT `column_name` 
FROM `table_name` 
WHERE NOT `condition`;

其中： condition 为设置的条件，最后返回的结果应不满足 condition 。

特殊条件

使用 IN 查询多条件

---

当我们需要查询单个表条件过多时，就会用多个 'OR' 连接或者嵌套，这会比较麻烦，现在我们有 'IN' 能更方便的解决这一问题。

SELECT *
FROM `table_name`
WHERE `column_name` IN `value`;

使用 NOT IN 排除

---

前面我们已经学习了逻辑运算符，这里的 'IN' 也可以与逻辑运算符 'NOT' 并用，组成 'NOT IN'，表示不在集合中的所有结果。

SELECT *
FROM `table_name`
WHERE `column_name` NOT IN value;

使用 BETWEEN AND 查询两值间的数据范围

---

BETWEEN AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。

在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。

在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

因此，请检查您的数据库是如何处理 BETWEEN 操作符！

SELECT *
FROM `table_name`
WHERE `column_name` BETWEEN `value` AND `value`;

使用 IS NULL 查询空数据

---

NULL 值代表遗漏的未知数据。默认的，表的列可以存放 NULL 值。 如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 用作未知的或不适用的值的占位符。

SELECT *
FROM `table_name`
WHERE `column_name` IS NULL;

使用 LIKE 模糊查询

---

前面我们学习 BETWEEN AND 和比较运算符时，和文本操作类似，比如前面课程名称首字母的范围，这种方案虽然可行但是不稳定，不是直接对文本的操作。在这里我们学习使用 LIKE 更准确规范得解决文本比较问题。

LIKE 比较类似我们平时用到的搜索。

SELECT *
FROM `table_name`
WHERE `column_name` LIKE  `value`;

其中 'D%' 表示以 D 开头的所有单词，% 表示为通配符，可以替代 0 个或多个字符

对于SQL 中的通配符有以下类型：

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
或 [!charlist]	不在字符列中的任何单一字符

orderby和limit

使用 ORDER BY 对数据进行排序

1. 使用 ORDER BY 对数据进行排序

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序，其具有 ASC（升序）和 DESC（降序）两个关键字，且默认按照升序排列。

• ASC ：按升序排列，ORDER BY 默认按照升序对记录进行排序，因此升序的关键字 ASC 可以省去不写。

• DESC：按降序排列，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

基本语法

SELECT `column_name`, `column_name`
FROM `table_name`
ORDER BY `column_name`, `column_name` ASC|DESC;

使用 LIMIT 限制输出行数

LIMIT 子句用于 SELECT 中，对输出结果集的行数进行约束，LIMIT 接收2个参数 offset 和 count，两个参数都是整型数字，但通常只用一个。

SELECT `column_name`, `column_name`
FROM `table_name`
LIMIT `offset` , `count`;

• offset ：是返回集的初始标注，起始点是0，不是1哦

• count ：制定返回的数量

算数函数

使用 AVG() 函数求数值列的平均值

---

平均函数 AVG() 是平均数 AVERAGE 的缩写，它用于求数值列的平均值。它可以用来返回所有列的平均值，也可以用来返回特定列和行的平均值。 具体的计算过程为：其通过对表中行数计数并计算特定数值列的列值之和，求得该列的平均值。

但是当参数 column_name 列中的数据均为空时，结果会返回 NULL。

SELECT AVG(`column_name`) 
FROM `table_name`;

使用 MAX() 函数返回指定列中的最大值

---

最大值函数 MAX() 用于返回指定列中的最大值。它只有一个参数 column_name ，表示指定的列名。但是当参数 column_name 列中的数据均为空时，结果会返回 NULL。

SELECT MAX(`column_name`) 
FROM `table_name`;

使用 MIN() 函数返回指定列中的最小值

---

MIN() 函数的功能与 MAX() 正好相反，它用于返回指定列中的最小值。但与 MAX() 相同的是，它也只有一个参数 column_name ，表示指定的列名，且当参数 column_name 列中的数据均为空时，结果会返回 NULL。

SELECT MIN(`column_name`) 
FROM `table_name`;

使用 SUM() 函数统计数值列的总数

---

SUM() 函数用于统计数值列的总数并返回其值。它只有一个参数 column_name ，表示指定的列名，但是当参数 column_name 列中的数据均为空时，结果会返回 NULL。

SELECT SUM(`column_name`) 
FROM `table_name`;

使用 ROUND() 函数将数值四舍五入

---

ROUND() 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(`column_name`, `decimals`) 
FROM `table_name`;

• column_name 为要舍入的字段

• decimals 规定要返回的小数位数

• ROUND() 函数始终返回一个值。当 decimals 为正数时，column_name 四舍五入为 decimals 所指定的小数位数。当 decimals 为负数时，column_name 则按 decimals 所指定的在小数点的左边四舍五入。

• 特别的，如果 length 是负数且大于小数点前的数字个数，ROUND() 函数将返回 0

ROUND( X )：返回参数 X 四舍五入后的一个整数。

ROUND(X, D)： 返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。

❗ 注意：ROUND() 返回值数据类型会被变换为一个 BIGINT 。

使用 NULL() 函数判断空值

在本小节中，我们主要介绍 SQL 中的 ISNULL() 函数和 IFNULL() 函数，他们的用途都是判断字段是否为空，但是具体的用法有些差别。

ISNULL() 函数用于判断字段是否为 NULL，它只有一个参数 column_name 为列名，根据column_name 列中的字段是否为 NULL 值返回 0 或 1。

SELECT ISNULL(`column_name`)
FROM `table_name`;

• 如果 column_name 列中的某个字段是 NULL 则返回 1，不是则返回 0

IFNULL() 函数也用于判断字段是否为NULL，但是与 ISNULL() 不同的是它接收两个参数，第一个参数 column_name 为列名，第二个参数 value 相当于备用值。

SELECT IFNULL(`column_name`, `value`)
FROM `table_name`;

• 如果 column_name 列中的某个字段是 NULL 则返回 value 值，不是则返回对应内容。

• COALESCE(column_name, value) 函数也用于判断字段是否为NULL，其用法和 IFNULL() 相同。

使用 COUNT() 函数计数

---

COUNT() 函数用于计数，可利用其确定表中行的数目或者符合特定条件的行的数目。当COUNT() 中的参数不同时，其的用途也是有明显的不同的，主要可分为以下三种情况：COUNT(column_name) 、COUNT( * ) 和 COUNT(DISTINCT column_name) 。

COUNT(column_name) 函数会对指定列具有的行数进行计数，但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况，便于统计数据的缺失值。

假如出现某一列的数据全为 NULL 值的情况， 使用COUNT( column_name ) 函数对该列进行计数，会返回 0。

SELECT COUNT(`column_name`) 
FROM `table_name`;

COUNT(*) 函数会对表中行的数目进行计数，包括值为 NULL 所在行和重复项所在行。该函数主要用于查看表中的记录数。

SELECT COUNT(*) 
FROM `table_name`;

❗ 注意： COUNT(column_name) 与 COUNT(*) 的区别

• COUNT(column_name) 中，如果 column_name 字段中的值为 NULL，则计数不会增加，而如果字段值为空字符串""，则字段值会加 1

• COUNT() 中，除非整个记录全为 NULL，则计数不会增加，如果存在某一个记录不为 NULL，或者为空字符串""，计数值都会加 1。正常来说，表都会有主键，而主键不为空，所以 COUNT() 在有主键的表中等同于 COUNT(PRIMARY_KEY)，即查询有多少条记录。

时间函数

使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间

• NOW() 可以用来返回当前日期和时间 格式：YYYY-MM-DD hh:mm:ss

• CURDATE() 可以用来返回当前日期 格式：YYYY-MM-DD

• CURTIME() 可以用来返回当前时间 格式：hh:mm:ss

在使用 NOW() 和 CURTIME() 时，如果要精确的秒以后的时间的话，可以在（）中加数字，加多少，就表示精确到秒后多少位

比如 NOW(3) 就是精确到毫秒，表示为： 2021-03-31 15:27:20.645

使用 DATE()、TIME() 函数提取日期和时间

SELECT DATE('2021-03-25 16:16:30') AS `date`,TIME('2021-03-25 16:16:30')  AS `time`;
+------------+----------+
| date       | time     |
+------------+----------+
| 2021-03-25 | 16:16:30 |
+------------+----------+
1 row in set

EXTRACT() 函数提取指定的时间信息

---

前面我们已经学习了 DATE 函数和 TIME 函数，明白 DATE 返回日期， TIME 返回时间，如果我只想知道年份的信息或者小时的信息，那么该怎么解决呢？这时，我们就可以使用 EXTRACT() 函数来解决问题。

EXTRACT() 函数用于返回日期/时间的单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。

语法

SELECT EXTRACT(unit FROM date)
FROM `table`

table 是表格名

date 参数是合法的日期表达式。

unit 参数是需要返回的时间部分，如 YEAR 、MONTH 、 DAY 、 HOUR 、MINUTE 、SECOND 等。

在一般情况下，EXTRACT(unit FROM date) 与 unit() 的结果相同。

DATE_FORMAT() 用法

我们在 SQL 中使用 DATE_FORMAT() 方法来格式化输出 date/time。 需要注意的是 DATE_FORMAT() 函数返回的是字符串格式。

语法

SELECT DATE_FORMAT(date,format);

date 一个有效日期。

format 是 date/time 的输出格式。

使用 DATE_ADD() 增加时间

DATE_ADD() 函数是常用的时间函数之一，用于向日期添加指定的时间间隔。

SELECT DATE_ADD(date, INTERVAL expr type)
FROM table_name

• date 指代希望被操作的有效日期，为起始日期

• expr 是希望添加的时间间隔的数值（expr 是一个字符串，对于负值的间隔，可以以 ”-“ 开头）

• type 是具体的数据类型，表示加上时间间隔的单位（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

使用 DATE_SUB() 减少时间

上一节了解过能够令时间加上几天、几小时的函数 DATE_ADD() ，对应的，当我们需要修改数据表中的时间，让时间减少几天、几年时，又该如何做呢？

我们同样可以使用相关的时间函数对时间进行修改。

DATE_SUB() 函数

DATE_SUB() 函数是常用的时间函数之一，用于从日期减去指定的时间间隔。它与 DATE_ADD() 函数具有相似的用法。

SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name

• date 指代希望被操作的有效日期

• expr 是希望添加的时间间隔

• type 是具体的数据类型（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差

DATEDIFF() 常用的日期差，在 MySQL 中默认只能计算天数差。

DATEDIFF() 用法：DATEDIFF（时间1，时间2）

SELECT DATEDIFF(时间1,时间2) AS date_diff FROM courses;

DATEDIFF() 差值计算规则：时间 1 - 时间 2

date_diff 为返回结果列名称

约束

非空约束 NOT NULL

NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值，这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。

下面的 SQL 强制 ID 列、 LastName 列以及 FirstName 列不接受 NULL 值：

CREATE TABLE `Persons` (
    `ID` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255) NOT NULL,
    `Age` int
);

在一个已创建的表的 Age 字段中添加 NOT NULL 约束如下所示：

ALTER TABLE `Persons`
MODIFY `Age` int NOT NULL;

在一个已创建的表的 Age 字段中删除 NOT NULL 约束如下所示：

ALTER TABLE `Persons`
MODIFY `Age` int NULL;

主键约束 PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录 ，简单的说，PRIMARY KEY = UNIQUE + NOT NULL ,从技术的角度来看，PRIMARY KEY 和 UNIQUE 有很多相似之处。但还是有以下区别：

• NOT NULL UNIQUE 可以将表的一列或多列定义为唯一性属性，而 PRIMARY KEY 设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复。

• PRIMARY KEY 可以与外键配合，从而形成主从表的关系，而 NOT NULL UNIQUE 则做不到这一点

如：

表一：用户 id (主键)，用户名

表二: 银行卡号 id (主键)，用户 id (外键)

则表一为主表，表二为从表。

• 更大的区别在逻辑设计上。 PRIMARY KEY 一般在逻辑设计中用作记录标识，这也是设置 PRIMARY KEY 的本来用意，而 UNIQUE 只是为了保证域/域组的唯一性。

CREATE TABLE 时 添加 PRIMARY KEY 约束

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    PRIMARY KEY (`P_Id`)
);

ALTER TABLE 时添加主键约束

ALTER TABLE `Persons`
ADD PRIMARY KEY (`P_Id`)

如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

撤销 PRIMARY KEY

ALTER TABLE `Persons`
DROP PRIMARY KEY

外键约束 FOREIGN KEY

什么是外键

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 。

让我们看了例子，如果一个字段 X 在一张表（表 1 ）中是关键字，而在另一张表（表 2 ）中不是关键字，则称字段 X 为表 2 的外键。

外键的作用

外键最根本的作用：保证数据的完整性和一致性。接下来通过一个例子来深入理解一下。

现在有两张表——学生表和院系表，这里的院系就是学生表的外键，外键表是学生表，主键表是院系表。 假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。 当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。 所以删除外键表中的数据并不影响主键表。

外键约束

外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。

CREATE TABLE 时的 SQL FOREIGN KEY 约束

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

NOT NULL 表示该字段不为空 REFERENCES 表示 引用一个表

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

CONSTRAINT 表示约束，后面接约束名称，常用于创建约束和删除约束；

ALTER TABLE 时的 SQL FOREIGN KEY 约束

当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束:

ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:

ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销 FOREIGN KEY 约束

ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders

检查约束 CHECK

CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。 满足条件的值将会插入表中，否则将放弃插入操作。 可以为同一列指定多个 CHECK 约束。

CHECK 约束既可以用于某一列也可以用于某张表：

• 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

• 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。 当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值。 CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性。

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

DROP TABLE IF EXISTS lesson;

---

创建表结构时可以使用 CHECK 约束，也可以给已创建的表增加 CHECK 约束。

🔔我们举一个例子感受一下它的用法：

假如我们想创建一个简单的课程表 ，表中每一条数据记录着课程编号 、课程名称 、学生总数 、创建课程时间 以及授课教师编号 。 其中课程编号 为主键。courses``id``name``student_count``created_at``teacher_id``id

根据基本常识，学生总数 一定是非负值，在这里我们设置它必须为正整数，可以使用 CHECK 约束。student_count

创建表（CREATE TABLE）时添加 CHECK约束

在创建课程表 时，给学生总数 字段加上一个大于 0 的约束。

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

为多个列添加 CHECK 约束

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

如果想为一个表中多个字段添加约束，直接在 CHECK 关键字后的括号内添加，两个约束间使用 AND 关键字连接。

为 CHECK 约束命名

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;
)

核心部分的基本语法为：

[CONSTRAINT <constraint name>] CHECK (<condition>)

• 约束关键字 CONSTRAINT：表示其后面接的内容为约束

• constraint name：为约束名称

• 关键字 CHECK：表示检查约束

• condition：被约束内容

表已存在时添加 CHECK 约束

课程表 已存在的情况下为学生总数 字段添加一个大于 0 的 CHECK 约束

ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);

• ALTER TABLE 关键字：表示修改表的定义

• ADD 关键字：表示增加

撤销 CHECK 约束

如果想要撤销 CHECK 约束，可以使用 DROP 关键字。

ALTER TABLE `courses` 
DROP CHECK chk_courses

默认约束 DEFAULT

DEFAULT 约束

默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。 MySQL 默认值约束用来指定某列的默认值。

DEFAULT 约束用法

• DEFAULT 约束用于向列中插入默认值。

• 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

• 例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

CREATE TABLE 时的 DEFAULT 约束

• 使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下所示：

  <字段名> <数据类型> DEFAULT <默认值>

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数, DEFAULT 约束也可以用于插入系统值：

CREATE TABLE `Orders`
(
    `O_Id` int NOT NULL,
    `OrderNo` int NOT NULL,
    `P_Id` int,
    `OrderDate` date DEFAULT GETDATE()
)

ALTER TABLE 时的 DEFAULT 约束

如果表已被创建时，想要在 列创建 DEFAULT 约束，请使用下面的 SQL：City

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT

多表连接

联结

我们可以举一个生活中的例子来理解“联结”：

在大学每一个新学期即将开始的时候，我们都会遇到一个大难题，那就是选课。

假如所有课程的信息和所有教师的信息都存放在同一张表中，我们会发现这显得很繁琐且不便于信息的更改。 因为同一个教师开设的不同课程对应的教师信息都是相同的，当教师信息改变时，需要修改多条课程。

因此，为了同学们在选课时操作更便利，我们根据内容将所有数据分解为了两个表，即教师表和课程表。 我们可以将他们称为关系表。 关系表就是一类数据一个表，各表通过某些关系互相关联。

🤔 那么问题就来了，数据存储在两个表中，如果我们想用一条 SELECT 语句同时查询课程信息和对应的教师信息该怎么办呢？

答案就是——使用联结。

简单地说，联结是一种机制，用于在一条 SELECT 语句中关联多个表，返回一组输出。

这个时候就要说一下联结中的两大主角——主键（PRIMARY KEY）*和*外键（FOREIGN KEY）。

以我们使用的教师表和课程表为例：

表1：courses (课程表)

列名	类型	注释
编号	int unsigned	主键
名字	瓦尔查尔	课程名称
student_count	int	学生总数
created_at	日期	创建课程时间
teacher_id	int	讲师 id

表2：teachers (教师表)

列名	类型	注释
编号	int	主键
名字	瓦尔查尔	讲师姓名
电子邮件	瓦尔查尔	讲师邮箱
年龄	int	讲师年龄
国家	瓦尔查尔	讲师国籍

教师表 中包含所有教师的信息，每个教师具有唯一的标识，这个标识被称为主键（PRIMARY KEY），可以为 id 或其他唯一值。 我们这里选择教师编号（）为教师表的主键。teachers``id

而在课程表 中，除了教师编号 （即教师表的主键）外不存储任何教师的信息。 则教师编号为课程表的外键（FOREIGN KEY）。courses``teacher_id

教师表的主键又叫作课程表的外键，因此这两个表通过教师编号这一列关联了。

🤔 那我们又该如何创建联结呢？ 规定要联结的所有表以及它们如何关联就可以了。

在设置关联条件时，为避免不同表被引用的列名相同，我们需要使用完全限定列名（用一个点分隔表名和列名），否则会返回错误。

`table1`.`common_field` = `table2`.`common_field`

JOIN 连接子句

SQL JOIN 连接子句用于将数据库中两个或者两个以上表中的记录组合起来。 其类型主要分为 INNER JOIN（内连接）、OUTER JOIN（外连接）、全连接（FULL JOIN）和交叉连接（CROSS JOIN），其中 OUTER JOIN 又可以细分为 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）。

因此，我们主要使用的 JOIN 连接类型如下：

• INNER JOIN：如果表中有至少一个匹配，则返回行

• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

• FULL JOIN：只要其中一个表中存在匹配，则返回行

• CROSS JOIN：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积

内连接 INNER JOIN

---

最常用也最重要的多表联结类型就是 INNER JOIN（内连接），有时候也被称作 EQUIJOIN（等值连接）。

内连接根据联结条件来组合两个表中的字段，以创建一个新的结果表。 假如我们想将表 1 和表 2 进行内连接，SQL 查询会逐个比较表 1 和表 2 中的每一条记录，来寻找满足联结条件的所有记录对。 当联结条件得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

简单的说，内连接就是取两个表的交集，返回的结果就是连接的两张表中都满足条件的部分。

基本语法有如下两种写法：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

或

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

注：INNER JOIN 中 INNER 可以省略不写

其中，语法的核心部分如下所示：

FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field

外连接 OUTER JOIN

在MySQL中，外连接查询会返回所操作的表中至少一个表的所有数据记录。 在MySQL中，数据查询通过SQL语句 “OUTER JOIN… ON” 来实现，外连接查询可以分为以下三类：

• 左外连接

• 右外连接

• 全外连接

外连接数据查询语法如下：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;

在上述语句中，参数 column_name 表示所要查询的字段名字，来源于所连接的表 table1 和 table2，关键字 OUTER JOIN 表示表进行外连接，参数 CONDITION 表示进行匹配的条件。

左外连接 LEFT JOIN

外连接查询中的左外连接就是指新关系中执行匹配条件时，以关键字 LEFT JOIN 左边的表为参考表。 左外连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录。 如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2
        ON CONDITION ;

右外连接 RIGHT JOIN

外连接查询中的右外连接是指新关系中执行匹配条件时，以关键字 RIGHT JOIN 右边的表为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2
        ON CONDITION ;

全外连接 FULL (OUTER) JOIN

---

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。 FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

注：MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起实现全连接。

UNION ：联合的意思，即把两次或多次查询结果合并起来

要求：两次查询的列数必须一致，同时，每条 SELECT 语句中的列的顺序必须相同

推荐：列的类型可以不一样，但推荐查询的每一列，相对于的类型应该一样

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准，即UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。 如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么 UNION 会将相同的行合并，最终只保留一行。 也可以这样理解，UNION 会去掉重复的行。 如果不想去掉重复的行，可以使用 UNION ALL 。 如果子句中有 order by,limit，需用括号()包起来。 推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2 ON CONDITION 
UNION
SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2 ON CONDITION ;

交叉连接 CROSS JOIN

什么是交叉连接

与内连接和外连接相比，交叉连接非常简单，因为它不存在 ON 子句，那怎么理解交叉连接呢？

交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。 即将两个表的数据一一对应，其查询结果的行数为左表中的行数乘以右表中的行数。

CROSS JOIN（交叉连接）的结果也称作笛卡尔积，我们来简单了解一下什么是笛卡尔积：

笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。

交叉连接有两种定义方式，分为隐式连接和显式连接。 两种定义方式的查询结果是相同的。

• 隐式交叉连接：不需要使用 CROSS JOIN 关键字，只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可，这种方式基本上可以被任意数据库系统支持。

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`,`table2`;

• 显式交叉连接：与隐式交叉连接的区别就是它使用 CROSS JOIN 关键字，用法与 INNER JOIN 相似。

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
CROSS JOIN `table2`;

分组查询

GROUP BY 子句

GROUP BY 函数就是 SQL 中用来实现分组的函数，其用于结合聚合函数，能根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT `column_name`, aggregate_function(`column_name`)
FROM `table_name`
WHERE `column_name` operator value
GROUP BY `column_name`;

HAVING 子句

我们在使用 WHERE 条件子句时会发现其不能与聚合函数联合使用，为解决这一点，SQL 中提供了 HAVING 子句。 在使用时， HAVING 子句经常与 GROUP BY 联合使用，HAVING 子句就是对分组统计函数进行过滤的子句。

HAVING 子句对于 GROUP BY 子句设置条件的方式其实与 WHERE 子句与 SELECT 的方式类似，语法也相近，但 WHERE 子句搜索条件是在分组操作之前，而 HAVING 则是在之后。

SELECT   `column_name`, aggregate_function(`column_name`) 
FROM     `table_name` 
WHERE    `column_name` operator value 
GROUP BY `column_name` 
HAVING   aggregate_function(`column_name`) operator value;

子查询

SELECT 语句中的子查询

当一个查询是另一个查询的条件时，称之为子查询。

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

因此，子查询本质上就是一个完整的 SELECT 语句，它可以使一个 SELECT、INSERT INTO 语句、DELETE 语句或 UPDATE 语句嵌套在另一子查询中。 子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR (
    SELECT `column_name(s)`
    FROM `table_name`
);

以及，SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

INSERT 语句中的子查询

对于 INSERT 语句中的子查询来说，首先是使用子查询的 SELECT 语句找到需要插入的数据，之后将返回的数据插入到另一个表中。 在子查询中所选择的数据可以用任何字符、日期或数字函数修改。

INSERT INTO `table_name`
	SELECT `colnum_name(s)`
	FROM `table_name`
	[ WHERE VALUE OPERATOR ]

注意：INSERT 语句中的子查询其实是将一个表中查询到的数据“复制”到另一个表中，由于主键具有唯一性，如果需要仅在单张表中使用 INSERT 子查询，只能在无主键的单张表中进行操作，否则，需要有两张表（如只一张表，则需新建一张表）。

UPDATE 语句中的子查询

对于 UPDATE 语句，首先通过 SELECT 语句查询需要更新的信息，再使用 UPDATE 语句对信息进行更新。 当通过 UPDATE 语句使用子查询时，能够实现表中单个列或多个列的数据更新。

UPDATE `table_name` 
SET `column_name` = `new_value`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )

注意：在 UPDATE 语句的子查询中，子查询 SELECT 语句所用的表和 UPDATE 语句所要更改的表不能是同一张表！

DELETE 语句中的子查询

DELETE FROM `table_name`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`  
   [WHERE] )

注意：在 DELETE 语句的子查询中，子查询 SELECT 语句所用的表和 DELETE 语句所要更改的表不能是同一张表！

内联视图子查询

内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。

现需要查询国籍为美国（USA），且年龄最大的教师，请使用内联视图子查询实现。

SELECT *
FROM (
	SELECT *
	FROM `teachers`
	WHERE `country` = 'USA'
) `T`
WHERE `age` = (
	SELECT MAX(`age`)
	FROM `teachers`
);

N 操作符的多行子查询

使用 IN 操作符进行子查询，其实是将子查询返回的集合和外层查询得到的集合进行交集运算，这个结果可以是零个值，也可以是多个值。 由此，最后可以查询出与列表中任意一个值匹配的行。

SELECT `column_name`
FROM `table_name`
WHERE `column_name` IN(
    SELECT `column_name`
    FROM `table_name`
    WHERE `column_name` = VALUE
);

ANY 操作符的多行子查询

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ANY(SELECT column_name
   FROM table_name)

ALL 操作符的多行子查询

在子查询中使用 ALL ，表示与子查询返回的所有值比较为真，则返回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ALL(SELECT column_name
   FROM table_name)

多列子查询

当是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝，>，<，>=，<=， <> 等）来进行比较；

当是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ANY，ALL 等）来进行比较。

SELECT `name`, `age`, `country` 
FROM `teachers` 
WHERE (`country`, `age`) IN ( 
        SELECT `country`, MAX(`age`) 
        FROM `teachers` 
        GROUP BY `country` 
);

HAVING 子句中的子查询

当子查询出现在 HAVING 子句中时，像 HAVING 子句中的任何表达式一样，表示要进行分组过滤，它被用作行组选择的一部分，一般返回单行单列的数据。

现需要计算每位教师所开课程的平均学生人数与全部课程的平均学生人数，比较其大小，最后返回超过全部课程平均学生人数的教师姓名，请编写相应的 SQL 语句实现。

SELECT `name`
FROM `teachers`
WHERE `id` IN (
    SELECT `teacher_id`
    FROM `courses`
    GROUP BY `teacher_id`
    HAVING AVG(`student_count`) > (
        SELECT AVG(`student_count`)
        FROM `courses`
    )
);