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

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

1
2
3
4
5
6
7
8
9
10
11
12
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;

1
2
3
4
5
6
Create trigger trigger_name
Before|after trigger_EVENT
ON table_name for each row
BEGIN
Trigger_STMT
END;

1
2
3
4
5
6
7
8
9
10
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 ;

1
2
Insert into t_dept values(‘chaoyang’,’xinxi’20);
Select * from t_diary;

1
show triggersG

1
2
Use Information_schema;
Desc triggers;

1
2
Eg:drop trigger tri_diarytime;
Show triggers G

1
alter table t_dept add primary key auto_increment(deptno);

1
2
alter table t_dept alter column deptno
set default 10;

1
2
3
4
5
6
7
8
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);

1
2
3
4
5
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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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;

1
2
3
4
5
6
7
8
9
10
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;

1
2
3
4
5
6
UPDATE tab_name
SET f1=v1,f2=v2
WHERE condition;
eg:update t_dept
set loc=’lanzhou’
where dname=’XINXI’;

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

1
2
3
4
5
eg:update t_dept
set loc=’lanzhou’
where deptno<100;
eg:update t_dept
set loc=’gansu’;

1
2
3
4
5
DELETE FROM tab_name
WHERE condition;
eg:delete from t_dept
where dname=’dept1’;
select * from t_dept;

1
2
DELETE FROM tab_name
WHERE condition;

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

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

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

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