1. 触发器使用：
mysql> delimiter // mysql> CREATE TRIGGER t_user_add BEFORE insert ON user -> FOR EACH ROW -> BEGIN -> IF NEW.num< 0 THEN -> SET NEW.num = 0; -> ELSEIF NEW.num > 100 THEN -> SET NEW.num = 100; -> END IF; -> END;// mysql> delimiter ;
删除触发器：DROP TROGGER trigger_name
2. 设置变量：
set @sum = 0;
select @sum;
3. 创建视图：
CREATE VIEW userview AS SELECT name, age, concat('major:', major) as major FROM user;
3. 存储过程：
简单的打印，语句为：
mysql> delimiter // mysql> create procedure printStr2(in str varchar(20)) -> begin -> select concat('print:', str); -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimeter ; mysql> call printStr2('hello,world'); +-----------------------+ | concat('print:', str) | +-----------------------+ | print:hello,world | +-----------------------+
循环语句，输出到指定数的和：
mysql> delimiter // mysql> create procedure sp_addtonum(in num int, out sum int) -> begin -> declare i int; -> set i=1; -> set sum=0; -> label:while i<=num do -> set sum=sum+i; -> set i=i+1; -> end while label; -> end;// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call sp_addtonum(100, @sum); Query OK, 0 rows affected (0.00 sec) mysql> select @sum; +------+ | @sum | +------+ | 5050 | +------+ 1 row in set (0.00 sec)
删除存储过程：DROP PROCEDURE sp_name;
