数据库约束与索引

DDL(Data Definition Language，数据定义语言，DROP\ ALTER\ CREATE)

DML(Data Manipulation Language，数据操纵语言，INSERT\UPDATE\DELETE)

DCL(Data Control Language，数据控制语言，GRANT)

DQL(Data Query Language，数据查询语言，SELECT)

一、约束

1.主键约束

-- 添加主键约束：保证数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2, ..., 字段名n)
-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

2 外键约束

-- 添加外键约束
ALTER TABLE 表名1 ADD CONSTRAINT 外键名称 FOREIGN KEY(表名1的字段名) REFERENCES 表名2(表名2的字段名);
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3 唯一约束

-- 为字段添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1, 字段名2, ..., 字段名n);
-- 删除字段的唯一约束
ALTER TABLE 表名 DROP KEY 约束名称;

4 非空约束

-- 为字段添加非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NOT NULL;
-- 删除字段非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;

5 默认值约束

-- 为字段添加默认值
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
-- 删除字段的默认值
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

6 自增约束

-- 为字段添加自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型 AUTO_INCREMENT;
-- 为字段删除自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型;

二、索引

1 什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引可以对比书籍的目录来理解。

2 索引的作用

• 保证数据的准确性

• 提高检索速度

• 提高系统性能

3 索引的类型

• 唯一索引（UNIQUE）：不可以出现相同的值，可以有NULL值

• 普通索引（INDEX）：允许出现相同的索引内容

• 主键索引（PRIMARY KEY）：不允许出现相同的值

• 全文索引（FULLTEXT INDEX）：可以针对值中的某个单词，但效率确实不敢恭维

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

4. 索引的创建、查看、删除

-- 创建索引
ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名称;

5. 使用索引的注意事项

• 虽然索引大大提高了查询速度，但也会降低更新表的速度，比如对表进行INSERT,UPDATE和DELETE操作，此时，数据库不仅要保存数据，还要保存一下索引文件

• 建立索引会占用磁盘空间的索引文件。如果索引创建过多（尤其是在字段多、数据量大的表上创建索引），就会导致索引文件过大，这样反而会降低数据库性能。因此，索引要建立在经常进行查询操作的字段上

• 不要在列上进行运算（包括函数运算），这会忽略索引的使用

• 不建议使用like操作，如果非使用不可，注意正确的使用方式。like '%查询内容%'不会使用索引，而like '查询内容%'可以使用索引，这是因为SQL查询满足的是最左匹配原则

• 避免使用IS NULL、NOT IN、<>、!=、OR操作，这些操作都会忽略索引而进行全表扫描