天天看點
傳回

Mysql面試必看45題Mysql練習

Mysql練習

一、資料表和資料

學生表 *Student*

create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);
insert into Student values('01' , N'趙雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'錢電' , '1990-12-21' , N'男')
insert into Student values('03' , N'孫風' , '1990-05-20' , N'男')
insert into Student values('04' , N'李雲' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吳蘭' , '1992-03-01' , N'女')
insert into Student values('07' , N'鄭竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')

科目表 Course

create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)
); 
insert into Course values('01' , N'國文' , '02')
insert into Course values('02' , N'數學' , '01')
insert into Course values('03' , N'英語' , '03')

教師表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10))
insert into Teacher values('01' , N'張三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')

成績表 SC

create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
); 
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 as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
WHERE b.score>c.score;
    思路： 
    #查詢" 01 "課程比" 02 "課程成績高的學生的資訊及課程分數
#1.查詢學生的資訊及課程分數(使用内連接配接将學生的資訊表（student）及課程分數表（sc）通過and b.CId=01查詢01的成績)
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
#2.在一的基礎上，通過再一次内連接配接拼接CId=02的列
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
#3.添加條件" 01 "課程比" 02 "課程成績高
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
WHERE b.score>c.score;

1.1 查詢同時存在" 01 “課程和” 02 "課程的情況（題目解析：查詢同時擁有01和02課程成績的學生 邏輯：有成績的學生肯定選修了課程）

​ 答案1：

#子查詢
SELECT * FROM 
(SELECT * FROM Sc where cid=01) a
inner
JOIN 
(SELECT * FROM Sc where cid=02) b
on  a.sid=b.sid;

​ 答案1思路：

#1.查詢選修課程1的學生
SELECT * FROM Sc where cid=01
#2.查詢選修課程2的學生
SELECT * FROM Sc where cid=02
#3.将1和2作為子查詢，同時存在" 01 "課程和" 02 "課程作為條件，即1和2的sid相等
SELECT * FROM 
(SELECT * FROM Sc where cid=01) a
inner
JOIN 
(SELECT * FROM Sc where cid=02) b
on  a.sid=b.sid;

​ 答案2

SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
WHERE a.CId=01 and 
b.CId=02

​ 答案2思路

#1.将sc和sc自連接配接
SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
#2.别名為a的sc取01，别名為a的sc取02進行連接配接
SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
WHERE
a.CId=01 
and 
b.CId=02

1.2 查詢存在" 01 “課程但可能不存在” 02 "課程的情況(不存在時顯示為 null )（題目解析：查詢擁有01但是沒有02課程成績的學生 邏輯：有成績的學生肯定選修了課程，沒有成績的可能沒有選修（為啥是可能不是肯定?選修了課程但被取消成績?））

​ 答案

SELECT * FROM  sc a LEFT JOIN  sc b
ON a.SId=b.SId and b.CId=02
where a.CId=01

1.3 查詢不存在" 01 “課程但存在” 02 "課程的情況

SELECT * FROM 
(SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)) a
INNER JOIN sc b
on a.sid=b.sid and b.cid=02

​ 思路

#1.SELECT sid FROM sc where cid=0查詢擁有課程id的所有學生的id
SELECT sid FROM sc where cid=01
#2.查詢不擁有課程id的所有學生的資訊
SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)
#3.内聯a和b且b用于課程2
SELECT * FROM 
(SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)) a
INNER JOIN sc b
on a.sid=b.sid and b.cid=02

2查詢平均成績大于等于 60 分的同學的學生編号和學生姓名和平均成績

SELECT * FROM
(SELECT sid,sname FROM student) a
LEFT JOIN
(SELECT sid,avg(score) as avg FROM sc GROUP BY sid) b
on a.Sid=b.Sid
HAVING b.avg>60

\3. 查詢在 SC 表存在成績的學生資訊

\4. 查詢所有同學的學生編号、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )

SELECT * FROM
(SELECT sid,sname FROM student) a
LEFT JOIN 
(SELECT sid,sum(cid),SUM(score) FROM sc GROUP BY sid) b
on a.sid=b.Sid

4.1 查有成績的學生資訊

\5. 查詢「李」姓老師的數量

SELECT * FROM teacher WHERE tname like '%李%';
SELECT count(1) as cons FROM teacher WHERE tname like '%李%';

\6. 查詢學過「張三」老師授課的同學的資訊

SELECT d.sid,d.sname,a.tname FROM 
(SELECT * FROM teacher) a
INNER JOIN 
(SELECT * FROM course) b
on a.tid=b.tid

INNER JOIN 
(SELECT * FROM sc) c
on b.cid=c.cid

INNER JOIN
(SELECT * FROM student) d
on c.sid=d.sid
where a.tname="張三";

\7. 查詢沒有學全所有課程的同學的資訊

SELECT * FROM 
(SELECT * FROM student) a
INNER JOIN 
(SELECT sid,count(score) as count FROM sc GROUP BY sid)b
on a.sid=b.sid
HAVING b.count != (SELECT COUNT(1) FROM course)

\8. 查詢至少有一門課與學号為" 01 "的同學所學相同的同學的資訊

SELECT  * FROM 
student a
INNER JOIN
(SELECT  * FROM sc where cid in (SELECT cid FROM sc where sid=01) GROUP BY sid) b
on a.Sid=b.sid
           
SELECT DISTINCT a.* FROM 
student a
INNER JOIN
sc b
on a.Sid=b.sid
where b.cid in (SELECT cid FROM sc where sid=01)

\9. 查詢和" 01 "号的同學學習的課程完全相同的其他同學的資訊

SELECT * FROM 
sc
where 
sid not in (SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01))
and sid !=01
GROUP BY sid
HAVING count(1) =(SELECT count(1) FROM sc where sid=01)

思路

#1.查詢 01 号的同學學習的課程
SELECT cid FROM sc where sid=01
#2.查詢學習了01 号的同學沒有學習的課程的同學
SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01)
#3.條件 和01 号的同學學習的課程總數相同
count(1) =(SELECT count(1) FROM sc where sid=01)
#4.沒有學習1号同學沒有學習的課程且課程數量相同可認為和01 号的同學學習的課程完全相同
SELECT * FROM 
sc
where 
sid not in (SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01) )
and sid !=01
GROUP BY sid
HAVING count(1) =(SELECT count(1) FROM sc where sid=01)

\10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名

#1.查詢張三的教師号tid
SELECT tid FROM teacher where tname='張三'
#2.通過tid查詢張三的所有課程的cid
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='張三') a
INNER JOIN 
course b
on a.tid=b.tid
#3.查詢cid在張三課程中的學生sid
SELECT sid FROM sc where cid  in(
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='張三') a
INNER JOIN 
course b
on a.tid=b.tid
)GROUP BY sid
#4.查詢學生表和成績表的内聯表，且成績表中sid不在3中
SELECT DISTINCT c.Sname
FROM
student c
INNER JOIN
sc d
on c.Sid=d.SId and d.sid not in (SELECT sid FROM sc where cid  in(
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='張三') a
INNER JOIN 
course b
on a.tid=b.tid
)GROUP BY sid)

\11. 查詢兩門及其以上不及格課程的同學的學号，姓名及其平均成績

#查詢兩門及其以上不及格課程的同學的學号，姓名及其平均成績 
#1.查詢學生不及格課程總數
SELECT sid FROM sc where score<60  GROUP BY sid HAVING COUNT(1) >=2;
#2.查詢查詢學生不及格課程總數大于2的學生sid 
SELECT sid FROM sc where score<60  GROUP BY sid
#3.查詢學号，姓名及其平均成績且學号sid在2中
SELECT a.Sname,b.* FROM
student a
INNER JOIN
(
SELECT sid,avg(score) as avgscore FROM sc GROUP BY sid HAVING sid  in (SELECT sid FROM sc where score<60  GROUP BY sid HAVING COUNT(1) >=2)
)b
on 
a.sid=b.sid

\12. 檢索" 01 "課程分數小于 60，按分數降序排列的學生資訊

SELECT a.*,b.score FROM 
student A
INNER JOIN
(SELECT * FROM SC WHERE CID=01 and score<60)B
ON a.sid=b.sid
ORDER BY score desc

\13. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

SELECT * FROM sc a
LEFT JOIN
(SELECT sid,avg(score) as avg1 FROM sc GROUP BY sid ORDER BY  avg1) b
on a.Sid=b.sid
ORDER BY b.avg1 desc

\14. 查詢各科成績最高分、最低分和平均分：

以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

要求輸出課程号和選修人數，查詢結果按人數降序排列，若人數相同，按課程号升序排列

#2.sum(case when score>=70 and score<80 THEN 1 else 0 end)/count(1) as 中等率, sum循環，滿足THEN 1（加一）不滿足else 0（+0）
SELECT a.CId,a.Cname,b.* FROM
course a
RIGHT JOIN 
(SELECT cid,max(score) as max_score,min(score) as min_score,avg(score) as avg_score,COUNT(1) as 選修人數,
sum(case when score>=60 THEN 1 else 0 end)/count(1)as 及格率,
sum(case when score>=70 and score<80 THEN 1 else 0 end)/count(1) as 中等率,
sum(case when score>=80 and score<90 THEN 1 else 0 end)/count(1) as 優良率,
sum(case when score>=90  THEN 1 else 0 end)/count(1) as 優秀率
FROM sc GROUP BY cid)b
on a.cid=b.cid
ORDER BY b.選修人數 desc,b.cid asc

\15. 按各科成績進行排序，并顯示排名， Score 重複時保留名次空缺

SELECT 
sid,cid,score,
@rank:=@rank+1
FROM sc,(SELECT @rank:=0) as t ORDER BY score desc;

15.1 按各科成績進行排序，并顯示排名， Score 重複時合并名次

SELECT a.sid,a.cid,a.score,
case when @sco=score THEN @rank:=@rank
else @rank:=@rank+1 END as rn,
@sco:=score
FROM 
sc as a,
(SELECT @rank:=0,@sco:=null) as b
ORDER BY score desc;
           
SELECT a.sid,a.cid,a.score,
case when @sco=score THEN @rank:=@rank
     when @sco:=score then @rank:=@rank+1 
		 END  as rm
FROM 
sc as a,
(SELECT @rank:=0,@sco:=null) as b
ORDER BY score desc;

\16. 查詢學生的總成績，并進行排名，總分重複時保留名次空缺

16.1 查詢學生的總成績，并進行排名，總分重複時不保留名次空缺

SELECT 
sid,sum(score)as 總成績,
case when score=@sco then @rank:=@rank
		 when @sco:=score then @rank:=@rank+1
		 END as 排名
FROM sc,(SELECT @rank:=0,@sco:=null) as b 
GROUP BY sid 
ORDER BY 總成績 desc;

\17. 統計各科成績各分數段人數：課程編号，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT a.Cname as 課程名稱,b.*
FROM course  as a
INNER JOIN
(SELECT 
cid as 課程編号,
count(1)  as 總人數,
concat( sum(case when score>85 and score<=100 THEN 1 else 0 end)/count(1)*100,'%')  as '[100-85)',
concat(sum(case when score>70 and score<=85 THEN 1 else 0 end)/count(1)*100,'%')  as '[85-70)',
concat(sum(case when score>60 and score<=70 THEN 1 else 0 end)/count(1)*100,'%') as '[70-60)',
concat(sum(case when score>0 and score<=60 THEN 1 else 0 end)/count(1)*100,'%')  as '[60-0]'
FROM sc GROUP BY cid) as b
on a.cid=b.課程編号;

\18. 查詢各科成績前三名的記錄（若大于這個成績的成績數量少于三，則這條資料為前三名）

SELECT * 
FROM sc as a
where 
(SELECT count(1) FROM sc b where b.cid=a.cid and b.score>a.score)<=3 ORDER BY cid,score desc

\19. 查詢每門課程被選修的學生數

\20. 查詢出隻選修兩門課程的學生學号和姓名

SELECT a.Sname,b.sid
FROM student as a
INNER JOIN
(SELECT count(1) as aa,sid FROM sc GROUP BY sid HAVING aa=2) as b
on a.sid=b.sid

\21. 查詢男生、女生人數

\22. 查詢名字中含有「風」字的學生資訊

\23. 查詢同名同性學生名單，并統計同名人數

SELECT count(1) FROM 
student as a
INNER JOIN student as b
on a.Sname=b.Sname and a.Ssex=b.Ssex and a.sid != b.sid;

\24. 查詢 1990 年出生的學生名單

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

\26. 查詢平均成績大于等于 85 的所有學生的學号、姓名和平均成績

SELECT 
b.`學号`,
a.sname as 姓名,
b.`平均成績`
FROM
student as a
INNER JOIN
(SELECT 
sid as 學号,
avg(score) as 平均成績
FROM sc GROUP BY sid HAVING 平均成績>=85) as b
on a.Sid=b.學号;

\27. 查詢課程名稱為「數學」，且分數低于 60 的學生姓名和分數

SELECT a.Sname,b.score
FROM student as a
INNER JOIN
(SELECT score,sid
FROM sc 
WHERE 
cid=(SELECT cid FROM course where Cname='數學')
and score<60) as b
on a.Sid=b.sid
;

\28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
LEFT JOIN
sc as b
on a.SId=b.SId;

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

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
inner JOIN
(SELECT * FROM sc where score>70)as b
on a.SId=b.SId;

\30. 查詢不及格的課程

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
inner JOIN
(SELECT * FROM sc where score<60)as b
on a.SId=b.SId;

\31. 查詢課程編号為 01 且課程成績在 80 分以上的學生的學号和姓名

\32. 求每門課程的學生人數

\33. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績

\34. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績

\35. 查詢不同課程成績相同的學生的學生編号、課程編号、學生成績

SELECT DISTINCT * FROM sc as a
INNER JOIN sc as b
on a.sid=b.sid and a.cid != b.cid and a.score =b.score GROUP BY a.sid,a.cid;

\36. 查詢每門功成績最好的前兩名

\37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。

\38. 檢索至少選修兩門課程的學生學号

\39. 查詢選修了全部課程的學生資訊

\40. 查詢各學生的年齡，隻按年份來算

\41. 按照出生日期來算，目前月日 < 出生年月的月日則，年齡減一

\42. 查詢本周過生日的學生

SELECT * FROM student where week(sage)=WEEK('2020-01-02')

\43. 查詢下周過生日的學生

\44. 查詢本月過生日的學生

\45. 查詢下月過生日的學生

mysql
上一篇: Mysql Innodb的undo redo操作過程
下一篇: 資料庫的redo undo 思考總結

繼續閱讀