索引
普通索引的创建 | CREATE [UNIQUE] INDEX 索引名 ON 表名(字段,…) |
组合索引 | CREATE INDEX索引名ON 表名(字段1,字段2) |
修改索引 | ALTER TABLE 表名 ADD INDEX|KEY 索引名(字段……); |
查看索引 | Show index|keys from 表名; |
删除索引 | drop index 索引名 on 表名; |
视图
视图的创建 | CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名[(column_list)] AS select语句 [WITH [CASCADED | LOCAL] CHECK OPTION] |
修改视图 | ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名 [(column_list)] AS select语句 [WITH [CASCADED | LOCAL] CHECK OPTION] |
查看视图 | SHOW CREATE VIEW 视图名 |
删除视图 | DROP VIEW [IF EXISTS] 视图名1 [,视图名2] ... [RESTRICT | CASCADE] |
触发器
触发器创建 | |
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <触发的SQL语句> | |
删除触发器 | DROP TRIGGER [schema_name.]trigger_name |
查询触发器 | SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] |
游标的应用
DELIMITER // CREATE PROCEDURE cursor_test() BEGIN -- 声明与列的类型相同的四个变量 DECLARE id INT(5); DECLARE pname VARCHAR(20); DECLARE pprice DOUBLE; DECLARE pdescription VARCHAR(20); -- 1、定义一个游标mycursor DECLARE mycursor CURSOR FOR SELECT *FROM shops_info; -- 2、打开游标 OPEN mycursor; REPEAT #开启循环 -- 3、使用游标获取列的值 FETCH NEXT FROM mycursor INTO id,pname,pprice,pdescription; -- 4、显示结果 SELECT id,pname,pprice,pdescription; UNTIL done END REPEAT; -- 5、关闭游标 CLOSE mycursor; END; // DELIMITER ;