一、 SELECT语句

SELECT语句是最常用的SQL语句，它能帮助我们从一个或多个表中查询信息。
SELECT 语句用于从数据库中选取数据，并将结果存储在一个临时结果表中，这个表称为结果集，结果集实际上也是一种包含行与列的表，只不过是未持久化的，即临时表。

1、使用SELECT COLUMN 查询单个列

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

SELECT `column_name`FROM `table_name`

我们在命名字段的时候，字段名可能会与 SQL 关键字冲突，这时候要用反引号将列名和表名包含一下，避免关键字冲突。在绝大部分时候，不加反引号并不会导致程序出错。

2、使用 SELECT COLUMN, COLUMN 查询多个列

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

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

3、使用 SELECT * 查询所有列

SELECT 语句可以直接检索表中所有信息，即检索所有的列。这可以通过在列名的位置使用星号（ * ）通配符来实现，输出的列的顺序一般是列在表定义中出现的物理顺序。

通配符是一类键盘字符，* (星号) 就是较为常用的通配符之一，可以使用 * 代替零个、单个或多个字符。
使用 * 通配符最大的优点就是，当不明确需要检索的列名时，可以通过检索所有列名来确定。

基础语法：

SELECT * FROM `table_name`;

4、使用 SELECT DISTINCT 查询不同行

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

SELECT DISTINCT `column_name`FROM `table_name`;

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

在大多数情况下，我们只希望留下感兴趣的行而过滤掉不感兴趣的行，这时我们可以使用 WHERE 子句来帮助我们。SELECT WHERE 语句是筛选查询很重要的操作，WHERE 关键字后面加上条件可以过滤掉我们不需要信息，对查询效率有着很大的提高。
在使用 SELECT WHERE 语句检索表数据时，需要给出检索的表名 (table_name)、检索的列名 (column_name) 和操作符 (operator) 。
基础语法：

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

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

二、INSERT语句

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

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

基础语法：

INSERT INTO `table_name`VALUES (value1,value2,……)

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

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

INSERT INTO `table_name`('column1,column2,……)VALUES(value1,value2,……);

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

由此我们不难发现，如果我们需要为表中的每一列插入数据，那么第一种形式的写法会更为方便，如果我们只想在指定列中插入数据，那么第二种形式的写法会更为方便。

三、UPDATE语句

可以用来更新表中已存在的记录基础语法：

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

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

四、DELETE语句

DELETE 关键字对原有的数据进行删除基础语法：

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

注意 SQL DELETE 语句中的 WHERE 子句。WHERE 子句规定哪条记录或者哪些记录需要删除。如果省略了 WHERE 子句，所有的记录都将被删除！

可以在不删除表的情况下，删除表中所有的行，这意味着表结构、属性、索引将保持不变。

五、运算符

（一）比较运算符

比较运算符用于比较运算，判断逻辑是否成立。
基本语法：

A operator B

常用的比较运算符有 =（等于）、!=（不等于）、 <>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于），其中 != 和 <> 在特殊情况下用法是不同的，这里暂时不提。
比较运算符常常与 WHERE 在一起使用。WHERE 用于逻辑判断，WHERE 后面写判断的条件，满足条件的语句会被筛选出来。
基本语法：

WHERE A operator B

（二）、逻辑运算符

1、使用AND连接多条件

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

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

2、使用 OR 连接多个条件

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

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

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

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

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

（三）、特殊条件

1、使用 IN 查询多条件

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

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

2、使用 NOT IN 排除

'IN’ 也可以与逻辑运算符 'NOT’ 并用，组成 'NOT IN’，表示不在集合中的所有结果。

SELECT *FROM table_nameWHERE column_name NOT IN value;

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

BETWEEN AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。例如 MySQL 中，BETWEEN 选取介于两个值之间且包括两个测试值的字段，即BETWEEN 200 AND 250 选取结果会包括 200 和 250。
基本语法：

SELECT *FROM table_nameWHERE column_name BETWEEN value AND value;

4、使用 IS NULL 查询空数据

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

注意：无法比较 NULL 和 0；它们是不等价的。
无法使用比较运算符来测试 NULL 值，比如 =、!= 或 <>。
我们必须使用 IS NULL 和 IS NOT NULL操作符。

基本语法：

SELECT *FROM table_nameWHERE column_name IS NULL;

5、使用LIKE模糊查询

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

SELECT *FROM table_nameWHERE column_name LIKE value;

SQL 中的通配符有：

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

（四）、ORDER BY 与LIMIT

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

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

ASC ：按升序排列，ORDER BY 默认按照升序对记录进行排序，因此升序的关键字 ASC 可以省去不写;
DESC：按降序排列，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。
基本语法：

SELECT column_name,column_nameFROM table_nameORDER BY column_name,column_name ASC|DESC;

2、使用LIMIT限制输出行数

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

offset ：是返回集的初始标注，起始点是0，不是1
count ：制定返回的数量

基本语法：

SELECT column_name,column_nameFROM table_nameLIMIT offset,count;

LIMIT 关键字的位置，需要放在 ORDER BY 关键字的后面，否则会报错。

六、算术函数

（一）、使用 AVG() 函数求数值列的平均值

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

SELECT AVG(column_name)FROM table_name;

别名补充：

别名是一个字段或值的替换名，由关键字 AS 赋予。别名还有其他用途，常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的表列名含糊或者容易误解时扩充它等。
别名常与函数联用，给使用函数之后的新计算列一个名字，方便我们查看和使用。

（二）、使用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() 返回值数据类型会被变换为一个 BIGINT

（六）、使用NULL函数判断空值

NULL 并不是值，它表示数值未知或者不确定。因此，NULL 无法和 0 或空格字符串 “” 进行比较，甚至 NULL 与 NULL 之间也无法比较。默认地，表的列可以存放 NULL 。

1、ISNULL函数

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

SELECT ISNULL(column_name)FROM table_name;

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

2、IFNULL函数

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) 。

1、COUNT（column_name)

COUNT(column_name) 函数会对指定列具有的行数进行计数，但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况，便于统计数据的缺失值。假如出现某一列的数据全为 NULL 值的情况，使用COUNT( column_name ) 函数对该列进行计数，会返回 0。

SELECT COUNT(column_name)FROM table_name;

2、COUNT(*)

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

SELECT (*)FROM table_name;

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

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

3、COUNT(DISTINCT column_name)

可以去除重复项

七、时间函数

1. 使用 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

2、使用DATE（）和TIME ()提取日期和时间

将时间中的代表日期的元素和代表时间的元素从时间中提取出来。

3、使用EXTRACT 函数提取指定的时间信息

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

SELECT EXTRACT(unit FROM date)FROM table_name;

table_name 是表格名
date 参数是合法的日期表达式。
unit 参数是需要返回的时间部分，如 YEAR 、MONTH 、 DAY 、 HOUR 、MINUTE 、SECOND 等。
在一般情况下，EXTRACT(unit FROM date) 与 unit() 的结果相同。

4、使用DATE_FORMAT格式化输出日期

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

SELECT DATE_FORMAT(date,format);

date 一个有效日期，format 是 date/time 的输出格式。
案例：

SELECT DATE_FORMAT(`created_at`, '%Y %m') AS `DATE_FORMAT`FROM `courses`;

其中 %m 表示月份，%d 表示日期，%Y 表示年份，%w 表示星期

5、使用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 等）

6、使用DATE_SUB减少时间

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

SELECT DATE_SUB(INTERVAL expr type)FROM table_name;

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

DATEDIFF()

DATEDIFF() 常用的日期差，在 MySQL 中默认只能计算天数差。
DATEDIFF() 差值计算规则：时间 1 - 时间 2
date_diff 为返回结果列名称

SELECT DATEDIFF(时间1，时间2) AS date_diff 
FROM table_name;

八、约束

在 SQL 中，约束是规定表中的数据规则。若存在违反约束的行为，行为就会阻止。它能帮助管理员更好地管理数据库，并且确保数据库中数据的正确性和有效性。例如在后台的数据库设计中对要输入的数据进行核查或判断，再决定是否写入数据库，这都是约束的应用。

（一）、非空约束NOT NULL

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

CREATE TABLE `Persons`(`Id` int NOT NULL,`name` varchar(255) NOT NULL);

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

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

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

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

如果指定’ '（两个单引号，其间没有字符），这
在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无
值。NULL 值用关键字 NULL 而不是空串指定。

（二）、唯一约束UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录
UNIQUE 和主键约束均为列或列集合提供了唯一性的保证
主键约束会自动定义一个 UNIQUE 约束，或者说主键约束是一种特殊的 UNIQUE 约束。但是二者有明显的区别：每个表可以有多个 UNIQUE 约束，但只能有一个主键约束。

1、CREATE TABLE 时的 UNIQUE 约束

下面的 SQL 在 Persons 表创建时在 P_Id 列上创建 UNIQUE 约束：
MySQL：最后单独添加

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

SQL Server / Oracle / MS Access：定义属性时添加

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

命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:

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

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE `Persons`(`P_Id` int NOT NULL,`LastName` varchar(255) NOT NULL,`FirstName` varchar(255),`Address` varchar(255),`City` varchar(255),CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`))

2、ALTER TABLE 时的 UNIQUE 约束

当表已被创建时，在 P_Id 列创建 UNIQUE 约束:
MySQL / SQL Server / Oracle / MS Access

ALTER TABLE PersonsADD UNIQUE(P_Id);

当表已被创建时，需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

3、撤销UNIQUE 约束

MySQL:

ALTER TABLE PersonsDROP INDEX uc_PersonID;

SQL Server / Oracle / MS Access:

ALTER TABLE PersonDROP CONSTRAINT uc_PersonID;

（三）、主键约束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 只是为了保证域/域组的唯一性。

1、CREATE TABLE 时添加PRIMARY KEY

MySQL：

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`));

SQL Server / Oracle / MS Access：

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

如需命名并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

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

2、ALTER TABLE 时添加主键约束

与UNIQUE约束类似，不再叙述。

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

（四）、外键约束FOREIGN KEY

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 。
让我们看了例子，如果一个字段 X 在一张表（表 1 ）中是关键字，而在另一张表（表 2 ）中不是关键字，则称字段 X 为表 2 的外键。
外键最根本的作用：保证数据的完整性和一致性。
例如：
现在有两张表——学生表和院系表，这里的院系就是学生表的外键，外键表是学生表，主键表是院系表。假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。所以删除外键表中的数据并不影响主键表。
外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。

1、CREATE TABLE 时的 SQL FOREIGN KEY 约束

在 “Orders” 表创建时在 “P_Id” 列上创建 FOREIGN KEY 约束：

REFERENCES 表示引用一个表

MySQL：FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

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))

SQL Server / Oracle / MS Access：

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

2、ALTER TABLE 时的 SQL FOREIGN KEY 约束

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

MySQL / SQL Server / Oracle / MS Access：

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

其他操作类似PRIMARY KEY

（五）、检查约束CHECK

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

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

定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值。
CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性。
例如：
假如我们想创建一个简单的课程表 courses ，表中每一条数据记录着课程编号 id、课程名称 name 、学生总数 student_count 、创建课程时间created_at 以及授课教师编号 teacher_id。其中课程编号 id 为主键。根据基本常识，学生总数 student_count 一定是非负值，在这里我们设置它必须为正整数，可以使用 CHECK 约束。

1、CREATE TABLE时添加 CHECK约束

在创建课程表 courses 时，给学生总数 student_count 字段加上一个大于 0 的约束。
MySQL：CHECK (student_count> 0)

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

SQL Server / Oracle / MS Access：

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

为多个列添加 CHECK 约束
MySQL / SQL Server / Oracle / MS Access：
如果想为一个表中多个字段添加约束，直接在 CHECK 关键字后的括号内添加，两个约束间使用 AND 关键字连接。

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 约束命名
MySQL / SQL Server / Oracle / MS Access：

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

（六）、默认约束DEFAULT

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

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。
例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

1、CREATE TABLE 时的 DEFAULT 约束

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

<字段名> <数据类型> DEFAULT <默认值>
MYSQL / SQL Server / Oracle / MS Access：

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())

2、ALTER TABLE 时的 DEFAULT 约束

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

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

SQL Server / MS Access：

ALTER TABLE `Persons`ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`

3、撤销 DEFAULT 约束

如需撤销 Persons表的 DEFAULT 约束 :
MySQL：

ALTER TABLE `Persons`ALTER `City` DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE `Persons`ALTER COLUMN `City` DROP DEFAULT

九、联结

关系表就是一类数据一个表，各表通过某些关系互相关联。在设置关联条件时，为避免不同表被引用的列名相同，我们需要使用**完全限定列名（用一个点分隔表名和列名）**，否则会返回错误。基本语法：

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

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`;

INNER 可以省略，联结条件需使用特定的 ON 子句给出。

例如：

SELECT `c`.`id`, `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`FROM `courses` `c`INNER JOIN `teachers` `t` ON `c`.`teacher_id` = `t`.`id`;

courses c 等同于 courses AS c ，给courses 表取别名为 c
teachers t 等同于 teachers AS t ，给 teachers 表取别名为 t

（二）、外连接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;

1、左外连接 LEFT JOIN

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

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

2、右外连接RIGHT JOIN

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

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

3、全外连接FULL 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 c.name AS course_name, t.age AS teacher_ageFROM courses c	LEFT JOIN teachers t ON c.teacher_id = t.idUNIONSELECT c.name AS course_name, t.age AS teacher_ageFROM courses c	RIGHT JOIN teachers t ON c.teacher_id = t.id;

4、交叉连接CROSS JOIN

交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。即将两个表的数据一一对应，其查询结果的行数为左表中的行数乘以右表中的行数。
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 valueGROUP 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;

例如：
现需要结合教师表与课程表，统计不同教师所开课程的学生总数，对于没有任课的老师，学生总数计为 0 ，最后查询学生总数少于 3000 的教师姓名及学生总数（别名为 student_count ），结果按照学生总数升序排列，如果学生总数相同，则按照教师姓名升序排列。
使用 SQL 中 HAVING 子句查询的方式如下：

SELECT `T`.`name` AS `name`, IFNULL(SUM(`C`.`student_count`),0) AS `student_count`FROM `courses` `C` RIGHT JOIN `teachers` `T`ON `C`.`teacher_id` = `T`.`id`GROUP BY `T`.`id`HAVING `student_count` < 3000ORDER BY `student_count`, `name`;

十一、简单的子查询

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

1、SELECT 语句中的子查询

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

2、INSERT 语句中的子查询

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

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

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

3、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 语句所要更改的表不能是同一张表！

4、DELETE 语句中的子查询

对于 DELETE 语句，首先通过 SELECT 语句查询需要删除的数据，再使用 DELETE 语句对数据进行删除。当通过 DELETE 语句使用子查询时，可以完成复杂的数据删除控制。

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

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

十二、子查询进阶

（一）、内联视图子查询

内联视图子查询，是将子查询插入到表名 table_name 的位置。
内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。
例如：
现需要查询国籍为美国（USA），且年龄最大的教师，请使用内联视图子查询实现。将从教师表中查询到的美国教师作为内联表，再使用 WHERE 子句进行查询操作。

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

（二）、IN操作符的多行子查询

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

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

（三）、ANY操作符的多行子查询

操作符 ANY 属于逻辑运算符的一种，与 IN 运算符不同，ANY 必须和其它的比较运算符共同使用，其表示查询结果中的任意一个。
在子查询中使用 ANY ，表示与子查询返回的任何值比较为真，则返回真。
语法：

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

例如：
现需要查询学生上课人数超过 “Eastern Heretic” 的任意一门课的学生人数的课程信息，请使用 ANY 操作符实现多行子查询。
第一层的父查询为在课程表 courses 中查询满足条件的全部课程信息，这个条件由子查询来完成，即为，查询学生上课人数超过 ”Eastern Heretic“ 的任意一门课的学生人数。这一部分的子查询中需要结合 ANY 操作符实现。之后，再将子查询进行拆分，形成第二层的嵌套子查询。
第二层的父查询为在课程表 courses 中根据教师 id 查询学生上课人数，其子查询为在教师表 teachers 中查找教师名 name 为 “Eastern Heretic” 的教师 id。

由于我们最终得到的课程信息中肯定不包含 “Eastern Heretic” 的课程，所以我们要在 WHERE 条件中再设置一项：不为 “Eastern Heretic” 所开的课程。

结合以上，使用 SQL 中子查询的方式如下：

SELECT *FROM `courses`WHERE `student_count` > ANY (
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Eastern Heretic'
		)
	)
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Eastern Heretic'
	);

（四）、ALL操作符的多行子查询

与 ANY 一样，操作符 ALL 也属于逻辑运算符的一种，且都须与其它的比较运算符共同使用，其表示查询结果中的所有。
在子查询中使用 ALL ，表示与子查询返回的所有值比较为真，则返回真。
语法：

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

（五）、多列子查询

对于多列子查询：

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

例如：
现需要找到每个国家年龄最大的教师，请编写 SQL 语句实现多列子查询
使用 SQL 中子查询的方式如下：

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

（六）、HAVING子句中的子查询

当子查询出现在 HAVING 子句中时，像 HAVING 子句中的任何表达式一样，表示要进行分组过滤，它被用作行组选择的一部分，一般返回单行单列的数据。
例如：
现需要计算每位教师所开课程的平均学生人数与全部课程的平均学生人数，比较其大小，最后返回超过全部课程平均学生人数的教师姓名，请编写相应的 SQL 语句实现。
本题需要使用 HAVING 语句根据教师 id 进行分组，实现“计算每位教师所开课程的平均人数”，并使用子查询实现其与“全部课程的平均人数”的比较。
使用 SQL 中 HAVING 子查询的方式如下：

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`
	));

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。