mysql：多表查询与事务

最新推荐文章于 2024-07-14 17:38:52 发布

你的pizza到了

最新推荐文章于 2024-07-14 17:38:52 发布

阅读量394

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_38353993/article/details/82899847

版权

mysql 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

1、三范式

1.1 什么是范式

范式就是设计数据库的表的规则

通过科学规范的设计规则，可以优化数据库的存储性能

1.2 范式的基本分类

目前关系型数据库有六种范式：我们程序员一般只需要掌握前三种

1.3 一二三范式

在这里插入图片描述

总结：

1）、第一范式：每一个字段不可进行再次拆分

2）、第二范式：一张表只描述一件事情；每个表中的每个字段都依赖于主键

3）、第三范式：从表的外键必须使用在主表的主键

2、多表查询

多表的记录组成一条完整的信息

准备数据

-- 多表查询
create database db_day03;
use db_day03;
-- 创建部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

2.1 笛卡尔积【了解】

多表查询每一条记录进行组合；多表记录的乘积；又称为交叉连接

SELECT * FROM emp,dept;
SELECT COUNT(*) FROM emp,dept;

2.2 内连接

拿左表的记录去匹配右表的记录，若匹配上就显示（交集）

隐式内连接

-- 格式
select ... from 左表,右表 where 左表(员工).外键=右表(部门).主键;

显示内连接【推荐】

-- 格式
select ... from 左表 [inner] join 右表 on 左表(员工).外键=右表(部门).主键;

# 内连接
-- 查询唐僧的 id，姓名，性别，工资和所在部门名称
-- 隐式内连接
-- 1.1 确定查询表 -- 1.2 确定连接条件
SELECT * FROM emp AS e,dept AS d WHERE e.dept_id = d.id;
-- 1.3 确定显示字段
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp AS e,dept AS d WHERE e.dept_id = d.id;
-- 1.4 确定业务条件
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp AS e,dept AS d WHERE e.dept_id = d.id AND e.name='唐僧';

-- 显示内连接
-- 不推荐  一般on 作为连接条件
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id=d.id AND e.name='唐僧';
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id=d.id  WHERE e.name='唐僧';

2.3 外连接

a）左外连接【推荐】

展示左表的全部记录，并拿左表的记录去匹配右表的记录，若匹配上显示右表记录，没匹配上就显示NULL

-- 格式
select ... from 左表 left [outer] join 右表 on 条件;

# 左外连接
-- 查询所有员工信息及对应的部门名称
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
-- 查询所有部门及对应的员工信息
SELECT * FROM dept d LEFT JOIN emp e ON   d.id=e.dept_id;

b）右外连接

展示右表的全部记录，并拿右表的记录去匹配左表的记录，若匹配上显示左表记录，没匹配上就显示NULL

-- 格式
select ... from 左表 right [outer] join 右表 on 条件;

# 右外连接
-- 查询所有部门及对应的员工信息
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

在这里插入图片描述

2.4 子查询

一条select查询的结果，作为另一条select语法的一部分

子查询结果为单值

# 子查询结果为单值
-- 1 查询工资最高的员工是谁？ 
-- 1.1 查询最高工资
SELECT MAX(salary) FROM emp;
-- 1.2 查询员工信息
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
-- 2 查询工资小于平均工资的员工有哪些？
-- 2.1 查询平均工资
SELECT AVG(salary) FROM emp;
-- 2.2 查询小于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

子查询结果为单列多行

# 子查询结果为单列多行
-- 1 查询工资大于5000的员工，来自于哪些部门的名字
-- 1.1 查询工资大于5000的员工
SELECT dept_id FROM emp WHERE salary >5000;
-- 1.2 查询对应的部门名称 1 2
SELECT NAME FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000) ;
  
-- 2 查询开发部与财务部   所有的员工信息
-- 2.1 查询开发部和财务部
SELECT id FROM dept WHERE NAME IN('开发部','财务部');
-- 2.2 查询对应的员工信息 1 3
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME IN('开发部','财务部'));

子查询结果为多列多行

# 子查询结果为多列多行
-- 1 查询出2011年以后入职的员工信息，包括部门名称
-- 1.1 查询出2011年以后入职的员工信息   
-- 查询结果 就是一张虚拟表
SELECT * FROM emp WHERE join_date > '2011-01-01';
-- 1.2 通过查询作为虚拟表 关联部门
SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-01-01') AS e INNER JOIN dept d ON e.dept_id = d.id;

-- 拓展  关联二张表 在过滤条件
-- 1.1 确定查询表
SELECT * FROM emp e INNER JOIN dept d;
-- 1.2 确定连接条件
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
-- 1.3 确定显示字段
-- 1.4 确定业务条件
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.join_date> '2011-1-1';

总结

子查询结果为单列，肯定在where后面作为条件使用

子查询结果为多列，肯定在from后面作为虚拟表使用

3、多表案例

进入企业开发，一般我们的业务需求都会涉及多表查询，来一波4张表，起始甭管几张表查询它都是有规律可循

准备数据

-- 多表案例
create database db_day03_1;
use db_day03_1;
-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

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

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

-- 员工表
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)
);

-- 添加员工
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);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,
  losalary INT, -- low salary 
  hisalary INT -- high salary
);

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

多表分析

在这里插入图片描述

练习

-- 1 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
-- 1.1 确定查询 员工和职务
SELECT * FROM emp e INNER JOIN job j;
-- 1.2 确定连接条件 
SELECT * FROM emp e INNER JOIN job j ON e.job_id = j.id;
-- 1.3 确定显示字段
SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e INNER JOIN job j ON e.job_id = j.id;

-- 2 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 2.1 确定查询表 员工 职务 部门
SELECT * FROM emp e INNER JOIN job j  INNER JOIN dept d;
-- 2.2 确定连接条件
-- 不推荐
SELECT 
  * 
FROM
  emp e 
  INNER JOIN job j 
  INNER JOIN dept d 
    ON e.job_id = j.id 
    AND e.dept_id = d.id ;
-- 推荐
SELECT 
  * 
FROM
  emp e 
  INNER JOIN job j 
    ON e.job_id = j.id 
  INNER JOIN dept d 
    ON e.dept_id = d.id ;
    
-- 2.3 确定显示字段
SELECT 
  e.id,
  e.ename,
  e.salary,
  j.jname,
  j.description,
  d.dname,
  d.loc 
FROM
  emp e 
  INNER JOIN job j 
    ON e.job_id = j.id 
  INNER JOIN dept d 
    ON e.dept_id = d.id ;

-- 3 查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 3.1 确定查询表 员工 职务 部门 工资等级
SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s;
-- 3.2 确定连接条件
SELECT * FROM emp e INNER JOIN job j ON e.job_id = j.id INNER JOIN dept d ON e.dept_id = d.id INNER JOIN salarygrade s ON e.salary BETWEEN s.losalary AND s.hisalary;
-- 3.3 确定显示字段
SELECT 
  e.id,
  e.ename,
  e.salary,
  j.jname,
  j.description,
  d.dname,
  d.loc,
  s.grade
FROM
  emp e 
  INNER JOIN job j 
    ON e.job_id = j.id 
  INNER JOIN dept d 
    ON e.dept_id = d.id 
  INNER JOIN salarygrade s 
    ON e.salary BETWEEN s.losalary 
    AND s.hisalary 
   ORDER BY e.salary;

总结

1 多表查询会产生笛卡尔积

2 消除笛卡尔的规律：2张表1个条件，3张表2个条件（条件=表个数-1）

3 步骤

确定查询表

确定连接条件

确定显示字段

-- 4.4 确定业务条件
SELECT 
  e.id,
  e.ename,
  e.salary,
  j.jname,
  j.description,
  d.dname,
  d.loc,
  s.grade
FROM
  emp e 
  INNER JOIN job j 
    ON e.job_id = j.id 
  INNER JOIN dept d 
    ON e.dept_id = d.id 
  INNER JOIN salarygrade s 
    ON e.salary BETWEEN s.losalary 
    AND s.hisalary 
  WHERE j.jname = '经理'  
   ORDER BY e.salary;

-- 5 查询出部门编号、部门名称、部门位置、部门人数
-- 5.1 找出员工表 每个部门人数 分组+聚合函数
SELECT dept_id,COUNT(*) FROM emp GROUP BY dept_id;
-- 5.2 把部门人数作为虚拟表 关联 部门
SELECT * FROM (SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id) AS e INNER JOIN dept d ON e.dept_id = d .id;
-- 5.3 确定显示字段
SELECT d.id,d.dname,d.loc,e.total FROM (SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id) AS e INNER JOIN dept d ON e.dept_id = d .id;

4、事务安全

4.1 概述

事务是指逻辑上的一组操作单元，组成这组操作的各个单元，要么都成功，要么都失败

准备数据

-- 事务转账案例

-- 创建数据表
CREATE TABLE account ( -- 账户
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE -- 余额
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张雨绮', 1000), ('袁巴元', 1000);

模拟转账

-- 雨绮扣钱  -100 
-- mysql 数据库 一条sql语句就是一个事务
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 机器故障了

-- 巴元收钱  +100
UPDATE account SET balance = balance + 100 WHERE id = 2;

4.2 事务操作

a）手动提交事务

SQL	描述
start transaction	开启事务
rollback	回滚事务
commit	提交事务

模拟转账成功

-- 开启事务
start transaction;
-- 雨琦扣钱 -100
update account set balance=balance-100 where id =1;
-- 巴元加钱 +100
update account set balance=balance+100 where id =2;
-- 提交事务
commit;

模拟转账失败

-- 开启事务
begin;
-- 雨琦扣钱 -100
update account set balance=balance-100 where id =1;
-- 机器故障
-- 回滚事务
rollback;
-- 巴元加钱 +100
update account set balance=balance+100 where id =2;

b）自动提交事务

MySQL数据库默认一条sql语句一个事务

查看MySQL是否开启自动提交

-- 格式
show variables like '%commit%';

临时修改自动提交为关闭

-- 关闭  off - false - 0
set autocommit=0;
-- 开启  on - true - 1
set autocommit=1;

模拟转账失败

-- 雨琦扣钱 -100
update account set balance=balance-100 where id =1;
-- 机器故障
-- 回滚事务
rollback;

4.3 事务原理

在这里插入图片描述

4.4 回滚点

事务开启后，当我们一部分操作成功，添加一个回滚点，后续操作报错了，直接到回滚点，保证之前的操作能够成功提交

添加一个回滚点

-- 格式
savepoint 回滚点名;

直接到回滚点

-- 格式
rollback to 回滚点名;

模拟雨琦扣钱5次，2个成功了，保证能够提交

-- 开启事务
begin;
-- 雨琦扣钱 1
update account set balance=balance-100 where id =1;
-- 雨琦扣钱 2
update account set balance=balance-100 where id =1;
-- 添加回滚点
savepoint ok;
-- 雨琦扣钱 3
update account set balance=balance-100 where id =1;
-- 雨琦扣钱 4
update account set balance=balance-100 where id =1;
-- 机器故障
-- 到回滚点
rollback to ok;
-- 提交事务
commit;

4.5 事务特性【ACID】（面试常问）

特性	描述
原子性：Atomicity	事务是指逻辑上的一组操作单元，组成这组操作的各个单元，要么都成功，要么都失败
一致性：Consistency	事务执行前后，保证数据的完整性
隔离性：Isolation	一个事务尽量不要被其他事务所干扰
持久性：Durability	事务一旦成功提交，同步数据库文件，不会再发生修改

a）隔离性产生的问题

脏读：一个事物中读取到另一个事务未提交的数据【防止】
不可重复读：一个事物中二次读取的内容不一致（update）
虚读、幻读：一个事物中二次读取的数量不一致（insert、delete）

b）mysql数据库隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

从安全角度来看：4>3>2>1

从性能角度来看：1>2>3>4

综合考虑：2、3

c）效果演示

当前数据库隔离级别

-- 格式
show variables like '%isolation%';

临时修改数据库的隔离级别

-- 格式
set session transaction isolation level 级别字符串;

准备2个事务，使用dos窗口 A窗口左侧、B窗口右侧

演示脏读

需要设置数据库隔离级别为 read uncommitted

set session transaction isolation level read uncommitted;

在这里插入图片描述

解决脏读

需要设置数据库隔离级别为 read committed

set session transaction isolation level read committed;

在这里插入图片描述

解决不可重复读

需要设置数据库隔离级别为 repeatable read

set session transaction isolation level repeatable read;

在这里插入图片描述

演示串行化

需要设置数据库隔离级别为 serializable

set session transaction isolation level serializable ;

在这里插入图片描述

复习

能够理解三大范式
能够使用内连接进行多表查询
能够使用左外连接和右外连接进行多表查询
能够使用子查询进行多表查询
能够理解多表查询的规律
能够理解事务的概念
能够说出事务的原理
能够在MySQL中使用事务
能够理解脏读,不可重复读,幻读的概念及解决办法

你的pizza到了

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录