Mysql的一些操作

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;