Mysql从入门到入魔——9. 游标、高级SQL特性

本篇主要内容

游标的使用、常见的约束及创建方式、INNODB 中常见索引的介绍及用法。话不多说，进入正题！

---

---

1. 游标

SQL 检索操作返回结果集，简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询， 它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了 游标之后，应用程序可以根据需要滚动或浏览其中的数据。

1.1 使用游标

游标使用的步骤：

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

声明游标后，可以根据需要频繁地 打开或关闭 游标。在游标打开时，可根据需要频繁地执行 取 操作。

注意：不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

1.2 创建游标

使用 DECLEAR 来创建游标，DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和 其他子句。

下面的语句定义了名为 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。

Order表中的信息：

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

这个存储过程中，使用 DECLARE 语句用来定义和命名游标。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

1.3 使用游标数据

使用 OPEN 语句来打开游标，CLOSE 语句关闭游标，在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索的数据（所需的列），数据存储的位置（定义的变量）。 它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 num 的局部变量中，并将查询 num 的结果。由于只检索到第一行，所以 num 的值为 ‘20005’。

下面，循环检索数据，从第一行到最后一行。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- 打开游标
	OPEN ordernumbers;
	-- 循环所有行
	REPEAT
		-- 获取第一行数据
		FETCH ordernumbers INTO num;
	-- 结束循环
	UNTIL done END REPEAT;
	-- 查询结果
	SELECT num;
	-- 关闭游标
	CLOSE ordernumbers;
END;
CALL processorder();

循环了结果集的所有行，所以 num 的值是最后一行的数据。

与上一个例子不同之处是，这个例子的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真。

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;

这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000' 出现时，SET done=true。SQLSTATE '02000'是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

1.4 游标的优缺点

优点：游标是面向集合与面向行的设计思想之间的一种桥梁，因为游标是针对行操作的，所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点：

• 速度较慢
• 会产生死锁现象
• 内存大

---

2. 约束

2.1 约束

DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，用 CREATE TABLE 或是 ALTER TABLE 语句。

2.2 主键

主键是一种特殊的约束，用来保证一列（或 一组列）中的值是唯一的，而且永不改动。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会 非常困难。

主键的条件：

• 任意两行的主键值都不相同。
• 每行都具有一个主键值（即列中不允许 NULL 值）。

创建表时定义主键。

CREATE TABLE teacher
(
	id INT(11) PRIMARY KEY,
	teacher_name VARCHAR(10)
);

使用 ALTER TABLE 添加主键。

ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);

删除主键约束。

ALTER TABLE teacher DROP PRIMARY KEY;

2.3 外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完 整性的极其重要部分。

下面新建 student 表并添加外键 teacher_id 与 teacher 表中的主键 id 进行关联。

在创建表的时定义外键。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加外键。

ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);

使用外键可以有效地防止意外删除，比如在上面两表中如果删除 teacher 表中的信息，如果该 id 在 student 表中也有出现，那么 Mysql 会防止删除操作。当然也可以启用级联删除的特性，那么在删除时就会删除所有相关信息。

删除外键。

ALTER TABLE student DROP FOREIGN KEY teacher_id_id;

2.4 唯一约束

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

• 表可包含多个唯一约束，但每个表只允许一个主键。
• 唯一约束列可包含 NULL 值。
• 与主键不一样，唯一约束不能用来定义外键。

在创建表的时定义唯一约束。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加唯一约束。

ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);

删除唯一性约束。

ALTER TABLE student DROP INDEX unique_id;

2.5 检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

常见用途：

• 检查最小或最大值。
• 指定范围。
• 只允许特定的值。

下面创建一个检查约束来限制性别列只能输入男、女。

在创建表的时定义检查约束。

CREATE TABLE student
(    
    stu_id INT(11) PRIMARY KEY,
	gender VARCHAR(1) CHECK(gender IN('男', '女'))
);

使用 ALTER TABLE 添加检查约束。

ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in ('男', '女'));

删除检查约束。

ALTER TABLE student DROP CHECK check_gender;

---

3. 索引

索引用来排序数据以加快搜索和排序操作的速度。主键数据总是排序的， 因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。这时候我们可以使用索引，在一个或多个列上定义索引，使 DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

索引特点：

• 索引提高检索的性能，但降低了数据增删改的性能。 在执行这些操作时，DBMS 必须动态地更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。取值不多的数据（如地区）不如具有更多可能值的数据（如姓名），能够更加体现索引的价值。
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
• 可以在索引中定义多个列（例如，国家 + 城市）。

3.1 普通索引

在创建表的时创建普通索引。

DROP TABLE IF EXISTS student;
CREATE TABLE student
(    
  	id INT(11),
	stu_name VARCHAR(10)
)

直接创建。

CREATE INDEX stu_id ON student(id);

修改表时创建。

ALTER TABLE student ADD INDEX stu_id(id);

删除索引。

DROP INDEX stu_id ON student;

3.2 唯一索引

唯一索引列值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。事实上，在许多场合，创建唯一索引的目的往往不是提高访问速度，而是为了避免数据出现重复。

CREATE UNIQUE INDEX stu_id ON student(id);

3.3 全局索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

CREATE FULLTEXT INDEX s_name ON student(stu_name);

3.4 多列索引

多列索引，即在数据表的多个字段上创建索引。

CREATE TABLE student
(    
  id INT(11),
	stu_name VARCHAR(10),
	email VARCHAR(20),
	INDEX info(stu_name, email)
);

在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 stu_name 字段)，索引才会被使用（最左前缀’原则）。如果没有用到第一字段，则索引不起任何作用。

-- 使用索引
SELECT * FROM student WHERE stu_name = '张三';
SELECT * FROM student WHERE stu_name = '李四' AND email = '11111@qq.com';
-- 未使用索引
SELECT * FROM student WHERE email = '11111@qq.com';

---

参考链接：
【详细解析】MySQL索引详解（ 索引概念、6大索引类型、key 和 index 的区别、其他索引方式）

这就是本文所有的内容了，如果感觉还不错的话。❤ 点个赞再走吧！！！❤


后续会继续分享《Mysql从入门到入魔》系列文章，如果感兴趣的话可以点个关注不迷路哦~。