关系型数据库大王Mysql——SQL编程

SQL编程

触发器

什么是触发器?

​ 当某个表的数据发生某件事(insert, delete, update), 然后自动触发预先编译好的若干条sql

触发器

1.特点：触发的操作和触发器的sql语句是一个事务操作，具备原子性，要么都执行，要么都不执行

2.作用：保证数据的完整性，起到约束的作用

示例1

mysql> create table emp_count( -> emp_count_id int primary key auto_increment, -> total int); Query OK, 0 rows affected (0.07 sec) mysql> show tables; +--------------------+ | Tables_in_dml_test | +--------------------+ | department | | emp_count | | employee | | user | +--------------------+ 4 rows in set (0.00 sec) mysql> insert into emp_count values(1,0); Query OK, 1 row affected (0.01 sec) mysql> select * from emp_count; +--------------+-------+ | emp_count_id | total | +--------------+-------+ | 1 | 0 | +--------------+-------+ 1 row in set (0.00 sec) mysql> update emp_count set total = 18 where total = 0; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp_count; +--------------+-------+ | emp_count_id | total | +--------------+-------+ | 1 | 18 | +--------------+-------+ 1 row in set (0.00 sec) #临时修改终止符，以免与触发器语句发生冲突 mysql> \d $ #delimiter $ mysql> create trigger emp_count_p after insert -> on employee for each row -> begin -> update emp_count set total = total + 1 where emp_count_id = 1; -> end -> $ Query OK, 0 rows affected (0.01 sec) mysql> show triggers\G *************************** 1. row *************************** Trigger: emp_count_p Event: INSERT Table: employee Statement: begin update emp_count set total = total + 1 where emp_count_id = 1; end Timing: AFTER Created: 2025-11-18 20:10:20.59 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> insert into employee(number,name) values("23123213132",'来俊希')$ Query OK, 1 row affected (0.01 sec) mysql> select * from emp_count$ +--------------+-------+ | emp_count_id | total | +--------------+-------+ | 1 | 19 | +--------------+-------+ 1 row in set (0.00 sec)

示例2

mysql> create table bank( -> b_id int primary key auto_increment, -> value int)$ Query OK, 0 rows affected (0.03 sec) mysql> create table u( -> u_id int primary key auto_increment, -> value int)$ Query OK, 0 rows affected (0.04 sec) #创建触发器 mysql> create trigger u_bank_t after insert -> on u for each row -> begin -> update bank set value = value + 500 where b_id = 1; -> end -> $ Query OK, 0 rows affected (0.02 sec) #查看创建的触发器 mysql> show triggers\G *************************** 1. row *************************** Trigger: emp_count_p Event: INSERT Table: employee Statement: begin update emp_count set total = total + 1 where emp_count_id = 1; end Timing: AFTER Created: 2025-11-18 20:10:20.59 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: u_bank_t Event: INSERT Table: u Statement: begin update bank set value = value + 500 where b_id = 1; end Timing: AFTER Created: 2025-11-18 20:24:50.28 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.00 sec) mysql> insert into bank(b_id,value) values(1,0); Query OK, 1 row affected (0.01 sec) mysql> select * from bank; +------+-------+ | b_id | value | +------+-------+ | 1 | 0 | +------+-------+ 1 row in set (0.00 sec) mysql> insert into u(u_id,value) values(1,500); Query OK, 1 row affected (0.01 sec) mysql> select * from bank; +------+-------+ | b_id | value | +------+-------+ | 1 | 500 | +------+-------+ 1 row in set (0.00 sec) mysql> select * from u; +------+-------+ | u_id | value | +------+-------+ | 1 | 500 | +------+-------+ 1 row in set (0.00 sec) mysql> select value from u where u_id = 1; +-------+ | value | +-------+ | 500 | +-------+ 1 row in set (0.00 sec) mysql> select value from u group by u_id having max(value); +-------+ | value | +-------+ | 500 | +-------+ 1 row in set (0.00 sec) mysql> insert into bank(value) select value from u group by u_id having max(value); Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from bank; +------+-------+ | b_id | value | +------+-------+ | 1 | 500 | | 2 | 500 | +------+-------+ 2 rows in set (0.00 sec)

存储过程

什么是存储过程

事先经过编译并存储在数据库中的一段sql语句集合

示例1

mysql> create procedure emp_count() -> begin -> select count(emp_count_id) from emp_count; -> end -> $ Query OK, 0 rows affected (0.01 sec) #查看创建的存储过程 mysql> show create procedure emp_count\G$ *************************** 1. row *************************** Procedure: emp_count sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `emp_count`() begin select count(emp_count_id) from emp_count; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) #调用存储过程 mysql> call emp_count(); -> $ +---------------------+ | count(emp_count_id) | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec)

示例2

mysql> create table t1( -> id int primary key auto_increment, -> password varchar(255)); -> $ Query OK, 0 rows affected (0.03 sec) mysql> create procedure insert_many_date(in total_row) -> begin -> declare i int default 1; -> while (i < rows) do -> insert into t1 values(i,md5(i)); -> set i = i + 1; -> end while; -> end -> $ mysql> create procedure user_password(in pwd varchar(255)) -> begin -> select * from user where password = pwd; -> end$ Query OK, 0 rows affected (0.01 sec) mysql> call user_password("111")$ Empty set (0.01 sec)

示例3

mysql> select * from user; -> $ +---------+----------+----------+------+------------+ | user_id | username | password | name | hire_date | +---------+----------+----------+------+------------+ | 1 | ljx | ljxbbfjw | cj | 2006-06-06 | | 2 | ljx | ljxbbfjw | cj | 2006-06-06 | | 3 | lisi | 123 | ls | 2007-07-07 | | 4 | wangwu | 123 | ww | 2008-08-08 | | 5 | cj | 123 | cj | 2009-09-09 | +---------+----------+----------+------+------------+ 5 rows in set (0.00 sec) mysql> create procedure user_password(in pwd varchar(255)) -> begin -> select * from user where password = pwd; -> end$ Query OK, 0 rows affected (0.01 sec) mysql> call user_password("123")$ +---------+----------+----------+------+------------+ | user_id | username | password | name | hire_date | +---------+----------+----------+------+------------+ | 3 | lisi | 123 | ls | 2007-07-07 | | 4 | wangwu | 123 | ww | 2008-08-08 | | 5 | cj | 123 | cj | 2009-09-09 | +---------+----------+----------+------+------------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

示例4

mysql> select @a; -> $ +------------+ | @a | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> set @a = 1; -> $ Query OK, 0 rows affected (0.01 sec) mysql> select @a; -> $ +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> \d $ mysql> create procedure user_count_a(out number int) -> begin -> select count(1) into number from user; -> end$ Query OK, 0 rows affected (0.01 sec) mysql> show create procedure user_count_a\G$ *************************** 1. row *************************** Procedure: user_count_a sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `user_count_a`(out number int) begin select count(1) into number from user; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> call user_count_a(@a); -> $ Query OK, 1 row affected (0.01 sec) mysql> select @a -> $ +------+ | @a | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> \d ; mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)

示例5

mysql> \d $ mysql> create procedure count_emp_name(in dep_name varchar(255),out count_emp int) -> begin -> select count(*) into count_emp from employee where department_NO = (select number from department where name = dep_name); -> end$ Query OK, 0 rows affected (0.02 sec) mysql> \d ; mysql> show create procedure count_emp_name\G; *************************** 1. row *************************** Procedure: count_emp_name sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `count_emp_name`(in dep_name varchar(255),out count_emp int) begin select count(*) into count_emp from employee where department_NO = (select number from department where name = dep_name); end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> call count_emp_name("上海中心",@a); Query OK, 1 row affected (0.00 sec) mysql> select @a -> ; +------+ | @a | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> select count(*) from employee where department_NO = (select number from department where name = "上海中心"); +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)