《SQL 必知必会》笔记

2020-01-29 · views: loading...

《SQL 必知必会》笔记

1 - 了解 SQL
- 数据库
- 表
- 列
  - 主键：
2 - 检索数据
- 查询语句 SELECT
- 注释
- 结果集去重 DISTINCT
- 限制结果 LIMIT
3 - 排序检索数据
- 排序数据 ORDER BY
- 按多个列排序
- 降序排列
4 - 过滤数据
- WHERE
- 操作符
5 - 高级数据过滤
- AND OR
- IN
- NOT
6 - 用通配符进行过滤
- LIKE
- % 通配符
- _ 通配符
- 注意
7 - 创建计算字段
- 拼接字段
- AS 别名
- 计算字段
8 - 使用函数处理数据
- 函数
9 - 汇总数据
- 聚集函数
- 聚集不同值
10 - 分组数据
- GROUP BY 分组
- HAVING 过滤分组
  - WHERE 与 HAVING 的差别：
- ORDER BY 分组和排序
- SELECT 子句书写顺序
11 - 使用子查询
- 利用子查询进行过滤
- 作为计算字段使用子查询
  - 1：从 Customers 表中检索顾客列表；
  - 2：对于检索出的每个顾客，统计其在 Orders 表中的订单数目
12 - 连接表
- 连接
- 内连接（inner join）
- MySQL 中的多种写法：
- 笛卡尔积：（叉连接）（cross join）
- 连接多个表
13 - 创建高级连接
- 使用表别名
- 自连接（self-join）
- 自然连接（natural join）
- 外连接（outer join）
- 全外连接（full outer join）
14 - 组合查询
- 组合查询（ UNION）
- 创建组合查询
  - 包含或取消重复的行：
  - 排序：
- UNION 规则
15 - 插入数据
- 数据插入
- 插入完整的行：（MySQL 中可以省略 INTO）
- 更安全的方法 or 插入部分字段：
- 一次插入多行数据
- 插入检索出的数据
- 从一个表复制到另一个表
16 - 更新和删除数据
- 更新数据（UPDATE）
- 删除数据（DELETE）
17 - 创建和操纵表
- 创建表：
- 删除表：
- 添加列：
- 修改列类型：
- 修改列名：
- 删除列
18 - 使用视图
- 为什么使用视图
- 视图的规则和限制
- 使用视图
19 - 使用存储过程
20 - 管理事务处理
21 - 使用游标
22 - 高级 SQL 特性
- 约束
- 索引
- 触发器
- 数据库安全

《SQL 必知必会》第 4 版

豆瓣：https://book.douban.com/subject/24250054/

SQL 文件下载地址：https://forta.com/books/0672336073/

1 - 了解 SQL

数据库

保存有组织的数据的容器（通常是一个文件或多个文件）。

数据库软件应称为数据库管理系统（DBMS）.

表

某种特定类型数据的结构化清单。

列

表中的一个子段。所有表都是由一个或多个列组成的。

主键：

表中的任何列都可以作为主键，只要它满足以下条件：

任意两行都不具备有相同的主键值；
每一行都必须具有一个主键值（主键列不允许 NULL 值）；
主键列中的值不允许修改或更新；
主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。

2 - 检索数据

查询语句 `SELECT`

检索一个或多个列。

SELECT 列名 FROM 表名;
SELECT * FROM 表名;

注释

在大多数数据库软件中，支持 # -- /**/ 等单行和多行注释。

结果集去重 `DISTINCT`

SELECT DISTINCT 列名 
FROM 表名;

DISTINCT 关键字限制所有的列，如果后接多列，除非两列内容完全相同，否则都会被纳入结果集。

限制结果 `LIMIT`

大多用于分页显示：

-- 从第 0 行开始，显示 5 个。第一页：
SELECT * FROM 表名 LIMIT 5 OFFSET 0;
-- 从第 5 行开始，现实 5 个。第二页：
SELECT * FROM 表名 LIMIT 5 OFFSET 5;

MySQL、MariaDB、SQLite 简化语法：

# 从 n 起，取 m 行。 n 可省略，默认为 0。
SELECT 列1, 列2 FROM 表 LIMIT n, m; 

-- 示例：
-- 第一页：
SELECT * FROM 表名 LIMIT 0, 5;
-- 第二页：
SELECT * FROM 表名 LIMIT 5, 5;
-- 第三页
SELECT * FROM 表名 LIMIT 10, 5;

3 - 排序检索数据

ASC 默认，升序，全称 ascending。

DESC 降序，全称 descending。

排序数据 `ORDER BY`

SELECT 列名 
FROM 表名
ORDER BY 列名;

ORDER BY 子句中用非检索的列排序也是合法的。

按多个列排序

写在前面的优先级高。

SELECT 列名 
FROM 表名
ORDER BY 列名1, 列名2;

按列位置排序，可以使用列名的书写位置，不推荐：

SELECT 列名1, 列名2, 列名3
FROM 表名
ORDER BY 2, 3;

降序排列

ORDER BY 默认为升序排列。（从上至下，越来越大（0→100，a→z））

DESC 只对前面那一列生效，如果想多列使用降序排序，必须对每一列指定 DESC 关键字。

示例：（prod_price 列降序排序，prod_name 仍按升序排序）

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name

4 - 过滤数据

`WHERE`

SELECT 列名
FROM 表名
WHERE 条件;

操作符

常见操作符：< > = <= >= != 。。。

BTEWEEN 操作符，左闭右闭。

IS NULL

5 - 高级数据过滤

`AND` `OR`

AND 优先级大于 OR。

使用小括号，不依赖优先级顺序，可以消除歧义。

`IN`

指定范围，集合中的每个条件都可以进行匹配。

SELECT * 
FROM 表名
WHERE xxx_id IN (no1, no2);

加强版的 OR，使逻辑更清晰。

`NOT`

否定紧跟的条件。

6 - 用通配符进行过滤

`LIKE`

LIKE 指示 DBMS 后跟的搜索模式利用通配符而不是简单的相等匹配进行比较。

`%` 通配符

% 能匹配 0 个、1 个或多个字符。

示例：

SELECT * FROM Products WHERE prod_name LIKE "fish%";

`_` 通配符

_ 总是刚好匹配一个字符，不能多也不能少。

示例：（与 % 的区别与对比）

SELECT prod_name FROM Products WHERE prod_name LIKE "% inch teddy bear";
-- 8 inch teddy bear
-- 12 inch teddy bear
-- 18 inch teddy bear

SELECT prod_name FROM Products WHERE prod_name LIKE "__ inch teddy bear"; -- 两个下划线
-- 12 inch teddy bear
-- 18 inch teddy bear

SELECT prod_name FROM Products WHERE prod_name LIKE "_ inch teddy bear"; -- 一个下划线
-- 8 inch teddy bear

注意

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

7 - 创建计算字段

拼接字段

MySQL 和 MariaSQL 使用 CONCAT() 函数拼接字符串；

使用 TRIM()、LTRIM()、RTRIM 去掉字符串两边、左、右的空格。

示例：

SELECT
	CONCAT(
		TRIM( vend_name ),
		'\t\t(',
		TRIM( vend_country ),
		')' 
	) 
FROM
	Vendors;

`AS` 别名

计算字段

对检索出的数据进行算术计算：

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems 
WHERE order_num = 20008;

8 - 使用函数处理数据

函数

文本处理函数
日期和时间处理函数
数值处理函数

需要参考相应 DBMS 文档。

9 - 汇总数据

聚集函数

对某些行运行的函数，计算并返回一个值。

`AVG()` 平均值

只适用于单列，忽略值为 NULL 的行。

`COUNT()` 计数

COUNT(*) 对表中行的数目进行计算，不管表列中是否包含 NULL 值。

COUNT(列名) 会忽略值为 NULL 的行。

`MAX()` 最大值

`MIN()` 最小值

`SUM()` 求和

聚集不同值

对以上 5 个聚集函数都可以使用如下参数：

ALL 默认，对所有行执行计算。
DISTINCT 只包含不同的值。（不能用于 COUNT(*)，只能用于列名。）

例：

SELECT AVG(DISTINCT salary) FROM employee;

会忽略工资相同的员工工资，来计算平均值。

10 - 分组数据

`GROUP BY` 分组

示例：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

-- vend_id	num_prods
-- BRS01	3
-- DLL01	4
-- FNG01	2

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
- 如果嵌套了分组，数据将在最后指定的分组上进行汇总。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 只，它们将分为一组。

`HAVING` 过滤分组

示例：（列出具有 2 个以上产品且其价格大于等于 4 的供应商）

SELECT vend_id, COUNT(*)
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

-- vend_id	COUNT(*)
-- BRS01	3
-- FNG01	2

HAVING 支持所有 WHERE 的操作符。

紧接 GROUP BY，不能单独存在。

聚合函数只能与具体数据做条件判断，不能与字段做条件判断。（可用表连接实现）

`WHERE` 与 `HAVING` 的差别：

WHERE 不能使用聚合函数，HAVING 可以在分组后使用聚合函数。

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

所以 WHERE 排除的行不包括在分组中，这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

`ORDER BY` 分组和排序

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这时保证数据正确排序的唯一方法。千万不要尽依赖 GROUP BY 排序数据。

`SELECT` 子句书写顺序

子句	说明	是否必须使用
`SELECT`	要返回的列或表达式	是
`FROM`	从中检索数据的表	仅在从表选择数据时使用
`WHERE`	行级过滤	否
`GROUP BY`	组级过滤	仅在按组计算聚集时使用
`HAVING`	组级过滤	否
`ORDER BY`	输出排序顺序	否

11 - 使用子查询

利用子查询进行过滤

作为子查询的 SELECT 语句只能查询单个列。

示例，列出订购物品 RGAN01 的所有顾客：

1：检索包含物品 RGAN01 的所有订单的编号

SELECT order_num
FROM OrderIterms
WHERE prod_id = 'RGAN01';

2：检索具有前一步骤列出的订单编号的所有顾客的 ID

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderIterms
                    WHERE prod_id = 'RGAN01');

3：检索前一步骤返回的所有顾客 ID 的顾客信息

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderIterms
                                      WHERE prod_id = 'RGAN01'));

内连接写法：（第 12 课）

-- 列出订购物品 RGAN01 的所有顾客：
SELECT
	cust_name,
	cust_contact 
FROM
	OrderItems,
	Orders,
	Customers 
WHERE
	OrderItems.prod_id = 'RGAN01' 
	AND OrderItems.order_num = Orders.order_num 
	AND Orders.cust_id = Customers.cust_id;

作为计算字段使用子查询

示例，现实 Customers 表中每个顾客的订单总数：

1：从 `Customers` 表中检索顾客列表；

SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id = '1000000001';

2：对于检索出的每个顾客，统计其在 `Orders` 表中的订单数目

SELECT cust_name, 
       cust_state,
	   (SELECT COUNT(*)
		FROM Orders
		WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

12 - 连接表

连接

为什么使用连接？

如果数据存储在多个表中，怎么用一条 SELECT 语句就检索出数据呢？

答案是使用连接。

内连接（inner join）

内连接，又称等值连接（equijoin），它基于两个表之间的相等测试。

示例：

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

MySQL 中的多种写法：

SELECT ... FROM 表1, 表2 WHERE 连接条件;
SELECT ... FROM 表1 [INNER] JOIN 表2 ON 连接条件;
SELECT ... FROM 表1 [INNER] JOIN 表2 WHERE 连接条件; （在使用外连接时，使用 WHERE 会出问题）

笛卡尔积：（叉连接）（cross join）

没有连接条件的表关系返回的结果为笛卡尔积，检索出的行数将是第一个表中的行数乘以第二个表中的行数。

连接多个表

-- 显示订单 20007 中的物品。
SELECT
	prod_name,
	vend_name,
	prod_price,
	quantity 
FROM
	OrderItems,
	Products,
	Vendors 
WHERE
	order_num = 20007 
	AND OrderItems.prod_id = Products.prod_id 
	AND Products.vend_id = Vendors.vend_id;

-- 列出订购物品 RGAN01 的所有顾客：
SELECT
	cust_name,
	cust_contact 
FROM
	OrderItems,
	Orders,
	Customers 
WHERE
	OrderItems.prod_id = 'RGAN01' 
	AND OrderItems.order_num = Orders.order_num 
	AND Orders.cust_id = Customers.cust_id;

13 - 创建高级连接

使用表别名

除了之前的给列起别名，也可以在 FROM 子句中给表起别名，以供在 SELECT、WHERE 子句中使用。

可以缩短 SQL 语句。允许在一条 SELECT 语句中方便多次使用相同的表。

自连接（self-join）

内连接的一种，连接的是同一个表。

示例：

-- 给 Jim Jones 同一公司的所有顾客发送一封信件。
SELECT c1.cust_name, c1.cust_contact, c1.cust_email
FROM Customers AS c1, Customers AS c2
WHERE c2.cust_contact = 'Jim Jones'
AND c1.cust_name = c2.cust_name;
-- cust_name	cust_contact		cust_email
-- Fun4All		Jim Jones			jjones@fun4all.com
-- Fun4All		Denise L. Stephens	dstephens@fun4all.com

自连接比子查询性能更好。

自然连接（natural join）

自然连接排除多次出现，使每一列只返回一次。

SELECT * FROM Customers NATURAL JOIN Orders;
-- 两个表中都有 cust_id 列，但只返回了一个。

*感觉没什么用啊。。。除非真的要查询这么多字段。

外连接（outer join）

内连接只返回符合条件的记录，外连接还返回不符合条件的记录。
左外连接即保留左表的所有记录，与右表连接；
右外连接即保留右表的所有记录，与左表连接。
左外连接（left outer join）与右外连接（right outer join）可以互相转换。（SQLite 即便不支持右外连接，也可以用左外连接代替）

内外连接的区别，示例：（查询每个顾客的订单计数，外连接可以查询出尚未下过订单的顾客）

-- 内连接，只查询出条件关联的行
-- 查询不到顾客 1000000002，因为他没有下过订单
SELECT Customers.cust_id, Orders.order_num
FROM Customers JOIN Orders
ON Customers.cust_id = Orders.cust_id;
-- cust_id		order_num
-- 1000000001	20005
-- 1000000001	20009
-- 1000000003	20006
-- 1000000004	20007
-- 1000000005	20008

-- 外连接，保留顾客表中所有顾客的记录，去外连接订单表
-- 可以查询到 1000000002
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT JOIN Orders
ON Customers.cust_id = Orders.cust_id;
-- cust_id		order_num
-- 1000000001	20005
-- 1000000001	20009
-- 1000000002	(NULL)
-- 1000000003	20006
-- 1000000004	20007
-- 1000000005	20008

-- 完善：外连接+分组计数
-- 带聚集函数的连接
SELECT Customers.cust_id, COUNT(Orders.order_num)
FROM Customers LEFT JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
-- cust_id		COUNT(Orders.order_num)
-- 1000000001	2
-- 1000000002	0
-- 1000000003	1
-- 1000000004	1
-- 1000000005	1

其他外连接示例：

-- 列出所有产品以及订购数量，包括没有人订购的产品
SELECT Products.prod_id, SUM(IFNULL(OrderItems.quantity,0))
FROM Products LEFT JOIN OrderItems
ON Products.prod_id = OrderItems.prod_id
GROUP BY Products.prod_id;

全外连接（full outer join）

MySQL、MariaSQL、SQLite、Access、Open Office Base 不支持全外连接。

全外连接检索两个表中的所有行并关联那些可以关联的行。

14 - 组合查询

组合查询（ `UNION`）

SQL 允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果返回。

主要有两种情况需要使用组合查询：

在一个查询中从不同的表返回结果数据；
对一个表执行多个查询，按一个查询返回数据。

提示：多数情况下，组合相同表的两个查询，通过具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询。

创建组合查询

示例：（需要 Illinois、Indiana、Michigan 等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的 Fun4All）

单条语句 1：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');
-- cust_name		cust_contact	cust_email
-- Village Toys		John Smith		sales@villagetoys.com
-- Fun4All			Jim Jones		jjones@fun4all.com
-- The Toy Store	Kim Howard		(NULL)

单条语句 2：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
-- cust_name	cust_contact		cust_email
-- Fun4All		Jim Jones			jjones@fun4all.com
-- Fun4All		Denise L. Stephens	dstephens@fun4all.com

UNION 组合：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
-- cust_name		cust_contact			cust_email
-- Village Toys		John Smith				sales@villagetoys.com
-- Fun4All			Jim Jones				jjones@fun4all.com
-- The Toy Store	Kim Howard				(NULL)
-- Fun4All			Denise 	L. Stephens		dstephens@fun4all.com

使用 WHERE，效果相同：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
OR cust_name = 'Fun4All';

包含或取消重复的行：

上面例子中，Fun4All Jim Jones jjones@fun4all.com 这一行在两个 SELECT 语句中均被查询出，但 UNION 取消了重复的行。

UNION ALL 可以让每个条件的匹配行全部出现。

排序：

使用 UNION 组合查询时，只能使用一条 ORDER BY 子句进行排序，它必须位于最后一条 SELECT 语句之后。

UNION 规则

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分割。
UNION 中的每个查询必须包含相同数量的列、表达式或聚集函数。
列数据类型必须兼容：类型不必完全相同但必须兼容（如不同长度的字符串）

15 - 插入数据

数据插入

INTERT 语句：用来将行插入（或添加）到数据库表，插入有几种方式：

插入完整的行；
插入行的一部分；
插入某些查询的结果。

插入完整的行：（MySQL 中可以省略 `INTO`）

INSERT INTO Customers
VALUES('1000000006',
		'Toy Land',
		'123 Any Street',
		'New York',
		'NY',
		'11111',
		'USA',
		NULL,
		NULL);

这种插入方法必须完整的按顺序提供列名所对应的数据。

由于较为依赖于表中列的定义次序，在表结构变动后会变得不安全或更改更麻烦。

更安全的方法 or 插入部分字段：

INSERT INTO Customers(cust_id,
					  cust_name,
					  cust_address,
					  cust_city,
					  cust_state,
					  cust_zip,
					  cust_country,
					  cust_contact,
					  cust_email)
VALUES('1000000006',
		'Toy Land',
		'123 Any Street',
		'New York',
		'NY',
		'11111',
		'USA',
		NULL,
		NULL);

这样 DBMS 将用 VALUES 列表中的值按次序一一对应填入列表中的对应项。

也可以省略部分字段，只要这些省略的字段允许 NULL 值或提供默认值。

一次插入多行数据

# 单条数据
INSERT INTO 表名[(字段1, 字段2...)] VALUES(值1, 值2...);
# 多条数据
INSERT INTO 表名[(字段1, 字段2...)] VALUES(值1, 值2...), (值1, 值2...) ...;

插入检索出的数据

INSERT SELECT 语句可以插入检索出的行：

INSERT INTO Customers(cust_id,
					  cust_name,
					  cust_address,
					  cust_city,
					  cust_state,
					  cust_zip,
					  cust_country,
					  cust_contact,
					  cust_email)
SELECT cust_id,
	   cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email
FROM CustNew;

插入行数取决于 SELECT 语句查询出多少行。

DBMS 不在乎 SELECT 返回的列名，会将查询出的字段按 INSERT 语句给出的次序一一对应。

从一个表复制到另一个表

SQL 语法：

SELECT *
INTO CustCopy
FROM Customers;

MySQL、MariaDB、SQLite、Oracle、PostgreSQL 语法：

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

message:

There is no primary key here. Update will only use exact matching of the old values of the columns here. Thus, it may update more than one record.

*MySQL 在复制时没有复制主键约束。

16 - 更新和删除数据

在更新和删除数据时，如果不指定 WHERE 条件，将会更新或删除所有行。

更新数据（`UPDATE`）

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

要更新的表；
列名和它们的新值；
确定要更新哪些行的过滤条件。

更新一列：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

更新多列：

UPDATE Customers
SET cust_contact = 'Sam Roberts',
	cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

在 `UPDATE` 语句中进行表连接

# 把ALLEN调往RESEARCH部门,职务调整为ANALYST
UPDATE t_emp e 
JOIN t_dept d ON e.ename = 'ALLEN' AND d.dname = 'RESEARCH'
SET e.deptno = d.deptno, e.job = 'ANALYST', d.loc = '北京';

支持内连接另一个结果集：

# 把底薪低于公司平均底薪的员工,底薪增加150元
UPDATE t_emp e
JOIN (SELECT AVG(sal) AS avg FROM t_emp) e2 ON e.sal < e2.avg
SET sal = sal + 150;

同样支持外连接。

删除数据（`DELETE`）

关于删除所有的行：

不指定 WHERE 条件时，DELETE 将会删除所有的行，但如果你明确要这么做，使用 TRUNCATE TABLE 可以更快的删除整个表的数据（因为不记录数据的变动（没有事务））。

删除行：

DELETE FROM Customers
WHERE cust_id = '1000000006';

同样支持表连接：

DELETE 表1, ...
FROM 表1 JOIN 表2 ON 条件
[WHERE 条件 ...];

可以一次性删除多个表的内容：

# 删除SALES部门和该部门的全部员工记录
DELETE e,d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES";

同样支持外连接。

17 - 创建和操纵表

创建表：

CREATE TABLE 表名 (
    列名1 数据类型 [约束] [COMMENT],
    列名2 数据类型 [约束] [COMMENT],
    ...;
)[COMMENT = 注释];

删除表：

# 删除
DROP TABLE 表名;
# 清空
TRUNCATE TABLE 表名;

添加列：

ALTER TABLE 表名
ADD 列名 数据类型 [约束] [COMMENT],
ADD 列名 数据类型 [约束] [COMMENT],
...;

修改列类型：

ALTER TABLE	表名
MODIFY 列名 数据类型 [约束] [COMMENT],
MODIFY 列名 数据类型 [约束] [COMMENT],
...;

修改列名：

ALTER TABLE 表名
CHANGE 列名 新列名 数据类型 [约束] [COMMENT],
CHANGE 列名 新列名 数据类型 [约束] [COMMENT],
...;

删除列

ALTER TABLE 表名
DROP 列1,
DROP 列2,
...;

18 - 使用视图

视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

为什么使用视图

重用 SQL 语句；
简化复杂的 SQL 操作。在编写查询后，可以方便的重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表；
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据。

视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

性能问题：每次使用视图时，都必须处理查询执行时需要的所有检索。检索过于复杂，性能可能会下降的很厉害。

视图的规则和限制

视图必须唯一命名（视图与表也不能重名）；
视图不能索引，也不能有关联的触发器或默认值。
。。。

使用视图

-- 创建视图
CREATE VIEW 视图名 AS
SELECT ...;

-- 删除视图
DROP VIEW 视图名;

视图重命名：覆盖或更新视图，必须先删除它，然后再重新创建。

示例：（利用视图简化复杂的连接）

-- 查询已订购任意产品的所有顾客的列表
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num;

-- 为上面的 SQL 语句创建视图
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num;

-- 使用视图
SELECT * FROM ProductCustomers;

-- 删除视图
DROP VIEW ProductCustomers;

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

示例：

-- 用 ProductCustomers 视图查询订购了 RGAN01 的顾客
SELECT * FROM ProductCustomers
WHERE prod_id = 'RGAN01';

19 - 使用存储过程

一组可编程的函数，是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。

优点(为什么要用存储过程？)：

将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

批量处理：SQL+循环，减少流量，也就是“跑批”

统一接口，确保数据的安全

相对于 Oracle 数据库来说，MySQL的存储过程相对功能较弱，使用较少。

—— MySQL存储过程的创建及调用

20 - 管理事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT 语句（回退 SELECT 语句也没有必要）。

也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

具体语法应参考相应的 DBMS 文档。

21 - 使用游标

有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

具体语法应参考相应的 DBMS 文档。

22 - 高级 SQL 特性

约束

管理如何插入或处理数据库数据的规则。

主键（PRIMARY KEY）
外键（REFERENCES）
唯一约束（UNIQUE）
检查约束（CHECK）

索引

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

可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。

索引改善检索操作的性能，但降低了数据插入、修改、删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如省）不如具有更多可能值的数据（如姓、名）能通过索引得到更大的提升。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可能在索引中定义多个列（如州+城市）。这样索引仅在以州+城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。

触发器可以与特定表上的 INSERT、UPDATE、DELETE 操作（或组合）相关联。

与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。（例如：与 Orders 表上的 INSERT 操作相关联的触发器只在 Orders 表中插入行时执行。类似的，Customers 表上的 INSERT 和 UPDATE 操作的触发器只在表上出现这些操作时执行。）

触发器内的代码具有一下数据库的访问权：

INSERT 操作中的所有新数据；
UPDATE 操作中的所有新数据和旧数据；
DELETE 操作中删除的数据。

根据所使用的 DBMS 不同，触发器可在特定操作执行之前或之后执行。

触发器的常见用途：

保证数据一致。（如：在 INSERT 或 UPDATE 操作中将所有州名转换为大写）
基于某个表的变动在其他表上执行活动。（如：每当更新或删除一行时将审计跟踪记录写入某个日志表）
进行额外的验证并根据需要回退数据。（如：保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入）
计算计算列的值或更新时间戳。

不同 DBMS 的触发器创建于法差异很大。

数据库安全

一般来说，需要保护的操作有：

对数据库管理功能的访问（创建表、更改或删除已存在的表等）；
对特定数据库或表的访问；
访问的类型（制度、对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
闲置管理用户账号的能力。

1 - 了解 SQL#

数据库#

表#

列#

主键：#

2 - 检索数据#

查询语句 SELECT#

注释#

结果集去重 DISTINCT#

限制结果 LIMIT#

3 - 排序检索数据#

排序数据 ORDER BY#

按多个列排序#

降序排列#

4 - 过滤数据#

WHERE#

操作符#

5 - 高级数据过滤#

AND OR#

IN#

NOT#

6 - 用通配符进行过滤#

LIKE#

% 通配符#

_ 通配符#

注意#

7 - 创建计算字段#

拼接字段#

AS 别名#

计算字段#

8 - 使用函数处理数据#

函数#

9 - 汇总数据#

聚集函数#

AVG() 平均值#

COUNT() 计数#

MAX() 最大值#

MIN() 最小值#

SUM() 求和#

聚集不同值#

10 - 分组数据#

GROUP BY 分组#

HAVING 过滤分组#

WHERE 与 HAVING 的差别：#

ORDER BY 分组和排序#

SELECT 子句书写顺序#

11 - 使用子查询#

利用子查询进行过滤#

1：检索包含物品 RGAN01 的所有订单的编号#

2：检索具有前一步骤列出的订单编号的所有顾客的 ID#

3：检索前一步骤返回的所有顾客 ID 的顾客信息#

作为计算字段使用子查询#

1：从 Customers 表中检索顾客列表；#

2：对于检索出的每个顾客，统计其在 Orders 表中的订单数目#

12 - 连接表#

连接#

内连接（inner join）#

MySQL 中的多种写法：#

笛卡尔积：（叉连接）（cross join）#

连接多个表#

13 - 创建高级连接#

使用表别名#

自连接（self-join）#

自然连接（natural join）#

外连接（outer join）#

全外连接（full outer join）#

14 - 组合查询#

组合查询（ UNION）#

创建组合查询#

包含或取消重复的行：#

排序：#

UNION 规则#

15 - 插入数据#

数据插入#

插入完整的行：（MySQL 中可以省略 INTO）#

更安全的方法 or 插入部分字段：#

一次插入多行数据#

插入检索出的数据#

从一个表复制到另一个表#

16 - 更新和删除数据#