Mysql数据库（四） MySQL索引&视图&存储过程

四、MySQL索引&视图&存储过程

MySQL 索引

基本概念

• 在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效
• 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是 一个人力三轮车。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

常见索引分类

• 分类

  索引名称	说明
  主键索引(primary key)	主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一 条记录
  唯一索引(unique)	唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一
  普通索引(index)	最常见的索引,作用就是 加快对数据的访问速度

主键索引 (PRIMARY KEY)

• 特点

• 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录

• 一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL

• 语法格式

  -- 创建表的时候直接添加主键索引 (最常用)
  CREATE TABLE 表名(     
      -- 添加主键 (主键是唯一性索引,不能为null,不能重复,)    
      字段名 类型 PRIMARY KEY  
  ); 
  
  -- 修改表结构 添加主键索引
  ALTER TABLE 表名 ADD PRIMARY KEY (列名); 
  

唯一索引(UNIQUE)

• 特点

• 索引列的所有值都只能出现一次, 必须唯一

• 唯一索引可以保证数据记录的唯一性。事实上，在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复

• 语法格式

  -- 创建表的时候直接添加唯一索引
  CREATE TABLE 表名(      
      列名 类型(长度),      
      -- 添加唯一索引    
      UNIQUE [索引名称] (列名)
  ); 
  
  -- 使用create语句创建: 在已有的表上创建索引
  create unique index 索引名 on 表名(列名(长度));
  

普通索引 (INDEX)

• 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度

• 因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引

• 语法格式

-- 使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名[长度]);

删除索引

• 由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索 引

• 语法格式

  ALTER TABLE 表名 DROP INDEX 索引名;
  

索引的优缺点总结

• 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引
• 索引的优点
• 大大的提高查询速度
• 可以显著的减少查询中分组和排序的时间
• 索引的缺点
  • 创建索引和维护索引需要时间，而且数据量越大时间越长
  • 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

MySQL 视图

基本概念

• 视图是一种虚拟表
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
• 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
• 视图向用户提供基表数据的另一种表现形式

视图的作用

• 权限控制时可以使用
  • 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
• 简化复杂的多表查询
  • 视图 本身就是一条查询SQL,我们可以将一次复杂的查询，构建成一张视图, 用户只要查询视图 就可以获取想要得到的信息(不需要再编写复杂的SQL)
  • 视图主要就是为了简化多表的查询

创建视图

• 语法格式

  create view 视图名 [column_list] as select语句;
  /* view: 表示视图 
     column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中                 查询的属性相同
     as : 表示视图要执行的操作 
     select语句: 向视图提供数据内容
  */   
  

视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果,不允许更新）
• 删除视图，表不受影响，而删除表，视图不再起作用

MySQL 存储过程

基本概念

• MySQL 5.0 版本开始支持存储过程。
• 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据 库对象
• 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行
• 简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制

存储过程的优缺点

• 优点:
  • 存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
  • 存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
• 缺点:
  • 在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
  • 尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难
  • 阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦

存储过程的创建方式

创建简单的存储过程

• 语法格式

  DELIMITER $$   -- 声明语句结束符，可以自定义 一般使用$$ 
  CREATE PROCEDURE 过程名称()  -- 声明存储过程 
  BEGIN       -- 开始编写存储过程     
       -- 要执行的操作 
  END $$          -- 存储过程结
  

• 调用存储过程
  语法格式

  call 存储过程名;
  

创建接收参数的存储过程

• 语法格式

  CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型) 
  -- IN  输入参数：表示调用者向存储过程传入值
  

创建有返回值的存储过程

• 语法格式

  CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型, OUT 参数名 参数类型) 
  
  -- 变量赋值方式
  SET @变量名=值
  
  
  -- 举例
  # 创建存储过程 接收参数插入数据, 并返回受影响的行数 
  DELIMITER $$ 
  CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) 
  BEGIN    -- 执行插入操作    
      INSERT INTO orders VALUES(o_oid,o_gid,o_price);    
      -- 设置 num的值为 1    
      SET @out_num = 1;    
      -- 返回 out_num的值    
      SELECT @out_num; 
  END $$
  
  -- 调用存储过程
  # 调用存储过程插入数据,获取返回值
  CALL orders_proc(1,2,30,@out_num); 
  

MySQL触发器

基本概念

• 触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。——百度百科
• 简单理解: 当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句

触发器创建的四个要素

• 监视地点（table）
• 监视事件（insert/update/delete）
• 触发时间（before/after）
• 触发事件（insert/update/delete）

创建触发器

• 语法格式

  delimiter $                 -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
  CREATE TRIGGER  触发器名  -- 触发器名，在一个数据库中触发器名是唯一的
  before/after（insert/update/delete） -- 触发的时机 和 监视的事件 
  on 表名           -- 触发器所在的表 
  for each row        --  固定写法 叫做行触发器, 每一行受影响，触发事件都执行 
  begin               
      -- begin和end之间写触发事件 
  end $   -- 结束标记
  

• 需求: 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。

-- 1.修改结束标识
DELIMITER $ 
-- 2.创建触发器 
CREATE TRIGGER t1 
-- 3.指定触发的时机,和要监听的表 
AFTER INSERT ON orders 
-- 4.行触发器 固定写法 
FOR EACH ROW 
-- 4.触发后具体要执行的事件 
BEGIN    
-- 订单+1 库存-1    
    UPDATE goods SET num = num -1 WHERE gid = 1; 
END$

DCL(数据控制语言)

• MySql默认使用的都是 root 用户，超级管理员，拥有全部的权限。除了root用户以外，我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库

创建用户

• 语法格式

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 
  
  -- 用户名   创建的新用户,登录名称
  -- 主机名   指定该用户在哪个主机上可以登陆，本地用户可用 localhost 如果想让该用户可以 从任意远            程主机登陆，可以使用通配符 %
  -- 密码     登录密码
  

用户授权

• 创建好的用户,需要进行授权

• 语法格式

  GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名'; 
  
  -- 权限   授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。 如果要授予所有的          权限则使用 ALL
  -- ON    用来指定权限针对哪些库和表。
  -- TO    表示将权限赋予某个用
  

查看权限

• 语法格式

  SHOW GRANTS FOR '用户名'@'主机名'; 
  

删除用户

• 语法格式

  DROP USER '用户名'@'主机名';
  

查询用户

• 选择名为 mysql的数据库, 直接查询 user表即可

  -- 查询用户 
  SELECT * FROM USER;
  

数据库备份&还原

• 备份的应用场景
• 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障
• 比如发生 意外停机或存储介质损坏，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的

备份

• 进入到Mysql安装目录的 bin目录下, 打开DOS命令行

• 语法格式

  mysqldump -u 用户名 -p 密码 数据库 > 文件路径
  

还原

• 还原的时候需要先创建一个数据库

• 打开DOS命令行，登录mysql数据库

• 语法格式

  source sql文件地址