day58-1 数据库--约束

ゝ一纸荒年。 2023-10-09 11:30 15阅读 0赞

*  约束(constraint)
 *  MySQL中的约束
    
     *  unique
     *  not null & null
     *  default
        
         *  普通约束测试
     *  primary key
        
         *  单字段主键
         *  多字段主键
         *  总结：
         *  为主键设置自动增长（auto\_increment）
     *  foreign key
        
         *  foreign key 带来的约束作用：
         *  强调：foreign key就是用来保证两张表之间的关联关系是正确的，而不仅仅是逻辑上的。
     *  级联操作 cascade
        
         *  两种级联操作
     *  外键的使用场景

# 约束(constraint) #

**数据库的约束，是对数据安全性，完整性的保证。**

# MySQL中的约束 #

## unique ##

唯一性约束，表示这个字段不能出现重复的值，用于唯一标识一条记录。

例如身份证号码，学号等

## not null & null ##

非空约束，表示这个字段的值不能为空

例如，账户名，密码等

一些数据类型默认就是null，表示可以为空

## default ##

默认值，用于给某一个字段设置默认值

### 普通约束测试 ###

# 完整的建表语句 create table table_name(字段名称 字段类型[(宽度) 约束]) charset utf8 # 学生表 具备 姓名 性别 学号 create table student( name char(20) not null, gender enum('g','b') default 'b', id int unique ); insert into student values(null,null,null); # 报错，原因是name不能为空 insert into student values('jack',null,null); # 可以插入，null也是一个特殊的值，并且id的唯一约束，也可以为null insert into student(name,id) values('tom',null); # 可以插入 当没有给gender指定参数时，将使用默认值 alter table student modify id int unique not null; # 为已经存在的字段添加约束，报错，因为之前已经添加了空

## primary key ##

主键约束，从约束角度来看就等同于，非空+唯一+索引

### 单字段主键 ###

# 定义方法一：在某一个字段后用primary key create table person( id int primary key, # 主键 name char(10) ); # 定义方法二：在字段定义结束后单独定义primary key create table person( id int, name char(10), primary key(id) ); insert into person values('1', 'tom'); # 正确 insert into person values('1', 'jerry'); # 报错，主键冲突 insert into person values('2', 'jerry'); # 正确 insert into person values(null, 'tom'); # 报错，不能为空 select * from person; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 2 | jerry | +----+-------+ 2 rows in set (0.00 sec) # 从约束角度来看就等同于，非空+唯一 # 在innodb存储引擎中，主键用于组织数据(树形结构)，也就是说主键对于innodb引擎来说是必要的，如果没有手动指定主键，mysql会自动查找一个具备非空且唯一的字段作为主键，如果也没有这样的字段，mysql会创建一个隐藏字段，作为主键。 # 需要明确的是主键是一种索引，unique也是，索引的作用就是加速查询 # 如果我们在查询语句中没有使用索引字段，mysql将无法为我们的查询加速，也就是没有主键，就无法加速查询

### 多字段主键 ###

create table service( ip char(20), port char(5), name char(10), primary key(ip,port) ); insert into service values('1','1','haha'); insert into service values('1','1','lala'); #报错 insert into service values('1','2','lala'); select * from service; +----+------+------+ | ip | port | name | +----+------+------+ | 1 | 1 | haha | | 1 | 2 | lala | +----+------+------+ 2 rows in set (0.00 sec) # 只要不存在两个都重复的就可以

### 总结： ###

**主键 具备约束的作用，还能加快我们的查询速度**，所以我们今后在创建表的时候都应该创建索引字段，那么应该将什么样的字段设置为主键？  
如果本来的业务中就存在非空且唯一的字段，那就把它设为主键，如果没有就自己添加一个字段专门作为主键，**一般我们会将主键设置为int类型，是为了方便保证其唯一**

### 为主键设置自动增长（auto\_increment） ###

当我们创建了主键字段时，插入数据必须保证主键是唯一且不为空的，这就需要我们自己管理主键值，操作比较麻烦，所以mysql有一个自动增长的属性，可以添加在整形字段上，每次插入数据时，都可以自动的插入值，并且每次都会相比上一次加1(不是简单的表内最后一个值+1)，不会冲突。

create table teacher( id int primary key auto_increment, name char(10) ); insert into teacher values(null, 'jack'); # 对于自动增长的字段可以给null，也会自动生成值 insert into teacher(name) values('jack'); # 也可以跳过这个字段，他一样会自动生成值 insert into teacher values(4, 'jack'), (null, 'jack'); select * from teacher; +----+------+ | id | name | +----+------+ | 1 | jack | | 2 | jack | | 4 | jack | | 5 | jack | +----+------+ # 给普通字段添加自动增长，但必须是一个数字类型，且具备key，但是通常与主键一起使用 create table teacher1( id int unique auto_increment, name char(10) );

## foreign key ##

例如我们需要描述一个学校的老师，属性有：id，名字，部门，工作内容，这个学校可能只有几个部门，但是却有很多个老师，那意味着部门这个字段需要重复存储。

解决方案：我们再定义出一张部门表，然后让老师表去关联部门表

这里使用外键约束，用于指向另一个表的主键字段

create table dept( id int primary key auto_increment, name char(20), job char(20) ); # 创建表的时候进行外键约束 create table teacher1( id int primary key auto_increment, name char(20), dept_id int, foreign key(dept_id) references dept(id) ); # 解释： # foreign key(dept_id) references dept(id) # dept_id 表示当前表的外键字段 # dept 表示关联dept表 # dept(id) 表示关联dept表的id字段 # 主表与从表 # 先有主表 再有从表 # 这里是先有dept 再有teacher

### foreign key 带来的约束作用： ###

1.  如果在从表中插入一条记录，试图连接一个主表中不存在的id，会导致插入失败（必须保证部门id(外键的值）必须是主表中存在的）。
2.  创建表的顺序
    
     *  主表-->从表
3.  插入数据的顺序
    
     *  主表记录-->从表记录
4.  删除表的顺序
    
     *  从表-->主表
5.  从表更新外键时，必须保证这个值是主表中存在的
6.  删除主表记录前，要保证从表中没有外键连接了被删除的id，如果有，需要先删除从表的记录，再删除主表
7.  更新主表记录的主键时，要保证从表中没有外键关联被更新的id

### 强调：foreign key就是用来保证两张表之间的关联关系是正确的，而不仅仅是逻辑上的。 ###

#### 练习： 班级表 和 学员表 ####

主表是班级

从表是学员 外键加给学员

create table class( id int primary key auto_increment, name char(10) ); create table student( id int primary key auto_increment, name char(10), class_id int, foreign key(class_id) references class(id) ); insert into class values(null, '7'); insert into student values(null, 'leijun', 1);

## 级联操作 cascade ##

添加外键约束后，当我们需要修改主表信息时，必须先删除从表中关联的数据，这样会很麻烦

而级联操作指的就是，当你操作主表时，自动的操作从表

### 两种级联操作 ###

1.  级联删除：当删除主表时，自动删除从表中的相关数据
2.  级联更新：当主表的主键更新时，自动的更新关联的从表数据

举例：以上面的班级，学生为例

drop table if exists student; drop table if exists class; # 如果表存在，就执行删除，可以避免报错 if exists # if not exists 如果不存在才执行 create table class( id int primary key auto_increment, name char(20)); create table student( id int primary key auto_increment, name char(20), class_id int, foreign key(class_id) references class(id) on delete cascade # 级联删除 on update cascade # 级联更新 ); # 级联操作可以单独使用，也可以一起使用，不需要使用逗号，空格隔开即可 insert into class values(null, 'py9'), (null, 'py10'); insert into student values(null, 'leijun', 1), (null, 'sdf', 2); update class set id=3 where id=2; select * from class; +----+------+ | id | name | +----+------+ | 1 | py9 | | 3 | py10 | +----+------+ 2 rows in set (0.00 sec) select * from student; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 1 | leijun | 1 | | 2 | sdf | 3 | +----+--------+----------+ 2 rows in set (0.00 sec) delete from class where id=3; select * from class; +----+------+ | id | name | +----+------+ | 1 | py9 | +----+------+ 1 row in set (0.00 sec) select * from student; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 1 | leijun | 1 | +----+--------+----------+ 1 row in set (0.00 sec)

## 外键的使用场景 ##

*  表和表之间存在关系

转载于:https://www.cnblogs.com/lucky75/p/11196902.html