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!

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

秒不可闫M先生

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值