mysql 视图、索引、存储过程 、触发器、游标及事务

mysql

视图、索引、存储过程 、触发器、游标及事务
select *  from abc;
id  name
1 zhansan  
2 lisi     
3 wangwu   
4 xiaoming 
6 xw
 
1. 视图
与包含数据的表不一样，视图只包含使用时动态检索数据的查询。作为视图，它不包含表中应该有的任何数据，它包含的是一个sql查询。

创建视图：
create view productcustomers as select cust_name,cust_contact,prod_id 
               from customers,orders,orderitems 
               where customers.cust_id=orders.cust_id 
                        and orderitems.order_num = orders.order_num;

使用视图：select cust_name, cust_contact from productcustomers where prod_id='TNT2';

使用 show create view viewname; 
来查看视图；用drop删除视图，其语法为drop view viewname;
更新视图时，可以先用drop，再用create，也可以使用create or replace view。

2. 存储过程

需要注意的是DELIMITER &&和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

--设置分隔符“//”
DELIMITER //
create procedure proc_a() begin select sum(id) from abc; end;
//
DELIMITER;
--还原分隔符“;"
call proc_a();

delimiter &&
create procedure proc_b(
out p1 decimal(8,2)
)
begin
select min(id) into p1 from abc;
end;
&&
delimiter ;

call proc_b(@pid);

3. 游标 cursor

mysql游标只能用于存储过程，存储过程处理完成后，游标就消失。
(1)，在使用游标之前，必须DECLARE声明定义它。这个过程实际上没有检索数据，它只是定义要使用的select语句。 
(2)，一旦声明之后，必须OPEN打开游标以供使用。这个过程，会执行前面定义的select语句，并存储检索出的数据以供浏览和滚动。 
(3)，游标在打开后，可以使用FETCH语句，分别访问它的每一行，FETCH还将移动游标中的内部行指针，使下一条FETCH语句检索下一行。所以在上例中，FETCH ordernumbers INTO o;表示将检索当前行的order_num到一个名为o的局部声明的变量中。FETCH在REPEAT中，它会反复执行，直到done为真。done为真的条件是： 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 这条语句定义了一个句柄HANDLER，它在条件出现时被执行，也就是当sqlstate ‘02000’ 出现时，set done = 1。 sqlstate ‘02000’ 表示未找到行，当repeat没有更多的行而不能继续循环时，这个条件就会出现。 
注意：用declare定义的局部变量必须在定义任意游标或句柄之前，而句柄必须在游标之后定义。 
(4).在结束游标使用时，必须关闭游标。

delimiter &&
drop procedure proc_m;
create procedure proc_m()
begin
declare o varchar(20);
declare ordernum cursor for select name from abc;
open ordernum;
fetch ordernum into o;
select o;
fetch ordernum into o;
select o;
fetch ordernum into o;
select o;
close ordernum;
end;
&&
delimiter ;

call proc_m();

4. 触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE；INSERT；UPDATE。其他的MySQL语句不支持触发器。只有表才支持触发器，视图不支持。

创建触发器需要给出4条信息： 
1，唯一的触发器名； 
2，触发器关联的表； 
3，触发器应该响应的活动（DELETE、INSERT或UPDATE） 
4，触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，则应该定义两个触发器。

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有： 
1，如果 BEFORE 触发器执行失败，SQL 无法正确执行。 
2，SQL 执行失败时，AFTER 型触发器不会触发。 
3，AFTER 类型的触发器执行失败，SQL 会回滚。

假设系统中有两个表： 
班级表 class(班级号 classID, 班内学生数 stuCount) 
学生表 student(学号 stuID, 所属班级号 classID) 
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

创建有多个执行语句的触发器

DELIMITER ||
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END
||
DELIMITER ;

NEW 和 OLD用来表示触发器的所在表中，触发了触发器的那一行数据。NEW表示经过处理后的新表，OLD表示处理前的旧表。 
另外，OLD 是只读的，不能更新，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器。 
delimiter &
create trigger tri_a after insert on abc for each row
begin

    DECLARE c INT;
    SET c = (SELECT name FROM abc 
    WHERE name=NEW.name);
end
&
delimiter ;

--drop trigger tri_b;
delimiter &

create trigger tri_b after insert on abc for each row
begin

    DECLARE c varchar(50);
    SET c = (SELECT name FROM abc 
    WHERE new.id = 1 );
end
&
delimiter ;

删除触发器：DROP TRIGGER name; 触发器不能更新或覆盖，所以修改触发器只能先删除再创建。 
查看触发器：SHOW TRIGGERS [FROM schema_name];
SHOW TRIGGERS from iu;

insert into abc values(34,'from');

5. 事务

事务四大特征(ACID)
原子性(A)：事务是最小单位，不可再分
一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：事务A和事务B之间具有隔离性
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

commit:提交
rollback：回滚

1.READ UNCOMMITTED(未提交读)。在RU的隔离级别下，事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读。这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用。

2.READ COMMITTED(提交读)。在RC的隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见，举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，会读到最新的数据2。在RC的隔离级别下，会出现不可重复读的问题。这个隔离级别是许多数据库的默认隔离级别。

3.REPEATABLE READ(可重复读)。在RR的隔离级别下，不会出现不可重复读的问题。事务A对数据做的修改，提交之后，对于先于事务A开启的事务是不可见的。举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，仍然只能读到1。在RR的隔离级别下，会出现幻读的问题。幻读的意思是，当某个事务在读取某个范围内的值的时候，另外一个事务在这个范围内插入了新记录，那么之前的事务再次读取这个范围的值，会读取到新插入的数据。Mysql默认的隔离级别是RR，然而mysql的innoDB引擎间隙锁成功解决了幻读的问题。

4.SERIALIZABLE(可串行化)。可串行化是最高的隔离级别。这种隔离级别强制要求所有事物串行执行，在这种隔离级别下，读取的每行数据都加锁，会导致大量的锁征用问题，性能最差。

事务示例：
set session transaction isolation level 
                read-uncommitted--读未提交，也叫脏读
           或   read-committed --不可重复读，也叫读已提交
           或   repeatable-read --可重复读，默认级别
           或   serializable --串行化
           ;
TART TRANSACTION ;
DML 语句
COMMIT ;
/ROLLBACK ;

 mysql默认采用自动提交(AUTOCOMMIT)模式.如果没有显示的开始一个事务,那么每条sql语句都会被当作一个事务执行提交的操作

提交：
  （1）当AUTOCOMMIT=0的时候所有的sql语句都是在一个事务中,直到显示的执行COMMIT和ROLLBACK回滚该事务结束.同时又开始了另外一个新的事务.
  （2） 
        
        开启事务
            START TRANSACTION 
        结束事务
          (1)  COMMIT:      提交 
          (2)  ROLLBACK:    回滚