企业官网建设流程全解析

深圳有哪些网站公司,域名cn是个什么网站,wordpress文章分享插件,wdcp wordpress 速度慢SQL编程 触发器 什么是触发器? ​ 当某个表的数据发生某件事(insert, delete, update), 然后自动触发预先编译好的若干条sql 触发器 1.特点#xff1a;触发的操作和触发器的sql语句是一个事务操作#xff0c;具备原子性#xff0c;要么都执行#xff0c;要么都不执行…SQL编程触发器什么是触发器?​ 当某个表的数据发生某件事(insert, delete, update), 然后自动触发预先编译好的若干条sql触发器1.特点触发的操作和触发器的sql语句是一个事务操作具备原子性要么都执行要么都不执行2.作用保证数据的完整性起到约束的作用示例1mysql 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)示例2mysql 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语句集合示例1mysql 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 DEFINERroot% 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)示例2mysql 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)示例3mysql 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)示例4mysql 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 DEFINERroot% 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)示例5mysql \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 DEFINERroot% 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)