开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 【MySQL】MySQL表的增删改查（进阶） -> 正文阅读

[大数据]【MySQL】MySQL表的增删改查（进阶）

1.数据库的约束

1.1 约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标
识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略
CHECK子句

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列为空，默认值unkown：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

在这里插入图片描述

1.5 PRIMARY KEY：主键约束

主键约束也就是：设置的那一列主键值不能重复，也不能为null
指定id列为主键：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

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

-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment ;

在这里插入图片描述

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 'unkown',
   qq_mail VARCHAR(20),
 classes_id int,
 FOREIGN KEY (classes_id) REFERENCES classes(id)
);

讲解：
在这里插入图片描述
如图我们设置了班级表classes，班级ID为自增主键，以及学生表student，学生ID为自增主键；
要实现的逻辑是一个学生只能去一个班，一个班可以有多个学生；
也就在学生表里面的class_id设置外键约束自classes表中的id；
在这里插入图片描述
也就是说插入学生数据时候学生班级ID需要在班级表中存在才可以插入成功，否则不成功

如图插入1-3班的学生数据都可以成功，因为班级表中就有这三个班级，而插入6班时候就因为外键约束而插入失败了；
在这里插入图片描述
没有班级ID为6的同学；

1.7 CHECK约束（了解）

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

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

2.表的设计

三大方式：
在这里插入图片描述

示例1：
设计一个学校食堂管理系统，学校食堂管理系统，包含食堂表，食堂仓口表，仓口收费记录表

-- 一个食堂有多个仓口卖饭，关系为1:m，每个仓口卖饭可以有很多次，仓口与收费记录也是1:m
// m:多
create table hall(

id int primary key,

  name varchar(20)

);

create table hall_opening(

id int primary key,

  name varchar(20),

  hall_id int,

  foreign key (hall_id) references hall(id)

);

create table info(

id int primary key,

  price int,

  info_date timestamp,

  hall_opening_id int,

  foreign key (hall_opening_id) references hall_opening(id)

);

在这里插入图片描述

示例2：
设计一个车辆违章系统，车辆违章系统，包含用户表，车辆表，违章信息表。违章信息表中包含用户和车辆的违章信息

-- 用户可以拥有多辆车，关系为1:m，题目已经说明违章信息包括用户和车辆，说明违章信息表中要记录用户和车辆，一个用户可以有多次违章记录，用户与违章记录关系为1:m，一辆车也可以有多次违章记录，车辆与违章记录关系也为1:m


create table user(

id int primary key,

  name varchar(20)

);

create table cars(

id int primary key,

  name varchar(20),

  user_id int,

  foreign key (user_id) references user(id)

);

create table info(

id int primary key,

  user_id int,

  cars_id int,

  foreign key (user_id) references user(id),

  foreign key (cars_id) references cars(id)

);

在这里插入图片描述

示例3:
设计一个学校宿舍管理系统，学校宿舍管理系统，要求包含宿舍信息，学生信息，每日的宿舍查房记录。

-- 主要考虑学生与宿舍的关系：m:1，宿舍的查房记录是根据宿舍来查的，与宿舍有关系，一个宿舍可以多次查房，宿舍与查房记录是1:m的关系

create table dormitory(

id int primary key,

  number varchar(20)

);



create table student(

id int primary key,

  name varchar(20),

  dormitory_id int,

  foreign key (dormitory_id) references dormitory(id)

);



create table info(

id int primary key,

  dormitory_id int,

  status bit,

  info_date timestamp,

  foreign key (dormitory_id) references dormitory(id)

);

在这里插入图片描述

示例4：
设计一个考勤系统，考勤系统，包含员工表，考勤记录表

-- 主要考虑记录表中的记录信息，是如何关联到员工表，员工与记录关系为1:m。



create table emp(

id int primary key,

  name varchar(20)

);

create table info(

id int primary key,

  emp_id int,

  info_date timestamp,

  foreign key (emp_id) references emp(id)

);

在这里插入图片描述

示例5：
设计课程表，课程包包含学生信息

-- 创建课程表
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)
);