MySQL - 查询

MySQL - 核心数据管理查询

DDL 和 DML 介绍

MySQL 查询是用于与数据库交互的命令。它们大致分为两类：

• 数据定义语言 (DDL)： 定义或修改数据库结构的命令。示例：CREATE、ALTER、DROP。
• 数据操作语言 (DML)： 管理数据库中数据的命令。示例：INSERT、SELECT、UPDATE、DELETE。

本教程将通过使用 products 表作为示例，引导您学习管理数据库最基本的查询。为了提高可读性，我们将 SQL 关键字大写，尽管 MySQL 对大小写不敏感。

设置：CREATE DATABASE 和 USE

首先，我们创建一个新数据库来存放表。然后，我们选择它，使其成为后续命令的活动数据库。

语法

CREATE DATABASE database_name;
USE database_name;

示例

-- 创建一个名为 'my_store' 的新数据库
CREATE DATABASE my_store;

-- 将 'my_store' 设置为活动数据库
USE my_store;

定义结构：CREATE TABLE

CREATE TABLE 语句定义了一个新表、其列以及每列的数据类型和约束。

语法

CREATE TABLE table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...
    PRIMARY KEY (primary_key_column)
);

包含现代最佳实践的示例

此示例包含常见的数据完整性约束：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    in_stock INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• AUTO_INCREMENT：自动为每个新行生成唯一的数字。
• PRIMARY KEY：唯一标识表中的每条记录。
• NOT NULL：确保列不能包含 NULL 值。
• UNIQUE：确保列中的所有值都不同。
• DEFAULT：当未指定值时，为列设置默认值。

添加数据：INSERT INTO

INSERT INTO 语句向表中添加新的数据行。

语法

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

示例（插入多行）

INSERT INTO products (sku, name, price, in_stock)
VALUES
('BK-001', 'Modern JavaScript', 29.99, 100),
('DB-002', 'SQL for Beginners', 24.99, 50),
('FW-003', 'Learning React', 39.99, 75);

检索数据：SELECT

SELECT 语句用于查询数据库并检索符合您指定条件的数据。

示例：获取所有产品

SELECT * FROM products;

示例：根据条件获取特定列

-- 查找所有价格低于 $30 的产品
SELECT name, price FROM products
WHERE price < 30.00;

修改数据：UPDATE

UPDATE 语句修改表中现有记录。

警告： UPDATE 语句务必使用 WHERE 子句。否则，您将更新表中的所有行。

语法

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

示例

-- 将 'SQL for Beginners' 这本书打折
UPDATE products
SET price = 19.99
WHERE sku = 'DB-002';

改变结构：ALTER TABLE

ALTER TABLE 语句用于在现有表中添加、删除或修改列。

示例：添加、修改和删除列

-- 添加一个用于产品描述的新列
ALTER TABLE products
ADD COLUMN description TEXT;

-- 修改列的数据类型
ALTER TABLE products
MODIFY COLUMN name VARCHAR(300) NOT NULL;

-- 删除 description 列
ALTER TABLE products
DROP COLUMN description;

删除数据：DELETE 与 TRUNCATE

DELETE 和 TRUNCATE 都用于从表中删除数据，但它们的工作方式不同。

DELETE 语句

根据 WHERE 子句删除一行或多行。它是一种 DML 操作，因此速度较慢，会记录每次行删除，并且可以在事务中回滚。它不会重置 AUTO_INCREMENT 计数器。

-- 警告：请务必小心使用 WHERE 子句！
DELETE FROM products WHERE in_stock = 0;

TRUNCATE TABLE 语句

快速删除表中的所有行。它是一种 DDL 操作，不容易回滚，并且会重置所有 AUTO_INCREMENT 值。对于清空整个表来说，它比 DELETE 快得多。

TRUNCATE TABLE products;

删除结构：DROP TABLE

DROP TABLE 语句会彻底删除表的结构及其所有数据。此操作不可逆。

语法

DROP TABLE table_name;

示例

-- 这将永久删除 products 表。
DROP TABLE products;