索引
|
普通索引的创建 |
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 ;