一、查询练习
01
select s.s_id, s.s_name, s.s_birth, s.s_sex, c.s_id, c.c_id, c.s_score 01score ,c1.s_score 02score
from Student s inner join Score c on s.s_id = c.s_id and c.c_id = '01' left join
score c1 on s.s_id = c1.s_id and c1.c_id = '02' where c.s_score > c1.s_score;
03
select s.s_name, s.s_id, avg(c.s_score) cvg from student s inner join score c on s.s_id = c.s_id group by s.s_id having cvg > 60;
04
select s.s_name, s.s_id, avg(c.s_score) cvg from student s inner join score c on s.s_id = c.s_id group by s.s_id having cvg < 60 union select s.s_name, s.s_id, avg(c.s_score) cvg from student s left join score c on s.s_id = c.s_id group by s.s_id having cvg is null;
05
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id,s.s_name name, sum(c.s_score) sum, count(1) count from student s left join score c on s.s_id = c.s_id group by s.s_id;
06
查询"李"姓老师的数量
select substring(t_name,1,1) as name , count(1) as count from teacher where t_name like '李%' group by name;
07
查询学过"张三"老师授课的同学的信息
select t.t_name tname, st.s_name stname, sc.s_score scscore from teacher t left join
Course cou on t.t_id = cou.t_id left join score sc on cou.c_id = sc.c_id left join student
st on sc.s_id = st.s_id where t.t_name = '张三';
08
查询没学过"张三"老师授课的同学的信息 (吴兰，王菊)
- 哪些学生学习 ‘张三’老师的课程 (s_id)
select st.s_id sid from teacher t left join
Course cou on t.t_id = cou.t_id left join score sc on cou.c_id = sc.c_id left join student
st on sc.s_id = st.s_id where t.t_name = '张三';
- 通过查询学生表，通过where使用not int 进行过滤
select * from student where student.s_id not in (select st.s_id sid from teacher t left join
Course cou on t.t_id = cou.t_id left join score sc on cou.c_id = sc.c_id left join student
st on sc.s_id = st.s_id where t.t_name = '张三');
09 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
1. 查询出课程编号为02的学生 sid
select * from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = 02;
2. 查询出课程编码为01的学生， 通过where sid in () 进行过滤
select * from student st1 inner join score sc1 on st1.s_id = sc1.s_id and sc1.c_id = 01 where st1.s_id in (select sc.s_id from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = 02)
10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student st1 left join score sc1 on st1.s_id = sc1.s_id and sc1.c_id = 01 where st1.s_id not in (select sc.s_id from student st left join score sc on st.s_id = sc.s_id and sc.c_id = 02)
11. 查询没有学全所有课程的同学的信息
1. 三张分数表连接，查询过学习过所有课程的学生
select st2.s_id from student st2 inner join score sc2 on st2.s_id = sc2.s_id and sc2.c_id = 03 where st2.s_id in (select sc1.s_id from student st1 inner join score sc1 on st1.s_id = sc1.s_id and sc1.c_id = 01 where st1.s_id in (select sc.s_id from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = 02))
2. 通过not in 进行过滤
select * from student where student.s_id not in (select st2.s_id from student st2 inner join score sc2 on st2.s_id = sc2.s_id and sc2.c_id = 03 where st2.s_id in (select sc1.s_id from student st1 inner join score sc1 on st1.s_id = sc1.s_id and sc1.c_id = 01 where st1.s_id in (select sc.s_id from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = 02)));
12 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
1. 查询出01同学的课程信息 (c_id)
select sc.c_id from score sc inner join student st on sc.s_id = st.s_id where st.s_id = '01'
2. 查询所有学生c_id2
select distinct st1.s_name from student st1 left join score sc1 on st1.s_id = sc1.s_id
3. c_id2 in (c_id) , 有同学报名多门课程，我们可以使用distinct过滤一下
select distinct st1.s_name from student st1 left join score sc1 on st1.s_id = sc1.s_id where sc1.c_id in (select sc.c_id from score sc inner join student st on sc.s_id = st.s_id where st.s_id = '01');
13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
1. 01学生学习的课程id (c_id)，我们把c_id给拼接(concat)到一起
select group_concat(sc.c_id) from student st inner join score sc on st.s_id = sc.s_id and st.s_id = '01';
2. 查询其他学生的信息， 也使用concat拼接到一起，或者使用 in
select st.s_name,group_concat(sc.c_id) cc from student st inner join score sc on st.s_id = sc.s_id group by st.s_id having cc in (select group_concat(sc.c_id) from student st inner join score sc on st.s_id = sc.s_id and st.s_id = '01');
14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
1. 查询出张三讲授的课程，通过课程查询到学生的s_id
select sc.s_id from score sc inner join Course cu on sc.c_id = cu.c_id inner join teacher tc on cu.t_id = tc.t_id where tc.t_name = '张三'
2. not in 进行过滤
select * from student where student.s_id not in (select sc.s_id from score sc inner join Course cu on sc.c_id = cu.c_id inner join teacher tc on cu.t_id = tc.t_id where tc.t_name = '张三');
15. 查询两门及其以上不及格课程的同学的学号，姓名 及 其平均成绩
1.where 分数 < 60 , 通过 group by(s_id) 进行分组， 通过count函数进行过滤
// 60分一下的不加入统计
select st.s_name, st.s_id, avg(sc.s_score) avg where sc1.s_id = st.s_id as avg from student st left join score sc on st.s_id = sc.s_id where sc.s_score < 60 group by st.s_id having count(1) >= 2
// 60分以上的也加入统计
select st.s_name, st.s_id, (select avg(sc1.s_score) from student st1 left join score sc1 on st1.s_id = sc1.s_id) avg where sc1.s_id = st.s_id as avg from student st left join score sc on st.s_id = sc.s_id where sc.s_score < 60 group by st.s_id having count(1) >= 2
2, 某个同学的平均分
select avg(sc1.s_score) from student st1 left join score sc1 on st1.s_id = sc1.s_id where sc1.s_id = '01';
注意： only_full_group_by 在mysql5.7之前默认不开启的，后面就开启了
16 检索"01"课程分数小于60，按分数降序排列的学生信息
select st.s_name, sc.c_id, sc.s_score from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = '01' and sc.s_score < 60 order by sc.s_score desc;
二、注意where和having的使用区别
1. where和having都可以使用的场景
1）select addtime,name from dw_users where addtime> 1500000000
2）select addtime,name from dw_users having addtime> 1500000000
解释：上面的having可以用的前提是我已经筛选出了addtime字段，在这种情况下和where的效果是等效的，但是如果我没有select addtime就会报错！！因为having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。
2. 只可以用where，不可以用having的情况
1） select addtime,name from dw_users where addtime> 1500000000
2） select phone,name from dw_users having addtime> 1500000000//报错！！！因为前面并没有筛选出addtime字段
3. 只可以用having，不可以用where情况
查询每种category_id商品的价格平均值，获取平均价格大于100元的商品信息
1）select category_id , avg(price) as ag from dw_goods group by goods_category having ag > 100
2）select category_id , avg(price) as ag from dw_goods where ag>100 group by goods_category //报错！！因为from dw_goods 这张数据表里面没有ag这个字段
注意:
where 后面要跟的是数据表里的存在的字段，如果我把ag换成avg(price)也是错误的！因为表里没有该字段。
而having只是根据前面查询出来的是什么就可以后面接什么（比如一些聚合函数这样的“****伪字段”）。
三、# 事务
用途:
a -> b 转账
1\. a的账户的钱 先扣去掉一部分 update person set money = money - 10 where name = a;
2\. b的账户的钱 增加a扣除的部分 update person set money = money + 10 where name = b;
上面两个条sql语句 要求全成功， 要么都失败， 这个时候我们就可以把这两个sql语句放到同一事务中
支持情况: 只有Innodb 引擎的才支持, 常见的引擎有两个(Innodb, MyISAM)
事务的特征（ACID）:
原子性，每个事务是一个最小的不可分割单元
一致性，数据执行执行完事务之后，数据要保持一致(正确)
隔离性，事务和事务之间是隔离的，相互不影响(4中隔离级别)
持久性，一旦事务提交成功，数据发生了不可逆的变化
事务执行的语句:
select 不需要事务的
插入，更新，删除 默认情况下一条sql就是一个事务，事务默认是开启自动提交的
sql语句的执行 (SET AUTOCOMMIT=0), 把自动提交给取消
开始事务 begin
执行sql (如果中间错误了，执行rollback)
提交事务 commit
四、 mysql的隔离级别
1. #查看mysql 默认的隔离级别
@@global.transaction_isolation 系统的隔离级别
@@transaction_isolation 当前会话的隔离级别
select @@global.transaction_isolation,@@transaction_isolation;
2.事务的并发问题
1.脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
隔离级别
解决不可重复读，导致幻读
1\. 在A中开启事务 2\. 在B中开启事务
3\. 查询一下叫'李四'学生 4\. 插入‘李四’的学生
5\. 提交事务
6\. 查询‘李四’,李四不存在
7 插入一下李四，有插入不成功(name要唯一)
**serializable(串行化)**
** 解决幻读**
注意：
1\. 一个事务一个的执行，事务都不并发，没有任何问题，但是会到性能下降
# 隔离界别越高，性能越下降(采用默认的隔离级别就可以了)
修改隔离级别
set session transaction isolation level read uncommitted
set session transaction isolation level read committed
set session transaction isolation level repeatable read
视图
View
应用场景
1\. 数据关联很复杂
2\. 有一些数据不能想某些人看 (让别人通过视图查询数据)
** 四、什么是视图**
一张虚拟表，有字段等，但是不真正的存储数据，对查询结果的一个引用，在使用视图的时候，我们以查询为主，基本不上不做更新，添加，删除操作。
1. 查询 张三的人，他的各门课程成绩， 按照降序进行排序
select st.s_name sname, cu.c_name cname, sc.s_score score from student st left join score sc on st.s_id = sc.s_id left join course cu on cu.c_id = sc.c_id where st.s_name = '钱电' order by score desc;
可以使用视图
[图片上传失败...(image-92039d-1594807281885)]
1. create view temp as select st.s_id sid,st.s_name sname, cu.c_name cname, sc.s_score score from student st left join score sc on st.s_id = sc.s_id left join course cu on cu.c_id = sc.c_id
2. select * from temp where sname = '赵雷' order by score desc;
