MySQL新手入门：库表操作从入门到精通，避坑指南！

内容摘要：MySQL新手入门：库表操作从入门到精通，避坑指南！,

很多刚接触数据库的朋友，最开始都会遇到各种各样的问题，比如如何创建数据库，如何创建表，各种数据类型如何选择等等。今天我们就来详细聊聊 初识 MySQL —— 库和表的操作，帮你打好 MySQL 基础。

数据库（Database）的操作

数据库是存储和管理数据的仓库。首先，我们需要了解如何创建、查看、修改和删除数据库。

创建数据库

使用 CREATE DATABASE 语句创建数据库。

CREATE DATABASE IF NOT EXISTS my_database;
-- 创建名为 my_database 的数据库，如果不存在则创建

IF NOT EXISTS 避免了数据库已存在时报错，提高了脚本的兼容性。这个技巧在自动化部署脚本里非常实用，比如使用 Ansible 部署 MySQL 时，可以保证脚本的幂等性。

查看数据库

使用 SHOW DATABASES 语句查看所有数据库。

SHOW DATABASES;
-- 显示 MySQL 服务器上的所有数据库

如果你安装了宝塔面板，也可以在宝塔面板的数据库管理界面直接查看。

选择数据库

使用 USE 语句选择要使用的数据库。

USE my_database;
-- 选择 my_database 数据库作为当前数据库

修改数据库

修改数据库通常是修改其字符集和排序规则。使用 ALTER DATABASE 语句。

ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改 my_database 数据库的字符集为 utf8mb4，排序规则为 utf8mb4_unicode_ci

选择合适的字符集非常重要，特别是涉及到存储中文等多字节字符时。utf8mb4 是 utf8 的超集，支持存储更多的字符。

删除数据库

使用 DROP DATABASE 语句删除数据库。请谨慎操作，数据删除后无法恢复。

DROP DATABASE IF EXISTS my_database;
-- 删除名为 my_database 的数据库，如果存在则删除

数据表（Table）的操作

数据表是数据库中存储数据的基本单元。接下来，我们将学习如何创建、查看、修改和删除数据表。

创建数据表

使用 CREATE TABLE 语句创建数据表。你需要指定表名和列名，以及每列的数据类型。

CREATE TABLE IF NOT EXISTS users (
 id INT PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(50) NOT NULL,
 email VARCHAR(100) UNIQUE,
 password VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建名为 users 的数据表，包含 id、username、email、password 和 created_at 列

• INT：整数类型。
• VARCHAR(n)：可变长度字符串类型，n 指定最大长度。
• PRIMARY KEY：主键约束，用于唯一标识表中的每一行。
• AUTO_INCREMENT：自增长属性，通常用于主键。
• NOT NULL：非空约束，确保列的值不能为空。
• UNIQUE：唯一约束，确保列的值在表中是唯一的。
• TIMESTAMP：时间戳类型，用于存储日期和时间。
• DEFAULT CURRENT_TIMESTAMP：默认值为当前时间戳。

数据类型选择避坑

• 字符串: VARCHAR 和 TEXT 的区别在于存储长度，VARCHAR 适合存储较短的字符串，例如用户名，TEXT 适合存储较长的文本，例如文章内容。
• 整数: INT，BIGINT， TINYINT 等， 根据实际数值范围选择，避免浪费存储空间。
• 时间: 优先使用 TIMESTAMP ，因为它与时区相关，方便进行跨时区的数据处理。

查看数据表

使用 SHOW TABLES 语句查看当前数据库中的所有数据表。

SHOW TABLES;
-- 显示当前数据库中的所有数据表

使用 DESCRIBE 或 DESC 语句查看数据表的结构。

DESCRIBE users;
-- 显示 users 表的结构，包括列名、数据类型、约束等

修改数据表

使用 ALTER TABLE 语句修改数据表。你可以添加、修改或删除列，以及修改约束。

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 在 users 表中添加名为 phone 的列，数据类型为 VARCHAR(20)

ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
-- 修改 users 表中 email 列的数据类型为 VARCHAR(150)

ALTER TABLE users DROP COLUMN phone;
-- 删除 users 表中的 phone 列

ALTER TABLE users ADD INDEX idx_username (username);
-- 添加username索引，优化查询

修改表结构注意事项

• 修改表结构可能会导致数据丢失，例如删除列。务必谨慎操作，并在生产环境操作前进行备份。
• 添加索引可以提高查询效率，但也会降低写入效率。需要根据实际情况进行权衡。
• 如果表的数据量很大，修改表结构可能会耗费很长时间，甚至导致服务中断。可以考虑使用在线 DDL 工具，例如 pt-online-schema-change。

删除数据表

使用 DROP TABLE 语句删除数据表。请谨慎操作，数据删除后无法恢复。

DROP TABLE IF EXISTS users;
-- 删除名为 users 的数据表，如果存在则删除

实战避坑经验总结

1. 字符集选择：始终使用 utf8mb4 字符集，避免中文乱码问题。
2. 数据类型选择：根据实际需求选择合适的数据类型，避免浪费存储空间，并提高查询效率。
3. 备份：在进行任何修改数据库结构的操作之前，务必进行数据备份。
4. 索引优化：合理使用索引可以提高查询效率，但不要滥用索引。可以使用 EXPLAIN 语句分析 SQL 查询的性能。
5. 慢查询优化：定期检查慢查询日志，优化 SQL 查询语句。
6. 连接数控制：在高并发场景下，需要控制 MySQL 的最大连接数，避免服务器资源耗尽。可以通过调整 max_connections 参数来控制连接数。可以使用 Nginx 作为反向代理，实现负载均衡，缓解 MySQL 的压力。

掌握了 初识 MySQL —— 库和表的操作，你就可以开始你的 MySQL 数据库之旅了。希望这篇文章能够帮助你更好地理解 MySQL 的基本概念和操作，少走弯路。在实际项目中，还会遇到更多复杂的问题，需要不断学习和实践。