第一章表的约束

1.1 概念

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

1.2 分类

主键约束：primary key。
非空约束：not null。
唯一约束：unique。
外键约束：foreign key。

1.3 非空约束

1.3.1 概述

限定字段的值不能为null。

1.3.2 在创建表的时候添加非空约束

示例：

CREATE TABLE stu (

    id INT PRIMARY KEY auto_increment,

    `name` VARCHAR ( 20 ) NOT NULL -- 非空约束

);

1.3.3 创建表之后，添加非空约束

示例：

-- 创建表

CREATE TABLE stu (

`id` INT PRIMARY KEY auto_increment,

`name` VARCHAR ( 20 ) );

-- 创建表之后，添加非空约束

alter table stu modify `name` varchar(20) not null;

1.3.4 删除非空约束

示例：

alter table stu modify `name` varchar(20) ;

1.4 唯一约束

1.4.1 概述

限定字段的值唯一。

1.4.2 创建表的时候，添加唯一约束

示例：

-- 创建表时添加唯一约束

CREATE TABLE stu (

`id` INT PRIMARY KEY auto_increment,

`name` VARCHAR ( 20 ) ,

`phone` VARCHAR(20) UNIQUE -- 添加了唯一约束

) ;

注意：在MySQL中唯一约束限定的字段的值可以有多个null。

1.4.3 创建表之后，添加唯一约束

示例：

-- 创建表后，添加唯一约束

CREATE TABLE stu (

`id` INT PRIMARY KEY auto_increment,

`name` VARCHAR ( 20 ), `phone` VARCHAR ( 20 )

);

alter table stu modify `phone` VARCHAR(20) UNIQUE;

1.4.4 删除唯一约束

示例：

alter table stu drop index `phone`;

1.5 主键约束

1.5.1 概述

主键约束=非空+唯一。
一个表只能有一个主键。
主键就是表中记录的唯一标识。

1.5.2 创建表的时候，添加主键约束

示例：

CREATE TABLE stu (

`id` INT PRIMARY KEY  , -- 添加主键约束

`name` VARCHAR ( 20 ) );

1.5.3 删除主键约束

示例：

alter table stu drop primary key; -- 因为一张表只能由一个主键

1.5.4 创建表之后，添加主键约束

示例：

CREATE TABLE stu (

`id` INT  ,

`name` VARCHAR ( 20 ) );

alter table stu modify id INT PRIMARY KEY;

1.6 外键约束

1.6.1 创建表的时候，添加外键约束

语法：

create table 表名(

   字段1,

   字段2,

   ……

   字段n,

   外键字段,

   constraint 外键名称 foreign key (外键字段) references 主表名称(主键)

);

示例：

CREATE TABLE `dept`  (

  `id` int PRIMARY KEY AUTO_INCREMENT,

  `name` varchar(20)

); 

CREATE TABLE `employee`  (

  `id` int PRIMARY KEY  AUTO_INCREMENT,

  `name` varchar(20) ,

  `age` int ,

  `dept_id` int ,

  CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)

);

1.6.2 创建表之后，添加外键约束

语法：

alter table 表名 add CONSTRAINT 外键名  FOREIGN KEY (`外键字段`) REFERENCES `主表名称` (`主键`) ;

示例：

alter table employee add CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ;

1.6.3 删除外键

语法：

alter table 表名 drop foreign key 外键名;

示例：

alter table employee drop foreign key fk_dept_id;

1.6.4 外键约束的级联操作

如下场景：如果需要将开发部的主键更改，怎么办（类似实际中部门需要重组，但是不开除员工，而是将原来的部门解散，将员工组成新的部门）？

CREATE TABLE `dept`  (

  `id` int PRIMARY KEY AUTO_INCREMENT,

  `name` varchar(20)

); 

CREATE TABLE `employee`  (

  `id` int PRIMARY KEY  AUTO_INCREMENT,

  `name` varchar(20) ,

  `age` int ,

  `dept_id` int ,

  CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)

);

INSERT INTO `dept` VALUES (1, '开发部');

INSERT INTO `dept` VALUES (2, '测试部');

INSERT INTO `employee` VALUES (1, '张三', 20, 1);

INSERT INTO `employee` VALUES (2, '李四', 25, 1);

INSERT INTO `employee` VALUES (3, '王五', 38, 2);

INSERT INTO `employee` VALUES (4, '赵六', 30, 1);

MySQL简版（二）

思路1：
将开发部下面的雇员的外键字段设置为null，然后更新开发部的主键id，将原来开发部下面的员工的外键字段设置为新的开发部的主键id。

update employee set dept_id = null where dept_id = (SELECT id from dept where `name` = '开发部');

update dept set id = 6 where `name` = '开发部';

update employee set dept_id = 6 where dept_id is  null;

思路2：
在创建外键的时候设置级联更新。

alter table employee add CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON UPDATE CASCADE;

在实际的场景中，会有这样的情况，一旦部门变动，要么将雇员打散更改到新的部门（级联更新）要么直接给赔偿金N+1让其辞职（级联删除）。

alter table employee add CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON UPDATE CASCADE on DELETE CASCADE;

上面的情景中，有些公司还会出现干出这样的事情，让员工自动离职（部门解散了，既不劝退你，也不让你去新部门，让你发配边疆）。

alter table employee add CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON UPDATE CASCADE on DELETE set null;

第二章多表操作

2.1 表和表之间的关系

2.1.1 一对一的关系

如：人和身份证的关系。
分析：一个人只有一个身份证，而一个身份证只能对应一个人。

2.1.2 一对多的关系

如：部门和员工的关系。
分析：一个部门有多个员工，而一个员工只能属于一个部门。

2.1.3 多对多的关系

如：学生和课程的关系。
分析：一个学生可以选择多个课程，而一门课程可以被多个学生选择。

2.2 多表关系的实现

2.2.1 一对多的关系

在多的一方添加外键，关联到一的一方的主键。

比如：部门和雇员

MySQL简版（二）

2.2.2 多对多的关系

建立第三方表，添加2个联合主键分别指向多对多关系表的主键。

比如：学生和课程

MySQL简版（二）

2.2.3 一对一的关系

思路1：参照一对多的关系，只不多的是在多的一方的外键上设置唯一约束。
思路2：直接拿其中的一方的主键作为外键指向另一方的主键。

第三章数据库设计范式

3.1 概念

设计数据库的时候，遵从不同的规范要求，设计合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。

3.2 第一范式（1NF）

每一列都是不可分割的原子数据项。

在实际情况下，可能有如下的表：

MySQL简版（二）

但是此表违背了第一范式，每一列都是不可分割的原子数据项，所以需要将系拆分为两列，如下图所示：

MySQL简版（二）

虽然，此表符合了第一范式，但是依然存在如下的问题：
①存在非常严重的数据冗余（重复）：姓名、系名、系主任。
②数据添加存在问题：比如添加新开设的系和系主任的时候（此时还没有学生），数据不合法。
③数据删除存在问题：比如将张无忌同学毕业了，删除数据，会将系的数据一起删除。

3.2 第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）。

函数依赖：A-->B，如果通过A属性（属性组的）值，可以确定唯一B属性的值。则称B依赖于A。例如：学号-->姓名，即学号被姓名所依赖。(学号，课程名称)-->分数，即学号和课程名称属性组被分数所依赖。
完全函数依赖：A-->B，如果通过A属性组的值，可以确定唯一B属性的值。例如：（学号，课程名称）-->分数，即

3.3 第二范式（2NF）

3.4 第三范式（3NF）

第四章数据库的备份和还原

4.1 命令行方式

备份：

mysqldump -u用户名 -p密码 数据库 > 保存的路径

还原：

1.登录数据库

2.创建数据库

3.使用数据库

4.source sql文件保存的路径

4.2 图形化界面

略。

第五章多表查询

5.1 概述

多表查询可能会产生笛卡尔积，所以在实际开发过程中，我们需要通过各种手段去消除笛卡尔积。

5.2 准备工作

sql：

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for dept

-- ----------------------------

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept`  (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of dept

-- ----------------------------

INSERT INTO `dept` VALUES (1, '测试部');

INSERT INTO `dept` VALUES (2, '开发部');

INSERT INTO `dept` VALUES (3, '产品部');

-- ----------------------------

-- Table structure for employee

-- ----------------------------

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee`  (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',

  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',

  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门id',

  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',

  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',

  `birthday` date NULL DEFAULT NULL COMMENT '出生日期',

  PRIMARY KEY (`id`) USING BTREE,

  INDEX `fk_dept_id`(`dept_id`) USING BTREE,

  CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employee

-- ----------------------------

INSERT INTO `employee` VALUES (1, '孙悟空', 20, 1, '男', 7200.00, '2019-08-21');

INSERT INTO `employee` VALUES (2, '猪八戒', 30, 2, '男', 3600.00, '2019-08-01');

INSERT INTO `employee` VALUES (3, '唐僧', 15, 2, '男', 13600.00, '2019-08-02');

INSERT INTO `employee` VALUES (4, '白骨精', 14, 3, '女', 5000.00, '2019-08-03');

INSERT INTO `employee` VALUES (5, '蜘蛛精', 12, 1, '女', 4500.00, '2019-08-05');

SET FOREIGN_KEY_CHECKS = 1;

5.3 内连接

隐式内连接语法：

SELECT

    字段列表

FROM

    表名1,

    表名2

WHERE

    ...

示例：查询所有员工信息和对应的部门信息

SELECT

    employee.id AS employeeId,

    employee.`name` AS employeeName,

    employee.age AS employeeAge,

    employee.birthday AS employeeBirthday,

    employee.gender AS employeeGender,

    employee.salary AS employeeSalary,

    dept.`name` AS deptName

FROM

    employee,

    dept

WHERE

    employee.dept_id = dept.id;

隐式内连接：

SELECT

    字段列表

FROM

    表名1

INNER JOIN

    表名2

ON ...

示例：查询所有员工信息和对应的部门信息

SELECT

    employee.id AS employeeId,

    employee.`name` AS employeeName,

    employee.age AS employeeAge,

    employee.birthday AS employeeBirthday,

    employee.gender AS employeeGender,

    employee.salary AS employeeSalary,

    dept.`name` AS deptName

FROM

    employee INNER JOIN dept ON employee.dept_id = dept.id;

5.4 外连接

左外连接：

SELECT

    字段列表

FROM

    表名1

LEFT OUTER JOIN

    表名2

ON ...

右外连接：

SELECT

    字段列表

FROM

    表名1

RIGHT OUTER JOIN

    表名2

ON ...

示例：

SELECT

    employee.id AS employeeId,

    employee.`name` AS employeeName,

    employee.age AS employeeAge,

    employee.birthday AS employeeBirthday,

    employee.gender AS employeeGender,

    employee.salary AS employeeSalary,

    dept.`name` AS deptName

FROM

    employee

    LEFT OUTER JOIN dept ON employee.dept_id = dept.id;

第六章子查询

6.1 概述

查询中嵌套查询，称嵌套的查询为子查询。

6.2 子查询的不同情况

6.2.1 子查询的结果是单行单列的

子查询可以作为条件，使用运算符进行判断。

示例：

-- 查询员工工资小于平均工资的员工

SELECT

    *

FROM

    employee

WHERE

    salary < ( SELECT AVG( salary ) FROM employee );

6.2.2 子查询的结果是多行单列的

子查询可以作为条件，使用IN关键字。

示例：

-- 查询开发部和测试部的所有员工信息

SELECT

    e.*

FROM

    employee e

WHERE

    e.dept_id IN ( SELECT id FROM dept WHERE `name` IN ( '开发部', '测试部' ) );

6.2.3 子查询的结果是多行多列的

子查询作为虚拟表。

示例：

-- 查询员工的生日在2019-08-02之后的员工信息和部门信息

SELECT

    d.*,temp.*

from dept d INNER JOIN (SELECT * from employee e where e.birthday >= '2019-08-02') temp

on d.id = temp.dept_id

第七章事务

7.1 事务的基本介绍

7.1.1 概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

7.1.2 操作

开启事务：

start transaction;

回滚：

rollback;

提交：

commit;

7.1.3 MySQL数据库中事务的提交方式

自动提交：
- MySQL就是自动提交的。
- 一个DML（增、删、改）语句会自动提交一次事务。　　
手动提交：
- 需要先开启事务，再提交。

7.1.4 修改MySQL的事务的默认提交方式

查看事务的默认提交方式：

SELECT @@autocommit;  -- 1表示自动提交 0 代表手动提交

修改默认的提交方式：

SET @@autocommit = 0;-- 1表示自动提交 0 代表手动提交

7.2 事务的四大特征

7.2.1 原子性

是不可分割的最小操作单位，要么同时成功，要么同时失败。

7.2.2 持久性

当事务提交或回滚后，数据库会持久化保存数据。

7.2.3 隔离性

多个事务之间。相互独立。

7.2.4 一致性

事务操作前后，数据总量不变。

7.3 事务的隔离级别

7.3.1 概念

多个事务之间是互相隔离、互相独立的。
如果多个事务操作同一批数据，则会引起一些问题，设置不同的隔离级别就可以解决这些问题。

7.3.2 存在问题

脏读：一个事务，读取到另一个事务没有提交的数据。
不可重复读：在同一事务中，两次读取到的数据不一样。
幻读：一个事务操作（DML）数据表中的所有记录，另一个事务添加了数据，则第一个事务查询不到自己的修改。

7.3.3 隔离级别

READ UNCOMMITED：读未提交。产生的问题：脏读、不可重复读、幻读。
READ COMMITED：读已提交。产生的问题：不可重复读、幻读。Oracle默认。
REPEATABLE READ：可重复读。产生的问题：幻读。MySQL默认。
SERIALIZABLE：串行化。可以解决所有的问题。

第八章用户管理和权限管理

8.1 用户管理

8.1.1 查询用户

语法：

-- 切换mysql数据库

USE mysql;

-- 查询user表

SELECT

    *

FROM

    `user`;

8.1.2 创建用户

语法：

CREATE USER '用户名' @'主机名' IDENTIFIED BY '密码';

示例：

CREATE USER 'xuweiwei' @'localhost' IDENTIFIED BY '';

CREATE USER 'xuweiwei' @'%' IDENTIFIED BY ''; -- %表示任意主机

8.1.3 修改密码

语法：

-- 5.7 之前的版本

UPDATE USER SET PASSWORD = PASSWORD ( '密码' ) WHERE USER = '用户名';

-- 5.7 之后的版本

UPDATE USER SET authentication_string = PASSWORD ( '密码' ) WHERE USER = '用户名';

示例：

UPDATE USER SET authentication_string = PASSWORD ( '' ) WHERE USER = 'xuweiwei';

8.1.4 删除用户

语法：

DROP USER '用户名'@'主机地址';

示例：

DROP USER 'xuweiwei'@'localhost';

8.2 权限管理

8.2.1 查询权限

语法：

SHOW GRANTS FOR '用户名' @'主机名';

示例：

SHOW GRANTS FOR 'root'@'localhost';

8.2.2 授予权限

语法：

GRANT 权限列表 ON 数据库.表名 TO '用户名' @'主机名' IDENTIFIED BY '密码';

示例：

-- 授予权限

GRANT ALL PRIVILEGES ON *.* TO 'root' @'localhost' IDENTIFIED BY '';

-- 刷新权限

FLUSH ALL PRIVILEGES;

8.2.3 撤销权限

语法：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名' @'主机名';

示例：

REVOKE ALL PRIVILEGES ON *.* FROM 'xuweiwei' @'localhost';

MySQL简版（二）

第一章 表的约束

1.1 概念

1.2 分类

1.3 非空约束

1.3.1 概述

1.3.2 在创建表的时候添加非空约束

1.3.3 创建表之后，添加非空约束

1.3.4 删除非空约束

1.4 唯一约束

1.4.1 概述

1.4.2 创建表的时候，添加唯一约束

1.4.3 创建表之后，添加唯一约束

1.4.4 删除唯一约束

1.5 主键约束

1.5.1 概述

1.5.2 创建表的时候，添加主键约束

1.5.3 删除主键约束

1.5.4 创建表之后，添加主键约束

1.6 外键约束

1.6.1 创建表的时候，添加外键约束

1.6.2 创建表之后，添加外键约束

1.6.3 删除外键

1.6.4 外键约束的级联操作

第二章 多表操作

2.1 表和表之间的关系

2.1.1 一对一的关系

2.1.2 一对多的关系

2.1.3 多对多的关系

2.2 多表关系的实现

2.2.1 一对多的关系

2.2.2 多对多的关系

2.2.3 一对一的关系

第三章 数据库设计范式

3.1 概念

3.2 第一范式（1NF）

3.2 第二范式（2NF）

3.3 第二范式（2NF）

3.4 第三范式（3NF）

第四章 数据库的备份和还原

4.1 命令行方式

4.2 图形化界面

第五章 多表查询

5.1 概述

5.2 准备工作

5.3 内连接

5.4 外连接

第六章 子查询

6.1 概述

6.2 子查询的不同情况

6.2.1 子查询的结果是单行单列的

6.2.2 子查询的结果是多行单列的

6.2.3 子查询的结果是多行多列的

第七章 事务

7.1 事务的基本介绍

7.1.1 概念

7.1.2 操作

7.1.3 MySQL数据库中事务的提交方式

7.1.4 修改MySQL的事务的默认提交方式

7.2 事务的四大特征

7.2.1 原子性

7.2.2 持久性

7.2.3 隔离性

7.2.4 一致性

7.3 事务的隔离级别

7.3.1 概念

7.3.2 存在问题

7.3.3 隔离级别

第八章 用户管理和权限管理

8.1 用户管理

8.1.1 查询用户

8.1.2 创建用户

8.1.3 修改密码

8.1.4 删除用户

8.2 权限管理

8.2.1 查询权限

8.2.2 授予权限

8.2.3 撤销权限

相关文章

第一章表的约束

第二章多表操作

第三章数据库设计范式

第四章数据库的备份和还原

第五章多表查询

第六章子查询

第七章事务

第八章用户管理和权限管理