MySQL超详细学习教程，2023年硬核学习路线_mysql教程

前言

这是一篇 MySQL 通关一篇过硬核经验学习路线，包括数据库相关知识，SQL语句的使用，数据库约束，设计等。整理的初衷是为了记录自己的成长，同时帮助到需要学习和查看的朋友。

1. 数据库的相关概念

1.1 数据

数据是指数据库中存储的基本对象，是描述事物的符号记录。

1.2 数据库

数据库是指存放数据的仓库，长期存放在计算机内的有组织可共享的数据集合。

数据库技术解决了数据的持久化存储问题，同时利用数据库管理系统解决了对大量数据操作繁琐的问题。

1.3 数据库管理系统

数据库管理系统是指位于用户和操作系统之间的一层数据管理软件，科学的组织和存储，高效的获取和维护。

我们所说的 MySQL 数据库就是指 MySQL 数据库管理系统，在大家日常的使用习惯中把他叫做 MySQL 数据库。在我们的电脑安装好数据库管理系统软件以后就可以创建数据库来管理数据，同时也可以对数据库中的数据进行增删改查的操作。

1.4 数据库系统

数据库，数据库管理系统，应用程序和数据库管理员共同组成了数据库系统。

1.5 SQL

SQL的英文是 Structured Query Language，简称 SQL，是一种操作关系型数据库的结构化查询语言，我们在操作数据库时经常用到的操作就是查询操作。

SQL定义了操作所有关系型数据库的统一标准，可以使用 SQL 操作所有的关系型数据库管理系统，在使用其他的数据库管理系统时，也同样可以使用 SQL 来操作。

2. MySQL数据库

2.1 MySQL安装

MySQL有很多的安装方式，这里可以使用绿色版，避免了安装版的一些繁琐的操作，直接在官网下载与自己电脑相对应的版本的压缩文件，然后将压缩文件解压到一个非中文的目录中。

这里选择下载的产品版本是 5.7.24 ，原因是各个库支持的最完善且相对于最新版本更加的稳定。

2.2 MySQL配置

2.2.1 添加环境变量

右键此电脑 / 属性 / 高级系统设置 / 环境变量 ，在系统变量中新建变量，命名为 MYSQL_HOME，变量值为刚才 MySQL 的存放路径。

双击系统变量中的 Path，值为 %MySQL_HOME%\bin 。添加环境变量的方式与前面学习配置 Java 环境变量大致相同。

那么，我们为什么要先配置环境变量呢？

平时，我们在命令行窗口中输入一个可执行程序的命令时，Windows 会先在环境变量中的Path所指的路径中寻找，如果找到就直接执行，没找到就在当前工作目录中寻找，如果还没找到，就会报错。

我们添加环境变量的目的就是能够在任意路径下运行配置了环境变量的程序，而不用总是修改工作目录，大大简化了操作。

我们如何验证添加环境变量成功呢？

此时，我们只需要以管理员身份运行命令提示符工具，执行 mysql，如果提示 Can't connect to MySQL server on 'localhost'，则环境变量添加成功。

注：此时必须以管理员身份运行命令提示符工具，否则会报错。

2.2.2 新建配置文件

在MySQl的根目录中创建一个配置文件 my.ini ,其内容为：

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1
2
3
4
5
6
7

主要目的是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。

2.2.3 初始化MySQL

在命令提示符窗口中运行mysqld --initialize-insecure，如果没有出现报错，则证明data目录初始化成功。

mysqld --initialize-insecure
1

此时当我们再打开查看MySQL目录，已经有data目录生成。

2.2.4 注册MySQL服务

在命令提示符窗口中运行 mysqld -install，此时你的电脑就成功注册了MySQL服务，此时你的电脑就可以称为MySQL服务器。

mysqld -install
1

2.2.5 启动MySQL服务

在命令提示符窗口中运行net start mysql，此时，我们已经成功启动MySQL服务。

net start mysql
1

运行net stop mysql即可停止MySQL服务。

net stop mysql
1

可以通过运行mysqladmin -u root password 1234修改默认账户密码，这里的1234指默认管理员(即root账户)的密码。

mysqladmin -u root password 1234
1

2.3 MySQL登录和退出

在命令行中运行mysql -uroot -p,按照提示输入密码 ，即登录成功。

mysql -uroot -p密码
1

登录命令中的参数：

mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
1

退出MySQL时：

exit
quit
1
2

2.4 MySQL卸载

我们只需要简单的三步就可以完成MySQL的卸载：

第一步：运行net stop mysql

net stop mysql
1

第二步：运行mysqld -remove mysql

mysqld -remove mysql
1

第三步：删除MySQL目录及相关的环境变量。

2.5 MySQL数据模型

MySQl 是一个关系型数据库。关系型数据库是建立在关系模型基础上的数据库，简单来说，关系型数据库是由多张能互相连接的二维表组成的数据库。

与关系型数据库对应的是非关系型数据库，关系型数据库有很多的优点，例如，都是使用表结构，格式一致，易于维护。使用通用的 SQL 语言操作，方便快捷，可用于复杂查询等。

我们通过客户端可以用数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹，而一个数据库下可以创建多张表，文件夹中的 .frm 就是表文件，.MYD是数据文件，通过这两个文件就可以查询到数据展示成二维表的效果。

3. SQL语句

3.1 SQL简介

SQL被称为结构化查询语言，可以用于对所有的关系型数据库进行操作，即我们可以通过SQL语句对数据库、表、数据进行增删改查操作。

SQL定义了操作关系型数据库的统一标准，但是，对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方。

3.2 通用语法

MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

SQL 语句可以单行或多行书写，以分号结尾。

注释：

1. 单行注释: – 注释内容 或 #注释内容(MySQL 特有)

2. 多行注释: /* 注释 */

3.3 SQL分类

DDL： 数据定义语言，用来定义数据库对象：数据库，表，列等

DML： 数据操作语言，用来对数据库中表的数据进行增删改

DQL：数据查询语言，用来查询数据库中表的记录(数据)

DCL：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

数据是数据库中非常重要的部分，所以在日后的操作中我们最常进行的是对数据的一些操作，即对数据进行怎删改查，所以最常用操作的是 DML 和 DQL 。

总的来说DML用于对数据增删改，DQL用于对数据查询操作，DDL用于操作数据库，而DCL是用来控制权限。 SQL 的基本操作一般是指对数据库，数据表，数据的增删改查。

4. DDL- 操作数据库

首先要学习的是使用DDL来对数据库进行操作，主要是对数据库的增删改查操作。

4.1 查询

查询所有的数据库：

show databases;
1

例如：

4.2 创建数据库

创建新的数据库：

create database 数据库名称;
1

使用上面的方式创建新的数据库时，如果该数据库已经存在，则会出现错误，所以我们在创建新的数据库时一般会判断该数据库是否存在，如果已存在，则不会创建。

创建新的数据库(判断数据库是否已经存在)：

create database if not exists 数据库名称;
1

例如：

4.3 删除数据库

删除数据库：

drop database 数据库名称;
1

和前面创建新的数据库相同，为了避免出现错误，我们一般会先判断该数据库是否存在，如果不存在，则不会删除。

删除数据库(判断数据库是否已经存在)：

drop database if exists 数据库名称;
1

例如：

4.4 使用数据库

现在我们已经成功创建了新的数据库，接下来，我们要在数据库中创建数据表，首先我们要明白是对哪一个数据库进行操作，此时我们要先学会使用数据库，才能对数据库中的表进行操作。

使用数据库：

use 数据库名称;
1

查询当前正在使用的数据库：

select database();
1

例如：

5. DDL- 操作数据表

同样，对数据表的操作无非就是增删改查，在学习相关操作之前，我们先要熟悉一下MySQL的数据类型。

5.1 数据类型

MySQL支持多种数据类型，但是大致可以分为3中，分别是数值型，日期型和字符串型。下面是比较常用的几种数据类型：

数值：

日期：

字符串：

定长字符串和变长字符串的区别：字符串是我们在数据库中经常使用的数据类型，使用变长字符串，如果字符的长度没有达到指定的长度，那么实际的长度是多少就占用几个字符，这样的做法显然是使用时间换空间，而使用定长字符串虽然有时会出现浪费空间的情况，但是一般储存性能比较高。

5.2 查询表

查询当前数据库下所有的表：

show tables;
1

查询表结构：

desc 表名称;
1

例如：

5.3 创建表

创建一个新的表：

create table 表名称(
		字段名1 数据类型,
		字段名2 数据类型,
		...
		字段名n 数据类型  #这里是不需要加上,的
);
1
2
3
4
5
6

例如：

5.4 删除表

删除表：

drop table 表名;
1

删除表(判断表是否存在)：

drop table if exists 表名;
1

例如：

5.5 修改表

修改表名：

alter table 表名 rename to 新的表名;
1

增加一列：

alter table 表名 add 列名 数据类型;
1

修改数据类型：

alter table 表名 modify 列名 新数据类型;
1

修改列名和数据类型：

alter table 表名 change 列名 新列名 新数据类型;
1

删除列：

alter table 表名 drop 列名;
1

例如：

6. 实战案例详解

需求：设计包含如下信息的学生表，请注重数据类型、长度的合理性。

1. 编号
2. 姓名，姓名最长不超过10个汉字
3. 性别，因为取值只有两种可能，因此最多一个汉字
4. 生日，取值为年月日
5. 成绩，小数点后保留两位
6. 地址，最大长度不超过 64
7. 学生状态（用数字表示，正常、休学、毕业…）

在完成这样一个案例前，首先要创建一个学生数据库，在数据库中创建一张新的表，创建表时注意语法格式，数据类型和长度的合理性。

以管理员身份运行命令提示符cmd，启动Mysql服务，登录MySQL：

创建学生信息数据库：

create database if not exists student;
1

使用student数据库：

use student;
1

创建数据表：

create table stu(
		id int ,-- 编号
		name varchar(10),-- 姓名
		gender char(1),-- 性别
		birthday date,-- 生日
		score double(5,2) ,-- 分数
		addr varchar(50),-- 地址
		status tinyint-- 状态
);
1
2
3
4
5
6
7
8
9

---

现在，我们已经学会了写SQL来操作数据库，但是我们在命令行中写SQL时，往往有体验感差，效率低等问题，现在开始我们就要学习在MySQL的图形化客户端Navicat中执行SQL语句。

Navicat 为数据库管理、开发和维护提供了一款直观而强大的图形化界面，大大的提高了工作效率，建议在学习中也使用这款开发工具。接下来，在Navicat中新建数据库，新建查询，我们就可以编写SQL并且执行SQL语句了。

7. DML- 增删改数据

7.1 添加数据

给指定列添加数据：

insert into 表名(列名1,列名2...) values(值1,值2...);
1

给全部列添加数据：

insert into 表名 values(值1,值2...);
1

批量添加数据：

insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)，(值1,值2...)...;
1

批量添加数据(省略字段名)：

insert into 表名 values(值1,值2...),(值1,值2...)，(值1,值2...)...;
1

在开发过程中添加数据时是不建议省略字段名的，这样降低了代码的可读性，使效率下降。例如：

查询表中的所有数据的方法是：

select * from 表名;
1

后面会用到的。

需求：往下面的tb_user表中添加一条数据。

insert into tb_user(id,name) values(2,'李四');
1

添加成功：

---

7.2 修改数据

修改表的数据：

update 表名 set 列名1=值1,列名2=值2...[where 条件];
1

在修改数据时，也可以不使用where条件，此时的操作是修改整列数据，这样的操作是很危险的。

需求：把下面tb_user表中的张三的密码改为abc23

update tb_user set passwor d ='abc123' where name='张三';
1

修改成功：

---

7.3 删除数据

删除表的数据：

delete from 表名 [where 条件];
1

在删除某条数据时，如果不使用where条件，将会导致删除整个表的数据。

需求：删除tb_user表中的李四记录。

delete from tb_user where name='李四';
1

操作成功：

8. DQL- 数据的查询操作

查询是数据操作至关重要的一部分，比如说在所有商品中查找出价格在规定范围内的所有商品，要想把数据库中的数据在客户端中展示给用户，一般都进行了查询的操作。

在实际开发中，我们要根据不同的需求，并且考虑查询的效率来决定怎样进行查询，学习查询前，可以先看看查询的完整语法：

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定
1
2
3
4
5
6
7
8
9
10
11
12
13
14

根据查询的完整语法中的关键字，我们分别来学习基础查询，条件查询，排序查询，分组查询和分页查询。

下面的练习中使用以下的案例学习单表查询：

-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(10), -- 姓名
age int, -- 年龄
gender varchar(5), -- 性别
math double(5,2), -- 数学成绩
english double(5,2) -- 英语成绩

);
-- 添加数据
INSERT INTO stu(id,name,age,gender,math,english)
VALUES
(1,'小张',23,'男',66,78),
(2,'小李',20,'女',98,87),
(3,'小陈',55,'男',56,77),
(4,'小樊',20,'女',76,65),
(5,'小马',20,'男',86,NULL),
(6,'小赵',57,'男',99,99);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在Navicat中选中SQL并执行：

8.1 基础查询

1. 基础查询语法

查询多个字段：

select 字段列表 from 表名;
1

查询全部字段：

select * from 表名;
1

去除重复记录：

select distinct 字段列表 from 表名;
1

起别名操作：

select 字段名 别名 from 表名;
1

2. 基础查询练习

使用学生表进行基础查询练习：

查询多个字段的练习：

select name,math from stu;
1

起别名操作练习：

select name,english 英语成绩 from stu;
1

8.2 条件查询

1. 条件查询语法

一般语法：

select 字段列表 from 表名 where 条件列表;
1

条件查询一般配合运行符进行，下面是常见的几个运算符：

2. 条件查询练习

使用学生表进行条件查询练习：

查询年龄大于20的学生信息：

select * from stu where age>20;
1

查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于21岁的学生信息：

select * from stu where age in(18,20,21);
1

模糊查询使用like关键字，可以使用通配符进行占位:

• _ : 代表单个任意字符
• % : 代表任意个数字符

查询姓名中含有张的学生信息：

select * from stu where name like '%张%';
1

8.3 排序查询

1. 排序查询语法

select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;
1

注：排序方式有两种：分别是升序ASC和降序DESC，默认情况下是升序ASC。

2. 排序查询练习

使用学生表进行排序查询练习：

查询学生信息，按照数学成绩降序排列：

select * from stu order by math DESC;
1

8.4 聚合函数

1. 聚合函数语法

什么是聚合函数呢？在进行查询操作时，往往需要对一整列进行运算，例如可以计算一整列成绩数据的平均值，我们就要使用聚合函数。下面是常见的聚合函数：

一般语法：

select 聚合函数 from 表名;
1

注：NULL值不参与聚合函数运算。

2.聚合函数练习

使用学生表进行聚合函数的练习：

统计该表中一共有几个学生：

select count(id) from stu;
1

上面我们使用某一字段进行运算，这样做可能面临的问题是某一个值可能是NULL，所以我们一般使用 * 进行运算，因为一行中不可能所有的字段都是NULL。

select count(*) from stu;
1

查询数学成绩的平均分:

select avg(math) from stu;
1

8.5 分组查询

1. 分组查询语法

select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]
1

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

2. 分组查询练习

使用学生表进行分组查询练习：

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组:

select gender, avg(math),count(*) from stu where math > 70 group by gender;
1

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的:

select gender, avg(math),count(*) from stu where math > 70 group by gender having count(*) > 2;
1

注：where 和 having 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。所以，where 不能对聚合函数进行判断，having 可以。

8.6 分页查询

1. 分页查询语法

在我们的印象中，网页在展示大量的数据时，往往不是把数据一下全部展示出来，而是用分页展示的形式，其实就是对数据进行分页查询的操作，即每次只查询一页的数据展示到页面上。

select 字段列表 from 表名 limit 查询起始索引,查询条目数;
1

在 limit 关键字中，查询起始索引这个参数是从0开始的。

2. 分页查询练习

使用学生表进行分页查询练习：

从0开始查询，查询3条数据：

select * from stu limit 0,3;
1

起始索引 = (当前页码 - 1) * 每页显示的条数

在SQL标准中，一共规定了6种不同的约束，包括非空约束，唯一约束和检查约束等，而在MySQL中是不支持检查约束的，所以这篇文章先对其余5种约束做一个详解和练习。

9. 约束的概念

约束是作用于表中列上的规则，用于限制加入表的数据。例如，作为主键的列一定是非空的唯一的，否则将无法对数据进行区分。约束的存在保证了数据库中数据的正确性、有效性和完整性。所以约束在数据库设计中是非常重要的。

10. 约束的分类

前面说到SQL标准把约束分为了6大类，分别是非空约束，唯一约束，主键约束，检查约束，默认约束和外键约束，添加约束时我们只需要在SQL中添加关键词，便可以限制表中的数据。

11. 非空约束

目的：保证列中所有的数据不能有null值

添加约束：

CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	…
);

1
2
3
4
5

建完表后添加非空约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
1

删除约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型;
1

12. 唯一约束

目的：保证列中所有数据各不相同

添加约束：

CREATE TABLE 表名(
	列名 数据类型 UNIQUE [AUTO_INCREMENT],
	-- AUTO_INCREMENT: 当不指定值时自动增长
	…
);

CREATE TABLE 表名(
	列名 数据类型,
	…
	[CONSTRAINT] [约束名称] UNIQUE(列名)
);
1
2
3
4
5
6
7
8
9
10
11

建完表后添加唯有约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
1

删除约束：

ALTER TABLE 表名 DROP INDEX 字段名;
1

13. 主键约束

目的：主键是一行数据的唯一标识，要求非空且唯一

添加约束：

CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
	…
);

CREATE TABLE 表名(
	列名 数据类型,
	[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);
1
2
3
4
5
6
7
8
9

建完表后添加主键约束：

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
1

删除约束：

ALTER TABLE 表名 DROP PRIMARY KEY;
1

14. 默认约束

目的：保存数据时，未指定值则采用默认值

添加约束：

CREATE TABLE 表名(
	列名 数据类型 DEFAULT 默认值,
	…
);
1
2
3
4

建完表后添加默认约束：

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
1

删除约束：

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
1

15. 外键约束

当我们添加了外键以后，就在数据库层面建立了两张表的关系。

目的：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

添加约束：

CREATE TABLE 表名(
	列名 数据类型,
	…
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
1
2
3
4
5

建完表后添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
1

删除约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
1

16. 约束的案例练习

首先，我们使用以下的案例来练习约束：

-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int primary key, -- 编号 主键
name varchar(10)  not null unique, -- 姓名 非空，唯一
age int not null, -- 年龄 非空
gender varchar(5) not null, -- 性别 非空
math double(5,2) not null, -- 数学成绩  非空
english double(5,2) default 0 -- 英语成绩 默认为0
);
1
2
3
4
5
6
7
8
9
10
11

验证主键约束，其特点是非空且唯一，先添加一条数据：

insert into stu(id,name,age,gender,math,english) values(1,'小张',23,'男',66,78);
1

添加第二条数据时，尝试 id 添加为空值：

insert into stu(id,name,age,gender,math,english) values (null,'小李',20,'女',98,87);
1

尝试 id 添加为重复的值：

insert into stu(id,name,age,gender,math,english) values (1,'小陈',55,'男',56,77);
1

我们已经验证了主键约束，当我们添加不合法的数据时，添加失败。

验证非空约束：

当我们添加以下的数据时：

insert into stu(id,name,age,gender,math,english) values (2,NULL,20,'女',76,65);
1

验证唯一约束：

当我们添加以下的数据时：

insert into stu(id,name,age,gender,math,english) values 
(5,'小张',20,'男',86,NULL);
1
2

验证默认约束：

当我们添加以下的数据时：

insert into stu(id,name,age,gender,math) values (6,'小赵',23,'男',99);
1

验证外键约束：

我们使用一下的案例来验证外键约束：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部门表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

此时，我们在员工表中添加了外键，相当于在数据库层面上建立了两张表的关系，此时如果员工表中有员工 a ，他属于 1 号部门，那么我们想要删除部门表中的 1 号部门就会删除失败，因为员工 a 是属于 1 号部门的，此时两张表建立了关系。

添加数据：

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','西安'),('销售部', '成都');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 20, 2);
1
2
3
4
5
6
7

此时想要是删除销售部门时，发现删除失败。

17. 数据库设计

17.1 概念

数据库设计是软件研发过程中很重要的一个环节，在学习数据库设计之前，要先了解软件研发的步骤。

一个成熟的软件，从想法的产生到成功上线，要经历需求调研及分析，设计，编码，测试和部署等过程，在开始阶段产品经理会根据客户的需求设计产品原型，而设计一般有架构师和开发工程师完成，这其中就包括数据库设计，还有软件结构设计，接口设计等，接着就是由开发工程师来编写代码，由测试工程师来测试，接着就可以部署上线。

数据库设计就是根据业务系统的具体需求，结合我们所选用的 DBMS，为这个业务系统构造出最优的数据存储模型。通俗的说就是建立数据库中的表结构以及表与表之间的关联关系的过程，分析有哪些表，哪些字段等。

数据库设计分为以下几个步骤：

• 需求分析
• 逻辑分析
• 物理分析
• 维护设计

17.2 表的关系

表和表之间的关系有一对一的关系，主要用于表的拆分，利于把常用数据和不常用数据分开存储，提高查询的效率。一对多或者多对一的关系，例如部门表和员工表的关系，多对多的关系，例如商品和订单的关系。

一对多的关系中，在表示多的一方建立外键，指向另一方的主键，示例：

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查看两表结构模型：

多对多的关系中，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，例如商品表和订单表。示例：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id int primary key auto_increment,
	title varchar(100),
	price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES
tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES
tb_goods(id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

查看两表的结构模型：

一对一的关系中，在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)，例如用户和用户详情。

示例：

create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);
create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

18. 多表查询

前面学习查询操作时，一直是从一张表中查询数据，而数据库的多表查询操作也是非常重要的一部分。多表查询就是一次性从多张表中查询出需要的数据。

在MySQL中多表查询分为连接查询和子查询，而连接查询又分为内连接查询和外连接查询，外连接查询的方式有左外连接查询和右外连接查询。下面对多表查询做一些基础的练习！

下面是演示多表查询使用的 sql:

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

# 创建部门表
CREATE TABLE dept(
	did INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- 性别
	salary DOUBLE, -- 工资
	dep_id INT,
	FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,dep_id) VALUES
('小张','男',3000,1),
('小李','女',3600,2),
('小王','男',9000,2),
('小美','女',5000,null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

执行 sql ，创建表：

18.1 内连接查询

内连接查询分为隐式内连接查询和显式内连接查询，表示查询 A 集合和 B 集合的交集。

语法：

-- 隐式内连接查询
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接查询
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
1
2
3
4
5

隐式内连接查询示例：

select * from emp,dept where dep_id=did;
1

结果：

显式内连接查询示例：

select * from emp inner join dept on dep_id=did;
1

结果：

18.2 外连接查询

外连接查询分为作为连接查询和右外连接查询。左外连接查询：相当于查询A表所有数据和交集部分数据。

右外连接查询：相当于查询B表所有数据和交集部分数据。

语法：

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
1
2
3
4
5

左外连接查询示例：

select * from emp left outer join dept on dep_id=did;
1

结果：

右外连接查询示例：

select * from emp right outer join dept on dep_id=did;
1

结果：

18.3 子查询

子查询是指查询中嵌套有查询，以下的几种情况通常使用嵌套查询：

子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断，例如查询员工中工资大于小李的员工信息：

select * from emp where salary >(select salary from emp where name='小李');
1

结果：

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断，例如查询研发部和财务部员工信息：

select * from emp where dep_id in (select did from dept where dname='研发部' or dname= '财务部');
1
2

结果：

子查询语句结果是多行多列，子查询语句作为虚拟表，例如查询员工中工资大于3000的员工的信息和部门信息：

select * from (select * from emp where salary>3000) t1, dept where t1.dep_id=dept.did;
1

19. 事务

19.1 概念

数据库的事务是一种机制，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

例如在进行转账操作时，张三转账给李四 500 元，首先会验证张三账户余额是否充足，如果是，则张三账户减少 500，李四账户增加 500 。如果在这过程中，由于某种原因出现错误，李四的账户没有增加 500 ，那么张三账户减少 500 的操作也不能完成。也就是要保证在这个过程中张三和李四账户余额总和要保持不变。

19.2 语法

开启事务：

START TRANSACTION;
或者
BEGIN;
1
2
3

回滚事务：

commit;
1

提交事务：

rollback;
1

小练习：张三转账给李四

DROP TABLE IF EXISTS account;
-- 创建账户表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name varchar(10),
	money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);
1
2
3
4
5
6
7
8
9

添加事务：

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 张三账户 -500
UPDATE account set money = money - 500 where name = '张三';
3%0;--出现异常
-- 3. 李四账户 +500
UPDATE account set money = money + 500 where name = '李四';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9
10
11
12
13

如果不添加事务，张三的账户就会减少 500 ，而李四的账户并不会增加 500，这样并不符合我们的实际生活。否则张三的 500 便不翼而飞了，哈哈哈！可见事务的重要性。

19.3 特征

MySQl 事务具有四大特征，分别是：

• 原子性: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性 :事务完成时，必须使所有的数据都保持一致状态
• 隔离性: 多个事务之间，操作的可见性
• 持久性: 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

20. 总结

科技发展，社会进步。未来对数据的定义会不断地革新，生活的方方面面都会被列入数据的行列。从某种意义上来说，数据就是信息，只是数据不能直观地带来价值，而信息可以，但未来，这两者之间的距离会越来越缩小，直至划上等号。

未来是一个数字化的时代，数据是我们最为宝贵的资源，不论是哪个专业出身，未来或多或少都会卷入数据时代的浪潮之中。而那些前提性的工作基本都是依靠数据库来完成。

祝你事业有成！下期见。