MySQL

安装使用
数据库操作
数据库约束

安装使用

安装

使用管理员身份运行cmd，进入安装路径的 bin 文件：

cd C:\Users\licong23\code\node\mysql-8.0.25-winx64\bin

执行安装：

mysqld --install
// Service successfully installed.

1
2

执行移除：

mysqld --remov
// Service successfully removed

1
2

mac 终端启动

启动 mysql 服务:

sudo /usr/local/mysql/support-files/mysql.server start

停止 mysql 服务:

sudo /usr/local/mysql/support-files/mysql.server stop

windows 启动

到电脑服务窗口中启动
命令方式启动

net start mysql
// MySQL 服务正在启动.
// MySQL 服务已经启动成功.

1
2
3

连接数据库

命令行连接：

mysql -u root -p

连接远程 mysql:

mysql --host=[ip] --user=root --password=123456

命令行退出:

exit

修改密码

原始密码：

账号: root
密码: 123456

执行以下语句，即可将密码改为123456:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

命令符

; 表示每一行的结束。
\h 显示帮助内容。
\c 清除命令行。
[where condition] 查询条件。

TIP

数据存储的位置被称为表，表存储的位置被称为数据库。我们需要先建数据库，然后建表，然后插入数据，再进行查询。

建数据库 -> 建表 -> 插入数据 -> 查询。

SQL

SQL 是用于访问和处理数据的标准的计算机语言。

SQL 语句不区分大小写，但是字符串里面的值区分大小写。

SQL 语句必须以分号结尾，空格和换行没有限制。

SQL 语言分类

数据定义语言：简称 DDL (Data Definition Language)
- 用来定义数据库对象: 数据库、表、列；关键字：create、drop、alter 等
数据操作语言：简称 DML (Data Manipulation Language)
- 用来对数据库中表的数据进行增删改。关键字： insert、update、delete 等
数据查询语言：简称 DQL(Data Query Language)
- 用来查询数据库中表的数据，关键字： select from where 等
数据控制语言：简称 DCL(Data Control Language)
- 用来定义数据库访问权限和安全级别，创建用户等。关键字： grant 等

DDL 语句

用来操作数据库以及表的create、drop、alter等

数据库相关

查看已有的数据库：

show databases;

创建新的数据库dbname：

create database [dbname];

切换数据库：

use [dbname];

查看当前使用的数据库：

select database();

删除数据库：

drop database [dbname];

TIP

新建数据库，选择 utf8mb4 字符集

表相关

查看数据库中的表：

show tables;

创建表：

create table [table_name]([Field_name] [type_name]);

复制一个相同的表结构：

create table [new_table_name] like [old_table_name]
// 使用`like`关键字

1
2

删除表：

drop table [table_name];

修改表名：

alter table [table_name] rename [new_table_name];

查看表的基本信息：

desc [table_name];

修改表结构：

alter table [table_name] modify [Field_name] [type_name]

对表结构增加一个字段：

alter table [table_name] add [Field_name] [type_name]

对表结构删除一个字段：

alter table [table_name] drop column [Field_name]

DML 语句

对表进行insert、delete、update

插入

注意事项：

值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它字段类型的值必须使用引号
如果要插入空值，可以使用NULL，这个不用加单引号

指定字段名，插入数据：

insert into [table_name]([Field_name], [Field_name]) values('value', 'value');

不指定字段名，一一对应全部插入数据：

insert into [table_name] values('value', 'value');

一次性插入多条数据：

insert into [table_name]([Field_name], [Field_name]) values
('value', 'value'),
('value', 'value'),
('value', 'value');

1
2
3
4

批量插入数据忽略失败的数据行: ignore

insert ignore into [table_name]([Field_name], [Field_name]) values
('value', 'value'),
('value', 'value'),
('value', 'value');

1
2
3
4

实现不存在就插入，存在就更新：

insert ignore into [table_name]([Field_name], [Field_name]) values
('value', 'value'),
('value', 'value'),
('value', 'value')
on duplicate key update [Field_name]=values([Field_name]);

1
2
3
4
5

insert 方言语法

insert into [table_name] set [Field_name]='value1', [Field_name]='value2'

蠕虫复制

创建，先复制表结构：

create table [new_table_name] like [old_table_name];

再将数据复制到新表中：

insert into [new_table_name] select * from [old_table_name];

或者只复制部分数据到新表中：

insert into [new_table_name]([Field_name], [Field_name]) select [Field_name], [Field_name] from [old_table_name];

更新

默认的安全策略，使得更新时，如果不依靠主键列进行查询时将被提示并失败。
解决方式：SET SQL_SAFE_UPDATES = 0;

子句执行顺序： UPDATE -> WHERE -> ORDER BY -> LIMIT -> SET

不带条件，将全部修改：

update [table_name] set [Field_name] = 'value';

带条件修改，一条：

update [table_name] set [Field_name] = 'value' [where condition];

带条件修改，多条：

update [table_name] set [Field_name]='value', [Field_name]='value', [Field_name]='value' [where condition];

更新多张表的记录

可以使用表连接的方式，去修改多张表里面更新数据。

UPDATE [table_name] as t1 JOIN [table_name] as t2
SET t1[Field_name]='newVal', t2[Field_name]='newVal'
WHERE t1[Field_name]='value' AND t2[Field_name]='value';

1
2
3

连接一张临时表：

UPDATE [table_name] as t1 JOIN
(SELECT AVG([Field_name]) as avg FROM [table_name]) as t2
ON t1[Field_name]<t2[Field_name]
SET t1[Field_name]=t2[Field_name]+100;

1
2
3
4

删除

子句执行顺序： FROM -> WHERE -> ORDER BY -> LIMIT -> DELETE

删除指定条件的记录：

delete from [table_name] [where condition];

删除指定条件的第一条记录：

delete from [table_name] [where condition] order by [Field_name] desc limit 1;

不指定条件，全部删除：

delete from [table_name];

清空整张表的记录，并重新创建一个新的相同表结构：

truncate table [table_name];

删除多张表的记录

使用表连接的方式。

语法格式：

delete t1, t2
from [table_name] as t1 join [table_name] as t2 on []
[where condition]
order by [Field_name] desc
limit;

1
2
3
4
5

DQL 语句

查询语句的子句执行顺序:

1.词法分析与优化，读取 SQL 语句 2.FROM，选择数据来源 3.SELECT，选择输出内容

对表进行各种维度select查询

查询表中所有数据：

select * from [table_name];

查指定的数据项：

select [Field_name] from [table_name];

条件查询：

select * from [table_name] [where condition];

去重（如果查询多列，去重会失效，只能只用一次）：

select distinct [Field_name] from [table_name];

查询结果参与运算：

select [Field_name] + [Field_name] from [table_name];

-- 参与运算的必须是数值类型，可以是固定值也可以是其它字段名

1
2
3

模糊查询

在 select 中的 like 表示模糊查询

通配符有两个：

%：表示 0 个或多个字符
_：表示一个字符

select * from [table_name] where [Field_name] like '%value%';

范围查询

两种方式：

使用比较运算符
使用between加and

select * from [table_name] where [Field_name] between 'value' and 'value';

where 条件

where 子句中，条件执行的顺序是从左到右。所以我们应该把索引条件，或者筛选记录最多的条件写在最左侧，先过滤掉最多的记录。

执行顺序：FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT

比较运算符: > < >= <= == != <>
逻辑运算符: and or not in() not in()
位运算符
算数运算符

排序

执行顺序：FROM -> SELECT -> ORDER BY -> LIMIT

order by：

ASC: 升序，默认
DESC: 降序

第一条件相同的情况下，按照主键大小排序。

单列排序，只通过一个字段排序：

select * from [table_name] where [where condition] order by [Field_name] desc;

组合排序，先通过第一个字段排序，如果相同，再通过第二个字段：

select * from [table_name] where [where condition] order by [Field_name] desc, [Field_name] desc, [Field_name] desc;

分页查询

执行顺序：FROM -> SELECT -> LIMIT

使用limit语句，限制查询记录。

语法格式：

offset: 是值偏移量，默认为 0
length: 是值需要查询的总条数

跳过前面 20 条，查询后 10 条：

select * from [table_name] limit 20,10;

完整例子：

select *|[Field_name] [as 别名]
from [table_name]
where [where condition]
group by [Field_name] having[]
order by [Field_name] desc
limit[];

1
2
3
4
5
6

聚合函数

可以对数据求和、求最大值和最小值、平均值。返回的结果只有一条数据。

可以纵向查询，对一列的值进行计算，然后返回一个结果，会忽略空值。

使用顺序一定是在GROUP BY分组之后，不能出现在 WHERE 语句里面，语法会报错

聚合函数：
- count 统计数量
- sum 求和
- max 最大值
- min 最小值
- avg 平均值

计算总数count：

select count([Field_name]) from [table_name];

求平均值(IFNULL 处理 null 的情况)：

select avg([field_name] + IFNULL([field_name], 0)) from [table_name] where [where condition];

分组查询

执行顺序：FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

GROUP BY子句的作用是通过一定的规则将一个数据集，划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理。

select子句中可以包括聚合函数，或者GROUP BY子句的分组列，但其余字段内容都不能出现，否则会报错。可以使用GROUP_CONCAT函数将未分组的字段拼接为一条字符串。

group by：关键字表示对分类聚合的字段进行分组
with：表示对汇总之后的记录进行再次汇总
- with rollup：对每一列的结果再次汇总
having：表示对分类后的结果再进行条件的过滤，和where类似用法，会在GROUP BY后面执行，里面可以使用聚合函数做条件判断

[Field_name]是分组规则，要结合聚合函数使用，计算某一组中的数据：

select sum([Field_name]),[Field_name] from [table_name] group by [Field_name];

逐级分组，先以[Field_name_1]分成一组，再用[Field_name_2]细分出一组：

select sum([Field_name]),[Field_name] from [table_name] group by [Field_name_1], [Field_name_2];

分组group by加having一起使用，在分组后再添加条件：

select count(*) from [table_name] where [where condition] group by [Field_name] having count(*) > 10;

having 与 where 的区别：

having 是在分组后对数据进行过滤。
where 是在分组前对数据进行过滤。
having 后面可以使用聚合函数。
where 后面不可以使用聚合函数。

关键字

distinct 去重。
where 条件查询。
order by 排序，desc降序排列，asc升序排列。
limit 限制，只查询指定条数。
group by 分组

表连接

内连接
外连接

内连接是结果集中只保留符合连接条件的记录。外连接是不管符不符合连接条件，记录都保留在结果集中。

内连接

select * from [table_name_1] as t1 join [table_name_2] as t2 on [连接条件];

内连接的数据不一定必须有同名字段，只要字段之间符合逻辑关系即可。

相同的表也可以和自己做连接。

可以将查询出来的条件，视为一张临时表，然后使用表连接，代替子查询。

外连接

外连接会保留符合条件的记录，并且会保留不符合条件的记录。

LEFT：保留左表的全部记录，和右表去连接
RIGHT：保留右表的全部记录，和左表去连接

UNION关键字，可以将左右外连接查询语句的结果集进行合并。

子查询

子查询是一种查询中嵌套查询的语句。

在 SELECT 语句中使用子查询效率低下，但是将子查询结果视作一张临时表使用表连接效率会很高。

WHERE 子句和 SELECT 子句会执行多次，不推荐在里面使用。但可以在 FROM 子句中转成表连接使用，只会执行一次。

按照使用划分：

FROM 子查询
WHERE 子查询
SELECT 子查询

按照数据划分：

单行子查询，只有一条记录
多行子查询，有多行记录，不能出现在SELECT 子查询中

公共表表达式

子句执行顺序和执行次数

子句执行顺序：FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

FROM: 执行一次
WHERE: 执行多次
SELECT: 执行多次，每输出一条记录都要执行一次

Datatype 数据类型

字符串类型
数值类型
日期和时间类型

字符串类型

类型	大小(8bit)	说明
CHAR()	1-255 字节	固定长度字符串
VARCHAR()	1-65535 字节	不固定长度字符串
TEXT	1-65535 字节	不确定长度字符串
MEDIUMTEXT	1-1 千 6 百万字节	不确定长度字符串
LONGTEXT	1-42 亿字符	不确定长度字符串

数值类型

类型	大小(8bit)	说明
INTEGER	1 字节	小整数
SMALLINT	2 字节	普通整数
MEDIUMINT	3 字节	普通整数
INT	4 字节	较大整数
BIGINT	8 字节	大整数
FLOAT	4 字节	大整数
DOUBLE	8 字节	大整数
DECIMAL	----	非常精确

binary和varbinary: 包含二进制字符串。
blob: 一个二进制大对象。
enum: 枚举类型，会转换为大写。

小数位

浮点数：

单精度浮点型: FLOAT 型
双精度浮点型: DOUBLE 型

定点数：

DECIMAL

FLOAT和DOUBLE都无法做到完全精确，十进制转换二进制存储的时候会丢失精度。DECIMAL可以非常精确保证不丢失精度，因为它使用了字符串。

日期和时间类型

类型	大小(8bit)	说明
DATE	3 字节	日期格式`YYYY-MM-DD`
TIME	3 字节	日期格式`HH:MM:SS`
YEAR	1 字节	日期格式`YYYY`
DATETIME	8 字节	日期格式`YYYY-MM-DD HH:MM:SS`
TIMESTAMP	4 字符	日期格式`YYYY-MM-DD HH:MM:SS UTC`

数据库设计

用主键字段查询会非常快。

数据拆分保存到不同的表中，彼此保持关联。

三大范式

第一范式：原子性
- 拆分到最细致
第二范式：唯一性
- 必须有主键
第三范式：关联性
- 表中每一列都与主键有直接关系，不存在传递依赖

字段约束

保证数据的正确性、有效性和完整性

约束种类：

PK(PRIMARY KEY): 主键约束
AI(AUTO_INCREMENT): 自动增长
NN(NOT NULL): 非空约束
UQ(UNIQUE): 唯一约束
DEFAULT: 默认值
FOREIGN KEY: 外键约束
INDEX: 索引

主键约束

PRIMARY KEY，用来唯一标识一条记录，每个表都应该有一个主键，必须唯一，不能为 NULL 值，建议使用数字类型。

通常不用业务字段作为主键，而是把 id 作为主键。主键是给数据库和程序使用的，不是给用户使用的，可以没有含义，但是不能重复，且有值。

主键起始值默认为 1，可以修改为 100

alter table [table_name] AUTO_INCREMENT=100;

唯一约束

添加UNIQUE：

create table [table_name] (
	id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	phone int UNIQUE NOT NULL,
)

1
2
3
4

外键约束

外键约束用来保证关联数据的逻辑关系。

外键约束的定义是写在子表上的FOREIGN KEY。

如果形成外键闭环结构，我们将无法删除任何一张表的记录，会丧失灵活度，在真实的项目中放弃使用外键约束。

父表（部门）：

CREATE TABLE t_dept (
  deptno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  dname VARCHAR(20) NOT NULL UNIQUE,
  tel CHAR(11) UNIQUE
);

1
2
3
4
5

子表（员工）：

CREATE TABLE t_emp (
  empno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20) NOT NULL,
  deptno INT UNSIGNED NOT NULL,
  FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);

1
2
3
4
5
6

索引

针对数据量很大，而且经常被查询的数据表可以设置索引，索引只添加在经常被用作检索条件的字段上面。

如果数据量很小，查询很少，可以不必设置索引。

可以更好的查找，排序，优化。

向表中添加索引：

CREATE TABLE t_emp (
  empno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20) NOT NULL,
  deptno INT UNSIGNED NOT NULL,
  INDEX idx_no (deptno)
);

1
2
3
4
5
6

查看表中的索引：

SHOW INDEX FROM [table_name];

运算符

算数运算符
比较运算符
逻辑运算符
位运算符

算数运算符

在 mysql 中，任何数与null进行算数运算，结果都是null。可以使用自带的IFNULL方法，转换成 0.

IFNULL(null, 0)

比较运算符

IN: 包含
IS NULL: 为空
IS NOT NULL: 不为空
BETWEEN AND: 范围之间
LIKE: 模糊查询
REGEXP: 正则表达式

逻辑运算符

AND: 与
OR: 或
NOT: 非
XOR: 异或

内置函数

字符函数
数字函数
日期函数
条件函数

字符函数

数字函数

日期函数

DATE_FORMAT(日期, 表达式)函数: 用于格式化日期，返回想要的日期格式

两个日期不能与数字直接加减，需要使用计算函数：DATE_ADD()

条件函数

IFNULL(表达式,值)
IF(表达式,值1,值2)
CASE ... END

事务机制

理解：事务是一个或多个 SQL 语句组成的整体，要么全部执行成功，要么全部执行失败。

MySQL 总共有 5 中日志，其中只有redo日志和undo日志与事务有关。

利用日志来实现间接写入

如果在批量修改数据库的时候，突然断电或发生意外，会导致无法区分哪些数据已被修改，哪些未被修改。这是非常严重的灾难。所以需要使用事务机制。

事务使用流程

开启事务
执行 SQL 语句
提交事务

ACID 属性

原子性：要么成功，要么失败，不允许停留在某个中间状态。
一致性：无论给定任何时间、并发事务有多少，必须保证运行结果的一致性。
隔离性：事务不受其它并发事务的影响。
持久性

事务隔离级别

按需要选择使用哪种隔离级别，读取不同隔离中的数据。

重复读取（默认级别）：代表事务在执行中反复读取数据，得到的结果是一致的，不会受其它事务影响。

数据导入与导出

导出:

导出的存粹是业务数据
数据量小的时候适合导出成 SQL 文件
数据量大的时候适合导出成文本文档

导入：

导入文本文档数据之前，需要创建表结构

# MySQL

# 安装使用

# 安装

# mac 终端启动

# windows 启动

# 连接数据库

# 修改密码

# 命令符

# SQL

# SQL 语言分类

# DDL 语句

# 数据库相关

# 表相关

# DML 语句

# 插入

# insert 方言语法

# 蠕虫复制

# 更新

# 更新多张表的记录

# 删除

# 删除多张表的记录

# DQL 语句

# 模糊查询

# 范围查询

# where 条件

# 排序

# 分页查询

# 聚合函数

# 分组查询

# 关键字

# 表连接

# 内连接

# 外连接

# 子查询

# 公共表表达式

# 子句执行顺序和执行次数

# Datatype 数据类型

# 字符串类型

# 数值类型

# 小数位

# 日期和时间类型

# 数据库设计

# 三大范式

# 字段约束

# 主键约束

# 唯一约束

# 外键约束

# 索引

# 运算符

# 算数运算符

# 比较运算符

# 逻辑运算符

# 内置函数

# 字符函数

# 数字函数

# 日期函数

# 条件函数

# 事务机制

# 事务使用流程

# ACID 属性

# 事务隔离级别

# 数据导入与导出

MySQL

安装使用

安装

mac 终端启动

windows 启动

连接数据库

修改密码

命令符

SQL

SQL 语言分类

DDL 语句

数据库相关

表相关

DML 语句

插入

insert 方言语法

蠕虫复制

更新

更新多张表的记录

删除

删除多张表的记录

DQL 语句

模糊查询

范围查询

where 条件

排序

分页查询

聚合函数

分组查询

关键字

表连接

内连接

外连接

子查询

公共表表达式

子句执行顺序和执行次数

Datatype 数据类型

字符串类型

数值类型

小数位

日期和时间类型

数据库设计

三大范式

字段约束

主键约束

唯一约束

外键约束

索引

运算符

算数运算符

比较运算符

逻辑运算符

内置函数

字符函数

数字函数

日期函数

条件函数

事务机制

事务使用流程

ACID 属性

事务隔离级别

数据导入与导出