数据库操作（四）—— 高级篇

高级操作第 22 - 30 章

视图

1、def：虚拟表，仅包含使用时动态检索的一个 SQL 查询

2、规则

必须唯一命名
需要访问权限
视图可嵌套
视图中的 ORDER BY 语句会被检索中的 ORDER BY（如果有）覆盖
不可添加索引或触发器、默认值，但可与表一同使用

3、使用

创建：CREATE VIEW
查看所创建视图：SHOW CREATE VIEW viewname
删除视图：DROP VIEW viewname
过滤视图、简化计算字段

4、重新格式化检索出的数据

把多次需要的结果通过转化为视图存放

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(', RTrim(vend_country),')')
		AS vend_title
FROM vendors
ORDER BY vend_name;

再次检索出数据：

1 2	SELECT * FROM vendorlocations;

5、更新视图：CREATE OR REPLACE VIEW

以下情况视图不可更新：
- 存在 GRUOP BY 分组
- 联结、子查询、并
- 聚集函数、DISTINCT
- 导出计算列

存储过程

1、使用原因

封装处理，简化操作、提高性能
无需反复建立处理步骤，保证了数据完整性
简化变动管理 - 安全性

2、执行存储过程：CALL

1
2
3

CALL productpricing(@pricelow,
                   @pricehigh,
                   @priceaverage);

mysql 中所有变量名都必须以 @开始
mysql 中注释以–开头

3、创建存储过程：CREATE PROCEDURE

创建一个新的存储过程，没有返回数据：

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

DELEMITER：指定新的语句分隔符，避免；被 mysql 命令行实用程序解释为存储过程的成分

4、删除存储过程：DROP PROCEDURE

5、使用参数

存储过程的参数允许的数据类型与表中一致
- 记录集不是允许的类型。记录集：从指定数据库中检索到的数据的集合
创建布尔值：IN 参数名 BOOLEAN
IF / ELSEIF / ELSE 子句

6、检查存储过程：SHOW CREATE PROCEDURE

获取详细信息的存储过程列表：SHOW PROCUDURE STATUS

游标 cursor

1、使用游标

必须先声明再打开，结束后关闭
创建与访问：DECLARE / FETCH
打开与关闭：OPEN / CLOSE CURSOR

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders
END;

-- 打开游标
OPEN ordernumbers;
-- 关闭游标
CLOSE ordernumbers;
-- 检索order_num列到一名为o的局部变量中（不处理检索出的数据）
FETCH ordernumbers INTO o;

UNTIL done END REPEAT：反复执行直到布尔值 done 为真
- 手动退出：LEAVE
CONTINUE HANDLER：条件出现时才被执行

1 2	DECLARE done BOOLEAN DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;

说明在 SQLSTATE ‘02000’出现时，把 done 的值从默认值 0 设置为 1

2、DECLARE 的次序

定义顺序：DECLARE 定义的局部变量 - 游标 - 句柄
- 句柄（Handle）：用来标识对象或者项目的标识符

触发器

1、需给出信息：

最好每个数据库中触发器名称唯一
关联表
应响应活动与执行时机

2、创建：CREATE TRIGGER

相应以下任意语句：DELETE INSERT UPDATE

1 2	CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'

仅表可用触发器，视图不行
- 每张表最多支持 6 个触发器（3 种语句 * 2 个时机）
- 某时机前触发 BEFORE 失败：则若有 AFTER 触发器，默认不执行

3、删除：DROP TRIGGER

4、使用

可引用 NEW 虚拟表，以访问被插入行
- 可在 BEFORE INSERT 触发器中更新 NEW 表中的值
- 对于 AUTO_INCREMENT，INSERT 前置 0，后为新值
DELETE 触发器可引用 OLD 访问被删除行
- 仅只读，不可再更新
- BEFORE DELETE 相对 AFTER ~ 优点：不可存档，DELETE 本身将被放弃

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;

UPDATE 触发器可用 NEW、OLD
- BEFORE UPDATE 触发器中，NEW 中可改动的 UPDATE 值可被更新
- OLD 表仅可读，不可更新
触发器是自动执行的，可能需要特殊权限
不支持在触发器中使用 CALL 语句

5、展望

内容仍较初级，有诸多改进空间
可用于保证数据一致性、创建审计跟踪

事务处理 transaction processing

1、一些概念

事务：一组 SQL 语句
回退 rollback：撤销指定语句的过程
（隐含）提交 commit：（自动）将未存储语句写入表
保留点 savepoint：设置的临时占位符（placeholder），可回退

2、使用事务处理

-- 回退
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

-- 提交（例中两个DELETE都返回成功才COMMIT）
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

-- 使用保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;

回退后不再显示空结果
三种操作执行流结束后事务均会自动关闭
保留点个数越多越好，灵活回退

3、更改默认的提交行为

仅针对每个连接，不针对服务器

1 2	-- 此时不自动提交 SET autocommit = 0;

* 全球化与本地化

1、使用

显示字符集、校对顺序：SHOW CHARACTER SET / COLLATION；
显示特定字符集：SHOW VARIABLES LIKE ‘character%’；
与创建表不同的校对顺序查询

1 2	SELECT * FROM customers; ORDER BY lastname, firstname, CLLATE latin1_general_cs;

2、串在字符集间的转换：Cast () / Conver () 函数

安全管理

1、访问控制

谨慎使用 root 登录
给予用户适当的访问权

2、管理用户

获取所用用户列表

1 2	USE mysql； SELECT user FROM user；

创建用户账号
- IDENTIFIED BY：指定纯文本口令
- 或使用 GRANT / INSERT 语句（不好）

1	CREATE USER bob IDENTIFIED BY 'mypassword'

重命名：RENAME USER
删除：DROP USER
设置管理权限：SHOW GRANTS FOR 用户名
- * 表示无权限

3、GRANT / REVOKE 语句

需给出的信息：需授予权限所需库或表用户名

-- 给予权限
SHOW GRANTS FOR bob;
GRANT SELECT ON crashcourse.* to bob;
-- 撤销权限
REVOKE GRANTS FOR bob;
REVOKE SELECT ON crashcourse.* to bob;

可更改权限（P202）
可提前设置授权：对所涉及对象，可不存在就设定权限
简化多次授权

1	GRANT SELECT, SELECT ON crashcourse.* TO bob;

4、更改口令：SET PASSWORD

1	SET PASSWORD FOR bob = PASSWORD('newpassword');

* 数据库维护

1、备份

使用 mysqldump 转储到外部文件
使用 mysqlhotcopy 复制到另一数据库
使用 BACKUP TABLE / SELECT INTO OUTFILE 转储
- 复原：RESTORE TABLE
- 刷新：FLUSH TABLES，保证所有数据及索引被写入磁盘

2、维护

ANALYZE TABLE：检查表键是否正确
CHECK TABLE：检查各类问题
- CHANGED：最后一次检查以来改动的表
- ENTENDED：最彻底检查
- FAST：未正常关闭表
- MEDIUM：检验所有删除链接及键检验
- QUICK：快扫

3、诊断启动问题

–safe-mode

–help、–verbose：显示全文本消息

4、日志文件

错误日志：hostname.err
查询日志：hostname.log
缓慢查询日志：hostname-slow.log

改善性能

1、EXPLAIN 语句：使 mysql 解释如何解释一条 SELECT 语句

2、DELAYED 关键字：把控制立即返回给调用程序

高级操作 第 22 - 30 章

视图