SQL-多表查询-事务

于 2023-06-06 15:18:53 发布
阅读量400 收藏 2
点赞数
分类专栏： sql 文章标签： sql 数学建模 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_50206068/article/details/131066941
版权

SQL-多表查询-事务

在这里插入图片描述

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据

前期表准备

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


	# 创建部门表
	CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

	# 创建员工表
	CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
    );
	-- 添加部门数据
	INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
	-- 添加员工数据
	INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
	('孙悟空','男',7200,'2013-02-24',1),
	('猪八戒','男',3600,'2010-12-02',2),
	('唐僧','男',9000,'2008-08-08',2),
	('白骨精','女',5000,'2015-10-07',3),
	('蜘蛛精','女',4500,'2011-03-14',1),
	('小白龙','男',2500,'2011-02-14',null);

1 多表查询

1.1 内连接查询

  • 语法

    -- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

    内连接相当于查询 A B 交集数据

在这里插入图片描述

  • 案例

    • 隐式内连接

      SELECT emp.NAME, emp.gender, dept.dname FROM emp, dept WHERE emp.dep_id = dept.did;

    • 显式内连接

      SELECT * FROM emp INNER JOIN dept ON emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
SELECT * FROM emp JOIN dept ON emp.dep_id = dept.did;

1.2 外连接查询

  • 语法

    -- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

    左外连接：相当于查询A表所有数据和交集部分数据

    右外连接：相当于查询B表所有数据和交集部分数据

在这里插入图片描述

  • 案例

    • 查询emp表所有数据和对应的部门信息（左外连接）
      select * from emp left join dept on emp.dep_id = dept.did;

      结果显示查询到了左表（emp）中所有的数据及两张表能关联的数据。

    • 查询dept表所有数据和对应的员工信息（右外连接）
      select * from emp right join dept on emp.dep_id = dept.did;

      结果显示查询到了右表（dept）中所有的数据及两张表能关联的数据。

1.3 子查询

  • 概念
    查询中嵌套查询，称嵌套查询为子查询。

  • 例子: 查询工资高于猪八戒的员工信息

    • 第一步：先查询出来 猪八戒的工资
      select salary from emp where name = '猪八戒'
    • 第二步：查询工资高于猪八戒的员工信息
      select * from emp where salary > 3600;
    • 合并：
      select * from emp where salary > (select salary from emp where name = '猪八戒');

  • 子查询根据查询结果不同，作用不同

    • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
    • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
    • 子查询语句结果是多行多列，子查询语句作为虚拟表

  • 案例

    • 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

      -- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';

select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

    • 查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息

      -- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;

1.4 案例

  • 准备表

    DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
	did INT PRIMARY KEY PRIMARY KEY, -- 部门id
	dname VARCHAR(50), -- 部门名称
	loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
	id INT PRIMARY KEY,
	jname VARCHAR(20),
	description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
	id INT PRIMARY KEY, -- 员工id
	ename VARCHAR(50), -- 员工姓名
	job_id INT, -- 职务id
	mgr INT , -- 上级领导
	joindate DATE, -- 入职日期
	salary DECIMAL(7,2), -- 工资
	bonus DECIMAL(7,2), -- 奖金
	dept_id INT, -- 所在部门编号
	CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
	CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
	grade INT PRIMARY KEY,   -- 级别
	losalary INT,  -- 最低工资
	hisalary INT -- 最高工资
);
				
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

  • 需求

    1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

      -- 方式一 ：隐式内连接
SELECT emp.id, emp.ename, emp.salary, job.jname, job.description
FROM emp, job 
WHERE emp.job_id = job.id;

-- 方式二 ：显式内连接
SELECT emp.id, emp.ename, emp.salary, job.jname, job.description
FROM emp
INNER JOIN job ON emp.job_id = job.id;

    2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

      -- 方式一 ：隐式内连接
SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc
FROM emp, job, dept
WHERE emp.job_id = job.id AND dept.id = emp.dept_id;

-- 方式二 ：显式内连接
SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc
FROM emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id;

    3. 查询员工姓名，工资，工资等级

      SELECT emp.ename, emp.salary, t2.*
FROM emp, salarygrade t2
WHERE emp.salary BETWEEN t2.losalary AND t2.hisalary

    4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

      SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc, t2.grade
FROM emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

    5. 查询出部门编号、部门名称、部门位置、部门人数

      SELECT dept.id, dept.dname, dept.loc, t1.count
FROM dept, 
	(SELECT dept_id, count(*) count FROM emp GROUP BY dept_id) t1
WHERE dept.id = t1.dept_id

2 事务

2.1 概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
事务是一个不可分割的工作逻辑单元。

2.2 语法

  • 开启事务

    START TRANSACTION;
或者  
BEGIN;

  • 提交事务

    commit;

  • 回滚事务

    rollback;

2.3 四大特征

  • 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
  • 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
  • 隔离性（Isolation） :多个事务之间，操作的可见性
  • 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;

2.4 案例

  • 准备表

    DROP TABLE IF EXISTS account;

-- 创建账户表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name varchar(10),
	money double(10,2)
);

-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);

  • 添加事务sql

    -- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

    上述sql中执行成功则 提交事务，而出现问题则 回滚事务。
    java中可以抓取异常，没出现异常 提交事务，出现异常 回滚事务。

太阳与我肩并肩
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL（事务）
oYeLuo1234的博客
04-10 283
开启事务： BEGIN /STATR TRANSCATION 提交事务：COMMIT 回滚事务：ROLLBACK 并发事务引发的问题：脏读 不可重复读 幻读 为了解决这个问题引入了隔离级别这个·概念 SET SESSION TRANSCATION ISOLATION LEVEL SERIALIZABLE //设置隔离级别 三个隔离级别（isolation level）： 读未提交Read uncommitted:多个同时在执行的事务，可以读取到其他事务，还处于事务未提交时的数据修改 （读已提交
SQL——事务
Wen先森的博客
06-05 4828
事务是用于保证数据的一致性,它由一组相关的DML（增、删、改）语句，该组的DML语句要么全部成功，要么全部失败。使用事务可以确保数据库的一致性和完整性，避免数据出现异常或不一致的情况。
mysql 查询正在执行的事务以及等待锁 常用的sql语句
az44yao的专栏
02-18 2208
jdbc协议连接数据库：jdbc:mysql://localhost:3306/test jdbc协议连接本地上端口为3306实例下的test数据库，则查询数据表 时不需要加上数据库的名字。mysql中建立一个会话，不是和具体的数据库相连接，而是跟instance建立会话(即在navicat上执行一个查询,可以查询端口下对应的多个数据库，查询时数据库名+数据表名即可)而一个实例可以建立多个数据库，即一个会话可以操作一个实例上的多个数据库。查询mysql数据库中存在的进程。
【建议收藏】数据库 SQL 入门——事务（内附演示）
森月酱的博客
04-11 1395
本次列举了SQL中常用事务相关语句，在实际学习的过程中还需要多学习以及使用才能熟练掌握。
MySQL数据库（四）- 事务和锁
m0_66668867的博客
08-02 414
事务特性，隔离级别，行锁、页锁和表锁，共享锁和排它锁，意向锁，乐观锁和悲观锁，死锁，MVCC
SQL - 事务控制 PDF源文件
最新发布
01-25
SQL - 事务控制 PDF源文件
SQL - 事务控制 MarkDown源文件
01-25
SQL - 事务控制 MarkDown源文件
Javaweb【SQL多表查询、事务、DCL】
12-14
今日内容多表查询：事务DCL： 多表查询： * 查询语法： select 列名列表 from 表名列表 where.... * 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ...
SQL-92标准下载【最新】
06-25
SQL-92标准下载【最新】 这个标准的前身是SQL92 ANSI/ISO标准，而SQL92之前还有一个SQL89 ANSI/ISO标准。 它定义了一种语言（SQL）以及数据库的...不过，这对于查询和应用的可移植性没有多大的意义，这一点你也清楚吗？
Archery SQL审核查询平台-其他
06-12
Archery 定位于 SQL 审核查询平台，旨在提升 DBA 的工作效率，支持多种数据库的 SQL 上线和查询，同时支持丰富的MySQL运维功能，所有功能都兼容手机端操作。 启动： 下载 Releases文件，解压后进入docker-compose...
事务特性、隔离级别及验证、多个事务运行存在的问题
weixin_44569143的博客
03-25 331
一、什么是事务(transaction) 事务就是一堆的逻辑语句+逻辑判断 一个数据库事务由一条或者多条sql语句构成，它们形成一个逻辑的工作单元。这些sql语句要么全部执行成功，要么全部执行失败 事务是保证数据的完整性和一致性的重要手段 注意事项、@@autocommit作用 系统变量@@autocommit默认是打开的，这意味着任何1条DML语句是一个事务，会自动提交(Orac...
sql-查看当前运行的所有事务
blues的博客
12-01 1878
SELECT * FROM information_schema.innodb_trx命令是用来查看当前运行的所有事务： 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务： KILL165667; KILL 后面的数字指的是trx_mysql_thread_id值。 ...
c#多sql操作使用事务
weixin_42542629的博客
04-23 193
private static void ExecuteSqlTransaction(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transactio
mysql简单事务sql语句_mysqli的多条sql语句操作以及简单的事务处理~~~~~
weixin_35133814的博客
02-03 263
首先介绍一下多条sql语句的操作~~~~~普通的方法： $mysqli->query();通知他们执行方法 ： $mysqli->multi_query();拼接sql语句//更新数据(其他的删除，)$sql = "UPDATE zd_admin SET level = 1 WHERE id = 1;";...
数据库多表查询和事务
九门宁生
08-15 164
多表查询： * 查询语法： select 列名列表 from 表名列表 where.... * 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
【Mysql】第4篇--多表查询和事务
检哥说IT的博客
06-26 350
范式: 设计数据库表的规则。 第一范式:每一列不能再拆分,查询出的结果不需要进一步处理,可以直接使用。 第二范式:每张数据库表必须要有主键，可以解决数据冗余(重复)问题 (一张表只能描述一件事情)。 第三范式：从表的外键必须使用主表的主键。.........
mysql多表查询&事务
qq_53347907的博客
02-23 218
mysql多表查询&事务 一、课程目标 1. 【了解】什么是多表查询 2. 【掌握】多表查询的语法 3. 【掌握】多表查询的案例 4. 【了解】DCL语句 5. 【理解】什么是事务 6. 【掌握】事务的四大特征 7. 【理解】事务的隔离级别 二、多表查询 2.1 多表查询概述 同时查询多张表获取到需要的数据组成完整的信息返回给用户。 2.2 多表查询语法 select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 ord
MySQL基础（多表查询、事务）
Jonny_Wang的博客
07-16 215
内容来自黑马课件总结，侵删
mysql：多表查询与事务
qq_38353993的博客
09-29 356
1、三范式 1.1 什么是范式 ​ 范式就是设计数据库的表的规则 ​ 通过科学规范的设计规则，可以优化数据库的存储性能 1.2 范式的基本分类 ​ 目前关系型数据库有六种范式：我们程序员一般只需要掌握前三种 1.3 一二三范式 总结： 1）、第一范式：每一个字段不可进行再次拆分 2）、第二范式：一张表只描述一件事情；每个表中的每个字段都依赖于主键 3）、第三范式：从表的外键必须使用在主表的主键...
mysql80 sql-model
05-09
MySQL 80 SQL模型还具有其他特点，如支持事务处理、多版本并发控制、行级锁定、多主复制、基于角色和多源复制等，使其在大型企业和组织中更加受欢迎。此外，MySQL 80 SQL模型还支持对数据库的故障转移和负载平衡，并...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值