java学习记录三十：MySql基础三：子查询、事务
一、子查询
一、解释
子查询嵌套查询, 查询的语句可以作为另外一个查询语句的条件， 直观一点: 一条语句里面包含了多个select，里面嵌套的那个查询就是子查询
- 一个查询语句的结果作为另一个查询语句的条件
- 有查询的嵌套，内部的查询称为子查询
- 子查询要使用括号
- 子查询结果的三种情况：
- 子查询的结果是一个值的时候
- 子查询结果是单列多行的时候
- 子查询的结果是多行多列
二、三种结果的查询
1.子查询的结果是一个值的时候
子查询结果只要是单个值，肯定在WHERE后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段[= > < <>]（子查询）;
例子：查询工资大于5000的员工，来自于哪些部门的名字
1.查询最高工资是多少
SELECT MAX(salary) FROM emp;
2.根据最高工资到员工表查询到对应的员工信息
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);
例子：查询开发部与财务部所有的员工信息
1.查询平均工资是多少
SELECT AVG(salary) FROM emp
2.到员工表查询小于平均的员工信息
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
2.子查询结果是单列多行的时候
子查询结果只要是单列，肯定在WHERE后面作为条件
子查询结果是单列多行，结果集类似于一个数组，父查询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
例子：查询工资大于5000的员工，来自于哪些部门的名字**
1.先查询大于5000的员工所在的部门id
SELECT dept_id FROM emp WHERE salary > 5000;
2.再查询在这些部门id中部门的名字
SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000);
例子二：查询开发部与财务部所有的员工信息
1.先查询开发部与财务部的id
SELECT id FROM dept WHERE NAME IN('开发部','财务部');
2.再查询在这些部门id中有哪些员工
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));
3.子查询的结果是多行多列
子查询结果只要是多行多列，肯定在FROM后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段
- 例子：查询出2011年以后入职的员工信息，包括部门名称
1.在员工表中查询2011-1-1以后入职的员工
SELECT * FROM emp WHERE join_date > '2011-1-1';
2.查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;
三、总结
1.子查询的结果是单行单列(一个值情况), 一般放在where后面作为条件, 通过=,>,<,<>
select ... from ... where 列 [=><<>...] (子查询)
2.子查询的结果是单列多行, 一般放在where后面作为条件, 通过in
select ... from ... where 列 in (子查询)
3.子查询的结果是多行多列, 一般放在from后面作为虚拟表, 需要给虚拟表取别名
select ... from (子查询) as 别名 where 条件
二、事务
一、解释
事务指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败。
- 操作: zs向李四转账100元
- 组成单元: zs钱-100, ls钱+100
- 操作成功: zs钱900,ls钱1100
- 操作失败: zs钱1000,ls钱1000
- 不可能发生: zs钱900,ls钱1000; zs钱1000,ls钱1100
二、事务的作用
保证一组操作全部成功或者失败。
三、MYSQL进行事务管理
- 自动事务(mysql默认)
- 手动开启一个事务
自动事物一般是一条语句，直接完成，同步到数据库。
手动开启，后续的操纵都是写到临时日志文件上面的，如果临时文件断开，临时文件会自动清空。commit会把操作同步到数据库中，rollback会把临时文件直接清空。
1、自动事务
一条sql语句就是一个事务
-- 场景: zs向ls转账100元
-- zs钱-100 ls钱+100
-- 自动事务管理: MySQL默认就是自动事务管理(自动开启事务,自动提交事务),一条sql语句就是一个事务
update account set money = money - 100 where name = 'zs';
-- 异常
update account set money = money + 100 where name = 'ls';
2.手动开启一个事物
start transaction;开启事务
commit；提交
rollback；回滚
-- 没有异常
start transaction; -- 开启事务
update account set money = money - 100 where name = 'zs'; -- zs钱-100
-- 没有异常
update account set money = money + 100 where name = 'ls'; -- ls钱 +100
commit; -- 提交事务
-- 有异常
start transaction; -- 开启事务
update account set money = money - 100 where name = 'zs'; -- zs钱-100
-- 有异常
update account set money = money + 100 where name = 'ls'; -- ls钱 +100
rollback; -- 回滚事务
第二种方式：设置MYSQL中的自动提交的参数
查看MYSQL中事务是否自动提交
show variables like '%commit%';
设置自动提交的参数为OFF
set autocommit = 0;-- 0:OFF 1:ON
3.回滚点
在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。回滚点不会结束事务。
3.1回滚点的操作语句
--设置回滚点
savepoint 名字
--回到回滚点
rollback 名字
3.2例子
-
将数据还原到1000
-
开启事务
-
让张三账号减3次钱
-
设置回滚点：savepoint three_times;
-
让张三账号减4次钱
-
回到回滚点：rollback to three_times;
总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。
start transaction;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
-- 以上sql语句没有问题
savepoint abc;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
-- 出现异常,回滚到abc回滚点位置
rollback to abc;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
commit;
3.3 应用场景
插入大量的数据的时候. 1亿条数据 需要插入很久. 要求: 1亿条数据是一个整体,要么全部插入成功的 要么都不插入成功.
四、事务特性和隔离级别
1.事物特性
- 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
eg: zs 1000; ls 1000;
zs 给 ls转100
要么都发生zs 900; ls 1100;
要么都不发生zs 1000; ls 1000;
- 一致性（Consistency）事务前后数据的完整性必须保持一致.
eg: zs 1000; ls 1000; 一共2000
zs 给 ls转100
要么都发生zs 900; ls 1100; 一共2000
要么都不发生zs 1000; ls 1000; 一共2000
- 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
eg: zs 1000 给小红 转520, 张三 提交了
- 隔离性（Isolation）事务的隔离性是指多个用户并发操作数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。 简单来说: 事务之间互不干扰
如果不考虑隔离性，会引发下面的问题
| 并发访问问题 | 含义 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复读 | 一个事务中两次读取到的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题 |
| 幻读 | 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题 |
2.事务隔离级别
可以通过设置事物隔离级别解决读的问题
2.1事务四个隔离级别
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
| 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
隔离级别越高，安全性越高，性能(效率)越差。
2.2设置隔离级别
set session transaction isolation level 隔离级别;
eg: 设置事务隔离级别为:read uncommitted,read committed,repeatable read,serializable
set session transaction isolation level read uncommitted;
2.3查询当前事务隔离级别
select @@tx_isolation;
3.总结
- 事务的特性
- 原子性
- 一致性
- 持久性
- 隔离性
- 不考虑隔离性会引出一些问题: 脏读, 不可重复读, 幻读
- 这些问题 可以 隔离级别解决
三、数据库安全性问题的例子
四、数据的备份和还原
在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
1.备份数据库
mysqldump -u用户名 -p密码 数据库 > 文件的路径
2.还原格式
SOURCE 导入文件的路径
注意：还原的时候需要先登录MySQL,并创建数据库和选中对应的数据库
3.使用navicat备份和还原
右键选择数据库-结构和数据-进行备份
右键选择数据库-运行sql文件-进行还原
工作里面一般是运维在处理, 运维处理的话一般使用定时任务自动备份.
五、数据库设计三大范式
一、1NF
数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性
二、2NF
在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。
简而言之,第二范式需要满足:
- 一张表只描述一件事情
- 表中的每一个列都依赖于主键
如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相同数据。
三、3NF
在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足2NF的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则C传递依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列x → 非主键列y
四、总结
1.列不可分割—1NF
2.不能产生局部依赖—2NF
3.不能产生传递依赖–3NF
记住: 一张表只描述一件事,每个列都不能分割,并且每个列都直接依赖主键
125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
