MySQL多表查询和事务

最新推荐文章于 2024-09-12 09:40:12 发布
最新推荐文章于 2024-09-12 09:40:12 发布
阅读量1.2k 收藏 24
点赞数 21
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49668551/article/details/134711843
版权

MySQL多表查询和事务

一、多表查询

1.1、多表关系

  1. 概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一

  1. 一对多（多对一）

  1. 多对多

  • 创建学生课程关系表

CREATE TABLE student_course (

    id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,

    studentid INT NOT NULL COMMENT '学生ID',

    courseid INT NOT NULL COMMENT '课程ID',

    CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),

    CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student(id)

) COMMENT '学生课程中间表';

  1. 一对一

1.2、多表查询概述

SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;

1.3、内连接

  1. 示例1：查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）

--表结构：emp，dept

--连接条件：emp.dept_id = dept.id

SELECT emp.name,dept.name FROM emp,dept where emp.dept_id = dept.id;

  1. 示例2：查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）

SELECT e.name,d.name FROM emp e inner join dept d ON e.dept_id = d.id;

1.4、外连接

  • 左边连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...；

相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据。

  • 右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...；

相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据。

  1. 示例1：查询emp表的所有数据，和对应的部门信息（左外连接）

SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;

此查询检索表中的所有列，并从表中检索每个雇员的“name”列。如果员工没有相应的部门（表中不存在），则结果集仍将包含该员工的信息，并且表中与部门相关的列将填充 NULL 值。

  1. 查询dept表的所有数据，和对应的员工信息（右外连接）

SELECT d.*,e.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

1.5、自连接

SELECT 字段列表 FROM 表A 别名A  JOIN  表A 别名B  ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

  1. 示例1：查询员工及其所属领导的名字

SELECT a.name,b.name FROM emp a,emp b WHERE a.managerid = b.id;

SELECT a.name, b.name：从表的两个实例（别名为 和 ）中检索“name”列。FROM emp a, emp b：指定表上的自连接。该表使用两次，使用不同的别名 （a 和 b） 来表示同一表的两个不同实例。WHERE a.managerid = b.id：设置联接的条件。

  1. 示例2：查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来

SELECT a.name ‘员工’,b.name ‘领导’ FROM emp a LEFT JOIN emp b ON a.managerid = b.id;

SELECT a.name AS '员工', b.name AS '领导'：从表的两个实例（别名为 a和 b）中检索“name”列。FROM emp a：指定左联接中的左表（a），执行左外连接。条件是左实例 （b） 中的managerid 必须等于右实例 （a）中id的行。如果没有匹配项，则右侧实例中的列将包含 NULL 值。

1.6、联合查询—union，union all

SELECT 字段列表 FROM 表A...

UNION [ALL]

SELECT 字段列表 FROM 表B...;

  1. 示例1：将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来

SELECT * FROM emp WHERE salary <5000

UNION

SELECT * FROM emp WHERE age > 50;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致；UNION ALL会将全部的数据直接合并在一起，UNION会对合并之后的数据去重。

1.7、子查询

  1. 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符：=  <>  >  >=  <  <=

示例1：查询“销售部”的所有员工信息

--a.查询“销售部”部门ID

select id from dept where name = ‘销售部’;

--b.根据销售部部门ID，查询员工信息

SELECT *

FROM emp

WHERE dept_id = (

    SELECT id

    FROM dept

    WHERE name = '销售部'

);

示例2：查询在“方东白”入职之后的员工信息

--a.查询方东白的入职日期

select entrydate from emp where name = ‘方东白’;

--b.查询指定入职日期之后的员工信息

SELECT *

FROM emp

WHERE entrydate > (

    SELECT entrydate

    FROM emp

    WHERE name = '方东白'

);

  1. 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符

描述

IN

在指定的集合范围之内，多选一

NOT IN

不在指定的集合范围之内

ANY

子查询返回列表中，有任意一个满足即可

SOME

与ANY等同，使用SOME的地方都可以使用ANY

ALL

子查询返回列表的所有值都必须满足

  • 示例1：查询“销售部”和“市场部”的所有员工信息

--a.查询“销售部”和“市场部”的部门ID

select id from dept where name = ‘销售部’ or name = ‘市场部’;

--b.根据部门ID，查询员工信息

SELECT *

FROM emp

WHERE dept_id IN (

    SELECT id

    FROM dept

    WHERE name IN ('销售部', '市场部')

);

  • 示例2：查询比财务部所有人工资都高的员工信息

---a.查询所有财务部人员工资

select id from dept where name = ‘财务部’;

select salary from emp where dept_id = (select id from dept where name = ‘财务部’);

--b.比财务部所有人工资都高的员工信息

SELECT *

FROM emp

WHERE salary > ALL (

    SELECT salary

    FROM emp

    WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部')

);

  • 示例3：查询比研发部其中任意一人工资高的员工信息

--a.查询研发部所有人的工资

select salary from emp where dept_id = (select id from dept where name = ‘研发部’);

--b.查询比研发部任意一人工资高的员工信息

SELECT *

FROM emp

WHERE salary > ANY (

    SELECT salary

    FROM emp

    WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部')

);

  1. 行子查询

子查询返回的结果是一行（可以是多行），这种子查询称为行子查询。

常用的操作符：=、<>、IN、NOT IN

  • 示例1：查询与“张三”的薪资及直属领导相同的员工信息；

--a.查询“张三”的薪资及直属领导

select salary,managerid from emp where name = ‘张三’;

--b.查询与“张三”的薪资及直属领导相同的员工信息；

SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name = ‘张三’);

  1. 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

  • 示例1：查询与“李四”，“王五”的职位和薪资相同的员工信息

--a.查询“李四”，“王五”的职位和薪资

SELECT job,salary FROM emp WHERE name = ‘李四’ OR name = ‘王五’;

--b.查询与“李四”，“王五”的职位和薪资相同的员工信息

SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name = ‘李四’ OR name = ‘王五’);

  • 查询入职日期是“2016-01-01”之后的员工信息，及其部门信息；

--a.入职日期是“2016-01-01”之后的员工信息

SELECT * FROM emp WHERE entrydate > ‘2016-01-01’;

--b.查询这部分员工，对应的部门信息；

SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > ‘2016-01-01’) e LEFT JOIN dept d ON e.dept_id = d.id;

二、多表查询案例

  • 查询员工的姓名、年龄、职位、部门信息（隐式内连接）

--表：emp，dept

--连接条件：emp.dept_id = dept.id

SELECT e.name,e.age,e.job,d.name FROM emp e,dept d WHERE e.dept_id = d.id;

  • 查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显式内连接）

--表：emp，dept

--连接条件：emp.dept_id = dept.id

SELECT e.name,e.age,e.job,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.age < 30;

  • 查询拥有员工的部门ID、部门名称

--表：emp，dept

--连接条件：emp.dept_id = dept.id

SELECT DISTINCT d.id,d.name FROM emp e,dept d WHERE e.dept_id = d.id;

  • 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来

--表：emp，dept

--连接条件：emp.dept_id = dept.id

--外连接（左外连接）

SELECT e.*,d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40；

  • 查询所有员工的工资等级

--表：emp，salgrade

--连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

SELECT e.*,s.grade,s.losal,s.hisal FROM emp e,salgrade s WHERE e.salary >= s.losal AND e.salary <= s.hisal;

SELECT e.*,s.grade,s.losal,s.hisal FROM emp e,salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

  • 查询“研发部”所有员工的信息及工资等级

--表：emp,salgrade,dept

--连接条件：emp.salary between salgrade.losal and salgrade.hisal，emp.dept_id = dept.id

--查询条件：dept.name = ‘研发部’

SELECT e.*,s.grade FROM emp e,dept d,salgrade s WHERE e.dept_id = d.id AND (e.salary between s.losal and s.hisal) AND d.name = ‘研发部’;

  • 查询“研发部”员工的平均工资

--表：emp，dept

--连接条件：emp.dept_id = dept.id

SELECT AVG(e.salary) FROM emp e,dept d WHERE e.dept_id = d.id AND d.name = ‘研发部’;

  • 查询工资比“钱六”高的员工信息

--a.查询“钱六”的薪资

select salary from emp where name = ‘钱六’;

--b.查询比“钱六”工资高的员工数据

SELECT * FROM emp WHERE salary > (select salary from emp where name = ‘钱六’);

  • 查询比平均薪资高的员工信息

--a.查询员工的平均薪资

select avg(salary) from emp;

--b.查询比平均薪资高的员工信息

SELECT * FROM emp WHERE salary > ( select avg(salary) from emp);

  • 查询低于本部门平均工资低的员工信息

--a.查询指定部门的平均工资

select avg(e1.salary) from emp e1 where e1.dept_id = 1;

--b.查询低于本部门平均工资的员工信息

SELECT *,(select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) ‘平均’ FROM emp e2 WHERE e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

  • 查询所有的部门信息，并统计部门的员工人数

--a.查询所有的部门信息

SELECT d.id,d.name,(SELECT COUNT(*) FROM emp e WHERE e.dept_id =d.id) ‘人数’ FROM dept d;

--b.统计部门的员工人数

SELECT COUNT(*) FROM emp WHERE dept_id =1;

  • 查询所有学生的选课情况，展示出学生名称，学号，课程名称

--表：student，course，student_course

--连接条件：student.id = student_course.studentid，course.id = student_course.courseid

SELECT s.name,s.no,c.name FROM student s , student_course sc , course c WHERE s.id = sc.studentid AND sc.courseid = c.id;

三、事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失效。

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

3.1、事务操作

  • 转账操作（张三给李四转账1000）
  1. 查询张三账户余额

SELECT * FROM account WHERE name = ‘张三’;

  1. 将张三的账户余额-1000

UPDATE account SET money = money – 1000 WHERE name = ‘张三’;

  1. 将李四的账户余额+1000

UPDATE account SET money = money + 1000 WHERE name = ‘李四’;

方式一：

  1. 查看/设置事务提交方式（值是1为自动；值是0为手动）

SELECT @@autocommit；

SET @@autocommit = 0；

  1. 提交事务

COMMIT；

  1. 回滚事务

ROLLBACK；

方式二：

  1. 开启事务

START TRANSACTION 或 BEGIN

  1. 提交事务

COMMIT；

  1. 回滚事务

ROLLBACK；

3.2、事务四大特性

  1. 原子性（Automicty）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3.3、并发事务问题

问题

描述

脏读

一个事务读到另外一个事务还没有提交的数据。

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

3.4、事务隔离级别

隔离级别

脏读

不可重复读

幻读

Read uncommitted

√

√

√

Read committed

×

√

√

Repeatable Read(默认)

×

×

√

Serializable

×

×

×

--查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

--设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：事务隔离级别越高，数据越安全，但是性能越低。

Xx震zZ
关注
Mysql查询正在执行的事务以及等待锁的操作方式
12-16
使用navicat测试学习: 首先使用set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚) 在打开一个执行update 查询 正在执行的事务： SELECT * FROM information_schema.INNODB_TRX 根据这个事务的线程ID（trx_mysql_thread_id）: 从上图看出对应的mysql 线程：一个94362 （第二个正在等待锁）另一个是93847(第一个update 正在执行 没有提交事务) 可以使用mysql命令：kill  线程id       杀掉线程 期间如果并未杀掉
mysql 查询开启事务
zhaoyangjian724的专栏
02-24 1633
select @@pseudo_thread_id 88055009 SELECT NOW(), (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec, b.id, b.user, b.host, b.db, a.trx_query, b.COMMAND FROM information_schema.innodb_trx a INNER...
2024最新版mysql数据库表的查询操作-总结
最新发布
2401_86940371的博客
09-12 2207
这就要根据不同的业务需求了，就比如，order和customers，顾客可以有订单也可以没订单，现在需要知道所有顾客的下单情况，而我们不能够只查询出有订单的用户，而把没订单的用户丢在一边不显示，这个就跟我们的业务需求不相符了，有人说，既然知道了有订单的顾客，通过单表查询出来不包含这些有订单顾客，不就能达到我们的要求吗，这样是可以，但是很麻烦，如何能够将其一起显示并且不那么麻烦呢？这就是分组的意思， 在上面对s_id进行查询的时候，发现很多重复的值，我们也就可以对它进行分组，将相同的值分为一组，
mysql事务查询
weixin_46729014的博客
05-06 716
使用undo log日志实现，原子性要么都成功，要么都失败，会记录每一次的操作记录的undo log日志，后面发生异常时在从undo log把事物回滚掉。：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务所干扰，多个并发事务之间要相互隔离。4.事务的一致性是通过原子性，持久性，隔离性来实现的。3.事务的隔离性是通过(读写锁+MVCC)来实现的。2.事务的持久性性是通过redolog来实现的。1.事务的原子性是通过undolog来实现的。：redo log日志实现。
[笔记]MySQL查看事务信息
永远相信美好的事情即将发生
10-10 6691
笔记记录了如何使用 SQL命令查看事务信息
数据库死锁问题解决
08-31 715
数据库执行超时处理办法 一般数据库执行超时 尝试以下方法 显示所有活跃连接 SHOW FULL PROCESSLIST 查询数据库死锁 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; lock_id：锁 ID。 lock_trx_id：拥有锁...
MySQL多表查询、事务和管理用户及授权
05-09
本文将深入探讨MySQL中的多表查询、事务处理以及用户管理和权限授权。 **一、多表查询** 在数据库设计中，往往需要处理多个相关表的数据。MySQL提供了多种多表查询方法，包括： 1. **JOIN操作**：JOIN允许你将两...
MySQL 如何查询当前最新事务ID
09-08
总的来说，MySQL中查询当前最新事务ID是事务管理和性能优化中不可或缺的技能，其主要通过执行SHOW ENGINE INNODB STATUS命令和查询INFORMATION_SCHEMA.INNODB_TRX表来实现。掌握这些方法将有助于高效管理和维护MySQL...
MySQL(多表查询 索引 事务 视图)
m0_51318597的博客
09-13 597
对表中字段的约束 级联删除更新 多表查询 自连接查询 索引 事务 视图
MySQL数据库设计概念（多表查询&事务操作）
来了点个赞再走...
05-22 4084
数据库设计概念以及表与表之间的关系，连接查询和嵌套查询，MySQL数据库事务操作。。。
Spring事务和MySQL事务详解面试
热门推荐
用文字记录下每一个瞬间的感动和每一次思考的火花
09-10 23万+
文章目录数据库事务事务是什么事务的四大特性MySQL事务隔离级别查看MySQL当前事务隔离级别MySQL默认操作模式为自动提交模式JDBC处理事务Spring事务Spring的事务传播PROPAGATION_REQUIREDPROPAGATION_SUPPORTSPROPAGATION_MANDATORYPROPAGATION_REQUIRES_NEWPROPAGATION_NOT_SUPPORTEDPROPAGATION_NEVERPROPAGATION_NESTEDSpring事务的隔离级别Spring
mysql 查询正在执行的事务以及等待锁 常用的sql语句
菜菜
05-16 7万+
mysql 查询正在执行的事务以及等待锁 常用的sql语句
mysql 查询正在进行的事务
stephen
06-08 1万+
SELECT * FROM information_schema.innodb_trx ；
mysql 查询+事务
weixin_46767635的博客
11-25 3532
DQL排序查询 /* 默认升序排序 desc -- 降序排序 asc -- 升序排序 */ select * from emp order by sal; DQL聚合函数 作用：将一列数据作为一个整体，进行纵向的计算 语法格式： select 聚合函数(字段名) from 表名 [where 条件] /* - count() 统计个数 - sum() 求和 - max() 求最大值 - min() 求最小值 - avg() 求平均数 */ s
MySQL基础- 多表查询 和 事务
qq_72343101的博客
05-24 1433
指从多张表中查询数据-- 直接这样查询就会使表1.表2的数据全部相乘 -- 例如，表1为员工表，有10个员工（10条数据），表2为部门表，有6个部门（6条数据）， -- 那么直接使用下面的方式查询出的结果就会有60条数据，也就是所谓的笛卡尔积 select 字段列表 from 表 1 ，表 2. . .;笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况，所以，在进行多表查询时需要消除无效的笛卡尔积，实际的消除方法就使用where条件，使多表中关联的外键进行联系。
数据库 查询 事务
夜观星空的博客
04-18 617
数据库的查询 SQLitedatabase中提供了query()方法用于对数据进行查询， private void queryData(){ /* 第一个参数表名 * 第二个参数指定 要查询哪些列，如果要在cursor里获取没有指定的列，将会报错。(null时表示查询所有列) * 第三四个参数用于约束查寻满足该条件的行(一行或多行)
Mysql 事务
黄飞Gary
04-03 265
        SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。 一、Read Uncommitted（读取未提交内容） 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（D...
MySQL查询+事务
qq_45562888的博客
07-13 430
目录子查询模糊查询exists事务 子查询 子查询是在SQL语句中嵌套使用一套select语句 它是先将内层的子查询语句执行完之后再执行外层的父查询语句 in和=：in是将子查询语句的结果整合到父查询语句中进行一一比较；=是在子查询语句只返回一个数据时可以进行比较 #删除年龄比肖梅大的学生信息 DELETE FROM student WHERE borndate<(SELECT borndate FROM student WHERE `name`='肖梅'); #查询s1的学生信息 SELECT
mysql8查看大事务
qq_39839075的博客
06-11 2848
所以我们要通过mysql的其他表查询这个大事务的详情，尤其是大事务的sql语句，进而知道是在做什么业务。大事务的特点是执行时间长，长期占有锁不释放，导致其他想操作同一行数据的线程阻塞，如果客户端设置了超时时间，超时后，客户端进行重试，又会申请一个mysql线程，然后再阻塞，最终会造成整个mysql库的线程枯竭，整个mysql库不可用，危害极大。至此，我们就得到了大事务的sql语句，接下来，可以根据sql语句对应一下业务模块，进而得出这个大事务能不能kill。很简单，就搞一张表，一个主键，一个业务字段。
MySQL多表查询与事务的操作详解
MySQL是一种非常流行的关系型数据库管理系统，它可以进行多表查询和事务操作。在进行MySQL多表查询之前，我们需要回顾一下DQL查询的基础知识。DQL查询是一种数据查询语言，常用的排序子句是order by。其中，asc表示...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值