【MySQL】2.深入理解MySQL：数据类型、DDL与DML语句全解析

数据类型、DDL（数据定义语言）和DML（数据操纵语言）语句构成了数据管理和操作的核心。从精心选择数据类型以优化存储和查询性能，到运用DDL语句设计和调整数据库结构，再到使用DML语句对数据进行日常的增删改查，每个环节都至关重要。本文将带你深入探索MySQL的这些基础而强大的概念，为你的数据操作之旅提供一份详尽的指南。

一.数据类型

MySQL支持的数据类型很多，每种数据类型都有其特定的使用场景。以下是MySQL支持的一些主要数据类型及其适用场景：

1. 整型（Integer）：
   TINYINT：适用于存储非常小的整数值，如状态标识（0和1）。
   SMALLINT：适用于存储较小的整数值，如较小范围的ID。
   MEDIUMINT：适用于存储中等大小的整数值。
   INT 或 INTEGER：最常用的整数类型，适用于存储一般大小的整数值，如用户ID。
   BIGINT：适用于存储较大的整数值，如大量的计数或ID。

2. 浮点数和双精度（Floating-Point and Double）：
   FLOAT：适用于需要处理小数的数值场景，如价格计算。
   DOUBLE 或 DOUBLE PRECISION：适用于需要更高精度的数值场景，如科学计算。

3. 定点数（Fixed-Point）：
   DECIMAL 或 NUMERIC：适用于需要固定精度的小数值，如货币计算。

4. 字符串类型：
   CHAR：适用于存储短的、定长的字符串，如性别、国家代码。
   VARCHAR：适用于存储可变长度的字符串，如用户名、文章标题。
   TEXT：适用于存储大量文本，如文章内容、评论。

5. 二进制字符串：
   BINARY：适用于存储二进制数据的短字符串。
   VARBINARY：适用于存储可变长度的二进制字符串。
   BLOB：适用于存储二进制大对象，如图片、音频文件。

6. 日期和时间类型：
   DATE：适用于存储日期，如生日、纪念日。
   TIME：适用于存储时间，如预约时间、营业时间。
   DATETIME：适用于存储日期和时间的组合，如事件的开始和结束时间。
   TIMESTAMP：类似于DATETIME，但时间精度到秒，通常用于记录数据的最后修改时间。

7. 枚举（ENUM）：
   适用于存储预定义集合中的一个值，如状态（‘active’, ‘inactive’, ‘pending’）。

8. 集合（SET）：
   适用于存储多个预定义值的组合，如用户权限（‘create’, ‘read’, ‘update’, ‘delete’）。

9. 空间数据类型：
   适用于存储地理空间数据，如地图应用中的点、线、面。

10. JSON类型：
    适用于存储JSON格式的数据，如配置信息、用户偏好设置。

选择数据类型时，应考虑字段将要存储的数据类型、大小、精度以及如何使用这些数据。正确的数据类型选择可以提高存储效率、查询性能和数据的准确性。

二.DDL语句

在MySQL中，DDL（Data Definition Language，数据定义语言）语句用于定义和更改数据库的结构。以下是一些常用的MySQL DDL语句，包括它们的说明、格式、示例和注释：

1. CREATE 创建

说明：用于创建新的数据库、表、视图等。

格式：

CREATE DATABASE database_name;

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

CREATE VIEW view_name AS SELECT column_list FROM table_name WHERE condition;

示例：

CREATE DATABASE mydatabase; -- 创建数据库 mydatabase

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL
); -- 创建用户表

CREATE VIEW active_users AS
SELECT id, username
FROM users
WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 创建视图显示最近一个月登录的用户

2. ALTER 修改

说明：用于修改现有数据库对象的结构，如添加或删除列、修改数据类型等。

格式：

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

示例：

ALTER TABLE users ADD email VARCHAR(100); -- 在用户表中添加 email 列

ALTER TABLE users MODIFY COLUMN password VARCHAR(100); -- 修改密码列的数据类型

ALTER TABLE users DROP COLUMN email; -- 从用户表中删除 email 列

3. DROP 删除

说明：用于删除数据库对象，如数据库、表、视图等。

格式：

DROP DATABASE IF EXISTS database_name;

DROP TABLE IF EXISTS table_name;

DROP VIEW IF EXISTS view_name;

示例：

DROP DATABASE IF EXISTS olddatabase; -- 如果存在，则删除数据库 olddatabase

DROP TABLE IF EXISTS users; -- 如果存在，则删除用户表

DROP VIEW IF EXISTS active_users; -- 如果存在，则删除视图 active_users

4. TRUNCATE 清空

说明：用于删除表中的所有行，但保留表结构。

格式：

TRUNCATE TABLE table_name;

示例：

TRUNCATE TABLE users; -- 清空用户表中的所有数据

5. RENAME 重命名

说明：用于修改数据库对象的名称。

格式（MySQL 5.6.1+）：

RENAME TABLE old_table_name TO new_table_name;

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

示例：

RENAME TABLE users_old TO users_new; -- 将表 users_old 重命名为 users_new

ALTER TABLE users CHANGE username user_name VARCHAR(50); -- 将列 username 重命名为 user_name

6. INDEX 索引

说明：用于管理表上的索引，以提高查询性能。

格式：

CREATE INDEX index_name ON table_name (column_name);

DROP INDEX index_name ON table_name;

示例：

CREATE INDEX idx_user_name ON users (user_name); -- 在用户表的 user_name 列上创建索引

DROP INDEX idx_user_name ON users; -- 删除用户表上的 idx_user_name 索引

7. PRIMARY KEY 主键

说明：用于定义表的主键，确保列的唯一性。

格式：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

示例：

ALTER TABLE users ADD PRIMARY KEY (id); -- 将 id 列设为主键

8. FOREIGN KEY 外键

说明：用于定义表的外键约束，维护表之间的数据一致性。

格式：

ALTER TABLE child_table ADD CONSTRAINT fk_name
  FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);

示例：

ALTER TABLE orders ADD CONSTRAINT fk_users
  FOREIGN KEY (user_id) REFERENCES users (id); -- 将订单表的 user_id 列设为外键，引用用户表的 id 列

9. CHECK 检查

说明：用于定义列值的限制条件。

格式（MySQL 8.0.16+）：

ALTER TABLE table_name ADD CONSTRAINT check_name CHECK (expression);

示例：

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0); -- 确保价格列的值大于0

10. COMMENT 注释

说明：用于为数据库对象添加注释。

格式：

ALTER TABLE table_name ADD COMMENT 'table comment';

ALTER TABLE table_name MODIFY column_name datatype COMMENT 'column comment';

示例：

ALTER TABLE users ADD COMMENT 'This table contains user data'; -- 为用户表添加注释

ALTER TABLE users MODIFY user_name VARCHAR(50) COMMENT 'The name of the user'; -- 为 user_name 列添加注释

注释：DDL操作通常需要数据库的写权限，并且在执行这些操作时要小心，因为它们可能会对数据库的结构和其中的数据产生不可逆的更改。在执行DDL语句之前，建议备份相关数据。

三.DML语句

MySQL中的DML语句主要用于对数据库中的数据执行添加、修改、删除和查询操作。DML语句可以分为单表操作和多表操作两类。

1.单表操作

单表操作涉及对单个数据库表的直接操作。

1. SELECT 查询

说明：从表中检索数据，可以指定条件、排序和限制结果集。

格式：

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC, ...
LIMIT offset, count;

示例：

SELECT * FROM users WHERE age > 30 ORDER BY last_name DESC LIMIT 10;

注释：SELECT * 表示选择所有列，ASC 表示升序，DESC 表示降序。

2. INSERT 插入

说明：向表中添加新的数据行。

格式：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例：

INSERT INTO users (first_name, last_name, email) VALUES ('Alice', 'Smith', 'alice@example.com');

注释：如果列名未指定，则默认插入所有列，列的顺序和值必须匹配。

3. UPDATE 更新

说明：更新表中的现有数据。

格式：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

UPDATE users SET email = 'newalice@example.com' WHERE first_name = 'Alice';

注释：WHERE 子句用于指定哪些行将被更新，如果省略，将更新所有行。

4. DELETE 删除

说明：从表中删除数据。

格式：

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM users WHERE last_name = 'Smith';

注释：与UPDATE 一样，WHERE 子句用于指定哪些行将被删除，如果省略，将删除所有行。

2.多表操作

多表操作涉及对两个或更多表的联合操作。

1. SELECT 联合查询

说明：从多个表中检索数据，通常用于执行连接操作。

格式：

SELECT column1, column2, ...
FROM table1
JOIN_TYPE table2 ON join_condition
WHERE condition
ORDER BY column1 ASC|DESC, ...
LIMIT offset, count;

示例：

SELECT users.first_name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.order_date > '2023-01-01';

注释：JOIN_TYPE 可以是 INNER JOIN, LEFT JOIN, RIGHT JOIN, 或 FULL OUTER JOIN。

2. UPDATE 多表更新

说明：同时更新多个表中的数据。

格式（MySQL不支持直接的多表更新，但可以使用多个UPDATE语句或存储过程）：

示例：

UPDATE users, orders SET users.last_login = NOW() WHERE users.id = orders.user_id AND orders.order_date > '2023-01-01';

注释：这种写法在MySQL中不推荐使用，因为它可能会导致不可预测的更新。通常应该避免跨表更新。

3. DELETE 多表删除

说明：同时从多个表中删除数据。

格式（同样，MySQL不支持直接的多表删除）：

示例：

DELETE users, orders FROM users INNER JOIN orders ON users.id = orders.user_id WHERE orders.order_date < '2022-01-01';

注释：与多表更新一样，这种写法不推荐使用，因为它可能会导致数据不一致。通常应该在一个事务中使用多个DELETE语句。

在使用DML语句时，务必注意以下几点：

• 使用WHERE子句精确指定要操作的记录。
• 在执行可能影响大量数据的操作之前，考虑先对数据进行备份。
• 使用事务来确保数据的一致性，特别是在执行多表更新或删除操作时。
• 考虑性能影响，尤其是在涉及大量数据或复杂连接操作的情况下。

3.JOIN_TYPE

SQL中的JOIN操作用于将两个或多个表中的行结合起来，基于相关的列之间的关系。以下是INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN的区别及其适用场景：

1. INNER JOIN

说明：内连接，只有两个表中都有匹配的行才会被选取。

适用场景：当你需要查询两个表中都有的、匹配的数据时使用。

示例：

SELECT *
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

2. LEFT JOIN (或 LEFT OUTER JOIN)

说明：左连接，结果集包括左表中的所有行，即使右表中没有匹配的行。右表中没有匹配的行将用NULL填充。

适用场景：当你需要查询左表的所有数据，并且对于关联的右表数据不关心是否完整时。

示例：

SELECT *
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

3. RIGHT JOIN (或 RIGHT OUTER JOIN)

说明：右连接，与左连接相反，结果集包括右表中的所有行，即使左表中没有匹配的行。左表中没有匹配的行将用NULL填充。

适用场景：当你需要查询右表的所有数据，并且对于关联的左表数据不关心是否完整时。

示例：

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

4. FULL OUTER JOIN

说明：全外连接，结果集包括两个表中所有匹配的行加上两个表中不匹配的行。如果某一侧没有匹配，那么该侧的结果将用NULL填充。

适用场景：当你需要查询两个表中所有数据，无论它们是否匹配时。

示例：

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_field = table2.common_field;

注意：并非所有数据库系统都支持FULL OUTER JOIN。在MySQL中，可以使用以下的左连接和右连接的组合来模拟全外连接：

(SELECT * FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field)
UNION
(SELECT * FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field)
WHERE table1.common_field IS NULL OR table2.common_field IS NULL;

选择使用哪种类型的JOIN取决于你的需求和你想从数据库查询中获得的数据。通常，INNER JOIN是最常用的，因为它只返回两个表中都有的数据，而LEFT JOIN和RIGHT JOIN则可以返回一个表的全部数据，配合另一个表中相关的数据。FULL OUTER JOIN则更为全面，但使用时需要确保它符合你的查询逻辑。

四.结语

通过本文的全面解析，你现在应该对MySQL的数据类型选择、DDL语句的结构变更能力以及DML语句的数据操纵技巧有了深刻的理解。掌握了这些知识，你将能够在数据库设计和操作中做出更明智的决策，无论是进行精细的数据查询、构建高效的数据模型，还是实施数据的增删改操作。记住，每一行SQL代码都可能对数据的完整性和性能产生重大影响，因此，始终以谨慎和专业的态度对待数据库操作是非常重要的。随着你对MySQL的进一步探索，这些基础概念将继续作为你坚实的后盾，助你在数据管理的道路上越走越远。