Mysql面试热身题集总结
生活随笔
收集整理的這篇文章主要介紹了
Mysql面试热身题集总结
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
文章目錄
- 前言
- 1、熱身題實踐
- 其他
前言
一直有個想法：把面試需要的知識點全都總結(jié)一下，包括數(shù)據(jù)庫，語言，算法，數(shù)據(jù)結(jié)構(gòu)等知識，形成一個面試總結(jié)筆記，這樣以后面試的時候只看這些文章回顧下就行了。今天就先總結(jié)下Mysql的面試熱身題吧，后續(xù)會總結(jié)其他方面的點。當(dāng)然，文章同樣會不定時更新。
1、熱身題實踐
說明：以下五十個語句都按照測試數(shù)據(jù)進(jìn)行過測試，最好每次只單獨運(yùn)行一個語句。 問題及描述： --1.學(xué)生表 Student(S#,Sname,Sage,Ssex) --S# 學(xué)生編號,Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別 --2.課程表 Course(C#,Cname,T#) --C# --課程編號,Cname 課程名稱,T# 教師編號 --3.教師表 Teacher(T#,Tname) --T# 教師編號,Tname 教師姓名 --4.成績表 SC(S#,C#,score) --S# 學(xué)生編號,C# 課程編號,score 分?jǐn)?shù) */ --創(chuàng)建測試數(shù)據(jù) 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' , '孫風(fēng)' , '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' , '數(shù)學(xué)' , '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"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
方法一：連表查詢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.score2、查詢同時存在"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 null3、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
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) desc4、查詢在sc表存在成績的學(xué)生信息的SQL語句。
SELECT * FROM Student s LEFT JOIN SC on s.Sno=SC.Sno GROUP BY s.Sno HAVING count(score)>05、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
SELECT s.Sno , Sname, count(Cno),sum(score) from Student s left join SC on s.Sno=SC.Sno GROUP BY s.Sno6、查詢"李"姓老師的數(shù)量
SELECT count(*) FROM Teacher t WHERE t.Tname LIKE '李%'7、查詢學(xué)過"張三"老師授課的同學(xué)的信息
方法一：連表查詢 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、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息
#注意子查詢的使用場景 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、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
方法一：連表查詢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、查詢沒有學(xué)全所有課程的同學(xué)的信息
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之后是對相應(yīng)的屬性做限制，之后將滿足限制的所有數(shù)據(jù)篩選出來(分成多少組有多少數(shù)據(jù))11、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
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、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
#注意不能在where子句中限制組函數(shù)如：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"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
14、查詢每門課程被選修的學(xué)生數(shù)
SELECT cno,count(sno)FROM scGROUP BY cno15、查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名
SELECT s.sno,sname FROM student s LEFT JOIN sc on s.sno=sc.sno GROUP BY s.sno HAVING count(*)=216、查詢1990年出生的學(xué)生名單
SELECT s.* FROM Student s WHERE s.Sage BETWEEN'1990-01-01' and '1990-12-31'17、查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列
SELECT cno,avg(score) FROM sc GROUP BY cno ORDER BY avg(score) desc,cno asc18、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù)。
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)>=7019、查詢選修"張三"老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
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 120、查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績
SELECT DISTINCT sc.cno,sc.score,sc.snoFROM sc LEFT join sc sc1on sc.sno=sc1.snoWHERE sc.cno!=sc1.cnoand sc.score=sc1.score21、查詢各科成績最好的前兩/三名#重點注意
方法一：連表查詢（重點參考） SELECT * FROM sc a LEFT join sc b on a.cno=b.cno and a.score<b.score#篩選我的成績比別人的成績差的數(shù)據(jù) GROUP BY a.cno,a.sno HAVING count(1)<2#如果我的成績比別人的成績差的數(shù)據(jù)條數(shù)小于2，也就代表有0或1個人的成績比我好，則對應(yīng)的用戶就被篩選出來了 直觀的意義是取分組后的TOPN,實際操作時候，只能是從每組里面取第一條數(shù)據(jù)，只是利用多重分組來將滿足條件的TopN條數(shù)據(jù)給篩選出來而已！#方法二：使用子查詢 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) 相關(guān)子查詢的特點就是子查詢依賴與外部查詢，在這里面其實是 select * from test 已經(jīng)先執(zhí)行了一遍了，查出了所有的數(shù)據(jù) 然后相關(guān)子查詢針對每一行數(shù)據(jù)進(jìn)行select count(*) from test1 where course=a.course and score>a.score 例如：第一行是張三，數(shù)學(xué)77，那么相關(guān)子查詢做的工作就是找出test表所有課程是數(shù)學(xué)的行，查詢 張三，77|李四，68|王五，99然后where條件score>77，查詢出王五，99，count=1，這時候外部條件2>1,符合。第二行是李四，數(shù)學(xué)68，那么相關(guān)子查詢做的工作就是找出test表所有課程是數(shù)學(xué)的行，查詢 張三，77|李四，68|王五，99然后where條件score>68，查詢出張三，77,王五，99，count=2，這時候外部條件2>2,不符合。第三行是王五，數(shù)學(xué)99，那么相關(guān)子查詢做的工作就是找出test表所有課程是數(shù)學(xué)的行，查詢 張三，77|李四，68|王五，99然后where條件score>99，沒有數(shù)據(jù)，這時候外部條件2>0,符合。那么就篩選出了數(shù)學(xué)最大的2個人，張三和王五。 其實這里的子查詢就是找出和當(dāng)前行類型能匹配上的比他大的有多少，沒有比他大的他就是最大 那么找top1就是 1>(xxx)，topN就是N>(xxxxx)#以下幾個題目要了解下子查詢在select字段時的原理
22、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
–及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
23、按各科成績進(jìn)行排序，并顯示排名
--Score重復(fù)時合并名次（利用select字段里的子查詢，利用兩張表的關(guān)聯(lián)之后通過score的大小的個數(shù)來生成序列） 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的執(zhí)行順序，從from到where->group by->having->select時對應(yīng)的表的形態(tài)（這里即 Select * FROM sc s ORDER BY s.cno,s.score desc 的輸出結(jié)果！） 之后在select子查詢中利用連表進(jìn)行連接24、查詢學(xué)生平均成績及其名次
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# 實例說明原理：
查詢學(xué)生成績及其名次
主查詢的輸出結(jié)果
最終輸出結(jié)果
解釋：這里在子查詢的t就是主查詢的輸出結(jié)果（如上圖所示），然后拿t和sc做連接查詢再加上score>=t.score的限制條件，得到最終結(jié)果（即sc中對應(yīng)的各科的成績要大于t.score）
25、查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列
#注意cast函數(shù)的應(yīng)用 select cno,(CAST(avg(score) as DECIMAL(18,2))) as avg_score FROM sc GROUP BY cno ORDER BY avg_score desc,cno desc26、查詢選修"張三"老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
SELECT sno FROM sc WHERE cno='01' ORDER BY score desc LIMIT 1 （查詢topn的另一種方法，通過orderby排序后利用limit來實現(xiàn)）27、查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績
#注意下幾種join的鏈接結(jié)果 SELECT * FROM sc a join sc b WHERE a.cno!=b.cno and a.score=b.score and a.sno!=b.sno總結(jié)：
內(nèi)連接是最常見的一種連接，只連接匹配的行
LEFT JOIN返回左表的全部行和右表滿足ON條件的行，如果左表的行在右表中沒有匹配，那么這一行右表中對應(yīng)數(shù)據(jù)用NULL代替。
FULL JOIN 會從左表 和右表 那里返回所有的行。如果其中一個表的數(shù)據(jù)行在另一個表中沒有匹配的行，那么對面的數(shù)據(jù)用NULL代替
其他
累加求和套路（求截止當(dāng)前月份的累計消費(fèi)額）
練習(xí)數(shù)據(jù)：
建表語句：
數(shù)據(jù)展示：
1、求每個用戶每月的消費(fèi)總和：
SELECT user,date_format(month, '%Y-%m'),sum(salary) from test GROUP BY user,date_format(month, '%Y-%m')
2、將月總金額表 自己連接 自己連接
3、從上一步的結(jié)果中
進(jìn)行分組查詢，分組的字段是a.username a.month
求月累計值： 將b.month <= a.month的所有b.salary求和即可
最終求用戶A和B每月累計消費(fèi)總和：
總結(jié)
以上是生活随笔為你收集整理的Mysql面试热身题集总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 机器学习之提升算法
- 下一篇： 多文件云传输系统框架