mysql学习 week5(b站黑马) 事务

多表查询 练习

数据环境准备：

create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）.
select *
from emp,dept where emp.dept_id=dept.id;
--显式
--select *
--from emp join dept on emp.dept_id = dept.id;

2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name,age,job,d.name
from (select * from emp where age<30) e join dept d
on e.dept_id = d.id;

select e.name , e.age , e.job , d.name from emp e inner join dept d 
on e.dept_id =d.id where e.age < 30;

3). 查询拥有员工的部门ID、部门名称
--显示内连接
select distinct dept_id,dept.name
from dept join emp e on dept.id = e.dept_id;
--隐式内连接
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出
来(外连接)
select *
from (select *from emp where age > 40) e
left join dept on e.dept_id=dept.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id 
where e.age >40 ;

5). 查询所有员工的工资等级
select e.name,s.grade from emp e ,salgrade s 
where e.salary between s.losal and s.hisal order by grade asc;


6). 查询 "研发部" 所有员工的信息及 工资等级
select e.name,e.salary,s.grade from 
    (select * from emp where emp.dept_id=
        (select dept.id from dept where dept.name='研发部')) e ,salgrade s 
where e.salary between s.losal and s.hisal order by grade asc;

select e.name,e.salary,s.grade from emp e ,salgrade s 
where e.salary between s.losal and s.hisal
     and e.dept_id=
        (select dept.id from dept where dept.name='研发部')
order by grade asc;

7). 查询 "研发部" 员工的平均工资
select avg(salary) from emp where emp.id=
                    (select dept.id from dept where dept.name='研发部');


8). 查询工资比 "灭绝" 高的员工信息
select *from emp where emp.salary >
(select salary from emp where name='灭绝');


9). 查询比平均薪资高的员工信息
select *from emp where salary>
(select avg(salary)from emp);


10). 查询低于本部门平均工资的员工信息
select *from emp e,
    (select avg(salary) as sa,dept_id as id from emp group by dept_id) a 
where e.salary<a.sa and e.dept_id=a.id;


11). 查询所有的部门信息, 并统计部门的员工人数
--外连接，有的员工可能没有部门，被忽略
select d.name,count(e.id) as '人数',e.dept_id as'部门id'
from emp e left join  dept d
on d.id=e.dept_id
group by e.dept_id;

--内连接形式，忽略没有部门的员工的信息
select d.name,count(e.id) as '人数',e.dept_id as'部门id'
from emp e,dept d
where d.id=e.dept_id
group by e.dept_id;

12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
--外连接，保留没有课的同学
select student.name,student.no,sc.name as'课程' from student
    left join (select course.name,student_course.course_id,student_course.student_id 
             from course,student_course where student_course.course_id=course.id sc
 on student.id = sc.student_id;
--多次外连接
select student.name,student.no,student_course.course_id,course.name from student
   left join student_course on student.id = student_course.student_id
   left join course on course.id=student_course.course_id;

--内连接方式，忽略没有课的同学
select s.name , s.no , c.name from student s , student_course sc , course c 
where s.id = sc.student_id and sc.course_id = c.id ;

事务

        事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

        比如: 张三给李四转账 1000 块钱，张三银行账户的钱减少 1000 ，而李四银行账户的钱要增加

1000 。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败

事务操作

操作方式一

1） 查看/设置事务提交方式

select @@autocommit;
--查询结果为1，自动提交

set @@autocommit=0;
--将autocommit致1，手动提交

2） 提交事务

COMMIT;

3)    回滚事务

ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为 , 把默认的自动提交修改为了手动提交, 此时我们执行的 DML 语句都不会提交 , 需要手动的执行 commit 进行提交。

操作方式二 

1). 开启事务

START TRANSACTION 或 BEGIN ;

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

事务四大特性

        原子性（Atomicity ）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

        一致性（Consistency ）：事务完成时，必须使所有的数据都保持一致状态。

        隔离性（Isolation ）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立

环境下运行。

        持久性（Durability ）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称 ACID。

并发事务问题

赃读

：一个事务读到另外一个事务还没有提交的数据。

不可重复读

：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读

：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据

已经存在，好像出现了 " 幻影 " 。

 

事务隔离级别  

--查看事务隔离级别：
SELECT @@TRANSACTION_ISOLATION;

--设置事务隔离级别：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
--SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效

事务隔离级别演示部分建议看视频

55. 基础-事务-并发事务演示及隔离级别_哔哩哔哩_bilibili