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