網(wǎng)上搜的威蕉，各個(gè)版本都有一點(diǎn)歉胶，部分解法參考數(shù)據(jù)蛙公眾號(hào)

Dataset

參考：https://blog.csdn.net/weixin_39718665/article/details/78161013

create database mysql45 charset = utf8;
use mysql45;
#建學(xué)生信息表student
create table student(
sno varchar(20) not null primary key,  # 學(xué)號(hào)
sname varchar(20) not null,  # 姓名
ssex varchar(20) not null,  # 性別
sbirthday datetime,  # 生日
class varchar(20)  # 所在班級(jí)
);
#建立教師表teacher
create table teacher
(
tno varchar(20) not null primary key,  # 教師編號(hào)
tname varchar(20) not null,  # 教師姓名
tsex varchar(20) not null,  # 教師性別
tbirthday datetime,  # 生日
prof varchar(20),  # 職稱
depart varchar(20) not null  # 所在部門
);
#建立課程表course
create table course
(
cno varchar(20) not null primary key,  # 課程編號(hào)
cname varchar(20) not null,  # 課程名稱
tno varchar(20) not null,  # 教師編號(hào)
foreign key(tno) references teacher(tno)
);
#建立成績(jī)表score
create table score
(
sno varchar(20) not null,  # 學(xué)號(hào)
foreign key(sno) references student(sno),
cno varchar(20) not null,  # 課程編號(hào)
foreign key(cno) references course(cno),
degree decimal  # 成績(jī)
);

#添加學(xué)生信息
insert into student values('108','曾華','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王麗','女','1976-01-23','95033');
insert into student values('101','李軍','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陸君','男','1974-06-03','95031');
#添加教師信息
insert into teacher values('804','李誠(chéng)','男','1958-12-02','副教授','計(jì)算機(jī)系');
insert into teacher values('856','張旭','男','1969-03-12','講師','電子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','計(jì)算機(jī)系');
insert into teacher values('831','劉冰','女','1977-08-14','助教','電子工程系');
#添加課程信息
insert into course values('3-105','計(jì)算機(jī)導(dǎo)論','825');
insert into course values('3-245','操作系統(tǒng)','804');
insert into course values('6-166','數(shù)字電路','856');
insert into course values('9-888','高等數(shù)學(xué)','831');
#添加成績(jī)信息
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('101','3-105','64');
insert into score values('107','3-105','91');
insert into score values('108','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');

順便總結(jié)一下數(shù)據(jù)庫(kù)的基本操作

# 創(chuàng)建
CREATE DATABASE database_name CHARSET = utf8; # 我的習(xí)慣是創(chuàng)建的時(shí)候設(shè)置編碼格式
CREATE TABLE table_name(column_name data_type);
# 使用數(shù)據(jù)庫(kù)
USE database_name;
# 更改表
ALTER TABLE table_name MODIFY column_name data_type;  # 更改字段數(shù)據(jù)類型
ALTER TABLE table_name ADD column_name data_type;  # 增加一個(gè)字段
ALTER TABLE table_name (還有別的KEY WORDS...)
# 更新數(shù)據(jù)
UPDATE table_name SET column_name = value,... WHERE conditions;  # 不加WHERE就是更改整個(gè)列
# 刪除(別亂用)
DROP DATABASE database_name;
DROP TABLE table_name;
DELETE FROM table_name WHERE conditions;  # 刪除記錄
# 插入
INSERT INTO table_name (column_names) VALUES(values);
# 查看
SHOW DATABASES;
SHOW TABLES;
DESC table_name;  # 查看表結(jié)構(gòu)

查詢和學(xué)號(hào)為108、101的學(xué)生同年出生的所有學(xué)生的Sno豌骏、Sname和Sbirthday列

select sno, sname, sbirthday from student
where year(sbirthday) in (select year(sbirthday) from student where sno = 108 or sno = 101)
and sno != 108 AND sno != 101;

查詢選修某課程的學(xué)生人數(shù)多于5人的教師姓名

select tname from teacher
join course on teacher.tno = course.tno
join score on course.cno = score.cno
group by score.cno
having count(*) > 5;

查詢95033班和95031班全體學(xué)生的記錄

select * from Student,Score,Course
where Student.Sno=Score.Sno and Score.Cno=Course.Cno and class = '95033' or '95031';

查詢選修編號(hào)為“3-105”課程且成績(jī)至少高于選修編號(hào)為“3-245”的學(xué)生的Cno、Sno和Degree,并按Degree從高到低次序排序

select cno, sno, degree from score
where cno = '3-105'
and degree > (select min(degree) from score where cno = '3-245')
order by degree desc; 

查詢所有教師和學(xué)生的name、sex和birthday

用UNION聯(lián)合顯示兩次查詢的結(jié)果
PS：union相當(dāng)于是union all取DISTINCT的結(jié)果

select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher;

查詢成績(jī)比該課程平均成績(jī)低的學(xué)生的成績(jī)表

用自連接的方式體現(xiàn)“該課程”

select * from score a
where degree < (select avg(degree) from score b where a.cno = b.cno); 

數(shù)據(jù)庫(kù)引擎會(huì)采取逐條取主查詢記錄與子查詢實(shí)施比對(duì)(where degree < ...)悴能，子查詢中，取主查詢的字段a.cno作為過濾雳灾，輸出不同課程的avg(degree)漠酿；比對(duì)結(jié)束后確定是否檢出該條記錄，最后匯總各次檢索的結(jié)果輸出整個(gè)記錄集

查詢Student表中每個(gè)學(xué)生的姓名和年齡

select sname,year(now())-year(sbirthday) from student;

以班號(hào)和年齡從大到小的順序查詢Student表中的全部記錄

select * from student order by class desc, sbirthday asc;

查詢參加3-105和3-245兩門課程的學(xué)生的成績(jī)(左右表的形式)

# 條件放在where后面和on后面的邏輯是不同的
select * from score a
join score b on a.sno = b.sno
where a.cno = '3-105' and b.cno = '3-245';
----
select * from (select * from score where cno = '3-105') a
join score b on a.sno = b.sno and b.cno = '3-245';

查詢不參加6-166課程谎亩，但是參加3-105課程的學(xué)生(分步)

select * from 
(select sno from score where sno not in (select sno from score where cno = '6-166')) a
join score b on a.sno = b.sno and b.cno = '3-105';  # 會(huì)出現(xiàn)兩個(gè)sno列
----
select * from score
where sno not in (select sno from score where cno = '6-166')
and cno = '3-105';

查詢和109號(hào)同學(xué)參加的課程至少有一門相同的同學(xué)信息

select distinct a.* from student a
join score b on a.sno = b.sno
where b.cno in (select cno from score where sno = '109')
and b.sno != '109';

查詢和109號(hào)同學(xué)課程安排完全相同的同學(xué)信息

查詢邏輯：找出109同學(xué)參加的課程炒嘲，求補(bǔ)集宇姚；限制和109同學(xué)參加的課程數(shù)量相同

select sno from score
where sno not in (select sno from score where cno not in (select cno from score where sno = '109'))
and sno != '109'
group by sno
having count(*) = (select count(*) from score where sno = '109');

查詢沒有參加過李誠(chéng)老師課的學(xué)生

思路：選出參加過李誠(chéng)老師課的學(xué)生的sno，用NOT IN取沒參加過的學(xué)生（主體是學(xué)生而不是課程）

select distinct sname from student
join score on student.sno = score.sno
where score.sno not in(
select distinct score.sno from score
join course on score.cno = course.cno
join teacher on course.tno = teacher.tno
where tname = '李誠(chéng)');

查詢兩門課及以上都不到80分的學(xué)生信息及其平均成績(jī)

select a.*, avg(b.degree) from student a
join score b on a.sno = b.sno
where a.sno in (
select sno from score
where degree <80
group by sno
having count(*) > 1)
group by a.sno;

按平均成績(jī)降序顯示所有學(xué)生的成績(jī)和平均成績(jī)

思路：先對(duì)score表求聚合average夫凸，再把平均成績(jī)貼到score表上進(jìn)行排序

select a.*, b.avg_score from score a
join (
select sno,avg(degree) as avg_score from score
group by sno) b on a.sno = b.sno
order by b.avg_score desc;

查詢各課程最高分浑劳、最低分、平均分寸痢、優(yōu)良率

及格率/好評(píng)率同理
條件計(jì)數(shù)的思路：滿足條件的記為1呀洲，不滿足則記為0，最后求和啼止，得到共有多少條記錄滿足指定條件

select 
cno,
max(degree) as 最高分,
min(degree) as 最低分,
avg(degree) as 平均成績(jī),
sum(case when degree >= 80 then 1 else 0 end) / count(1) as 優(yōu)良率
from score
group by cno;

按成績(jī)進(jìn)行排序道逗，并顯示排名， 成績(jī)重復(fù)時(shí)保留名次空缺

思路：對(duì)score表按degree字段降序排序献烦，并依次賦予排名

select
sno,
cno,
degree,
case when @score = degree then @rank  # 方便理解滓窍，設(shè)置為和上一條記錄相同的rank值
     else @rank:=@rank+1 end as 排名,
@score:=degree
from score, (select @rank:=0, @score:=null) as t
order by degree desc;

知識(shí)點(diǎn)：涉及到mysql中的自定義變量@變量名，以及賦值符號(hào):=巩那；關(guān)聯(lián)表時(shí)吏夯，若不指定關(guān)聯(lián)的鍵，則會(huì)生成笛卡爾集即横，即score表的每一條記錄后都會(huì)帶有t表的記錄
我的推測(cè)：由于我們?cè)陉P(guān)聯(lián)表時(shí)定義了@score為null值噪生，又由于mysql是逐行選取，因此會(huì)取上一條非空的記錄作為@score的值和該行的degree值進(jìn)行比較
弊端：最后的結(jié)果會(huì)多出來@score列东囚，是我們不想要的跺嗽，那么可以將@score列并入case when的條件語(yǔ)句中...

select
sno,
cno,
degree,
case when @score = degree then ''
     when @score:=degree then @rank:=@rank+1 end as 排名
from score, (select @rank:=0, @score:=null) as t
order by degree desc;

知識(shí)點(diǎn)：由于case when語(yǔ)句的運(yùn)行規(guī)則是逐條進(jìn)行，因此當(dāng)@score = degree語(yǔ)句成立時(shí)页藻，不執(zhí)行下一行判斷桨嫁；若不成立，則由于@score:=degree是一個(gè)賦值語(yǔ)句份帐，恒成立璃吧，進(jìn)而執(zhí)行@rank+1語(yǔ)句

18.

思路：難點(diǎn)在于前三如何取，用大于某成績(jī)的記錄條數(shù)<3來做為限制條件即可以選取到前三名的記錄

select
*
from score a
where (select count(1) from score b where a.cno = b.cno and b.degree > a.degree) < 3
order by cno desc, degree desc;

我推測(cè)的取數(shù)邏輯：按cno和degree降序排列废境；取第一個(gè)記錄后畜挨，進(jìn)入到where過濾；子查詢的第一個(gè)條件為cno相等噩凹，第二個(gè)條件為大于第一條記錄中degree的值的記錄朦促，假設(shè)有2條，則count函數(shù)返回2栓始，滿足小于3的條件，父查詢中的where返回true血当；第一條記錄被選出幻赚，進(jìn)入第二條記錄...

選出同名同性別的學(xué)生（非重復(fù)記錄）禀忆，并統(tǒng)計(jì)人數(shù)

思路：名字性別可以重復(fù)，學(xué)生的編號(hào)不能重復(fù)落恼，

select
a.sname,
a.ssex,
count(1)
from student a
inner join student b on a.sname = b.sname and a.ssex = b.ssex and a.sno != b.sno
group by a.sname, a.ssex;

查詢均分高于85的學(xué)生姓名

思路：分步式箩退，先查詢出均分高于85的學(xué)生編號(hào)；再和student表關(guān)聯(lián)佳谦，查詢學(xué)生姓名

select b.sname,a.avg_score
from
(select sno, avg(degree) as avg_score
from score
group by sno
having avg(degree) > 85) a
join student b on a.sno = b.sno; 

查詢?nèi)我庖婚T課程的成績(jī)?cè)?0分以上的學(xué)生信息

思路：先查找出成績(jī)大于80分的學(xué)生編號(hào)（這些學(xué)生至少有一門課成績(jī)大于80分）戴涝，再用編號(hào)作為限定條件選取學(xué)生信息

select * 
from student a
where sno in (
select sno
from score
where degree > 80);

查詢李誠(chéng)老師的學(xué)生中成績(jī)最好的學(xué)生姓名和成績(jī)

select
a.sname, b.degree
from student a
join score b on a.sno = b.sno
join course c on b.cno = c.cno
join teacher d on c.tno = d.tno
where d.tname = '李誠(chéng)'
order by b.degree desc
limit 1;

如果成績(jī)出現(xiàn)重復(fù)，用limit方法則只能選取出一位學(xué)生
假設(shè)成績(jī)有重復(fù)的情況下钻蔑，查詢李誠(chéng)老師的學(xué)生中成績(jī)最好的學(xué)生姓名和成績(jī)
思路：先取出李誠(chéng)老師的學(xué)生信息以及成績(jī)啥刻，再對(duì)學(xué)生成績(jī)進(jìn)行排名，最后取 排名為1的記錄即可

select *  # 第三步
from
(
select  # 第二步
a.sname,
case when @score = degree then @rank
     when @score:=degree then @rank:=@rank+1 end as 排名
from
(select  # 第一步
a.sno, a.sname, b.degree, d.tname
from student a
join score b on a.sno = b.sno
join course c on b.cno = c.cno
join teacher d on c.tno = d.tno
where d.tname = '李誠(chéng)') a, (select @rank:=0, @score:=null) b
) a
where 排名 = 1;

36.

和之前的思路（大于某成績(jī)的記錄數(shù)量小于3）不同咪笑，使用分組排名的形式

select sno, cno, degree, 排名 from
(select
a.*,
@rank:=if(@class=cno,if(@score=degree,@rank,@rank+1),1) as 排名,
@score:=degree,
@class:=cno
from score a, (select @rank:=0, @score:=null, @class:=null) b
order by cno, degree desc) a
where 排名 < 3;

大前提：整張表是按cno升序和degree降序進(jìn)行排列的
第四行@rank:=if(@class=cno, if(@score=degree, @rank,@rank+1), 1)所做的事是：

1. 當(dāng)一條記錄進(jìn)入選擇語(yǔ)句可帽，首先判斷課程cno是否和上一條記錄@class相同，如果不是窗怒，說明該記錄屬于一門新的課程映跟，從1開始排名
2. 如果@class=cno為真，說明該記錄和上一條記錄屬于同一門課程扬虚，則進(jìn)入下一層判斷努隙，判斷該記錄的成績(jī)degree是否和上一條記錄的成績(jī)@score相同；如果相同辜昵，則和上一條記錄并列排名荸镊；如果不同，排名加一

如果出現(xiàn)排名相同的情況路鹰，但是我們只想取第一個(gè)學(xué)生怎么辦贷洲？
并未考證過，僅為思路（數(shù)據(jù)中沒有相同成績(jī)的學(xué)生）
where 排名 < 3 and (select sno from temp b where b.排名 = a.排名 limit 1)

with temp as
(select
a.*,
@rank:=if(@class=cno,if(@score=degree,@rank,@rank+1),1) as 排名,
@score:=degree,
@class:=cno
from score a, (select @rank:=0, @score:=null, @class:=null) b
order by cno, degree desc)
select sno, cno, degree, 排名 from temp a
where 排名 < 3 
and (select sno from temp b where b.排名 = a.排名 limit 1);

選修了全部課程的學(xué)生信息

select *
from score
join student on score.sno = student.sno
group by score.sno
having count(1) = (select count(1) from course);
# 由于我們的課程數(shù)據(jù)有4條晋柱，但是成績(jī)表中只包含了三門課程优构，因此沒有人修全部課程

要求按照出生年月日計(jì)算學(xué)生年齡

運(yùn)用時(shí)間差函數(shù)timestampdiff或datediff
時(shí)間差函數(shù)比之前用到的年份相減法多涵蓋了日期和時(shí)間，只有完整地相差一年才會(huì)返回1

select sname, timestampdiff(year,sbirthday, now()) as 年齡 from student;
# year是自行設(shè)置的參數(shù)雁竞，設(shè)定返回的值類型

查詢本周過生日的學(xué)生

week()函數(shù)能返回該日期在一年內(nèi)對(duì)應(yīng)的周數(shù)

select sname from student
where week(sbirthday) = week(now());

如果使用month()函數(shù)同理可查詢到本月過生日的學(xué)生

查詢同月過生日的學(xué)生數(shù)量

在select后的變量名(如mon)可以用在篩選/分組等操作

select count(sname),month(sbirthday) as mon
from student
group by mon
order by mon;

查詢成績(jī)?cè)谇?/3的學(xué)生姓名和總成績(jī)

思路：運(yùn)用窗口函數(shù)ntile進(jìn)行分箱

select
b.sname, a.tot
from(
select
sno, sum(degree) tot,
ntile(3) over(order by sum(degree) desc) lvl
from score
group by sno) a join student b
on a.sno = b.sno
where a.lvl = 1;

查詢按男生/女生分組钦椭，成績(jī)分別在前50%的學(xué)生姓名和成績(jī)

思路：使用group by將成績(jī)按sno求和，再使用ntile窗口函數(shù)對(duì)總成績(jī)分箱碑诉，取出level=1的學(xué)生記錄

select
b.sname, b.ssex, a.tot
from(
  select
  b.sno, b.ssex, sum(a.degree) tot,
  ntile(2) over(partition by b.ssex order by sum(degree) desc) lvl
  from score a join student b
  on a.sno = b.sno
  group by a.sno) a  # 或者group by a.sno, b.ssex因?yàn)橥鈱拥膬?yōu)先級(jí)高于內(nèi)層
join student b on a.sno = b.sno
where a.lvl = 1;

問：為什么不能按男/女生分組彪腔？原因?qū)?shù)據(jù)不夠理解，因?yàn)橐粋€(gè)學(xué)生會(huì)參加多個(gè)課程进栽，只能通過在外層對(duì)sno進(jìn)行分組求sum(degree)德挣，才能在內(nèi)層進(jìn)行排序，內(nèi)層排序可以和partition同時(shí)進(jìn)行（如果在外層對(duì)ssex進(jìn)行分組快毛，窗口函數(shù)的內(nèi)層就無法按sno求sum格嗅，而是按照ssex作為聚合方式求sum）
回顧：外層的where 番挺、group by、order by會(huì)優(yōu)先于窗口函數(shù)內(nèi)層的partition by屯掖、order by執(zhí)行

查詢計(jì)算機(jī)導(dǎo)論課程成績(jī)最高的學(xué)生和操作系統(tǒng)成績(jī)最高的學(xué)生

select cname, max(degree)
from score join course on score.cno = course.cno
where cname in ('計(jì)算機(jī)導(dǎo)論', '操作系統(tǒng)')
group by score.cno;

查詢score表中3-245課程選修人數(shù)和3-105課程選修人數(shù)之比

WITH子句的用法：

1. 基本格式為WITH table_name AS(query_expressions)
2. 在創(chuàng)建CTE之后必須跟隨索引該CTE的語(yǔ)句(見代碼注解)

為什么使用WITH子句玄柏？增加代碼可讀性...

with temp as(
select distinct count(*) as cnt,
cno
from score
group by cno)
select distinct  # with子句和主query是連在一起的一條query贴铜，沒有分號(hào)  # 這里不加distinct會(huì)出現(xiàn)3條相同的記錄粪摘，暫時(shí)不明白原理
# 在select中使用select!!!
(select cnt from temp where cno = '3-245')/(select cnt from temp where cno = '3-105') as ratio
from temp  # 必須包含temp表，否則會(huì)報(bào)錯(cuò)
;

查詢連續(xù)3位學(xué)生成績(jī)都在70分以上的cno(非排序)

連續(xù)n條記錄相同的情況

select
distinct cno
from(
select
cno,
case when @sc <= 70 and degree <= 70 then @rank:= 0
     when @sc <= 70 and degree > 70 then @rank:= 1
     when @sc > 70 and degree > 70 then @rank:= @rank + 1
     when @sc > 70 and degree <= 70 then @rank:= 0
end rn,
@sc:=degree
from score, (select @rank:=0, @sc:=0) a
) b
where rn >= 3;

查詢每位學(xué)生參加的課程中绍坝，編號(hào)最大的課程成績(jī)

HAVING的用法：本質(zhì)是篩選

select * from score
group by sno
having max(cno);

將所有學(xué)生的成績(jī)均分為5級(jí)徘意，查詢3-105課程在每級(jí)中的貢獻(xiàn)率

分級(jí)怎么分？

按序號(hào)排序asc陷嘴，查詢下一位同學(xué)比上一位同學(xué)出生要晚的同學(xué)姓名

select sno, sname 
from(
select
sno, sname,
case when timestampdiff(day, @birth, sbirthday) > 0 then 1
     else 0 end rn,
@birth:= sbirthday
from student, (select @birth:= null) a) a
where rn = 1;

查詢每位學(xué)生各門課的成績(jī){leetcode1179}

要求是輸出sno列和各課程名稱

select
sno,
sum(case cno when '3-105' then degree end) 計(jì)算機(jī)導(dǎo)論,
sum(case cno when '3-245' then degree end) 操作系統(tǒng),
sum(case cno when '6-166' then degree end) 數(shù)字電路
from score
group by sno;

查詢順序是先group by再select映砖；注意sum的用法，不加sum則只會(huì)輸出第一條數(shù)據(jù)

選取第N(4)高的成績(jī){leetcode177}

排序類窗口函數(shù)的運(yùn)用
ROW_NUMBER()排序規(guī)則1灾挨、2邑退、3、4
RANK()排序規(guī)則1劳澄、2地技、2、4
DENSE_RANK()排序規(guī)則1秒拔、2莫矗、2、3

select
distinct degree  # 成績(jī)可能重復(fù)
from(
  select
  degree,
  dense_rank() over(order by degree desc) rnk
  from score) a
where rnk = 4;

查詢每門課每位學(xué)生和上一位學(xué)生成績(jī)的差

第一種方法：用自定義變量
之前錯(cuò)誤的思路為：希望將上一條記錄的degree賦值給自定義變量砂缩，然后做差作谚；實(shí)際上做不到，只能通過判斷或運(yùn)算來調(diào)用上一條記錄的自定義變量

select
cno, sno,
degree,
case when @cls=cno then round(degree - @pre, 0)  # 使用自定義變量@cls來保證diff在同一cno內(nèi)
     when @pre:=null then null end diff,  # 若@cls!=cno則進(jìn)入下一個(gè)when庵芭，@pre:=null恒成立
@pre:=degree,
@cls:=cno
from score, (select @pre:=null, @cls:=null) a
order by cno;  # 第一步先進(jìn)行排序

第二種方法：窗口函數(shù)

select cno, sno, degree-pre diff
from(
  select
  cno, sno, degree,
  lag(degree,1) over(partition by cno) pre
  from score) a;