Day11_范式+多表查询+事务及其隔离级别+JDBC入门

一、数据库三大范式

1、1NF：数据库表中的每一列不可在拆分原子数据项，必须独立的列；

2、2NF：在1NF的基础上，数据库表中的非关键字段必须完全依赖于主键字段，完全依赖是指不能存在仅依赖主键一部分的列；

3、3NF：在2NF基础上，任何非主列不得传递依赖于主键；

二、多表查询

1、操作步骤：

1）需要几张表（表名是什么）                        2）查询指定表的中那些字段       

3）这些表之间的关联关系是什么

2、多表查询分类（三种）：

1、内连接

        1）隐式内连接（推荐）：看不到join关键字，使用where条件；

语法：select 字段列表 from 表名1,表名2 where 连接条件（选择条件）;
select 
  e.`id` '员工编号',
  e.`name` '员工姓名',
  e.`salary` '工资',
  e.`join_date` '入职日期',
  d.`id` '部门编号',
  d.`name` '部门名称' 
from emp e,dept d where e.`dept_id` = d.`id` ;

        2）显式内连接：inner join关键字

语法：select 字段列表 from 表名1 inner join 表名2 on 连接条件; （inner可以省略）
SELECT 
    e.*,
    d.*
FROM emp e INNER JOIN dept d ON e.`dept_id` = d.`id` ;

2、外连接

        1）左外连接（推荐）：将左边表数据（包括了null）以及满足两种表条件的数据全部展示。

注意1：外连接的笛卡尔表与内连接一样，区别在于：

            外连接的每行情况均需展示至少一条，当判断条件一致时，其后为从表对应数据，当判断条件没有一致的情况时，也要显示，显示情况为其后从表数据均为null；

注意2：与内连接不同，外连接必须设置on后的条件；否则无法执行;

内连接只能查询满足连接条件的数据，当数据为null时也应该查询出来

语法：select 字段列表 from 表名1 left outer join 表名2 on 连接条件; （outer可以省略）
SELECT 
  e.`id` '员工编号',
  e.`name` '员工姓名',
  e.`salary` '员工工资',
  e.`join_date` '入职日期',
  e.`dept_id` '部门编号',
  d.`name` '部门名称' 
FROM emp e LEFT outer JOIN dept d ON e.`dept_id` = d.`id` ;

 

        2）右外连接

SELECT 
 d.`name` '部门名称' ,
  e.`id` '员工编号',
  e.`name` '员工姓名',
  e.`salary` '员工工资',
  e.`join_date` '入职日期',
  e.`dept_id` '部门编号'
FROM emp e RIGHT OUTER JOIN dept d ON e.`dept_id` = d.`id` ;

 

3、子查询：select语句嵌套select语句（三种方式）：查询的效率比较低

        1）常用where条件使用比较运算符

查询表中工资最大的员工数据
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp) ;
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp) ;

        2）使用in语句

SELECT 
  * 
FROM emp WHERE emp.`dept_id` IN 
(SELECT id FROM dept WHERE NAME = '市场部' OR NAME = '财务部') ;

        3）使用一条select语句的结果作为“虚表”和其他表查询

需求:查询入职日期大于"2020-01-14"的员工信息以及部门名称信息
虚表查询
SELECT t.*,d.name '部门名称' 
FROM
  (SELECT * FROM emp WHERE join_date > '2020-01-14') t 
  LEFT OUTER JOIN dept d ON t.dept_id = d.id ;
升级方式：
SELECT
    e.*,
    d.`name` '部门名称'
FROM
    emp e,dept d 
WHERE e.`dept_id` = d.`id` AND e.`join_date` >'2020-01-14';

三、数据库的事务

隔离性这块比较乱，详见：

数据库的隔离级别有哪些？_想吃披萨的博客-CSDN博客_数据库的隔离级别有哪些?

1、什么是事务？

        事务是作为一个单元的一组有序的数据库操作。

         如果组中的所有操作都成功，则认为事务成功，即使其中只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务回滚，改事务的所有操作都将取消。

        事务有四大特性（ACID）:

        原子性(atomicity)：使用事务管理的业务操作(执行多个sql),要么同时执行成功,要么同时失败。

        一致性(consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性(isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

事务隔离分为不同级别，包括：

读未提交（Read uncommitted）：读未提交便会读到

读提交（read committed）：读提交了便会读到

可重复读（repeatable read）：读提交了也不会读到，但是真实数据已经更改，会造成幻读；

串行化（Serializable）：一个事务执行时，另一个事务无法。

        持久性(durability)：针对增删改操作,只要提交事务,即使关机了,数据永久保存!
2、隔离级别及其作用

隔离级别共有四种：级别从低到高，安全性从低到高，执行效率从高到低；

read uncommitted：读未提交, 会导致最严重的问题 "脏读"(一个事务读到另一个没有提交事务)

即一个事务执行一行业务，另一个事务能立马反应查询到，不管哪怕第一个事务之后是不是会回滚。

read committed：读已提交,可以有效防止脏读,但是会现在一个问题"不可重复读"(在提交事务之前和事务,数据不一致!)

即一个事务执行完并提交后，另一个事务能立马反应查询到，造成另一个事务在执行时数据读取值会更改。

repeatable read：可重复读(mysql默认隔离级别) , 可以有效防止脏读以及不可重复读,会出现"幻读"。

即一个事务执行完并提交后，另一个事务不能立马反应查询到，在执行过程中数据值不会变化。

幻读情况：

1、两个事务同时执行时，如果最开始执行了一次读操作，则当一个事务执行完并提交，对数据进行更改了，未执行完的事务查询值仍然会为上次读到的值，但是实际数据已经更改，若对数据进行操作，则为对真实数据操作，操作更改的值重新读将读到真实值（哪怕他在事务中）；

2、两个事务同时执行时，且均未提交，一个事务修改了值，则另一个事务无法对该值进行修改，知道该事物回滚或者提交后，才有代码执行后的返回值；

serializable：序列化，级别最高,安全性最高,效率低!

一个事务执行表中数据时，另一个事务连查询都不能查询到数据；即锁表

事务的使用方法：

start transaction;
业务代码块1;
业务代码块2;
//如果出现业务错误，则使用回滚；
rollback;
//如果所有业务均正确执行，则使用提交；
commit;

数据库隔离级别查询及更改：

数据库隔离级别查询：
    mysql5.1以上至5.7查询事务的隔离级别：SELECT @@tx_isolation;
    mysql8查询事务的隔离级别：SELECT @@transaction_isolation;
数据库隔离级别设置：
    set global transaction isolation level 隔离级别名称;