多表和事务

1 三范式
2 多表查询
多表表的记录组成一条完整的信息

准备数据

-- 多表查询

-- 创建部门表
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 确定查询的表
SELECT * FROM emp e,dept d;
-- 1.2 确定过滤条件
SELECT * FROM emp e,dept d WHERE e.dept_id = d.id AND e.name = '唐僧';
-- 1.3 确定显示字段
SELECT e.id,e.name,e.gender,e.salary ,d.name FROM emp e,dept d WHERE e.dept_id = d.id AND e.name = '唐僧';
-- 显示内连接
SELECT * FROM emp e INNER JOIN dept d;
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id AND e.name = '唐僧';
-- 推荐
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name = '唐僧';
2.3 外连接
左外连接【推荐】
展示join左表的全部记录,并且拿左表的记录去匹配右表的记录,若匹配上显示右表记录,没匹配上显示null

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

左外连接

-- 查询所有员工信息及对应的部门名称
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
右外连接
展示join右表的全部记录,并且拿右表的记录去匹配左表的记录,若匹配上显示左表记录,没匹配上显示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 查询工资最高的员工是谁?
SELECT * FROM emp WHERE salary =(SELECT MAX(salary) FROM emp);
-- 2 查询工资小于平均工资的员工有哪些?
SELECT AVG(salary) FROM emp;
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
子查询结果为单列多行

子查询结果为单列多行

-- 1 查询工资大于5000的员工,来自于哪些部门的名字
SELECT dept_id FROM emp WHERE salary >5000;
SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
-- 2 查询开发部与财务部所有的员工信息
SELECT id FROM dept WHERE NAME IN('开发部','财务部');
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME IN('开发部','财务部'));
-- 拓展
SELECT * FROM emp e, dept d WHERE e.dept_id = d.id AND d.name IN('开发部','财务部');
子查询结果为多列多行

子查询结果为多列多行

-- 1 查询出2011年以后入职的员工信息,包括部门名称
SELECT * FROM emp WHERE join_date >'2011-1-1';

SELECT FROM (SELECT FROM emp WHERE join_date >'2011-1-1') e INNER JOIN dept d ON e.dept_id =d.id;

-- 拓展
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.join_date > '2011-1-1';

总结

子查询结果只要是单列,肯定在where后作为条件

格式:select ... from 表名 where 字段= |in (子查询);

子查询结果只要是多列,肯定在from后作为虚拟表

格式:select ... from (子查询) as 表别名 where 条件;

3 多表案例
准备数据

-- 多表案例

-- 部门表
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,
hisalary INT
);

-- 添加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 ;

总结:
1 多表查询一定会出现笛卡尔积
2 消除笛卡尔积的规律:二张1个条件,三张表2个条件(条件=表个数-1)
3 多表查询
确定表
过滤条件
确定字段

-- 4 查询经理(职位 条件)的信息。显示员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
-- 4.1 确定表
SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade;
-- 4.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;
-- 4.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 ;

-- 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 = '经理';

-- 5 查询出部门编号、部门名称、部门位置、部门人数
-- 5.1 分组+聚合 获取部门人数
SELECT
dept_id,
COUNT(dept_id) AS total
FROM
emp
GROUP BY dept_id ;

-- 5.2 子查询作为虚拟表与 部门表关联
SELECT

  • FROM

(SELECT

dept_id,
COUNT(dept_id) 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(dept_id) 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);
4.2 事务操作
手动开启事务
sql 描述
start transaction | begin 开启事务
rollback 回滚事务
commit 提交事务
转账成功

-- 开启事务
start transaction;
-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- 小路加钱
UPDATE account SET balance = balance+100 WHERE id = 2;
-- 提交事务
commit;
转账失败

-- 开启事务
start transaction;
-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- 发生故障
-- 回滚事务
rollback;
自动开启事务
mysql数据库默认一条语句一个事务

查看是否开启自动提交事务

show variables like '%commit%';

--
set autocommit = 1 -- true on 开启自动提交
set autocommit = 0 -- false off 关闭自动提交
转账失败

-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- 事务回滚
rollback;
4.3 事务原理

4.4 回滚点
事务开启之后,一部分代码执行成功,设置一个回滚点,后续代码报错了,直接到回滚点,保证之前的代码成功提交

设置回滚点

格式:savepoint 回滚点名;

到回滚点

格式:rollback to 回滚点名;

转账

前二次成功,设置回滚点,后续报错了,保证前二次成功提交

-- 开启事务
begin;
-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- 设置回滚点
savepoint ojbk;
-- 乃亮扣钱
UPDATE account SET balance = balance-100 WHERE id = 1;
-- xxxxx
-- 到回滚点
rollback to ojbk;
-- 提交事务
commit;
4.5 事务特性
特性 描述
原子性:A 事务是指逻辑上的一组操作单元,组成这组操作的各个单元,要么都成功,要么都失败;
一致性:C 事务执行前后,保证数据的完整性;
隔离性:I 一个事务中,尽量不被其他事务干扰;
持久性:D 事务一旦提交,同步数据库,不再发生改变;
不考虑隔离性出现什么问题

脏读:一个事务读取到另一个事务未提交的数据

不可重复读:一个事务二次读取的内容不一致(update)

虚读、幻读:一个事务二次读取的数量不一致(insert)

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

从性能角度看:1>2>3>4

效果演示【看一看】
查看mysql数据库的隔离级别

show variables like '%isolation%';
==临时==修改数据库隔离级别

set session transaction isolation level 级别字符串
准备二个事务,需要使用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;

update account set balance =886 where id =1;

Last modification:July 29th, 2019 at 05:37 pm

Leave a Comment