MySQL关于库的操作-优快云博客

1. 库的操作

1.1 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification ...]

参数说明：

IF NOT EXISTS：可选项，如果要创建的数据库已存在，则不会报错，避免执行中断。
CHARACTER SET：指定数据库的字符集，决定了可以存储哪些字符（如中文、英文等）。
COLLATE：指定字符集的校验规则，决定了字符如何比较和排序（如是否区分大小写）。

1.2 创建数据库案例

案例1：创建默认数据库

create database db1;

说明：未指定字符集和校验规则时，MySQL使用默认配置（通常是utf8字符集和utf8_general_ci校验规则）。

案例2：指定字符集

create database db2 charset=utf8;

说明：明确指定使用utf8字符集，适用于需要存储中文等多语言字符的场景。

案例3：指定字符集和校验规则

create database db3 charset=utf8 collate utf8_general_ci;

说明：既指定字符集为utf8，又指定校验规则为utf8_general_ci（不区分大小写）。

1.3 字符集和校验规则

1.3.1 查看系统默认字符集和校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

作用：查看当前数据库的默认字符集和校验规则。

1.3.2 查看数据库支持的字符集

show charset;

作用：列出MySQL支持的所有字符集，如utf8、gbk、latin1等。
说明：字符集决定了可以存储的字符范围，例如utf8支持中文，而latin1不支持。

1.3.3 查看数据库支持的校验规则

show collation;

作用：列出所有可用的校验规则。
说明：每个字符集对应多个校验规则，如utf8_general_ci（不区分大小写）、utf8_bin（二进制比较，区分大小写）。

1.3.4 校验规则对数据库的影响

不区分大小写的场景（utf8_general_ci）：

create database test1 collate utf8_general_ci;
use test1;
create table person(name varchar(20));
insert into person values('a'), ('A'), ('b'), ('B');

查询结果：

select * from person where name='a';
-- 结果返回 'a' 和 'A'，因为不区分大小写。

select * from person order by name;
-- 排序结果：a, A, b, B（不区分大小写时，a和A被视为相同）。

区分大小写的场景（utf8_bin）：

create database test2 collate utf8_bin;
use test2;
create table person(name varchar(20));
insert into person values('a'), ('A'), ('b'), ('B');

查询结果：

select * from person where name='a';
-- 结果只返回 'a'，因为区分大小写。

select * from person order by name;
-- 排序结果：A, B, a, b（按ASCII码排序，大写字母在小写字母之前）。

实际应用建议：

用户名/邮箱登录校验：建议使用不区分大小写的校验规则，避免用户因大小写输入错误而无法登录。
密码验证：密码应在应用层处理，数据库不直接比较密码原文。
需要精确匹配的场景（如文件路径）：建议使用区分大小写的校验规则。

1.4 操纵数据库

1.4.1 查看数据库

show databases;

作用：列出MySQL服务器中的所有数据库。

1.4.2 显示创建语句

show create database 数据库名;

作用：查看创建指定数据库的SQL语句。
说明：

MySQL 建议我们关键字使用大写，但是不是必须的。

数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。

/*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

1.4.3 修改数据库

语法：

ALTER DATABASE db_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name;

示例：

alter database mybase charset=gbk;

说明：将mytest数据库的字符集修改为gbk。
注意：修改字符集只会影响之后创建的表，不会改变已有表的字符集。

1.4.4 数据库删除

语法：

DROP DATABASE [IF EXISTS] db_name;

示例：

drop database if exists test1;

执行结果：

数据库从show databases列表中消失。
数据库对应的文件夹被删除。
该数据库中的所有表和数据都会被永久删除（级联删除）。

1.4.5 ubuntu下查看MySQL创建的数据库和表的文件信息

MySQL存储所有数据库文件的根目录

mysql> select @@datadir;

or

mysql> show variables like 'datadir';

通过命令行查看文件结构

# 进入MySQL数据目录
sudo ls -l /var/lib/mysql/

# 查看特定数据库的文件（假设数据库名为mydb）
sudo ls -l /var/lib/mysql/mydb/

MySQL根据存储引擎的不同，会生成不同类型的物理文件

文件扩展名	存储引擎	作用
.frm	所有引擎	存储表的结构定义（列名、数据类型、索引等）
.ibd	InnoDB	存储表的数据和索引（每个表独立表空间）
.MYD	MyISAM	存储表的数据（MY Data）
.MYI	MyISAM	存储表的索引（MY Index）
ibdata1	InnoDB	系统表空间文件，存储共享的系统表和数据
ib_logfile0/1	InnoDB	事务日志文件（redo log）

重要警告：

生产环境禁止随意删除数据库，操作前务必确认数据库名称。
建议定期备份重要数据库。

1.4.6 备份和恢复

1.4.6.1 备份

语法：

mysqldump -P3306 -uroot -p密码 -B 数据库名 > 备份文件路径

示例：

mysqldump -P3306 -uroot -p123456 -B mytest > D:/mytest.sql

test2_backup.sql 把我们整个创建数据库，建表，导入数据的语句都装载这个文件中:

说明：mysqldump是MySQL自带的备份工具，生成的SQL文件包含完整的建库、建表和插入数据的语句。

1.4.6.2 恢复

语法：

source 备份文件路径;

示例：

source D:/mysql-5.7.22/mytest.sql;

说明：需要在MySQL命令行中执行，会逐条执行备份文件中的SQL语句。

1.4.6.3 注意事项

备份单张或多张表：

mysqldump -uroot -p 数据库名 表名1 表名2 > 备份文件.sql

备份多个数据库：

mysqldump -uroot -p -B 数据库1 数据库2 > 备份文件.sql

不带-B参数的备份与恢复：

备份时：mysqldump -uroot -p 数据库名 > 备份.sql。
恢复时需要三步：

create database 数据库名;  -- 先创建空数据库
use 数据库名;              -- 切换到该数据库
source 备份.sql;           -- 执行恢复

最佳实践：

show processlist;

定期备份重要数据库。
备份文件应存储在安全的位置，最好与数据库服务器分离。
恢复前先确认目标数据库为空或做好数据保护。

1.4.7 查看连接情况

语法：

show processlist;

示例输出：

字段说明：

Id：连接标识符。
User：连接的用户。
Host：连接的主机。
db：当前连接的数据库。
Command：当前执行的命令类型（Sleep表示空闲）。
Time：命令执行时间。
State：当前状态。
Info：正在执行的SQL语句。

实际应用：

性能排查：当数据库响应慢时，查看是否有长时间运行的查询。
安全监控：检查是否有异常IP连接数据库。
连接数管理：查看当前连接数是否过多。

安全提示：

如果发现不是自己登录的陌生连接，可能是数据库被入侵的迹象，应立即采取措施（如修改密码、限制IP访问等）。