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]>