跳到主要内容

MySQL 数据库操作

本章将详细介绍 MySQL 中数据库的创建、查看、选择和删除操作。

创建数据库​

基本语法​

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

参数说明：

  • IF NOT EXISTS：可选，如果数据库已存在则不报错
  • CHARACTER SET：指定数据库默认字符集
  • COLLATE：指定数据库默认排序规则

基本创建​

-- 创建简单数据库
CREATE DATABASE mydb;

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS mydb;

指定字符集和排序规则​

-- 创建使用 UTF-8 字符集的数据库
CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 查看支持的字符集
SHOW CHARACTER SET;

-- 查看支持的排序规则
SHOW COLLATION;

常用字符集和排序规则：

字符集排序规则说明
utf8mb4utf8mb4_unicode_ci推荐，支持完整 Unicode（包括 Emoji）
utf8mb4utf8mb4_general_ci旧版，性能略高但排序不够准确
utf8mb4utf8mb4_0900_ai_ciMySQL 8.0 默认，基于 UCA 9.0.0
latin1latin1_swedish_ci默认西欧字符集
注意

MySQL 中的 utf8 实际上是 utf8mb3，只支持 3 字节的 UTF-8 字符。如果需要存储 Emoji 等 4 字节字符，必须使用 utf8mb4。

查看创建语句​

SHOW CREATE DATABASE mydb;

输出示例：

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

查看数据库​

查看所有数据库​

SHOW DATABASES;

输出示例：

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

模糊匹配查询​

-- 查看名称包含 "my" 的数据库
SHOW DATABASES LIKE '%my%';

-- 查看名称以 "db" 开头的数据库
SHOW DATABASES LIKE 'db%';

系统数据库说明​

数据库说明
mysql存储用户账号和权限信息
information_schema存储数据库元数据（表、列、索引等）
performance_schema存储性能监控数据
sys提供便于理解的性能视图

选择数据库​

USE 语句​

在对表进行操作之前，需要先选择（使用）一个数据库：

USE mydb;

执行后会显示 Database changed，表示已切换到目标数据库。

查看当前数据库​

-- 方式一：使用函数
SELECT DATABASE();

-- 方式二：查看状态
STATUS;

在 SQL 语句中指定数据库​

也可以在 SQL 语句中直接指定数据库，无需切换：

-- 查询 mydb 数据库中的 users 表
SELECT * FROM mydb.users;

-- 在 mydb 数据库中创建表
CREATE TABLE mydb.users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

修改数据库​

修改字符集和排序规则​

ALTER DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

说明：

  • 修改数据库的字符集只会影响新创建的表
  • 已存在的表需要单独修改

查看修改后的配置​

SHOW CREATE DATABASE mydb;

删除数据库​

基本语法​

DROP DATABASE [IF EXISTS] 数据库名;

删除示例​

-- 删除数据库
DROP DATABASE mydb;

-- 删除数据库（如果存在）
DROP DATABASE IF EXISTS mydb;
危险操作

DROP DATABASE 会删除数据库中的所有表和数据，且不可恢复。在生产环境中执行此操作前，务必：

  1. 确认要删除的数据库名称
  2. 确保已有备份
  3. 在非高峰期执行

数据库命名规则​

推荐的命名规范​

  1. 使用小写字母：避免不同操作系统的大小写敏感问题
  2. 使用下划线分隔：my_database 而不是 myDatabase
  3. 避免使用保留字：不要使用 select、order 等 MySQL 保留字
  4. 长度适中：建议不超过 64 个字符

特殊字符处理​

如果数据库名包含特殊字符，需要使用反引号包裹：

-- 数据库名包含连字符
CREATE DATABASE `my-db`;

-- 数据库名是保留字
CREATE DATABASE `select`;

大小写敏感问题​

数据库名的大小写敏感性取决于操作系统：

操作系统大小写敏感
Linux敏感（MyDB 和 mydb 是不同的数据库）
Windows不敏感
macOS取决于文件系统

可以通过 lower_case_table_names 系统变量查看或设置：

-- 查看当前设置
SHOW VARIABLES LIKE 'lower_case_table_names';

-- 值说明：
-- 0：区分大小写（Linux 默认）
-- 1：表名存储为小写，查询不区分大小写（Windows 默认）
-- 2：表名按原样存储，查询不区分大小写（macOS 默认）

实用技巧​

批量创建数据库脚本​

-- 创建开发、测试、生产环境数据库
CREATE DATABASE IF NOT EXISTS app_dev CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS app_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS app_prod CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

检查数据库是否存在​

-- 方式一：查询 information_schema
SELECT SCHEMA_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'mydb';

-- 方式二：使用 SHOW DATABASES
SHOW DATABASES LIKE 'mydb';

导出数据库创建脚本​

# 使用 mysqldump 只导出数据库结构
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 导出整个数据库
mysqldump -u root -p mydb > mydb_backup.sql

常见问题​

1. 数据库已存在错误​

-- 错误：数据库已存在
CREATE DATABASE mydb;
-- ERROR 1007 (HY000): Can't create database 'mydb'; database exists

-- 解决方案：使用 IF NOT EXISTS
CREATE DATABASE IF NOT EXISTS mydb;

2. 权限不足​

-- 错误：权限不足
CREATE DATABASE mydb;
-- ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mydb'

-- 解决方案：使用 root 用户授权
GRANT CREATE ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

3. 字符集问题​

-- 查看数据库字符集
SELECT 
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydb';

小结​

本章我们学习了：

  1. 创建数据库：CREATE DATABASE 语法和字符集设置
  2. 查看数据库：SHOW DATABASES 查看所有数据库
  3. 选择数据库：USE 语句切换当前数据库
  4. 修改数据库：ALTER DATABASE 修改字符集
  5. 删除数据库：DROP DATABASE 删除数据库
  6. 命名规范：数据库命名的最佳实践

练习​

  1. 创建一个使用 utf8mb4 字符集的数据库
  2. 查看 MySQL 中所有支持的字符集
  3. 练习数据库的切换操作
  4. 创建一个测试数据库，然后删除它

参考资源​