【MySQL表的增删改查】（进阶一）

📚前言

  SQL语言有40多年的历史，从它被应用至今几乎无处不在。我们消费的每一笔支付记录，收集的每一条用户信息，，发出去的每一条消息，都会使用数据库或与其相关的产品来储存，而操纵数据库的语言正是SQL！！！

🎍数据库约束

约束可以理解成，数据库提供的一种针对数据的合法性，验证的机制。
非法操作（不合理的操作）

☘️约束类型

• NOT NULL - 指示某列不能存储 NULL 值。

设定 NOT NULL,说明列里面不能存储空值，表里的这个内容就是必填项

• UNIQUE - 保证某列的每行必须有唯一的值。

设定这一列的所有行的数据都带是唯一的（不能重复）
每次插入或修改，都要先触发查询。如果当前插入或修改的值已经存在，就会插入或修改失败。

• DEFAULT - 规定没有给列赋值时的默认值。

指定默认值 不进行任何指定，默认值就是 NULL
在指定列插入时，会用到

• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键：一条记录的身份标识
类似于 学号 或者 身份证号 相当于 （NOT NULL + UNIQUE）

• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键：涉及到两个表之间的关系
例如：
班级表 学生表
要求学生表中的数据，必须要在班级表中存在

• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

check 指定条件，插入或者修改数据，数据复核条件才能插入或者修改成功
check 指定条件，插入或者修改数据，数据复核条件才能插入或者修改成功不复合条件，直接失败

🎊NULL约束

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

-- 重新设置学生表结构

DROP TABLE IF EXISTS student;

-- IF EXISTS语句用于判断一个条件是否为真
-- 如果条件为真，则执行语句块中的操作，否则跳过该语句块。

CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

📃UNIQUE：唯一约束

指定sn列为唯一的、不重复的：
加上 unique 约束之后
每次 插入 / 修改 都会需要先进行查询，如果发现重复（已经存在）就会插入 / 修改失败

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

🎈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)
);

⛳PRIMARY KEY：主键约束（重点）

指定id列为主键：
1.不能为空
2.不能重复

-- 重新设置学生表结构
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

插入三条数据

插入一条100的数据

在插入一条数据，结果是 5？ 还是 101？

 insert into student values(null,'mingzhi');
 select * from student;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | mingzhi |
|   2 | mingzhi |
|   3 | mingzhi |
| 100 | mingzhi |
| 101 | mingzhi |
+-----+---------+

如果把100 和 101 的数据给删除掉，在插入一条数据，结果会是多少呢？

📗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)!
);

数据库的子表和父表最简单区别方法:
1.有外键是子表
2.有主键那边叫做父表
3.外键去关联主键，一个主键可能对应多个表中的多个外键

🍡CHECK约束（了解）

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

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

🍎 表的关系

三大范式

🌱 一对一

🏐一对多

🍢多对多

• 创建课程表

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

🏸新增

插入查询结果
语法：

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 '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;