核心DDL与DML命令：创建、查询、更新、删除

使用MySQL通常从两项任务开始：定义表结构以及操作表中的数据行。核心的DDL和DML命令正是安全完成这些操作的关键。

DDL命令用于定义数据库对象（如表）。DML命令用于插入、读取、更新和删除表数据。以下示例使用一个小型products表，以便在上下文中展示这些命令。

数据定义语言（DDL）：创建表

DDL命令涉及数据库对象的模式（schema）和结构。数据存储中最基本的DDL命令是CREATE TABLE，它允许你定义数据的蓝图。

CREATE TABLE 语法

创建表的基本语法包括指定表名，然后定义其列，包括数据类型和任何约束。

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

table_name 在当前数据库中必须唯一。每个列需要名称、数据类型以及可选的约束，例如 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY 或 DEFAULT value。

CREATE TABLE 示例

让我们创建一个简单的 products 表，用于存储库存中的商品信息：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

在此示例中：

• product_id 是一个自动递增的整数，并作为主键。
• product_name 是必需的字符串，且必须唯一。
• price 是必需的十进制数。
• stock_quantity 如果未指定，则默认为 0。
• created_at 记录行创建时的时间戳。

数据操作语言（DML）：操作数据

DML命令用于管理数据库表中的记录（行）。它们是日常数据库操作中最常用的命令。

INSERT 命令：添加新数据

INSERT 命令用于向表中添加新的数据行。

INSERT 语法

使用 INSERT 有几种方式：

1. 为所有列指定值：

   INSERT INTO table_name
   VALUES (value1, value2, value3, ...);
   

   值的顺序必须与表定义中列的顺序一致。

2. 为特定列指定值：

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

   这种方式通常更受青睐，因为它更易读，并且在表结构发生变化时不易出错。

INSERT 示例

使用我们的 products 表：

-- 为所有列插入值（假设 product_id 为 AUTO_INCREMENT）
INSERT INTO products (product_name, category, price, stock_quantity)
VALUES ('Laptop Pro', 'Electronics', 1200.50, 50);

-- 为特定列插入值
INSERT INTO products (product_name, price)
VALUES ('Mechanical Keyboard', 75.99);

-- 一次插入多行
INSERT INTO products (product_name, category, price, stock_quantity)
VALUES 
('Ergonomic Mouse', 'Electronics', 25.00, 120),
('Desk Lamp', 'Home Goods', 45.00, 75);

SELECT 命令：检索数据

SELECT 命令是数据检索的基石。它允许你查询数据库并根据各种条件获取特定数据。

SELECT 语法

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC]
LIMIT number;

• column1, column2, ...：你想要检索的列。使用 * 选择所有列。
• FROM table_name：你正在查询的表。
• WHERE condition：（可选）根据指定条件过滤行。条件可以包含比较运算符（=、!=、>、<、>=、<=）、逻辑运算符（AND、OR、NOT）和模式匹配（LIKE）。
• ORDER BY column_name [ASC|DESC]：（可选）按一个或多个列对结果集进行排序。ASC 表示升序（默认），DESC 表示降序。
• LIMIT number：（可选）限制返回的行数。

SELECT 示例

-- 选择所有产品的所有列
SELECT *
FROM products;

-- 仅选择产品的名称和价格
SELECT product_name, price
FROM products;

-- 选择 'Electronics' 类别中的产品
SELECT *
FROM products
WHERE category = 'Electronics';

-- 选择价格大于 100 的产品，按价格降序排列
SELECT product_name, price
FROM products
WHERE price > 100
ORDER BY price DESC;

-- 选择价格最高的前 5 个产品
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- 选择名称以 'L' 开头的产品
SELECT *
FROM products
WHERE product_name LIKE 'L%';

UPDATE 命令：修改现有数据

UPDATE 命令允许你更改表中的现有记录。

UPDATE 语法

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

• table_name：包含要更新记录的表。
• SET column1 = value1, ...：指定要更新的列及其新值。
• WHERE condition：至关重要！ 该子句决定哪些行将被更新。如果省略，将更新表中的所有行。

UPDATE 示例

-- 将 'Laptop Pro' 的价格提高 50
UPDATE products
SET price = price + 50.00
WHERE product_name = 'Laptop Pro';

-- 更新 'Mechanical Keyboard' 的类别和库存
UPDATE products
SET category = 'Accessories', stock_quantity = 100
WHERE product_name = 'Mechanical Keyboard';

-- **警告**：这会将所有产品的价格更新为 10.00
-- UPDATE products
-- SET price = 10.00;

提示： 更新时始终使用 WHERE 子句，以确保只修改预期的记录。先用 SELECT 语句测试你的 WHERE 子句，确认它定位到了正确的行。

DELETE 命令：删除数据

DELETE 命令用于从表中删除行。

DELETE 语法

DELETE FROM table_name
WHERE condition;

• table_name：要从中删除行的表。
• WHERE condition：至关重要！ 该子句指定要删除哪些行。如果省略，将删除表中的所有行。

DELETE 示例

-- 删除名称为 'Desk Lamp' 的产品
DELETE FROM products
WHERE product_name = 'Desk Lamp';

-- 删除所有库存为零的产品（stock_quantity 为 0）
DELETE FROM products
WHERE stock_quantity = 0;

-- **警告**：这会删除 'products' 表中的所有记录！
-- DELETE FROM products;

警告： 与 UPDATE 类似，在 DELETE 语句中省略 WHERE 子句是一种破坏性操作，会删除表中的所有数据。请极其谨慎地使用。

关键要点

理解并有效使用 DDL 和 DML 命令是数据库管理的基础。CREATE TABLE 允许你定义数据的结构，而 INSERT、SELECT、UPDATE 和 DELETE 则使你能够填充、查询、修改和管理这些数据。

通过使用清晰的 WHERE 子句（特别是对于 UPDATE 和 DELETE）练习这些命令，你可以为使用 MySQL 数据库打下坚实的基础，并确保数据的完整性和准确性。在执行可能具有破坏性的操作之前，请始终记得备份数据。