mysql数据库入门5

1.事务及其特性

当执行多条mysql语句或者查询多个表时,启用事务,使他们同时成功或者同时失败

ACID:
原子性（Atomicity）:每个事务都是一个整体,不可再拆分,事务中sql语句要么都执行成功,要么都执行失败

一致性（Consistency）:事务在执行前的数据库的状态与执行后的数据库的状态保持一致,如转账前两个人总钱数是2000,转账后两个人的总钱数还是2000;

隔离性（Isolation）:事务和事务之间不应该互相影响,执行时保持隔离的状态

持久性（Durability）:一旦事务执行成功,对数据库的修改是持久的,就算关机也保存下来了

2.事务的隔离级别

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

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

不可重复读:
一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这 是事务 update 时引发的问题

幻读:
一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是 insert 或 delete 时引发的问题

查询数据库默认隔离级别:

SELECT @@transaction_isolation;

3.数据库的三大范式

第一范式(1NF):表的每一列不可被拆分
例子:如姓名一列不可被拆分中文名和英文名

第二范式(2NF):在第一范式的基础上,非主键列必须绝对依赖于主键列,一张表只描述一个事物
例子:如学生基本信息中不可以出现学校图书表相关的信息

第三范式(3NF):在2NF基础上,数据库的每列不得传递依赖于主键
例子:一个学生可以选择多个课程,一个课程可以被多个学生选择

4.多表查询

创建一个员工表-

CREATE TABLE emp ( 
 id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
 NAME VARCHAR(10),    -- 员工名称
 gender CHAR(1), -- 性别
 salary DOUBLE, -- 工资
 join_date DATE, -- 入职日期
 dept_id INT,  -- 部门编号
 FOREIGN KEY (dept_id) REFERENCES dept(id)-- 外键，关联部门表(部门表的主键)
 )

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,'2020-08-20',2); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女 ',5000,'2020-10-07',3);
 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女 ',4500,'2021-01-14',1);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('高圆圆','女 ',16000,'2022-05-17',1);

笛卡尔积

查询多张表:查询员工表和部门表信息 注意事项:如果使用这种语法 select 字段列表 from 表名1,表名2;
会出现"笛卡尔乘积",原因:两张表是有关联关系,就是员工表的dept_id要关联部门表id A表的记录数 *B表的记录数 = 总记录数

多表查询分类三种

1)内连接

	隐式内连接---看不到join关键字,使用where条件

内连接之隐式内连接 :

select 字段列表 from 表名名1,表名名2 where  连接条件 ;

1:查询哪张表
2:查询哪些字段
3:表和表之间的关联关系:连接条件

- 需求:查询员工表的id以及name,工资,入职日期以及关联同时查询部门表的部门编号以及部门名称


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` ;

显示内连接—inner join 关键字:inner可以省略

select 字段列表 from 表名名1 inner join 表名名2   on 连接条件

-- 查询所有员工以及所有的部门信息
SELECT 
    e.*,
    d.*
FROM
  emp e
INNER JOIN dept d 
ON 
   e.`dept_id` = d.`id` ;

2)外连接

上面这种情况,查询出来只是查询来满足连接条件的数据 dept_id和dept的id一致的
– 但是没有部门的员工应该查询出来—使用外连接
– 左外连接和右外连接
– 左外连接:将左边表数据以及满足两种表条件的数据全部展示;
– 语法:select 字段列表 from 表名1 left outer join 表名2 on 连接条件
– outer可以省略

左外连接(推荐)

-- 查询员工的id,name,salary,join_date以及部门编号以及所有部门的名称,没有部门的员工也得展示
SELECT 
  e.`id` '员工编号',
  e.`name` '员工姓名',
  e.`salary` '员工工资',
  e.`join_date` '入职日期',
  e.`dept_id` '部门编号',
  d.`name` '部门名称' 
FROM
  emp e 
  LEFT JOIN dept d 
  ON e.`dept_id` = d.`id` ;
    

右外连接

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语句,

    这种场景实际开发使用很少
      查询的效率比较低
      常用where条件使用比较运算符
      或者使用in集合语句
      或者使用一条select语句的结果作为一个"虚表"和其他表查询

情况1:使用where 条件使用比较运算符

-- 需求:查询工资大于平均工资的员工信息
-- 1)查询平均工资是多少
-- select avg(salary) from emp ;-- 7900
-- 2)查询工资大于7900的员工信息
-- select * from emp where salary > 7900 ;

-- 一步走
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp) ;

子查询第二情况2:使用in集合语句

-- 需求:查询在市场部或者财务部的员工信息
-- 1)先查询出市场部和财务部的id
-- select id from dept where name = '市场部' or name = '财务部' ;
-- 2)查询dept_id是2或者3的员工信息
-- select * from emp where emp.`dept_id` in(2,3) ;

-- 一步走
SELECT 

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

情况三 虚表查询

-- 员工表
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);


查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

SELECT 
	e1.ename '员工姓名',
	e2.ename '领导姓名'
FROM
	(SELECT ename,mgr FROM emp) e1
LEFT OUTER JOIN
	(SELECT id , ename FROM emp) e2
ON 
	e1.mgr = e2.id