|
1
|
<br data-filtered=
"filtered"
>
|
1.创建数据库
MariaDB [(none)]> create database utec default character set utf8 collate utf8_general_ci;
2.创建数据表
部门表
MariaDB [utec]> create table bu(bu_name varchar(10));
Query OK, 0 rows affected (0.01 sec)
员工信息表
MariaDB [utec]> create table yg_info(id int,name varchar(10),sex enum('f','m'));
Query OK, 0 rows affected (0.01 sec)
员工薪资表
MariaDB [utec]> create table yg_money(id int,money int);
Query OK, 0 rows affected (0.01 sec)
员工部门表
MariaDB [utec]> create table yg_bu(id int,bu_name varchar(10));
Query OK, 0 rows affected (0.01 sec)
员工职位表
MariaDB [utec]> create table yg_identily(id int,jon varchar(10));
Query OK, 0 rows affected (0.00 sec)
3.插入数据
bu
MariaDB [utec]> insert into bu values('caiwu'),('it'),('yewu'),('yanfa'),('gongcheng');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [utec]> select * from bu;
+-----------+
| bu_name |
+-----------+
| |
| caiwu |
| it |
| yewu |
| yanfa |
| gongcheng |
+-----------+
yg_info
MariaDB [utec]> alter table yg_info change id id int not null auto_increment primary key;
MariaDB [utec]> delimiter $
MariaDB [utec]> create procedure insert_info() begin declare y int default 1; while y<10000 do insert into
yg_info(name) values(concat('yg',y)); set y=y+1; end while; end$
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> delimiter ;
MariaDB [utec]> call insert_info(); ##插入9999条数据
MariaDB [utec]> delimiter $
MariaDB [utec]> create procedure insert_id() begin declare y int default 1; while y<10000 do
insert into yg_money(id)values(y);
insert into yg_bu(id)values(y);
insert into yg_identily(id)values(y);
set y=y+1;end while; end$
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> delimiter ;
MariaDB [utec]> call insert_id(); ##更新其余几个表的ID
设定员工ID小于等于5000的 sex 为f 大于5000的为m
设定员工ID小于1000的工资 1500 ID在1001到3000的工资为1400
ID在3001到5000的为1350 ID在5001到7000的工资为 1200
ID为7001到9999的工资为1100
MariaDB [utec]> delimiter $
MariaDB [utec]> create procedure utec_zong()
-> begin
-> update yg_info set sex='f' where id<=5000;
-> update yg_info set sex='m' where id>5000;
-> update yg_money set money=1500 where id<1000;
-> update yg_money set money=1400 where id>=1000 and id<3000;
-> update yg_money set money=1350 where id>=3000 and id<5000;
-> update yg_money set money=1200 where id>=5000 and id<7000;
-> update yg_money set money=1100 where id>=7000 and id<10000;
-> end$
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> delimiter ;
MariaDB [utec]> call utec_zong();
Query OK, 3000 rows affected (0.79 sec)
设置触发 不允许插入ID<1 和ID>10000的
MariaDB [utec]> create trigger bi_yginfo before insert on yg_info for each row begin
if new.id<1 then delete from yg_info where id<1;
elseif new.id>10000 then delete from yg_info where id>10000;
end if; end$
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> delimiter ;
MariaDB [utec]>
设置外键
MariaDB [utec]> alter table yg_money add foreign key(id) references yg_info(id) on delete cascade on update cascade;
Query OK, 10000 rows affected (0.40 sec)
Records: 10000 Duplicates: 0 Warnings: 0
MariaDB [utec]> alter table yg_bu add foreign key(id) references yg_info(id) on delete cascade on update cascade;
Query OK, 9999 rows affected (0.13 sec)
Records: 9999 Duplicates: 0 Warnings: 0
MariaDB [utec]> alter table yg_identily add foreign key(id) references yg_info(id) on delete cascade on update cascade;
Query OK, 9999 rows affected (0.15 sec)
Records: 9999 Duplicates: 0 Warnings: 0
测试下
MariaDB [utec]> insert into yg_money(id)values(10000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`utec`.`yg_money`, CONSTRAINT `yg_money_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yg_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
##外键约束
MariaDB [utec]> insert into yg_info (id)values(10000);
Query OK, 1 row affected, 1 warning (0.01 sec)
MariaDB [utec]> insert into yg_info (id)values(10001);
ERROR 1442 (HY000): Can't update table 'yg_info' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
MariaDB [utec]> ##触发约束
设置触发不允许删除和更新 yg_info的ID列
MariaDB [utec]> create trigger delete_yginfo before delete on yg_info for each row begin if old.id=id then rollback; end if; end$
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.
##mysql不可以给触发器显式或隐式方式开始或结束事务的语句的语句，比如COMMIT,START TRANSACTION,ROLLBACK。
MariaDB [utec]> create procedure rollbk()
-> begin
-> rollback;
-> end$
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> create trigger delete_yginfo before delete on yg_info for each row begin if old.id=id then
call rollbk; end if; end$
Query OK, 0 rows affected (0.01 sec)
MariaDB [utec]> delimiter ;
MariaDB [utec]>
MariaDB [utec]> delete from yg_info where id=1;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause' ##这个报错是因为我的触发禁止删除导致 见下
MariaDB [utec]> drop trigger delete_yginfo;
Query OK, 0 rows affected (0.00 sec)
MariaDB [utec]> delete from yg_info where id=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [utec]> select * from yg_info limit 2;
+----+------+------+
| id | name | sex |
+----+------+------+
| 2 | yg2 | f |
| 3 | yg3 | f |
+----+------+------+
2 rows in set (0.00 sec)
MariaDB [utec]>
本文转自 am2012 51CTO博客，原文链接:http://blog.51cto.com/goome/1966994
