Mysql索引、存储过程和触发器

Mysql索引、存储过程和触发器

Mysql索引

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

/*
	索引
		我们可以通过对数据表中的字段创建索引，来提高查询速度
		
	索引常见分类
		主键索引	primary key		主键是一个唯一性的索引，每个表中只能有一个
		唯一索引  unique				索引列的所有数据，只能出现一次，必须是唯一的
		普通索引	index 				最常见索引，提高数据访问速度
		
	表对应的索引被保存在一个所以文件中，若对数据进行增删改操作，mysql就需要对索引进行更新
*/

主键索引 (PRIMARY KEY)

主键是一个唯一性的索引，每个表中只能有一个

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

/*
	主键索引	
		创建方式1：创建表时直接添加主键
		创建方式2：创建表之后创建索引 ALTER TABLE 表名 ADD PRIMARY KEY (主键);
*/

-- 给demo创建主键索引
ALTER TABLE demo01 ADD PRIMARY KEY (did);

唯一索引(UNIQUE)

索引列的所有数据，只能出现一次，必须是唯一的

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

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

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

-- 添加唯一索引的列，他的值只能出现一次
-- 保证数据的完整性，同时提高查询效率

普通索引 (INDEX)

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引

/*
	普通索引
	  创建方式1  create index 索引名 on 表名(列名[长度])
		创建方式2  ALTER TABLE 表名 ADD INDEX 索引名 (列名)
*/

-- 为demo01表中dname字段添加普通索引
ALTER TABLE demo01 ADD INDEX ind_dname(dname);

删除索引

/*
	删除索引
		ALTER TABLE 表名 DROP INDEX 索引名称;
*/

-- 删除demo01中dname索引
ALTER TABLE demo01 DROP INDEX ind_dname;

索引总结

创建原则

优先选择经常出现在条件或排序分组后的字段

优缺点

优点

大大提高查询速度

减少查询中分组和排序时间

通过唯一索引保证数据唯一性

缺点

创建和维护需要时间，数据量越大，时间越长

表中数据进行增删改该操作时，索引也需要维护，降低维护速度

索引文件需要占用磁盘空间

视图

视图是一种虚拟表。

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

视图向用户提供基表数据的另一种表现形式

作用

权限控制使用

简化复杂的多表查询

创建

/*
	create view 视图名 [column_list] as select语句;
	view: 表示视图
	column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询的属性相同
	as : 表示视图要执行的操作
	select语句: 向视图提供数据内容
*/
-- 创建视图
-- 查询所有商品和商品对应分类信息
SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;
-- 根据上面查询构建一张视图
CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c on p.category_id = c.cid;
-- 操作视图就相当于操作一张只读表
SELECT * FROM products_category_view;

-- 查询所有商品 和 商品的对应分类信息
-- 查询分类表、商品表
-- 查询条件 分组操作
-- 查询字段 平均价格、分类名
-- 多表连接条件

-- 使用多表方式查询
SELECT c.cname,AVG(p.price) FROM products p LEFT JOIN category c ON p.category_id = c.cid GROUP BY c.cname

-- 使用视图查询
SELECT pc.cname,AVG(pc.price) FROM products_category_view pc GROUP BY pc.cname;

-- 查询鞋服分类下最贵的商品的全部信息
-- 多表查询
-- 查询鞋服分类最高商品价格
SELECT MAX(p.price) FROM products p LEFT JOIN category c ON p.category_id = c.cid WHERE c.cname ='鞋服';
-- 进行子查询，将上面查询结果作为条件
SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid WHERE c.cname = '鞋服' AND p.price = (SELECT MAX(p.price) FROM products p LEFT JOIN category c ON p.category_id = c.cid WHERE c.cname ='鞋服');

-- 视图查询
SELECT * FROM products_category_view pc WHERE pc.cname = '鞋服' AND pc.price = (SELECT MAX(pc.price) FROM products_category_view pc WHERE pc.cname = '鞋服');

视图和表的区别

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

存储过程

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

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

/*
	创建存储过程
		create procedure 存储过程名称()
		begin 
			-- 要执行的语句
		end 
*/

-- 方式1 编写存储过程, 查询所有商品数据
CREATE PROCEDURE goods_proc()
BEGIN
 SELECT * FROM goods;
END 

-- 调用存储过程 call
CALL goods_proc;

-- 方式2 接收一个商品id, 根据id删除数据
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
	DELETE FROM goods WHERE gid = goods_id;
END

-- 调用存储过程 call
CALL goods_proc02(1);

-- 方式3 向订单表 插入一条数据, 返回1,表示插入成功
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);
	SET @out_num = 1;
	SELECT @out_num;
END

-- 调用存储过程 获取返回值
CALL orders_proc(1,2,5,@out_num);

触发器

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

简单理解: 当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句

四要素

1. 监视地点（table）
2. 监视事件（insert/update/delete）
3. 触发时间（before/after）
4. 触发事件（insert/update/delete）

/*
	CREATE TRIGGER Trigger_Name  -- 触发器名，在一个数据库中触发器名是唯一的
	before/after（insert/update/delete） -- 触发的时机 和 监视的事件
	on table_Name -- 触发器所在的表
	for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行
	begin
		-- begin和end之间写触发事件
	end
*/
-- 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量
-- 监视表 orders
-- 监视事件 insert
-- 触发时间 after
-- 触发事件	update
CREATE TRIGGER t1
AFTER INSERT ON orders 
FOR EACH FROM
BEGIN
	UPDATE goods SET num = num - 1 WHERE gid = 4;
END