首页 > 数据库 >搞懂MySQL 子查询，事务，权限并附加MySQL实战

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

时间：2024-09-30 22:54:35浏览次数：9  
标签：name -- employees 查询 MySQL 搞懂 权限 WHERE id

摘要：

       本文主要用来讲解和介绍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.小结：

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

标签：name,--,employees,查询,MySQL,搞懂,权限,WHERE,id
From： https://blog.csdn.net/m0_72047169/article/details/142552806

相关文章

  • MySQL之多表关系篇与多表查询篇
    学习前思路拆解：1.理解数据为什么要拆表存储2.明白多表有几种数据关系3.掌握表关系限制的语法并实现4.掌握多表查询语法和练习数据库多表关系1.为什么要拆表存储数据？因为将数据全部存储在一个表中，容易出现数据冗余。例子：学生表和学生成绩表不拆表存储：拆表存储： ......
  • MySQL之数据表，数据类型，表约束类型的指令讲解和练习
        本篇博客主要用来记录和分享本人学习MySQL数据库的基本操作指令的笔记和心得，包括数据表操作、数据管理、数据类型的讲解以及表的约束。通过实际的示例和注意事项，帮助大家更好地理解和应用这些知识。一、数据表操作指令1.创建数据表使用CREATETABLE指令可以创......
  • Connector C++ 连接 MySQL 数据库之增删改查
    在vcpkg中折腾了mysql-connector-cpp8.0很久，一直连接不上远程数据库，后面查官方文档，mysql-connector-cpp8.0好像只支持MySQL8.0以上的数据库，本来想把远程服务器上的MySQL升级到MySQL8.0，后面发现测试服务器的配置有点拉跨，架不住MySQL8.0，但是vcpkg中又没有mysql-c......
  • 记一次mysql导致服务器CPU突然暴涨的问题
    9月30号20点，感觉网站后台怎么越来越慢，打开阿里云看了服务器CPU竟然在几天内，一直是99%~100%，今天一天就觉得网站访问慢，还想着优化读取数据，原来CPU早就干爆了。想想有人攻击的话也不至于一直这样，putty上top一下，果然是mysql占满了CPU，大致查找策略如下 1、通过ps命令查找mysql进程......
  • mysql写外键约束时不生效
    写了外键约束，删除父表内容发现删除成功解决方法查看两个表使用的引擎showcreatetable表名;发现ENGINE=MyISAM更改引擎ALTERTABLEempENGINE=InnoDB;重新写外键再次尝试删除父表内容失败MyISAM和InnoDB是MySQL中的两种存储引擎，各自有不同的特点：MyISAM：......
  • sql注入-基于MySQL
    SQL注入的失败——SQL语句的未知性导致的拼接失败SQL操作都会导致SQL注入的出现：select，insert，update，delete数据类型：数字型(无符号干扰)——select*fromnewswhereid=$id;字符型(有符号干扰)——select*fromnewswhereid='$id';搜索型(有多符号干扰)——s......
  • 【25届计算机毕设选题推荐】基于python+MySQL的社区医院挂号就诊管理系统的设计与实现
    ✍✍计算机毕业编程指导师**⭐⭐个人介绍：自己非常喜欢研究技术问题！专业做Java、Python、小程序、安卓、大数据、爬虫、Golang、大屏等实战项目。⛽⛽实战项目：有源码或者技术上的问题欢迎在评论区一起讨论交流！⚡⚡Java、Python、小程序、大数据实战项目集⚡⚡文末获取......
  • 【2025计算机毕设选题推荐】基于python+MySQL的社区医院管理系统的设计与实现
    ✍✍计算机毕业编程指导师**⭐⭐个人介绍：自己非常喜欢研究技术问题！专业做Java、Python、小程序、安卓、大数据、爬虫、Golang、大屏等实战项目。⛽⛽实战项目：有源码或者技术上的问题欢迎在评论区一起讨论交流！⚡⚡Java、Python、小程序、大数据实战项目集⚡⚡文末获取......
  • nodejs koa 用knex.js链接mysql数据库，进行数据增删改查
    1、knex.js安装npminstallknex官方Installation|Knex.js中文文档|Knex.js中文网2、进行数据库链接constknex=require('knex')({client:'mysql2',connection:{host:'127.0.0.1',//地址user:'root',//账号......
  • 初学MySQL之基础篇1
    1、基本规则SQL可以写成一行或者多行，关键字不能被缩写也不能分行；但为了提高可读性，各子句分行写，必要时使用缩进；每条命令以;或\g结束；所有的()、单引号、双引号使用英文状态输入，必须成对结束；字符串型和日期、时间类型的数据使用单引号（''）表示；列的别名尽量使用双引......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99