高级特性——mysql必知必会（三）

视图

• 作用：
  
  1. 重用 sql语句。
  2. 简化复杂的sql。
  3. 保护数据，给予用户部分数据的访问权限而不是整个表。
  4. 更改数据的格式和表示。
• 视图不包含数据，是虚拟的表。每次使用视图时，必须处理查询的所有检索，所以复杂视图和嵌套视图会大大降低性能。
• 视图不能索引，也不能有关联的触发器。
• 视图通常用于查询，而不用于行的增删。

使用

• CREATE VIEW 创建视图。DROP VIEW 删除视图。
• 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 orders.order_num = orderitems.order_num;

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

• 格式化数据。

CREATE VIEW vendorlocation AS
SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT vend_title FROM vendorlocation;

• 过滤不需要的数据。

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT cust_id, cust_name, cust_email FROM customeremaillist;

• 使用计算字段处理数据。

CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;

SELECT order_num, expanded_price FROM orderitemsexpanded;

索引

• 使用索引可以更快的查找数据，但是更新一个包含索引的表需要更多时间，因为索引也需要更新。

使用

• CREATE INDEX 允许使用重复的值。
  CREATE UNIQUE INDEX 两行不能有相同的索引值。
• DESC 可以使用在列名后降序索引某列。

CREATE UNIQUE INDEX orderindex
ON orders(order_num DESC, order_date);

• 强制使用某索引。强制操作。

SELECT * FROM salaries FORCE INDEX(idx_emp_no)
WHERE emp_no = 10005;

• 删除索引。

ALTER TABLE orders DROP INDEX orderindex;

存储过程

• 作用：
  
  1. 封装操作。简化使用操作，减少错误，减少基础数据的访问从而提高安全性。
  2. 提高性能。存储过程比单独使用sql要快。
  3. 进行较复杂的逻辑操作。

使用

• CALL 调用存储过程，可以将存储过程理解为一个函数。
• mysql变量名以@开始。
• IN 表示传入参数， OUT 表示传出参数。INOUT 表示传入传出参数。
• INTO 表示将值赋予某个变量。
• DECLARE 声明变量。
• COMMENT 对存储过程说明。
• IF THEN 进行逻辑操作。

CREATE PROCEDURE ordertotal2(
    IN onumber INT,
    IN taxable boolean,
    OUT ototal DECIMAL(8,2)
) COMMENT 'obtain orer total with tax'
BEGIN
    -- var
    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*(1+taxrate/100) INTO total;
    END IF;

    SELECT total INTO ototal;
END;

CALL ordertotal2(20005, 0, @total);
SELECT @total;

• 删除存储过程。

DROP PROCEDURE IF EXISTS ordertotal2;

• 显示创造存储过程的语句。

SHOW CREATE PROCEDURE ordertotal;

• 显示存储过程状态。

SHOW PROCEDURE STATUS LIKE 'ordertotal%';

游标

• 对检索出来的结果集的行进行操纵，如下一行、前10行。
• 游标只能用于存储过程。

使用

• DECLARE 创建游标， OPEN、CLOSE 打开关闭游标。
• FETCH 游标访问结果集的每一行。
• CONTINUE HANDLER 表示在条件出现时执行代码，SQLSTATE ‘02000’ 是未找到条件，即没有更多行可以访问。
• REPEAT 和UNTIL 为循环语句。

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 ordertotal2(o, 1, t);
        INSERT INTO ordertotals(order_num, total) VALUES(o, t);
    UNTIL done END REPEAT;
    CLOSE ordernumbers;
END;

CALL processorders;

触发器

• 针对关联的表， 在DELETE 、INSERT 、UPDATE 之前或者之后执行操作。是一种特殊的存储过程。
• 只有表支持触发器，视图不支持。

使用

• CREATE TRIGGER 创建触发器。
• NEW 访问操作之后的虚拟表。
  OLD 访问操作之前的虚拟表。
• 可以使用BEGIN END 执行多条语句。

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

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;

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

• 删除触发器。

DROP TRIGGER updatevendor;

事务处理

• 事务处理可以用来维护数据库的完整性，保证成批的sql操作要么完全执行，要么完全不执行。
• START TRANSACTION 标识事务的开始。
• ROLLBACK 用于撤销sql语句，但是不能撤销CREATE,DROP,SELECT 操作。

START TRANSACTION;
DELETE FROM ordertotals;
ROLLBACK;

• 一般sql语句隐含提交（implicit commit），即自动提交。但是事务处理中，不会隐含提交，需要使用COMMIT 。COMMIT或者ROLLBACK之后，事务会自动关闭，将来更改会隐含提交。

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

• SAVEPOINT 可以实现部分回退功能。
  保留点在事务处理完成之后自动释放，也可以使用RELEASE SAVEPOINT 明确释放保留点。

SAVEPOINT delete1;
ROLLBACK TO delete1;

• autocommit标识是否提交更改，针对每个连接而不是服务器。
  SET autocommit = 0 设为不自动提交更改。SET autocommit = 1 设为自动提交更改。

字符集和校对

• 字符集：字母和符号的集合。
  编码：字符集成员的内部表示。
  校对：规定字符如何比较。
• 一个字符集可以有不止一种校对，有些校对区分大小写（_cs结尾区分大小写，_ci结尾不区分大小写）。
• 通过CHARACTER SET 指定字符集，COLLATE 指定校对。
  如果不指定，则使用数据库的默认值。
  也可以为每个列设置字符集和校对。

CREATE TABLE mytable
(
    col1 INT,
    col2 VARCHAR(10),
    col3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci,
    PRIMARY KEY (col1)
)
CHARACTER SET hebrew
COLLATE hebrew_general_ci;

• 可以在SELECT中临时指定校对，区分大小写，从而影响最终排序。

SELECT * FROM mytable
ORDER BY col3
COLLATE latin1_general_cs;

安全管理

• 用户应该对他们所需要的数据具有适当的权限，既不能多也不能少。
• 访问控制有助于防止无意的错误。尽量不要使用root。

设置账户

• 查找账号列表。账号信息存储在mysql数据库的user表中。账号为user@host，host可以为%，表示不限制登录主机。

USE mysql;
SELECT user FROM user;

• CREATE 创建账号， IDENTIFIED BY PASSWORD 给出口令。

CREATE USER aaa IDENTIFIED BY 'aaa';

• 重命名账号。

RENAME USER aaa TO bbb;

• 删除账号。

DROP USER bbb;

• 修改密码。FOR省略，则更新当前登录用户的口令。

SET PASSWORD FOR aaa = PASSWORD('bbb');
SET PASSWORD = PASSWORD('bbb);

设置账户权限

• 设置访问权限。GRANT 赋予权限，REVOKE 取消权限。

GRANT SELECT,INSERT ON mysql_crash_course.* TO aaa;

REVOKE INSERT ON mysql_crash_course.* FROM aaa;

• 权限的范围，ON之后。
  
  • 整个服务器。 ON *.* 。
  • 整个数据库。 ON database.* 。
  • 特定的表。 ON database.table 。
  • 特定的列。GRANT SELECT(col1, col2) ON database.table 。
  • 特定的存储过程。GRANT EXECUTE ON PROCEDURE database.procedureName 。
• 权限的类型。
  
  

性能分析

EXPLAIN

• 分析sql语句的执行及数据库索引的使用。
  
• 详细解释：https://blog.csdn.net/jathamj/article/details/53909614