[大数据]MYSQL表的增删改查进阶（上）

1. 数据库约束

1.1 约束类型

• not null 指某列不能存储null值。
• unique 保证某列的每行必须有唯一的值。
• default 规定没有给列赋值时的默认值。
• primary key not null 和 unique的结合。确保某列(或两个列多个列的结合)有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• foreign key 保证一个表中的数据匹配另一个表中的值的参照完整性。
• check 保证列中的值符合指定的条件。对于MYSQL数据库，对于check字句进行分析，但是忽略check字句。

注意：
空字符串和null显示的时候是不一样的：

1.2 null约束

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

-- 重新设置学生表结构 
drop table if exists student;
create table student(
id int not null,
sn int ,
name varchar(20),
qq_mail varchar(20)
);

1.3 unique：唯一约束

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

-- 重新设置学生表结构
drop table if exists student;
create table student(
id int not null,
sn int unique ,
name varchar(20),
qq_mail varchar(20)
);

1.4 default：默认值约束

指定插入数据时，name列为空，默认值unknown：

-- 重新设置学生表结构
drop table if exists student;
create table student(
id int not null,
sn int ,
name varchar(20) default 'unknown',
qq_mail varchar(20)
);

1.5 primary key ：主键约束

指定id列为主键：

-- 重新设置学生表结构
drop table if exists student;
create table student(
id int not null primary key,
sn int unique,
name varchar(20) default 'unknown',
qq_mail varchar(20)
);

对于整数类型的主键，常搭配自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1.

-- 主键是 not null和 unique 的结合，可以不用 not null
id int primary key auto_increment,

主键是用来标识数据库中的数据，数据是在不同设备中流转的，一般来说在web开发中，数据是在用户浏览器、Java程序、数据库中，在做修改、删除、查询时就需要知道是哪条数据。

1.6 foreign key ：外键约束

外键用于关联其他表的主键或唯一键，语法：

foreign key (字段名) references 主表(列)

案例：

• 创建班级表classes，id为主键：

-- 创建班级表，有使用MYSQL关键字作为字段时，需要使用``来标识
 drop table if exists classes;
 create table classes (
 id int primary key auto_increment,
 name varchar(20),
 `desc` varchar(100)
);

• 创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id主键，classes_id为外键，关联班级表id

-- 重新设置学生表结构
 drop table if exists student;
 create table student (
 id int primary key auto_increment,
 sn int unique,
 name varchar(20) default 'unknown',
 qq_mail varchar(20),
 classes_id int,
 foreign key (classes_id) references classes(id)
);

1.7 check约束

MySQL使用时不报错，但忽略该约束：

drop table if exists test_user;
create table test_user (
id int,
name varchar(20),
gender varchar(1),
check(gender='男' or gender='女')
);

2. 表的设计

三大范式：

2.1 一对一

2.2 一对多

例子：

2.3 多对多

• 创建课程表

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

• 创建学生课程中间表—考试成绩表

-- 创建课程学生中间表：考试成绩表
 drop table if exists score;
 create table score (
 id int primary key auto_increment,
 score decimal(3,1),
 student_id int,
 course_id int,
 foreign key (student_id) references student(id),
 foreign key (course_id) references course(id)
 );

例子：

以中间表来记录多对多的关系，中间表中：
1.id为1的一个学生，有多个课程，即成绩
2.id为1的课程，有多个学生
注意：

1. 两张表，M：N关联，不直接在两张表产生关联关系。
2. 表的直接关系，只有一对一，和一对多。
3. 多对多的关系，是在中间表产生的间接关系。

3. 新增

插入查询结果
语法：

insert into table_name [(column [, column ...])] select ...

案例：
创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
drop table if exists test_user;
create table test_user (
id int primary key auto_increment,
name varchar (20) comment '姓名',
age int comment '年龄',
email varchar(20) comment '邮箱',
sex varchar (1) comment '性别',
mobile varchar(20) comment '手机号'
);