第5章 索引与数据完整性约束

-MYSQL访问表中的行的的方法：顺序访问（逐行浏览表）、索引访问
索引
功能描述：根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，可以直接根据该列上的索引找到对应行的位置，从而快速地查找到数据
分类：
普通索引（INDEX）
唯一性索引（UNIQUE）
主键（PRIMARY）：每个表只能有一个主键
全文索引（FULLTEXT）：只能在VARCHAR或TEXT类型的列上创建
创建索引
CREATE INDEX、ALTER TABLE语句或在创建表时创建索引
－－－CREATE INDEX－－－
语法格式：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tb1_name(index_col_name,...)
其中：index_col_name的格式为：
col_name[(LENGTH)][ASC|DESC]
index_name:索引名称，索引在一个表中名称必须是唯一的
USING index_type：指定索引类型，MYSQL支持的类型有BTREE和HASH，如
果不指定USING子句，MYSQL自动创建一个BTREE索引index_col_name：LENGTH使用列的前LENGTH个字符创建索引。

如果索引列长度超过索引长
度的上限（256个字符），则需要利用前缀来进行索引
注1：可以在一个索引的定义中包含多个列，中间用逗号隔开，但它们要属于同一个表，这样的索引叫复合索引
－－－ALTER TABLE－－－
语法格式：
ALTER[IGNORE] TABLE tb1_name
ADD INDEX[index_name][index_type](index_col_name,...) 添加索引
|ADD [CONSTRAINT [symbol]]PRIMARY KEY [index_type](index_col_name,...)添加主键|ADD [CONSTRAINT [symbol]]UNIQUE [index_name][index_type](index_col_name,...) 添加唯
一性索引
|ADD [FULLTEXT|SPATIAL][index_name](index_col_name,...) 添加全文索引
|ADD [CONSTRAINT [symbol]]FOREIGN KEY [index_name](index_col_name,...)[reference_definition] 添加外键
|DISABLE KEYS
|ENABLE KEYS
index_type：语法格式为：USING{BTREE|HASH}
CONSTRAINT [symbol]:为主键、UNIQUE键、外键定义一个名字
DISABLE KEYS|ENABLE KEYS：只在MYISAM表中有用，使用ALTER TABLE...DISABLE KEYS 可以让MYSQL更新表时停止更新MYISAM表中的非唯一性索引
注：使用PRIMARY KEY的列，必须是一个具有NOT NULL属性的列
－－－创建表时创建索引－－－
语法格式：
CREATE [TEMPORARY] TABLE[IF NOT EXISTS] tb1_name
[([column_definition],...|[(index_definition)]
[table_option][select_statement]
其中，index_definition为索引项：
[CONSTRAINT [symbol]]PRIMARY KEY [index_type](index_col_name,...) 主键|{INDEX|KEY}[index_name][index_type](index_col_name,...) 索引
|[CONSTRAINT [symbol]]UNIQUE [INDEX][index_name][index_type](index_col_name,...) 唯一性索引
|[FULLTEXT|SPATIAL][index_name](index_col_name,...) 全文索引
|[CONSTRAINT [symbol]]FOREIGN KEY [index_name](index_col_name,...)[reference_definition] 外键
删除索引：
DROP INDEX 、ALTER TABLE语句
DROP INDEX 语法格式：
DROP INDEX index_name ON ta1_nmae
ALTER TABLE语法格式：
ALTER[IGNORE] TABLE ta1_name
|DROP PRIMARY KEY 删除主键（不需提供索引名称，因为一个表中只有一个主键）|DROP INDEX index_name删除索引
|DROP FOREIGN KEY fk_symbol删除外键
注：在更新表中索引列上的数据时，对索引也需要更新
数据的完整性约束
完整性：一致性、正确性
主键约束
可以用两种方式来定义主键约束：作为列或表的完整性约束
作为列的完整性约束：在列定义的时候加上关键字PRIMARY KEY
作为表的完整性约束：在语句最后加上一条PRIMARY KEY(Ccol_name,...)语句
示例：
CREATE TABLE tb1_name
(学号，VARCHAR (6) NOT NULL PRIMARY KEY,
出生日期，VARCHAR(8) NOT NULL );
CREATE TABLE tb1_name
(学号，VARCHAR (6) NOT NULL ,
出生日期，VARCHAR(8) NOT NULL ),
PRIMARY KEY(学号);
注：当表中的主键为复合键时，只能定义为表的完整性约束
替代键约束
UNIQUE 关键字
CREATE TABLE tb1_name
(学号，VARCHAR (6) NOT NULL PRIMARY KEY,
出生日期，VARCHAR(8) NOT NULL UNIQUE);
参照完整性约束
定义外键的语法格式见索引部分，其中reference_definition的语法格式如下：
REFERENCES tb1_name [(index_col_name,...)]
|ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
|ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
外键被定义为表的完整性约束，reference_definition中包含了外键所参照的表和列，还可以声明参照对象；
tb1_name:外键所参照的表名，这个表叫被参照表，而外键所在的表叫做参照表
RESTRICT：默认的参照动作
CHECK完整性约束
在创建表的时候定义，可以定义为列完整性约束（包含在列自身的定义中），也可定义为表完整性约束
语法格式：
CHECK(expr)
expr:表达式，指定需要检查的条件
命名完整性约束
CONSTRAINT 关键字
语法格式：
CONSTRAINT[symbol]
symbol:指定的名字，在完整性约束的前面被定义，在数据库中必须是唯一的。

如果没有被指出，则MYQSL自动创建这个名字。

只能给表完整性约束指定，而无法给列完整性约束指定名字
删除完整性约束
DROP TABLE、ALTER TABLE 关键字
DROP TABLE：所有的完整性约束都被自动删除了
ALTER TABLE：完整性可以独立地被删除
举例：删除表XS的主键
ALTER TABLE xs DROP PRIMARY KEY;。