MySQL~数据库约束、表的设计以及进阶版插入

目录

数据库约束

NULL约束

UNIQUE 唯一约束 

DEFAULT 默认约束值 

PRIMARY KEY 主键约束 

FOREIGN KEY 外键约束 

表的设计 

一对一

一对多

多对多

 新增(插入)进阶

---

数据库约束

约束即按照一定条件进行规范的做事，表定义的时候某些字段保存的数据，需要按照一定的约束条件去定义.

NULL约束

· NOT NULL：指示某列不能存储NULL值

创建表时，可以指定某列不为空

-- NULL约束
drop table if exists student;
create table student(
    id int not null,
    sn int,
    name varchar(20),
    sex varchar(2) 
);

上述创建的学生表指定id不能存储null值，通过查看表结构可以具体看出

-- 查看学生表
desc student;

 

给student表插入数据，测试当id插入为null时是否可以正常插入

-- 给学生表插入数据

-- id插入为null
insert into student values(null, 1, 'a', null);
-- id插入不为null，其他为null
insert into student values(142516, null, null, null);

可以看出当指定某列不能存储null值后，再进行插入null会发生报错，未指定的列可以插入null值 

 

UNIQUE 唯一约束 

保证某列的每一行必须有唯一的值.

指定sn列为唯一的、不重复的.

-- UNIQUE约束
drop table if exists student;
create table student(
    id int not null,
    sn int unique,
    sname varchar(20),
    sex varchar(2)
);

unique功能测试，sn已经约束为unique，测试插入相同sn时会发生什么

-- unique功能测试
insert into student values(1, 100, null, null);
insert into student values(2, 100, null, null);

第一次插入时成功插入，第二次插入时因为student表中已经存在sn = 100的数据，因此不能再插入相同的数据，这即为UNIQUE的作用体现. 

 

DEFAULT 默认约束值 

规定没有给列赋值时的默认值

指定插入数据时，sname列为空，默认值为unknown.

-- DEFAULT功能测试
-- 重新设置学生表结构
drop table if exists student;
create table student(
    id int not null,
    sn int unique,
    sname varchar(20) default 'unknown',
    sex varchar(5)
);

通过查看表结构，sname的Default为unknown 

 

 进行插入数据测试，插入两条数据，第一条不对sname插入，第二条对sname插入null，对比查看结果

-- 插入数据测试
insert into student(id, sn) values(1, 100);
-- 在sname列插入null
insert into student values(2, 101, null, '男');

通过对比可以看出，当不对sname进行插入数据时，默认为unknown，当显示的插入数据时，即使是null，默认值也不会生效，被改变为null. 

 

PRIMARY KEY 主键约束 

即为UNIQUE和NOT NULL的结合.确保某列(或两个列多个列的结合)有唯一标识，有助于更容易、更快速地找到表中的一个特定的记录.

设置id列作为主键

-- PRIMARY KEY功能测试
-- 重新设置学生表结构
drop table if exists student;
create table student(
    id int primary key,
    sn int unique,
    sname varchar(20) default 'unknown',
    sex varchar(5)
);

为了方便后续测试，首先对student表先插入两个数据 

· 正常插入（同时满足not null 和 unique）

-- 插入数据测试
insert into student(id, sn) values(1, 100);
insert into student(id, sn) values(2, 101);

 

primary key = not null unique，分别测试主键约束是否具有not null 和unique的功能.

· 测试主键的not null功能

-- 测试主键的not null功能
insert into student(id) values(null);

· 测试主键的unique功能

-- 测试主键的unique功能
insert into student values(1, 102, '张三', '男');

 

FOREIGN KEY 外键约束 

保证一个表中的数据匹配另一个表中的值的参照完整性,外键用来关联其他表的主键或唯一键

· 创建班级表classes，id作为主键 

-- 创建班级表classes，id作为主键
create table classes(
    id int primary key auto_increment,
    cname varchar(20)
);

· 创建学生表，一个学生对应一个班级，一个班级对应多个学生，id作为主键，classes_id作为外键，关联班级表id.

-- 创建学生表，一个学生对应一个班级，一个班级对应多个学生.
-- id作为主键，classes_id作为外键，关联班级表id
drop table if exists student;
create table student(
    id int primary key auto_increment,
    sn int unique,
    sname varchar(20) default 'unknown',
    sex varchar(5),
    classes_id int,
    foreign key (classes_id) references classes(id)
);

表的设计 

一对一

例如人和身份证表

实际上，这两个表可以设计为一个表，但放在一起的话比较繁琐，因此建立两个表并以一对一关系将其关联起来.

设计一对一关系的目的：①解耦：模型之间的解耦、②不经常使用的数据，单独存储，效率会比较高

一对多

例如班级表和学生表

 一对多是有方向的，例如：

①班级到学生：一对多，一个班级包含多个学生；

②学生到班级：一对一，一个学生一个班级

多对多

多对多关系在表设计时：①两张主表建立多对多的关系，这个多对多关系在两个主表中没有外键体现；②使用一张单独的中间表，来表示两张主表之间的多对多关系.

· 创建学生表

-- 创建学生表
drop table if exists student;
create table student(
    id int primary key auto_increment,
    name varchar(20) default 'unknown'
);

· 创建课程表 

-- 创建课程表
drop table if exists course;
create table course(
    id int primary key auto_increment,
    cname varchar(20) not null
);

· 创建中间表（学生课程考试成绩表）

-- 创建中间表（学生课程考试成绩表）
drop table if exists exam_score;
create table exam_score(
    id int primary key auto_increment,
    -- 学生表的主键，作为中间表的外键
    student_id int,
    -- 课程表的主键，作为中间表的外键
    course_id int,
    -- 建立主外键关联
    foreign key(student_id) references student(id),
    foreign key(course_id) references course(id),
);

 

 新增(插入)进阶

· 首先建立学生表

drop table if exists student;
create table student(
    id int primary key,
    sn int unique,
    sname varchar(20) default 'unknown',
    sex varchar(5)
);

· 给学生表中普通插入数据 

insert into student values(1,101,'张三','男');
insert into student values(2,102,'王五','男');
insert into student values(3,103,'李四','男');

目前学生表中存有以下数据

·  建立用户表

-- 建立用户表
drop table if exists user;
create table user(
    id int primary key auto_increment,
    uname varchar(20) comment '姓名',
    age int comment '年龄',
    sex varchar(5) comment '性别',
    mobile varchar(20) comment '电话号'
);

· 将学生表所有数据复制到用户表中

-- 将学生表所有数据复制到用户表中
insert into user(id, uname, sex) select id, sname, sex from student;

成功将学生表中所有数据复制到用户表中 

 

本篇博客所有代码均已更新上传至gitee中，点击链接获取：gitee代码仓库