索引及视图

索引

普通索引

创建索引:CREATE INDEX indexName ON mytable(username(length));
添加索引:ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);
删除索引:DROP INDEX [indexName] ON mytable;

唯一索引

创建索引:CREATE UNIQUE INDEX indexName ON mytable(username(length))
添加索引:ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

使用ALTER添加和除索引

添加索引：mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
删除索引：mysql> ALTER TABLE testalter_tbl DROP INDEX c;

显示索引

mysql> SHOW INDEX FROM table_name; \G
……..

视图

语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

撤销视图

DROP VIEW view_name