mysql学习笔记（四）约束与索引

关系型数据库的设计规则：遵循ER模型和三范式

ER模型

E:Entity实体，指代数据库当中的一张表

R:Relationship,代表关系

ER图：https://jingyan.baidu.com/article/d5a880eba77c3513f147ccdf.html

三范式

1.列不能拆分

2.唯一标识··3.关系引用主键

具体体现：

• 将数据放到表中，表放在库中
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己，表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储
• 表由列组成，我们也称为字段，每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计
• 表的数据按行存储

约束与索引

完整性

数据完整性（Data Integrity）是指数据的精确性（Accuracy） 和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）、用户定义的完整性(User-definedIntegrity)。

实体完整性：同一个表中不能有相同的无法区分的数据

域完整性：数据的域有必要限定

参照完整性：所有的引用参照属性都在相应的表中能够找到

用户定义的完整性：符合用户定义的规则

根据约束的特点，分为以下几种:

PRIMARY KEY主键约束不能重复，不能为空NOT NULL 非空约束不能为空DEFAULT默认约束插入数据时此列没有值插入，则使用默认值UNIQUE KEY唯一约束此列不能有重复值FROEIGN KEY外键约束约束表之间的关系

一、主键约束（primary key constraint） 

• 主键创建时默认会自动生成约束，也可通过显示声明。  alter table 表名 add constraint 约束名 primary key (列名) 
• 一个表有且只有一个主键约束
• 主键约束意味着唯一且非空
• 创建主键会自动创建对应的索引，删除主键也会删除对应的索引

索引的本质是一张特殊的表，当数据量大时，查找索引比起要查找全部内容快了太多了，并且由于索引表数据量小，也非常节省计算机资源。

• 主键约束分为单列主键约束和复合主键约束

单列主键约束为id int key;

复合主键约束为

create table dbtest(

id int auto_increment,

name varchar(10),

primary key(id,name)

);

将多个元素设置为主键。

二、外键约束（foreign key constraint） 

外键创建时默认会自生成约束，也可通过显示声明。 

一个表可以有很多的外键约束

外键约束需要一个表的两个字段或者两个表的两个字段之间建立外键约束

外键约束一定是在从表、子表中建立的。

外键的建立前提是主表必须唯一或者是主键

• alter table 表名 add constraint 约束名 foreign key(列名) references 其他表(其他表的列名); 
• foreign key (列名) references 其他表(其他表的列名);

主表：有被依赖项的表。例如学生表和课程表

从表：外键所在，依赖主表的表。例如选课表，表中有两个外键，分别参考学生表和课程表

约束的五个等级：

1. cascade方式:级联，主动权在主表上，如果主表被依赖字段修改了，从表的外键字段也会跟着修改。
2. set null方式:主动权在主表上，如果主表被依赖字段修改了，从表的外键字段会将值设置为Null,这里要求，外键字段不能有非空约束。
3. no action方式：不作为，同4
4. restrict方式：如果主表被依赖字段的值被从表引用了，那么主表对该字段的修改和删除就被完全限制了，主表没有主动权，必须先处理从表的值。
5. set default方式：主动权在主表上，如果主表被依赖字段修改了，从表的外键字段会将值设置为default,这里要求，外键字段必须有默认约束。

使用方法

• alter table 表名 add constraint 约束名 foreign key(列名) references 其他表(其他表的列名) [on update 等级] [on delete 等级];

例如:alter table uniquetest add constraint fc foreign key(name) references testtab(fname) on delete cascade on update cascade;

如何查看某个表的外键约束名：

select * from information_schema.table_constraints where table_name=表名;

三、检查约束（check constraint） 

• alter table 表名 add constraint 约束名 check (列名 between 1 and 100) 

注：MYSQL不支持

四、唯一约束（unique constraint） 

一个表可以有多个唯一约束，唯一键意味着唯一，可以为NULL，这意味着除了NULL值其他的都必须唯一，而可以有多个NULL值。

• alter table 表名 add constraint 约束名 unique(列名) 

或者在建表时通过unique key(字段列)设置唯一约束

五、默认约束（default constraint） 

一个表可以有很多的默认值约束

默认值约束只能针对某一个字段来说

默认值约束意味着该字段如果没有手动赋值，会按默认值处理

• alter table 表名 add constraint 约束名 default (默认值) for 列名

六、自增约束（auto_increment） 

一个表只能有一个自增约束，因为一个表只能有一个维护自增值的变量

自增约束的列只能是整数列

自增约束的列必须是键列

• alter table 数据库名.表名称 modify 字段名 数据类型 auto_increment;

Identity用法详解https://blog.csdn.net/weixin_34367845/article/details/90087125

identity是sqlServer的语法,mysql不支持identity

Identity是标识值，在SQL Server中，有ID列，ID属性，ID值，ID列的值等术语。

Identity属性是指在创建Table时，为列指定的Identity属性，其语法是：column_name type IDENTITY [ (seed , increment) ]，Identity属性有两个参数：seed和increment，seed是ID值的初始值，increment是ID值的增量。在Table中创建的Column，如果使用Identity属性标识，那么该列是ID列。默认情况下，不能显式向ID列插入数值。ID列是由系统自动赋值的，在赋值时，系统根据该表的ID值，自动插入递增的，唯一的数值，同时ID值根据Increment自动递增。ID值有自动递增的特性，当语句执行失败或事务回滚时，ID值不会回滚，这会导致ID列的值不连续。

如果想要显式向ID列插入特定的数值，那么，必须启用 Identity_Insert选项，该选项自动将ID值更新为ID列的最大值。

七、非空约束（not null）

一个表可以有很多的非空约束

非空约束只能针对某一个字段来说

非空约束意味着该字段不能存入null

alter table 数据库名.表名称 modify 字段名 数据类型 not null;

PS

约束的删除方法

alter table 数据库名.数据表名 drop index 约束名

约束有多种方法添加

添加not null/unique/key的尾缀，

添加foreign key(在上文中定义的列名) references 其他表名(其他表的列名)来实现约束

create table Student(
    Uid int not null identity(1,1) primary key,  --主键约束--
    Name varchar(20) not null unique,  --唯一约束--
    Age int not null check(Age>=18),  --检查约束--
    TeaUid int not null foreign key references Teacher(Uid),  --外键约束--
    Notes varchar(100) null
)

约束本身的的约束信息存放在后台

show databases;可以看见一个叫information_schema的数据库

show tables；可以看见table_constraint的表，其中保存着约束信息。

show index from 数据库名.数据表名；可以全面的查看索引信息。

约束在mysql中也可用Index指代

不用填写约束名，且默认约束名就为列名