mysql 学习（2）

mysql 开启事务：start transaction ; commit 提交 rollback 回滚
查询事务级别 ：select @@tx_isolation
可以设置 autocommit 为0
导入sql语句:source xxx.sql
在mysql中扩展:/!5002*code...*/指定mysql版本中才会执行的sql语句，在其他数据库中会把它当做注释

1.trigger 触发器
CREATE TRIGGER triggerdemo 
BEFORE INSERT ON bb_menu_new
FOR EACH ROW BEGIN
/*DELETE FROM bb_menu_new  where id=1;*/
SET @x=666;
END;
error（错误）：
because it is already used by statement which invoked this stored function/trigger.
原因：MYSQL的限制触发器中不能对本表进行操作，否则没完没了的递归
解决办法：用set

2.view （视图）
创建视图
CREATE VIEW v AS SELECT *  FROM bb_menu_new t limit 0,3;
更改视图
ALTER VIEW v AS 
SELECT * FROM bb_menu_new;

algorithm(算法) MERGE 
在某些查询下（视图和基表没有一对一，则视图是不可更改基表的数据帝，反之亦然）；
CREATE ALGORITHM = MERGE VIEW v_merge(vc1,vc2) AS 
SELECT text,url FROM bb_menu_new WHERE id>1 GROUP BY id;

插入
CREATE TABLE t1(a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a<2 
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a>0
WITH LOCAL CHECK OPTION
;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a>0
WITH CASCADED CHECK OPTION
;

3.索引
CREATE TABLE indexdemo(name varchar(255)) ENGINE = MEMORY;
CREATE INDEX name_index USING BTREE ON indexdemo(name(4));