目录
一、MySQL库的操作详解
〇、登录MySQL
mysql -h 127.0.0.1 -P 3306 -u root -p-
-h默认为本地（127.0.0.1），-P默认为3306端口。 -
-u为谁登录
一、数据库的创建与字符集设置
1. 创建数据库的语法
CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT CHARACTER SET charset_name]
[DEFAULT COLLATE collation_name];-
关键参数：
-
CHARACTER SET：指定数据库字符集（如utf8,gbk）。 -
COLLATE：指定字符集的校验规则（如utf8_general_ci）。
-
2. 创建数据库示例
-- 创建默认字符集的数据库
CREATE DATABASE db1;
-- 指定字符集为utf8
CREATE DATABASE db2 CHARSET=utf8;
-- 指定字符集和校验规则
CREATE DATABASE db3 CHARSET=utf8 COLLATE utf8_general_ci;
说明：未指定字符集时，默认使用 utf8 字符集和 utf8_general_ci 校验规则。
查看创建出来的文件:
show + databases;
bash下查看MySQL创建的文件
cd /var/lib/mysql
二、字符集与校验规则
1. 查看系统默认设置
-- 查看默认字符集
SHOW VARIABLES LIKE 'character_set_database';
-- 查看默认校验规则
SHOW VARIABLES LIKE 'collation_database';
2. 查看支持的字符集与校验规则
-- 查看所有支持的字符集
SHOW CHARSET;
-- 查看所有支持的校验规则
SHOW COLLATION;3. 校验规则对查询的影响
示例：区分大小写 vs 不区分大小写
-- 创建不区分大小写的数据库
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'
-- 创建区分大小写的数据库
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'create + database +文件名
排序差异
-- test1（不区分大小写排序）
SELECT * FROM person ORDER BY name;
-- 结果：a, A, b, B
-- test2（区分大小写排序）
SELECT * FROM person ORDER BY name;
-- 结果：A, B, a, b三、数据库的日常操作
1. 查看与修改数据库
-- 查看所有数据库
SHOW DATABASES;
-- 显示数据库创建语句
SHOW CREATE DATABASE mytest;
-- 修改数据库字符集
ALTER DATABASE mytest CHARSET=gbk;2、 查看当前所在的库
select database();
3. 删除数据库
DROP DATABASE IF EXISTS db_name;
注意：删除后数据库及所有表数据将被永久删除！
4、修改
alter database db1 charset = gbk collate gbk_chinese_ci;alter database+需要修改的库+需要修改的内容
5、查看当前创建库命令
show+create+database+库名称show+create+database+库名称
四、备份与恢复
1. 备份数据库
# 备份整个数据库
mysqldump -u root -p -B mytest > D:/mytest.sql
# 备份单张表
mysqldump -u root -p mytest table1 table2 > D:/mytest_table.sql2. 恢复数据库
-- 登录MySQL后执行
SOURCE D:/mytest.sql;注意事项：
-
若备份时未使用
-B参数，需先手动创建空数据库再恢复。 -
备份文件本质是SQL脚本，包含建库、建表及插入数据的语句。
五、使用库
use + 创建的文件（create出来的对象);
system clear = bash中的clear；
六、高级操作：查看连接状态及当前所在的库
SHOW PROCESSLIST;输出示例：
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | test | Sleep | 100 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+作用：监控当前数据库连接，识别异常请求（如未知用户连接）。
七、最佳实践与注意事项
-
字符集选择：推荐使用
utf8mb4支持更多字符（如Emoji）。 -
备份策略：
-
生产环境每日定时备份。
-
备份文件加密存储，防止数据泄露。
-
-
避免误删：执行
DROP DATABASE前务必确认数据库名。 -
权限管理：限制非管理员用户的
CREATE/DROP权限。
通过本文，您已掌握MySQL库的完整操作流程。从创建到备份恢复，每一步都至关重要。建议结合实践加深理解，例如在本地环境中模拟不同校验规则的查询场景，或尝试备份恢复真实项目数据。
二、数据库表的操作
一、表的创建与结构设计
1. 创建表的语法
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype [约束],
column2 datatype [约束],
...
[PRIMARY KEY (列名)],
[FOREIGN KEY (列名) REFERENCES 其他表(列名)]
) ENGINE=存储引擎 DEFAULT CHARSET=字符集;2. 核心参数说明
-
数据类型：如
INT,VARCHAR(n),DATE,TEXT。 -
约束：
-
PRIMARY KEY：主键（唯一且非空）。 -
UNIQUE：唯一约束。 -
NOT NULL：非空约束。 -
DEFAULT：默认值。 -
AUTO_INCREMENT：自增（常用于主键）。
-
-
存储引擎：如
InnoDB（默认）、MyISAM。 -
字符集：如
utf8mb4（推荐支持Emoji）。
3. 创建表示例
-- 创建学生表（包含主键、自增、默认值）
CREATE TABLE student (
id INT ,
name VARCHAR(50) comment '用户名',
possword char(32) comment '用户密码',
birthday date comment '用户生日'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;4、表的查看
show tables;
5、查看表的详细信息
desc 表名;
6、创建表的详细信息
show create table +表名
去掉多余字符
show create table +表名+\G
7、表名修改
alter table 原始名 rename to 新名称 
二、表结构修改
1. 添加列
ALTER TABLE student ADD COLUMN email VARCHAR(100) AFTER name;
2. 修改列
-- 修改列数据类型
ALTER TABLE student MODIFY COLUMN name VARCHAR(60);
-- 重命名列
ALTER TABLE student CHANGE COLUMN emil data DATE; 
3. 删除列
ALTER TABLE student DROP COLUMN email; 
4. 添加约束
-- 添加唯一约束
ALTER TABLE student ADD UNIQUE (email);
-- 添加外键约束
ALTER TABLE student ADD FOREIGN KEY (class_id) REFERENCES class(id);5. 删除表
DROP TABLE IF EXISTS student;三、数据操作（CRUD）
1. 插入数据
-- 插入单条数据
INSERT INTO student (name, gender, birthday)
VALUES ('张三', '男', '2005-03-15');
-- 批量插入数据
INSERT INTO student (name, gender) VALUES
('李四', '女'),
('王五', '男');2. 查询数据
-- 查询所有列
SELECT * FROM student;
-- 条件查询（WHERE）
SELECT name, birthday FROM student WHERE gender = '男';
-- 排序（ORDER BY）
SELECT * FROM student ORDER BY birthday DESC;
-- 分组聚合（GROUP BY）
SELECT gender, COUNT(*) AS total FROM student GROUP BY gender; 
3. 更新数据
UPDATE student SET class_id = 2 WHERE id = 3;4. 删除数据
-- 删除单条记录
DELETE FROM student WHERE id = 5;
-- 清空表（不可恢复！）
TRUNCATE TABLE student;四、索引管理
1. 创建索引
-- 单列索引
CREATE INDEX idx_name ON student (name);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON student (email);
-- 复合索引
CREATE INDEX idx_gender_birthday ON student (gender, birthday);2. 查看索引
SHOW INDEX FROM student;3. 删除索引
DROP INDEX idx_name ON student;五、表优化与维护
1. 分析表使用情况
-- 查看表状态
SHOW TABLE STATUS LIKE 'student';
-- 分析索引效率
EXPLAIN SELECT * FROM student WHERE name = '张三';2. 优化表碎片
OPTIMIZE TABLE student;3. 修复表（仅MyISAM）
REPAIR TABLE student;六、常见问题与解决方案
1. 插入中文乱码
-
原因：表字符集与客户端不匹配。
-
解决：建表时指定
DEFAULT CHARSET=utf8mb4，连接时设置SET NAMES utf8mb4。
2. 自增主键不连续
-
原因：删除数据或事务回滚导致。
-
解决：使用
ALTER TABLE student AUTO_INCREMENT=新值重置。
3. 外键约束失败
-
原因：插入的数据在关联表中不存在。
-
解决：先确保关联表中有对应数据，或暂时禁用外键检查：
-
SET FOREIGN_KEY_CHECKS = 0; -- 执行操作 SET FOREIGN_KEY_CHECKS = 1;
七、最佳实践
-
命名规范：表名和字段名使用小写+下划线（如
student_info）。 -
适度索引：避免过度索引，优先为高频查询字段和 WHERE 条件列建索引。
-
分区策略：海量数据表可按时间或范围分区。
-
定期备份：结合
mysqldump或工具（如 Percona XtraBackup）进行备份。
通过本文，您已掌握MySQL表的核心操作，从基础设计到高级优化均有覆盖。建议结合项目需求实践，例如设计一个用户管理系统，练习表关联、事务处理等复杂场景。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
