MySQL约束、索引

约束

约束的作用：保证数据的一致性，有效性
约束的分类：

• 默认约束：default
• 非空约束：not null
  如：
  在插入记录时性别不输入，默认为 ’ nv ’

sex enum('nan', 'nv') default 'nv',

做一个default约束，在插入时可以不插入性别，如

insert into stuinfo(id, name, score) values(1, 'Tom', 89);

非空约束：不允许该字段的值为 NULL

举个栗子：

create table student(
id int not null,
name varchar(25) not null,
sex enum('nan', 'nv') default 'nan',
course varchar(20) not null default 'python'
);

查看一下表结构：desc student;

mysql> desc student;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id     | int(11)          | NO   |     | NULL    |       |
| name   | varchar(25)      | NO   |     | NULL    |       |
| sex    | enum('nan','nv') | YES  |     | nan     |       |
| course | varchar(20)      | NO   |     | python  |       |
+--------+------------------+------+-----+---------+-------+

插入一条语句

insert into student(id, name) values(1,'Lucy');

查看一下结果：

mysql> select * from student;
+----+-----------+------+--------+
| id | name      | sex  | course |
+----+-----------+------+--------+
|  1 | Lucy      | nan  | python |
+----+-----------+------+--------+

索引

定义：对数据库中表的一列或多列的字段的值进行排序的一种结构（BTree）
使用索引的优点：加快数据的检索（查询）速度
缺点：

1. 当对表中数据更新时，索引需要动态保护，降低了数据维护速度
2. 索引需要占用物理存储空间

普通索引index

普通可设置多个字段，字段的值没有约束
经常给用来查询的字段设置为索引字段
索引标志：MUL

普通索引的创建：
举例如：

create table stuinfo(
id int,
name varchar(25),
score float(5,2),
index(name),
index(score)
);

以上栗子创建了两个普通索引，查看一下：

mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| score | float(5,2)  | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

key一列就是索引的列，我们会发现在name和score有值MUL。
另一种查看方法：

show index from 表名\G;

如果表已经创建了，怎么给字段添加普通索引呢？
格式：

create index 索引名 on 表名(字段名);
如：create index age on stuinfo(age);

删除普通索引：

drop index 索引名 on 表名;

唯一索引unique

可设置多个字段
设置了唯一约束的字段值不允许有重复，但可以为NULL
key标志：UNI

创建唯一索引：

create table stuinfo(
id int,
name varchar(25),
score float(5,2),
unique(id)，
unique(name)
);

只做栗子说明。
和普通索引的创建方式相同。

在已有表中添加唯一索引

create unique index 索引名 on 表名(字段名);

查看、删除同index普通索引。

主键索引primary key && 自增长auto_increment

一张表中只能有一个字段设置主键索引
设置了主键的字段不允许为空，也不能为NULL
key标志：PRI
通常给id设置主键，使其唯一锁定一条记录

创建索引：

create table stuinfo(
id int primary key auto_increment,
name varchar(20),
....
);

另一种写法：
create table stuinfo(
id int auto_increment,
name varchar(20),
....
primary key(id)
);

已有表中创建索引：

alter table 表名 add primary key(id);
alter table 表名 modify id int auto_increment;

设置了主键后最好是设置自增长，否则如果插入数据时不插入id就插不上了。

删除主键：
先删除自增长，后删除主键

alter table 表名 modify id int;
alter table 表名 drop primary key;

外键索引foreign key

让当前表的字段值在另一张表的范围内去选择

语法格式：

foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作;

使用规则：
主表、从表字段数据类型要一致
被参考字段是主键

通过栗子讲解外键
在这之前先了解一下主表、从表、参考字段和被参考字段：
对于主表来说，它也是一个普通的表，只是有一个表2设置了外键，而设置了外键的表2要依附另一张表，那么这张表就是主表，表2就是从表；从表和主表之间还要有一个关联（参考和被参考字段），就是说从表根据什么来依附主表（要求从表和主表的参考和被参考字段是同一个，比如id），就按id来说，主表的id是要被从表的id参考的，所以主表的id是被参考字段，自然从表的id就是参考字段了，另外要求主表的参考字段必须设置为主键。

主表与从表之间有什么不同，为什么要关联？
拿下边的栗子来讲吧
表1：学生缴费表（财务）–>jftab–>主表

id  name  class  money
1   Tom    6     5000
2   Lucy   6     5000
3   Jame   6     5000

表2：学生信息表（班主任）–>bjtab–>从表（不能单独对表操作）

stu_id  name  money

现在我们创建者两张表：
注意主键、外键、主表、从表、参考字段、被参考字段

--表1：jftab
create table jftab(
id int primary key auto_increment,
name varchar(20) not null,
class char(1),
money smallint
)character set utf8;

--表2：bjtab
create table bjtab(
stu_id int,
name varchar(20),
money smallint,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
)character set utf8;

往主表插入两条数据并查看一下：

insert into jftab values(1,'Tom','6',5000),(2,'Lucy','6',5000);
mysql> select * from jftab;
+----+------+-------+-------+
| id | name | class | money |
+----+------+-------+-------+
|  1 | Tom  | 6     |  5000 |
|  2 | Lucy | 6     |  5000 |
+----+------+-------+-------+

在设置了外键后的从表是没有权限自己插入主表外的数据的（注意参考字段）
做个测试，插入id=4的一条数据试试

mysql> insert into bjtab values(4, 'Jame', '5000');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`stuinfo`.`bjtab`, CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

我们发现报错了，只能插入在主表有的被参考字段的值
也就是说我们在从表中只能插入id为1和2的记录，我们试试

mysql> insert into bjtab values(1, 'Jame', 3000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bjtab;
+--------+------+-------+
| stu_id | name | money |
+--------+------+-------+
|      1 | Tom  |  5000 |
+--------+------+-------+

我们现在对主表进行删除和更新操作（测试时把Tom改为了abc了）

mysql> select * from jftab;
+----+------+-------+-------+
| id | name | class | money |
+----+------+-------+-------+
|  1 | abc  | 6     |  5000 |
|  2 | Lucy | 6     |  5000 |
+----+------+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from bjtab;
+--------+------+-------+
| stu_id | name | money |
+--------+------+-------+
|      1 | abc  |  5000 |
+--------+------+-------+
1 row in set (0.00 sec)

mysql> delete from jftab where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from jftab;
+----+------+-------+-------+
| id | name | class | money |
+----+------+-------+-------+
|  2 | Lucy | 6     |  5000 |
+----+------+-------+-------+
1 row in set (0.01 sec)

mysql> select * from bjtab;
Empty set (0.00 sec)

以上就是级联删除，当对主表进行删除时，从表里也有相关记录的话也会跟着删除

在来个更新试试

mysql> update jftab set name='Tom' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from jftab;
+----+------+-------+-------+
| id | name | class | money |
+----+------+-------+-------+
|  2 | Tom  | 6     |  5000 |
+----+------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from bjtab;
+--------+------+-------+
| stu_id | name | money |
+--------+------+-------+
|      2 | Lucy |  5000 |
+--------+------+-------+
1 row in set (0.00 sec)

但是我们发现更改姓名时不能级联，可以试试id

mysql> update jftab set id=8 where name='Lucy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from jftab;
+----+------+-------+-------+
| id | name | class | money |
+----+------+-------+-------+
|  8 | Lucy | 6     |  5000 |
+----+------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from bjtab;
+--------+------+-------+
| stu_id | name | money |
+--------+------+-------+
|      8 | Lucy |  5000 |
+--------+------+-------+
1 row in set (0.00 sec)

这回竟然可以了，也就是说更改只能是被参考字段

删除外键：

alter table 表名 drop foreign key 外键名;
查看外键名：show create table 表名;

已有表中添加外键：

alter table 表名 add
foreign key(stu_id) references jftab(id)
on delete 级联动作
on update 级联动作;

级联动作：
cascade：数据级联删除，级联更新（参考字段）
restrict：默认，不常用
set null：主表删除、更新，从表相关联记录字段值为NULL

The end！