mysql中触发器，视图，索引的简单理解

1.触发器

触发器在之前的项目中, 应用的着实不多, 没有办法的时候, 才会去用这个. 因为这个东西在后期并不怎么好维护, 也容易造成紊乱.

首先我们先建3张表：

CREATE TABLE `tch_teacher` (
    `Id` INT (11) NOT NULL AUTO_INCREMENT,
    `Sex` SMALLINT (6) DEFAULT NULL,
    `BId` VARCHAR (36) DEFAULT NULL,
    `No` VARCHAR (20) DEFAULT NULL,
    `Name` VARCHAR (30) DEFAULT NULL,
    `IsDeleted` INT (1) DEFAULT  '0' PRIMARY KEY (`Id`),
    KEY `Index_Sex` (`Sex`) USING BTREE,
    KEY `Index_BId` (`BId`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 21 DEFAULT CHARSET = latin1;

CREATE TABLE `tch_contact` (
    `Id` INT (11) NOT NULL AUTO_INCREMENT,
    `TId` INT (11) DEFAULT NULL,
    `QQ` VARCHAR (15) DEFAULT NULL,
    `Weixin` VARCHAR (50) DEFAULT NULL,
    `Phone` VARCHAR (15) DEFAULT NULL,
    PRIMARY KEY (`Id`),
    KEY `Index_TId` (`TId`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 11 DEFAULT CHARSET = latin1 COMMENT = '联系方式表';

CREATE TABLE tch_all (
    Id INT NOT NULL,
    Sex SMALLINT,
    BId VARCHAR (36),
    NO VARCHAR (20),
    NAME VARCHAR (30),
    QQ VARCHAR (15),
    Weixin VARCHAR (50),
    Phone VARCHAR (15)
) COMMENT '完整表';

写个插入语句，分别往tch_all和tch_teacher 插入一条 数据，就可以了

1. 触发时机 before/after

这里的触发器, 触发的时机是在tch_teacher表数据插入之后. 也就是说, tch_teacher插入成功了之后, 才会向tch_all表插入数据. 这里有一个点需要注意下. 在tch_teacher插入成功后, 向tch_all插入的时候报错, 那么tch_teacher的新插数据就回被回滚.有插入后触发, 自然就有插入前触发, 只需要将after改成before即可. before触发, 则会先想tch_all插入数据, 再向tch_teacher插入数据. 插入过程中, 不管哪一步失败, 都会回滚数据. 所以不需要担心, 触发不成功的情况下, 会不会造成冗余或者错误数据.

2. 触发方式 insert/update/delete

触发方式, 有插入/修改/删除 时触发. 例子中我只写了插入触发别的两种方式的使用方法是和这个一样的.

3. 原数据引用  old/new

这里有一个问题, 我修改了数据, 那么我怎么引用他们呢? 既然修改了数据, 那肯定是有 修改前数据和修改后新数据 的引用的, 这里直接使用 old 来指向修改前的数据, new 指向修改后的数据. 这里的指向, 是指向的tch_teacher中的数据, 不是tch_all的数据.

4. 注

例子中, 我只用了一句话, 例子嘛, 简单就好. 其实在实际使用过程之中, 不会是这么简单的. 举个例子说吧.很多时候, 由于数据重要性, 不会直接删除数据. 而是选择更新数据状态来表示其已不再使用. 这里就用 isdeleted来表示, 0表示能用, 1表示不再使用.当我更新tch_teacher的isdeleted的值为1的时候, 触发修改触发器, 在触发器中, 我就需要判断 new.isdeleted的值, 从而选择是否删除关系表中的数据.

delimiter $
DROP TRIGGER IF EXISTS tg_update_all ; 
CREATE TRIGGER tg_update_all AFTER UPDATE ON tch_teacher FOR EACH ROW
BEGIN
IF new.isdeleted = 1 THEN
    DELETE FROM tch_all WHERE id = old.id ;
ELSE
    UPDATE tch_all set sex=new.sex, bid=new.bid, NO=new.NO, NAME=new.NAME where id = old.id;
END IF ;
END$
delimiter ;

这个sql就是把tch_teacher当做tch_all的触发器，当改变tch_teacher的数据的时候，会根据条件修改tch_al

2.视图

drop view if EXISTS v_all; -- 删除视图
create view v_all AS    -- 新建视图
select tch_teacher.*,tch_contact.QQ,tch_contact.Weixin,tch_contact.Phone from tch_teacher 
left join tch_contact on tch_teacher.Id=tch_contact.TId

这个是把tch_cntact和tch_teacher创建视图，是用id关联的，执行完就可以在视图中查看了，但是两个表的数据要全，不然视图中会显示不出来

3.索引

首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的sql是 SELECT * FROM award WHERE nickname = 'css'

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname='css'的数据,是不是感觉性能提升了好多咧....

CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

首先先创建一个表，是用来测试的，但是只有数据多的时候才会体现出来效果，给一个mysql循环插入的语句

create procedure pwhile()
begin
declare i int;
set i = 0;
while i <10 do
INSERT INTO award(aty_id,nickname,is_awarded,award_time,account,password,message,created_time,updated_time) VALUES(1,1,1,1,1,11,1,1,1);
set i = i+1;
end while;
end;

call pwhile();

此sql是把存储过程修改掉，如果没有procddure的创建方法，declare是不可使用的，而且此方法执行一次之后就无法执行，需要停止掉，如下：

DROP PROCEDURE pwhile

此sql就可以停止掉了，想插入多少就多少

 

详细