MySQL 基础

简介 ¶

MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，后来被 Sun Microsystems 收购，最终成为 Oracle 的一部分。它是基于 SQL（Structured Query Language，结构化查询语言）来进行数据存储和管理的。MySQL 广泛用于 Web 应用程序、企业级应用程序和大数据处理场景，支持多种操作系统，包括 Linux、Windows 和 macOS。

MySQL 是一种基于客户端 - 服务器架构的数据库系统，其主要功能是提供数据存储、数据查询和事务处理能力。它支持 ACID（原子性、一致性、隔离性、持久性）事务，并具备高效、稳定的性能。

存储引擎 ¶

• InnoDB 更适合需要高并发、事务支持和外键约束的场景。
• MyISAM 更适合以读为主、对事务要求不高的场景，比如数据仓库和分析系统。

InnoDB¶

特点：

• 支持事务：InnoDB 是一个支持 ACID 事务的引擎，通过 COMMIT​、ROLLBACK​ 和 SAVEPOINT​ 操作来确保数据完整性。
• 外键约束：支持外键，能够维护表之间的引用完整性，防止无效的数据插入。
• 行级锁：InnoDB 支持行级锁，可以更好地处理高并发事务。相比于表级锁，它的并发性能更高。
• 崩溃恢复：通过日志文件和检查点进行崩溃恢复，保证数据在意外情况下的恢复能力。
• 聚簇索引：InnoDB 使用聚簇索引，将数据和主键一起存储，因此查询主键时性能较高。

场景：

• 需要事务支持的场景：如银行、财务等对数据完整性要求高的系统。
• 高并发读写的应用：行级锁适合高并发写操作，比如电商网站、社交网络等。
• 需要外键约束的数据库设计：InnoDB 提供的外键约束功能可以自动维护数据的一致性。

MyISAM¶

特点：

• 不支持事务：MyISAM 不支持事务，数据完整性只能通过应用程序控制。
• 表级锁：MyISAM 采用表级锁，每次操作会锁定整个表，因此在高并发写入时性能较差。
• 查询速度较快：MyISAM 的读性能较好，特别适合以查询为主的应用。
• 存储空间较小：MyISAM 索引和数据分开存储，且不支持事务日志，因此占用的磁盘空间通常较小。
• 全文索引：MyISAM 支持全文索引。

场景：

• 查询密集型应用：如数据仓库、数据分析系统等场景，写操作较少、查询操作较多。
• 不需要事务控制的应用：如一些简单的数据展示平台。
• 需要更少存储空间的场景：MyISAM 占用存储空间相对较少，适合数据量较大的历史数据存储。

事务 ¶

事务是在数据库中执行的一系列操作单元，这些操作要么都成功提交，要么都失败回滚。

ACID¶

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致，即事务必须将数据库从一个一致性状态带到另一个一致性状态。
• 隔离性（Isolation）：事务的执行不会受到其他事务的干扰。
• 持久性（Durability）：一旦事务成功提交（COMMIT​），它对数据库的修改就是永久性的，不会因为系统崩溃或其他故障而丢失。

隔离级别 ¶

• READ UNCOMMITTED（读未提交）：事务可以读取未提交事务的数据。
• READ COMMITTED（读已提交）：事务只能读取已经提交的事务的数据。
• REPEATABLE READ（可重复读）：事务在执行过程中，所有读取的数据都保持一致，不会被其他事务修改。
• SERIALIZABLE（可串行化）：事务完全隔离，每个事务都仿佛是串行执行的，其他事务无法读取、修改或插入正在进行的事务的相关数据。

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	允许	允许	允许	最好
READ COMMITTED	不允许	允许	允许	较好
REPEATABLE READ	不允许	不允许	允许	中等
SERIALIZABLE	不允许	不允许	不允许	最差

事务异常 ¶

• 脏读（Dirty Read）：事务 A 在未提交的情况下读取了事务 B 的数据。若事务 B 最终回滚，事务 A 就读取到了无效数据，即事务 A 读取的数据可能不再有效或与最终提交的数据不一致。
• 不可重复读（Non-repeatable Read）：事务 A 在执行两次相同的查询时，查询结果发生了变化。换句话说，在事务 A 执行期间，其他事务修改了事务 A 正在查询的数据。
• 幻读（Phantom Read）：事务 A 在执行查询时，另一个事务 B 插入、删除或更新了使得事务 A 查询结果集发生变化的数据。事务 A 在执行两次相同查询时，查询的结果集发生了变化，虽然事务 A 查询的条件没有变化。
• 锁竞争（Lock Contention）：多个事务尝试同时访问同一数据时发生的竞争条件，可能导致事务在等待锁时产生延迟或阻塞。
• 死锁（Deadlock）：两个或多个事务相互等待对方释放锁，导致它们永远无法继续执行。

并发控制 ¶

悲观锁 ¶

悲观锁假设最坏的情况，认为多个线程或进程在同一时间访问数据时，必定会发生冲突，因此每次访问共享数据时都会加锁，确保只有一个线程能够访问数据。

特点：

• 加锁策略：每次访问数据时，都会加锁，其他线程或进程必须等待当前线程释放锁才能继续操作。
• 锁的类型：通常使用数据库的 SELECT FOR UPDATE​、LOCK TABLE​ 等语句进行加锁，或者在分布式系统中使用如 Redis 锁、Zookeeper 等工具进行加锁。
• 性能影响：悲观锁可能会导致性能瓶颈，因为锁的粒度较大，且加锁会阻塞其他线程的访问。锁释放的时机需要谨慎控制，否则会导致死锁或性能下降。
• 适用场景：适用于竞争非常激烈的环境，比如多个线程同时修改某个数据时。悲观锁确保每次只有一个线程可以访问数据，避免了冲突，但可能带来较高的延迟。

乐观锁 ¶

乐观锁假设不会发生冲突，允许多个线程或进程并发地访问和修改数据，只有在提交时才会检查是否发生冲突。如果发生冲突，操作会失败，通常会重新尝试。

特点：

• 加锁策略：在操作开始时不加锁，而是在数据提交时检查是否有冲突。如果数据在操作过程中被修改，操作会失败或者回滚。
• 锁的类型：乐观锁通常通过版本号或时间戳的方式来实现，每次读取数据时记录当前版本号，在提交更新时检查数据的版本号是否发生变化。常见的实现方式是通过在数据表中添加版本号（version​）字段。
• 性能影响：由于乐观锁不在数据访问时加锁，而是通过版本控制来进行并发检测，因此它的性能相对较好，尤其适用于读多写少的场景。
• 适用场景：适用于并发读取较多、写入冲突较少的场景，比如订单管理系统、社交媒体的点赞等。

比较 ¶

特性	悲观锁（Pessimistic Lock）	乐观锁（Optimistic Lock）
加锁时机	访问数据时立即加锁，阻止其他线程操作	在提交时检查数据是否被修改，只有冲突时才检测
性能	可能会导致性能瓶颈，尤其是高并发情况下，锁竞争激烈时性能差	性能较高，尤其是读多写少的场景，因为不会频繁加锁
适用场景	数据冲突较多的场景，如高并发写操作	数据冲突较少的场景，如高并发读操作
实现难度	简单，直接通过锁来保护数据的一致性	较复杂，需要使用版本号或时间戳等机制来检测冲突
死锁风险	存在死锁风险，如果锁的顺序不当或超时不释放	无死锁风险
冲突检测	在操作时防止冲突，确保互斥	仅在提交时检测冲突，操作时不加锁

操作语言 ¶

• 数据定义语言 DDL（Data Definition Language）：用于定义数据库对象（如数据库、表、索引等）的结构和关系。
• 数据查询语言 DQL（Data Query Language）：主要用于查询数据库中的数据，DQL 的核心是 SELECT​ 语句。
• 数据操作语言 DML（Data Manipulation Language）：用于对数据库表中的数据进行增删改操作。
• 数据控制语言 DCL（Data Control Language）：用于定义数据库的访问权限和安全级别，管理用户权限。
• 事务控制语言 TCL（Transaction Control Language）：用于管理数据库事务，确保数据的完整性和一致性。

注意：SQL 语句以 ;​结束，不区分大小写。

安装与配置 ¶

MySQL 分为服务端和客户端，一般我们不使用官方的 Workbench 客户端，因此只需要安装好 Community Server ，即免费社区版服务端。

也可以使用 Docker 来直接启动一个 MySQL 服务。

以 Windows 的 5.7 版本的 ZIP Archive 为例。

1. 解压下载的 ZIP 文件到你想要安装 MySQL 的目录。假设我们解压到 C:\mysql​ 目录。
2. 创建数据存放目录。

mkdir data

3. 修改配置文件。

将 my-default.ini​ 重命名为 my.ini​ ，修改为如下配置：

[mysqld]
# MySQL Server 配置
basedir=C:/mysql
datadir=C:/mysql/data
port=3306
socket=mysql.sock
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 配置字符集
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 错误日志
log-error=C:/mysql/data/mysql_error.log

[client]
port=3306
socket=mysql.sock

4. 初始化数据库。

进入 mysql​ 目录，执行：

bin\mysqld --initialize --console

会输出密码，如 abcdefgh​：

[Note] A temporary password is generated for root@localhost: abcdefgh

5. 启动服务。

bin\mysqld --defaults-file=my.ini --daemonize

6. 登录。

bin\mysql -u root -p

回车后，直接输入初始化的密码，密码是不可见的。

7. 修改密码。

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

\q;

8. 设置开机自启。

# 注册服务
bin\mysqld --install MySQL --defaults-file=C:\mysql\my.ini

# 启动
net start MySQL
# 停止
net stop MySQL

DDL¶

数据库 ¶

创建 ¶

CREATE DATABASE `库名`
  [[DEFAULT] CHARACTER SET 字符集]
  [[DEFAULT] COLLATE 排序规则]
  [COMMENT '数据库注释'];

选中 ¶

USE `库名`;

删除 ¶

DROP DATABASE [IF EXISTS] `库名`;

查看 ¶

# 查看所有数据库
SHOW DATABASES;

# 查看创建语句
SHOW CREATE DATABASE `库名`;

# 查看当前使用的数据库
SELECT DATABASE();

修改 ¶

ALTER DATABASE `库名` CHARSET 字符集 COLLATE 排序规则;

表 ¶

创建 ¶

CREATE TABLE `表名` (
    `列名` 数据类型 [约束] [AUTO_INCREMENT] [COMMENT '列注释'] [CHARSET 字符集] [COLLATE 排序规则],
    ...
) [ENGINE = 引擎] [CHARSET = 字符集] [COLLATE = 排序规则] [COMMENT = '表注释'];

删除 ¶

# 删除表
DROP TABLE [IF EXISTS] `表名`;

# 清空数据
TRUNCATE TABLE `表名`;

修改 ¶

# 修改表属性
ALTER TABLE `表名` 
  [ENGINE = 引擎] 
  [CHARSET = 字符集] 
  [COLLATE = 排序规则] 
  [COMMENT = '表注释'];

# 表重命名
ALTER TABLE `表名` RENAME TO `新表名`;

复制 ¶

CREATE TABLE `新表名` AS SELECT * FROM `旧表名`;

查看 ¶

# 查看所有表
SHOW TABLES;

# 查看表概要
DESC|DESCRIBE `表名`;

# 查看表创建语句
SHOW CREATE TABLE `表名`;

# 查看表状态
SHOW TABLE STATUS FROM `库名` WHERE NAME='表名';

列 ¶

添加 ¶

ALTER TABLE `表名` ADD COLUMN `字段名` 数据类型 [约束] [AFTER 位置] ...;

删除 ¶

ALTER TABLE `表名` DROP COLUMN `列名`;

修改 ¶

ALTER TABLE `表名` MODIFY COLUMN `列名` 数据类型 [约束] ...;

更改 ¶

ALTER TABLE `表名` CHANGE COLUMN `旧列名` `新列名` 数据类型 [约束] ...;

约束 ¶

注意：非空和默认值两个约束类型只能在列中进行操作。

添加 ¶

# 主键
ALTER TABLE `表名` ADD CONSTRAINT `主键名` PRIMARY KEY (`列名`);

# 外键
ALTER TABLE `表名` ADD CONSTRAINT `外键名` FOREIGN KEY (`列名`) REFERENCES `参考表`(`参考列`);

# 唯一
ALTER TABLE `表名` ADD CONSTRAINT `唯一约束名` UNIQUE (`列名`);

# 检查
ALTER TABLE `表名` ADD CONSTRAINT `检查约束名` CHECK (条件);

删除 ¶

# 主键
ALTER TABLE `表名` DROP PRIMARY KEY;

# 外键
ALTER TABLE `表名` DROP FOREIGN KEY `外键名`;

# 唯一
ALTER TABLE `表名` DROP UNIQUE `唯一约束名`;

# 检查
ALTER TABLE `表名` DROP CHECK `检查约束名`;

DQL¶

单表查询 ¶

基本 ¶

SELECT *|`列1`, `列2`, ... FROM `表名`;

条件 ¶

​WHERE​ 用于过滤行数据。

SELECT `列1`, `列2` FROM `表名` WHERE 条件;

排序 ¶

SELECT `列1`, `列2` FROM `表名` ORDER BY `列1` [ASC|DESC];

分组 ¶

SELECT `列1`, 聚合函数(`列2`) FROM `表名` GROUP BY `列1` [HAVING 条件] [WITH ROLLUP];

​HAVING​用于过滤分组数据，一般与聚合函数搭配。

​WITH ROLLUP​用于在结果中添加小计行。

分页 ¶

SELECT `列1`, `列2` FROM `表名` LIMIT N;

去重 ¶

SELECT DISTINCT `列1` FROM `表名`;

连接查询 ¶

内连接 ¶

只返回两个表中都匹配的记录

# 显式
SELECT `列1`, `列2` FROM `表1` INNER JOIN `表2` ON 表1.列 = 表2.列;

# 隐式
SELECT `列1`, `列2` FROM `表1`, `表2` WHERE 表1.列 = 表2.列;

​USING​：可以简化 ON​，用于连接相同的列名。

左连接 ¶

返回左表中的所有记录，以及右表中匹配的记录。如果右表没有匹配记录，结果为 NULL​。

SELECT 列1, 列2 FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列;

右连接 ¶

返回右表中的所有记录，以及左表中匹配的记录。如果左表没有匹配记录，结果为 NULL​。

SELECT 列1, 列2 FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列;

全外连接 ¶

MySQL 不支持 全外连接（FULL OUTER JOIN​），但是可以通过使用 UNION​ 来模拟它。

SELECT 列1, 列2 FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列
UNION
SELECT 列1, 列2 FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列;

交叉连接 ¶

返回两个表的笛卡尔积。即，对于每一行左表的记录，它将与右表的每一行记录进行组合。因此，返回的记录数是左表记录数与右表记录数的乘积。

SELECT 列1, 列2, ...
FROM 表1
CROSS JOIN 表2;   

自然连接 ¶

自动连接两个表中所有相同名称的列。

SELECT 列1, 列2, ...
FROM 表1
NATURAL JOIN 表2;   

自连接 ¶

一个表与其自身进行连接。可以通过别名来区分同一表的不同实例。

SELECT 列1, 列2, ...
FROM 表1 AS 别名1
INNER JOIN 表1 AS 别名2
ON 别名1.列 = 别名2.列;

子查询 ¶

单行子查询 ¶

SELECT 列1 FROM 表名 WHERE 列2 = (SELECT 列 FROM 另一表 WHERE 条件);

多行子查询 ¶

SELECT 列1 FROM 表名 WHERE 列2 IN (SELECT 列 FROM 另一表 WHERE 条件);

关联子查询 ¶

SELECT 列1, 列2 FROM 表1 WHERE 列3 = (SELECT 列3 FROM 表2 WHERE 表1.列 = 表2.列);

集合查询 ¶

合并去重 ¶

SELECT 列1 FROM 表1
UNION
SELECT 列1 FROM 表2;

合并不去重 ¶

SELECT 列1 FROM 表1
UNION ALL
SELECT 列1 FROM 表2;

其他查询 ¶

CASE 查询 ¶

# 简单CASE
SELECT 列1, CASE 列2
    WHEN 值1 THEN '结果1'
    WHEN 值2 THEN '结果2'
    ELSE '默认结果'
END FROM 表名;

# 搜索CASE
SELECT 列1, CASE
    WHEN 条件1 THEN '结果1'
    WHEN 条件2 THEN '结果2'
    ELSE '默认结果'
END FROM 表名;

NULL 处理 ¶

• ​​IS NULL​​：检查值是否为 NULL​。

SELECT 列1 FROM 表名 WHERE 列2 IS NULL;

• ​​IS NOT NULL​​：检查值是否不为 NULL​。

SELECT 列1 FROM 表名 WHERE 列2 IS NOT NULL;

• ​​COALESCE​​：返回第一个非 NULL​ 的值。

SELECT COALESCE(列1, 列2, '默认值') FROM 表名;

• ​​IFNULL​​：如果表达式为 NULL​，则返回默认值。

SELECT IFNULL(列1, '默认值') FROM 表名;

DML¶

插入 ¶

# 单条
INSERT INTO `表名` (`列名1`, `列名2`, ...) VALUE (值1, 值2, ...);

# 多条
INSERT INTO `表名` (`列名1`, `列名2`, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

# 从另一表插入数据
INSERT INTO `表名` (`列名1`, `列名2`, ...)
SELECT `列名1`, `列名2`, ... FROM `另一表` WHERE 条件;

更新 ¶

UPDATE `表名` SET `列名1` = 新值1[, `列名2` = 新值2] [WHERE 条件];

# 根据查询结果更新
UPDATE `表名` SET `列名` = (SELECT 值 FROM `另一表` WHERE 条件) WHERE 条件;

删除 ¶

DELETE FROM `表名` [WHERE 条件];

合并 ¶

​MERGE​ 语句在一些数据库系统中用于同时执行 INSERT​、UPDATE​ 和 DELETE​ 操作。虽然 MySQL 在 8.0 版本之前并没有直接支持 MERGE​，但可以通过 INSERT ... ON DUPLICATE KEY UPDATE​ 或使用 REPLACE​ 语句模拟。

INSERT INTO `表名` (`列名1`, `列名2`, ...) 
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE `列名1` = 新值1, `列名2` = 新值2;

替换 ¶

​REPLACE​ 是一种特殊的 INSERT​ 操作，当插入的数据已存在时，会首先删除已存在的行，然后再插入新数据。通常用于具有主键或唯一键的表。

REPLACE INTO `表名` (`列名1`, `列名2`, ...) 
VALUES (值1, 值2, ...);

DCL¶

用户 ¶

创建 ¶

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

删除 ¶

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

修改 ¶

# 5.7.6 以上版本
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';

# 5.7.5 及以下
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('新密码');

# 修改当前连接用户密码
SET PASSWORD = PASSWORD('新密码');

# 命令行修改密码
mysqladmin -u 用户名 -p'旧密码' password '新密码'

查看 ¶

# 返回当前客户端连接的用户名
SELECT USER();

# 返回服务器验证的当前用户
SELECT CURRENT_USER();

权限 ¶

查看 ¶

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

授权 ¶

GRANT 权限类型 ON '库.表' TO '用户名'@'主机' [WITH GRANT OPTION];

​WITH GRANT OPTION​：可以允许该用户授权给其他用户。

撤销 ¶

REVOKE 权限类型 ON '库.表' FROM '用户名@主机名';

刷新 ¶

FLUSH PRIVILEGES;

DA¶

备份 ¶

# 单数据库
mysqldump -u 用户名 -p 库名 > 备份文件.sql

# 多数据库
mysqldump -u 用户名 -p --databases 库1 库2 > 多库备份.sql

# 所有数据库
mysqldump -u 用户名 -p --all-databases > 全库备份.sql

# 指定表
mysqldump -u 用户名 -p 库名 表1 表2 > 表备份.sql

# 压缩备份
mysqldump -u 用户名 -p 库名 | gzip > 压缩备份.sql.gz

恢复 ¶

# 单/多库恢复
mysql -u 用户名 -p [库名] < 备份文件.sql

# 压缩恢复
gunzip < 压缩备份.sql.gz | mysql -u 用户名 -p 库名

一般多库恢复不需要指定库名。

‍