基本概念

SQL（Structured Query Language，发音为 sequal）是一种专门用来与数据库沟通的语言。通过 SQL，用户可以对数据库进行各种操作。

标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSISQL。

数据库

数据库（database）是一个用于保存有组织的数据的容器或数据集合。

每个数据库都有唯一的标识符。

表

表（table）是某种特定类型数据的结构化清单，用于存储某种特定类型的数据。

在同一个数据库中的每个表都有一个唯一的标识符来标识自己。因此，在同个数据库中，不能拥有两个名称相同的表，但在不同的数据库中可以使用相同的表名。

模式

模式（schema）是一种关于数据库和表的布局及特性的信息。它是用来描述数据库中表的特性（如，存储的数据类型、数据的分解方式、各部分的信息的命名），或描述整个数据库（和其中表的关系）。

行和列

列

列（column）是表中的一个字段（即，表的组成部分）。所有的表都是由一个或多个列组成的。列存储表中的某部分信息，且这部分信息必须是同一种数据类型（datatype）。

数据类型限制（或允许）该列中存储的数据的种类。它还能帮助正确地分类数据，并在优化磁盘使用方面起重要的作用。

利用特定的列，可以对数据进行分类和过滤。所以，数据分解十分重要。

数据类型及其名称是 SQL 不兼容的重要原因之一。对于不同的 DBMS，大多数基本数据类型都得到了一致的支持，但许多高级数据类型却没有。

行

表中每一条记录的数据都是按行（row）存储的。

主键

主键（primary key）是用来唯一标识表中每一行的列（如国际标准书号 ISBN 就可作为主键），可以是一列或多列。

满足以下条件的列即可作为主键：

1. 任意两行都不具有相同的主键值。

2. 每一行都必须具有一个主键值（主键列不允许空值 NULL）。

3. 主键列中的值不允许修改或更新。

4. 主键值不能重用（即使某行从表中删除，它的主键依然不能赋给以后的新行）。

在使用多列作为主键时，上述条件必须应用到所有列，即所有列值的组合必须是唯一的（但不同列中可以存在相同的值）。

关系数据库

关系数据库由关系表组成，而关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联。

常见的两个关系表都会有至少一个相同类型且表示相同的列，对于其中一个表，该列是这个表的主键，从而能使另一个表甚至关系数据库中的其他表通过这个主键对应到这个表中的行。

一个优秀的关系数据库通常具有以下优点：

• 关系库中的每个表中的每个行的信息重复很少，减少了时间和空间的浪费。

• 如果信息有更新，仅需更新对应的表中的数据。

• 数据是一致的（没有重复的数据），使得处理数据和生成报表更简单。

总结：关系数据库的可伸缩性（scale）远比非关系数据库要好。

可伸缩是指，能够适应不断增加的工作量而不失败。

数据库管理系统

数据库管理系统（DataBase Management System，DBMS）是一种用于创建和操纵数据库的软件。

绝大多数 DBMS 都支持 ANSI SQL，但一些 DBMS 有自己的扩展。不同的 SQL 实现有自己的名称（如 Oracle 的 PL/SQL、微软 SQL Server 的 Transact-SQL 等）。

DBMS 可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

数据库管理系统、数据库和表的关系：

SQL 语句分类

1. DDL（Data Definition Language，数据定义语言）：用来定义数据库对象：数据库，表，列等。

   关键字：create、drop、alter 等。

2. DML（Data Manipulation Language，数据操作语言）：用来对数据库中表的数据进行增删改。

   关键字：insert、delete、update 等。

3. DQL（Data Query Language，数据查询语言）：用来查询数据库中表的记录。

   关键字：select、where 等。

4. DCL（Data Control Language，数据控制语言）：用来定义数据库的访问权限和安全级别，及创建用户。

   关键字：GRANT、REVOKE 等。

SQL 语法

• SQL语句可以单行或多行书写，以分号结尾。

• 可使用空格和缩进来增强语句的可读性。

• 大多数数据库的 SQL 语句不区分大小写，关键字建议使用大写（可以提高运行速度）。

字段

字段（filed）基本上与列的意思相同，但字段这个术语通常在计算字段这种场合下使用。

SELECT 语句

任何 SQL 语句都是查询（query）。但此术语一般指 SELECT 语句。

• 检索列：

  -- 从 tab_name 中检索出filed1, filed2 等列（字段）
  -- 可以检索单个列或多个列
  SELECT filed1, filed2, ...
  FROM tab_name;
  

  列的顺序是按照指令中指出的列名的顺序显示。行的顺序是按照存储在物理内存中的顺序显示。

  ---

• 使用通配符 * 检索表中的所有列：

  -- 检索出 tab_name 中所有的列
  SELECT *
  FROM tab_name;
  

  一般情况下，返回的列是按照存储在物理内存中的顺序显示的，并没有特定顺序。可能是按照数据被添加到表中的顺序显示（在没有增删数据的情况下），也可能不是（在有数据进行过更新或删除，这个顺序将会受到 DBMS 重用回收存储空间的方式的影响）。

  使用通配符可以很方便地显示表中所有的列（包括名字未知的列），但这么做可能会降低检索速度和应用程序的性能（因为程序会检索所有表中的所有列）。

ALL 和 DISTINCT

可以在列名前指定 ALL（默认）和 DISTINCT 关键字，它们表示：

• ALL —— 列出当前指定的列的所有行。

• DISTINCT —— 列出由当前所有列组合的不重复的行。

可以对聚集函数使用 ALL 和 DISTINCT 关键字指定。

• ALL —— 默认选项，包含所有的行，对聚集函数指定 ALL（可忽略）表示对所有的行执行计算。

• DISTINCT —— 指定 DISTINCT，包含不重复的行。

  • 对聚集函数指定 DISTINCT 表示只对不重复的行进行计算。

  • 使用 DISTINCT 必须明确指出列名，而不能使用 *。

列出不重复的行：

-- filed1, filed2, ... 不重复
SELECT DISTINCT filed1, filed2, ...
FROM tab_name;

DISTINCT 作用于所有指定的列，该 SELECT 语句会返回所有不重复的 filed1, filed2, ... 组合的行。使用 DISTINCT * 可以返回由所有列组合的不重复的行。

限制返回的行

各 DBMS 实现并不相同：

• SQL Server：使用 TOP 关键字。

  -- 返回前 n 行
  -- n 是任一正整数
  SELECT TOP n filed1, filed2, ...
  FROM tab_name;
  

  ---

• DB2：FETCH FIRST n ROWS ONLY。

  -- 返回前 n 行
  -- n 是任一正整数
  SELECT filed1, filed2, ...
  FROM tab_name
  FETCH FIRST n ROWS ONLY;
  

  ---

• Oracle：基于 ROWNUM（行计数器）。

  -- 返回前 n 行
  -- n 是任一正整数
  SELECT filed1, filed2, ...
  FROM tab_name
  WHERE ROWNUM <= n;
  

  ---

• MySQL、MariaDB、PostgreSQL and SQLite：使用 LIMIT 子句。

  -- 返回前 n 行
  -- n 是任一正整数
  SELECT filed1, filed2, ...
  FROM tab_name
  LIMIT n;
  

  -- 返回从第 m 行开始的前 n 行
  -- n 是任一正整数，m 是任一非负整数
  SELECT filed1, filed2, ...
  FROM tab_name
  LIMIT n OFFSET m;
  

  在 MySQL、MariaDB 和 SQLite 中，上条指令可简化为：

  -- 返回从第 m 行开始的前 n 行
  -- n 是任一正整数，m 是任一非负整数
  -- m 指定开始的行数，n 指定返回的行数
  SELECT filed1, filed2, ...
  FROM tab_name
  LIMIT m, n;
  

  行是从 0 开始计数。

ORDER BY 排序数据

1. 使用 ORDER BY 子句取一个或多个列的名字，据此对数据进行排序：

   -- order1, order2, ... 是排序的依据
   -- 该语句会先对 order1 进行排序
   -- 仅在具有多行相同 order1 值时，才按照 order2 排序
   SELECT filed1, filed2, ...
   FROM tab_name
   ORDER BY order1, order2, ...;
   

   ORDER BY 子句必须是 SELECT 语句中的最后一条子句。默认是升序排序（从 A 到 Z）。

   ---

2. 按相对列位置进行排序：

   -- n1、n2 …… 是小于或等于指定的列的总数的正整数
   -- n1、n2 …… 这组数中的每两个数都不相等
   SELECT filed1, filed2, ...
   FROM tab_name
   ORDER BY n1, n2, ...;
   

   按相对位置排序的好处是不用输入列名，但有可能会造成错用列进行排序（特别是在对 SELECT 清单进行更改时）。

实际列名排序和相对列位置排序可混合使用。

📌最好的排序方式是，对冗长的列名重命名，然后使用列名进行排序。这样可以减少错误的出现。

指定排序方向

指定排序的关键字：ASC —— 升序排序、DESC —— 降序排序。ASC 和 DESC 必须对每一列进行指定，因为它们只会对直接位于其前面的列名指定排序方式。可以对某一列指定 ASC（或 DESC）后对其它列指定 DESC（或 ASC）。

-- order1, order2, ... 是排序的依据，且都被指定了升序排序
SELECT filed1, filed2, ...
FROM tab_name
ORDER BY order1 ASC, order2, ...

1. DESC 关键字指定降序排序。

   -- order1, order2, ... 是排序的依据，且都被指定了降序排序
   SELECT filed1, filed2, ...
   FROM tab_name
   ORDER BY order1 DESC, order2 DESC, ...;
   

2. 对某些列指定升序排序，对另外某些列指定升序排序。

   -- asc_order1, asc_order2, ... 都是指定升序排序
   -- desc_order1, desc_order2, ... 都是指定降序排序
   SELECT filed1, filed2, ...
   FROM tab_name
   ORDER BY asc_order1 ASC, asc_order2, ..., desc_order1 DESC, desc_order2 DESC, ...;
   

大多数 DBMS 默认使用字典（dictionary）排序（即大小写字母被视为相同），但许多 DBMS 允许改变这种方式。

WHERE 过滤数据

使用 WHERE 子句能够根据指定的搜索条件（search criteria），检索用户需要的数据。搜索条件也被称为过滤条件（filter condition1）。

• 检索单一条件：

  -- condition1 是表中任一列名
  -- condition1 的类型和 n 的类型要一致
  SELECT filed1, filed2, ...
  FROM tab_name
  WHERE condition11 = n;
  

  数据也可以在应用层进行过滤，但当 DBMS 返回的数据超出实际所需的数据时，会影响应用的性能，并且降低应用的可伸缩性，这还会造成网络宽带的浪费（服务器不得不通过网络发送多余的数据）。

  ---

• WHERE 子句操作符（条件操作符）：

  并非所有 DBMS 都支持所有的操作符，某些 DBMS 可能不支持个别操作符。

  操作符	说明	操作符	说明
  =	等于	>	大于
  <> or !=	不等于	>=	大于等于
  <	小于	!>	不大于
  <=	小于等于	BETWEEN	范围在指定的两个值之间
  !<	不小于	IS NULL	为 NULL 的值

  ---

• 使用 BETWEEN 在指定范围内检索：

  -- condition1 是表中任一列名
  -- condition1 的类型和 n 、m 的类型要一致
  -- n 和 m 为低端值和高端值
  -- 检索时匹配的值包括 n 和 m
  SELECT filed1, filed2, ...
  FROM tab_name
  WHERE condition11 BETWEEN n AND m;
  

  ---

• 使用 IS NULL 检索空值：

  -- condition1 是表中任一列名
  SELECT filed1, filed2, ...
  FROM tab_name
  WHERE condition11 IS NULL;
  

• IS NOT NULL 检索不为空值的行：

  -- condition1 是表中任一列名
  SELECT filed1, filed2, ...
  FROM tab_name
  WHERE condition11 IS NOT NULL;
  

逻辑操作符

可以在一条 WHERE 子句中包含任意数量的逻辑操作符（logical operator），且允许两者结合以进行复杂、高级的过滤。

1. AND 操作符检索满足所有给定条件的行：

   -- condition11，condition12 …… 是包含条件操作符的子句
   SELECT filed1, filed2, ...
   FROM tab_name
   WHERE condition11 AND condition12
       AND ...;
   

   ---

2. OR 操作符检索满足任一条件的行：

   -- condition11，condition12 …… 是包含条件操作符的子句
   SELECT filed1, filed2, ...
   FROM tab_name
   WHERE condition11 OR condition12
       OR ...;
   

   许多 DBMS 在使用 OR 操作符时，当第 1 个条件满足的情况下，就不再计算第 2 个条件。

   ---

3. NOT 操作符是否定操作符，否定跟随在其后的条件。

   -- condition1 是任意条件子句
   SELECT filed1, filed2, ...
   FROM tab_name
   WHERE NOT condition11;
   

   大多数 DBMS 允许使用 NOT 否定任何条件。

IN 指定条件范围

IN 操作符用来指定条件范围，匹配范围中指定的每个条件。IN 操作符后的括号中的合法值用逗号分隔。

-- condition1 是表中任一列
-- val1, val2, ... 是与 condition1 类型相同的一组合法值
SELECT filed1, filed2, ...
FROM tab_name
WHERE condition1 IN (val1, val2, ...);

IN 操作符和 OR 操作符的功能类似，但在某些方面，IN 操作符有其优点。

• 在有很多合法选项时，IN 操作符可读性更强。

• 与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。

• IN 操作符一般比一组 OR 操作符执行得更快。

• IN 操作符可包含其他 SELECT 语句，能更动态地建立 WHERE 子句。

通配符

通配符（wildcard）是用于匹配值的一部分的特殊字符。

搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。

利用通配符，可以创建比较特定数据的搜索模式。要使用通配符，必须在搜索子句中使用 LIKE 操作符。且通配符只能用于字符串。

• 使用通配符搜索一般比其他简单搜索耗费更长的处理时间。

• 不要过度使用通配符。

• 尽量不要把通配符用在搜索模式的开始处（通配符置于开始处是最慢的）。

• 不同类型的通配符可以混合使用。

1. % 通配符：表示任何字符出现任意次数。

   -- condition1 是表中任一字符类型的列
   -- 该语句表示在 condition1 列中搜索以 ABC 开头的字符串
   SELECT filed1, filed2, ...
   FROM tab_name
   WHRER condition1 LIKE 'ABC%';
   

   • 包含 ABC 的字符串：'%ABC%'；

   • 以 ABC 结尾的字符串：'ABC%'；

   • 以 A 为开头，以 B 为结尾的字符：'A%B'；

   • 某些 DBMS 会用空格来填补字段内容，可能会对该搜索模式造成影响。

   • 包含 AB 和 CD 的字符串：'%AB%CD%'；

   • ……

   ---

2. _ 通配符：表示匹配单个任意字符。

   -- condition1 是表中任一字符类型的列
   -- 该语句表示匹配以 ABC 结尾，开头为任意一个字符的字符串
   SELECT filed1, filed2, ...
   FROM tab_name
   WHERE condition1 LIKE '_ABC';
   

   ---

3. [] 通配符：用来指定一个字符集，它必须匹配指定位置的一个字符。

   Example:

   -- condition1 是表中任一字符类型的列
   -- 该语句匹配以 A 或 B 其中之一与 C 的组合的字符串（仅有两个字符）
   SELECT filed1, filed2, ...
   FROM tab_name
   WHERE condition1 LIKE '[AB]C';
   

   使用 ^ 可以来否定 [] 定义的字符集：'[^AB]' —— 该模式会匹配除了 A 和 B 之外的任何一个字符。

子查询

子查询通过嵌套的SELECT语句，将查询结果作为值返回。

SELECT可以嵌套地使用。

拼接

拼接（concatenate）是指将值联结到一起（将一个值附加到另一个值）构成单个值。

• SQL Server 使用 + 号拼接：

  -- filed1, filed2, filed3, ... 可以是任意同类型的字段
  SELECT filed1 + filed2 + filed3 + ...
  FROM tab_name;
  

• DB2、Oracle、PostgreSQL 和 SQLite 使用 ||：filed1 || filed2 || filed3 || ...。

• MySQL 和 MariaDB 使用 Concat() 函数：Concat(filed1, filed2, filed3, ...)。

某些 DBMS 会用空格填充字段，可以使用 RTRIM() 函数来清除这些空格。

AS 指定别名

使用 AS 关键字赋予字段或值一个别名（alias）。别名既可以是一个单词，也可以是一个字符串，字符串需用引号括起。使用别名时不应使用表中实际的列名，虽然这样做也算合法，但可能会产生模糊的错误消息。

-- filed 是一个字段或值
-- alias 是 filed 的别名
-- 赋予别名后可以在接下来的子句中使用别名来替代这个字段
SELECT filed AS alias
FROM tab_name
ORDER BY alias;

函数

数值处理函数

数值处理函数仅处理数值数据，是最一致、最统一的函数。

常用的数值处理函数：

文本处理函数

常用的文本处理函数：

日期和时间处理函数

日期和时间函数在 SQL 中可移植性最差。

• SQL Server 使用 DATEPART(part, date) 返回日期中的一部分，part（不是字符型的参数）指定要返回的成分，date 是要从中国年返回成分的日期。

• PostgreSQL 使用 DATE_PART(part_str, date) 返回日期中的一部分，part_str 指定要返回的成分，date 是要从中国年返回成分的日期。

• Oracle 和 PostgreSQL 都支持 EXTRACT(part FROM date) 返回日期中的一部分。part（不是字符型）指定要返回的成分，date 是要从中国年返回成分的日期。

• Oracle 还支持 to_date(date_str, form_str) 将字符串转化成日期，date_str 是某个带有日期格式的字符串，form_str 指定日期的形式。一般这样使用：WHERE date_column BETWEEN to_date('2020-01-01', 'yyyy-mm-dd') AND todate('2020-12-31', 'yyyy-mm-dd')。

• DB2、MySQL 和 MariaDB 中有 YEAR(date) 和 MONTH(date) 等函数用于提取年份和月份等信息。

• 在 SQLite 可以使用 strftime('%Y', date) 提取年份，也可以提取月份等。

MySQL Date 函数：

SQL Server Date 函数：

汇总数据

使用聚集函数（aggregate function）可以对某些行进行计算并返回一个值。

SQL 聚集函数：

Usage for Example:

SELECT AVG(filed) AS average
FROM tab_name
WHERE condition1;

SELECT 语句可以根据需要包含多个聚集函数。

GROUP BY 数据分组

分组是使用 SELECT 语句的 GROUP BY 子句，将数据分为多个逻辑组，对每个组进行操作。

-- 显示 filed1, filed2, ... 并根据它们进行分组
-- COUNT(*) 表示计算分组中的所有行
-- 该语句是先按照 filed1 进行分组，然后再按照 filed2 进行分组
-- 使用分组后输出的结果可能是根据 filed1, filed2, ... 排序好的
SELECT filed1, filed2, ..., COUNT(*);
FROM tab_name
GROUP BY filed1, filed2, ...;

GROUP BY 子句指示 DBMS 分组数据，然后对每个组（不是整个结果集）进行聚集。

使用 GROUP BY 的规定：

• GROUP BY 子句可以包含任意数目的列。所以可以对分组进行嵌套，更细致地进行数据分组。

• 在建立分组后，操作的最小单位只能是分组，而无法再对个别列进行操作。

• GROUP BY 子句中列出的每一列都必须是检索列或有效表达式列（但不能是聚集函数）。在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式，而不能使用别名。

• 大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。

• 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。

• 分组列中所有包含具有 NULL 值的行，都将 NULL 作为一个分组返回。

• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

📌并非所有 SQL 实现都支持在使用 GROUP BY 子句时，使用相对位置指定列，并且使用它容易在编辑 SQL 语句时出错。

HAVING 过滤分组

使用 HAVING 子句来过滤分组。HAVING 支持所有 WHERE 子句支持的操作符，两者的区别是 WHERE 仅对行进行过滤，而 HAVING 可以对分组进行过滤（在没有指定 GROUP BY 时，大多数 DBMS 会同等对待 WHERE 和 HAVING，即 HAVING 也可以对行进行过滤）。

-- filed_1, filed_2, ... 是聚集操作
-- condition1 是相对于每行的条件，condition1_ 是相对于每个分组的条件
SELECT filed1, filed2, ... filed_1, filed_2, ...;
FROM tab_name
WHERE condition1
GROUP BY filed1, filed2, ...
HAVING condition1_;

在同时使用 WHERE 和 GROUP BY 时，被 WHERE 过滤掉的行并不会包括在分组中。

分组和排序

ORDER BY 与 GROUP BY 的区别

GROUP BY 分组不一定是按照分组顺序输出的，所以应该提供明确的 ORDER BY 子句指定排序方式，以确保在任何情况下输出都能正确地排序。

子查询

子查询（subquery）是指嵌套在其他查询中的查询。

-- 每个子查询中的 SELECT 语句有且仅有一个字段
-- 在子查询中企图检索多个列将返回错误
-- 理论上可以无限嵌套，但实际使用时由于性能的限制，不能嵌套太多的子查询
SELECT filed1, filed2, ...
FROM table1
WHERE condition11 IN (SELECT filed_1
                     FROM table2
                     WHERE condition12 IN (SELECT filed_2
                                          FROM table3
                                          WHERE condition13 IN (...));

在 SELECT 语句中，子查询总是从内向外处理（类似递归的处理方式）。

使用计算字段进行子查询

-- filed_1, filed_2, ... 是计算字段，是由其 AS 前的圆括号中的子查询建立的
-- table1._filed1 是 table1 中名为 _filed1 的列或列表达式
-- table2._filed1 是 table2 中名为 _filed2 的列或列表达式
-- table1._filed1 和 table2._filed1 是两个不同表格中同名同类型的列，并不是同一个列
-- table1._filed2 和 table3._filed2 的关系与 table1._filed1 和 table2._filed1 类似
-- 第 1 条子查询的解释是，对所有 table2 中符合 table1._filed1 = table2._filed1 的行进行计数
SELECT filed1, filed2, ...
       (SELECT COUNT(*)
        FROM table2
        WHERE table1._filed1 = table2._filed1) AS filed_1
       (SELECT COUNT(*)
        FROM table3
        WHERE table1._filed2 = table3._filed2) AS filed_2
       ...
FROM table1;

计算字段用一个句点分隔表名和列名，句点前是表名，句点后是列名，即 tab_name.column，这种写法也叫完全限定列名。在有可能混淆列名时（如使用子查询时）必须使用这种语法。

JOIN 联结表

联结（join）是一种机制，用来在一条 SELECT 语句中关联表，可以联结多个表返回一组输出。

联结不是物理实体，它在实际的数据库表总并不存在，它仅在数据查询的执行期间存在。 对于联结，需要考虑性能，如果联结过多的表，可能会非常耗费资源，使性能下降。 SQL 本身不限制每个联结约束中表的数目，但实际上许多 DBMS 都有限制。

联结类型：

• 内联结（inner join）

• 外联结（outer join）

• 自联结（self-join）

• 自然联结（natural join）

可以在一个 SELECT 语句中使用联结多个表，而且每个表的联结类型可以不相同。

INNER JOIN 内联结

内联结（inner join）又称为等值联结（equijoin），是基于两个表之间的项等测试。

许多子查询都可以使用内联结来替代，但是子查询与联结同样需要耗费一定的资源，所以在一个语句中，不应使用过多的子查询和联结。

1. 在 FROM 中指定多个列（等值联结）。

   对于联结，需要保证所有联结都有 WHERE 子句，并且要保证 WHERE 子句的正确性。

   -- filed1, filed2, ... 是存在于 table1, table2, ... 中的任意列或列表达式
   -- 如果在不同表中出现名称相同的列，就需要使用完全限定名来指定
   -- 完全限定名：tab_name.column_name
   -- condition1s 中必须给出联结条件
   SELECT filed1, filed2, ...
   FROM table1, table2, ...
   WHERE condition1s;
   

   如果上述语句没有在 WHERE 中指出配对方式，那么 table1 中的每一行会与 table2 中的每一行进行配对，然后 table1 和 table2 已经配对完成的行会再次与 table3 中的每一行配对，以此类推，直到所有的表都完成了配对。这种由没有联结条件的表联结后返回的结果为笛卡尔积（cartesian product）。

2. 使用 INNER JOIN（内联结）指定要联结的表，联结条件用特定的 ON 子句给出。

   同样地，对于这种方式的联结，需要保证所有联结都有有效的 ON 子句。如果没有 ON 子句，也会出现笛卡尔积。

   -- filed1, filed2, ... 是存在于 table1, table2, ... 中的任意列或列表达式
   -- 如果在不同表中出现名称相同的列，就需要使用完全限定名来指定
   -- condition1 中必须给出联结条件
   SELECT filed1, filed2, ...  
   FROM table1
   INNER JOIN table2 ON condition11
   INNER JOIN table3 ON condition12
   INNER JOIN ...
   -- 如果有其他条件的话 WHERE 子句在 INNER JOIN 之后给出
   

ANSI SQL 规范首选 INNER JOIN 语法。

自联结

自联结（self-join）是指在同一个 SELECT 语句中，多次联结同一个表。

对于自联结来说，由于多次引用同一个表，如果不对表起别名的话，会造成系统混乱（同一个表，表名自然相同，所以系统无法区分究竟指的是哪个表，虽然本质上都是同一个表）。

SELECT T1.filed1, T1.filed2, ...
FROM tab_name AS T1, tab_name AS T2
WHERE T1.filed_1 = T2.filed_1
    AND T2.filed_2 = value;

虽然自联结也可以用子查询替换，但对于大多数 DBMS 来说，处理联结远比处理子查询快。

SELECT filed1, filed2, ...
FROM tab_name
WHERE filed_1 = (SELECT filed_1
                 FROM tab_name
                 WHERE filed_2 = value);

自然联结

自然联结（natural join）指每个含义相同的列仅出现一次的联结。自然联结一般是通过对一个表使用通配符，而对其他表的列使用明确的子集来完成。

SELECT T1.*, T2.filed1, T2.filed2, ...
FROM table1 AS T1, table2 AS T2
WHERE T1.filed_1 = T2.filed_1
    AND ...;

OUTER JOIN 外联结

外联结（outer join）是指包含无关联行的联结。通常，内联结都是将一个表格中的行与另一个表格中的行通过联结条件进行关联，此时两个表格中没有被联结条件关联起来的行将会被忽略。而外联结则会包含这些没有被联结条件所关联起来的行，并根据需要给某些列赋予 NULL 值。

外联结有三种类型：

• LEFT OUTER JOIN —— 左外联结，包含左表的所有行（即 FROM 后面紧跟的表，示例中为 T1），即使在右表（示例中为 T2）中没有被匹配（结果为 NULL）。

  基本上所有的 DBMS 都支持。

  SELECT T1.filed1, T1,filed2, ...
         T2.filed_1, T2.filed_2, ...
  FROM table1 AS T1
  LEFT OUTER JOIN table2 AS T2
  ON T1.filed = T2.filed;
  

• RIGHT OUTER JOIN —— 右外联结，包含左表的所有行（即 JOIN 关键字后紧跟的表，示例中为 T2），即使在右表（示例中为 T1）中没有被匹配（结果为 NULL）。

  SQLite 不支持。

  SELECT T1.filed1, T1,filed2, ...
         T2.filed_1, T2.filed_2, ...
  FROM table1 AS T1
  RIGHT OUTER JOIN table2 AS T2
  ON T1.filed = T2.filed;
  

• FULL OUTER JOIN —— 全外联结，只要左表和右表其中一个表中存在匹配，就返回。

  MariaDB、MySQL 和 SQLite 都不支持。

左外联结和右外联结可以相互转换。

联结、聚合函数与分组

联结的一种用法是使用聚合函数从另一个表格中汇总数据。

-- condition1 是联结条件
SELECT T1.filed1, T2.filed2, ...
       COUNT(T2.filed_1) AS name_filed
FROM table1 AS T1
INNER JOIN table2 AS T2 ON condition1
GROUP BY T1.filed1, T1,filed2, ...;

对于上述语句也可以指定外联结，仅需将 INNER JOIN 替换成外联结的关键字。

使用外联结从另一个表中汇总数据，那些没有被关联的行经过 COUNT() 函数计算后的返回值将会为 0。

UNION 组合查询

组合查询通常称为并（union）或符合查询（compound query）。通过组合查询可以同时执行多条 SELECT 语句，并将结果作为一个查询结果集返回。

需要使用组合查询的情况：

• 在一个查询中从不同的表返回结构数据；

• 对一个表执行多个查询，按一个查询返回数据。

📌

• 组合相同表的查询所完成的工作一般可以用 WHERE 子句替代。

  理论上使用 WHERE 子句和组合查询从性能上看并没有太大差别，但各实现之间任有差别。

• 第一条 SELECT 中的列名或指定的别名作为整个组合查询的结果集的列名。因此组合查询所返回的结果集的列名是第一条 SELECT 中的列名，且在组合查询中的 ORDER BY 子句中指出的列名应是第一条 SELECT 中第列名。

• 每一个组合查询最多仅能有一条 ORDER BY 子句，且必须位于整个组合查询语句的最后（即最后一条 SELECT 语句）。

  即，无法对某条 SELECT 指定一种排序方式，而对另一条 SELECT 指定另一种排序方式，所有的 SELECT 只能用同一种方式排序。

使用组合查询的规则：

• 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。

• 每个查询必须包含相同的列、表达式或聚集函数（数量必须要相同，类型必须要兼容）。

使用 UNION 关键字，会自动去除重复的行：

-- filed1, filed2, ... 和 filed_1, filed_2, ... 数量必须相同，类型要兼容
-- table1 和 table2 可以是同个表格，也可以是不同的表格
-- ORDER BY 必须位于语句的最后
SELECT filed1, filed2, ...
FROM table1
WHERE condition11
UNION
SELECT filed_1, filed_2, ...
FROM table2
WHERE condition12
UNION
...
ORDER BY filed;

在上述语句中，将 UNION 替换成 UNION ALL，返回的结果集将包含重复的行。

INSERT 语句

INSERT 语句用来将行插入（或添加）到数据库表。

插入完整的行

1. 简单但不保险的方法：

   INSERT INTO tab_name
   VALUES(value1,
          value2,
          value3,
          ...,
          NULL,
          ...);
   

   VALUES 后面的括号中，按照表中列的定义次序，指出了要存储到表中的数据，且必须给每一列提供一个值（在没有指定列名的情况下）。如果某列没有值，则该列应该使用 NULL（在允许对该列指定空值的情况下）。各列必须以它们在表定义中出现的次序填充。

   这种语法虽然简单但并不安全，因为这种 SQL 语句高度依赖于表中列的定义次序。如果表结构发生变动，那么这样的语句并不能按照预期的情况执行。

   📌在某些 SQL 实现中，INTO 关键字是可选的。但为了确保可移植，还是要提供 INTO 关键字。

2. 保险但比较繁琐的方法：

   -- filed1 与 value1 对应，filed2 与 value2 对应，以此类推
   INSERT INTO tab_name(filed1,
                          filed2,
                          filed3,
                          ...)
   VALUES(value1,
          value2,
          value3,
          ...);
   

   这种方法需要在表名后面，使用括号将表中的所有列名指出（不用按照表中列的定义顺序）。在插入行时，VALUES 必须以其指定的次序匹配指定的列名，这样即使表结构改变，该 INSERT 语句仍能正确工作。不要使用没有明确给出列的 INSERT 语句。

主键的值必须有唯一性，DBMS 不允许插入主键值重复的行。如果确实需要插入，应先删除原有的记录。

插入行的一部分

-- filed1 与 value1 对应，filed2 与 value2 对应，以此类推
INSERT INTO tab_name(filed1,
                       filed2,
                       ...)
VALUES(value1,
       value2,
       ...);

同样使用表名后面的括号指定列，只不过仅需指出需要插入的部分列，并且只需给这些列提供值。

无论是插入完整的行还是插入行的一部分，都必须确保 VALUES 的数目正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每一个列一个值。否则，就会产生错误。

在 VALUES 中没有指出的列，DBMS 将赋予它们默认值或空值。这些列必须满足：

• 列定义为允许 NULL 值。

• 在表定义中给出了默认值。

INSERT SELECT

INSERT 还允许利用 SELECT 语句将检索到的结果插入表中。

-- 将 table2 中满足条件的 filed_1, filed_2, ... 列按照对应顺序插入到 table1 中
-- table1 和 table2 可以是同一个表
INSERT INTO table1(filed1,
                   filed2,
                   ...)
SELECT filed_1,
       filed_2,
       ...
FROM table2
WHERE condition1s;

一般 INSERT 只插入一行。而 INSERT SELECT 可以用一条语句插入多行。

SELECT INTO

SELECT ... INTO 用于从一个表中复制数据，然后把数据插入到另一个新表中（INTO 子句后指定的表，该语句执行后会自动创建）。

📌MySQL 不支持 SELECT ... INTO 语句。

-- INTO 后面的 IN 子句代表插入到其他数据库
-- IN 子句可省略，默认代表当前数据库
-- new_table 的表结构与 old_table 指定列 filed1, filed2, ... 的结构相同
SELECT filed1, filed2, ...
INTO new_table [IN external_db]
FROM old_table;

SELECT INTO 语句可以通过使用促使查询没有数据返回的 WHERE 子句创建一个新的空表：

SELECT filed1, filed2, ...
INTO new_table
FROM old_table
WHERE 1 = 0;

更新和删除数据

在客户端或服务器的 DBMS 中，使用 UPDATE 和 DELETE 语句可能需要特殊的安全权限。

UPDATE 语句

UPDATE 语句用于更新（修改）表中的数据。

有两种使用 UPDATE 语句的方式：

1. 更新表中的特定行 —— 使用 WHERE 子句过滤：

   基本的 UPDATE子句由三部分组成：

   • 要更新的表 —— 使用 UPDATE 关键字指定；

   • 列名和它们的新值 —— 使用 SET 关键字指定更新的内容（即使用 “列=值” 对将新值赋给被更新的列）；

   • 确定要更新哪些行的过滤 —— 使用 WHERE 子句指定过滤条件。 在 UPDATE 中，WHERE 可以使用 SELECT 子查询。

   -- filed1, filed2, ... 是 tab_name 中的列
   -- value1, value2, ... 是与 filed1, filed2, ... 相对应类型的数据
   -- filed1 = value1, filed2 = value2, ... 是 “列=值” 对
   -- condition1s 是过滤的条件，指定了要更新的行
   UPDATE tab_name
   SET filed1 = value1,
       filed2 = value2,
       ...
   WHERE condition1s;
   

2. 更新表中的所有行 —— 不使用 WHERE 子句。

   一般的 UPDATE 字句都要使用 WHERE 字句指定过滤条件，否则 SET 子句指定的更新内容将应用到所有的列。

📌有的 SQL 实现支持在 UPDATE 语句中使用 FROM 子句，用一个表的数据更新另一个表的行。

使用 UPDATE 删除某个列的值：将要删除的列置为 NULL（假设表定义允许 NULL）。如：

UPDATE tab_name
SET del_filed = NULL
WHERE condition1s;

DELETE 语句

使用 DELETE 语句可以从一个表中删除（去掉）数据。

有两种使用 DELETE 语句的方式：

• 从表中删除特定的行 —— 使用 WHERE 子句过滤：

-- condition1s 是过滤条件，指定了要删除的行
DELETE FROM tab_name
WHERE condition1s;

• 从表中删除所有行 —— 不使用 WHERE 子句： 使用 DELETE 语句时应该指定 WHERE 子句，否则它将会删除表中所有的行。 如果确实想删除表中所有行，应该使用速度更快的 TRUNCATE TABLE 语句。

DELETE 语句删除的是表中的行，即它的操作对象是行而不是列，如果想要删除列，则要使用 UPDATE 语句将该列置为 NULL。

UPDATE 和 DELETE 的使用准则

• 绝对不要使用不带 子句的 UPDATE 或 DELETE 子句，除非确实打算更新和删除每一行。

• 应保证每个表都有主键。

• 在使用 UPDATE 或 DELETE 之前，应先用 SELETE 进行测试，确保 WHERE 子句过滤的是正确的记录。:

• 使用强制实施引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关的行。

• 如果 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 子句，那么应该使用它。

📌如果 SQL 没有撤销（undo）按钮，应该更小心地使用 UPDATE 和 DELETE 语句。

创建和删除数据库

创建数据库使用 CREATE DATABASE 语句。

CREATE DATABASE db_name;

删除数据库使用 DROP DATABASE 语句。

DROP DATABASE db_name;

注意：删除数据库后，数据库中的所有数据（表、视图、索引等数据都将会被删除）。

SQL 通用数据类型

数据类型定义列中存放的值的种类。数据库表中的每个列都要求有名称和数据类型。SQL 开发人员必须在创建 SQL 表时决定表中的每个列将要存储的数据的类型。

不同的 DBMS 数据类型有些不同。有的 DBMS 允许自定义数据类型，有的不允许。

以下是 SQL 通用数据类型：

SQL 数据类型快速参考手册：

时间数据格式

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD

• DATETIME - 格式：YYYY-MM-DD HH:MM:SS

• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

• YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD

• DATETIME - 格式：YYYY-MM-DD HH:MM:SS

• SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS

• TIMESTAMP - 格式：唯一的数字

创建和操纵表格

CREATE TABLE 创建表

一般有两种创建表格的方法：

1. 使用 SQL 语句 CTEATE TABLE 创建表格；

2. 多数 DBMS 都具有交互式创建和管理数据库表的工具。

   • 其本质是交互工具根据用户操作自动生成并执行相应的 SQL 语句。

不同的 SQL 实现中，CREATE TABLE 语句的语法会有所不同（主要区别是在列定义上）。

使用 CREATE TABLE 创建表，必须给出以下信息：

• 新表的名字，在关键字 CREATE TABLE 之后给出；

• 表列的名字和定义，在列名后的括号中指出，用逗号分隔；

• 有的 DBMS 还要求指定表的位置。

CREATE TABLE tab_name
(
    column_name1    DataType1,
    column_name2    DataType2,
    column_name3    DataType3,
    cluumn_name4    DataType4,
    ...
);

创建新表时只能指定不存在的表名，SQL 无法通过 CREATE 语句覆盖已有的表。

ALTER TABLE 更新表定义

使用 ALTER TABLE 时需要考虑的事情：

• 理想情况下，不要在表中包含数据时对表结构进行更新（特别是不要删除已有的列）。 应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

• 所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。

• 许多 DBMS 不允许删除或更改表中的列。

• 多数 DBMS 允许重新命名表中的列。

• 许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

使用 ALTER TABLE 必须给出的信息：

• 在 ALTER TABLE 之后给出表名；

• 列出要更改表的操作。

1. 增加表列：

   ALTER TABLE tab_name
   ADD filed DataType;
   

2. 删除列：

   ALTER TABLE tab_name
   DROP COLUMN filed;
   

3. 修改列定义：

   ALTER TABLE tab_name 
   ALTER COLUMN column_name NewDataType;
   

   在MySQL中使用：

   ALTER TABLE tab_name 
   MODIFY column_name NewDataType;
   

复杂的表结构更改一般需要执行以下步骤：

1. 用新的列布局创建一个新表；

2. 使用 INSERT SELECT 语句从旧表复制数据到新表；

3. 检验包含所需数据的新表；

4. 重命名旧表（在确定无误的情况下可删除）；

5. 用旧表原来的名字重命名新表；

6. 根据需要，重新创建触发器、存储过程、索引和外键。

使用 ALTER TABLE 的注意事项：

• SQLite 对使用 ALTER TABLE 的最重要的限制是：不支持使用 ALTER TABLE 定义主键和外键，必须在最初创建表时指定。

• 在使用 ALTER TABLE 之前应先做好完整的备份（表结构和数据的备份）。

• 表结构的更改不能撤销。

  • 如果增加了不需要的列，也许无法删除它们。

  • 如果删除了不应该删除的列，可能会丢失该列中的所有数据。

DROP TABLE 删除表

DROP TABLE 语句用于删除表本身（包括表数据）。

DROP TABLE tab_name;

📌删除表时，没有确认步骤，且不能撤销。

📌使用关系规则防止意外删除：

许多 DBMS 允许强制实施有关规则，防止删除与其他表相关联的表。在实施这些规则时，如果对某个表发布一条 DORP TABLE 语句，且该表是某个关系的组成部分，则 DBMS 将阻止这条语句执行，直到该关系被删除为止。

TRUNCATE TABLE 删除表数据

TRUNCATE TABLE 语句用于删除表内的数据（但并不包括表本身）。

TRUNCATE TABLE tab_name;

重命名表

对于重命名表，每个 DBMS 的支持都有所不同：

• DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 使用 RENAME 语句。

• SQL Server 使用 sp_rename 存储过程。

• SQLite 使用 ALTER TABLE 语句。

SQL 约束

SQL 约束（Constraints）用于规定表中如何插入或处理数据的规则。如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL 有如下几种约束：

Example：

CREATE TABLE tab_name
(
    # 主键约束
    column1  Type1  PRIMARY KEY,
    # 默认为允许 NULL，可以不用指出
    column2  Type2  [NULL], 
    # 不允许 NULL 的列，必须用 NOT NULL 指出
    column3  Type3  NOT NULL,
    # 用 DEFAULT 指定默认值
    # default_val 是 Type4 类型的值
    column4  Type4  NOT NULL  DEFAULT default_val,
    ...
);

NOT NULL 非空约束

CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    ...
);

添加 NOT NULL 约束：

ALTER TABLE tab_name
MODIFY column_name Type1 NOT NULL;

UNIQUE 唯一约束

每个表可以有多个 UNIQUE 约束。

创建表时：

CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    # 方法一：直接在列定义中用 UNIQUE 关键字指出
    column2 Type2 NOT NULL UNIQUE,
    ...
    # 方法二：定义列后使用 UNIQUE()
    UNIQUE (column1)
);

-- 命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    column2 Type2 NOT NULL,
    ...
    # 可以不用定义 u_name
    CONSTRAINT [u_name] UNIQUE (column1, column2, ...)
);

更新表定义时：

ALTER TABLE tab_name
ADD UNIQUE (column_name);

-- 命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
ALTER TABLE tab_name
# name 可以省略
ADD CONSTRAINT u_name UNIQUE (column1, column2, ...);

撤销 UNIQUE 约束：

MySQL：

ALTER TABLE tab_name
DROP INDEX u_name;

SQL Server / Oracle / MS Access：

ALTER TABLE tab_name
DROP CONSTRAINT u_name;

PRIMARY KEY 主键约束

只要满足以下条件，就可以被用作主键：

• 任意两行的主键值都不相同（主键必须包含唯一值）。

• 每行都具有一个主键值（主键列不能包含 NULL 值）。

• 包含主键值的列从不修改或更新。

• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

创建表时：

-- 在列定义中直接指出主键
CREATE TABLE tab_name
(
    column1 Type1 NOT NULL PRIMARY KEY,
    ...
);

-- 通过 PRIMARY KEY () 定义
CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    ...
    PRIMARY KEY (column1)
);

-- 命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    column1 Type1 NOT NULL,
    ...
    # 可以不用定义 pk_name
    CONSTRAINT [pk_name] PRIMARY KEY (column1, column2, ...)
);

更新表定义时：

ALTER TABLE tab_name
ADD PRIMARY KEY (column_name);

-- 命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
ALTER TABLE tab_name
# name 可以省略
ADD CONSTRAINT pk_name UNIQUE (column1, column2, ...);

撤销 PRIMARY KEY 约束：

MySQL：

ALTER TABLE tab_name
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access：

ALTER TABLE tab_name
DROP CONSTRAINT pk_name;

FOREIGN KEY 外键约束

创建表时：

CREATE TABLE tab_name
(
    # 方法一：使用 REFERENCES 关键字
    column1 Type1 NOT NULL REFERENCES table1(column_1),
    column2 Type2 NOT NULL,
    column2 Type2 NOT NULL,
    ...
    # 方法二：使用 FOREIGN KEY ()
    FOREIGN KEY (column2) REFERENCES table2(column_2),
    # 命名 FOREIGN KEY 约束
    CONSTRAINT [fk_name] FOREIGN KEY (column3)
    REFERENCES table3(column_3)
);

更新表定义时：

ALTER TABLE tab_name
ADD FOREIGN KEY (column1)
REFERENCES table1(column_1);

-- 命名 FOREIGN KEY 约束
ALTER TABLE tab_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column1)
REFERENCES table1(column_1);

撤销 FOREIGN KEY 约束：

MySQL：

ALTER TABLE tab_name
DROP FOREIGN KEY fk_name;

SQL Server / Oracle / MS Access：

ALTER TABLE tab_name
DROP CONSTRAINT fk_name;

CHECK 检查约束

CHECK 约束常见用途：

• 检查最小或最大值。

• 指定范围。

• 只允许特定值。

创建表时：

CREATE TABLE tab_name
(
    # 限制 column1 的值大于 0
    column1 Type1 NOT NULL CHECK (column1 > 0),
    column2 Type2 NOT NULL,
    ...
    # 限制 column2 只能取 'Y' 或 'N'
    CHECK (column2 LIKE '[YN]')
);

-- 命名 CHECK 约束，并定义多个列的 CHECK 约束
CREATE TABLE tab_name
(
    column1 Type1 NOT NULL,
    column2 Type2 NOT NULL,
    ...
    CONSTRAINT chk_name CHECK (column1 > 0 AND column2 LIKE '[YN]')
);

修改表定义时：

ALTER TABLE tab_name
ADD CHECK (column1 > 0);

-- 命名 CHECK 约束，并定义多个列的 CHECK 约束
ALTER TABLE tab_name
ADD CONSTRAINT chk_name CHECK (column1 > 0 AND column2 LIKE '[YN]');

撤销 CHECK 约束：

MySQL：

ALTER TABLE tab_name
DROP CHECK chk_name;

SQL Server / Oracle / MS Access：

ALTER TABLE tab_name
DROP CONSTRAINT chk_name;

DEFAULT 默认约束

创建表时：

CREATE TABLE tab_name
(
    # default_val 是 Type4 类型的值
    column1  Type1  NOT NULL  DEFAULT default_val,
    ...
)

-- 可以使用类似 GETDATE() 的函数插入系统值
CREATE TABLE tab_name
(
    # 默认值为当前的日期
    birthday  DATE  NOT NULL  DEFAULT GETDATE(),
    ...
)

更新表结构时：

MySQL：

ALTER TABLE tab_name
ALTER column1 SET DEFAULT default_val;

SQL Server / MS Access：

ALTER TABLE tab_name
ADD CONSTRAINT name DEFAULT default_val for column1;

Oracle：

ALTER TABLE tab_name
MODIFY column1 DEFAULT default_val;

撤销 DEFAULT 约束：

MySQL：

ALTER TABLE tab_name
ALTER column1 DROP DEFAULT;

SQL Server / Oracle / MS Access：

ALTER TABLE tab_name
ALTER COLUMN column1 DROP DEFAULT;

AUTO INCREMENT 字段

在定义表时，在列中添加 auto-increment 字段可以在新纪录插入表时对该列生成一个唯一的数字。

定义为 auto-increment 字段的列必须是整数类型。

定义为 auto-increment 的列常常被定义为 PRIMARY KEY。

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务：

CREATE TABLE tab_name
(
    # 定义 column1 为 AUTO INCREMENT 主键字段
    column1 TYPE1 NOT NULL PRIMARY KEY AUTO_INCREMENT,
    column2 TYPE2,
    column3 TYPE3,
    ...
);

定义了 AUTO_INCREMENT 字段后，可以不用对该列指定值，或者对该列指定为 NULL 值（会自动添加一个唯一的值）。

MySQL 的 AUTO_INCREMENT 只能在 key 上被定义。PRIMARY KEY 和 UNIQUE 都是有效的定义。

-- val2 和 val3 是分别与 column2 和 column3 同类型的值

-- 不对 auto-increment 列指定值
INSERT INTO tab_name ( column2, column3 )
VALUES ( val2, val3 );

-- 指定为 NULL 值
INSERT INTO tab_name ( column1, column2, column3)
VALUES ( NULL, val2, val3);

AUTO_INCREMENT 默认的开始值是 1，每新增一条记录都会递增 1。

让 AUTO_INCREMENT 序列以其他的值起始：

-- number 是任意数字
ALTER TABLE tab_name AUTO_INCREMENT=number;

MySQL 设定起始值只能在建表后使用 ALTER TABLE 语句。也可以在建表后直接使用 ALTER TABLE 语句定义 AUTO_INCREMENT 以及它的起始值。

CREATE TABLE tab_name
(
    column1 INT NOT NULL PRIMARY KEY,
    ...
);
ALTER TABLE tab_test AUTO_INCREMENT=number;
-- number 是任意数字

MS SQL Server 使用 IDENTITY() 来执行 auto-increment 任务：

-- IDENTITY(initial, incremental) 中，
-- initial 是初始值，incremental 是递增值
-- 即，以 initial 作为初始值，每新增一条记录递增 incremental
CREATE TABLE tab_name
(
    column1 INT IDENTITY(initial, incremental) PRIMARY KEY,
    ...
)

插入数据的方法同 MySQL。

Oracle 通过创建 sequence 对象（该对象生成数字序列）创建 auto-increment 字段：

CREATE SEQUENCE sp_name
MINVALUE 1    # 最小值为 1
START WITH 1  # 起始值为 1
INCREMENT BY 1  # 每次递增 1
CACHE 10    # 缓存 10 个值以提高性能

插入新记录时，必须使用 sequence 对象的 nextval 函数（该函数从 sp_name 序列中取回下一个值）：

-- 假设 tab_name 已被创建，
-- 且要对 column1 使用 nextval() 函数
INSERT INTO tab_name ( column1, column2, column3)
VALUES ( sp_name.nextval, val2, val3);

视图

视图是虚拟的表。视图并不包含数据，它只包含使用时动态检索数据的查询。视图的 SELECT 用法与表的用法基本相同。

使用视图的原因：

• 重用 SQL 语句。

• 简化复杂的 SQL 操作（无需知道视图的基本查询细节）。

• 使用表的一部分而不是整个表。

• 保护数据。 可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

• 更改数据格式和表示。 视图可返回与底层表的表示和格式不同的数据。

视图的使用规则：

• 视图名必须为一且不能与其他视图或表重名。

• 对于可创建的视图数目没有限制。

• 创建视图，必须具有足够的访问权限（通常由数据库管理人员授予）。

• 视图可以嵌套，但允许的嵌套层数在不同的 DBMS 中有所不同。 嵌套视图、使用多个联结和过滤创建复杂的视图都会使性能下降。在产品环境中使用之前，应该对其进行全面测试。

• 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。

• 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。

• 视图不能索引，也不能有关联的触发器或默认值。

• 有些 DBMS 把视图作为只读的查询（不能将数据写回底层表，如 SQLite）。

• 有些 DBMS 禁止对视图中的行进行插入或更新后，该行不再属于该视图的操作。 默认情况下，如果删除视图中的某行的某列后，可能会导致该行不属于该视图。但是某些 DBMS 会防止这种情况发生。

CREATE VIEW 创建视图

使用 CREATE VIEW 语句创建视图，通常包含两部分：

• 视图名；

• SELECT 查询语句。

视图的用途：

1. 利用视图简化复杂的联结：

   CREATE VIEW view_name AS
   SELECT filed1, filed2, ...
   FROM table1, table2, ...
   WHERE condition1;
   

2. 用视图重新格式话检索出的数据：

   -- filed1, filed2, ... 指需要重命名的字段
   ALTER VIEW view_name AS
   SELECT filed1 AS alias1, filed2 AS alias2, ...
   FROM tab_name;
   

3. 用视图过滤不想要的数据。

一般创建的视图都不会绑定特定的数据，这会提高视图的可重用性。

从视图检索数据时如果使用了一条 WHERE 子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

DROP VIEW 删除视图

通过 DROP VIEW 删除视图（仅删除视图的定义，视图中并包含真实的数据）：

DROP VIEW view_name;

索引

索引通过排序数据以加快搜索和排序操作的速度。

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。

• 索引数据可能要占用大量的存储空间。

• 用户无法看到索引。

• 并非所有数据都适合做索引。

• 索引用于数据过滤和数据排序。如果经常以某种特定的顺序排序数据，则该数据可能适合做索引。

• 可以在索引中定义多个列。这样的索引仅在以这个列组合排序时有用。

CREATE INDEX 创建索引

索引使用 CREATE INDEX 语句创建（不同的 DBMS 创建索引的语句变化很大）。

创建一个允许使用重复值的索引：

CREATE INDEX index_name
ON tab_name (column1, column2, ...);

创建唯一索引（不允许两个行拥有相同的索引值）：

CREATE INDEX index_name
ON tab_name (column1, column2, ...);

索引必须唯一命名。

DROP INDEX 删除索引

MySQL:

ALTER TABLE tab_name
DROP INDEX index_name;

MS Access：

DROP INDEX index_name ON tab_name;

MS SQL Server：

DROP INDEX tab_name.index_name;

DB2 / Oracle：

DROP INDEX index_name;