MySQL基础(三)：视图、存储过程、事务与索引

一：视图

视图是虚拟的表；
它们包含的不是数据而是根据需要检索数据的查询；

视图提供了一种封装SELECT语句的层次，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

视图的作用：
1，重用SQL语句；2，简化复杂的SQL操作；3，使用表的组成部分而不是整个表；4，保护数据；5，更改数据格式和表示

操作视图：

--创建视图
CREATE VIEW viewname as
	查询语句；

--查看视图
SHOW CREATE VIEW viewname;
--删除视图
DROP VIEW viewname;

 

二：存储过程

存储过程：就是为以后的使用而保存的一条或多条SQL语句的集合（类似于一个函数）

存储过程的优点：
简单：把处理封装在容易使用的单元中，简化复杂的操作；
安全：由于不要求反复建立一系列处理步骤，保证了数据的一致性；简化对变动的管理；

高性能：因为存储过程通常以编译过的形式存储，所有DBMS为处理命令所做的工作较少。

缺点：
不同DBMS中的存储过程语法有所不同，所有存储过程的可移植性很差；

存储过程的编写比基本的SQL语句复杂，需要更高的技能，更丰富的经验。

MySQL变量名规范：所有MySQL变量都必须以@开始

操作存储过程：

--创建存储过程
CREATE PROCEDURE productpricing1()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),   
    OUT pa DECIMAL(8,2),				
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END;

--执行存储过程：MySQL称存储过程的执行为调用
--存储过程将结果保存在3个变量中
CALL productpricing(@pricelow,
		    @pricehigh,
		    @priceaverage);


CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

CALL ordertotal(20005,@total);


--删除存储过程

DROP PROCEDURE productpricing;

--检查存储过程

SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE 'ordertotal';

 

三：游标

游标：cursor 是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集

在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据；

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

使用游标：
①在能够使用游标前，必须声明(定义)它；
②一旦声明后，必须打开游标以供使用；
③对于填有数据的游标，根据需要取出(检索)各行；

④在结束游标使用时，必须关闭游标。

--操作游标
CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
	DECLARE o INT;

	--Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	--Open the cursor
	OPEN ordernumbers;

	--Get order number
	FETCH ordernumbers INTO o;

	--Close the cursor
	CLOSE ordernumbers;
END;

 

四：事物处理

事物处理：可以用来维护数据库的完整性，它保证成批的SQL操作要么完全执行，要么完全不执行

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

事物：transaction 指一组SQL语句；
回退：rollback 指撤销指定SQL语句的过程；
提交：commit 指将未存储的SQL语句结果写入数据库表；

保留点：savepoint 指事务处理中设置的临时占位符，你可以对它发布回退。

事务处理用来管理INSERT、UPDATE和DELETE语句，不能回退SELECT语句，也不能回退CREATE或DROP操作

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。

保留点越多越好 可以在MySQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能按自己的意愿灵活地进行回退。

START TRANSACTION；
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

--使用保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;

五：索引

索引：用来排序数据以加快搜索和排序操作的速度

索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引；
索引数据可能要占用大量的存储空间；
并非所有数据都适合于索引；

索引用于数据过滤和数据排序；

补充：

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。

索引就像一本书的目录，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。
检索某些数据，在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

单列索引：主键索引 primary key  ；唯一索引  unique ；普通索引  index(字段名)  或 key(字段名)
组合索引（最左前缀）  key(字段名1，字段名2，字段名3)；key(title,time)  相当于分别建立了两组索引:①title；②title,time
全文索引  fulltext(字段名)
删除索引：DROP INDEX index_name ON table；alter table drop index xxx;

索引设计原则：
1．选择唯一性索引；
2．为经常需要排序、分组和联合操作的字段建立索引；
3．为常作为查询条件的字段建立索引；
4．限制索引的数目；
5．尽量使用数据量少的索引；
6．尽量使用前缀来索引；
7．删除不再使用或者很少使用的索引；

MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。
索引相关参数：
possible_keys：MySQL在搜索数据记录时可以选用的各个索引
key：实际选用的索引
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）；不损失精确性的情况下，长度越短越好 

聚集索引：
索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的），可以这么理解：只要是索引是连续的，那么数据在存储介质上的存储位置也是连续的。
非聚集索引：
索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。

InnoDB引擎会为每张表都加一个聚集索引；如果一个主键被定义了，那么这个主键就是作为聚集索引；如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。

 

六：触发器

触发器：是特殊的存储过程，它在特定的数据库活动发生时自动执行

触发器与单个的表相关联；与orders表上的INSERT操作相关联的触发器只在orders表中插入行时执行

创建触发器：
1，唯一的触发器名；
2，触发器关联的表；
3，触发器应该响应的活动（DELETE、INSERT或UPDATE）；
4，触发器何时执行（处理之前或之后）

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）

用途：
保证数据一致；
基于某个表的变动在其他表上执行活动；
进行额外的验证并根据需要回退数据；

计算计算列的值或更新时间戳；

--创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

--删除触发器
DROP TRIGGER newproduct;