SQL入门到精通理论+实战 -- 在 MySQL 中学习SQL语言

一、环境准备

1、MySQL 8.0 和 Navicat 下载安装

2、准备好的表和数据文件：

二、SQL语言简述

1、数据库基础概念

2、什么是SQL

3、SQL的分类

4、SQL通用语法

三、DDL（Data Definition Language）：数据定义语言

1、操作数据库

2、操作表

3、操作视图

4、操作索引

5、操作存储过程

四、DML（Data Manipulation Language）：数据操作语言

1、查询数据

（1）SELECT检索数据

（2）ORDER BY 对检索数据排序

（3）过滤数据--WHERE子句

（4）创建计算字段

（5）使用函数处理数据

（6）汇总数据--聚集函数的使用

（7）分组数据--GROUP BY和HAVING子句

（8）SELECT使用子查询

（9）MySQL中的表联结

（10）MySQL组合查询

2、插入数据

3、更新数据

4、删除数据

五、DCL（Data Control Language）：数据控制语言

1、权限管理基础

（1）授予权限（GRANT）

（2）撤销权限（REVOKE）

2、用户账户管理

（1）创建用户

（2）修改用户密码

（3）查看用户权限

（4）删除用户

六、TLC（Transaction Control Language）：事务控制语言

1、什么是事务处理

2、控制事务处理：

（1）事务的开始和结束

（2）回滚事务

（3）使用COMMIT

（4）使用保留点 SAVEPOINT

（5）结合存储过程的完整事务例子（存储过程后面介绍）

七、使用存储过程（函数）

1、存储过程概述

2、使用MYSQL存储过程

（1）存储过程的创建

（2）定义存储过程

（3）执行存储过程

3、存储过程的管理

八、游标控制（Cursor Control）

1、什么是游标

2、使用游标

九、高级SQL特性

1、约束

（1）主键

（2）外键

（3）唯一约束

（4）检查约束

2、索引

（1）什么是索引，为什么使用索引

（2）创建索引

3、触发器

一、环境准备

各节详细内容可参考专栏：SQL必知必会专栏

1、MySQL 8.0 和 Navicat 下载安装

MySQL 8.0 是数据库管理系统 DBMS

Navicat是一个可连接 DBMS 的可视化页面软件

下载和安装参考如下：

MySQL8.0和Navicat premium 12安装超详细教程

2、准备好的表和数据文件：

（表也可以在评论区获取源码）

百度网盘链接: 百度网盘请输入提取码

提取码: fpnx

各表的简单描述如下，详细请运行SQL文件查看。

样例表关系图

（1） Vendors 表

Vendors 表存储销售产品的供应商。每个供应商在这个表中有一个记录，供应商 ID 列（vend_id）用于进行产品与供应商的匹配。

（2） Products 表

Products 表包含产品目录，每行一个产品。每个产品有唯一的 ID（prod_id 列），并且借助 vend_id（供应商的唯一 ID）与供应商相关联。

（3）Customers 表

Customers 表存储所有顾客信息。每个顾客有唯一的 ID（cust_id 列）

（4）Orders 表

Orders 表存储顾客订单（不是订单细节）。每个订单唯一编号（order_num 列）。Orders 表按 cust_id 列（关联到 Customers 表的顾客唯一 ID）关联到相应的顾客。

（5） OrderItems 表

OrderItems 表存储每个订单中的实际物品，每个订单的每个物品一行。对于 Orders 表的每一行，在 OrderItems 表中有一行或多行。每个订单物品由订单号加订单物品（第一个物品、第二个物品等）唯一标识。订单物品用 order_num 列（关联到 Orders 表中订单的唯一 ID）与其相应的订单相关联。此外，每个订单物品包含该物品的产品 ID（把物品关联到 Products 表）。

二、SQL语言简述

1、数据库基础概念

数据库（database）：

保存有组织的数据的容器（通常是一个文件或一组文件）。
数据库是一个以某种有组织的方式存储的数据集合。最简单的办法是将数据库想象为一个文件柜。

数据库管理系统（DBMS）：

人们通常用数据库这个术语来代表他们使用的数据库软件，确切地说，数据库软件应称为数据库管理系统（DBMS）。数据库是通过 DBMS 创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。

表（Table）：某种特定类型数据的结构化清单（订单和顾客的清单需要分别有各自的表）。

模式（schema）：关于数据库和表的布局及特性的信息。

表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。描述表的这组信息就是所谓的模式（schema），模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。

列（column）：表中的一个字段。所有表都是由一个或多个列组成的。

数据类型：所允许的数据的类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。

行（row）：表中的一个记录。

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。例如，顾客表可以每行存储一个顾客。表中的行编号为记录的编号。
你可能听到用户在提到行时称其为数据库记录（record）。这两个术语多半是可以交替使用的，但从技术上说，行才是正确的术语。

主键（primary key）：一列（或一组列），其值能够唯一标识表中每一行。

2、什么是SQL

SQL（发音为字母 S-Q-L 或 sequel）是 Structured Query Language（结构化查询语言）的缩写。SQL 是一种专门用来与数据库沟通的语言。

SQL的优点：简答易学，几乎所有重要 DBMS 都支持 SQL，很灵活。

3、SQL的分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）：数据操作语言，用来对数据库记录（数据）操作；
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
TLC（Transaction Control Language）：事务控制语言，用来管理数据库事务。

4、SQL通用语法

SQL 语句可以单行或多行书写，以分号结尾；
处理 SQL 语句时，其中所有空格都被忽略；
可以用空格和缩进来来增强语句的可读性；
MySQL 不区别大小写，关键字建议使用大写；
单行注释：“-- ”（注意空格），多行注释“/* */”，*中间的内容都是注释

三、DDL（Data Definition Language）：数据定义语言

创建：CREATE + [数据库 | 表 | 视图 | ...] + name

删除：DROP + [数据库 | 表 | 视图 | ...] + name

修改：ALTER + [数据库 | 表 | 视图 | ...] + name

1、操作数据库

查看所有数据库：SHOW DATABASES;
切换数据库：USE sql-learn; 切换到sql-learn数据库后可以对其展开多种操作。
创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1;
删除数据库：DROP DATABASE [IF EXISTS] mydb1;
修改数据库：

ALTER DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name;/*
character_set_name：新的字符集，例如 utf8mb4。
collation_name：新的排序规则，例如 utf8mb4_unicode_ci。
*/

2、操作表

创建表

-- 列名 数据类型 NULL 默认值 约束（主键、外键、查看约束）
CREATE TABLE OrderItems_new3
( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL,PRIMARY KEY (order_num, order_item),FOREIGN KEY (prod_id) REFERENCES products(prod_id)
);

查看表结构：DESC 表名;
删除表：DROP TABLE 表名;
修改表：

-- 新增列
ALTER TABLE orders_new
ADD COLUMN vend_phone CHAR(20);-- 修改已有列
ALTER TABLE orders_new
MODIFY COLUMN vend_phone CHAR(30);-- 删除列
ALTER TABLE orders_new
DROP COLUMN vend_phone;-- 重命名表
ALTER TABLE orders_new 
RENAME TO orders_new1;RENAME TABLE orders_new TO orders_new1;

3、操作视图

在数据库中，视图是基于 SQL 查询结果的虚拟表，它本身不存储实际数据，而是在使用时动态从基表中获取数据。

创建视图：创建一个有邮箱的顾客视图。

CREATE VIEW CustomerEMailList AS 
SELECT cust_id, cust_name, cust_email 
FROM Customers 
WHERE cust_email IS NOT NULL;

修改视图：MySQL 没有专门的 ALTER VIEW 语法用于修改视图定义，通常采用 CREATE OR REPLACE VIEW 来重新创建视图。

CREATE OR REPLACE VIEW CustomerEMailList AS 
SELECT cust_id, cust_name, cust_email 
FROM Customers 
WHERE cust_email IS NOT NULL;

删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ...;DROP VIEW IF EXISTS CustomerEMailList;

4、操作索引

创建与删除索引：

-- 删除索引
DROP INDEX index_name ON table_name;
-- 创建新索引
CREATE INDEX new_index_name
ON table_name (new_column1, new_column2, ...);

5、操作存储过程

创建存储过程

DELIMITER //
CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type, ...)
BEGIN-- 存储过程主体，可包含各类 SQL 语句sql_statements;
END //
DELIMITER ;--  对邮件发送清单中具有邮件地址的顾客进行计数
DELIMITER //
CREATE PROCEDURE MailingListCount(OUT ListCount INT)BEGINDECLARE v_rows INT;SELECT COUNT(*) INTO v_rowsFROM customersWHERE cust_email IS NOT NULL;SET ListCount = v_rows;END //
DELIMITER ;

删除存储过程：

DROP PROCEDURE IF EXISTS procedure_name;

四、DML（Data Manipulation Language）：数据操作语言

1、查询数据

SELECT：是从一个或多个表中检索信息。

关键字（keyword）：作为 SQL 组成部分的保留字。关键字不能用作表或列的名字。

使用 SELECT 检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

（1）SELECT检索数据

检索单个列：

# 检索products表中的prod_name单列
SELECT prod_name FROM products;

检索多个列：

# 检索products表prod_id, prod_name, prod_price多列
SELECT prod_id, prod_name, prod_price
FROM products;

检索所有列：在实际列名的位置使用星号（*）通配符可以做到这点，如下所示。

# 检索products表中所有列
SELECT *
FROM products;

检索单个列不同值：使用 DISTINCT 关键字，顾名思义，它指示数据库只返回不同的值。

# “distinct” 主要取“不同的；有区别的”这个意思，用于筛选出独一无二的数据
# 在products表中检索不同的vend_id
SELECT DISTINCT vend_id 
FROM products;

限制结果：LIMIT限制行数，OFFSET指定从第几行开始（0是第一行）

# 限制第6行起步的5行数据，OFFSET指定跳过前几行
SELECT prod_name
FROM products
LIMIT 5 OFFSET 5;# 简化的语句，逗号之前的值对应 OFFSET，逗号之后的值对应 LIMIT。
SELECT prod_name
FROM products
LIMIT 0, 1;

（2）ORDER BY 对检索数据排序

排序数据使用ORDER BY子句，该子句只能放在SQL语句的最后的子句。也就是只能对最终的结果进行排序。

按单个列排序

-- 查询products表中的prod_name，并根据该列排序
SELECT prod_name
FROM products
ORDER BY prod_name;

按多个列排序

-- 按多列排序，查询products表中的prod_id, prod_price, prod_name列，依次排序
-- 如果第一列相同，则根据第二列排序，前两列相同则根据第三列排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_id, prod_price, prod_name;

按列位置排序

-- 按位置排序，就是SELECT后面跟着的列的顺序的相对位置
-- 这里的意思是先按prod_price排序, 后按prod_name排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY 2, 3;

指定排序方向：升序：ASC（ascending）降序：DESC（descending）

-- 倒序排序，上面都是默认正序由大到小排序的，这里先按prod_price倒序排序, 后按prod_name正序排序
-- ，DESC 是 DESCENDING 的缩写，这两个关键字都可以使用。与 DESC 相对的是 ASC（或 ASCENDING），在升序排序时可以指定它。
SELECT prod_id, prod_price, prod_name 
FROM products 
ORDER BY 2 DESC, 3 ASC;

（3）过滤数据--WHERE子句

使用过滤时，可以看作是将表中的每一行与过滤条件进行对比，满足条件才会被检索出来。

使用WHERE子句

/*
从 products 表中检索prod_name, prod_price
但不返回所有行，只返回prod_price 值为 10 的行
*/
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 10;      -- SQL语句中没有赋值的概念，大胆使用=符号

WHERE子句操作符

-- 列出所有价格小于 10 美元的产品。
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;-- 列出所有不是供应商 DLL01 制造的产品：
SELECT prod_name, vend_id
FROM products
WHERE vend_id <> 'DLL01';        -- 单引号用于限定字符串

范围值检查--BETWEEN AND

-- 使用 BETWEEN 操作符，它检索价格在 5 美元和 10 美元之间的所有产品。
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;  
-- 使用时必须指定两个值，用AND连接，并且是闭区间

NULL空值检查

-- 返回所有没有价格（空 prod_price 字段，不是价格为 0）的产品
SELECT prod_name, prod_price
FROM products
WHERE prod_price IS NULL;

组合条件--AND和OR操作符：AND优先级更高

-- 在处理 OR 操作符前，优先处理 AND 操作符
-- 这里对于1002的供应商就会显示所有产品
SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id = 1002 OR vend_id = 1001 AND prod_price <= 6;

IN和NOT操作符

IN 取一组由逗号分隔、括在圆括号中的合法值；

NOT：WHERE 子句中用来否定其后条件的关键字。

SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id IN (1001, 1002);-- NOT 关键字可以用在要过滤的列前，而不仅仅是在其后。
SELECT prod_name, prod_price, vend_id
FROM products
WHERE NOT vend_id IN (1001, 1002);-- NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件
SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id NOT IN (1001, 1002);

使用通配符进行过滤：LIKE操作符、% 和 _

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

为在搜索子句中使用通配符，必须使用 LIKE 操作符。LIKE指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。通配符搜索只能用于文本字段（字符串）

谓词（predicate）

从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在 SQL 文献或手册中遇到此术语时不知所云。

%通配符：代表搜索模式中给定位置的 0 个、1 个或多个字符。

-- %代表搜索模式中给定位置的 0 个、1 个或多个字符。
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '%ton%';-- 子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '%';-- 在针对邮箱应用场景十分实用
SELECT cust_name,cust_email
FROM customers
WHERE cust_email LIKE '%@coyote.com';

_通配符：只匹配单个字符，而不是多个字符。

-- （_）下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '_eton%';

（4）创建计算字段

计算字段是直接从数据库中检索出转换、计算或格式化过的数据，计算字段是运行时在 SELECT 语句内创建的。

字段基本与列相同，经常互换使用

拼接字段

创建由两列组成的标题。Vendors 表包含供应商名和国家信息。我们需要以vend_name (vend_country)的形式形成字段，也就是只需要一个字段来包含原本的两列值。

解决办法是把两个列拼接起来。在 MySQL中需要使用CONCAT函数。

-- mysql的拼接只支持concat函数，不支持+或者||符号
SELECT vend_name + ' (' + vend_country + ')' 
FROM Vendors 
ORDER BY vend_name;-- 列名不要加''，自己增加的某些字符或者字符串才用''包住
SELECT CONCAT(vend_name, '      (', vend_country, ')')
FROM Vendors 
ORDER BY vend_name;

去除空格

RTRIM()（去掉字符串右边的空格）；

LTRIM()（去掉字符串左边的空格）；

TRIM()（去掉字符串左右两边的空格）

SELECT CONCAT(TRIM(vend_name), '      (', vend_country, ')')
FROM Vendors 
ORDER BY vend_name;

使用别名：AS

这里可以看到字段名为我们的函数及其参数，很难看。其实没有列名，它只是一个值一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

为了解决这个问题，SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。

这里使用vend_title替换原默认计算字段名。

-- 一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。
-- SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予
SELECT CONCAT(vend_name, '      (', vend_country, ')') AS vend_title
FROM Vendors 
ORDER BY vend_name;

执行算术操作

现在我们要计算出订单号为20008的订单总金额，就需要使用到算术计算了，创建出新的字段进行别名，从而让客户端能够使用该列。

-- +-*/可用于计算字段，常搭配AS别名
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM OrderItems 
WHERE order_num = 20008;

（5）使用函数处理数据

上面我们已经使用过某些函数了，比如TRIM、CONCAT等，如下我们还将介绍一些函数的使用：

需要注意的是：函数在不同 DBMS 大都不相同，函数的可移植性差

针对文本字符串的MySQL函数

其中SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

-- SOUNDEX(str)函数:将任何文本串转换为描述其语音表示的字母数字模式的算法
-- customers表中有位顾客的联系名为Jim Jones，但现实中是Jimi Jones，现在要找到该客户的信息，利用发音相似
SELECT *
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Jimi Jones');

-- 其他函数的使用

/*针对文本字符串的mysql函数
*/-- 计算字段是对列内容的计算或者修改，会有新的一列显示出来，但其实并没有列名，需要用AS指定列（字段）名
-- 这里使用concat函数、upper函数和lower函数
SELECT CONCAT(cust_name,' (concat)') AS cust_name , cust_email, UPPER(cust_name) AS upper_cust_name, LOWER(cust_name) AS lower_cust_name
FROM customers
WHERE cust_email LIKE '%@coyote.com';-- LEFT(str,len)函数、RIGHT(str,len)函数、LENGTH(str)函数
SELECT cust_name, cust_email, LEFT(cust_name, 5) AS left5, RIGHT(cust_name, 5) AS right5, LENGTH(cust_name) AS name_length
FROM customers
WHERE cust_email LIKE '%@coyote.com';

日期和时间处理函数

日期和时间函数的差移植性

日期和时间采用相应的数据类型存储在表中，每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
应用程序一般不使用日期和时间的存储格式（可能直接使用的字符串），因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在 SQL中具有重要的作用。遗憾的是，它们很不一致，可移植性最差。

Orders 表中包含的订单都带有订单日期。为在 MySQL 中检索 2005 年的所有订单，可如下进行：使用YEAR函数，提取日期数据的年份。

-- 各家DBMS的日期和时间格式各不相同，具体看各家的情况
-- 返回2005年的订单
-- MySQL 具有各种日期处理函数，但没有 DATEPART()。MySQL用户可使用名为 YEAR()的函数从日期中提取年份：
SELECT order_num 
FROM orders 
WHERE YEAR(order_date) = 2005;

使用BETWEEN关键字获取时间范围的数据，要先将字符串转换为日期时间格式，使用 STR_TO_DATE函数进行转换。

-- STR_TO_DATE(str,format)函数可将字符串转换为日期格式，从而利用between关键字依旧可以实现相应功能
/*%Y：代表 4 位年份，例如 2025。%y：代表 2 位年份，例如 25（代表 2025）。%m：代表月份，范围是 01 - 12。%d：代表一个月中的第几天，范围是 01 - 31。%H：代表小时（24 小时制），范围是 00 - 23。%h：代表小时（12 小时制），范围是 01 - 12。%i：代表分钟，范围是 00 - 59。%s：代表秒，范围是 00 - 59。
*/
SELECT * 
FROM orders 
WHERE order_date BETWEEN STR_TO_DATE('2005-09-01','%Y-%m-%d') AND STR_TO_DATE('2005-09-30','%Y-%m-%d');

数值处理函数（数学）

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期--时间处理函数使用那么频繁。

具有讽刺意味的是，在主要 DBMS 的函数中，数值函数是最一致、最统一的函数。

/*ABS(X)、COS(X)、PI()、EXP(X)、SQRT(X)
*/
SELECT PI();
SELECT ABS(-5);
SELECT SIN(PI()/6), COS(PI()),TAN(PI()/4);

（6）汇总数据--聚集函数的使用

有时候我们只需要某写数据的一些汇总或者统计信息，这时候不需要返回完整的数据，节省资源，比如统计订单数？
SQL支持五类聚集函数用于汇总数据，分别是AVG、COUNT、MAX、MIN、SUM

AVG函数

对过滤后的所有行的指定列取平均值；获取多列的平均值需要使用多个AVG函数。

说明：NULL 值：AVG()函数忽略列值为 NULL 的行。

-- 求所有产品平均价格
SELECT AVG(prod_price) AS avg_price
FROM products;-- 求供应商是1002的所有产品均价，获取多列的平均值必须使用多个avg函数
SELECT AVG(prod_price) AS avg_price, MAX(prod_id) AS max_id
FROM products
WHERE vend_id = 1002;                    -- WHERE子句仅过滤出vend_id为1002的行

COUNT函数

COUNT()函数进行计数。可利用 COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。

-- count统计行数，COUNT(*)统计该表所有行数，即使某些行的某列值是NULL
-- 返回 Customers 表中顾客的总数
SELECT COUNT(*)      -- 14行
FROM products;SELECT COUNT(prod_price)      -- 13行，因为有一个price为NULL
FROM products;-- 统计拥有邮箱的顾客有多少个？
SELECT COUNT(cust_email)
FROM customers;

MAX和MIN函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

-- MAX函数，一般找日期和数值，对于文本直接返回该列排序后的最后一行
-- MIN函数与MAX函数相反
SELECT MAX(prod_id) AS max_id            -- 
FROM products;

提示：对非数值数据使用 MAX()

虽然 MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS 允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。

说明：NULL 值：MAX()函数忽略列值为 NULL 的行。

MIN()的功能正好与 MAX()功能相反，它返回指定列的最小值。与 MAX()一样，MIN()要求指定列名。

SELECT MIN(prod_price) AS min_price 
FROM Products;其中 MIN()返回 Products 表中最便宜物品的价格。

SUM函数

SUM()用来返回指定列值的和（总计）。下面举一个例子，OrderItems 包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有 quantity 值之和）：

-- 统计订单号为20005的所有物品个数
SELECT SUM(quantity) AS prod_quantity
FROM orderitems
WHERE order_num = 20005;

SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

-- SUM函数统计指定列值的和，比如统计某订单的所有产品总数和总价格
-- 利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。
SELECT SUM(quantity) AS items_ordered, SUM(item_price * quantity) AS total_price
FROM OrderItems 
WHERE order_num = 20005;

提示：在多个列上进行计算：

利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

说明：NULL 值：SUM()函数忽略列值为 NULL 的行。

聚集不同值--DISTICT

对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。

只包含不同的值，指定 DISTINCT 参数。

下面想通过COUNT函数通过orderitems表统计总共有多少个订单。

/*ALL和DISTINCT参数在聚集函数中的作用聚集不同值，上述中会对过滤后的数据中不为NULL的列值进行汇总，其实默认有一个ALL参数对于相同的值可能不需要汇总，比如我想在orderitem中查看有多少个订单聚集不同的值就需要使用DISTINCT参数
*/
SELECT COUNT(ALL order_num)             -- 11个，有重复订单
FROM orderitems;SELECT COUNT(DISTINCT order_num)    -- 5个，无重复订单
FROM orderitems;

注意：DISTINCT 不能用于 COUNT(*)

如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表达式。

组合聚集函数

组合聚集函数，就是SELECT语句可包含多个聚集函数，这里注意取的列名不要和原表重复。

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(DISTINCT prod_price) AS price_avg 
FROM Products;

（7）分组数据--GROUP BY和HAVING子句

为何要分组？比如我想统计供应商为1001的产品均价，用where子句过滤然后使用AVG即可。

-- 如果我想统计供应商为1001的产品均价，用where子句过滤就行了
SELECT AVG(prod_price)
FROM products
WHERE vend_id = 1001;

但是我想统计所有供应商的产品均价呢，还要去一个一个写吗？

这时候就要用到分组了。使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

SELECT vend_id, AVG(prod_price) AS avg_price, COUNT(*)
FROM products
GROUP BY vend_id;                                 
-- GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

分组的构成：包括分组键和组内数据，嵌套的分组结构可以看成树形结构

创建分组

SELECT vend_id, AVG(prod_price) AS avg_price, COUNT(*)
FROM products
GROUP BY vend_id;

通过上面的例子，可以知道分组是使用 SELECT 语句的 GROUP BY 子句建立的。

因为使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

过滤分组 -- 使用 HAVING 子句

除了能用 GROUP BY 分组数据外，SQL 还允许过滤分组，规定包括哪些分组，排除哪些分组。

例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。

在这个例子中 WHERE 不能完成任务，因为 WHERE 过滤指定的是行而不是分组。

那么，不使用 WHERE 使用什么呢？SQL 为此提供了另一个子句，就是HAVING 子句。HAVING 非常类似于 WHERE。唯一的差别是，WHERE过滤行，而 HAVING 过滤分组。

提示：HAVING 支持所有 WHERE 操作符

我们学习了 WHERE 子句的条件（包括通配符条件和带多个操作符的子句）。学过的这些有关 WHERE 的所有技术和选项都适用于 HAVING。它们的句法是相同的，只是关键字有差别。

那么，怎么过滤分组呢？请看以下的例子：

-- 过滤供应商的产品种类少于 3 的分组（过滤的是分组而不是行哦）

-- HAVING过滤分组，WHERE过滤行
-- 过滤产品种类少于3的分组
SELECT vend_id, AVG(prod_price) AS avg_price
FROM products
GROUP BY vend_id
HAVING COUNT(prod_name) < 3

说明：HAVING 和 WHERE 的差别

这里有另一种理解方法，WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。这是一个重要的区别，WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

WHERE和HAVING一起使用：-- 列出具有两个及以上产品且其价格大于等于 4 的供应商id：

-- WHERE和having的一起使用
SELECT vend_id
FROM products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(prod_name) >= 2;

分组和排序

数据进行分组后，可以使用ORDER BY 对分组进行排序，注意是对分组进行排序而不是针对行进行排序

检索包含三个或更多物品的订单号和订购物品的数目,要按订购物品的数目和订单号降序排序输出

-- 检索包含三个或更多物品的订单号和订购物品的数目
-- 要按订购物品的数目和订单号降序排序输出
SELECT order_num, COUNT(*) AS items
FROM orderitems
GROUP BY order_num
HAVING COUNT(order_item) >= 3
ORDER BY items DESC, 1 DESC;

（8）SELECT使用子查询

SELECT 语句是 SQL 的查询。我们迄今为止所看到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。

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

SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询。为什么要这样做呢？理解这个概念的最好方法是考察几个例子。

说明：MySQL 支持

如果使用 MySQL，应该知道对子查询的支持是从 4.1 版本引入的。MySQL 的早期版本不支持子查询。

使用子查询的情景：

找到包含物品 ANV01 的订单号
根据订单号找到顾客的ID
根据顾客的ID查找顾客的信息

不使用子查询，一步一步做：

-- 先找到有订购 ANV01 的订单号
SELECT order_num
FROM orderitems
WHERE prod_id = 'ANV01';
-- 20005-- 在找到该订单号的所有顾客id
SELECT cust_id
FROM orders
WHERE order_num IN (20005);
-- 10001-- 最后根据顾客id返回顾客的信息
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001);

这里使用子查询是如何呢？就是在过滤时以子查询的结果作为过滤的条件：

-- 使用子查询
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_numFROM orderitemsWHERE prod_id = 'ANV01');

先执行最内的子查询，最后才执行最外层的查询。

注意：只能是单列：作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

（9）MySQL中的表联结

为什么使用表联结：
如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。如果数据存储在多个表中，怎样用一条 SELECT 语句就检索出数据呢？

答案是使用联结。简单说，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行

这里介绍五种联结的概念：

笛卡尔积（叉联结），逗号分隔

比如查找供应商名、对应的prod_name、prod_price：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
-- 联结的操作在FROM中执行，连接后（笛卡尔积）有6*14行，再经过WHERE进行过滤

内联结（等值联结） -- INNER JOIN

上面使用叉联结+WHERE过滤后的结果与等值联结的结果相同。

等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型。下面的 SELECT 语句返回与前面例子完全相同的数据：

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id
-- 在使用这种语法时，联结条件用特定的 ON 子句而不是 WHERE 子句给出。
WHERE prod_price > 5;

内联结使用INNER JOIN来连接两个表，ON子句指定连接的条件，在这里是一种等值条件，意味着满足条件的行才会进行联结。
如果没有ON子句，联结的结果就是叉积的结果。
ON用于指定联结的条件，是在联结之前进行，WHERE只起到过滤的作用，在联结之后进行

联结多个表，查找20005号订单的各个产品名、生产商名、价格和数量

-- 内联结+ON条件联结+where过滤方式（只用ON或者where也可）
SELECT prod_name, vend_name, prod_price, quantity 
FROM OrderItems INNER JOIN Products INNER JOIN Vendors  
ON orderitems.prod_id = products.prod_idAND products.vend_id = vendors.vend_id
WHERE order_num = 20005;-- 叉积过滤方式
SELECT prod_name, vend_name, prod_price, quantity
FROM vendors, orderitems, products
WHERE order_num = '20005'AND vendors.vend_id = products.vend_idAND orderitems.prod_id = products.prod_id

使用表别名，在FROM中使用AS：简化子句条件。

-- 表别名，与列或者计算字段别名不同，在FROM中进行
-- 联结多个表，查找20005号订单的各个产品名、生产商名、价格和数量
SELECT prod_name, vend_name, prod_price, quantity 
FROM OrderItems AS O INNER JOIN Products AS P INNER JOIN Vendors AS V
ON O.prod_id = P.prod_idAND P.vend_id = V.vend_id
WHERE order_num = 20005;

自联结 -- self join

自联结就是将表自身联结起来，用内联结和笛卡尔积都可以，而自联结就是将表自身联结起来：例如在customers中找到与 Jim Jones 同一公司的所有顾客：

子查询方式：

-- 找到与 Jim Jones 同一公司的所有顾客
-- 子查询方式
SELECT cust_id, cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = (SELECT cust_nameFROM customersWHERE cust_contact = 'Jim Jones');

内联结和笛卡尔积方式：

-- 自联结，就是将利用表别名将表本身进行内联结或者笛卡尔积
SELECT C1.cust_id, C1.cust_name, C1.cust_contact, C1.cust_email
FROM customers AS C1 INNER JOIN customers AS C2
ON C2.cust_contact = 'Jim Jones'
WHERE C1.cust_name = C2.cust_name;
-- 利用ON先将C2过滤得只剩两行再进行内联结
-- 联结后再利用where进行过滤将C1的cust_name与C2的cust_name进行匹配筛选

提示：用自联结而不用子查询

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结 -- nature join

经过前面的练习，不难知道联结之后的结果难免出现相同的列，自然联结就是排除列的多次出现，使得每列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。也就是自己指定检索出的列名。一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。

-- 自然联结，想要消除内联结导致重复的列，系统无法做到，只能人工完成
-- 需要列出订购物品号 ANV01 的所有顾客的信息，包括订单信息，但不重复列
SELECT C.*, O.order_num, O.order_date, OI.order_item, OI.prod_id, OI.item_price, OI.quantity
FROM orders AS O INNER JOIN orderitems AS OI INNER JOIN customers AS C
ON OI.prod_id = 'ANV01'AND OI.order_num = O.order_numAND O.cust_id = C.cust_id;
-- 把ON换成WHERE效果是一样的，但是进行联结的时候不一样哦
-- WHERE是对联结的结果进行过滤，ON是在联结前设置条件

外联结 -- left join 、right join

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的顾客。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

举例更好理解，比如使用内联结查看已有顾客的订单数量（这些顾客至少有订单）

SELECT customers.cust_id, COUNT(*)
FROM orders INNER JOIN customers
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

这里的结果是满足的，但是如果我想要所有顾客的订单信息呢？（包括没有订单的顾客）就需要使用外联结了。

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）

-- 由于这些顾客在orders中没有行记录相应的id，就需要联结在相关表中没有关联的行，这就是外联结
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;
-- 因为有些顾客实际上没有订单，所以应该计数order_num
-- 还有一种叫全联结，但MySQL不允许

上面的例子使用 LEFT JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行。为了从右边的表中选择所有行，需要使用 RIGHT JOIN，如下例所示：

SELECT customers.cust_id, COUNT(orders.order_num)
FROM orders RIGHT JOIN  customers 
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

（10）MySQL组合查询

使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。

-- WHERE方式
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('MI', 'IN', 'OH')OR cust_name = 'Wascals'
ORDER BY cust_name;-- UNION方式，UNION默认自带DISTINCT，想要显示所有结果加上ALL
-- 并集时默认去掉重复行
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('MI', 'IN', 'OH')
UNION -- ALL
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Wascals';
ORDER BY cust_name;

使用UNION的规则：

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

2、插入数据

无指定列名插入

INSERT INTO 表名 VALUES( )

不指定列名就需要对所有列进行插入值，并且按照列的顺序

-- 此不指定列名的方式必须按照次序对所有列值进行填充（没有也需要使用NULL）
INSERT INTO Customers 
VALUES(NULL, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

指定列名插入

INSERT INTO 表名(c1, c2) VALUES(d1, d2)

指定部分或全部列名，插入值只需按照指定的列名顺序插入即可

/*
更安全一点的方式：在表名后可以按照任意次序给出列名，
VALUE对的上即可，并且无需对所有列进行填充
*/
INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip) 
VALUES(NULL, NULL, NULL, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111');
-- 省略的列会被取默认值或NULL，如果表中不允许会报错提示

INSERT + SELECT

INSERT 可以利用 SELECT 语句的结果插入表中，即 INSERT SELECT。

假如想把另一表中的顾客列合并到 Customers 表中。不需要每次读取一行再将它用 INSERT 插入，可以如下进行：（先将原来的customers表复制一份结构取名为CustNew）

-- INSERT + SELECT：将检索的数据插入表--插入多行
INSERT INTO CustNew(cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country 
FROM customers;

提示：INSERT SELECT 中的列名填充顺序

为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS 一点儿也不关心 SELECT返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

3、更新数据

UPDATE语法

-- UPDATE语法
UPDATE [表名]
SET [列名] = [新值]   -- 多个列之间用,分隔。在更新多个列时，只需要使用一条 SET 命令
WHERE [过滤条件];

举例

-- 10005现在有了邮箱值kim@thetoystore.com，请更新
UPDATE customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '10005';
-- 不加where进行过滤的后果就是会更新所有行的cust_email列值为'kim@thetoystore.com'-- 更改多个列值，只需要在列名+值之间以逗号分隔
-- 对于表的操作，都可以叫做查询，使用UPDATE时也会读取表的内容以进行过滤等操作
UPDATE customers
SET cust_email = 'kim@thetoystore.com',cust_country = 'China'
WHERE cust_id = '10005';

SET支持子查询、计算字段等方式

在update中使用子查询，使用子查询检索出的数据更新列数据

表结构：
employees表：
employee_id：员工编号（主键）
salary：员工工资

salary_adjustments表：
employee_id：员工编号
adjustment_amount：工资调整金额

目标是根据salary_adjustments表中的调整金额更新employees表中的员工工资。

UPDATE employees
SET salary = salary + (SELECT adjustment_amountFROM salary_adjustments saWHERE sa.employee_id = employees.employee_id
)
WHERE EXISTS (                          -- 可以使用尝试INSELECT 1                            -- 1只是习惯，EXISTS过滤掉那些没有返回行的数据FROM salary_adjustments saWHERE sa.employee_id = employees.employee_id
);

4、删除数据

DELETE语法

-- DELETE语法
DELETE FROM [表名]
WHERE [过滤条件];

举例

-- DELETE 删除表中的行数据，尽量加上FROM，保持可移植性
DELETE FROM Customers 
WHERE cust_id = '10006';

删除所有数据 -- TRUNCATE

DELETE不指定过滤条件就会删除所有行

TRUNCATE 其实属于 DDL 语句，因为它是先 DROP TABLE，再 CREATE TABLE。
而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

-- DELETE 删除表中的行数据，尽量加上FROM，保持可移植性
DELETE FROM Customers;-- 或者
TRUNCATE TABLE orders_new;

五、DCL（Data Control Language）：数据控制语言

MySQL 中的数据控制语言（DCL）主要用于管理数据库用户的权限，确保数据的安全性和完整性。其核心操作包括授予权限（GRANT）、撤销权限（REVOKE）以及对用户账户的管理。以下是详细介绍：

1、权限管理基础

在 MySQL 里，权限可被授予到不同的级别，如全局级别（整个 MySQL 服务器）、数据库级别、表级别、列级别和存储过程级别等。常见的权限有 SELECT（查询数据）、INSERT（插入数据）、UPDATE（更新数据）、DELETE（删除数据）、CREATE（创建数据库对象）、DROP（删除数据库对象）等。

（1）授予权限（GRANT）

授予权限语法：

GRANT privileges ON object TO user [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

privileges：要授予的权限，可以是单个权限（如 SELECT），也可以是多个权限（如 SELECT, INSERT），或者使用 ALL PRIVILEGES 表示授予所有权限。

object：权限应用的对象，可以是 *.*（表示所有数据库的所有表）、database_name.*（表示某个数据库的所有表）、database_name.table_name（表示某个数据库的某个表）等。

user：要授予权限的用户，格式为 'username'@'host'，username 是用户名，host 是允许连接的主机，可以使用 % 表示任意主机。

IDENTIFIED BY 'password'：可选参数，用于指定用户的密码。

WITH GRANT OPTION：可选参数，如果指定了该选项，用户可以将自己拥有的权限授予其他用户。

授予用户 test_user 在 mysql数据库的所有表上的 SELECT 和 INSERT 权限：

-- 这里先创建了用户，语法后面说
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'langxi';
GRANT SELECT,INSERT ON mysql.* TO 'test_user'@'localhost';

授予用户 admin_user 在所有数据库的所有表上的所有权限，并允许其将权限授予其他用户：

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'langxi';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

（2）撤销权限（REVOKE）

撤销权限语法：

REVOKE privileges ON object FROM user;

privileges：要撤销的权限，语法与 GRANT 中的 privileges 相同。

object：权限应用的对象，语法与 GRANT 中的 object 相同。

user：要撤销权限的用户，格式与 GRANT 中的 user 相同。

撤销用户 test_user 在 mysql 数据库的所有表上的 INSERT 权限：

REVOKE INSERT ON mysql.* FROM 'test_user'@'localhost';

撤销用户 admin_user 在所有数据库的所有表上的所有权限：

REVOKE ALL PRIVILEGES ON *.* FROM 'admin_user'@'localhost';

2、用户账户管理

（1）创建用户

语法：CREATE USER 'username'@'host' IDENTIFIED BY 'password';

创建一个名为 new_user 的用户，允许从本地连接，密码为 123456。

CREATE USER 'new_user'@'localhost' IDENTIFIED BY '123456';

（2）修改用户密码

语法：ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

将用户 new_user 的密码修改为 654321。

ALTER USER 'new_user'@'localhost' IDENTIFIED BY '654321';

（3）查看用户权限

语法：SHOW GRANTS FOR 'username'@'host';

查看 new_user 的权限

SHOW GRANTS FOR 'new_user'@'localhost';

查看所有用户权限

SHOW GRANTS：

（4）删除用户

语法：DROP USER 'username'@'host';

删除用户 new_user。

DROP USER 'new_user'@'localhost';

六、TLC（Transaction Control Language）：事务控制语言

1、什么是事务处理

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

关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。设计良好的数据库模式表之间都是关联的。

Orders 表就是一个很好的例子。订单存储在 Orders 和OrderItems 两个表中：Orders 存储实际的订单，OrderItems 存储订购的各项物品。这两个表使用称为主键的唯一 ID 互相关联，又与包含客户和产品信息的其他表相关联。

给系统添加订单的过程如下：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；

(2) 检索顾客的 ID；

(3) 在 Orders 表添加一行，它与顾客 ID 相关联；

(4) 检索 Orders 表中赋予的新订单 ID；

(5) 为订购的每个物品在 OrderItems 表中添加一行，通过检索出来的 ID把它与 Orders 表关联（并且通过产品 ID 与 Products 表关联）。

如果故障发生在添加顾客之后，添加 Orders 表之前，则不会有什么问题。某些顾客没有订单是完全合法的。重新执行此过程时，所插入的顾客记录将被检索和使用。可以有效地从出故障的地方开始执行此过程。
但是，如果故障发生在插入 Orders 行之后，添加 OrderItems 行之前，怎么办？现在，数据库中有一个空订单。
更糟的是，如果系统在添加 OrderItems 行之时出现故障，怎么办？结果是数据库中存在不完整的订单，而你还不知道。

如何解决这种问题？这就需要使用事务处理了。事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态

关于事务处理的几个术语：

事务（transaction）指一组 SQL 语句；

回退（rollback）指撤销指定 SQL 语句的过程；

提交（commit）指将未存储的 SQL 语句结果写入数据库表；

保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

再看这个例子，这次我们说明这一过程是如何工作的：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；
(2) 提交顾客信息；
(3) 检索顾客的 ID；
(4) 在 Orders 表中添加一行；
(5) 如果向 Orders 表添加行时出现故障，回退；
(6) 检索 Orders 表中赋予的新订单 ID；
(7) 对于订购的每项物品，添加新行到 OrderItems 表；
(8) 如果向OrderItems添加行时出现故障，回退所有添加的OrderItems行和 Orders 行。

提示：可以回退哪些语句？

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

2、控制事务处理：

（1）事务的开始和结束

START TRANSACTION;

......

COMMIT;

-- 事务的开始结束结构，只有提交之后才会真正对表进行更改
-- 最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 UPDATE 起作用，但第二条失败，则 UPDATE 不会提交。
START TRANSACTION;UPDATE customers
SET cust_name = 'zhangsan'
WHERE cust_id = 10012;UPDATE customers
SET cust_name = 'lisi'
WHERE cust_id = 10013;COMMIT;

（2）回滚事务

回滚：回到事务开始前的状态，也就是结束掉本次事务的生命周期
ROLLBACK;

-- 回滚事务START TRANSACTION;
-- 删除某行
DELETE FROM customers
WHERE cust_id = 10012;-- 删除后已经查找不到该ID了
SELECT cust_id
FROM customers
WHERE cust_id = 10012;-- 回滚：回到事务开始前的状态，也就是结束掉本次事务的生命周期
ROLLBACK;-- 回滚后可查到该id
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

（3）使用COMMIT

一般的 SQL 语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。

在事务处理块中，提交不会隐式进行。进行明确的提交，使用 COMMIT 语句。

COMMIT; -- 提交本次事务的操作

（4）使用保留点 SAVEPOINT

使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。

例如前面描述的添加订单的过程就是一个事务。如果发生错误，只需要返回到添加 Orders 行之前即可。不需要回退到 Customers 表（如果存在的话）。

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。在 SQL 中，这些占位符称为保留点。在MySQL 中创建占位符，可使用 SAVEPOINT 语句。

每个保留点都要取能够标识它的唯一名字。

创建保留点：SAVEPOINT name;

回滚到指定保留点：ROLLBACK TO (SAVEPOINT) name;

-- 使用保留点START TRANSACTION;
SAVEPOINT start0;
-- 删除某行
DELETE FROM customers
WHERE cust_id = 10012;
SAVEPOINT delete1;-- 删除后已经查找不到该ID了
SELECT cust_id
FROM customers
WHERE cust_id = 10012;-- 回滚：回到 start0 时的状态，并不是回到start0开始执行语句哦
ROLLBACK TO SAVEPOINT start0;
-- 回滚到start0 即可查看信息
SELECT cust_id
FROM customers
WHERE cust_id = 10012;-- 由于现在已经是start0 时的状态，这个时候并没有delete1这个保留点，所以会提示不存在
ROLLBACK TO delete1;
SELECT cust_id
FROM customers
WHERE cust_id = 10012;COMMIT;    -- 这里的提交相当于只提交了 start0 状态

（5）结合存储过程的完整事务例子（存储过程后面介绍）

-- 一个完整事务例子--MySQL中的条件控制只能在存储过程（函数）中使用
DELIMITER //
CREATE PROCEDURE CompleteOrderTransaction(IN custName CHAR(50))
BEGINDECLARE exit_handler INT DEFAULT FALSE;DECLARE custId INT DEFAULT NULL;DECLARE orderNum INT DEFAULT NULL;START TRANSACTION;SELECT cust_id INTO custIdFROM CustomersWHERE cust_name = custName;IF(custId IS NULL) THENINSERT INTO Customers(cust_name)  -- 插入顾客名VALUES(custName);SET custId = LAST_INSERT_ID();END IF;SELECT custId;SAVEPOINT StartOrder;      -- 保留点，即将开始创建订单INSERT INTO Orders(order_date, cust_id)         -- 插入订单VALUES(CURRENT_TIMESTAMP, custId);IF (ROW_COUNT() < 0) THENSELECT orderNum;ROLLBACK TO SAVEPOINT StartOrder;END IF;SET orderNum = LAST_INSERT_ID();-- 插入订单明细中的第一条INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(orderNum, 1, 'ANV01', 100, 5.49);IF (ROW_COUNT() < 0) THENROLLBACK TO SAVEPOINT StartOrder;END IF;-- 插入订单明细中的第二条INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(orderNum, 2, 'ANV02', 100, 10.99);IF (ROW_COUNT() < 0) THENROLLBACK TO SAVEPOINT StartOrder;END IF;COMMIT;
END //
DELIMITER ;CALL CompleteOrderTransaction('zhangsan');

七、使用存储过程（函数）

1、存储过程概述

由MySQL5.0 版本开始支持存储过程。存储过程其实就是编译好等待调用的函数。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由DBMS调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）

是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。就是数据库 SQL 语言层面的代码封装与重用。

存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT三种类型。

IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

2、使用MYSQL存储过程

（1）存储过程的创建

语法：

DELIMITER //CREATE/*[DEFINER = { user | CURRENT_USER }]*/PROCEDURE 数据库名.存储过程名([in 变量名 类型,out 参数 2，...])/*LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'*/BEGIN[DECLARE 变量名 类型 [DEFAULT 值];]存储过程的语句块;END //DELIMITER ;

DELIMITER //：将语句结束符从分号 ; 更改为 //，因为在存储过程中可能包含多个语句，每个语句默认以分号结束，这会导致存储过程在创建过程中提前结束。使用 DELIMITER // 可以让 MySQL 知道存储过程的结束位置是 //。
CREATE PROCEDURE XXXX(OUT XX INT)：创建一个名为 XXXX的存储过程，它有一个输出参数 XX，类型为 INT。参数可有多个，类型可为IN, OUT, INOUT中的一个
BEGIN：存储过程的开始。
DECLARE XX INT;：声明一个局部变量 XX，类型为 INT，可指定默认值；DECLARE用于声明变量，SET XX = 用于变量赋值
END //：存储过程的结束。
DELIMITER ;：将语句结束符恢复为分号。

（2）定义存储过程

对邮件发送清单中具有邮件地址的顾客进行计数

--  对邮件发送清单中具有邮件地址的顾客进行计数
DELIMITER //
CREATE PROCEDURE MailingListCount(OUT ListCount INT)BEGINDECLARE v_rows INT;SELECT COUNT(*) INTO v_rowsFROM customersWHERE cust_email IS NOT NULL;SET ListCount = v_rows;END //
DELIMITER ;

（3）执行存储过程

CALL MailingListCount(@ListCount);
SELECT @ListCount AS ListCount;

3、存储过程的管理

显示存储过程：

SHOW PROCEDURE STATUS;

显示特定数据库的存储过程

SHOW PROCEDURE STATUS 
WHERE db = 'sql-learn'OR NAME = 'MailingListCount';

模糊查找存储过程（特定模式）

SHOW PROCEDURE STATUS 
WHERE NAME LIKE '%Ma%';

显示存储过程的源码

SHOW CREATE PROCEDURE MailingListCount;

删除存储过程

DROP PROCEDURE MailingListCount;

八、游标控制（Cursor Control）

1、什么是游标

SQL 检索操作返回一组称为结果集的行，这组返回的行都是与 SQL 语句相匹配的行（零行或多行）。

结果集（result set）：SQL 查询所检索出的结果。

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

MySQL 5 以上支持在存储过程中使用CURSOR

2、使用游标

使用游标涉及几个明确的步骤。

在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的 DBMS）。
声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

在存储过程中创建游标

DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。为了说明，我们创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。

    DECLARE CustCursor CURSOR FOR SELECT cust_email -- 这里只选择你需要的列，避免使用SELECT *FROM Customers WHERE cust_email IS NOT NULL;

打开游标

使用 OPEN CURSOR 语句打开游标

		-- 打开游标OPEN CustCursor;

使用游标

现在可以用 FETCH 语句访问游标数据了。FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。从游标中检索一行（第一行）：

FETCH CustCursor INTO email;
-- 在这里可以进行相应的处理，例如打印或者存储到其他表中
SELECT email; -- 输出处理后的数据

在这个例子中，FETCH 用来检索当前行（自动从第一行开始），放到声明的变量 email 中。对于检索出来的数据进行查询操作。（检索后再使用 FETCH 就跳到下一行了）

关闭游标

    -- 关闭游标CLOSE CustCursor;

CLOSE 语句用来关闭游标。一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用 OPEN 打开它即可。

以下是完整的存储过程代码：

DELIMITER //CREATE PROCEDURE UseCursor()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE email VARCHAR(255); -- 假设你要获取的变量类型是VARCHAR，根据实际表结构调整DECLARE CustCursor CURSOR FOR SELECT cust_email -- 这里只选择你需要的列，避免使用SELECT *FROM Customers WHERE cust_email IS NOT NULL;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 打开游标OPEN CustCursor;read_loop: LOOP-- FETCH 语句检索一行并保存值到这些变量中FETCH CustCursor INTO email;IF done THENLEAVE read_loop;END IF;-- 在这里可以进行相应的处理，例如打印或者存储到其他表中SELECT email; -- 输出处理后的数据END LOOP;-- 关闭游标CLOSE CustCursor;
END //DELIMITER ;CALL UseCursor();

九、高级SQL特性

1、约束

约束（constraint）：管理如何插入或处理数据库数据的规则。

使用 DESC 查看 orders 表的表设计：DESC orders;

结果如下：除了列名外，还有各列的数据类型、是否NULL、KEY、Default、Extra，这些都是约束

（1）主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

表中任意列只要满足以下条件，都可以用于主键。

任意两行的主键值都不相同。
每行都具有一个主键值（即列中不允许 NULL 值）。
包含主键值的列从不修改或更新。
主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

创建表时定义主键：为列添加 PRIMARY KEY 关键字

CREATE TABLE Vendors 
( vend_id CHAR(10) NOT NULL PRIMARY KEY, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) NULL, vend_city CHAR(50) NULL, vend_state CHAR(5) NULL, vend_zip CHAR(10) NULL, vend_country CHAR(50) NULL 
);

修改表时添加主键：ADD CONSTRAINT PRIMARY KEY (vend_id)

ALTER TABLE Vendors 
ADD CONSTRAINT PRIMARY KEY (vend_id);

（2）外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。我们举个例子来理解外键。

Orders 表将录入到系统的每个订单作为一行包含其中。顾客信息存储在Customers 表中。Orders 表中的订单通过顾客 ID 与 Customers 表中的特定行相关联。顾客 ID 为 Customers 表的主键，每个顾客都有唯一的ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。

Orders 表中顾客 ID 列的值不一定是唯一的。如果某个顾客有多个订单，则有多个行具有相同的顾客 ID（虽然每个订单都有不同的订单号）。同时，Orders 表中顾客 ID 列的合法值为 Customers 表中顾客的 ID。

这就是外键的作用。在这个例子中，在 Orders 的顾客 ID 列上定义了一个外键，因此该列只能接受 Customers 表的主键值。

下面是在创建表时定义这个外键的方法。

CREATE TABLE Orders 
( order_num INTEGER NOT NULL PRIMARY KEY, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id) 
);CREATE TABLE OrderItems_new3
( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL,PRIMARY KEY (order_num, order_item),FOREIGN KEY (prod_id) REFERENCES products(prod_id)
);

修改表时添加外键：

ALTER TABLE Orders 
ADD CONSTRAINT 
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

（3）唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。

employees 表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员 ID（主键）。

雇员 ID 是主键，可以确定它是唯一的。你可能还想使 DBMS 保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义 UNIQUE 约束做到。唯一约束的语法类似于其他约束的语法。唯一约束既可以用 UNIQUE 关键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。

-- 唯一约束
ALTER TABLE XXX
ADD CONSTRAINT UNIQUE (XXX);-- 删除唯一约束，唯一约束是以索引方式创建的
ALTER TABLE XXX
DROP INDEX XXX;

（4）检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

检查最小或最大值。例如，防止 0 个物品的订单（即使 0 是合法的数）。
指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
只允许特定的值。例如，在性别字段中只允许 M 或 F。

换句话说，第 1 课介绍的数据类型限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS 本身将会拒绝任何无效的数据。下面的例子对 OrderItems 表施加了检查约束，它保证所有物品的数量大于 0。

CREATE TABLE OrderItems 
( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), item_price MONEY NOT NULL 
);

利用这个约束，任何插入（或更新）的行都会被检查，保证 quantity大于 0。

检查名为 gender 的列只包含 M 或 F，可编写如下的 ALTER TABLE 语句

ADD CONSTRAINT CHECK (gender IN ('M', 'F'));

2、索引

（1）什么是索引，为什么使用索引

假如要找出本书中所有的“数据类型”这个词，简单的办法是从第 1 页开始，浏览每一行。但以这种方式浏览整部书就不可行了。

随着要搜索的页数不断增加，找出所需词汇的时间也会增加。这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位置。为了搜索“数据类型”一词，可在索引中找出该词，确定它出现在哪些页中。然后再翻到这些页，找出“数据类型”一词。

使索引有用的因素是什么？很简单，就是恰当的排序。

数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。

但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS必须读出表中所有行（从第一行开始），看其是否匹配。

解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

在开始创建索引前，应该记住以下内容。

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

没有严格的规则要求什么应该索引，何时索引。大多数 DBMS 提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。

（2）创建索引

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

下面的语句在 Products 表的产品名列上创建一个简单的索引。

CREATE INDEX prod_name_ind 
ON Products (prod_name);

索引必须唯一命名。这里的索引名 prod_name_ind 在关键字 CREATE INDEX 之后定义。ON 用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

3、触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。

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

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

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

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

下面是触发器的一些常见用途。

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

下面的例子创建一个触发器，它对所有 INSERT 和 UPDATE 操作，将Customers 表中的 cust_state 列转换为大写。

DELIMITER //-- 用于 INSERT 操作的触发器
CREATE TRIGGER customer_state_after_insert
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN-- 将新插入的 cust_state 转为大写UPDATE CustomersSET cust_state = UPPER(NEW.cust_state)WHERE cust_id = NEW.cust_id;
END //-- 用于 UPDATE 操作的触发器
CREATE TRIGGER customer_state_after_update
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN-- 仅在 cust_state 发生变化时更新IF NOT (NEW.cust_state <=> OLD.cust_state) THENUPDATE CustomersSET cust_state = UPPER(NEW.cust_state)WHERE cust_id = NEW.cust_id;END IF;
END //DELIMITER ;