数据库多表和事务

多表和事务

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;

发表评论