搞懂MySQL 子查询，事务，权限并附加MySQL实战

摘要：

       本文主要用来讲解和介绍MySQL数据库中有关子查询，事务，权限等部分的内容，并附加了场景模拟和实战练习。用于快速掌握和理解子查询，事务，权限这些部分的语法和知识点。

1.子查询

子查询：一个sql语句嵌套了另一个或者多个查询语句。

1.1子查询的四种结果

标量子查询：返回结果为一行一列，用于条件判定。

行子查询：返回结果为单行多列，用于数据插入数据的值。

列子查询：返回结果为多行单列，用于条件对比。

表子查询：返回结果为多行多列，用于查询虚拟的中间表。

例子：

-- 创建 departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 创建 employees 表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- 插入部门数据
INSERT INTO departments (id, name) VALUES (1, '销售');
INSERT INTO departments (id, name) VALUES (2, '市场营销');
INSERT INTO departments (id, name) VALUES (3, '实习');
INSERT INTO departments (id, name) VALUES (4, '人力资源');

-- 插入员工数据
INSERT INTO employees (id, name, department_id, salary) VALUES (1, '艾丽斯', 1, 9500.00);
INSERT INTO employees (id, name, department_id, salary) VALUES (2, '鲍勃', 1, 8000.00);
INSERT INTO employees (id, name, department_id, salary) VALUES (3, '查理', 2, 6000.00);
INSERT INTO employees (id, name, department_id, salary) VALUES (4, '大卫', 2, 7500.00);
INSERT INTO employees (id, name, department_id, salary) VALUES (5, '伊芙', 3, 3000.00);

-- 创建 top_employees 表
CREATE TABLE top_employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) NOT NULL
);

-- 插入工资超过 7000 的员工
INSERT INTO top_employees (id, name, department_id, salary)
SELECT id, name, department_id, salary
FROM employees
WHERE salary > 7000;

/*下面将使用伪代码，来方便理解*/

-- 行子查询

-- 查询内容：找出比员工鲍勃的工资更高的其他员工。

SELECT 姓名, 工资 
FROM employees 
WHERE 工资 > (SELECT 工资 FROM employees WHERE name = '鲍勃');

-- 列子查询

-- 查询内容：查找所有工资低于大卫的员工姓名。
SELECT 姓名 
FROM employees 
WHERE 工资 < (SELECT 工资 FROM employees WHERE name = '大卫');

-- 表子查询

-- 查询内容：找出工资低于其部门平均工资的所有员工。
SELECT e.姓名, e.工资 
FROM employees e 
JOIN (SELECT 部门ID, AVG(工资) AS 平均工资 
      FROM employees 
      GROUP BY 部门ID) avg_table 
ON e.部门ID = avg_table.部门ID 
WHERE e.工资 < avg_table.平均工资;

结果图：

行子查询                                       列子查询                 表子查询

           

1.2 子查询搭配其他语句

子查询也可INSERT,UPDATE,DELETE语句中使用，以提供额外的判定条件。

例子：

-- 将 Sales 部门且工资低于 9000 的员工工资提高 10%
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE name = '销售')
AND salary < (SELECT AVG(salary)
              FROM employees
              WHERE department_id = (SELECT id FROM departments WHERE name = '销售'));

-- 删除所有在 Interns 部门的员工
DELETE FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = '实习');

-- 查看 employees 表
SELECT e.id AS 员工ID, e.name AS 员工姓名, d.name AS 部门名称, e.salary AS 工资
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 查看 top_employees 表
SELECT id AS 员工ID, name AS 员工姓名, department_id AS 部门ID, salary AS 工资
FROM top_employees;

结果：

employees 表                                                employees 表 （删除实习且销售部门涨10%工资）

 

top_employees 表

结果分析：子查询搭配INSERT,UPDATE,DELETE语句中使用，能准确运行，并且做到更加精细的对表内容进行调整。

2.事务

       事务中的单个命令不会立即改变数据库数据，当内部命令执行成功，统一更新数据，当有任意的命令失败时，可以通过回滚状态。

2.1 特点

原子性：事务中的所有操作要么全部执行成功，要么全部不执行。事务不可分割。
一致性：事务执行前后，数据库必须保持一致性状态。
隔离性：多个事务并发执行时，互不干扰。一个事务的执行不应影响到其他事务。
持久性：一旦事务提交，其结果是永久的，即使系统故障也不会丢失。

2.2 基本语法

开始事务：START TRANSACTION 或 BEGIN;
提交事务：COMMIT;
回滚事务：ROLLBACK;

2.3 实战场景模拟

假设一个网上银行系统中，有两个用户：Alice 和 Bob。Alice 希望将 1000 元转账给 Bob。这个转账过程涉及两个主要步骤：

1. 从 Alice 的账户中扣除 1000 元。
2. 向 Bob 的账户中添加 1000 元。

在这个过程中，使用事务是非常重要的，原因如下：

• 如果从 Alice 的账户扣款成功，但向 Bob 的账户添加金额失败（例如，系统崩溃或数据库错误），那么 Alice 的账户余额将不准确，这会导致资金丢失。
• 同样，如果向 Bob 的账户添加金额成功，但从 Alice 的账户中扣款失败，那么 Bob 将得到错误的金额。

因此，我们需要确保这两个操作要么同时成功，要么同时失败。

具体操作：

-- 1. 创建示例表

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    balance DECIMAL(10, 2)
);

INSERT INTO accounts (name, balance) VALUES
('Alice', 5000),
('Bob', 3000),
('Charlie', 7000);

2. 使用事务进行转账操作

-- 假设我们要将 1000 元从 Alice 转账给 Bob。我们可以通过事务来确保转账操作的完整性。

START TRANSACTION;

-- 从 Alice 的账户中扣除 1000 元
UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';

-- 向 Bob 的账户中添加 1000 元
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';

-- 提交事务
COMMIT;

 3. 错误处理与回滚示例

-- 假设在执行上述操作时，发生了错误（例如，Alice 的余额不足），我们希望能够回滚操作。

START TRANSACTION;

-- 假设 Alice 的余额不足
UPDATE accounts SET balance = balance - 6000 WHERE name = 'Alice'; -- 这里会导致错误

-- 向 Bob 的账户中添加 1000 元
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';

-- 回滚事务
ROLLBACK;

3.权限

       权限指的就是数据库中对用户进行操作的授权机制。通过设定不同用户的权限，可以控制他们对数据库的访问和操作。

3.1 权限的类型

CREATE: 允许用户创建数据库或表
SELECT：允许用户查询数据库或表中的数据。
INSERT：允许用户向数据表中插入新数据。
UPDATE：允许用户修改数据表中的现有数据。
DELETE：允许用户从数据表中删除数据。
DROP: 允许用户删除数据库或表。
ALL PRIVILEGES: 允许用户执行全部操作。
GRANT OPTION: 允许用户将权限分给其他用户。

3.2 场景模拟

假设有一个在线商店数据库 online_store，其中包含 users 和 orders 两张表。我们将创建两个用户：
admin 用户：拥有所有表的完全访问权限。
sales 用户：只能访问 orders 表的查询和插入权限。

代码实战：

创建用户：
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_password';
CREATE USER 'sales'@'localhost' IDENTIFIED BY 'sales_password';

--授予权限：
为 admin 用户授予所有权限：
GRANT ALL PRIVILEGES ON online_store.* TO 'admin'@'localhost';

--为 sales 用户授予 orders 表的查询和插入权限：
GRANT SELECT, INSERT ON online_store.orders TO 'sales'@'localhost';

--查看权限：
SHOW GRANTS FOR 'admin'@'localhost';
SHOW GRANTS FOR 'sales'@'localhost';

/*模拟用户操作：
使用 admin 用户登录，可以进行任何操作。
使用 sales 用户登录，只能执行查询和插入操作*/

-- 查询订单
SELECT * FROM orders;

-- 插入新订单
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);

-- 尝试更新订单（会失败，权限不足）
UPDATE orders SET quantity = 5 WHERE order_id = 1; 

--假设我们要撤销 sales 用户的插入权限：
REVOKE INSERT ON online_store.orders FROM 'sales'@'localhost';

--如果不再需要某个用户，可以删除：
DROP USER 'sales'@'localhost';

4.小结：

       所有的操作都是为了理解如何高效且安全地操作数据库。子查询使得数据查询更灵活，事务确保数据的安全性和一致性，而权限管理则是保障数据库安全的基石。