MySQL: 5、MySQL索引、视图、存储过程
一、MYSQL索引
1. 什么是索引：
在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和 运行更加高效。
2. 常见索引分类：
主键索引（primary key): 主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表钟的每一条记录
唯一索引（unique): 唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一
普通索引（index): 最常见的索引,作用就是 加快对数据的访问速度
MySql将一个表的索引都保存在同一个索引文件中, 如果对中数据进行增删改操作,MySql都会自动的更 新索引.
3、各种索引的语法格式：
1）主键索引(PRIMARY KEY)的语法格式：
- 创建表的时候直接添加主键索引（最常用）
create table 表名(
字段名 类型 PRIMARY KEY,
);
- 修改表结构时添加主键索引
alter table 表名 add primary key (字段名);
2) 唯一索引(UNIQUE)的语法格式：
- 创建表的时候直接添加唯一索引
CREATE TABLE 表名(
列名 类型(长度),
-- 添加唯一索引
UNIQUE [索引名称] (列名)
);
- 修改表结构时添加索引
ALTER TABLE 表名 ADD UNIQUE ( 列名 );
3)普通索引（INDEX) 的语法格式：
普通索引（由关键字KEY或INDEX定义的索引) 的唯一任务是加快对数据的访问速度，因此，
应该只为那些最经常出现在查询条件（where column =）或排序条件（ORDERBY column) 中的数据列创建索引
- 使用 create index 语句创建： 在已有的表上创建索引
create index 索引名 on 表名（列名[长度])
- 修改表结构添加索引
alter table 表名 add index 索引名（列名);
4) 删除索引：
由于索引会占用一定的磁盘空间，因此为了避免影响数据库性能，应该及时删除不再使用的索引
删除唯一索引和普通索引语法格式：
alter table 表名 drop index 索引名;
删除主键索引格式：
alter table 表名 drop primary key;
4、索引的优缺点总结：
- 添加索引首先应考虑在where 及 order by 涉及的列上建立索引
- 索引的优点：
1. 大大的提高查询速度
2.可以显著的减少查询中分组和排序的时间
- 索引的缺点：
1. 创建索引和维护索引需要时间，而且数据量越大时间越长
2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护 速度
二、MySQL视图
1、什么是视图？
1. 视图是由查询结果形成的一种虚拟表。
2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4. 视图向用户提供基表数据的另一种表现形式
2、视图的作用
- 权限控制时可以使用
比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
- 简化复杂的多表查询
视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图
就可以获取想要得到的信息(不需要再编写复杂的SQL)
视图主要就是为了简化多表的查询
3、视图的使用
1）创建视图
语法格式：create view 视图名 [column_list] as select 语句;
view ：表示视图
column_list: 可选参数，表示属性清单,指定视图中各个属性的名称，默认情况下，
与SELECT语句中查询 的属性相同
as ：表示视图要执行的操作
select 语句: 向视图提供数据的内容
案例1：
1. 先编写查询语句：查询所有商品和商品对应的分类信息
select * from products p left join category c on p.category_id = c.cid;
2.基于上面的查询语句一张视图
create view products_category_view
as select * from products p left join category c on p.category_id = c.cid;
3. 执行之后在视图栏里就能看到创建的视图
4.查询视图，直接将它当做一张只读的表操作就可以了
select * from products_category_view;
案例2：查询各个分类下的商品平均价格
通过多表查询：
select
cname as '分类名称',
avg(p.price) as '平均价格'
from products p left join category c on p.category_id = c.cid
group by c.cname;
通过视图查询，可以省略连表的操作：
select
cname as '分类名称',
avg(price) as '平均价格'
from products_category_view group by cname;
2) 视图与表的区别:
- 视图是建立在表的基础上，表是存储数据库中的数据，而视图只是做一个数据的展示
- 通过视图不能改变表中的数据（一般情况下视图中的数据库都是表中的列 经过计算得到的结果，不允许更新）
- 删除视图，表不受影响，而删除表，视图不再起作用
三、MySQL存储过程（mysql 5.0版本开始支持存储过程）
1.什么是存储过程？
简单来说就是存储过程其实就是一堆 SQL 语句的合并 ，中间加入了一些逻辑控制。
2.优缺点：
优点：存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
缺点：在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使 用，
并且互联网行业需求变化较快也是原因之一
尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储 过程变更一致也十分困难
阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦；
3. 创建简单的存储过程：
创建存储过程语法格式：
DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$
CREATE PROCEDURE 过程名称(IN 参数名 参数类型) -- 声明存储过程, IN 输入参数：表示调用者向存储过程传入值
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
调用存储过程的语法格式：
call 存储过程名；
案例1：
定义两张表：商品表和订单表
# 商品表
CREATE TABLE goods(
gid INT,
NAME VARCHAR(20),
num INT -- 库存
);
#订单表
CREATE TABLE orders(
oid INT,
gid INT,
price INT -- 订单价格
);
# 向商品表中添加3条数据
INSERT INTO goods VALUES(1,'奶茶',20);
INSERT INTO goods VALUES(2,'绿茶',100);
INSERT INTO goods VALUES(3,'花茶',25);
1.编写存储过程, 查询所有商品数据：
DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN s
elect * from goods;
END $$
2. 调用存储过程
call good_proc;
案例2: 接受一个商品id，根据id删除数据
DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$
调用存储过程 传递参数 删除id为2的商品：
call goods_proc02(2);
四、MySQ触发器
1.什么是触发器？
简单来说就是当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句。
2.触发器创建的四个要素：
1. 监视地点（table）
2. 监视事件（insert/update/delete）
3. 触发时间（before/after）
4. 触发事件（insert/update/delete）
3.触发器语法格式：
delimiter $ -- 将Mysql的结束符号从 ;改为 $,避免执行出现错误
CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的
before/after（insert/update/delete） -- 触发的时机 和 监视的事件
on table_Name -- 触发器所在的表 f
or each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行
begin
-- begin和end之间写触发事件
end
$ -- 结束标记
4. 案例：
1.向商品中添加一条数据
INSERT INTO goods VALUES(1,'book',40);
2.需求: 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。
编写触发器：
-- 1.修改结束标识
DELIMITER $
-- 2.创建触发器
CREATE TRIGGER t1
-- 3.指定触发的时机,和要监听的表
AFTER INSERT ON orders
-- 4.行触发器 固定写法
FOR EACH ROW
-- 5.触发后具体要执行的事件
BEGIN
-- 订单+1 库存-1
UPDATE goods SET num = num -1 WHERE gid = 1;
END$
3.向订单表中添加一条数据
insert into orders values（1，1，25）；
4. 插入后 goods表中的数据随之 -1
五、数据库备份
命令行备份：
1、进入到mysql安装目录的bin目录下，打开dos命令输入备份命令：
格式： mysqldump -u 用户名 -p 密码 数据库 > 文件路径
执行备份, 备份db2中的数据 到 H盘的 db2.sql 文件中
mysqldump -uroot -p123456 db2 > H:/db2.sql
命令行恢复备份：
source sql 文件地址；
注意，还原的时候需要先创建对应的数据库，然后 use 选择对应的数据库
