从0学开发笔记-MySQL约束、多表操作、数据库设计范式、备份还原

最新推荐文章于 2022-04-19 11:24:12 发布

喜欢历史的工科生

最新推荐文章于 2022-04-19 11:24:12 发布

阅读量73

点赞数

文章标签： MySQL约束多表操作数据库设计范式备份还原

原文链接：https://www.bilibili.com/video/BV1Lf4y1U7Cz

版权

约束：

概念：对表中的数据进行限定，保证数据的【正确性】、【有效性】、【完整性】
分类：
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束：foreign key

非空约束

【创建】表添加【非空约束】

CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
)

【删除】name的【非空约束】

ALTER TABLE stu MODIFY name VARCHAR(20)

创建表完后，添加非空约束

 ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束：

注意：
- 唯一约束可以有NULL，但是只能有一条有NULL
创建表时，添加唯一约束

CREATE TABLE stu(
		id INT,
		phone_number VARCHAR(20) UINQUE --手机号
);

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

在表创建完成以后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束

注意：
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录唯一标识
在创建表时，添加主键约束

CREATE TABLE stu(
		id INT PRIMARY KEY , -- KEY后面又空格
		name VARCHAR(20)
);

删除主键

-- 错误 ：ALTER TABLE stu MODIFY id INT;
ALTER TABLE stu DROP PRIMARY KEY;

创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长：
- 概念:如果某一列是【数值】类型的，使用auto_increment 可以来完成值的自动增长
- 给字段值设置为NULL，即可实现自动增长

--在创建表时，添加主键约束，并且完成主键自增长
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,--给id添加主键约束
	name VARCHAR(20)
);

--删除自动增长
ALTER TABLE stu MODIFY id INT;  --删除自动增长，不会将主键给删除

--添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT

外键约束

foregin key，让表与表产生关系，从而保证数据的正确性。

--创建表时，添加外键
CREATE TABLE 表名(
	...
	外键列
	CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
)

--删除外键
ALTER TABLE DROP FOREIGN KEY 外键名称;

-- 创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

级联操作

-- 添加级联操作
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
		FOREIGN KEY(外键名称字段) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE

--分类
--级联更新：ON UPDATE CASCADE  主表的列发生变化，相关联的表的外键也会发生变化
--级联删除：ON DELETE CASCADE  主表的列中某个值被删除，相关联的表的外键对应值也会被删除

数据库的设计

多表之间的关系
- 分类
  - 一对一（了解）
    - 如：人和身份证
    - 分析：一个人只有一个身份证，一个身份证只能对应一个人
  - 一对多（多对一）
    - 如：部门和员工
    - 分析：一个部分有多个员工，一个员工只能对应一个部门
  - 多对多：
    - 如：学生和课程
    - 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
- 实现
  - 一对多：
  - 多对多
  - 一对一
    一般出现一对一的情况放在一张表上就行
案例

--创建旅游路线分类表 tab_category
-- cid 旅游路线分类主键，自动增长
--cname 旅游路线分类名称非空，唯一，字符串 100
CREATE TABLE tab_category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE,
);


CREATE TABLE tab_rout(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)  -- 这里是省略的形式，省略了CONSTRAINT 外键名称
);

CREATE TABLE tab_user(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) UNIQUE NOT NULL,
	password VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT '男',
	telephone VARCHAR(11),
	email VARCHAR(100)
)

数据库的设计范式
- 概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵守前边的所有范式的要求
  - 设计关系数据库时，遵从不同的规范要求，设计处合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
  - 目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF,完美范式)
- 分类
  - 第一范式：每一列都是不可分割的原子数据项
  - 第二范式：在INF的基础上，非码属性必须完全依赖候选码(在1NF基础上消除非主属性对主码的【部分函数依赖】)
    - 几个概念
      - 函数依赖：A–>B,如果通过A的属性(属性组)的值，可以唯一确定B属性的值。则称B依赖于A。例如：学号–>姓名。
      - 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。例如：(学号，课程名称) -->分数
      - 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。例如：(学号，课程名)–>姓名
      - 传递函数依赖：A–>B,B–>C，如果通过A属性（属性组），可以唯一确定B属性的值，再通过B属性(属性组)的值可以确定唯一C属性的值，则成C传递函数依赖于A。例如：学号–>系名，系名–>系主任
      - 码：如果在一张表中，一个【属性或属性组】，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码。例如：该表中的码为一个属性组（学号，课程名称）
        主属性：码属性组中的所有缩影
        非主属性：除过码属性组的属性
  - 第三范式：在2NF上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)