数据库：索引、约束和策略

前言

数据库索引帮助MySQL高效获取数据的数据结构，对数据表里所记录 的引用指针

目录

前言

一、索引

1、索引的优缺点

2、基本语法

（1）普通索引：

（2）唯一索引：

（3）组合索引（多列索引）：

3、应用实例

二、约束和策略

1、约束和策略概述

2、数据库约束

1、主键约束

2、非空约束

3、唯一约束：

4、默认约束：

5、外键约束

---

一、索引

1、索引的优缺点

优点：提高数据检索的效率（减少了存储引擎需要扫描的数据量），降低数据库的IO成本     

           降低数据排序的成本，降低CPU的消耗

缺点：占用磁盘空间；

           降低更新表的速度；

          会增加数据库服务器写入操作的成本；

          太多的索引会影响查询效率

2、基本语法

（1）普通索引：

特点
普通索引使用没有限制，允许在所有数据类型上创建，可以有重复值和空值。
一个索引只包含单个列，一个表可以有多个单列索引

a、建表时创建
语法：index 索引名 (列名)

b、修改表方式创建
语法：alter table 表名 add index 索引名(列名)
c、直接创建
语法：create index 索引名 on 表名(列名)

（2）唯一索引：

特点：索引值的值必须是唯一，但允许有空值
如果是组合索引，则列值的组合必须是惟一的
a、建表时创建
语法：unique index 索引名 (列名)
b、修改表方式创建
语法：alter table 表名 add unique index 索引名(列名)
c、直接创建
语法：create unique index 索引名 on 表名(列名)
d、添加唯一约束方式
语法：字段名 数据类型 unique 

（3）组合索引（多列索引）：

特点：同时在多个列添加索引
a、建表时创建
语法：index 索引名 (列名1,列名2....)
b、修改表方式创建
语法：alter table 表名 add unique index 索引名(列名1,列名2....)
c、直接创建
语法：create index 索引名 on 表名(列名1,列名2....)

3、应用实例

（1）普通索引

## 普通索引
-- 建表时创建
create table text (
id int primary key,
name varchar(20),
age int,
index name_indx(name)
)
show index from text
-- 修改表方式创建
create table text2 (
id int primary key,
name varchar(20),
age int
)
alter table text2 add index name_indx(name);

show index from text2
-- 直接创建
create table text3 (
id int primary key,
name varchar(20),
age int
)
create index name_indx on text3(name);
show index from text3

（2）唯一索引

-- 建表时创建
create table text4 (
id int primary key,
name varchar(20),
age int ,
unique index un_idx_id(id)
)
show index from text4
-- 修改表方式创建
create table text5 (
id int primary key,
name varchar(20),
age int );
alter table text5 add unique index un_idx_id(id);
show index from text5

-- 直接创建
create table text6 (
id int primary key,
name varchar(20),
age int
);
create unique index un_idx_id on text6(id);
show index from text6
-- 
create table text7 (
id int primary key unique,
name varchar(20),
age int
);
show index from text7

（3）组合索引
 

-- 建表时创建
drop table text8
create table text8 (
id int primary key,
name varchar(20),
age int,
index id_name_age(name,age)
);
show index from text8
-- 修改表方式创建
create table text9 (
id int primary key,
name varchar(20),
age int
);
alter table text9 add index id_name_age(name,age);
show index from text9
-- 直接创建
create table text10 (
id int primary key,
name varchar(20),
age int
);
create index id_name_age on text10(name,age);
show index from text10

二、约束和策略

1、约束和策略概述

约束的作用：对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。约束都是加在列上的，对一列的数据进行限制。
分类：数据库约束主要包括：主键约束、非空约束、唯一约束、默认约束(缺省约束)、外键约束、检查约束(MySQL不支持检查约束)

2、数据库约束

1、主键约束

主键的作用是用来唯一标识一条记录，主键列的特点：非空、唯一。
添加了主键约束的列，列中的值必须是非空（不能是null）和唯一的（各个行的数据都不相同

增加主键：
(1)建表时添加主键
     字段名 数据类型  primary key   /    primary key (字段名)
(2)修改表方式添加主键
     alter table 表名 add constraint primary key (字段名)
删除主键：
     语法：alter table 表名drop primary key

主键自增策略：

(1)建表时添加主键自增策略
     语法：列名 数据类型 primary key auto_increment
     自动增长字段类型必须是整数类型
(2)修改表方式添加自增策略
     语法：alter table 表名 modify 主键列列名 数据类型 auto_increment
默认auto_increment开始值是1，如果希望修改起始值，可以使用
     语法 alter table 表名 auto_increment=起始值

truncate 截断表

作用：删除表中全部数据，相当于删除表后重新创建新表
语法：truncate table 表名

delete和truncate对于主键自增策略的区别

1.delete 删除表中的数据，但不重置auto_increment的值。 
2.truncate摧毁表，重建表，auto_increment重置为1 

2、非空约束

添加非空约束的字段表示这个字段的每一行的值都不能为null值

创建非空约束
(1)建表时添加非空约束
     语法：字段名 字段类型 not null
(2)修改表方式添加非空约束
     语法：alter table 表名 modify 列名 类型名 not null

3、唯一约束：

添加了唯一约束的列的值不能重复，但是可以为null

创建唯一约束
(1)建表时添加唯一约束
     语法：列名 数据类型 unique
(2)修改表方式添加唯一约束
     语法：alter table 表名add constraint [约束名] unique (字段名)
删除唯一约束：
     语法：alter table 表名 drop index 唯一约束的约束名

4、默认约束：

也叫缺省约束，是指指定某个列的缺省值，当该列在插入数据时不指定值，将会使用缺省值
创建缺省约束

(1)建表时添加缺省约束
     语法：列名 数据类型 default '缺省值'
(2)修改表方式添加缺省约束
     语法：alter table 表名 change column 列名 列名 数据类型 default '缺省值'
删除缺省约束：
     语法：alter table 表名 change column 列名 列名 数据类型 default null

5、外键约束

一张表中的某个字段引用另一个表的主键。
主表： 约束别人的表 
副表/从表： 使用别人的数据，被约束的表

创建外键
(1)建表时添加外键
     语法：[constraint] [外键约束名称] foreign key (外键字段名)  references  主表名(主键字段名)
     关键字解释： 
     constraint：约束关键字 
     foreign key(外键字段名)：某个字段作为外键 
     references  主表名(主键字段名)：表示参照主表中的某个字段
     要求：外键列的数据类型和长度必须和被参照的主键列的数据类型和长度一致

(2)修改表方式添加外键
     语法：alter table 从表名 add constraint [约束名] foreign key(外键列) reference 主表(主表主键列)

外键的级联：

级联操作：
在修改和删除主表的主键时，同时更新或删除表的外键值，称为级联操作
语法
on update cascade -- 级联更新，主键发生更新时，外键也会更新 
on delete cascade  -- 级联删除，主键发生删除时，外键也会删除