MySQL索引

什么是索引

在数据库中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效

常见的索引的分类

  • 主键索引 （primary key）主键是一个唯一性的索引，每个表中只能有一个主键
  • 唯一索引 （unique）索引列的所有数据只能出现一次，必须是唯一
  • 普通索引（index） 最常见的索引，作用是提高对数据的访问速度

表对应的索引被保存在一个索引文件中，如果对数据进行增删改查操作，那么mysql就需要对索引进行更新

保存位置一般在C:\ProgramData\mysql\Mysql 5.7\Data

主键索引

  • 创建表的时候直接添加主键
语法格式：
 create table 表名(
            --添加主键（主键是唯一性索引，不能为null，不能重复）
            字段名 类型 primary key,
);
  • 修改表结构 添加主键索引
语法格式：
 alter table 表名 add primary key(列名);

唯一索引

  • 特点：索引列的所有制都只能出现一次，必须唯一
  • 唯一索引可以保证数据记录的唯一性。事实上，在很多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而知识为了避免面数据出现重复
  • 创建表的时候添加唯一索引
语法格式：
 create table 表名(
       列名 类型（长度）,
       --添加唯一索引
       unique [索引名称] (列名)
);
  • 使用create语句创建：在已有的表上创建索引
create unique index 索引名 on 表名(列名[长度]);
  • 修改表结构时添加索引
alter table 表名 add unique(列名);

普通索引

  • 普通索引（由关键字KEY或者Index定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为了那些最经常出现的查询条件（where column=）或排序条件（Order By column）中的数据列创建索引
  • 使用create index语句创建：在已有的表上创建
create index 索引名 on 表名(列名[长度]);
  • 修改表结构时添加索引
alter table 表名 add index 索引名(列名);

删除索引

  • 语法格式：alter table 表名 drop index 索引名;

索引的总结

创建索引的原则

优先选择为经常出现在查询条件或排序、分组后面的字段 创建索引

索引的优点

  1. 可以大大提高查询速度
  2. 减少查询中分组和排序的时间
  3. 通过创建唯一索引保证数据的唯一性

索引的缺点

  1. 创建和维护索引需要时间，数据量越大，时间越长
  2. 表中的数据进行增删改查操作时，索引也需要进行维护，降低了维护的速度
  3. 索引文件需要占据磁盘空间

视图

什么是视图

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

视图的作用

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

视图主要是为了简化多表查询

视图的使用

创建视图

  • 语法格式：
create view 视图名[字段列表] as select 查询语句;

view 表示视图
字段列表 一般跟后面的查询语句相同
as select 查询语句 表示给视图提供数据的查询语句

视图与表的区别

  1. 视图是建立在表的基础之上
  2. 通过视图，不要进行增删改查操作，视图主要就是用来简化查询
  3. 删除视图，表不受印象，删除表，视图就不在起作用了。

MySQL存储过程

什么是存储过程

  • mysql5.0版本开始支持存储过程
  • 存储过程（stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行

简单理解：存储过程其实就是一堆SQL语句的合并。中间加入了一些逻辑控制。

存储过程的优缺点

优点

  • 存储过程一旦调试完成后，就可以稳定运行（前提是业务需求要相对稳定，没有变化）
  • 存储过程减少业务与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）

缺点

  • 在互联网行业中，大量使用mysql，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
  • 尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更移植也十分困难
  • 阿里的代码规范里也提出了禁止用存储过程，存储过程维护起来的确麻烦。

创建存储过程

方式一

语法格式:
delimiter $$ --声明语句的结束符号  自定义
create procedure 存储过程名称() ---声明存储过程
begin  -- 开始编写存储过程
---要执行的SQL
end $$  --存储过程结束

方式二

创建一个接收参数的存储过程

语法格式：
create procedure 存储过程名(In 参数名 参数类型)

方式三

获取存储过程的返回值

1.变量的赋值
set @变量名 = 值
2.out 输出参数
out 变量名 数据类型

MySQL触发器

什么是触发器

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

触发器创建的四个要素

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

创建触发器

  • 语法格式
delimiter $ ---自定义结束符号
create trigger  触发器名
after/before （insert/update/delete）---触发的时机和监视的事件
on tableName  ---触发器所在表
for each row --固定写法  表示行触发器
begin 
    ---被触发的事件
end  $

DCL（数据控制语言）

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

创建用户

语法格式：

create user '用户名' @ '主机名' identified by '密码'

参数

说明

用户名

创建新用户，登录名称

主机名

指定该用户在哪个主机上可以登录，本地用户可用localhost

如果想让该用户可以从任意远程主机登录，可以使用通配符%

密码

登录密码

用户授权

语法格式

grant 权限1,权限2 ... on 数据库名.表 to '用户名' @ '主机名'

查看用户权限

语法格式

show grants for '用户名' @ '主机名'

数据库备份 & 还原

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

SQLYoga数据备份

  1. 选中要备份的数据库，右键备份导出 —>选择备份数据库
  2. 指定文件位置，选择导出即可

命令行方式

语法格式:

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

还原：
source sql文件地址