简介

• SQL 是用于访问和处理数据库的标准的计算机语言

1、SQL 是什么

• SQL，指结构化查询语言，全称是 Structured Query Language
• SQL 让您可以访问和处理数据库
• SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言

2、SQL 能做什么

• SQL 面向数据库执行查询
• SQL 可从数据库取回数据
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

3、RDBMS

• RDBMS 指关系型数据库管理系统，全称 Relational Database Management System
• RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access
• RDBMS 中的数据存储在被称为表的数据库对象中
• 表是相关的数据项的集合，它由列和行组成

一、示例

use RUNOOB;				# 用于选择数据库
set names utf8;			# 用于设置使用的字符集
SELECT * FROM Websites;	# 读取数据表的信息

• SQL 对大小写不敏感：SELECT 与 select 是相同的

二、一些最重要的 SQL 命令

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

三、SQL SELECT 语句

• SELECT 语句用于从数据库中选取数据
• 结果被存储在一个结果表中，称为结果集

1、SQL SELECT 语法

# 从表中获取列
SELECT column_name,column_name
FROM table_name;

# 从表中获取所有列
SELECT * FROM table_name;

四、SQL SELECT DISTINCT 语句

• SELECT DISTINCT 语句用于返回唯一不同的值
• 在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值
• DISTINCT 关键词用于返回唯一不同的值

1、SQL SELECT DISTINCT 语法

# 从表中列中选取唯一不同的值，也就是去掉列中重复值
SELECT DISTINCT column_name,column_name
FROM table_name;

五、SQL WHERE 子句

• WHERE 子句用于过滤记录，用于提取那些满足指定条件的记录

1、SQL WHERE 语法

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

# 示例：
# 该语句表示从 Websites 表中获取所有列，其中 country='CN' 表示条件 country 等于 'CN'
SELECT * FROM Websites WHERE country = 'CN';

2、文本字段 vs 数值字段

• SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）

• 在上个实例中 'CN' 文本字段使用了单引号

• 如果是数值字段，请不要使用引号

  SELECT * FROM Websites WHERE id = 1;
  

3、WHERE 子句中的运算符

• 下面的运算符可以在 WHERE 子句中使用：

  运算符	描述
  =	等于
  <>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
  >	大于
  <	小于
  >=	大于等于
  <=	小于等于
  BETWEEN	在某个范围内
  LIKE	搜索某种模式
  IN	指定针对某个列的多个可能值
  AND	与
  OR	或
  NOT	非

• 示例：

  /* ********** Where 子句 ********** */
  # 搜索 val 等于 999 的数据：
  SELECT * from MyTable where val = 999;
  
  /* ********** Where + 条件（筛选行） ********** */
  # 比较运算符包含：=、>、<、>=、<=、!=、<>（不等于）
  
  # 搜索 val 小于 999 的数据：
  SELECT * from MyTable where val < 999;
  
  /* ********** 逻辑运算 ********** */
  # 优先级：() > NOT > AND > OR
  # 可组合使用
  
  # 搜索 val 小于 999 大于 123 的数据：
  SELECT * from MyTable where val < 999 AND val > 123;
  # 搜索 val 小于 999 或 index 大于 3 的数据：
  SELECT * from MyTable where val < 999 OR index > 3;
  # 搜索 val 不大于 999 的数据：
  SELECT * from MyTable where NOT val > 999;
  
  /* ********** 特殊条件 ********** */
  # 空值判断：is null
  SELECT * from MyTable where name is null;
  # between and（在之间的值，左闭右闭）
  SELECT * from MyTable where val between 1500 and 3000;
  # IN
  SELECT * from MyTable where val IN (5000,3000,1500);
  # LIKE
  # 查询 MyTable 表中 name 列中有 M 的值，M 为要查询内容中的模糊信息
  # % 表示多个字值，_ 下划线表示一个字符
  # M%：能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的
  # %M%：表示查询包含 M 的所有内容
  # %M_：表示查询以 M 在倒数第二位的所有内容
  SELECT * from MyTable where name LIKE 'M%';
  
  # WHERE 0 和 WHERE 1 中的 0 和 1 会自动转换为 false 和 true
  

六、SQL AND & OR 运算符

• AND & OR 运算符用于基于一个以上的条件对记录进行过滤

• 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录

• 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录

1、示例

# AND：下面的 SQL 语句从 "Websites" 表中选取国家为 "CN" 且 alexa 排名大于 "50" 的所有网站
SELECT * FROM Websites
WHERE country = 'CN'
AND alexa > 50;

# OR：下面的 SQL 语句从 "Websites" 表中选取国家为 "USA" 或者 "CN" 的所有客户
SELECT * FROM Websites
WHERE country = 'USA'
OR country = 'CN';

# 结合 AND & OR（使用圆括号来组成复杂的表达式）
# 下面的 SQL 语句从 "Websites" 表中选取 alexa 排名大于 "15" 且国家为 "CN" 或 "USA" 的所有网站
SELECT * FROM Websites
WHERE alexa > 15
AND (country = 'CN' OR country = 'USA');

七、SQL ORDER BY 关键字

• ORDER BY 关键字用于对结果集进行排序
• ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序
• ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字

1、SQL ORDER BY 语法

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

# 示例：
# 该操作获取所有列并按照 alexa 进行升序排序
SELECT * FROM Websites ORDER BY alexa;
# 下面操作获取所有列并按照 alexa 进行降序排序
SELECT * FROM Websites ORDER BY alexa DESC;
# 下面操作获取所有列并按照 "country" 和 "alexa" 进行升序排序
SELECT * FROM Websites ORDER BY country,alexa;
# 还可以：
SELECT * FROM Websites ORDER BY country DESC,alexa;
SELECT * FROM Websites ORDER BY country,alexa DESC;

八、SQL INSERT INTO 语句

• INSERT INTO 语句用于向表中插入新记录

1、SQL INSERT INTO 语法

# 1. 无需指定要插入数据的列名，只需提供被插入的值即可
INSERT INTO table_name
VALUES (value1,value2,value3,...);

# 2. 需要指定列名及被插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

# 不用向 id 字段插入任何数字，id 列是自动更新的，表中的每条记录都有一个唯一的数字

# 示例：
INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
# 下面的 SQL 语句将插入一个新行，但是只在 "name"、"url" 和 "country" 列插入数据（id 字段会自动更新）
INSERT INTO Websites (name, url, country)
VALUES ('*', 'http://*.com/', 'IND');

九、SQL UPDATE 语句

• UPDATE 语句用于更新表中已存在的记录

1、SQL UPDATE 语法

# WHERE 子句规定哪条记录或者哪些记录需要更新。如果省略了 WHERE 子句，所有的记录都将被更新！
# 执行没有 WHERE 子句的 UPDATE 要慎重，再慎重
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

# 示例：
UPDATE Websites 
SET alexa = '5000', country = 'USA' 
WHERE name = '菜鸟教程';

# 在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在 update 语句后携带 where 条件，否则就会报错
set sql_safe_updates = 1;

十、SQL DELETE 语句

• DELETE 语句用于删除表中的行

1、SQL DELETE 语法

# WHERE 子句规定哪条记录或者哪些记录需要删除。如果省略了 WHERE 子句，所有的记录都将被删除！
DELETE FROM table_name
WHERE some_column = some_value;

# 示例：
DELETE FROM Websites
WHERE name = 'Facebook' AND country = 'USA';

# 删除所有数据：可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变
DELETE FROM table_name;
# 或
DELETE * FROM table_name;

十一、SQL SELECT TOP、LIMIT、ROWNUM 子句

1、SQL SELECT TOP 子句

• SELECT TOP 子句用于规定要返回的记录的数目
• SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的
• 注意：并非所有的数据库系统都支持 SELECT TOP 语句。MySQL 支持 LIMIT 语句来选取指定的条数数据，Oracle 可以使用 ROWNUM 来选取

2、SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;

3、MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

# 示例：
SELECT *
FROM Persons
LIMIT 5;

4、Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

# 示例
SELECT *
FROM Persons
WHERE ROWNUM <= 5;

5、MySQL SELECT LIMIT 实例

# 下面的 SQL 语句从 "Websites" 表中选取头两条记录
SELECT * FROM Websites LIMIT 2;

6、SQL SELECT TOP PERCENT 实例

# 下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录
SELECT TOP 50 PERCENT * FROM Websites;

# 前 5 行
SELECT top 5 * from Websites;

# 后 5 行
# DESC 表示降序排列 ASC 表示升序排列
SELECT top 5 * from Websites order by id DESC;

十二、SQL LIKE 操作符

• LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

1、SQL LIKE 语法

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

2、示例

# 下面的 SQL 语句选取 name 以字母 "G" 开始的所有客户
SELECT * FROM Websites
WHERE name LIKE 'G%';

# 提示："%" 符号用于在模式的前后定义通配符（默认字母）。您将在下一章中学习更多有关通配符的知识

# 下面的 SQL 语句选取 name 以字母 "k" 结尾的所有客户
SELECT * FROM Websites
WHERE name LIKE '%k';

# 下面的 SQL 语句选取 name 包含模式 "oo" 的所有客户
SELECT * FROM Websites
WHERE name LIKE '%oo%';

# 通过使用 NOT 关键字，您可以选取不匹配模式的记录
# 下面的 SQL 语句选取 name 不包含模式 "oo" 的所有客户
SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';

十三、SQL 通配符

• 通配符可用于替代字符串中的任何其他字符

• 在 SQL 中，通配符与 SQL LIKE 操作符一起使用

• SQL 通配符用于搜索表中的数据

• 在 SQL 中，可使用以下通配符

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

1、使用 SQL % 通配符

# 下面的 SQL 语句选取 url 以字母 "https" 开始的所有网站
SELECT * FROM Websites
WHERE url LIKE 'https%';

# 下面的 SQL 语句选取 url 包含模式 "oo" 的所有网站：
SELECT * FROM Websites
WHERE url LIKE '%oo%';

2、使用 SQL _ 通配符

# 下面的 SQL 语句选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户
SELECT * FROM Websites
WHERE name LIKE '_oogle';

# 下面的 SQL 语句选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站
SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

3、使用 SQL [charlist] 通配符

# MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式

# 下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

# 下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站
SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

# 下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

十四、SQL IN 操作符

• IN 操作符允许您在 WHERE 子句中规定多个值

1、SQL IN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

# 示例：
# 下面的 SQL 语句选取 name 为 "Google" 或 "菜鸟教程" 的所有网站
SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

十五、SQL BETWEEN 操作符

• BETWEEN 操作符用于选取介于两个值之间的数据范围内的值
• BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期

1、SQL BETWEEN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

# 示例：
# 下面的 SQL 语句选取 alexa 介于 1 和 20 之间的所有网站
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

# 如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN
SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

# 带有 IN 的 BETWEEN 操作符实例
# 下面的 SQL 语句选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

# 带有文本值的 BETWEEN 操作符实例
# 下面的 SQL 语句选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

# 带有文本值的 NOT BETWEEN 操作符实例
# 下面的 SQL 语句选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站
SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';

# 带有日期值的 BETWEEN 操作符实例
# 下面的 SQL 语句选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

• 请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果
  • 在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段
  • 在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段
  • 在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段
• 因此，请检查您的数据库是如何处理 BETWEEN 操作符

十六、SQL 别名

• 通过使用 SQL，可以为表名称或列名称指定别名
• 基本上，创建别名是为了让列名称的可读性更强

1、列的 SQL 别名语法

SELECT column_name AS alias_name
FROM table_name;

# 示例：
# 下面的 SQL 语句指定了两个别名，一个是 name 列的别名，一个是 country 列的别名。提示：如果列名称包含空格，要求使用双引号或方括号
SELECT name AS n, country AS c
FROM Websites;

# 在下面的 SQL 语句中，把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

2、表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

# 示例：
# 下面的 SQL 语句选取 "百度" 的所访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）
SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id = w.id and w.name = "百度";

# 不带别名的相同的 SQL 语句
SELECT Websites.name, Websites.url, access_log.count, access_log.date
FROM Websites, access_log
WHERE Websites.id = access_log.site_id and Websites.name = "百度";

• 在下面的情况下，使用别名很有用：
  • 在查询中涉及超过一个表
  • 在查询中使用了函数
  • 列名称很长或者可读性差
  • 需要把两个列或者多个列结合在一起

十七、SQL 连接（JOIN）

• SQL join 用于把来自两个或多个表的行结合起来

• LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法：

  # 两个集合 A,B，假设：_*_ 代表 A∩B，*** 代表 A∪B
  # 即 * 代表选择部分，_ 代表未选择部分，左边为 A-B，中间为 A&B，右边为 B-A
  
  # 下面展示 7 种用法：
  # 1. **_:
  SELECT <select_list>
  FROM TableA
  LEFT JOIN TableB
  ON A.key = B.key
  # 2. *__:
  SELECT <select_list>
  FROM TableA
  LEFT JOIN TableB
  ON A.key = B.key
  WHERE B.key IS NULL
  
  # 3. _**:
  SELECT <select_list>
  FROM TableA
  RIGHT JOIN TableB
  ON A.key = B.key
  # 4. __*:
  SELECT <select_list>
  FROM TableA
  RIGHT JOIN TableB
  ON A.key = B.key
  WHERE A.key IS NULL
  
  # 5. _*_:
  SELECT <select_list>
  FROM TableA
  INNER JOIN TableB
  ON A.key = B.key
  
  # 6. ***:
  SELECT <select_list>
  FROM TableA
  FULL OUTER TableB
  ON A.key = B.key
  # 7. *_*:
  SELECT <select_list>
  FROM TableA
  FULL OUTER TableB
  ON A.key = B.key
  WHERE A.key IS NULL OR B.key IS NULL
  

1、SQL JOIN

• SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
• 最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）
• SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行

# 示例：
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id = access_log.site_id;

2、不同的 SQL JOIN

• 在继续讲解实例之前，先列出可以使用的不同的 SQL JOIN 类型：
  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行

十八、SQL INNER JOIN 关键字

• INNER JOIN 关键字在表中存在至少一个匹配时返回行

1、SQL INNER JOIN 语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
# 或
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;
# 注：INNER JOIN 与 JOIN 是相同的

# 示例：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id = access_log.site_id
ORDER BY access_log.count;

十九、SQL LEFT JOIN 关键字

• LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

1、SQL LEFT JOIN 语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
# 或
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
# 注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

# 示例：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
# 注释：LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配

二十、SQL RIGHT JOIN 关键字

• RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL

1、SQL RIGHT JOIN 语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
# 或
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
# 注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN

# 示例：
SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id = websites.id
ORDER BY access_log.count DESC;
# 注释：RIGHT JOIN 关键字从右表（Websites）返回所有的行，即使左表（access_log）中没有匹配

二十一、SQL FULL OUTER JOIN 关键字

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

1、SQL FULL OUTER JOIN 语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

# 示例：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id = access_log.site_id
ORDER BY access_log.count DESC;
# 注释：FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites" 表中没有匹配，也会列出这些行

二十二、SQL UNION 操作符

• SQL UNION 操作符合并两个或多个 SELECT 语句的结果
• UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同

1、SQL UNION 语法

# SQL UNION 语法
# 注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

# SQL UNION ALL 语法
# 注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

# 示例：
# 下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
# 注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值

# 下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的 country（也有重复的值）
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

2、带有 WHERE 的 SQL UNION ALL

# 下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）
SELECT country, name FROM Websites
WHERE country = 'CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country = 'CN'
ORDER BY country;

二十三、SQL SELECT INTO 语句

• 通过 SQL，您可以从一个表复制信息到另一个表

• SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中

• 注意：

  • MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT
  • 当然你可以使用以下语句来拷贝表结构及数据：

  CREATE TABLE 新表
  AS
  SELECT * FROM 旧表
  

1、SQL SELECT INTO 语法

# 复制所有的列插入到新表中
SELECT *
INTO newtable [IN externaldb]
FROM table1;

# 复制希望的列插入到新表中
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

# 提示：新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称

# 示例：
# 创建 Websites 的备份复件
SELECT *
INTO WebsitesBackup2016
FROM Websites;
# 只复制一些列插入到新表中
SELECT name, url
INTO WebsitesBackup2016
FROM Websites;
# 只复制中国的网站插入到新表中
SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country = 'CN';
# 复制多个表中的数据插入到新表中
SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id = access_log.site_id;
# 提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可
SELECT *
INTO newtable
FROM table1
WHERE 1 = 0;

二十四、SQL INSERT INTO SELECT 语句

• 通过 SQL，您可以从一个表复制信息到另一个表
• INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响

1、SQL INSERT INTO SELECT 语法

# 可以从一个表中复制所有的列插入到另一个已存在的表中
INSERT INTO table2
SELECT * FROM table1;

# 可以只复制希望的列插入到另一个已存在的表中
INSERT INTO table2 (column_name(s))
SELECT column_name(s)
FROM table1;

# 示例：
# 复制 "apps" 中的数据插入到 "Websites" 中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;
# 只复 QQ 的 APP 到 "Websites" 中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id = 1;

• select into from 和 insert into select 都是用来复制表
• 两者的主要区别为：select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在

# 1. 复制表结构及其数据
create table table_name_new as select * from table_name_old;

# 2. 只复制表结构
create table table_name_new as select * from table_name_old where 1 = 2;
# 或
create table table_name_new like table_name_old;

# 3. 只复制表数据
# 如果两个表结构一样
insert into table_name_new select * from table_name_old;
# 如果两个表结构不一样
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;

二十五、SQL CREATE DATABASE 语句

• CREATE DATABASE 语句用于创建数据库

1、SQL CREATE DATABASE 语法

CREATE DATABASE dbname;

# 示例：
# 下面的 SQL 语句创建一个名为 "my_db" 的数据库
CREATE DATABASE my_db;

二十六、SQL CREATE TABLE 语句

• CREATE TABLE 语句用于创建数据库中的表
• 表由行和列组成，每个表都必须有个表名

1、SQL CREATE TABLE 语法

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
# column_name 参数规定表中列的名称
# data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）
# size 参数规定表中列的最大长度

# 示例：
# 创建一个名为 "Persons" 的表，包含五列：PersonID、LastName、FirstName、Address 和 City
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
# 提示：可使用 INSERT INTO 语句向空表写入数据

• 提示：如需了解 MS Access、MySQL 和 SQL Server 中可用的数据类型，请访问 数据类型参考手册

二十七、SQL 约束（Constraints）

• SQL 约束用于规定表中的数据规则
• 如果存在违反约束的数据行为，行为会被约束终止
• 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）

1、SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

• 在 SQL 中，我们有如下约束：
  • NOT NULL - 指示某列不能存储 NULL 值
  • UNIQUE - 保证某列的每行必须有唯一的值
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性
  • CHECK - 保证列中的值符合指定的条件
  • DEFAULT - 规定没有给列赋值时的默认值

二十八、SQL NOT NULL 约束

• 在默认的情况下，表的列接受 NULL 值

• NOT NULL 约束强制列不接受 NULL 值

• NOT 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
  );
  

1、添加 NOT NULL 约束

# 在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束如下所示
ALTER TABLE Persons
MODIFY Age int NOT NULL;

2、删除 NOT NULL 约束

# 在一个已创建的表的 "Age" 字段中删除 NOT NULL 约束如下所示
ALTER TABLE Persons
MODIFY Age int NULL;

二十九、SQL UNIQUE 约束

• UNIQUE 约束唯一标识数据库表中的每条记录
• UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
• PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束
• 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

1、CREATE TABLE 时的 SQL 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 约束，请使用下面的 SQL 语法
# 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 时的 SQL UNIQUE 约束

# 当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD UNIQUE (P_Id)

# 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName)

3、撤销 UNIQUE 约束

# 如需撤销 UNIQUE 约束，请使用下面的 SQL
# MySQL
ALTER TABLE Persons
DROP INDEX uc_PersonID
# SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

三十、SQL PRIMARY KEY 约束

• PRIMARY KEY 约束唯一标识数据库表中的每条记录
• 主键必须包含唯一的值
• 主键列不能包含 NULL 值
• 每个表都应该有一个主键，并且每个表只能有一个主键

1、CREATE TABLE 时的 SQL PRIMARY KEY 约束

# 下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 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 约束，并定义多个列的 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)
)
# 注释：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的

2、ALTER TABLE 时的 SQL PRIMARY KEY 约束

# 当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

# 如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

3、撤销 PRIMARY KEY 约束

# 如需撤销 PRIMARY KEY 约束，请使用下面的 SQL
# MySQL
ALTER TABLE Persons
DROP PRIMARY KEY
# SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

三十一、SQL FOREIGN KEY 约束

• 一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY（唯一约束的键）
  • 通过一个实例来解释外键
  • "Persons" 表：P_Id、LastName、FirstName、Address、City
  • "Orders" 表：O_Id、OrderNo、P_Id
  • "Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列
  • "Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY
  • "Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY
  • FOREIGN KEY 约束用于预防破坏表之间连接的行为
  • FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

1、CREATE TABLE 时的 SQL FOREIGN KEY 约束

# 下面的 SQL 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束
# MySQL
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)
)
# 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法
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)
)

2、ALTER TABLE 时的 SQL FOREIGN KEY 约束

# 当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
# 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

3、撤销 FOREIGN KEY 约束

# 如需撤销 FOREIGN KEY 约束，请使用下面的 SQL
# MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
# SQL Server / Oracle / MS Access
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

三十二、SQL CHECK 约束

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

1、CREATE TABLE 时的 SQL CHECK 约束

# 下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 CHECK 约束。CHECK 约束规定 "P_Id" 列必须只包含大于 0 的整数
# MySQL
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id > 0)
)
# SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id > 0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
# 如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法
# 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 chk_Person CHECK (P_Id > 0 AND City = 'Sandnes')
)

2、ALTER TABLE 时的 SQL CHECK 约束

# 当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束，请使用下面的 SQL
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CHECK (P_Id > 0)
# 如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法
# MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id > 0 AND City = 'Sandnes')

3、撤销 CHECK 约束

# 如需撤销 CHECK 约束，请使用下面的 SQL
# MySQL
ALTER TABLE Persons
DROP CHECK chk_Person
# SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

三十三、SQL DEFAULT 约束

• DEFAULT 约束用于向列中插入默认值
• 如果没有规定其他的值，那么会将默认值添加到所有的新记录

1、CREATE TABLE 时的 SQL DEFAULT 约束

# 下面的 SQL 在 "Persons" 表创建时在 "City" 列上创建 DEFAULT 约束
# My SQL / 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 时的 SQL 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
# Oracle
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

3、撤销 DEFAULT 约束

# 如需撤销 DEFAULT 约束，请使用下面的 SQL
# MySQL
ALTER TABLE Persons
ALTER City DROP DEFAULT
# SQL Server / Oracle / MS Access
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

三十四、SQL CREATE INDEX 语句

• CREATE INDEX 语句用于在表中创建索引
• 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据
• 您可以在表中创建索引，以便更加快速高效地查询数据
• 用户无法看到索引，它们只能被用来加速搜索/查询
• 注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

1、SQL CREATE INDEX 语法

# 在表上创建一个简单的索引。允许使用重复的值
CREATE INDEX index_name
ON table_name (column_name);

2、SQL CREATE UNIQUE INDEX 语法

# 在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
# 注释：用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法

3、CREATE INDEX 实例

# 下面的 SQL 语句在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引
CREATE INDEX PIndex
ON Persons (LastName);
# 如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开
CREATE INDEX PIndex
ON Persons (LastName, FirstName);

三十五、SQL DROP 撤销索引、撤销表以及撤销数据库

• 通过使用 DROP 语句，可以轻松地删除索引、表和数据库

1、DROP INDEX 语句

# DROP INDEX 语句用于删除表中的索引
# 用于 MS Access 的 DROP INDEX 语法
DROP INDEX index_name ON table_name;
# 用于 MS SQL Server 的 DROP INDEX 语法
DROP INDEX table_name.index_name;
# 用于 DB2 / Oracle 的 DROP INDEX 语法
DROP INDEX index_name;
# 用于 MySQL 的 DROP INDEX 语法
ALTER TABLE table_name DROP INDEX index_name;

2、DROP TABLE 语句

# DROP TABLE 语句用于删除表
DROP TABLE table_name;

3、DROP DATABASE 语句

# DROP DATABASE 语句用于删除数据库
DROP DATABASE database_name;

4、TRUNCATE TABLE 语句

# 如果仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？
TRUNCATE TABLE table_name;

三十六、SQL ALTER TABLE 语句

• ALTER TABLE 语句用于在已有的表中添加、删除或修改列

1、SQL ALTER TABLE 语法

# 如需在表中添加列，请使用下面的语法
ALTER TABLE table_name
ADD column_name datatype;

# 如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）
ALTER TABLE table_name
DROP COLUMN column_name;

# 要改变表中列的数据类型，请使用下面的语法
# SQL Server / MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatypes;
# My SQL / Oracle
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
# Oracle 10G 之后版本
ALTER TABLE table_name
MODIFY column_name datatype;

2、SQL ALTER TABLE 实例

# 在 "Persons" 表中添加一个名为 "DateOfBirth" 的列
ALTER TABLE Persons
ADD DateOfBirth date;

# 改变 "Persons" 表中 "DateOfBirth" 列的数据类型
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

# 删除 "Person" 表中的 "DateOfBirth" 列
ALTER TABLE Persons
DROP COLUMN DateOfBirth;

三十七、SQL AUTO INCREMENT 字段

• Auto-increment 会在新记录插入表中时生成一个唯一的数字
• 我们通常希望在每次插入新记录时，自动地创建主键字段的值
• 我们可以在表中创建一个 auto-increment 字段

1、用于 MySQL 的语法

# 下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

# 默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1
# 要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法
ALTER TABLE Persons AUTO_INCREMENT = 100;

# 要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

2、用于 SQL Server 的语法

# 下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

# MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务
# 在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1
# 提示：要规定 "ID" 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)
# 要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）

3、用于 Access 的语法

# 下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

# MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务
# 默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1
# 提示：要规定 "ID" 列以 10 起始且递增 5，请把 autoincrement 改为 AUTOINCREMENT(10,5)
# 要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）

4、用于 Oracle 的语法

# 在 Oracle 中，代码稍微复杂一点
# 您必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段
# 请使用下面的 CREATE SEQUENCE 语法
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

# 上面的代码创建一个名为 seq_person 的 sequence 对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值
# 要在 "Persons" 表中插入新记录，我们必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

5、给已经存在的 colume 添加自增语法

ALTER TABLE table_name CHANGE column_name column_name data_type(size) constraint_name AUTO_INCREMENT;
# 示例：
ALTER TABLE student CHANGE id id INT( 11 ) NOT NULL AUTO_INCREMENT;

三十八、SQL 视图（Views）

• 视图是可视化的表
• 本章讲解如何创建、更新和删除视图

1、SQL CREATE VIEW 语句

• 在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表
• 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段
• 您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样

2、SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
# 注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据

3、SQL CREATE VIEW 实例

# 样本数据库 Northwind 拥有一些被默认安装的视图
# 视图 "Current Product List" 会从 "Products" 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued = No;
# 可以像这样查询上面这个视图
SELECT * FROM [Current Product List];

# Northwind 样本数据库的另一个视图会选取 "Products" 表中所有单位价格高于平均单位价格的产品
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
# 可以像这样查询上面这个视图
SELECT * FROM [Products Above Average Price];

# Northwind 样本数据库的另一个视图会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 "Product Sales for 1997" 的视图那里选取数据
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;
# 可以像这样查询上面这个视图
SELECT * FROM [Category Sales For 1997];

# 也可以向查询添加条件。现在，我们仅仅需要查看 "Beverages" 类的销售总数
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = 'Beverages'

4、SQL 更新视图

# 可以使用下面的语法来更新视图
# SQL CREATE OR REPLACE VIEW 语法
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
# 现在，希望向 "Current Product List" 视图添加 "Category" 列。将通过下列 SQL 更新视图
CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

# SQL Server
ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
<view_attribute> ::=
{
	[ ENCRYPTION ]
	[ SCHEMABINDING ]
	[ VIEW_METADATA ]
};
# schema_name: 视图所属架构的名称
# view_name: 要更改的视图
# column: 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）

5、SQL 撤销视图

# 可以通过 DROP VIEW 命令来删除视图
DROP VIEW view_name;

6、视图的作用

• 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系
• 视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
• 从而加强了安全性，使用户只能看到视图所显示的数据
• 视图还可以被嵌套，一个视图中可以嵌套另一个视图

三十九、SQL Date 函数

1、SQL 日期（Dates）

• 当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配
• 只要您的数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，情况就有点复杂了
• 在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数

2、MySQL Date 函数

• 下面的表格列出了 MySQL 中最重要的内建日期函数

  函数	描述
  NOW()	返回当前的日期和时间
  CURDATE()	返回当前的日期
  CURTIME()	返回当前的时间
  DATE()	提取日期或日期/时间表达式的日期部分
  EXTRACT()	返回日期/时间的单独部分
  DATE_ADD()	向日期添加指定的时间间隔
  DATE_SUB()	从日期减去指定的时间间隔
  DATEDIFF()	返回两个日期之间的天数
  DATE_FORMAT()	用不同的格式显示日期/时间

2、SQL Server Date 函数

• 下面的表格列出了 SQL Server 中最重要的内建日期函数

  函数	描述
  GETDATE()	返回当前的日期和时间
  DATEPART()	返回日期/时间的单独部分
  DATEADD()	在日期中添加或减去指定的时间间隔
  DATEDIFF()	返回两个日期之间的时间
  CONVERT()	用不同的格式显示日期/时间

3、SQL Date 数据类型

• 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 - 格式：唯一的数字
• 注释：当您在数据库中创建一个新表时，需要为列选择数据类型

4、SQL 日期处理

• 如果不涉及时间部分，那么我们可以轻松地比较两个日期

# 从表中选取 OrderDate 为 "2008-11-11" 的记录
SELECT * FROM Orders WHERE OrderDate = '2008-11-11';

# 如果表中有时间部分
SELECT * FROM Orders WHERE OrderDate = '2008-11-11'
# 或
SELECT * FROM Orders WHERE OrderDate = '2008-11-11 00：00：00'
# 那么我们将得不到结果！因为表中没有 "2008-11-11 00:00:00" 日期
# 如果没有时间部分，默认时间为 00:00:00

• 提示：如果希望使查询简单且更易维护，那么请不要在日期中使用时间部分

四十、SQL NULL 值

• NULL 值代表遗漏的未知数据
• 默认地，表的列可以存放 NULL 值
• 本章讲解 IS NULL 和 IS NOT NULL 操作符

1、SQL NULL 值

• 如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存
• NULL 值的处理方式与其他值不同
• NULL 用作未知的或不适用的值的占位符
• 注释：无法比较 NULL 和 0，它们是不等价的

2、SQL 的 NULL 值处理

• 如何测试 NULL 值呢？
• 无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>
• 必须使用 IS NULL 和 IS NOT NULL 操作符

3、SQL IS NULL

• 如何仅仅选取在 "Address" 列中带有 NULL 值的记录呢

  # 必须使用 IS NULL 操作符
  SELECT LastName,FirstName,Address FROM Persons
  WHERE Address IS NULL;
  # 提示：请始终使用 IS NULL 来查找 NULL 值
  

4、SQL IS NOT NULL

• 如何仅仅选取在 "Address" 列中不带有 NULL 值的记录呢

  # 必须使用 IS NOT NULL 操作符
  SELECT LastName,FirstName,Address FROM Persons
  WHERE Address IS NOT NULL;
  

四十一、SQL NULL 函数

1、SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

# 假如 "UnitsOnOrder" 是可选的，而且可以包含 NULL 值
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products;
# 在上面的实例中，如果有 "UnitsOnOrder" 值是 NULL，那么结果是 NULL

# 微软的 ISNULL() 函数用于规定如何处理 NULL 值
# NVL()、IFNULL() 和 COALESCE() 函数也可以达到相同的结果

# 在这里，希望 NULL 值为 0
# 下面，如果 "UnitsOnOrder" 是 NULL，则不会影响计算，因为如果值是 NULL 则 ISNULL() 返回 0
# SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products;
# Oracle 没有 ISNULL() 函数。不过，可以使用 NVL() 函数达到相同的结果
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products;
# MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同
# 在 MySQL 中，可以使用 IFNULL() 函数，如下所示
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products;
# 或者可以使用 COALESCE() 函数，如下所示
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products;

四十二、SQL 通用数据类型

• 数据库表中的每个列都要求有名称和数据类型
• SQL 开发人员必须在创建 SQL 表时决定表中的每个列将要存储的数据的类型。数据类型是一个标签，是便于 SQL 了解每个列期望存储什么类型的数据的指南，它也标识了 SQL 如何与存储的数据进行交互
• 注释：在不同的数据库中，同一种数据类型可能有不同的名称。即使名称相同，尺寸和其他细节也可能不同！ 请总是检查文档！
• 参考手册：SQL 通用数据类型

四十三、SQL 用于各种数据库的数据类型

• Microsoft Access、MySQL 和 SQL Server 所使用的数据类型和范围
• 参考手册：SQL数据类型

四十四、SQL 函数

• SQL 拥有很多可用于计数和计算的内建函数

1、SQL Aggregate 函数

• SQL Aggregate 函数计算从列中取得的值，返回一个单一的值
• 有用的 Aggregate 函数：
  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和

2、SQL Scalar 函数

• SQL Scalar 函数基于输入值，返回一个单一的值
• 有用的 Scalar 函数：
  • UCASE() - 将某个字段转换为大写
  • LCASE() - 将某个字段转换为小写
  • MID() - 从某个文本字段提取字符，MySql 中使用
  • SubString(字段，1，end) - 从某个文本字段提取字符
  • LEN() - 返回某个文本字段的长度
  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入
  • NOW() - 返回当前的系统日期和时间
  • FORMAT() - 格式化某个字段的显示方式

四十五、SQL AVG() 函数

• AVG() 函数返回数值列的平均值

1、SQL AVG() 语法

SELECT AVG(column_name) FROM table_name;

# 示例：
# 下面的 SQL 语句从 "access_log" 表的 "count" 列获取平均值
SELECT AVG(count) AS CountAverage FROM access_log;
# 下面的 SQL 语句选择访问量高于平均访问量的 "site_id" 和 "count"
SELECT site_id,count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);

四十六、SQL COUNT() 函数

• COUNT() 函数返回匹配指定条件的行数

1、SQL COUNT(column_name) 语法

# COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT(column_name) FROM table_name;

# 示例：
# 下面的 SQL 语句计算 "access_log" 表中 "site_id" = 3 的总访问量
SELECT COUNT(count) AS nums FROM access_log
WHERE site_id = 3;

2、SQL COUNT(*) 语法

# COUNT(*) 函数返回表中的记录数
SELECT COUNT(*) FROM table_name;

# 示例：
# 下面的 SQL 语句计算 "access_log" 表中总记录数
SELECT COUNT(*) AS nums FROM access_log;

3、SQL COUNT(DISTINCT column_name) 语法

# COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name;
# 注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access

# 示例：
# 下面的 SQL 语句计算 "access_log" 表中不同 site_id 的记录数
SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

四十七、SQL FIRST() 函数

• FIRST() 函数返回指定的列中第一个记录的值

1、SQL FIRST() 语法

# SQL FIRST() 语法
SELECT FIRST(column_name) FROM table_name;
# 注释：只有 MS Access 支持 FIRST() 函数

2、SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

# 示例：
SELECT TOP 1 name FROM Websites
ORDER BY id ASC;

3、MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

# 示例：
SELECT name FROM Websites
ORDER BY id ASC
LIMIT 1;

4、Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <= 1;

# 示例：
SELECT name FROM Websites
ORDER BY id ASC
WHERE ROWNUM <= 1;

四十八、SQL LAST() 函数

• LAST() 函数返回指定的列中最后一个记录的值

1、SQL LAST() 语法

SELECT LAST(column_name) FROM table_name;
# 注释：只有 MS Access 支持 LAST() 函数

2、SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

# 示例：
SELECT TOP 1 name FROM Websites
ORDER BY id DESC;

3、MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

# 示例：
SELECT name FROM Websites
ORDER BY id DESC
LIMIT 1;

4、Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <= 1;

# 示例：
SELECT name FROM Websites
ORDER BY id DESC
WHERE ROWNUM <= 1;

四十九、SQL MAX() 函数

• MAX() 函数返回指定列的最大值

1、SQL MAX() 语法

SELECT MAX(column_name) FROM table_name;

# 示例：
SELECT MAX(alexa) AS max_alexa FROM Websites;

五十、SQL MIN() 函数

• MIN() 函数返回指定列的最小值

1、SQL MIN() 语法

SELECT MIN(column_name) FROM table_name;

# 示例：
SELECT MIN(alexa) AS min_alexa FROM Websites;

五十一、SQL SUM() 函数

• SUM() 函数返回数值列的总数

1、SQL SUM() 语法

SELECT SUM(column_name) FROM table_name;

# 示例：
SELECT SUM(count) AS nums FROM access_log;

五十二、SQL GROUP BY 语句

• GROUP BY 语句可结合一些聚合函数来使用
• GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组

1、SQL GROUP BY 语法

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

# 示例：
# 统计 access_log 各个 site_id 的访问量
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

2、SQL GROUP BY 多表连接

• 下面的 SQL 语句统计有记录的网站的记录数量

# 下面的 SQL 语句统计有记录的网站的记录数量
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id = Websites.id
GROUP BY Websites.name;

五十三、SQL HAVING 子句

• 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
• HAVING 子句可以让我们筛选分组后的各组数据

1、SQL 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;

# 示例：
# 查找总访问量大于 200 的网站
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id = Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
# 查找总访问量大于 200 的网站，并且 alexa 排名小于 200
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id = access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

五十四、SQL EXISTS 运算符

• EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

1、SQL EXISTS 语法

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

# 示例：
# 查找总访问量（count 字段）大于 200 的网站是否存在
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);
# EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

五十五、SQL UCASE() 函数

• UCASE() 函数把字段的值转换为大写

1、SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name;

# 用于 SQL Server 的语法
SELECT UPPER(column_name) FROM table_name;

# 示例：
# 下面的 SQL 语句从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为大写
SELECT UCASE(name) AS site_title, url
FROM Websites;

五十六、SQL LCASE() 函数

• LCASE() 函数把字段的值转换为小写

1、SQL LCASE() 语法

SELECT LCASE(column_name) FROM table_name;

# 用于 SQL Server 的语法
SELECT LOWER(column_name) FROM table_name;

# 示例：
# 下面的 SQL 语句从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为小写
SELECT LCASE(name) AS site_title, url
FROM Websites;

五十七、SQL MID() 函数

• MID() 函数用于从文本字段中提取字符

1、SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name;
# column_name:	必需。要提取字符的字段
# start:		必需。规定开始位置（起始值是 1）
# length:		可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本

# 示例：
# 下面的 SQL 语句从 "Websites" 表的 "name" 列中提取前 4 个字符
SELECT MID(name,1,4) AS ShortTitle
FROM Websites;

五十八、SQL LEN() 函数

• LEN() 函数返回文本字段中值的长度

1、SQL LEN() 语法

SELECT LEN(column_name) FROM table_name;

# MySQL 中函数为 LENGTH()
SELECT LENGTH(column_name) FROM table_name;

# 示例：
# 下面的 SQL 语句从 "Websites" 表中选取 "name" 和 "url" 列中值的长度
SELECT name, LENGTH(url) as LengthOfURL
FROM Websites;

五十九、SQL ROUND() 函数

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

1、SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name;
# column_name:	必需。要舍入的字段
# decimals:		必需。规定要返回的小数位数

# 示例：
# ROUND(X)：返回参数X的四舍五入的一个整数
mysql> select ROUND(-1.23);
		-> -1
mysql> select ROUND(-1.58);
		-> -2
mysql> select ROUND(1.58);
		-> 2
# ROUND(X,D)：返回参数 X 的四舍五入的有 D 位小数的一个数字。如果 D 为 0，结果将没有小数点或小数部分
mysql> select ROUND(1.298, 1);
		-> 1.3
mysql> select ROUND(1.298, 0);
		-> 1

六十、SQL NOW() 函数

• NOW() 函数返回当前系统的日期和时间

1、SQL NOW() 语法

SELECT NOW() FROM table_name;

# 示例：
# 下面的 SQL 语句从 "Websites" 表中选取 name，url，及当天日期
SELECT name, url, Now() AS date
FROM Websites;

六十一、SQL FORMAT() 函数

• FORMAT() 函数用于对字段的显示进行格式化

1、SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name;
# column_name:	必需。要格式化的字段
# format:		必需。规定格式

# 示例：
# 下面的 SQL 语句从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

六十二、SQL 快速参考

• 参考手册：SQL 快速参考

• mysql -u root -p