数据库操作——Mysql索引&试图&存储过程_mysql 使用存储过程执行创建索引命令的方法

Mysql 索引

• 索引：在数据表中，对字段创建索引名，可以大大提高查询速度，通过善用这些索引，可以令MySQL的查询和运行更加高效
• • 如果拿汉语字典打比方，我们可以按照拼音，笔画，部首等目录（索引），查找到需要的字
• 常见的索引分类：
• • 主键索引：主键是一种唯一索引， 每个表只能有一个主键，用于表示数据表中的每一条记录
• • 唯一索引：索引列所在的值都只能出现一次，必须唯一
• • 普通索引：最常见的索引，作用就是加快对数据库的访问
• MySQL将一个表的索引都保存在同一个索引文件中，如果对表中的数据进行增删改操作，MySQL会自动更新索引

索引的创建和删除

• 主键索引创建
• • 一个表可以没有主见，但最多只能有一个主键，且主键的值不能包含NULL
• • • 格式：字段名 字段类型 primary key
• • • 创建表的时候直接添加（最常用）
• • • 建表之后，添加索引，使用DDL（数据定义语言）
      
• 唯一索引创建
• • 语法结构：字段名 字段值 unique
• • 创建表的时候直接添加（最常用）
• • 建表之后添加： creat unique index 索引名 or 表名(列名(长度))
• • 唯一索引保证了数据的唯一型，同时也提升了查询效率
    
• 普通索引：由关键字key或者index定义的索引，
• • 唯一的作用就是加快对数据的访问速度，因此应该为哪些最常出现在查询条件或者排序条件中的数据列创建索引
• 语法格式
• • 对已有的表创建索引：creat index 索引名 on 表名(列名(长度))
• • 修改表结构添加索引：alter table 表名 add index 索引名 (列名)
    
• 删除索引：由于索引会占用一定的内存空间，因此为了避免影响数据库性能，应该及时删除不再使用的索引
• • 语法格式：alter table 表名 drop index 列名
    

索引的性能测试

• 结论：
• • 一张表中有500万条信息的情况下
• • 通过ID（主键）查询数据，所需时间为：0.001毫秒
• • 通过普通字段（非索引）查询数据，所需时间为：2.179毫秒
• • 通过普通字段（非索引）进行分组查询，所需时间为：36.714毫秒
• • 对普通字段，添加索引，一般是在创建表之后，就要决定对哪些字段添加索引（普通索引）
• • 对鑫添加的索引字段，进行分组查询（相同语句），所需时间0.005毫秒

索引的总结

• 创建索引的原则：
• • 并不是每个字段都需要添加索引，优先为经常出现在查询条件/排序条件/分组后面的字段创建索引
• 索引的优缺点：
• • 优点：
• • 1.可以大大提高查询速度
• • 2.减少创建唯一索引保证数据的唯一性
• • 3.减少查询中分组和排序的时间
• • 缺点：
• • 1.创建和维护索引需要时间，数据量越大，时间越长
• • 2.表中的数据进行增删改操作时，索引也需要维护，降低了维护的速度
• • 3.索引文件需要占据内存空间

视图

• 视图的定义：
• • 视图是一种虚拟表
• • 视图建立在已有表的基础上，视图赖以建立的这些表成为基表
• • 向视图提供数据内通的语句为select语句，可以将视图理解为存储起来的select语句
• • 视图向用户提供基表数据的另一种表现形式
• 视图的作用：
• • 权限控制的时候可以使用
• • • 例如：某几列可以运行用户查询，其他列不允许，可以开通视图，查询特定的列，起到权限的作用
• • 简化复杂的多表查询
• • • 视图本身就是一条SQL查询语句，我们可以将一次复杂的查询，构建成一张视图，用户只要查询视图就可以获取想要得到的信息
• • • 视图主要是为了简化多表查询
• 使用视图的场景：如果某个查询结果出现的非常频繁，并且查询语法比较复杂，那么这个时候就可以根据这条查询语句构建一张视图，方便查询
• 视图就相当于只读的表，基本没有做增删改操作的

视图的使用

• 创建视图：
• • 语法格式：creat view 视图名 [字段列表] as select语句;
• • 上面的view表示视图；字段列表一般根据后面的查询语句来决定
• • as select 查询语句，表示给视图提供的查询语句
    

视图的查询

• 视图的查询和数据表查询一样，直接使用select语句即可
• 语法结构：select 字段名 from 视图名;’
  
  
• 视图和表的区别
• • 视图是建立在表的基础之上的，也就是说先有表，才有视图
• • 通过视图，不要进行增删改操作，视图主要就是用来简化查询的
• • 删除视图，表不受影响
• • 但是删除表，视图就不再起作用了

存储过程

• 存储过程就是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象
• 存储过程是为了完成特定的SQL语句集，经编译、创建并保存在数据库中，用户可通过指定存储过程的名字给定参数（需要时）来调用执行
• 简单的理解：存储过程就是一堆SQL语句的合并，中间加入了一些逻辑控制
• 存储过程的优缺点
• • 优点：存储过程一旦完成之后，就可以稳定运行（前提是：业务需求相对稳定，没有变化）
• • • 存储过程减少业务系统和数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，和数据库服务器不再同一个地区）
• • 缺点：在互联网中，大量水用MySQL，MySQL的存储过程和Oracle相比较弱，所以较少使用，并且互联网行业需求变化快也是原因之一
• • • 尽量在简单的逻辑中使用，存储过程移植比较困难，数据库集群环境，保证每个库之间存储过程变更一致也十分困难
• • • 阿里的代码规范中，也提出了禁止使用存储过程，存储过程维护起来比较麻烦
• 【强制】禁止使用存储过程，存储过程难以调试和扩展，没有可移植性

存储过程的创建方式

• 存储过程创建方式一：直接创建存储过程，语法结构如下

delimiter $$ -- 声明语句的结束符号，$$可以自定义
create procedure 存储过程名称() -- 声明存储过程
begin -- 开始编写存储过程
	-- 要执行的SQL
end $$ -- 存储过程结束
1
2
3
4
5

• 存储过程的调用，使用CALL 存储过程名称;即可

• 存储过程创建方式二，创建一个接收参数的存储过程：

delimiter $$ -- 声明语句的结束符号，$$可以自定义
create procedure 存储过程名(IN 参数名 参数类型) -- 声明存储过程
begin -- 开始编写存储过程
	-- 要执行的SQL
end $$ -- 存储过程结束
1
2
3
4
5

• 存储过程创建方式三，获取存储过程的返回值，语法结构：
• • 1.给变量赋值，set @变量名 = 值
• • 2.out 输出参数：out 变量名 数据类型

delimiter $$ -- 声明语句的结束符号，$$可以自定义
create procedure 存储过程名(IN 参数名称 数据类型,……) -- 声明存储过程
-- ……的含义是多个变量，有几个，就填写几个，逗号隔开
begin -- 开始编写存储过程
	-- 要执行的SQL
end $$ -- 存储过程结束
1
2
3
4
5
6

触发器

• 触发器是为了MySQL提给程序员和数据分析员，保证数据完整性的一种办法
• 他是与表时间相关的特殊的存储过程
• 他的执行不是由程序调用，也不是手工启动，而是由事件触发
• 例如：当对一个表进行操作时他就会激活，激活后就会执行
• 简单的说：当我们执行一条sql语句的时候，这条sql语句的执行会自动取触发执行其他sql语句
• 触发器的四要素
• • 监视地点：table
• • 监视事件：insert/update/delete
• • 触发事件：before/after
• • 触发事件：insert/update/delete

创建触发器

• 语法结构

delimiter $ -- 将说起来的结束符号从;变为$，避免执行出现错误
create trigger 触发起名称   -- 触发器名，在一个数据库中触发器是唯一的
before/after (insert/update/delete)-- 触发时机和触发事件
on 表名称   -- 触发器所在的表
for each row   -- 固定写法，叫作行触发器，每一行受影响，触发器事件都执行
begin
-- 被触发的事件
end $
1
2
3
4
5
6
7
8

• 下方例子中，触发器的含义是：对order表进行插入操作时，就修改库存信息，订单+1，库存-1
  

数据库权限

• Mysql中，默认使用的是root用户，超级管理员，拥有全部的权限
• 除了root用户之外，可以通过DCL（数据控制语言）来定义一些权限比较小的用户，分配不同的权限和维护数据库

创建用户

• 语法格式：create user '用户名'@'主机名' identified by ‘密码’;
• 参数含义：
• • 用户名：创建新用户，登录名称
• • 主机名：指定该用户在哪个主机上可以登录，本地用户可用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符（主机名可以用%代替）
• • 密码：登录密码
• 创建用户成功之后，可以在mysql（默认）的数据库中，user表中进行查看
  

用户授权

• 语法格式：grant 权限1,权限2…… on 数据库名.表名 to '用户名'@'主机名'；
• 参数解析：
• • 权限：授予用户的权限，例如create，alter，select，insert，update等等，如果要授予所有权限，就使用all
• • on：用来执行权限针对哪些库和表
• • to：表示将权限赋予某个用户
• 设置权限之后，不同的用户登录之后， 看到的页面和可执行的操作不相同
  

查看用户权限

• 语法格式：show grants for '用户名'@'主机名'；
  

删除用户权限

• 语法格式：drop user '用户名'@'主机名'；
  

数据库备份（命令行格式）

• 语法格式：mysqldump -u 用户名 -p 密码 数据库名 > 输出文件的路径
• 在cmd的mysql的目录下，输入以上命令即可