MySQL 8 教程

5.1 插入数据

插入数据是向数据库表中添加新记录的操作。MySQL提供了多种插入数据的方式，包括为表的所有字段插入数据、为表的指定字段插入数据、同时插入多条记录以及将查询结果插入表中等。

5.1.1 为表的所有字段插入数据

当为表的所有字段插入数据时，可以不指定字段名，但需要保证值的顺序与表中字段的顺序一致。

INSERT INTO 表名 VALUES (值1, 值2, ..., 值n);

-- 假设有一个employees表，包含id, name, age, department, salary五个字段
INSERT INTO employees VALUES (1, '张三', 30, '技术部', 8000);

-- 可以使用DEFAULT关键字为自增字段或有默认值的字段指定默认值
INSERT INTO employees VALUES (DEFAULT, '李四', 25, '市场部', 6000);

-- 对于字符串值，需要使用单引号或双引号括起来
INSERT INTO employees VALUES (DEFAULT, "王五", 35, "人事部", 7000);

5.1.2 为表的指定字段插入数据

在实际应用中，经常需要只为表的部分字段插入数据，而其他字段使用默认值或NULL值。

INSERT INTO 表名 (字段1, 字段2, ..., 字段m) VALUES (值1, 值2, ..., 值m);

-- 只为name, department和salary字段插入数据，其他字段使用默认值
INSERT INTO employees (name, department, salary) VALUES ('赵六', '财务部', 6500);

-- 可以任意指定字段的顺序
INSERT INTO employees (department, name, salary, age) VALUES ('技术部', '孙七', 9000, 28);

-- 可以插入NULL值（如果字段允许NULL）
INSERT INTO employees (name, department, salary, age) VALUES ('周八', '市场部', NULL, 26);

5.1.3 同时插入多条记录

MySQL支持在一条INSERT语句中同时插入多条记录，这比多次执行单条INSERT语句更高效。

INSERT INTO 表名 (字段1, 字段2, ..., 字段m) VALUES
(值1, 值2, ..., 值m),
(值1, 值2, ..., 值m),
...
(值1, 值2, ..., 值m);

-- 同时插入3条记录
INSERT INTO employees (name, age, department, salary) VALUES
('吴九', 32, '技术部', 8500),
('郑十', 29, '市场部', 6800),
('钱十一', 31, '财务部', 7200);

-- 不指定字段名，同时插入多条记录
INSERT INTO departments VALUES
(DEFAULT, '研发部'),
(DEFAULT, '测试部'),
(DEFAULT, '运维部');

5.1.4 将查询结果插入表中

MySQL支持将一个查询的结果插入到另一个表中，这在数据迁移、备份和报表生成等场景中非常有用。

INSERT INTO 目标表 (字段1, 字段2, ..., 字段m)
SELECT 字段1, 字段2, ..., 字段m
FROM 源表
WHERE 条件;

-- 假设有一个employees表和一个high_salary_employees表
-- 创建high_salary_employees表
CREATE TABLE high_salary_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

-- 将employees表中工资大于8000的员工插入到high_salary_employees表
INSERT INTO high_salary_employees (id, name, department, salary)
SELECT id, name, department, salary
FROM employees
WHERE salary > 8000;

-- 如果两个表的结构相同，可以省略字段列表
-- 复制整个表的数据
CREATE TABLE employees_backup LIKE employees;
INSERT INTO employees_backup
SELECT * FROM employees;

-- 插入计算结果
-- 假设有一个orders表和一个monthly_sales表
-- 创建monthly_sales表
CREATE TABLE monthly_sales (
    year INT,
    month INT,
    total_sales DECIMAL(12,2)
);

-- 计算每个月的销售总额并插入到monthly_sales表
INSERT INTO monthly_sales (year, month, total_sales)
SELECT YEAR(order_date), MONTH(order_date), SUM(amount)
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

5.2 更新数据

更新数据是修改表中现有记录的操作。在MySQL中，使用UPDATE语句来更新数据。

UPDATE 表名
SET 字段1 = 值1, 字段2 = 值2, ..., 字段m = 值m
[WHERE 条件]
[ORDER BY 排序字段]
[LIMIT 数量];

其中：

• 表名：要更新数据的表名
• SET：指定要更新的字段和新值
• WHERE：可选，指定更新条件，只有满足条件的记录才会被更新
• ORDER BY：可选，指定更新记录的顺序
• LIMIT：可选，限制更新记录的数量

-- 更新单个字段
UPDATE employees
SET salary = 8500
WHERE id = 1;

-- 更新多个字段
UPDATE employees
SET department = '研发部', salary = 9000
WHERE name = '张三';

-- 使用WHERE条件更新多条记录
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部';

-- 使用表达式更新字段值
UPDATE employees
SET hire_date = CURRENT_DATE()
WHERE id = 5;

-- 使用子查询更新数据
-- 假设有一个departments表，包含department_id和manager_id字段
UPDATE employees e
SET manager_id = (
    SELECT manager_id
    FROM departments d
    WHERE d.department_name = e.department
)
WHERE department = '技术部';

-- 使用ORDER BY和LIMIT限制更新的记录
UPDATE employees
SET salary = salary * 1.05
ORDER BY hire_date DESC
LIMIT 10;  -- 只更新最近入职的10名员工的工资

5.3 删除数据

删除数据是从表中移除记录的操作。在MySQL中，使用DELETE语句来删除数据。

DELETE FROM 表名
[WHERE 条件]
[ORDER BY 排序字段]
[LIMIT 数量];

其中：

• 表名：要删除数据的表名
• WHERE：可选，指定删除条件，只有满足条件的记录才会被删除
• ORDER BY：可选，指定删除记录的顺序
• LIMIT：可选，限制删除记录的数量

-- 删除单条记录
DELETE FROM employees
WHERE id = 10;

-- 删除满足条件的多条记录
DELETE FROM employees
WHERE department = '行政部';

-- 使用复杂的WHERE条件
DELETE FROM orders
WHERE order_date < '2023-01-01' AND status = 'cancelled';

-- 使用ORDER BY和LIMIT限制删除的记录
DELETE FROM logs
ORDER BY log_date ASC
LIMIT 1000;  -- 删除最早的1000条日志记录

-- 使用子查询删除数据
DELETE FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE status = 'inactive'
);

5.3.1 删除没有被关联的表

对于没有被其他表关联的表，可以直接使用DELETE语句删除数据。

-- 假设有一个独立的表，没有外键关联
DELETE FROM temp_data
WHERE create_time < '2023-01-01';

-- 删除整个表的数据（但保留表结构）
TRUNCATE TABLE temp_data;  -- 比DELETE FROM更高效

-- 或者
DELETE FROM temp_data;

5.3.2 删除被其他表关联的主表

如果要删除的数据所在的表是主表（被其他表通过外键关联），则需要特别注意，因为外键约束会防止删除被引用的记录。

有几种方法可以解决这个问题：

1. 先删除从表中关联的记录，再删除主表中的记录
2. 修改外键约束，设置ON DELETE CASCADE（级联删除）
3. 暂时禁用外键约束，删除数据后再启用

-- 假设有一个departments表（主表）和一个employees表（从表），employees表有一个外键指向departments表

-- 方法1：先删除从表中的关联记录，再删除主表中的记录
DELETE FROM employees WHERE department_id = 5;
DELETE FROM departments WHERE department_id = 5;

-- 方法2：修改外键约束，设置ON DELETE CASCADE
-- 首先删除现有的外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_department;
-- 然后添加新的外键约束，设置级联删除
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE;
-- 现在可以直接删除主表中的记录，从表中关联的记录会自动删除
DELETE FROM departments WHERE department_id = 6;

-- 方法3：暂时禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM departments WHERE department_id = 7;
-- 删除完成后，重新启用外键约束
SET FOREIGN_KEY_CHECKS = 1;
-- 注意：这种方法可能会导致数据不一致，谨慎使用

5.4 为表增加计算列

MySQL 5.7及以上版本支持计算列（Generated Columns），计算列的值是根据其他列的值计算出来的。计算列可以分为虚拟计算列（Virtual）和存储计算列（Stored）两种类型：

• 虚拟计算列（Virtual）：不存储在磁盘上，只有在查询时才计算，不占用存储空间
• 存储计算列（Stored）：存储在磁盘上，更新时会重新计算，占用存储空间，但查询性能更好

-- 创建表时定义计算列
CREATE TABLE 表名 (
    字段1 数据类型,
    字段2 数据类型,
    ...
    计算列名 数据类型 GENERATED ALWAYS AS (表达式) [VIRTUAL | STORED]
);

-- 创建包含计算列的表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    -- 虚拟计算列：计算总价值
    total_value DECIMAL(12,2) GENERATED ALWAYS AS (price * quantity) VIRTUAL,
    -- 存储计算列：计算折扣价格（假设折扣为90%）
    discounted_price DECIMAL(10,2) GENERATED ALWAYS AS (price * 0.9) STORED
);

-- 插入数据（不需要为计算列提供值）
INSERT INTO products (name, price, quantity) VALUES ('笔记本电脑', 5999.00, 10);

-- 查询数据（可以直接使用计算列）
SELECT id, name, price, quantity, total_value, discounted_price FROM products;

-- 输出示例
+----+--------------+---------+----------+-------------+------------------+
| id | name         | price   | quantity | total_value | discounted_price |
+----+--------------+---------+----------+-------------+------------------+
|  1 | 笔记本电脑   | 5999.00 |       10 |    59990.00 |          5399.10 |
+----+--------------+---------+----------+-------------+------------------+

-- 修改表，添加计算列
ALTER TABLE orders
ADD total_amount DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL;

-- 修改计算列的定义
-- 首先删除现有的计算列
ALTER TABLE products DROP COLUMN total_value;
-- 然后添加新的计算列
ALTER TABLE products
ADD total_value DECIMAL(12,2) GENERATED ALWAYS AS (price * quantity * 1.13) VIRTUAL;  -- 包含13%的税

5.5 MySQL 8.0的新特性——DDL的原子化

MySQL 8.0引入了原子DDL（Atomic DDL）功能，确保数据定义语言（DDL）语句的执行是原子的：要么全部成功，要么全部失败，不会出现部分执行的情况。这大大提高了数据库的可靠性和一致性。

5.5.1 原子DDL的优势

• 提高数据一致性：确保DDL操作不会导致数据库处于不一致的状态
• 简化错误处理：只需要处理整个操作成功或失败的情况，不需要处理部分成功的复杂情况
• 提高可靠性：在系统崩溃或断电等异常情况下，数据库能够恢复到一致的状态

5.5.2 原子DDL支持的操作类型

MySQL 8.0的原子DDL功能支持以下类型的操作：

• 表操作：CREATE、ALTER、DROP表等
• 索引操作：CREATE、ALTER、DROP索引等
• 视图操作：CREATE、ALTER、DROP视图等
• 触发器操作：CREATE、ALTER、DROP触发器等
• 存储过程和函数操作：CREATE、ALTER、DROP存储过程和函数等

-- 开始事务
START TRANSACTION;

-- 执行一个复杂的DDL操作
ALTER TABLE employees
    ADD COLUMN department_id INT,
    ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id),
    MODIFY COLUMN salary DECIMAL(12,2);

-- 如果执行成功，提交事务
COMMIT;
-- 如果执行失败，回滚事务
-- ROLLBACK;

-- 原子DDL操作示例：创建表并同时创建索引和触发器
START TRANSACTION;

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    INDEX idx_customer_id (customer_id)
);

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.order_date = CURRENT_TIMESTAMP;

-- 如果所有操作都成功，提交事务；如果任何操作失败，所有操作都会回滚
COMMIT;