mySQL多表查询与事务

时间:2019-05-11 10:08:30   收藏:0   阅读:150

一、范式

1. 什么是范式

1.1 什么是范式

1.2 有哪些范式

2. 常用范式

2.1 第一范式1NF

2.2 第二范式2NF

2.3 第三范式3NF

二、==多表查询==

多表查询的技巧:根据一些条件,把多张表关联合成一张表,从而把查询变成单表查询

-- 1.1 查询每个员工的信息,和所有的部门名称
-- 步骤1:确定数据在哪些表里 dept, emp
-- 步骤2:使用关联条件,关联这些表,合成一张表 dept.id = emp.dept_id
-- 步骤3:从关联后的表里,挑、运算、分组、统计 想要的数据
SELECT emp.*, dept.name FROM dept, emp WHERE dept.id = emp.dept_id

1. 迪卡尔积

2. 查询方式

2.1 内连接查询

-- 2.1 查询唐僧的 员工id、姓名、工资、性别、部门名称:  dept, emp; 关联条件:dept.id = emp.dept_id
-- 2.1.1 隐式内连接查询
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept, emp WHERE dept.id = emp.dept_id AND emp.name = ‘唐僧‘;
SELECT e.id, e.name, e.salary, e.gender, d.name deptname FROM dept d, emp e WHERE d.id = e.dept_id AND e.name = ‘唐僧‘;
?
-- 2.1.2 显式内连接查询
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept INNER JOIN emp ON dept.id = emp.dept_id WHERE emp.name = ‘唐僧‘;

 

2.2 外连接查询


-- 3. 外连接查询:查询一张表的全部数据,以及另外一张表的关联数据
-- 3.1 查询所有的员工,以其部门信息
-- 3.1.1 使用左外连接查询
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
-- 3.1.2 使用右外连接查询
SELECT * FROM dept d RIGHT JOIN emp e ON e.dept_id = d.id;
-- 3.2 查询所有的部门,及这个部门里的员工信息
-- 3.2.1 使用左外连接查询
SELECT * FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;
-- 3.2.2 使用右外连接查询
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

2.3 子查询


-- 3. 子查询:一种查询技巧,select嵌套
-- 3.1 查询最大工资的那个员工信息
-- 3.1.1 分步骤查询方式:
SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary = 9000;
-- 3.1.2 把多条语句合并成一条
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
?
-- 3.2 查询工资大于5000的员工 的部门名称
-- 3.2.1 使用外连接方式查询
SELECT d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.2.2 使用子查询方式
SELECT dept_id FROM emp WHERE salary > 5000;
SELECT NAME FROM dept WHERE id IN (1, 2);
?
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);
?
-- 3.3 查询工资大于5000的员工信息和部门名称
-- 3.3.1 使用外连接方式查询
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.3.2 使用子查询方式
-- 步骤1:先查询出来工资大于5000的员工信息:多行多列的结果,相当于一张虚拟表
SELECT * FROM emp WHERE salary > 5000;
-- 步骤2:拿部门表,和工资大于5000的员工信息虚拟表,进行内连接查询
SELECT * FROM dept d, (SELECT * FROM emp WHERE salary > 5000) t  WHERE d.id = t.dept_id;

 

三、事务

1. 事务简介

2. 事务管理

2.1 手动的事务管理


-- 1. 手动事务管理:jack给rose转账100
-- 1.1 开启事务
START TRANSACTION;
?
-- 1.2 jack扣钱100--- 开启事务后,执行数据变更,是在缓存里,没有真正生效
UPDATE account SET balance = balance - 100 WHERE NAME = ‘jack‘;
?
-- 1.3 rose加钱100
UPDATE account SET balance = balance + 100 WHERE NAME = ‘rose‘;
?
-- 1.4 关闭事务:提交事务,事务里所有的数据变更会真正的生效
-- commit;
-- 1.4 关闭事务:回滚事务,事务里所有的数据变更会撤消,不会生效
ROLLBACK;

2.2 自动的事务管理


-- 2. 自动事务提交
-- 2.1 MySql的事务自动提交的开关操作
-- 2.1.1 关闭自动提交:仅仅是本次连接有效
SET autocommit = 0;
-- 2.1.2 查询自动提交的开关
SELECT @@autocommit;
-- 2.1.3 开启自动提交
SET autocommit = 1;
?
?
-- 2.2 自动提交的事务管理
-- 2.2.1 关闭自动提交
SET autocommit = 0;
-- 2.2.2 执行jack扣钱100 -- 数据变更缓存起来了
UPDATE account SET balance = balance - 100 WHERE NAME = ‘jack‘;
-- 2.2.3 执行rose加钱100
UPDATE account SET balance = balance + 100 WHERE NAME = ‘rose‘;
-- 2.2.4 关闭事务:提交事务
-- commit;
-- 2.2.4 关闭事务:回滚事务
ROLLBACK;
?

2.3 事务的回滚点


-- 3.事务的回滚点
-- 3.1 开启事务
START TRANSACTION;
?
-- 3.2 jack扣钱100, 余额成:900
UPDATE account SET balance = balance - 100 WHERE NAME = ‘jack‘;
?
-- 3.3 设置一个回滚点,名称为point1
SAVEPOINT point1;
?
-- 3.4 jack扣钱100,余额成:800
UPDATE account SET balance = balance - 100 WHERE NAME = ‘jack‘;
?
-- 3.5 回滚到回滚点point1
ROLLBACK TO point1;
?
-- 3.5 结束事务
COMMIT;

3. 事务的特性和隔离(概念性的)

3.1 ==事务的ACID四大特性(面试题)==

3.2 事务并发的问题

3.3 事务的隔离级别

隔离级别脏读问题不可重复读问题虚读/幻读问题
read uncommitted
read committed
repeatable read
serializable

3.4 事务并发问题的解决(演示)


-- 1. 查询隔离级别
SELECT @@tx_isolation;
-- 2. 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3.4.1 read uncommitted
3.4.2 read committed
3.4.3 repeatable read
3.4.4 serializable

原文:https://www.cnblogs.com/penghuake/p/10847793.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!