MySQL 笔记

1. 数据库操作

# 创建数据库
CREATE DATABASE MySQL;

# 选择数据库
USE MySQL;

# 查看数据库
SHOW DATABASES;

# 查看当前数据库的表
SHOW TABLES;

# 显示创建特定数据库或表的语句
SHOW CREATE DATABASE MySQL;
SHOW CREATE TABLE tb;

# 显示授予用户的安全权限
SHOW GRANTS;

# 显示服务器错误或警告消息
SHOW ERRORS;
SHOW WARNINGS;

2. 表操作

2.1 创建和操纵表

# 创建表 (IF NOT EXISTS 会先检查是否有重复表名)
CREATE TABLE IF NOT EXISTS customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,	# 每个表只允许一个 AUTO_INCREMENT
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

# 为创建多个列组成的主键, 用逗号分隔
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1, # 指定默认值, 只支持常量
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

# 显示表结构
SHOW COLUMNS FROM customers;

• InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索
• MEMORY 在功能上等同于 MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
• MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

# 更新表结构

# 添加一列
ALTER TABLE vendors
ADD vend_phone CHAR(20);

# 删除一列
ALTER TABLE vendors
DROP COLUMN vend_phone;

# 删除表
DROP TABLE vendors;

# 重命名表
RENAME TABLE vendors TO vend,
			 customers TO cust;

2.2 插入数据

# 插入一行
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');

# 插入多行 (LOW_PRIORITY 降低INSERT语句优先级)
INSERT LOW_PRIORITY INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com'),(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');

# 插入检索出的数据
INSERT INTO customers(xxx) SELECT xxx FROM tb;

2.3 更新和删除数据

# 更新指定行
UPDATE customers
SET cust_email = 'elmer@fudd.com',
	cust_name = 'The Fudds'
WHERE cust_id = 10005;

# 如果使用UPDATE更新多行, 在其中一行或多行出错, 则整个UPDATE操作被取消, 可用 IGNORE 关键字忽略
UPDATE IGNORE customers...

# 删除指定列
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

# 删除指定行(不会删除表本身)
DELETE FROM customers
WHERE cust_id = 10006;

# 删除所有行 (实际上是删除表在重建一个, 比较快)
TRUNCATE TABLE;

2.4 检索数据

# 检索单列
SELECT cust_name
FROM customers;

# 检索多列
SELECT cust_id, cust_name
FROM customers;

# 检索所有列
SELECT *
FROM customers;

# 检索出有不同值的列
SELECT DISTINCT vend_id
FROM products;

# 限制显示的行数
SELECT prod_name
FROM products
LIMIT 5;	# 返回不多于5行
LIMIT 5,4;	# 返回从行5开始的4行, 第1行为行0
LIMIT 4 OFFSET 5; # 上面的另一种写法, 从行5开始取4行

# 使用完全限定的表名和库名
SELECT products.prod_name
FROM MySQL.products;

2.5 排序检索数据

# 按多个列升序排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

# 降序排序 (DESC关键字只应用到直接位于其前面的列名)
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

2.6 过滤数据

# 使用WHERE子句过滤掉不满足条件的行
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10
ORDER BY prod_price;	# 在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

# 空值检查
SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

2.7 数据过滤

# AND 操作符
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

# OR 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

# AND 和 OR组合 (AND优先级高)
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

# IN 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

# NOT 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

2.8 用通配符进行过滤

# 百分号(%)通配符, %表示任何字符出现任意次数
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';	# 找出所有以词jet开头的产品

# 下划线(_)通配符, 匹配单个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

2.9 用正则表达式进行搜索

# 基本字符匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

# 匹配区分大小写
SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY 'JetPack .000';

# 进行OR匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

# 匹配特定字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton' #[123]定义一组字符, 匹配1或2或3, [^123]匹配123之外的字符
ORDER BY prod_name;

# 匹配范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

# 匹配特殊字符
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.' # \\f(换页) \\n(换行) \\r(回车) \\t(制表) \\v(纵向制表)
ORDER BY vend_name;

# 匹配多个实例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;

# 定位符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]' # ^匹配串的开始
ORDER BY prod_name;

2.10 创建计算、拼接字段

# 计算字段并不实际存在于数据表中, 而是运行时在SELECT语句内创建的
# 拼接字段, 将值联结到一起构成单个值
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

2.11 执行算术计算

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

2.12 使用数据处理函数

# 文本处理函数
Left(): 返回串左边的字符
Length(): 返回串的长度
Locate(): 找出串的一个子串
Lower(): 将串转换为小写
LTrim(): 去掉左边的空格
Right(): 返回右边的字符
RTrim(): 去掉右边的空格
Soundex(): 返回串的SOUNDEX值
SubString(): 返回子串的字符
Upper(): 将串转换为大写

# SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法, 它匹配所有发音类似的字段

# 日期和时间处理函数
AddDate(): 增加一个日期(天、周等)
AddTime(): 增加一个时间(时、分等)
CurDate(): 返回当前日期
CurTime(): 返回当前时间
Date(): 返回日期时间的日期部分
DateDiff(): 计算两个日期之差
Date_Add(): 高度灵活的日期运算函数
Date_Format(): 返回一个格式化的日期或时间串
Day(): 返回一个日期的天数部分
DayOfWeek(): 对于一个日期, 返回对应的星期几
Hour(): 返回一个时间的小时部分
Minute(): 返回一个时间的分钟部分
Month(): 返回一个日期的月份部分
Now(): 返回当前日期和时间
Second(): 返回一个时间的秒部分
Time(): 返回一个日期时间的时间部分
Year(): 返回一个日期的年份部分

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

# 数值处理函数
Abs(): 返回一个数的绝对值
Cos(): 返回一个角度的余弦
Exp(): 返回一个数的指数值
Mod(): 返回除操作的余数
Pi(): 返回圆周率
Rand(): 返回一个随机数
Sin(): 返回一个角度的正弦
Sqrt(): 返回一个数的平方根
Tan(): 返回一个角度的正切

2.13 汇总数据

# 聚集函数(运行在行组上, 计算和返回单个值的函数)
AVG() : 返回某列的平均值
COUNT() : 返回某列的行数
MAX() : 返回某列的最大值
MIN() : 返回某列的最小值
SUM() : 返回某列值之和

# AVG()
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

# COUNT() 有两种使用方式
# 1. 使用COUNT(*)对表中行的数目进行计数, 不管表列中包含的是空值(NULL)还是非空值
# 2. 使用COUNT(column)对特定列中具有值的行进行计数, 忽略NULL值
SELECT COUNT(*) AS num_cust
FROM customers;

# MAX()
SELECT MAX(prod_price) AS max_price
FROM products;

# MIN()
SELECT MIN(prod_price) AS min_price
FROM products;

# SUM()
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

# 聚集不同值 (指定DISTINCT参数)
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

# 组合聚集函数
SELECT COUNT(*) AS num_items,
	   MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg
FROM products;

2.14 分组数据

# 分组允许把数据分为多个逻辑组, 以便能对每个组进行聚集计算
# 创建分组(按vend_id排序并分组数据, 对每个vend_id计算num_prods一次)
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

# 注意: GROUP BY 子句必须出现在WHERE子句之后, ORDER BY 子句之前

# 过滤分组(WHERE过滤行, 而HAVING过滤分组)
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

# HAVING和WHERE的差别: WHERE在数据分组前进行过滤, HAVING在数据分组后进行过滤, 它们也可以同时使用
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
# 先过滤掉prod_price < 10的数据, 然后根据vend_id进行分组, 对于每个分组, 只看个数大于等于2的项

# 分组和排序
# 一般在使用GROUP BY子句时, 应该也给出ORDER BY子句, 这是保证数据正确排序的唯一方法, 千万不要仅依赖GROUP BY排序数据
SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

# SELECT子句顺序: SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT

2.15 使用子查询

# 可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');
                    
                    
# 在WHERE子句中使用子查询, 应该保证SELECT语句具有与WHERE子句中相同数目的列
# 虽然子查询一般与IN操作符结合使用, 但也可以用于测试等于(=)、不等于(<>)等

# 作为计算字段使用子查询
SELECT cust_name,
       cust_state,
       (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

2.16 联结表

# 外键: 外键为某个表中的一列, 它包含另一个表的主键值, 定义了两个表之间的关系
# 分解数据为多个表能更有效地存储, 更方便地处理, 并且具有更大的可伸缩性, 如果数据存储在多个表中
# 需要使用联结, 用来在一条SELECT语句中关联表
# 联结不是物理实体, 它在实际的数据库表中不存在, 在一条SELECT语句中联结几个表时, 相应的关系是在运行中
# 构造的

# 创建联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

# 笛卡尔积 (由没有联结条件的表关系返回的结果, 也叫叉联结)
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;

# 注意: 应该保证所有联结都有WHERE子句, 否则MySQL将返回比想要的数据多得多的数据.应该保证WHERE子句的正确性

# 内部联结(之前的联结称为等值联结, 它基于两个表之间的相等测试)
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

# 联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
# 性能考虑: MySQL在运行时关联指定的每个表以处理联结, 这种处理可能是非常耗费资源的, 因此应该仔细, 不要
# 联结不必要的表, 联结的表越多, 性能下降得越厉害

2.17 创建高级联结

# 自联结 (首先联结两个表, 然后按第二表中的prod_id过滤数据, 返回所需的数据)
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

# 自然联结 (排除多次出现, 使每个列只返回一次)
# 通过对表使用通配符 (SELECT *), 对所有其他的列使用明确的子集来完成
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

# 外部联结 (有时候需要包含没有关联行的那些行)
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
# 与内部联结关联两个表中的行不同的是, 外部联结还包含没有关联的行, 在使用OUTER JOIN语法时, 必须
# 使用 RIGHT 或 LEFT 关键字指定包括所有行的表, 上面是从左边表选择所有行

# 使用带聚集函数的联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

2.18 组合查询

# 创建组合查询
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

# UNION中的每个查询必须包含相同的列、表达式或聚集函数
# UNION从查询结果集自动去除了重复的行(行为与单条SELECT语句中使用多个WHERE子句条件一样)

# 如果想要包含重复行, 可以使用 UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

# 对组合查询结果排序 (只能使用一条ORDER BY子句且必须出现在最后一条SELECT语句之后)
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;

2.19 全文本搜索

# 启用全文本搜索支持(一般在创建表时启用全文本搜索, CREATE TABLE语句接受FULLTEXT子句)
CREATE TABLE productnotes
(
    note_id	 int	NOT NULL AUTO_INCREMENT,
    prod_id  char(10) NOT NULL,
    note_date datetime NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

# 进行全文本搜索(使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列, Against()指定要使用的搜索表达式)
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

# 传递给Match()的值必须与FULLTEXT()定义中的相同, 如果指定多个列, 则必须列出它们
# 全文本搜索的一个重要部分就是对结果排序, 具有较高等级的行先返回

# 使用查询扩展 (找出可能与搜索有关的所有其他行)
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

# 布尔文本搜索
# 可以提供要匹配的词, 要排斥的词, 排列信息(优先级), 表达式分组等
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); # 包含heavy且不包含rope开头

3. 使用视图

3.1 为什么使用视图

• 重用 SQL 语句
• 简化复杂的 SQL 操作
• 使用表的组成部分而不是整个表
• 保护数据
• 更改数据格式和表示

3.2 视图的规则和限制

• 视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
• 对于可以创建的视图数目没有限制
• 为了创建视图，必须具有足够的访问权限
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
• ORDER BY 可以用在视图中，但如果从该视图检索数据的 SELECT 语句中也含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用

3.3 使用视图

• 视图用 CREATE VIEW 语句来创建
• 使用 SHOW CREATE VIEW viewname; 来查看创建视图的语句
• 用 DROP 删除视图，其语法为 DROP VIEW viewname
• 更新视图时，可以先用 DROP 再用 CREATE ,也可以直接用 CREATE OR REPLACE VIEW 。如果要更新的视图不存在，则第二条更新语句会创建一个视图，如果要更新的视图存在，这第二条更新语句会替换成原有视图

# 利用视图简化复杂的联结
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

# 检索视图
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

# 用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

# 用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

# 使用视图与计算字段
CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;

3.4 更新视图

更新一个视图将更新其基表，但并非所有视图都是可更新的。如果视图定义中有以下操作，则不能进行视图的更新：

• 分组（使用 GROUP BY 和 HAVING ）
• 联结
• 子查询
• 并
• 聚集函数（ Min() 、Count() 、Sum() 等）
• DISTINCT
• 导出（计算）列

4. 存储过程

4.1 为什么使用存储过程

存储过程就是为以后的使用而保存的一条或多条 MySQL 语句的集合，可将其视为批文件，优点：

• 通过把处理封装在容易使用的单元中，简化复杂的操作
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性
• 简化对变动的管理
• 提高性能，使用存储过程比使用单独的 SQL 语句要快
• 存在一些职能用在单个请求中的 MySQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

4.2 使用存储过程

# 创建存储过程
CREATE PROCEDURE productpricing() # 如果接受参数, 将在()中列举出来
BEGIN	# 限定存储过程体
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END; # 没有返回数据,因为这段代码并未调用存储过程

# 执行存储过程
CALL productpricing();

# 删除存储过程
DROP PROCEDURE productpricing; # 或者 DROP PROCEDURE IF EXISTS productpricing;

# 使用参数
CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN
	SELECT Min(prod_price)
	INTO pl
	FROM products;
	SELECT Max(prod_price)
	INTO ph
	FROM products;
	SELECT Avg(prod_price)
	INTO pa
	FROM products;
END;
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @pricehigh, @pricelow, @priceaverage; # 获得变量值

# ordertotal接受订单号并返回该订单的合计
CREATE PROCEDURE ordertotal(
	IN onumber INT,
    OUT ototal DECIMAL(8, 2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal;
END;
CALL ordertotal(20005, @total);
SELECT @total;

4.3 建立智能存储过程

-- Name: ordertotal
-- Parameters: onumber = order number
-- 		       texable = 0 if not taxable, 1 if taxable
--             ototal = order total variable
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	-- 定义局部变量
	DECLARE total DECIMAL(8, 2);
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	IF taxable THEN
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	SELECT total INTO ototal;
END;

# COMMENT关键字不是必须的, 但如果给出，将在SHOW PROCEDURE STATUS的结果中显示
CALL ordertotal(20005, 1, @total);
SELECT @total;

# 检查存储过程
SHOW CREATE PROCEDURE ordertotal;

5. 使用游标

5.1 游标

​ 有时，需要在检索出来的行中前进或后退一行或多行，游标是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集，并且 MySQL 游标只能用于存储过程（和函数）

​ 使用游标的步骤如下：

• 在使用前必须声明（定义）它，定义要使用的 SELECT 语句
• 一旦声明后，必须打开游标以供使用，用前面定义的 SELECT 语句把数据实际检索出来
• 对于有填有数据的游标，根据需要取出（检索）各行
• 在结束游标使用时，必须关闭游标

# 创建游标
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

# 打开和关闭游标
OPEN ordernumbers;
CLOSE ordernumbers;

5.2 使用游标数据

​ 在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行

CREATE PROCEDURE processorders()
BEGIN
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	OPEN ordernumbers;
	
	FETCH ordernumbers INTO o;	# 从第一行开始检索
	
	CLOSE ordernumbers;
END;

# 循环检索数据，从第一行到最后一行
CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	# 定义continue handler, 当repeate由于没有更多的行供循环而不能继续时，出现'02000'这个条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	OPEN ordernumbers;
	
	REPEAT
	
		FETCH ordernumbers INTO o;	# 从第一行开始检索
		
	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

DECLARE 语句的发布存在特定的次序，用 DECLARE 语句定义的局部变量在定义任意游标或句柄之前定义，而句柄必须在游标之后定义

# 综合样例
CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8, 2);
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8, 2));
	
	OPEN ordernumbers;
	
	REPEAT
		FETCH ordernumbers INTO o;
		
		CALL ordertotal(o, 1, t);
		
		INSERT INTO ordertotals(order_num, total)VALUES(o, t);
	
	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

6. 使用触发器

6.1 触发器

触发器是 MySQL 响应 DELETE 、INSERT 和 UPDATE 语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和END 语句之间的一组语句）

6.2 创建触发器

在创建触发器时，需要给出4条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（ DELETE 、INSERT 或 UPDATE ）
• 触发器何时执行（处理之前或之后）

应该尽量保持每个数据库的触发器名唯一（兼顾其它 DBMS ），只有表才支持触发器，视图不支持（临时表也不支持），每个表最多支持 6 个触发器，单一触发器不能与多个事件或多个表关联

# 在新行插入之后执行 SELECT 语句
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @ee; # MySQL5以后不支持触发器返回结果集

如果 BEFORE 触发器失败，则 MySQL 将不执行请求的操作，如果 BEFORE 触发器或语句本身失败，MySQL 将不执行 AFTER 触发器

6.3 删除触发器

DROP TRIGGER newproduct;

6.4 INSERT 触发器

• 在 INSERT 触发器代码内，可引用一个名为 New 的虚拟表，访问被插入的行
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自动生成值

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @num;

6.5 DELETE 触发器

• 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行
• OLD 中的值全部都是只读的，不能更改

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

6.6 UPDATE 触发器

• 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）
• OLD 中的值全都是只读的，不能更新

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

7. 管理事务处理

7.1 事务处理

InnoDB 引擎支持事务处理，可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的 MySQL 操作，以保证数据库不包含不完整的操作结果，如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态

• 事务（transaction）：指一组SQL语句
• 回退（rollback）：指撤销指定SQL语句的过程
• 提交（commit）：指将未存储的SQL语句结果写入数据库表
• 保留点（savepoint）：指事务处理中设置的临时占位符，你可以对它发布回退

7.2 控制事务处理

# 标识事务的开始
START TRANSACTION;

# 使用 ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK; # 回滚DELETE语句
SELECT * FROM ordertotals;

ROLLBACK 只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后），事务处理用来管理 INSERT 、UPDATE 和 DELETE 语句，不能回退 SELECT 、CREATE 或 DROP 操作

7.3 使用COMMIT

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

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; # 提交之后就不能回滚了(当COMMIT或ROLLBACK语句执行后，事务会自动关闭)

7.4 使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点

# 创建保留点
SAVEPOINT delete1;

# 回退到保留点
ROLLBACK TO delete1;

# 释放保留点
RELEASE SAVEPOINT delete1;

7.5 事务的四大特性（ACID）

• 原子性（atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚
• 一致性（consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏
• 隔离性（isolation）：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其它事务的操作所干扰，多个并发事务之间要相互隔离
• 持久性（durability）：一个事务一旦被提交了，那么对数据库中的改变就是永久性的

8. 安全管理

8.1 管理用户

# 查看所有用户账户
USE mysql;
SELECT user FROM user;

# 创建用户账号
CREATE USER ben IDENTIFIED BY 'abc';

# 重命名一个用户账号
RENAME USER ben TO bforta;

# 删除一个用户账号
DROP USER bforta;

# 更改口令
SET PASSWORD FOR bforta = Password('efg'); # 默认更改root用户

8.2 权限管理

# 查看用户权限
SHOW GRANTS FOR bforta;

# 设置用户权限
GRANT SELECT ON crashcourse.* TO bforta;	# 允许对crashcourse库下的所有表执行SELECT操作

# 撤销特定权限
REVOKE SELECT ON crashcourse.* TO bforta;

# 一次设置多个权限
GRANT SELECT, INSERT, DELETE ON crashcourse.* TO bforta;

8.3 远程连接mysql问题

cd /etc/mysql/mysql.conf.d/
vim mysqld.cnf

# 将 bind-address = 127.0.0.1 注释掉

# 重新启动mysql服务
service mysql restart;

9. 索引

• 索引，就是将数据表中某一列/某几列的值取出来构造成便于查找的结构进行存储，生成数据表的 目录 。当我们进行数据查询的时候，则先在 目录 中进行查找得到对应的数据的地址，然后再到数据表中根据地址快速获取数据记录，避免全表扫描
• 在 Linux 系统，MySQL 数据存放在 /var/lib/mysql 目录下，而索引数据根据引擎类型有以下存储方法：
  • InnoDB 存储引擎的表：将索引和数据都存放在同一个文件里（*.ibd)
  • MyISAM 存储引擎的表：索引和数据分开两个文件来存储（ .MYI 和 .MYD）

9.1 索引的分类

MySQL 中的索引，根据创建索引的列的不同，可以分为：

• 主键索引 ：在数据表的主键字段创建的索引，这个字段必须被 primary key 修饰，每张表只能有一个主键
• 唯一索引 ：在数据表中的唯一列创建的索引（unique），此列的所有值只能出现一次，可以为 NULL
• 普通索引 ：在普通字段上创建的索引，没有唯一性的限制
• 组合索引 ：两个及以上字段联合起来创建的索引
• 全文索引 ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

9.2 创建索引

-- 创建了唯一索引(列的值不能重复)
-- create unique index <index_name> on 表名(列名)
CREATE UNIQUE INDEX index_test1 ON tb_testindex(tid);

SHOW INDEX FROM tb_testindex;

-- 创建普通索引
-- create index <index_name> on table_name(column_name)
CREATE INDEX index_test2 on tb_testindex(name);

-- 创建组合索引（最好不要查过五个列）
CREATE INDEX index_test3 on tb_testindex(tid, name);
-- 联合（组合）索引满足最左前缀匹配
select * from tb_testindex where tid = 100;	# 命中
select * from tb_testindex where name = "hello";	# 未命中
select * from tb_testindex where name = "hello" AND tid = 100; # 全命中

• 索引创建完成之后无需调用，当根据创建索引的列进行数据查询的时候，会自动使用索引，组合索引需要根据创建索引的所有字段进行查询时触发

• 在命令行窗口中可以查看语句的查询规划

  explain select * from tb_testindex where tid = 25000\G;

9.3 删除索引

-- 删除索引: drop index <index_name> on table_name(column_name)
DROP INDEX index_test3 ON tb_testindex;

9.4 索引使用总结

优点：

• 大大降低了数据服务器在执行查询操作时扫描的数据，提高查询效率
• 索引可以避免服务器排序、将随机 I/O 变成顺序 I/O

缺点：

• 索引是根据数据表列的值创建的，当数据表中数据发生 DML（增删改） 操作时，索引也需要更新
• 索引文件也会占用磁盘空间

注意事项：

• 数据表中数据不多时，全表扫描可能更快，不要使用索引
• 数据量很大，但是 DML 操作很频繁时，不建议使用索引
• 不要在数据重复度高的列上创建索引（性别）
• 创建索引之后，要注意查询 SQL 语句的编写，避免索引失效