在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

50道MySQL面試題，經(jīng)典~

一、測試表數(shù)據(jù)

學生表：student [學號,學生姓名,出生年月,性別]

成績表：score [學號,課程號,成績]

課程表：course [課程號,課程名稱,教師號]

教師表：teacher [教師號,教師姓名)



下面是表結(jié)構(gòu)和數(shù)據(jù)，直接執(zhí)行即可~

#來源公眾號：【碼農(nóng)編程進階筆記】
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '數(shù)學老師-杰斯');
INSERT INTO `teacher` VALUES ('02', '語文老師-盲僧');
INSERT INTO `teacher` VALUES ('03', '英語老師-菲歐娜');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '流浪法師-瑞茲', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '探險家-EZ', '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', '戰(zhàn)爭女神-希維爾', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '光輝女郎-拉克絲', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '放逐之刃-銳雯', '1990-01-20', '女');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_score` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '語文', '02');
INSERT INTO `course` VALUES ('02', '數(shù)學', '01');
INSERT INTO `course` VALUES ('03', '英語', '03');

二、50道SQL題（不含答案）

好了，請打開你親愛的navicat，關(guān)閉百度、手機等一切阻礙你進步的絆腳石。開始你的表演~

#來源公眾號：【碼農(nóng)編程進階筆記】
-- 50道SQL面試題
-- 1、查詢課程編號為“01”的課程比“02”的課程成績高的所有學生的學號（難）

-- 2、查詢平均成績大于60分的學生的學號和平均成績

-- 3、查詢所有學生的學號、姓名、選課數(shù)、總成績

-- 4、查詢姓“猴”的老師的個數(shù)

-- 5、查詢沒學過“數(shù)學老師-杰斯”老師課的學生的學號、姓名

-- 6、查詢學過“數(shù)學老師-杰斯”老師所教的所有課的同學的學號、姓名

-- 7、查詢學過編號為“01”的課程并且也學過編號為“02”的課程的學生的學號、姓名

-- 8、查詢課程編號為“02”的總成績

-- 9、查詢所有，課程成績小于60分的學生的學號、姓名

-- 10、查詢沒有學全所有課的學生的學號、姓名

-- 11、查詢至少有一門課與學號為“01”的學生所學課程相同的學生的學號和姓名 （難）

-- 12、查詢和“01”號同學所學課程完全相同的其他同學的學號（難）

-- 13、查詢沒學過"數(shù)學老師-杰斯"老師講授的任一門課程的學生姓名

-- 14、空

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

-- 16、檢索"01"課程分數(shù)小于60，按分數(shù)降序排列的學生信息

-- 17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績(難)

-- 18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率

-- 19、查詢學生的總成績并進行排名

-- 20、查詢不同老師所教不同課程平均分,從高到低顯示

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

-- 22、按各科成績進行排序，并顯示排名(難)

-- 23、查詢每門功課成績最好的前兩名學生姓名

-- 24、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

-- 25、查詢各科成績前三名的記錄（不考慮成績并列情況）

-- 26、使用分段[100-85],[85-70],[70-60],[<60]來統(tǒng)計各科成績，分別統(tǒng)計各分數(shù)段人數(shù)：課程ID和課程名稱

-- 27、查詢每門課程被選修的學生數(shù)

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

-- 29、查詢男生、女生人數(shù)

-- 30、查詢名字中含有"風"字的學生信息

-- 31、查詢1990年出生的學生名單

-- 32、查詢平均成績大于等于85的所有學生的學號、姓名和平均成績

-- 33、查詢每門課程的平均成績，結(jié)果按平均成績升序排序，平均成績相同時，按課程號降序排列

-- 34、查詢課程名稱為"數(shù)學"，且分數(shù)低于60的學生姓名和分數(shù)

-- 35、查詢所有學生的課程及分數(shù)情況

-- 36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分數(shù)

-- 37、查詢不及格的課程并按課程號從大到小排列

-- 38、查詢課程編號為03且課程成績在80分以上的學生的學號和姓名

-- 39、求每門課程的學生人數(shù)

-- 40、查詢選修“數(shù)學老師-杰斯”老師所授課程的學生中成績最高的學生姓名及其成績

-- 41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 （難）

-- 42、統(tǒng)計每門課程的學生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

-- 43、檢索至少選修兩門課程的學生學號

-- 44、查詢選修了全部課程的學生信息

-- 45、查詢各學生的年齡

-- 46、查詢兩門以上不及格課程的同學的學號及其平均成績

-- 47、查詢本月過生日的學生

-- 48、查詢下一個月過生日的學生

三、50道SQL題（含答案）

-- 先全數(shù)據(jù)關(guān)聯(lián)一下看看
select * from student stu,score sc,course c,teacher t
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id;
50道SQL面試題開擼

1、查詢課程編號為“01”的課程比“02”的課程成績高的所有學生的學號（難）

#來源公眾號：【碼農(nóng)編程進階筆記】

SELECT a.s_id from
(select * from score sc1 where sc1.c_id = '01') a ,
(select * from score sc2 where sc2.c_id = '02') b
where a.s_id = b.s_id and a.s_score > b.s_score;

2、查詢平均成績大于60分的學生的學號和平均成績

#來源公眾號：【碼農(nóng)編程進階筆記】

select a.s_id,a.avg_score from
(select stu.s_id,stu.s_name,AVG(sc.s_score) as avg_score from student stu, score sc where stu.s_id = sc.s_id GROUP BY stu.s_id) a
where a.avg_score > 60 ORDER BY avg_score desc;  

3、查詢所有學生的學號、姓名、選課數(shù)、總成績

#來源公眾號：【碼農(nóng)編程進階筆記】

-- 我這里MySQL8執(zhí)行sql時是出現(xiàn)了sql_mode=only_full_group_by錯誤，執(zhí)行下行sql進行配置即可解決；
-- set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
select stu.s_id,stu.s_name,count(sc.c_id),IFNULL(SUM(sc.s_score),0) from student stu
LEFT JOIN score sc on stu.s_id = sc.s_id GROUP BY stu.s_id;
4、查詢姓“猴”的老師的個數(shù)
select count(*) from teacher where t_name like '猴%';

5、查詢沒學過“數(shù)學老師-杰斯”老師課的學生的學號、姓名

SELECT * from student a where a.s_id not in
 (select stu.s_id from student stu,score sc,course c,teacher t
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id and t.t_name ='數(shù)學老師-杰斯')

6、查詢學過“數(shù)學老師-杰斯”老師所教的所有課的同學的學號、姓名

select stu.s_id,stu.s_name from
student stu,score sc,course c,teacher t
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id and t.t_name ='數(shù)學老師-杰斯';

7、查詢學過編號為“01”的課程并且也學過編號為“02”的課程的學生的學號、姓名

SELECT a.s_id,a.s_name from
(SELECT stu1.s_id,stu1.s_name from student stu1, score sc1 where stu1.s_id = sc1.s_id and sc1.c_id ='01') a,  
(SELECT stu2.s_id,stu2.s_name from student stu2, score sc2 where stu2.s_id = sc2.s_id and sc2.c_id ='02') b
where a.s_id = b.s_id;

8、查詢課程編號為“02”的總成績

select SUM(sc.s_score) from score sc where c_id = '02';

9、查詢所有，課程成績小于60分的學生的學號、姓名

select stu.s_id,stu.s_name from student stu,score sc where stu.s_id = sc.s_id and sc.s_score < 60 GROUP BY stu.s_id;
 10、查詢沒有學全所有課的學生的學號、姓名
SELECT stu2.s_id,stu2.s_name from student stu2 ,
(SELECT stu.s_id,stu.s_name,IFNULL(COUNT(c.c_id),0) as c_count from student stu
LEFT JOIN score sc on stu.s_id = sc.s_id JOIN course c on sc.c_id = c.c_id GROUP BY stu.s_id) a
where stu2.s_id = a.s_id and a.c_count = (SELECT count(*) from course);
 11、查詢至少有一門課與學號為“01”的學生所學課程相同的學生的學號和姓名 （難）
SELECT a.s_id,a.s_name from
(SELECT stu1.s_id,stu1.s_name,sc1.c_id from student stu1,score sc1 where stu1.s_id = sc1.s_id) a,
(SELECT sc2.c_id from student stu2,score sc2 where stu2.s_id = sc2.s_id and stu2.s_id = '01') b
where a.c_id = b.c_id GROUP BY a.s_id;
12、查詢和“01”號同學所學課程完全相同的其他同學的學號（難）
select s_id,s_name from student
where s_id in (
select s_id from score
where s_id <> '01'
group by s_id
having count(*) = (select count(*) from score where s_id = '01'))
and s_id not in(select distinct s_id from score where c_id not in (select c_id from score where s_id = '01'))
13、查詢沒學過"數(shù)學老師-杰斯"老師講授的任一門課程的學生姓名
SELECT s_name from student stu2 where stu2.s_id not in
(select DISTINCT stu.s_id from student stu,score sc,course c,teacher t
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id and t.t_name = '數(shù)學老師-杰斯') ;
 15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

SELECT a.s_id, a.s_name, avg from student stu2,
(SELECT stu.s_id, stu.s_name, count(*) as count, AVG(sc.s_score) as avg from student stu, score sc
where stu.s_id = sc.s_id and sc.s_score < 60 GROUP BY stu.s_id) a where stu2.s_id = a.s_id and a.count >= 2;

16、檢索"01"課程分數(shù)小于60，按分數(shù)降序排列的學生信息

SELECT stu.s_id, stu.s_name,sc.s_score from student stu, score sc where stu.s_id = sc.s_id and sc.c_id = '01' and sc.s_score < 60 ORDER BY sc.s_score desc;

17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績(難)

-- 全量展示
SELECT * from
(SELECT stu.s_id,stu.s_name,sc.c_id,IFNULL(sc.s_score,0) from student stu LEFT JOIN score sc on stu.s_id = sc.s_id ) a,
(SELECT sc1.s_id,AVG(sc1.s_score) as avg from score sc1 GROUP BY sc1.s_id) b
where a.s_id = b.s_id ORDER BY avg desc;

-- 橫排展示
select s_id,s_name,
(select s_score from score where score.s_id=student.s_id and c_id='01') as '語文',
(select s_score from score where score.s_id=student.s_id and c_id='02') as '數(shù)學',
(select s_score from score where score.s_id=student.s_id and c_id='03') as '英語',
(select avg(s_score) from score where score.s_id=student.s_id) avg_score
from student
order by avg_score desc;
18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率

SELECT
c.c_id,c.c_name,MAX(sc.s_score),MIN(sc.s_score),AVG(sc.s_score),CONCAT(round(100 * sum(case when sc.s_score>=60 then 1.0 else 0.0 end)/count(*),2),'%') as '及格率'
from score sc, course c
where sc.c_id = c.c_id
GROUP BY c.c_id;

19、查詢學生的總成績并進行排名

SELECT
stu.s_id,stu.s_name,IFNULL(SUM(sc.s_score),0) as totalCount
from student stu LEFT JOIN score sc on stu.s_id = sc.s_id GROUP BY stu.s_id
ORDER BY totalCount desc

20、查詢不同老師所教不同課程平均分,從高到低顯示

SELECT
t.t_name,c.c_name,AVG(sc.s_score) as avg
from score sc, course c, teacher t where sc.c_id = c.c_id and c.t_id = t.t_id
GROUP BY t.t_id
ORDER BY avg desc;

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

SELECT
stu.s_id,stu.s_name,AVG(sc.s_score) as avg,rank() over(ORDER BY AVG(sc.s_score) desc) as `no`
from student stu
LEFT JOIN score sc on stu.s_id = sc.s_id
GROUP BY stu.s_id
ORDER BY avg desc;

22、按各科成績進行排序，并顯示排名(難)

#來源公眾號：【碼農(nóng)編程進階筆記】

select
c_id,s_id,s_score,rank() over(partition by c_id
order by s_score desc) num_row
from score;

23、查詢每門功課成績最好的前兩名學生姓名

#來源公眾號：【碼農(nóng)編程進階筆記】

select a.s_id,a.s_name,b.num_row
from student a
join (select s_id, c_id, row_number() over(partition by c_id order by s_score desc) num_row from score) b on a.s_id=b.s_id
where b.num_row<=2;

24、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

#來源公眾號：【碼農(nóng)編程進階筆記】

select a.s_id,a.s_name,b.c_id,b.s_score,b.num_row
from student a
,(select
      s_id,
      c_id,
      s_score,
      row_number() over(partition by c_id order by s_score desc) num_row from score) b
where a.s_id=b.s_id
and b.num_row between 2 and 3;
25、查詢各科成績前三名的記錄（不考慮成績并列情況）
#來源公眾號：【碼農(nóng)編程進階筆記】

select a.s_id,a.s_name,b.c_id,b.s_score,b.num_row
from student a
,(select
      s_id,
      c_id,
      s_score,
      row_number() over(partition by c_id order by s_score desc) num_row from score) b
where a.s_id=b.s_id
and b.num_row between 1 and 3;
26、使用分段[100-85],[85-70],[70-60],[<60]來統(tǒng)計各科成績，分別統(tǒng)計各分數(shù)段人數(shù)：課程ID和課程名稱
#來源公眾號：【碼農(nóng)編程進階筆記】
SELECT c.c_id,c.c_name,
(select count(*) from score sc1 where sc1.c_id=c.c_id and sc1.s_score >= 85) as '[100-85]',
(select count(*) from score sc2 where sc2.c_id=c.c_id and sc2.s_score BETWEEN 70 and 85) as '[85-70]',
(select count(*) from score sc3 where sc3.c_id=c.c_id and sc3.s_score BETWEEN 60 and 70) as '[70-60]',
(select count(*) from score sc4 where sc4.c_id=c.c_id and sc4.s_score < 60) as '[<60]'
from course c
GROUP BY c.c_id;
27、查詢每門課程被選修的學生數(shù)
select c_id,count(c_id) count from score group by c_id;

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

#來源公眾號：【碼農(nóng)編程進階筆記】

select a.s_id,a.s_name,count(b.s_id) count
from student a
join score b on a.s_id=b.s_id
group by s_id
having count=2;
29、查詢男生、女生人數(shù)
select s_sex,count(s_sex) count_sex from student group by s_sex;
30、查詢名字中含有"風"字的學生信息
select * from student where s_name like '%風%';

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

select s_id,s_name from student where year(s_birth)='1990';
 32、查詢平均成績大于等于85的所有學生的學號、姓名和平均成績
select a.s_id,a.s_name,avg(b.s_score) avg
from student a
join score b on a.s_id=b.s_id
group by b.s_id
having avg>=85;
33、查詢每門課程的平均成績，結(jié)果按平均成績升序排序，平均成績相同時，按課程號降序排列
select
c_id, avg(s_score) avg_score
from score
group by c_id
order by avg_score,c_id desc;

34、查詢課程名稱為"數(shù)學"，且分數(shù)低于60的學生姓名和分數(shù)

select sc.c_id,c.c_name,stu.s_id,stu.s_name,sc.s_score
from score sc
join course c on sc.c_id=c.c_id
join student stu on sc.s_id=stu.s_id
where sc.s_score<60 and c.c_name='數(shù)學';
35、查詢所有學生的課程及分數(shù)情況
select stu.s_id,stu.s_name,c.c_id,c.c_name,sc.s_score
from student stu
left join score sc on stu.s_id=sc.s_id
left join course c on sc.c_id=c.c_id ;

36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分數(shù)

select c.s_name,b.c_name,a.s_score
from score a
join course b on a.c_id=b.c_id
join student c on a.s_id=c.s_id
where a.s_score>70;

37、查詢不及格的課程并按課程號從大到小排列

select c_id from score where s_score < 60 GROUP BY c_id order by c_id desc;

38、查詢課程編號為03且課程成績在80分以上的學生的學號和姓名

select stu.s_id,stu.s_name
from student stu, score sc where stu.s_id=sc.s_id
and sc.s_score>80 and sc.c_id=03;

39、求每門課程的學生人數(shù)

select c_id, count(*) count from score group by c_id;

 40、查詢選修“數(shù)學老師-杰斯”老師所授課程的學生中成績最高的學生姓名及其成績

select stu.s_id,stu.s_name,sc.s_score from
student stu,score sc,course c,teacher t where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id
and t.t_name='數(shù)學老師-杰斯'
order by sc.s_score desc
limit 1;
41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 （難）
select a.s_id,a.c_id,a.s_score
from score a, score b where a.s_score=b.s_score
and a.c_id <> b.c_id and a.s_id=b.s_id
group by a.s_id,a.c_id,a.s_score;
42、統(tǒng)計每門課程的學生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

select
c_id, count(*) count
from score
group by c_id
order by c_id asc;

43、檢索至少選修兩門課程的學生學號

select s_id,count(*) count from score group by s_id having count>=2;

 44、查詢選修了全部課程的學生信息

select
stu.s_id,stu.s_name,count(*) count
from student stu,score sc
where stu.s_id = sc.s_id
group by sc.s_id
having count = 3;
45、查詢各學生的年齡
SELECT * ,(YEAR(CURDATE()) - YEAR(s_birth)) AS age from student;
46、查詢兩門以上不及格課程的同學的學號及其平均成績
select s_id,avg(s_score) avg
from score
where s_score < 60
group by s_id
having count(*) >= 2;
 47、查詢本月過生日的學生
select s_id from student where month(s_birth) = month(now());
48、查詢下一個月過生日的學生
select s_id from student where month(s_birth) = month(now())+1;


作者：碼農(nóng)編程進階筆記

歡迎關(guān)注微信公眾號 ：碼農(nóng)編程進階筆記