MySQL数据库操作 #

MySQL服务器
├── mysql（系统数据库）
├── information_schema（元数据）
├── performance_schema（性能监控）
├── sys（系统视图）
└── mydb（用户数据库）
    ├── 表（Tables）
    ├── 视图（Views）
    ├── 存储过程（Procedures）
    └── 函数（Functions）

CREATE DATABASE database_name;

-- 示例
CREATE DATABASE mydb;

-- 创建时指定字符集
CREATE DATABASE mydb 
CHARACTER SET utf8mb4;

-- 指定字符集和排序规则
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

-- 如果存在会警告，不会报错
CREATE DATABASE IF NOT EXISTS mydb;
-- Query OK, 1 row affected, 1 warning (0.00 sec)

SHOW DATABASES;

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

-- 查看以my开头的数据库
SHOW DATABASES LIKE 'my%';

+----------------+
| Database (my%) |
+----------------+
| mysql          |
| mydb           |
+----------------+

-- 查看包含test的数据库
SHOW DATABASES LIKE '%test%';

SHOW CREATE DATABASE mydb;

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

-- 查看当前选择的数据库
SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| mydb       |
+------------+

-- 如果未选择数据库
SELECT DATABASE();
-- 结果：NULL

-- 选择数据库
USE mydb;

-- Database changed

-- 后续操作都在该数据库中进行

# 登录时指定数据库
mysql -u root -p mydb

# 登录后切换
mysql> USE mydb;

-- 修改数据库字符集
ALTER DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

SHOW CREATE DATABASE mydb;

-- 字符集已更新

-- 删除数据库
DROP DATABASE mydb;

-- 删除后，数据库中的所有数据都会丢失！

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 如果不存在会警告，不会报错
DROP DATABASE IF EXISTS mydb;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 删除前确认
-- 1. 确保选择了正确的数据库
SELECT DATABASE();

-- 2. 查看数据库中的表
SHOW TABLES FROM mydb;

-- 3. 备份重要数据（如果需要）
-- mysqldump -u root -p mydb > mydb_backup.sql

-- 查看当前数据库的表
USE mydb;
SHOW TABLES;

-- 查看指定数据库的表
SHOW TABLES FROM mydb;

-- 查看数据库状态信息
SHOW STATUS;

-- 查看特定状态
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';

-- 查询所有数据库信息
SELECT 
    schema_name AS database_name,
    default_character_set_name AS charset,
    default_collation_name AS collation
FROM information_schema.schemata;

+--------------------+---------+--------------------+
| database_name      | charset | collation          |
+--------------------+---------+--------------------+
| information_schema | utf8    | utf8_general_ci    |
| mysql              | utf8mb4 | utf8mb4_0900_ai_ci |
| mydb               | utf8mb4 | utf8mb4_unicode_ci |
+--------------------+---------+--------------------+

-- 查询数据库大小
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases mydb test > databases_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 只备份结构（不含数据）
mysqldump -u root -p --no-data mydb > structure.sql

# 只备份数据（不含结构）
mysqldump -u root -p --no-create-info mydb > data.sql

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

# 在MySQL命令行中恢复
mysql> USE mydb;
mysql> SOURCE /path/to/mydb_backup.sql;

# 导出单个表
mysqldump -u root -p mydb users > users_backup.sql

# 导出多个表
mysqldump -u root -p mydb users orders > tables_backup.sql

-- 推荐：小写字母，下划线分隔
CREATE DATABASE ecommerce_db;
CREATE DATABASE blog_system;
CREATE DATABASE user_center;

-- 不推荐
CREATE DATABASE EcommerceDB;    -- 大小写混用
CREATE DATABASE ecommerce-db;   -- 使用连字符
CREATE DATABASE ecommerce;      -- 过于简单

-- 推荐：使用utf8mb4
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 原因：
-- 1. utf8mb4支持完整的UTF-8字符（包括emoji）
-- 2. utf8只支持3字节字符，可能丢失数据

-- 删除前检查
-- 1. 确认数据库名称
SHOW DATABASES LIKE 'mydb';

-- 2. 查看数据库内容
SHOW TABLES FROM mydb;

-- 3. 确认是否需要备份

-- 4. 执行删除
DROP DATABASE IF EXISTS mydb;

-- 错误信息
CREATE DATABASE mydb;
-- ERROR 1007 (HY000): Can't create database 'mydb'; database exists

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

-- 错误信息
USE mydb;
-- ERROR 1049 (42000): Unknown database 'mydb'

-- 解决方案
-- 先创建数据库
CREATE DATABASE mydb;
USE mydb;

-- 错误信息
CREATE DATABASE mydb;
-- ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mydb'

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