数据库约束
数据库约束其实是一种强制让数据库内容变得更加完整的方式, 就比如说有的字段里面可以为null, 但是为了其完整性, 可以使用not null 约束他来让他变得更加完整.
约束类型概览
- not null
- unique
- default
- primary key
- foreign key
NOT NULL
唯一约束, 对应字段的记录不能为null
创建表的时候,可以指定某个字段不为空, 例如:
drop table if exists student;
create table student (
id int not null,
sn int,
name varchar(20)
);
对于表student的id字段,我们将其设置为not null, 如果此时插入的记录里面,如果id为null,则会插入失败:
有时候, 一些实体所抽象出来的数据, 必须有一个识别码,也可以成为sn码, 就如同人的身份证一样,用来标识这些实物或者信息, 这些sn码是必不可少的, 所以这些用来标识身份的sn码就可以设置为not null约束(当然也需要避免重复), 来避免sn码为空, 数据也就更有意义和价值了.
UNIQUE
唯一约束, 对应字段的记录不能与其他记录重复.
创建表的时候, 指定sn码为unique约束
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
);被unique修饰的字段, 那么这个字段里面的每一条数据是唯一的,例如:
例如往里面插入:
然后插入具有相同sn码的"lisi":
发生异常:
Duplicate entry '1' for key 'sn': 键“sn”的重复条目“1”
注意: 所有的null 都遵循unique约束原则:
例如,对sn字段被unique修饰后, 连续插入两个null将不会抛出异常.
DEFAULT
默认值约束, 被default修饰的字段, 如果输入为null, 就会使用默认值来代替, 但是代替的类型必须一致
例如, 将name 设置为default约束, 如果输入name 的值没有被指定的时候, 那么就用 "unknown"代替(若name被指定为null, null不会被默认值替代)
PRIMARY KEY
主键约束, 指定字段约束为主键,能唯一的 标识表中的每一行
一个表中只能有一个primary key 主键列,被primary key 修饰的列不能为空,并且主键列的所有值都是唯一的.相当于同时被 not null 和 unique修饰
对于整形类型的主键, 一般搭配auto_increment 来使用, 插入字段的记录不给值的时候,使用表中主键的最大值 +1 .
例如:
create table student(
id int not null primary key,
sn int unique,
name varchar(20) default 'unknown'
);
表中已经有一个记录,如果继续插入:
搭配auto_increment :
插入两个记录,其中被primary key auto_increment 标记的字段传入值为null ,自动从0开始记录:
如果这个时候, 被 primary key auto_increment标记的id 传入一个指定 值的时候,然后再传空值:
结果是主键最大值2,直接跳过被设置为指定的数值,然后后面的空值会继续跟着主键最大值自增.
FOREIGN KEY
外键约束
使用语法:
foreign key 字段 references 主表
添加class表,classId为主键:
create table class(classId int primary key auto_increment, className varchar(20));
添加student表,studentId为主键:
create table student(studentId int primary key auto_increment, name varchar(20), classId int, foreign key( classId) references class( classId));
设置student中的classId字段被foreign key约束, 其约束项为class 表中的classId字段, 也就是说:
student 表中的classId 中的每一个数据, 都必须在class表中的classId 字段的记录中存在.
此时,student表被class表约束, 我们称student为class的子表, 称class 为student 的父表
如果向student里面插入或者修改的记录对应的foreign key 外键约束的字段数据在对应父表中不存在的话, 那么就会插入失败.
相反,如果要删除class表中的数据, 如果对应约束项的记录已经在子表中存在, 那么删除就会失败
更直观的来看个例子:
创建两个表:
商品表(Id, name, price....)
订单表(orderId, userId, goodsId, time.....)
其中商品id被外键约束, 其约束的内容为订单表中的goodsId, 用MySQL的语言就是:
create table orderList (orderId int primary key auto_increment , userId int, goodsId int, foreign key(id) references goodsList(Id));
假如这个商品表需要有一个商品下架功能, 但是, 这里面的id又有外键约束, 如果要在商品表中删除一个商品, 但是他的id 在订单表中还在被使用, 那么此时就无法直接删除, 那么这个功能该如何实现??
解决办法:给商品表加一个列字段, 用来表示该商品是否被下架,通过update 来修改这个字段, 此时外界约束就不会收到影响, 订单可以继续实时查看.
表的设计
如何设计表?
- 了解实际信息体的"实体"
- 明确实体之间的关系
- 按照实体的关系带入到表的体系当中.
无关联
各表之间没有关系, 只需要根据实体的个性 自行设计即可
一对一
例如, 一个学生表中 有许多关于学生的字段, 这些字段可以是姓名, 年龄等, 同时还存在一个账号表, 这两张表有很直接的关联, 那就是一个账号只能供一个学生使用, 同时 一个学生也只能拥有一个账号.
这种一对一的, 就可以直接设计成一张表:
student -- account ( studentId, studentName, studentAccount);
此外,更推荐将其设计成两个表, 然后让他们相互关联, 可以使用studentId进行关联, 也可以使用account进行关联.
一对多
一个班级可以包含多个学生, 但是一个学生只能存在于一个班级, 这就形成了一对多或者多对一的情况
student (studentID, name, age ....)
class (classID, className, studentList(每个记录为一个学生数据 集合 ) )
student表包含学生的基本数据, class 包含班级的数据, 同时也包含着具有的学生列表, 但是学生列表有很多行学生的数据, 不可能在一行里面显示, 但是使用数组的形式存放这些学生的记录的方法在MySQL是不支持的. 但是有些数据库支持以数组的形式存放, 比如redis ,
此外还可以使用外键约束来设计这个一对多的表:
多对多
例如, 一个学生可以上多个课程, 一个课程也可以有多个学生:
studentList (studentID, name );
course ( courseID, courseName );
将他们两个关联起来:
student_courseList (studentID, courseID );
进阶增删改查
增
与前面的直接使用insert into 表名 values (value1, value2, value3, ....)了不同的是, 这里的新增是直接插入查询结果. ;例如:
这里创建两张学生表:
student (id int, name varchar(20))
student2 (id int, name varchar(20))
student表中插入三个数据:
insert into student values (1, "张三"),(2, "李四"),(3, "王八");
如果这个时候, 我想把student表中的数据拷贝到student2 当中去, 使用将values改成select查询语句:
insert into student2 select * from student;
注意 :这并不代表任何两个表之间都可以这样操作, 查询的结果得到的列数, 对应字段的数据类型必须一样( 列名可以不一样, 但是类型必须一致 )
MySQL进阶-增删查改(全网最详细sql教学)-2
