文章目錄

前言
- 1、熱身題實踐
- 其他

前言

一直有個想法：把面試需要的知識點全都總結一下，包括數據庫，語言，算法，數據結構等知識，形成一個面試總結筆記，這樣以后面試的時候只看這些文章回顧下就行了。今天就先總結下Mysql的面試熱身題吧，后續會總結其他方面的點。當然，文章同樣會不定時更新。

1、熱身題實踐

說明：以下五十個語句都按照測試數據進行過測試，最好每次只單獨運行一個語句。
問題及描述：
--1.學生表
Student(S#,Sname,Sage,Ssex) --S# 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別
--2.課程表 
Course(C#,Cname,T#) --C# --課程編號,Cname 課程名稱,T# 教師編號
--3.教師表 
Teacher(T#,Tname) --T# 教師編號,Tname 教師姓名
--4.成績表 
SC(S#,C#,score) --S# 學生編號,C# 課程編號,score 分數
*/
--創建測試數據
create table student(sno varchar(10),sname varchar(10),sage datetime,ssex varchar(10));
insert into student values('01' , '趙雷' , '1990-01-01' , '男');
insert into student values('02' , '錢電' , '1990-12-21' , '男');
insert into student values('03' , '孫風' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');create table course(cno varchar(10),cname varchar(10),tno varchar(10));
insert into course values('01' , '語文' , '02');
insert into course values('02' , '數學' , '01');
insert into course values('03' , '英語' , '03');create table teacher(tno varchar(10),tname varchar(10));
insert into teacher values('01' , '張三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');create table sc(sno varchar(10),cno varchar(10),score int(4));
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);

1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

 方法一：連表查詢SELECT * FROM student sLEFT JOIN sc on s.sno=sc.snoLEFT JOIN sc sc1 on s.sno=sc1.snoWHERE sc.cno='01'and sc1.cno='02'and sc.score>sc1.score方法二：形成子表然后再連表查詢select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#;(注意子查詢的使用場景)方式三：注意：限制條件添加join條件中對左表沒有約束！左表沒有被限制條件限制！SELECT * FROM student sLEFT join sc on s.Sno=sc.Sno and sc.cno='01'LEFT join sc sc1 on s.Sno=sc1.Sno and sc1.cno='02'WHERE sc.score>sc1.score

2、查詢同時存在"01"課程和"02"課程的情況

SELECT * 
FROM student s
LEFT join sc on s.Sno=sc.Sno and sc.cno='01'
LEFT join sc sc1 on s.Sno=sc1.Sno and sc1.cno='02'
WHERE sc.score is not NULL and sc1.score is not null

3、查詢平均成績大于等于60分的同學的學生編號和學生姓名和平均成績

SELECT s.sno,s.sname,avg(sc.score)
FROM student s 
LEFT join sc on s.sno=sc.sno
GROUP BY s.sno
HAVING avg(sc.score)>=60
ORDER BY avg(sc.score) desc

4、查詢在sc表存在成績的學生信息的SQL語句。

SELECT * FROM Student s 
LEFT JOIN SC on s.Sno=SC.Sno
GROUP BY s.Sno
HAVING count(score)>0

5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

SELECT s.Sno , Sname, count(Cno),sum(score)
from Student s left join SC on s.Sno=SC.Sno
GROUP BY s.Sno

6、查詢"李"姓老師的數量

SELECT count(*) FROM Teacher t WHERE t.Tname LIKE '李%'

7、查詢學過"張三"老師授課的同學的信息

方法一：連表查詢
SELECT *
FROM student s 
LEFT join sc on s.sno=sc.sno
left join course c on c.cno=sc.cno
LEFT join teacher t on c.tno=t.tno
WHERE t.tname="張三"
方法二：利用子查詢
SELECT sname,sno
FROM student 
WHERE sno in
(SELECT distinct sno
FROM sc 
WHERE cno in(
SELECT c.cno
FROM course c 
LEFT JOIN teacher t on c.tno=t.tno
WHERE t.tname='張三'))

8、查詢沒學過"張三"老師授課的同學的信息

#注意子查詢的使用場景
SELECT * FROM Student WHERE Sno not in 
(SELECT s.Sno FROM 
Student s LEFT join SC on s.Sno=SC.Sno
LEFT join Course c on SC.Cno=c.Cno
LEFT join Teacher t on c.Tno=t.Tno
WHERE t.Tname = '張三')

9、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

  方法一：連表查詢SELECT DISTINCT s.sno,s.snameFROM student s LEFT JOIN sc on s.sno = sc.sno LEFT JOIN sc s1 on s1.sno=s.snoWHERE sc.cno='01' and s1.cno!='02'方法二： #注意子查詢的靈活使用SELECT s.*FROM student sLEFT join sc on s.sno=sc.snoWHERE sc.cno='01'and s.sno in(SELECT DISTINCT s1.sno FROM student s1LEFT join sc sc1 on s1.sno=sc1.snoWHERE sc1.cno!="02")

10、查詢沒有學全所有課程的同學的信息

SELECT s.* 
FROM Student s 
LEFT join SC on s.Sno=SC.Sno 
GROUP BY s.Sno
HAVING count(Cno)<
(select count(C#) from SC)
#注意group by之后是對相應的屬性做限制，之后將滿足限制的所有數據篩選出來(分成多少組有多少數據)

11、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

SELECT DISTINCT student.sno,sname
FROM student 
LEFT JOIN sc on student.sno=sc.sno
WHERE sc.cno in 
(SELECT cno 
FROM sc
WHERE sno='01')
and sc.sno!='01'

12、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

#注意不能在where子句中限制組函數如：where  max(score)>60
SELECT sc.sno,sname,avg(score)
FROM student s  
LEFT JOIN sc on s.sno=sc.sno
where s.sno in (
SELECT sc.sno
FROM student s  
LEFT JOIN sc on s.sno=sc.sno
WHERE sc.score<60
GROUP BY sc.sno
HAVING count(*)>=2)

group by sno
13、檢索"01"課程分數小于60，按分數降序排列的學生信息

SELECT *
FROM student s LEFT JOIN sc
on s.sno=sc.sno
WHERE sc.cno='01' 
and sc.score<60
ORDER BY score desc

14、查詢每門課程被選修的學生數

 SELECT cno,count(sno)FROM scGROUP BY cno

15、查詢出只有兩門課程的全部學生的學號和姓名

SELECT s.sno,sname
FROM student s LEFT JOIN sc
on s.sno=sc.sno
GROUP BY s.sno
HAVING count(*)=2

16、查詢1990年出生的學生名單

SELECT s.* 
FROM Student s
WHERE s.Sage BETWEEN'1990-01-01' and '1990-12-31'

17、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

SELECT cno,avg(score)
FROM sc
GROUP BY cno
ORDER BY avg(score) desc,cno asc

18、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數。

 SELECT s.sname,c.cname,scoreFROM student s LEFT join scon s.sno=sc.snoLEFT JOIN course c on sc.cno=c.cnoGROUP BY s.snoHAVING min(score)>=70

19、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

SELECT s1.*,score
FROM student s1 LEFT JOIN sc
on s1.sno=sc.sno
WHERE sc.cno in(
SELECT c.cno
FROM teacher t LEFT JOIN course c
on t.tno=c.tno
WHERE t.tname="張三")
ORDER BY score desc
LIMIT 1

20、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

     SELECT DISTINCT sc.cno,sc.score,sc.snoFROM sc LEFT join sc sc1on sc.sno=sc1.snoWHERE sc.cno!=sc1.cnoand sc.score=sc1.score

21、查詢各科成績最好的前兩/三名#重點注意

方法一：連表查詢（重點參考）
SELECT *
FROM sc a
LEFT join sc b on a.cno=b.cno and a.score<b.score#篩選我的成績比別人的成績差的數據
GROUP BY a.cno,a.sno
HAVING count(1)<2#如果我的成績比別人的成績差的數據條數小于2，也就代表有0或1個人的成績比我好，則對應的用戶就被篩選出來了
直觀的意義是取分組后的TOPN,實際操作時候，只能是從每組里面取第一條數據，只是利用多重分組來將滿足條件的TopN條數據給篩選出來而已！#方法二：使用子查詢
SELECT s1.* 
FROM SC s1 
WHERE
( SELECT COUNT(1) 
FROM SC s2 
WHERE s1.Cno=s2.CnoAND s2.score>=s1.score
)<=2
ORDER BY s1.Cno,s1.score DESC
分析下這個sql：
select * from test1 a where 2 > (select count(*) from test1 where course=a.course and score>a.score)
相關子查詢的特點就是子查詢依賴與外部查詢，在這里面其實是 select * from test 已經先執行了一遍了，查出了所有的數據
然后相關子查詢針對每一行數據進行select count(*) from test1 where course=a.course and score>a.score
例如：第一行是張三，數學77，那么相關子查詢做的工作就是找出test表所有課程是數學的行，查詢 張三，77|李四，68|王五，99然后where條件score>77，查詢出王五，99，count=1，這時候外部條件2>1,符合。第二行是李四，數學68，那么相關子查詢做的工作就是找出test表所有課程是數學的行，查詢 張三，77|李四，68|王五，99然后where條件score>68，查詢出張三，77,王五，99，count=2，這時候外部條件2>2,不符合。第三行是王五，數學99，那么相關子查詢做的工作就是找出test表所有課程是數學的行，查詢 張三，77|李四，68|王五，99然后where條件score>99，沒有數據，這時候外部條件2>0,符合。那么就篩選出了數學最大的2個人，張三和王五。
其實這里的子查詢就是找出和當前行類型能匹配上的比他大的有多少，沒有比他大的他就是最大
那么找top1就是 1>(xxx)，topN就是N>(xxxxx)

#以下幾個題目要了解下子查詢在select字段時的原理
22、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
–及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

SELECT sc.cno as "課程ID",c.cname as "課程name",
max(sc.score) as "最高分",min(sc.score) as "最低分",avg(sc.score) as "平均分",
CAST(((select count(1) from sc s WHERE s.cno=sc.cno and score>=60)*100/(select count(1) from sc s WHERE s.cno=sc.cno)) as DECIMAL(18,2)) as "及格率",
cast((select count(1) from sc where cno = c.cno and score >= 60)*100.0 / (select count(1) from sc where cno = c.cno) as decimal(18,2)) as "[及格率(%)]"
FROM sc 
LEFT JOIN course c  on sc.cno=c.cno
GROUP BY sc.cno
#這里的s指的就是外表s按照sql的執行順序，從from到where->group by->having->select時
對應的表的形態。即整個查詢沒有select子查詢作為字段時對應的輸出。

23、按各科成績進行排序，并顯示排名

--Score重復時合并名次（利用select字段里的子查詢，利用兩張表的關聯之后通過score的大小的個數來生成序列）
select t.* , (select count(distinct score) 
from sc 
where cno = t.cno 
and score >= t.score) as px 
from sc t 
order by t.cno, px
#這里的s指的就是外表s按照sql的執行順序，從from到where->group by->having->select時對應的表的形態（這里即
Select *
FROM sc s
ORDER BY s.cno,s.score desc
的輸出結果！）
之后在select子查詢中利用連表進行連接

24、查詢學生平均成績及其名次

 SELECT a.*,(SELECT count(1) FROM 
(select s.*,avg(sc.score) as avg2
FROM student s
left JOIN sc on sc.sno=s.sno
WHERE avg2>=a.avg1
GROUP BY s.sno
ORDER BY avg(sc.score) desc) b
)
FROM 
(select s.*,avg(sc.score) as avg1
FROM student s
left JOIN sc on sc.sno=s.sno
GROUP BY s.sno
ORDER BY avg(sc.score) desc) a

# 實例說明原理：
查詢學生成績及其名次

select t.*
FROM sc t
GROUP BY t.cno
ORDER BY cno,score desc

主查詢的輸出結果
在這里插入圖片描述

select t.*,
(SELECT count(DISTINCT score) 
FROM sc 
WHERE cno=t.cno 
and score>=t.score)
FROM sc t
GROUP BY t.cno
ORDER BY cno,score desc

最終輸出結果
在這里插入圖片描述
解釋：這里在子查詢的t就是主查詢的輸出結果（如上圖所示），然后拿t和sc做連接查詢再加上score>=t.score的限制條件，得到最終結果（即sc中對應的各科的成績要大于t.score）

25、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

#注意cast函數的應用
select cno,(CAST(avg(score) as DECIMAL(18,2))) as avg_score
FROM sc
GROUP BY cno
ORDER BY avg_score desc,cno desc

26、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

SELECT sno	
FROM sc
WHERE cno='01'
ORDER BY score desc
LIMIT 1
（查詢topn的另一種方法，通過orderby排序后利用limit來實現）

27、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

#注意下幾種join的鏈接結果
SELECT *
FROM sc a
join sc b
WHERE a.cno!=b.cno
and a.score=b.score
and a.sno!=b.sno

總結：
內連接是最常見的一種連接，只連接匹配的行
在這里插入圖片描述
LEFT JOIN返回左表的全部行和右表滿足ON條件的行，如果左表的行在右表中沒有匹配，那么這一行右表中對應數據用NULL代替。

FULL JOIN 會從左表和右表那里返回所有的行。如果其中一個表的數據行在另一個表中沒有匹配的行，那么對面的數據用NULL代替
在這里插入圖片描述

其他

累加求和套路（求截止當前月份的累計消費額）
練習數據：
建表語句：

create table  test
(user              VARCHAR(3),month             DATE,salary            NUMERIC(6)
);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-03 15:20:52', 33);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-05 15:20:52', 23);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-08 15:20:52', 66);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-02-08 15:20:52', 55);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-02-02 15:20:52', 35);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-02-02 15:20:52', 62);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-02-12 15:20:52', 52);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-01-12 15:20:52', 52);
INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-01-16 15:20:52', 23);

數據展示：
在這里插入圖片描述

1、求每個用戶每月的消費總和：

SELECT user,date_format(month, '%Y-%m'),sum(salary) 
from test 
GROUP BY user,date_format(month, '%Y-%m')

在這里插入圖片描述
2、將月總金額表自己連接自己連接

SELECT * FROM 
(SELECT user,date_format(month, '%Y-%m'),sum(salary) 
from test 
GROUP BY user,date_format(month, '%Y-%m')) a 
join 
(SELECT user,date_format(month, '%Y-%m'),sum(salary) 
from test 
GROUP BY user,date_format(month, '%Y-%m')) b 
on a.user=b.user

在這里插入圖片描述

3、從上一步的結果中
進行分組查詢，分組的字段是a.username a.month
求月累計值：將b.month <= a.month的所有b.salary求和即可
最終求用戶A和B每月累計消費總和：

SELECT a.user,a.dt,max(a.money),sum(b.money) FROM 
(SELECT user,date_format(month, '%Y-%m') dt,sum(salary) money 
from test 
GROUP BY user,date_format(month, '%Y-%m')) a 
join 
(SELECT user,date_format(month, '%Y-%m') dt,sum(salary) money 
from test 
GROUP BY user,date_format(month, '%Y-%m')) b 
on a.user=b.user
WHERE a.dt>=b.dt
GROUP BY a.user,a.dt

在這里插入圖片描述