一、约束
1.约束的作用
对表中数据进行进一步限制,保证表中数据的完整性、一致性和正确性。
2.约束的分类
1)主键约束
主键能够唯一地标识表中的一行,比如学生表中的学生id。
它的主要作用就是能够将表中的每一行数据区分开来,这样可以避免在执行update或delete操作时发生错误。
满足以下条件的任意列都可以作为主键:
- 任意两行的主键值不相同
- 每一行都存在主键值(即主键值不能为NULL)
- 包含主键值的列从不进行修改与更新
设置主键有下面几种方式:
- 在表定义时直接指定主键
- 使用ALTER TABLE语句来设置主键约束
--第一种方式
create table student(
stu_id char(9) primary key,
name char(10));
--第二种方式
create table student(
stu_id char(9),
name char(10),
primary key(stu_id)
);
--第三种方式
alter table student add constraint primary key(stu_id);
如果要更新主键,需要先将表中原来的主键drop掉,在重新添加主键约束。
2)外键约束
外键是表中的一列,它的值必须在另一个表的主键中,外键是保证引用完整性的极其重要部分。
举个栗子:
订单表(订单号,订单日期,顾客id)
顾客表(顾客id,顾客name,顾客地址)
订单表中,订单号是唯一的,但是顾客id不一定是唯一的,因为同一个顾客可能会产生多个订单。而在顾客表中,顾客id是唯一的。
订单表中顾客id的合法值应该为顾客表中顾客id的某个值,所以我们定义订单表中的顾客id为外键,它的值要以顾客表的主键(顾客id)为参考。
创建外键约束的方法:
create table Orders(
order_num int not null primary key,
order_date datetime not null,
cust_id char(10) not null references Customers(cust_id)
);
--第二种方法
alter table Orders add constraint foreign key(cust_id) references Customers(cust_id);
最后再说一下什么叫引用完整性。
引用完整性就是定义外关键字与主关键字之间的引用规则。如果要删除被引用的对象,那么也要删除引用它的所有对象,或者把引用值设置为空(如果允许的话)。
订单表中的顾客id为外键,它引用的是顾客表的主键。所以订单表为引用对象,顾客表为被引用对象。设想如果我们要从顾客表中删除某个顾客,而该顾客在订单表中有引用记录,一旦将该顾客删除,那么对应的订单表将失去引用对象,这对系统来说确实是不安全的。
3)唯一约束
唯一性约束用来保证某列中的数值是唯一的。它和主键的区别有下面几点:
- 主键必须非空,而唯一约束可包含NULL
- 每一个表只能有一个主键,却可以有多个唯一约束
- 唯一约束可修改或更新
- 唯一约束不能用来定义外键
创建方式:
- 直接在表定义时使用unique关键字
- 使用单独的constraint定义
4)check约束
check约束用来保证某一列的数值满足某种特定的条件,它的用途主要有以下几种:
- 检查最大最小值,比如订单数量不能为0
- 指定范围:比如发货日期必须大于等于今天的日期并且不超过明年的这个时候
- 只允许特定的值,比如性别列中只允许填写"M" or "F"
创建check约束:
---第一种方法
create table OrderItem(
order_num int not null,
order_item int not null,
quantity int not null check (quantity > 0)
);
---第二种方法
add constraint check (gender in ['M','F']);
不够mysql中check约束貌似没用???
使用enum()和触发器来解决~~~
5)非空和默认值
这也是常用的约束手段,分别使用not null和default来进行约束即可。
二、索引
1.索引的作用
加快搜索速度。
比如说你需要在一本书中寻找一个术语的位置,如果不使用索引,你需要从从第一页一行一行的找,虽然也能找到但是效率太低。如果书中有索引,且索引中有这个术语和所在的位置,我们就能快速找到它的位置了。
2.索引的使用场景
数据库中表的数据量较大的情况下,对于查询响应时间不能满足业务需求,可以合理的使用索引提升查询效率。
3.索引的分类
- 普通索引(单列)
- 复合索引(多列)
- 唯一索引
- 主键索引
- 全文索引
# 建立普通索引
create index <索引名> on <表名>(列名);
# 建立复合索引
create index <索引名> on <表名>(列名组);
# 创建唯一索引
create unique index <索引名> on <表名>(列名);
# 唯一索引
alter table <表名> add primary key(列名);
/* 全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较
目前只有char、varchar,text 列上可以创建全文索引 */
# 删除索引
drop index <索引名> on <表名>
- 索引名必须唯一
- 主键索引是一种特殊的唯一索引,它的值是唯一的且不能存在空值,通常在表定义时直接设置
唯一约束和唯一索引在 MySQL 数据库里区别:
- 概念上不同,约束是为了保证数据的完整性,索引是为了辅助查询
- 创建唯一约束时,会自动的创建唯一索引
- 在理论上,不一样,在实际使用时,基本没有区别
4.索引的优点
- 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
- 加快了搜索的速度
- 建立索引可以加快表与表之间的连接
5.索引的缺点
- 索引的确能改善搜索的速度,但是却降低了更新、删除数据的性能,原因在于每次删除或更新数据就需要对索引进行更新
- 索引其实也是一张表,所以建立索引必然会占据一定的内存空间