数据库高阶（一）表联结、多表查询练习

MySQL数据库高阶

🌟表联结查询
表关系：一对一（少），一对多（常用），多对多
✨子查询（嵌套sql）：为了了对每个客户执⾏行行COUNT()计算，应该将COUNT()作为⼀一个⼦子查询。
select cust_id,cust_name,
(select count(*)
from orders
where orders.cust_id = customers.cust_id) as orders
)
from customers
order by cust_name;

嵌套查询通常是，一条数据的查询结果作为另一条数据查询的条件
✨表联结（内部联结/等值联结）：
1）第一种写法
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id //一定要有where 语句，否则出现笛卡尔积
order by vend_name,prod_name;

2）第二种写法 与条件用where子句结果相同/或者 用join,与 inner join用法一样，结果相同
select vend_name,prod_name,prod_price from vendors inner join products on
vendors.vend_id = products.vend_id;

3）联结多个表（where写法）

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;

4）联结多个表（join写法）
select prod_name,vend_name,prod_price,quantity
from orderitems
inner join vendors on products.vend_id = vendors.vend_id
Inner join products orderitems.prod_id = products.prod_id
where order_num = 20005;

⚠️内部联结特点：关联后只能查到已下单的用户，不能查询所有用户信息

5）自联结
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR’;

如：查询一个问题产品的供应商生产的其他所有的产品名和产品id,建立两个一样的表自查，要比嵌套方式节约资源

6）外部联结：以其中某个表去做查询
left join: 是以left join左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，结果为null
right join: 是以right join右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，结果为null
select customers.cust_id,orders.order_num from customers left join orders on customers.cust_id = orders.cust_id;

⚠️如果用right join 表位置需要互换

将上表中order_num归类并计算数目，结果设定为字段num_ord

select customers.cust_name,customers.cust_id,
count(orders.order_num) as num_ord from customers
left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

⚠️注意：
1）count(orders.order_num) as num_ord 可以计算订单数
2）如果联结3个表，可以前两个进行where,join,inner join 联结，第三章进行外部链接（left join/right join)

✨UNION联合查询/组合查询：
✨union 可以并列两个或多个查询语句，自动去除重复，union all 不去充
✨排序时order by要放在最后，是对组合集进行排序
union 组合查询有规则

🌟50+SQ:练习题
5.查询平均成绩大于等于60分的同学的学生姓名和平均成绩
select sc.sid, sname, round(avg(score,2)) as avg_score from sc.student where sc.sid=student.sid group by sc.sid, sname having avg_score>=60;
通过round(avg(score),2)查询平均分数，并取后两位小数
6.查询sc表存在成绩的学生信息———- distinct表示去重
select distinct stu.* from student as stu join sc on stu.sid = sc.sid;
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 考察外联结和分组统计
select stu.sid, stu.sname,count(sc.cid) as total,
sum(sc.score) as sum_score from student as stu left join sc on stu.sid = sc.sid group by stu.sid,stu.sname;
11.至少有一门课与学号为“01”的同学所学相同的同学的信息
select distinct stu.*
from student as stu
left join sc on sc.sid = stu.sid
where sc.cid in (select cid from sc where sid=’01’);
12.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select s2.sid,student.sname
from sc as s1
join sc as s2
on s1.cid = s2.cid and s1.sid = ‘01’ and s2.sid != ‘01’
join student on s2.sid = student.sid
group by s2.sid,student.sname
having count(s2.cid) = (select count(*) from sc where sid = ‘01’);

🌟sql_mode 环境变量
查看当前数据库的sql_mode
select @@sql_mode;
修改sql_mode

1. 在当前数据库中进行修改(服务器重启后失败) set @@sql_mode= ‘xxx’
2. 修改配置文件 my.cnf sql_mode = ‘xxxx’ 修改完成后要重启mysql服务
   关于ONLY_FULL_GROUP_BY是否开启的建议:
3. 建议开启,符合SQL标准
4. 在mysql中有any_value(field)函数,允许返回非分组字段.(和关闭only_full_group_by模式相同)

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.sid,stu.sname,round(avg(sc.score),2) as avg_score
from student as stu
join sc on stu.sid = sc.sid
where sc.score < 60
group by stu.sid,stu.sname having count(sc.cid) >= 2;

15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select sc.sid,stu.sname,sc.score
from sc join student as stu
on sc.sid = stu.sid
where sc.cid = ‘01’ and sc.score < 60
order by sc.score desc;

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
stu.sname, a.score as ‘语文’, b.score as ‘数学’, c.score as ‘英语’, avg(d.score) as ‘平均成绩’
from student as stu
left join sc as a on stu.sid = a.sid and a.cid = ‘01’
left join sc as b on stu.sid = b.sid and b.cid = ‘02’
left join sc as c on stu.sid = c.sid and c.cid = ‘03’
left join sc as d on stu.sid = d.sid
group by stu.sname,语文,数学,英语
order by 平均成绩 desc;

显示结果：

17.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END
相当于java中的 if ,
select sc.cid, c.cname, max(sc.score) as ‘最高分’, min(sc.score) as ‘最低分’, round(avg(sc.score),2) as ‘平均分’,
count(sc.cid) as ‘选修人数’,
sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / count(sc.cid) as ‘及格率’,
sum(CASE WHEN sc.score >= 70 and sc.score < 80 THEN 1 ELSE 0 END) / count(sc.cid) as ‘中等率’,
sum(CASE WHEN sc.score >= 80 and sc.score < 90 THEN 1 ELSE 0 END) / count(sc.cid) as ‘优良率’,
sum(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / count(sc.cid) as ‘优秀率’
from sc join course as c on sc.cid = c.cid
group by sc.cid,c.cname
order by ‘选修人数’ desc,sc.cid;

concat();联结字符串；concat(sum*100+’%’);

18.按各科平均成绩进行排序，并显示排名，Score重复时保留名次空缺
– 按照s2进行分组,统计s1的平均分出现的次数,
select s2.cid,s2.avg_sc,count(distinct s1.avg_sc) as rank
from
(select cid,round(avg(score),2) as avg_sc from sc group by cid) as s1
join
(select cid,round(avg(score),2) as avg_sc from sc group by cid) as s2
on s1.avg_sc >= s2.avg_sc
group by s2.cid,s2.avg_sc
order by rank

结果：

🚩SQL面试题
用一条SQL语句查询出每门课都大于80分的学生姓名
select name,min(score) as min_sc from mst_stu group by name having min_sc > 80;

2. 查询后一天 temperature 比前一天高的date
   #先自联结，条件为datediff();两天之间相差正值，s1.date为后一天，并且温度大于s2.temperature.
   select s1.id
   from mst_weather as s1
   join mst_weather as s2
   on datediff(s1.date,s2.date)=1
   and s1.temperature > s2.temperature;

3.查询每个主播的最大level以及对应的最小gap(注意:不是每个主播的最大level和最小gap)
select zhubo_id,level,min(gap)
from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo group by zhubo_id)
group by zhubo_id,level;

4.下表是每个课程class_id对应的年级(共有primary、middle、high三个),以及某种比率rate（CASE WHEN 行转列）
✨第一种方法：CASE WHEN
select class_id,
max(CASE WHEN grade = ‘primary’ THEN rate ELSE 0 END) as ‘primary’,
max(CASE WHEN grade = ‘middle’ THEN rate ELSE 0 END) as ‘middle’,
max(CASE WHEN grade = ‘high’ THEN rate ELSE 0 END) as ‘high’
from mst_class
group by class_id;

✨第二种方法：— 使用IF()
select class_id,
max(IF(grade = ‘primary’,rate,0)) as ‘primary’,
max(IF(grade = ‘middle’,rate,0)) as ‘middle’,
max(IF(grade = ‘high’,rate,0)) as ‘high’
from mst_class
group by class_id;

5.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
– 先按照题设计表
create table mst_a(key varchar(10),value varchar(10));
create table mst_b(key varchar(10),value varchar(10));
insert into mst_a values(‘A’,‘aaa’),(‘B’,‘bbb’),(‘C’,‘ccc’);
insert into mst_b values(‘D’,‘ddd’),(‘E’,‘eee’),(‘A’,‘abc’);

– 先查询出哪个key符合要求?
select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_b.key;
– update mst_b set value = ? where key = ?
– update mst_b as up ,(?) as b set up.value = ? where up.key = ?
update mst_b as up,(
select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_b.key
) as b
set up.value = b.value where up.key = b.key
– 注意事项
– update 后面是可以进行任何查询语句,这个作用等同于 from
– update 更新表,不能在set和where中用于子查询
– update 也可以对多个表进行更新 (sqlserver不行)

🌟MySQL事务
一、事务的语法：
1)start transaction;/begin;
2)commit; 使当前的修改确定
3）rollback;使当前的修改被放弃

二、事务管理包括：事务的ACID特性，事务隔离级别，事务并发引起的问题 三部分
✨事务的ACID特性包括：
1）原子性：Atomicity
事务的原⼦子性是指事务必须是⼀一个原⼦子的操作序列列单元。事务中包含的各项操作在⼀一次执⾏行行过程中，只 允许出现两种状态之⼀一。
全部执⾏行行成功
全部执⾏行行失败
事务开始后所有操作，要么全部做完，要么全部不不做，不不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生⼀样。也就是说事务是一个不不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2）一致性：Consistency
事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执⾏之后，数据库都必须处于⼀致性状态。
比如:如果从A账户转账到B账户，不不可能因为A账户扣了钱，而B账户没有加钱。
3）隔离性：Isolation
事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各⾃完整的数据空间。
⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。 隔离性分4个级别。
4）持久性：Duration
事务的持久性是指事物一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事物成功结束后的状态。
三、事务的并发问题包括：
1）脏读（Dirty Read）
2) 不可重复读（Unrepeatable Read）
3）幻读：Phantom Read

四、事务的隔离级别
✨查看当前会话中事务的隔离级别

mysql> select @@tx_isolation;

✨设置当前会话中事务的隔离级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

✨读未提交(READ_UNCOMMITTED)
读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理理某一数据，并对其进行了更新，但同时尚未完成事务，因此还没有提交事务;⽽与此同时，允许另⼀个事务也能够访问该数据。

✨读已提交(READ_COMMITTED)
读已提交是不同的事务执⾏的时候只能获取到已经提交的数据。 这样就不会出现上⾯的脏读的情况了。 但是在同⼀个事务中执⾏同⼀个读取,结果不⼀致 。
✨可重复读(REPEATABLE_READ)
可重复读就是保证在事务处理过程中，多次读取同⼀个数据时，该数据的值和事务开始时刻是⼀致的。 因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。幻读：诡异的更新事件
✨顺序读(SERIALIZABLE)
顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执⾏，即事务只能⼀一个接⼀个地处理，不能并发。
五、不同的隔离级别的锁的情况(了解)
读未提交(RU): 有⾏级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
读已提交(RC):有⾏级的锁，没有间隙锁，读不到没有提交的数据。

可重复读(RR):有⾏级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情况。
序列列化(S):有⾏级锁，也有间隙锁，读表的时候，就已经上锁了了

六、隐式提交(了解)
DQL:查询语句
DML:写操作(添加,删除,修改)
DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图) DCL:控制语⾔言(给⽤用户授权,或删除授权)
DDL(Data Define Language):都是隐式提交。 隐式提交:执⾏这种语句相当于执⾏commit; DDL

🌟扩展：
MySQL的存储过程、MySQL的触发器、MySQL的视图
🌟MySQL索引原理及SQL优化