数据库概述

MySQL安装与配置

数据库基础操作

数据类型

表结构操作

数据插入更新删除

基础查询

多表查询基础

高级查询

多表查询进阶

索引与优化

事务与锁

存储过程与函数基础

触发器与事件

存储过程与函数进阶

视图

用户权限管理

数据备份与恢复

性能优化

数据库基础操作

一、创建数据库

1.1 基本语法

1.1.1 CREATE DATABASE

语法格式：

CREATE DATABASE [IF NOT EXISTS] 数据库名
[DEFAULT] CHARACTER SET 字符集名
[DEFAULT] COLLATE 校对规则名;

参数说明：

参数	说明
IF NOT EXISTS	如果数据库不存在则创建
CHARACTER SET	指定字符集
COLLATE	指定校对规则

1.1.2 创建示例

创建简单数据库：

CREATE DATABASE school;

创建指定字符集的数据库：

CREATE DATABASE school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

创建避免重复：

CREATE DATABASE IF NOT EXISTS school;

1.2 字符集与校对规则

1.2.1 常用字符集

查看支持的字符集：

SHOW CHARACTER SET;

常用字符集：

字符集	说明	每个字符最大长度
utf8	UTF-8 Unicode	3字节
utf8mb4	UTF-8 Unicode	4字节
latin1	cp1252西欧	1字节
gbk	GBK简体中文	2字节
gb2312	GB2312简体中文	2字节

推荐使用utf8mb4：

支持完整的UTF-8字符
支持emoji表情
兼容utf8

1.2.2 校对规则

查看校对规则：

SHOW COLLATION;

常用校对规则：

校对规则	说明
utf8mb4_general_ci	不区分大小写，通用
utf8mb4_unicode_ci	不区分大小写，支持Unicode
utf8mb4_bin	区分大小写，二进制比较
utf8mb4_0900_ai_ci	MySQL 8.0默认，不区分重音

校对规则后缀说明：

后缀	说明
_ci	Case Insensitive，不区分大小写
_cs	Case Sensitive，区分大小写
_bin	Binary，二进制比较
_ai	Accent Insensitive，不区分重音
_as	Accent Sensitive，区分重音

1.2.3 创建指定字符集的数据库

CREATE DATABASE school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

1.3 查看数据库创建语句

查看创建语句：

SHOW CREATE DATABASE school;

输出示例：

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------+

二、查看数据库

2.1 查看所有数据库

语法格式：

SHOW DATABASES;

输出示例：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

2.2 系统数据库说明

2.2.1 information_schema

作用：存储数据库元数据

包含信息：

数据库信息
表信息
列信息
索引信息
权限信息

常用查询：

-- 查看所有数据库
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

-- 查看所有表
SELECT TABLE_NAME, TABLE_TYPE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'school';

-- 查看表结构
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'school' AND TABLE_NAME = 'students';

2.2.2 mysql

作用：存储用户权限和系统信息

主要表：

user：用户账号和全局权限
db：数据库级权限
tables_priv：表级权限
columns_priv：列级权限

2.2.3 performance_schema

作用：存储性能监控数据

监控内容：

语句执行统计
锁等待信息
内存使用情况
I/O操作统计

2.2.4 sys

作用：提供易读的性能视图

基于performance_schema：

视图简化查询
格式化输出
易于理解

2.3 模糊查询数据库

使用LIKE：

SHOW DATABASES LIKE 'sch%';

使用WHERE：

SELECT SCHEMA_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME LIKE '%school%';

三、选择数据库

3.1 USE命令

语法格式：

USE 数据库名;

示例：

USE school;

输出：

Database changed

3.2 查看当前数据库

使用DATABASE()函数：

SELECT DATABASE();

输出：

+------------+
| DATABASE() |
+------------+
| school     |
+------------+

3.3 在命令行指定数据库

连接时指定：

mysql -u root -p school

执行SQL时指定：

mysql -u root -p -e "SELECT DATABASE()" school

四、删除数据库

4.1 DROP DATABASE

语法格式：

DROP DATABASE [IF EXISTS] 数据库名;

参数说明：

参数	说明
IF EXISTS	如果数据库存在则删除

删除示例：

DROP DATABASE school;

避免错误：

DROP DATABASE IF EXISTS school;

4.2 删除注意事项

⚠️ 重要警告：

删除数据库将删除所有表和数据
操作不可逆
建议先备份再删除

删除前检查：

-- 查看数据库大小
SELECT 
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'school'
GROUP BY table_schema;

-- 查看数据库中的表
SHOW TABLES FROM school;

五、修改数据库

5.1 ALTER DATABASE

语法格式：

ALTER DATABASE 数据库名
[DEFAULT] CHARACTER SET 字符集名
[DEFAULT] COLLATE 校对规则名;

修改字符集：

ALTER DATABASE school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

5.2 查看修改结果

SHOW CREATE DATABASE school;

六、数据库备份与恢复

6.1 使用mysqldump备份

6.1.1 备份单个数据库

语法格式：

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

备份示例：

mysqldump -u root -p school > school_backup.sql

备份多个数据库：

mysqldump -u root -p --databases school test > databases_backup.sql

备份所有数据库：

mysqldump -u root -p --all-databases > all_databases_backup.sql

6.1.2 备份选项

常用选项：

选项	说明
--single-transaction	InnoDB一致性备份
--routines	备份存储过程和函数
--triggers	备份触发器
--events	备份事件
--hex-blob	十六进制导出二进制字段

完整备份示例：

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  school > school_full_backup.sql

6.2 恢复数据库

6.2.1 使用mysql命令恢复

语法格式：

mysql -u 用户名 -p 数据库名 < 备份文件.sql

恢复示例：

# 先创建数据库
mysql -u root -p -e "CREATE DATABASE school"

# 恢复数据
mysql -u root -p school < school_backup.sql

6.2.2 使用SOURCE命令恢复

在MySQL命令行中：

-- 创建数据库
CREATE DATABASE school;

-- 选择数据库
USE school;

-- 恢复数据
SOURCE /path/to/school_backup.sql;

6.3 物理备份

6.3.1 直接复制数据目录

停止MySQL服务：

systemctl stop mysql

复制数据目录：

cp -r /var/lib/mysql /backup/mysql_backup_$(date +%Y%m%d)

启动MySQL服务：

systemctl start mysql

6.3.2 恢复物理备份

停止MySQL服务：

systemctl stop mysql

恢复数据：

rm -rf /var/lib/mysql
cp -r /backup/mysql_backup_20240101 /var/lib/mysql

修改权限：

chown -R mysql:mysql /var/lib/mysql

启动MySQL服务：

systemctl start mysql

七、存储引擎

7.1 查看存储引擎

查看支持的引擎：

SHOW ENGINES;

输出示例：

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

7.2 InnoDB引擎

7.2.1 特点

优势：

支持事务（ACID）
行级锁定
外键约束
崩溃恢复
多版本并发控制（MVCC）

适用场景：

高并发应用
需要事务支持
数据完整性要求高

7.2.2 存储结构

表空间文件：

.ibd文件：独立表空间
ibdata1：系统表空间

数据文件：

.frm文件：表结构定义（MySQL 8.0前）
.ibd文件：数据和索引

7.3 MyISAM引擎

7.3.1 特点

优势：

读取速度快
全文索引
表级锁定
存储空间小

劣势：

不支持事务
不支持外键
表级锁定并发性差

适用场景：

读密集型应用
不需要事务
全文搜索

7.3.2 存储结构

数据文件：

.frm文件：表结构定义
.MYD文件：数据文件
.MYI文件：索引文件

7.4 MEMORY引擎

7.4.1 特点

优势：

数据存储在内存
极快访问速度
哈希索引

劣势：

数据易丢失
表大小受限
不支持事务

适用场景：

临时表
缓存数据
查询中间结果

7.5 引擎选择

对比表：

特性	InnoDB	MyISAM	MEMORY
事务	✅	❌	❌
外键	✅	❌	❌
锁定	行级	表级	表级
全文索引	✅（5.6+）	✅	❌
崩溃恢复	✅	❌	❌
存储限制	64TB	256TB	内存大小

选择建议：

默认选择InnoDB
读密集型可选MyISAM
临时数据选MEMORY

八、本章小结

8.1 核心要点

✅ CREATE DATABASE创建数据库 ✅ SHOW DATABASES查看数据库 ✅ USE选择数据库 ✅ DROP DATABASE删除数据库 ✅ mysqldump备份数据库 ✅ InnoDB是默认存储引擎

8.2 验证清单

完成本章学习后，请确认您能够：

创建指定字符集的数据库
查看和选择数据库
删除数据库并理解风险
备份和恢复数据库
了解常用存储引擎特点

上一章：MySQL安装与配置

下一章：数据类型