mysql的视图、存储过程、游标、事务、引擎、索引的认识和使用

视图

一、视图概念：

视图是虚拟表，并不存在真正的表，可以重用sql语句，利用实际存在的一个或几个表链接查询数据，只展示部分数据，可以保护数据库中数据。只有部分表的权限！可查询，过滤，关联查询，一定条件下可以添加和更新数据！

二、视图特点：

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

三、视图的规则：

1、视图名必须唯一不能和表名相同
2、创建视图数据没有限制
3、创建视图必须具有足够权限
4、视图可以嵌套，视图创建新视图
5、order by 可以用在视图中，如果创建视图select语句中含有order by,则此order by 将被覆盖
6、视图不能被索引，视图不可以有触发器和默认值
7、视图可以和表一起使用

四、视图的使用

1、视图创建：

create view 视图名 as select 语句

2、视图使用：（当基本表使用）

select * from 视图名 where等过滤语句

五、视图作用（视图一般用于检索，不更新）

1、利用视图简化复杂链接
2、用视图重新格式化检索出的数据
3、用视图过滤不想要的数据
4、视图和计算字段
5、更新视图（对视图更新则是对基表更新（插入、删除等））
通常情况可以更新视图，但如果视图定义有以下操作时，则不能进行视图更新：
分组、链接、子查询、并、聚集函数、DISTINCT、导出（计算）列

存储过程

一、存储过程：

为以后使用保存的一条或多条sql语句的集合，用于批处理。

二、为什么要用存储过程：

1、把处理封装再容易使用的单元中，简化复杂操作
2、保证数据完整性
3、简化对变动管理
4、提高性能（提前编译预处理）
5、可以编写功能更强更灵活的代码

三、存储过程缺陷：

1、存储过程一般比基本sql语句复杂，
2、编写存储过程需要更高技能更丰富经验，
3、可能没有创建存储过程安全访问权限

四、使用存储过程

注意：命令行程序默认mysql语句命令行分隔符“；”，存储过程分隔符也是“；”，避免冲突，所以要临时更改mysql分隔符。

修改命令行分隔符为“//”命令：delimiter //

1、创建存储过程（可有/无参数）：create producedure 存储过程名

例子：into otoal ：将乘积结果赋值给输出参数ototal
create producedure ordertotal （in onnumber int，out ototal decimal(10,2)）
begin
select sum(item_price * quantity) from orderitems where order_num=onnumber into ototal;
end //

2、使用存储过程：call 存储过程名

注意：mysql中变量前要加“@”
(1)修改命令行分隔符：delimiter ;
(2)调用存储过程：call ordertotal(20005,@total);
（3）读取输出变量值：select @total;

具体过程如下图：
例1：

例2：

五、查看已经创建存储过程：

show create procedure 存储过程名字

六、删除存储过程：

drop procedure 存储过程名字

游标

一、什么是游标

用于交互式应用，对检索出来的结果集，对于每一行前进后退多行查询，滚动屏幕数据，对屏幕数据更改。

二、使用游标步骤：在存储过程中，创建游标

定义局部变量必须再定义游标以前定义，定义句柄须在定义游标之后定义，否则会出现错误。

1、声明游标
2、打开游标
3、根据需要在检索数据集中展示数据
4、关闭游标

三、在存储过程中，创建游标，使用游标

调用存储过程

触发器

一、触发器：

触发器是mysql响应delete、update、insert语句时自动执行的一条mysql 语句。

二、触发器创建规则：

1、具有唯一触发器名
2、触发器关联表
3、响应活动：delete insert update
4、触发器何时执行（处理之前或者之后）

三、触发器分类：

insert触发器、delete触发器、update触发器

四、创建触发器：

1、创建insert触发器：
create trigger 触发器名
例子：
创建一个表order_log用来记录新增

（1）创建触发器:for each row对每一行生效
delimiter //
create trigger trg_order_inser after insert on orders for each row
begin
insert into order_log(o_date,order_num) values (now(),new.order_num)
end;
//
（2）进行插入操作：自动调用insert触发器
insert into orders(order_date,cust_id) values (''2012-08-15",10001);
//

2、创建delete触发器，类似于insert触发器，只是虚拟表变为old
(1)创建：

（2）使用：

（3）查看delete触发器执行后的结果：

3、update触发器：
（1）
delimiter //
create trigger trg_order_update after update on orders for each row
begin
insert into order_log(o_date,order_num,dml_type) values (now(),old.order_num,‘update’)
end;
//
（2）update orders set…则自动触发update触发器

五、删除触发器

drop trigger trg_order_inser
drop trigger trg_order_update

补充：before update

六、触发器使用注意：

1、mysql5.1支持触发器是初级的。
2、创建触发器时需要特定安全访问权限，但触发器的执行是自动的。
3、应该用触发器保证格式一致性。
4、触发器创建审计跟踪
5、触发器中不能使用存储过程call语句

事务

一、事务概念：

事务保证数据库操作动作完整性，所有对数据库的操作，要么全部操作正确提交，要么出现错误，全部回滚到初始状态！----原子性
在所有操作没有执行完毕之前，其他会话不能够看到中间的改变过程-------隔离性
事务发生前和发生后，数据总额依然匹配-------一致性
事务产生的影响不能撤销-------持久性
如果除了错误，事务不能撤销，只能通过“补偿性事务”

事务特性ACID特性：原子性、隔离性、一致性、持久性

二、事务处理术语：

事务（transaction）
回退(rollback)
提交(commit)
保留点（savepoint）

事务的基本原理

三、控制事务处理

1、使用rollback：回退后，所有的增删改等操作回滚取消到原点

事务存储引擎选择
start transation; ###开启事务
select * from ordertotals;
delete from ordertotals;
select * from ordertotals;
rollback; #或者commit结束事务。

注意：有一些语句会造成事务隐式提交，比如start transaction

具体操作如下图：

2、使用commit(提交):提交之后，就不可以回滚，事务已经结束！

3、使用savepoint(保留点)

保留点设置可以再任意个操作之后并命名，以便于回滚到命名处，自由处理回滚，保留点在一个事务结束之后会自动释放。
使用例子：

通过以上例子我们发现uid=12的被删除，uid=13的被回滚，正是因为设置保留点起的作用，再复杂的事务中，我们可以尽可能多的设置保留点，这样就可以灵活的操作语句啦！

释放保留点

1、一个事务完成自动释放
2、RELEASE SAVEPOINT 保留点名

四、更改默认提交行为：针对每一次数据库链接的

mysql中增删改查命令默认是自动提交的。我用了不太好用，仍然自动提交了，待解决。

存储引擎

一、存储引擎概念：

数据库中对同样的数据，有着不同的存储方式和管理方式，再mysql中这种方式称为存储引擎。

二、常用表的引擎：

1、Myisam:批量插入速度快，不支持事务，锁表
2、InnoDB：批量插入速度相对慢，支持事务，锁行

全文索引：目前5.5版本，myisam，innoDB 都已经支持。

索引

一、索引概念：

给数据加上目录，快速查询到数据

二、索引分类：

1、btree（二叉树）索引：log2N
2、hash（哈希）索引：1

三、索引优缺点：

1、优点：加快了查询速度
2、缺点：（1）降低了增删改的速度。
（2）增大了表的文件大小（索引甚至比表文件还大）：

注意：当索引非常多，且数据量大时，需要导入数据处理方法：

四、索引与优化使用原则

1、不过度索引
2、索引条件列（where后面最频繁的条件比较适宜索引）
3、尽量索引散列值，过于集中的值不要索引。（如性别：男、女）碰撞性低越好!

五、索引类型：

1、普通索引（index）：加快查询速度

2、唯一索引（unique index）：行上的值不能重复

3、主键索引（primary key）：不能重复

			主键索引必唯一，但唯一索引不一定是主键
			一张表只能有一个主键，但是可以有一个和多个唯一索引

4、全文索引（fulltext index）：

六、索引使用：

		1、 查看表的所有索引：show index from 表名
		2、建立索引：alter table 表名 add index/unique/fulltext 【索引名】（列名）
			（1）建表

（2）加普通索引和全文索引


加主键索引：
alter table 表名 add primary key(列名) //不要加索引名，因为主键只有一个
（3）查看表索引

（4）删除索引
1、删除非主键索引：alter table 表名 drop index 索引名；
2、删除主键索引：alter table 表名 drop primary key;

七、全文索引使用：

1、建立表某个字段索引
2、使用全文索引：
select * from 表名 where match(全文索引名) against ("所查询字词")
######  全文索引不针对非常频繁的词做索引：如this，is，you等。全文索引再mysql默认情况下，对中文意义不大（因为英文单词间有空格区或者标点拆分单词，mysql中文难以区分单词。）