索引

什么是索引

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

常见的索引的分类

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

- 为demo01表添加 主键索引
ALTER TABLE demo01 ADD PRIMARY KEY (did);

/*
	唯一索引的创建
		create unique index 索引名 on 表名(列名(长度))
*/

-- 为demo01表的 hobby字段添加唯一索引
CREATE UNIQUE INDEX ind_hobby ON demo01(hobby);

-- 添加唯一索引的列,列的所有值都只能出现一次
INSERT INTO demo01 VALUES(1,'tom','篮球');

-- Duplicate entry '篮球' for key 'ind_hobby'
-- 唯一索引保证了数据的唯一性, 同时也提升了查询效率
INSERT INTO demo01 VALUES(2,'jack','篮球');


/*
	普通索引的创建
		1.create index 索引名 on 表名(列名[长度])
		2.ALTER TABLE 表名 ADD INDEX 索引名 (列名)
*/
-- 为 demo01表中的 dname字段添加普通索引
ALTER TABLE demo01 ADD INDEX ind_dname(dname);

/*
	删除索引
		ALTER  TABLE 表名  DROP INDEX 索引名;
*/
-- 删除dname字段上的索引
ALTER TABLE demo01 DROP INDEX ind_dname;


-- 导入测试索引.sql 文件

-- test_index 表中有 500万条数据
SELECT COUNT(*) FROM test_index;

-- 通过id查询一条数据
SELECT * FROM test_index WHERE id = 100001;

-- 通过 dname字段查询 耗时2秒左右
SELECT * FROM test_index WHERE dname = 'name5200';

-- 执行分组查询 dname没有添加索引 36秒
SELECT * FROM test_index GROUP BY dname;

-- dname字段添加索引
ALTER TABLE test_index ADD INDEX dname_indx(dname);

SELECT * FROM test_index GROUP BY dname;

总结

索引的总结：

创建索引的原则
	优先选择为 经常出现在 查询条件或者排序 分组后面的字段 创建索引.
	
索引的优点
	1.可以大大的提高查询速度
	2.减少查询中分组和排序的时间
	3.通过创建唯一索引保证数据的唯一性
	
索引缺点
	1.创建和维护索引需要时间,数据量越大 时间越长.
	2.表中的数据进行增删改操作时,索引也需要进行维护,降低了维护的速度
	3.索引文件需要占据磁盘空间

视图

什么是视图

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

视图的作用

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

创建视图

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

存储过程

什么是存储过程

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

优缺点

优点:

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

缺点:

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

创建格式

简单创建方式

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

eg:

DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
select * from goods;
END $$

带参数创建方式：

CREATE PROCEDURE 存储过程名称(IN 参数名参数类型)

DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$

赋值与输出参数

SET @变量名=值

OUT 变量名数据类型

# 创建存储过程 接收参数插入数据, 并返回受影响的行数
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 $$

触发器

什么是触发器

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

四要素

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

创建触发器

语法格式：

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

eg:需求: 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。编写触发器

-- 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(数据控制语言)

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'

eg:

创建 admin1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123456 CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456'; 创建 admin2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456 CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

用户授权

语法格式： GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

权限 : 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
ON 用来指定权限针对哪些库和表。
TO 表示将权限赋予某个用户。

查看权限

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

删除用户

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

查询用户

SELECT * FROM USER;

注：拉钩大数据预科班资料

MySQL索引&视图&存储过程

索引