Mysql基础回顾(下篇)

2016-07-28 17:55:46 Mysql 1441 0

更新时间2016年7月29日 01:55:46

阶段四

触发器

四要素

监视地点 监视事件 触发事件 触发事件

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行。

创建触发器
在MySQL中,创建触发器语法如下:

CREATE TRIGGER trigger_name

trigger_time trigger_event ON tbl_name

FOR EACH ROW

trigger_stmt

其中:

trigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。

由此可见,可以建立6种触发器,即:BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器,因此在一个表上最多建立6个触发器。

trigger_event 详解

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外,还定义了 LOAD DATA 和 REPLACE 语句,这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中,相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像,只是在表中有 primary key 或 unique 索引时,如果插入的数据和原来 primary key 或 unique 索引一致时,会先删除原来的数据,然后增加一条新数据,也就是说,一条 REPLACE 语句有时候等价于一条。
INSERT 语句,有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

INSERT 型触发器:插入某一行时激活触发器,可能通过 INSERT、LOAD DATA、REPLACE 语句触发;
UPDATE 型触发器:更改某一行时激活触发器,可能通过 UPDATE 语句触发;
DELETE 型触发器:删除某一行时激活触发器,可能通过 DELETE、REPLACE 语句触发。

BEGIN … END 详解
在MySQL中,BEGIN … END 语句的语法为:

BEGIN

[statement_list]

END
其中,statement_list代表一个或多个语句的列表,列表内的每条语句都必须用分号(;)来结尾。
而在MySQL中,分号是语句结束的标识符,遇到分号表示该段语句已经结束,MySQL可以开始执行了。因此,解释器遇到statement_list 中的分号后就开始执行,然后会报出错误,因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令(DELIMITER 是定界符,分隔符的意思),它是一条命令,不需要语句结束标识,语法为:
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号,默认的是分号(;),我们可以把它修改为其他符号,如$:
DELIMITER $
在这之后的语句,以分号结束,解释器不会有什么反应,只有遇到了$,才认为是语句结束。注意,使用完之后,我们还应该记得把它给修改回来。

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

DELIMITER $

create trigger tri_stuInsert after insert

on student for each row

begin

declare c int;

set c = (select stuCount from class where classID=new.classID);

update class set stuCount = c + 1 where classID = new.classID;

end$

DELIMITER ;


变量详解
MySQL 中使用 DECLARE 来定义一局部变量,该变量只能在 BEGIN … END 复合语句中使用,并且应该定义在复合语句的开头,

即其它语句之前,语法如下:

DECLARE var_name[,...] type [DEFAULT value]
其中:
var_name 为变量名称,同 SQL 语句一样,变量名不区分大小写;type 为 MySQL 支持的任何数据类型;可以同时定义多个同类型的变量,用逗号隔开;变量初始值为 NULL,如果需要,可以使用 DEFAULT 子句提供默认值,值可以被指定为一个表达式。

对变量赋值采用 SET 语句,语法为:

SET var_name = expr [,var_name = expr] ...

Before 与 After的区别
After     是先完成数据的增删改,再触发,触发的语句晚于监视的增删改,无法影响前面的增删改动作

Before 是先完成触发,再增删改

修改一次触发器,就得把之前的触发器给删了,不然原来的触发器还会继续生效

delimiter $

create trigger t1

before insert on indent

for each row

begin

if new. buy_num > 5 then

    set new.buy_num= 5;

end if;

update indent_details set num = num – buy_num where id =new.indent_id;

end$

delimiter ;


NEW 与 OLD 详解

上述示例中使用了NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示

触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法:NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。

查看触发器

和查看数据库(show databases;)查看表格(show tables;)一样,查看触发器的语法如下:

SHOW TRIGGERS [FROM schema_name];
其中,schema_name即 Schema 的名称,在 MySQL 中 Schema 和 Database 是一样的,也就是说,可以指定数据库名,这样就

不必先“USE database_name;”了。

删除触发器

和删除数据库、删除表格一样,删除触发器的语法如下:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:

①如果 BEFORE 触发器执行失败,SQL 无法正确执行。
②SQL 执行失败时,AFTER 型触发器不会触发。
③AFTER类型的触发器执行失败,SQL 会回滚。


 

预计更新时间2016年7月29日晚

事务

>对一组操作,要么全部执行,要么全部取消,只要没完成此次事务,其结果不会被看到

ACID特性

原子性:数据库事务不可再分的原则
一致性:要么一起执行,要么一起取消
隔离型:每个事务对其他事务是不可见的
持久性:当事务完成后,其影响会被保留,不能撤销

用法

start transaction;

sql语句1;

sql语句2;

...

若成功则提交commit;

若失败则回滚rollback;

     [示例]  HLZ给Leo转账,并成功

start transaction;

update bank  set  money= money -100 where name='HLZ';

update bank  set  money= money+100  where name='Leo';

commit;


索引

——这部分是简单引入,下一篇为优化的正文,我将进一步谈谈各种索引
索引是针对数据所建立的目录
优点:可以加快查询速度
缺点:降低了增删改的速度
原理:类似新华字典,这里就以新华字典为例把。
           新华字典根据 拼音或者笔画 可以搜索到对应的字的位置
           在对应字的位置也有对应的拼音与笔画
      引入索引之前我们先对比一下MyIsam与InnoDB存储引擎的各自特点,
       因为一会儿我们会用到各自的优点,如图所示



现在我们来稍稍谈谈个别索引的内部算法   (为方便书写对数表达式,我在这里用  lb  表示以2为底的对数)
设有N条随机记录,如果不用索引,平均要用查找   N/2    次。
《数据结构》可知
如果用btree(二叉树)索引          
 lb(N)    次

如果用hash(哈希)散列索引       1        次

一般有索引的数据都比较大,所以,换服务器的时候,记得先把索引去掉,导入数据库后再统一设置索引

索引类型
普通索引:index 只是加快了查询速度
唯一索引:unique index 行上的值不能重复,一个表中,可以有多个
主键索引:primary key 不能重复,这具有唯一索引的功能,但主键索引一个表中只能有一个
全文索引:fulltext index 不能重复
这里引入模糊查询与索引的关系(模糊查询有点像正则表达式查询,具体是怎样的,本篇文章就不讲了,请自行百度)
模糊查询的时候 "%输入的内容%"不能使用索引,   "输入的内容%"可以用到索引
索引创建原则
1.不要过度索引
2.在where条件最频繁的列上加
3.尽量索引散列值,过于集中的值索引意义不大


查看一张表上的所有索引
Show index from 表名
建立索引
Alter table 表名 add index / unique / fulltext 索引名 (列名)
    [示例] alter table indent add unique  name (name)
Alter table 表名 add primary key (列名)     ##这里不加索引名是因为主键只有一个
删除索引
Alter table 表名 drop index 索引名
Alter table 表名 drop primary key
全文索引
>用法
   Match(全文索引名) against ('输入的内容');
>停止词
   全文索引不针对非常频繁的词做索引,例如this,is,you,my等等
>全文索引与中文搜索
   全文索引在默认情况下,有中文无法做到索引,因为英文有空格或者标点符号来拆成单词,进而对单词进行索引整理
但是对中文语句,进行分词太难了,mysql无法对中文语句进行分词,如果非得实现,得用中文词库处理。(用sphinx处理,以后的文章我会说说怎么用它)
什么情况下使用索引
表的主关键字
表的字段唯一约束
直接条件查询的字段
查询中与其它表关联的字段
查询中排序的字段
查询中统计或分组统计的字段

什么情况下应不建或少建索引
表记录太少
经常插入、删除、修改的表

存储过程

>类似函数,进行封装、调用。(相当于sql编程)
>要点:封装sql、参数、控制结构、循环
查看现有的存储过程
Show procedure status
删除存储过程
Drop procedure status
调用存储过程
Call 存储过程的名字();

mysql里面只能用set来赋值
    [示例]
delimiter $
create procedure test1(n smallint)
begin
    declare i int;
    declare s int;
    set i  = 1;
    set s = 0;
    while i <= n do
        set s= s + i;
        set i = i  + 1;
   end while;
   select s;
end$
delimiter ;
其中   select s;    是为显示计算结果

与函数的区别
①定义时候的方法
若是定义函数
create function xxx();
②存储过程没有返回值,但是函数可以有


注:若无特殊说明,文章均为云天河原创,请尊重作者劳动成果,转载前请一定要注明出处