《MySQL必知必会》读书笔记4

第二十二章 使用视图

• 视图是一张虚拟表
• 为什么使用视图
  • 重用SQL语句
  • 简化复杂的SQL操作
  • 使用表的组成部分而不是整张表
  • 保护数据，只允许访问表的部分而不是整张表
  • 更改数据格式和表示，视图可以返回与底层表格式不同的数据

• 视图的使用规则

  • 唯一命名
  • 视图数目没有限制
  • 创建视图需要权限
  • 视图可以嵌套，可以利用其它视图来构造视图
  • 视图可以使用 ORDER BY，但是 SELECT 中的 ORDER BY 会覆盖视图中的 ORDER BY
  • 视图不能索引，也不能有关联的触发器或默认值
  • 视图可以和表一起使用，可以联结表和视图

• 使用视图

  • CREATE VIEW 创建视图
  • SHOW CREATE VIEW viewname; 查看所创建的视图
  • DROP 删除视图
  • 更新视图，可以先删除再创建，也可以使用 CREATE OR PEPLACE VIEW

• 利用视图简化复杂的联结：创建临时表，增加灵活性

• 用视图重新格式化检索出的数据：视图可以根据需要创建列名

• 利用视图过滤不想要的数据：通过视图过滤数据后代替表使用

• 利用视图简化计算字段

• 视图一般用于检索，不用于更新

第二十三章 使用存储过程

• 存储过程是一条或者多条SQL语句的集合，类似于批文件

• 使用存储过程的理由（简单、安全、高性能）

  • 简化复杂操作
  • 防止错误
  • 简化对变动的管理
  • 提高性能

• 创建和使用存储过程都需要权限

• 创建存储过程

  //创建一个名为productpricing的存储过程
  CREATE PROCECURE productpricing()
  BEGIN
      SELECT Avg(prod_price) AS priceaverage
      FROM products;
  END
  

• 执行存储过程

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

• 使用存储过程

  CALL productpricing();  
  

• 删除存储过程

  DROP PROCEDURE productpricing IF EXISTS;
  

第二十四章 使用游标

• 游标（cursor）是一个存储在MySQL服务器上面的数据库查询，可以在检索的结果集中滚动到某一行进行操作

• 使用游标

  • 声明游标
  • 打开游标
  • 使用游标取出需要的行并处理
  • 关闭游标

第二十五章 使用触发器

• 触发器是在事件发生时自动执行的一条MySQL语句

  • DELETE
  • INSERT
  • UPDATE

• 创建触发器

  • 唯一的触发器名
  • 关联的表
  • 响应的活动（DELETE、INSERT或UPDATE）
  • 何时执行（处理之前还是之后）
  • 只有表才支持触发器
  • 触发器按每个表每个事件每次地定义
  • 每个表每个事件每次只允许一个触发器
  • 每个表最多支持6个触发器（INSERT UPDATE 和 DELETE 的之前和之后）

// 创建一个名为 newproduct 的触发器，在 products 表每次 INSERT 之后输出 'Product added'
CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW SELECT 'Product added';

• 删除触发器
  • DELETE TRIGGER newproduct;
• 使用触发器
  • INSERT 触发器
    • 可以引用一个名为 NEW 的虚拟表，访问被插入的行
    • 通常 BEFORE 触发器用于数据验证，AFTER 触发器用于查询数据
  • DELETE 触发器
    • 可以引用一个名为 OLD 的虚拟表，访问被删除的行
  • UPDATE 触发器
    • 可以引用一个名为 OLD 的虚拟表，访问被更新以前的值
    • 可以引用一个名为 NEW 的虚拟表，访问被更新以后的值

第二十六章 管理事务处理

• 事务处理用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

• 不是所有的数据库引擎都支持事务处理

  • MyISAM 不支持
  • InnoDB 支持

• 几个术语

  • 事务 一组SQL语句
  • 回退 撤销指定SQL语句
  • 提交 将未存储的SQL语句结果写入数据库表
  • 保留点 指事务处理中设置的临时占位符

• 控制事务处理

  • 关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

• 事务开始 START TRANSACTION

• 回退 ROLLBACK

  • ROLLBACK 只能在一个事务处理内使用，在执行一条 START TRANSACTION 命令之后
  • INSERT UPDATE DELETE 可以回退
  • SELECT CREATE DROP 不能回退

• 提交 COMMIT

  • 为了保证 SQL 操作不是被部分处理，可以放在事务块中处理
  • 在事务处理块中，提交不会隐含的提交，需要使用 COMMIT 明确的提交
  • 只有事务块中全部语句都正确执行，才会执行 COMMIT

• 当 ROLLBACK 和 COMMIT 执行后，事务会自动关闭

• 使用保留点

  • 简单的 ROLLBACK 和 COMMIT 可以写入或者撤销整个事务处理，更复杂的可能要部分提交或者回退
  • 添加占位符，如果需要回退，可以回退到某个占位符（保留点）
  • 创建保留点 SAVEPOINT delete;
  • 回退到保留点 ROLLBACK TO delete;

第二十七章 全球化和本地化

• 字符集 字母和符号的集合
• 编码 字符集的内部表示
• 校对 为规定的字符如何比较的指令
• 可以给特定的表或者列定义字符集和校对，不指定的话就使用数据库默认

第二十八章 安全管理

• 访问控制
  • 用户对需要的数据具有适当的访问权
  • root是超级管理员，应该创建一系列的账号，开放不同的权限，给相关的人员使用
• 用户管理
  • MySQL的用户账号和信息存储在mysql表中
  • 创建用户
    • CREATE USER ben IDENTIFIED BY 'p@$$wOrd';
    • REMANE USER ben TO mark;
  • 删除用户
    • DROP USER mark;
  • 设置访问权限
    • 查看用户权限 SHOW GRANTS FOR mark;
    • 使用 GRANT 设置权限
      • 要授予的权限
      • 被授予权限的数据库或者表
      • 用户名
    • 使用 REVOKE 撤销权限
    • GRANT 和 REVOKE 可以在几个层次上控制访问权限
      • 整个服务器
      • 整个数据库
      • 特定的表
      • 特定的列
      • 特定的存储过程
    • 更改密码 SET PASSWORD FOR mark = PASSWORD('123456');
      • 不指定用户名时，修改的是当前登录用户的密码

第二十九章 数据库维护

• 备份数据
  • mysqldump
  • mysqlhotcopy
  • BACKUO TABLE 或者 SELECT INTO OUTFILE
• 数据库维护
  • ANALYZE TABLE 检查表键是否正常
  • CHECK TABLE
  • OPTIMIZE TABLE
• 诊断启动问题
  • --help 显示帮助
  • --safe-mode 装载减去某些最佳配置的服务器
  • --verbose 显示全文消息
  • --version 显示版本信息后退出
• 查看日志文件
  • 错误日志 hostname.err
  • 查询日志 hostname.log
  • 二进制日志 hostname-bin
  • 缓慢查询日志 hostname-show.log 记录执行缓慢的任何查询，优化数据库时很有用

第三十章 改善性能

• 硬件
• 内存分配、缓冲区大小
• 关注执行缓慢的进程
• 试验找出最佳的SQL语句
• 存储过程一般比一条条执行sql要快
• 总是使用正确的数据类型
• 不要检索比需求还多的数据
• 合理使用索引
• 使用SELECT + UNION 代替一系列复杂的 OR 条件
• 每条规则在某些条件下都会被打破