关系型数据库MySQL之触发器和表数据操作

最新推荐文章于 2024-07-02 09:36:06 发布

JiekeXu

最新推荐文章于 2024-07-02 09:36:06 发布

阅读量737

点赞数

分类专栏：数据库文章标签： mysql 触发器 update delete insert

本文链接：https://blog.csdn.net/JiekeXu/article/details/80502378

版权

数据库专栏收录该内容

32 篇文章 6 订阅

订阅专栏

一、触发器（TRIGGER）

触发器是MySQL的数据库对象之一，该对象与编程语言中的函数非常类似，都需要申明、执行等。

1.1、创建触发器

1> 创建有一条执行语句的触发器

创建触发器通过SQL语句CREATE TRIGGER 来实现，其语法形式如下：

Create trigger trigger_name
        BEFORE|AFTTER trigger_EVENT
            ON TABLE_NAME FOR EACH ROW trigger_STMT;

解释：trigger_name参数表示所要创建的触发器名字，在具体创建触发器时，触发器标识符不能与已存在的触发器重复，建议触发器的名字命名为trigger_XXX或者tri_XXX;BEFORE和AFTER参数指定了触发器执行时间，其中前者是指在触发器事件之前执行触发器语句，后者是指在触发器事件之后执行触发器语句; trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT、UPDATE语句；TABLE_NAME参数表示触发事件操作表的名字；FOR EACH ROW 参数表示任何一条记录上的操作满足触发事件都会触发该触发器；trigger_STMT参数表示激活触发器后被执行的语句。

Create table t_diary(diaryno int,tablename varchar(20),diarytime datetime);
CREATE TABLE `t_dept` (
  `loc` varchar(40) DEFAULT NULL,
  `dname` varchar(20) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

eg:create trigger tri_diarytime
        Before insert on t_dept for each row
            Insert into t_diary values(NULL,’t_dept’,now());
##该触发器当向t_dept表中插入任意一条数据时，就会在插入之前向表t_diary中插入当前时间。
Insert into t_dept values (’haidian’,’caiwudept’,1);
Select * from t_diary;

2> 创建包含多条执行语句的触发器

语法形式如下：

Create trigger trigger_name
            Before|after trigger_EVENT
              ON table_name for each row
                BEGIN
                Trigger_STMT
                END;

在mysql中，一般使用“；”符号作为语句的结束符号，可是在创建触发器时需要用到“；”符号作为语句的结束符号。为了解决这个问题，可以使用关键字delimiter语句，例如：“DELIMITER $$”，可以用来实现将结束符号设置成“$$”。

eg:DELIMITER $$
	   CREATE TRIGGER tri_diarytime2
			AFTER INSERT 
				ON t_dept for each row
					Begin
						Insert into t_diary values(NULL,’t_dept’,now());
						Insert into t_diary values(NULL,’t_dept’,now());
					End
					$$

    DELIMITER ;

上述语句中，通过“DELEMITER $$”语句设置结束符号为“$$”;然后在BEGIN和END之间编写了执行语句列表，最后通过“DELEMITER ;”语句将结束符号还原成默认结束符号“;”。

Insert into t_dept values(‘chaoyang’,’xinxi’20);

Select * from t_diary;

1.2、查看触发器

1> 通过show triggers语句查看触发器

定义好触发器之后，可以使用：

show triggers\G

查看触发器的详细信。

2> 也可通过查看系统表triggers实现查看触发器

在数据库Information_schema中可使用如下语句查看触发器：

Use Information_schema;

Desc triggers;

注意：删除t_dept表时所建立的触发器将不复存在，建立触发器时要写正确名字，不能有中文符号。

1.3、删除触发器

MySQL5.5中删除触发器使用drop trigger语句：

Eg:drop trigger tri_diarytime;

Show triggers \G

二、表数据操作

表数据操作此次主要是对插入、更改、删除（insert、update、delete）这三个方面进行简单的操作。

2.1插入数据

插入完整数据记录
插入数据记录一部分
插入多条数据记录
插入查询结果

补充：建完表后设置表的字段主键约束和自动增加：

alter table t_dept add primary key auto_increment(deptno);

建完表后设置表的字段为默认值：

alter table t_dept alter column deptno 
    set default 10;

2.1.1插入完整数据记录

插入完成记录通过insert into 语句来实现；语法如下：

INSERT INTO TAB_NAME(F1,F2,F3,F4,…Fn) 
        VALUES(V1,V2,V3,V4,…Vn);   #列名要和值一一对应
或者：INSERT INTO TAB_NAME         
        VALUES(V1,V2,V3,V4,…Vn);
eg: desc t_dept;
   insert into t_dept(loc,dname,deptno)
        values(‘beijing’,’orgino’,11);

2.1.2插入数据记录一部分，语法如下：

INSERT INTO TAB_NAME(F1,F2,F3,F4,…Fn) 
VALUES(V1,V2,V3,V4,…Vn);   #列名要和值一一对应
eg: desc t_dept;
   insert into t_dept(dname,deptno) values(’services’,11);

2.1.3插入多条数据记录

INSERT INTO TAB_NAME(F1,F2,F3,F4,…Fn) 
VALUES(V11,V12,V13,V14,…V1n),
            (V21,V22,V23,V24,…V2n),
(V31,V32,V33,V34,…V3n),
            (Vm1,Vm2,Vm3,Vm4,…Vmn); #列名要和值一一对应
或者：INSERT INTO TAB_NAME 
VALUES(V11,V12,V13,V14,…V1n),
            (V21,V22,V23,V24,…V2n),
(V31,V32,V33,V34,…V3n),
            (Vm1,Vm2,Vm3,Vm4,…Vmn); #列名要和值一一对应

eg:inset into t_dept
            values(‘haidian1’,’xinxi1’,1),
                 (‘haidian2’,’xinxi2’,2),
                 (‘haidian3’,’xinxi3’,3),
                 (‘haidian4’,’xinxi4’,4);
Select * from t_dept;

2.1.4插入查询结果

通过查看帮助文档还可以使用查询结果插入到表中，具体语法结构如下：

INSERT INTO Tab_name1(F1,F2,…Fn)
        Select (F1,F2,…Fn)
            From tab_name2
                Where …
eg: create table t_loder(id int,name varchar(20),dname varchar(40),loc varchar(40));
insert into t_loder values(1,'gsau1','dept1','chaoyaang1');
insert into t_loder(id,name,dname) values(2,'gsau2','dept2');
insert into t_loder values(3,'gsau3','dept3','chaoyaang3');

insert into t_dept(loc,dname,deptno) 
        select loc,dname,id from t_loder;

2.2更新数据

更新数据包括更新特定数据记录和更新所有数据记录。

2.2.1更新特定数据使用update，具体语法如下：

UPDATE tab_name 
    SET f1=v1,f2=v2
        WHERE condition;
eg:update t_dept
        set loc=’lanzhou’
            where dname=’XINXI’;

2.2.2更新所有数据使用update，具体语法如下：

UPDATE tab_name 
    SET f1=v1,f2=v2,f3=v3
        WHERE condition;

在上述语句中，为了更新所有的数据记录，参数condition需要满足表tab_name中所有的数据记录或者无关键字WHERE语句。

eg:update t_dept
        set loc=’lanzhou’
    where deptno<100;

eg:update t_dept
        set loc=’gansu’;

注意：要是某个字段设置为主键唯一约束的话不能全部更新，不然会报错ERROR 1062 (23000): Duplicate entry ‘10’ for key ‘PRIMARY*’！*

2.3删除数据

删除数据包括删除特定数据记录和和删除所有记录。

2.3.1删除特定数据使用delete from，具体语法如下：

DELETE FROM tab_name
        WHERE condition;
eg:delete from t_dept 
    where dname=’dept1’;
      select * from t_dept;

2.3.2删除所有数据使用delete from，具体语法如下：

DELETE FROM tab_name
        WHERE condition;

在上述语句中，为了删除所有的数据记录，参数condition需要满足表tab_name中所有的数据记录或者无关键字WHERE语句。

eg:delete from t_dept 
        where deptno<=60;
eg:delete from t_dept;  # 删除整个表数据，但表结构依然存在
    select * from t_dept;

注意：使用delete删除数据，但表结构依然存在，truncate也是删除数据结构依然存在，drop删除整个表包括结构。

三者区别具体如下所述：

程度从强到弱

1、drop  table tab_name;
drop将表格直接删除，没有办法找回

2、truncate (table) tab_name;
删除表中的所有数据，不能与where一起使用

3、delete from tab_name (where)
*删除表中的数据(可制定某一行)*

两者区别：truncate和delete的区别：

1、事务：truncate是不可以rollback的，但是delete是可以rollback的;原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback;

2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引;

3、truncate 不能触发任何Delete触发器;

4、delete 删除可以返回行数.