好多同學在面試的過程中會碰到關于SQL查詢的相關案例，讓手寫SQL語句，直接蒙蔽的不少哦，下面以幾個面試題為例，談談SQL的編寫，有題有答案，大家可以看看參考一下：

數據庫的基本準備：

/*

Navicat MySQL Data Transfer

Source Server? ? ? ? ?: localhost

Source Server Version : 50168

Source Host? ? ? ? ? ?: localhost:3306

Source Database? ? ? ?: test_exam

Target Server Type? ? : MYSQL

Target Server Version : 50168

File Encoding? ? ? ? ?: 65001

Date: 2020-08-21 16:26:33

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `class`

-- ----------------------------

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

? `cid` int(11) NOT NULL AUTO_INCREMENT,

? `caption` varchar(32) NOT NULL,

? PRIMARY KEY (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of class

-- ----------------------------

INSERT INTO `class` VALUES ('1', '三年二班');

INSERT INTO `class` VALUES ('2', '三年三班');

INSERT INTO `class` VALUES ('3', '一年二班');

INSERT INTO `class` VALUES ('4', '二年九班');

-- ----------------------------

-- Table structure for `course`

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

? `cid` int(11) NOT NULL AUTO_INCREMENT,

? `cname` varchar(32) NOT NULL,

? `teacher_id` int(11) NOT NULL,

? PRIMARY KEY (`cid`),

? KEY `fk_course_teacher` (`teacher_id`),

? CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '生物', '1');

INSERT INTO `course` VALUES ('2', '物理', '2');

INSERT INTO `course` VALUES ('3', '體育', '3');

INSERT INTO `course` VALUES ('4', '美術', '2');

-- ----------------------------

-- Table structure for `score`

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

? `sid` int(11) NOT NULL AUTO_INCREMENT,

? `student_id` int(11) NOT NULL,

? `course_id` int(11) NOT NULL,

? `num` int(11) NOT NULL,

? PRIMARY KEY (`sid`),

? KEY `fk_score_student` (`student_id`),

? KEY `fk_score_course` (`course_id`),

? CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),

? CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)

) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '1', '10');

INSERT INTO `score` VALUES ('2', '1', '2', '9');

INSERT INTO `score` VALUES ('5', '1', '4', '66');

INSERT INTO `score` VALUES ('6', '2', '1', '8');

INSERT INTO `score` VALUES ('8', '2', '3', '68');

INSERT INTO `score` VALUES ('9', '2', '4', '99');

INSERT INTO `score` VALUES ('10', '3', '1', '77');

INSERT INTO `score` VALUES ('11', '3', '2', '66');

INSERT INTO `score` VALUES ('12', '3', '3', '87');

INSERT INTO `score` VALUES ('13', '3', '4', '99');

INSERT INTO `score` VALUES ('14', '4', '1', '79');

INSERT INTO `score` VALUES ('15', '4', '2', '11');

INSERT INTO `score` VALUES ('16', '4', '3', '67');

INSERT INTO `score` VALUES ('17', '4', '4', '100');

INSERT INTO `score` VALUES ('18', '5', '1', '79');

INSERT INTO `score` VALUES ('19', '5', '2', '11');

INSERT INTO `score` VALUES ('20', '5', '3', '67');

INSERT INTO `score` VALUES ('21', '5', '4', '100');

INSERT INTO `score` VALUES ('22', '6', '1', '9');

INSERT INTO `score` VALUES ('23', '6', '2', '100');

INSERT INTO `score` VALUES ('24', '6', '3', '67');

INSERT INTO `score` VALUES ('25', '6', '4', '100');

INSERT INTO `score` VALUES ('26', '7', '1', '9');

INSERT INTO `score` VALUES ('27', '7', '2', '100');

INSERT INTO `score` VALUES ('28', '7', '3', '67');

INSERT INTO `score` VALUES ('29', '7', '4', '88');

INSERT INTO `score` VALUES ('30', '8', '1', '9');

INSERT INTO `score` VALUES ('31', '8', '2', '100');

INSERT INTO `score` VALUES ('32', '8', '3', '67');

INSERT INTO `score` VALUES ('33', '8', '4', '88');

INSERT INTO `score` VALUES ('34', '9', '1', '91');

INSERT INTO `score` VALUES ('35', '9', '2', '88');

INSERT INTO `score` VALUES ('36', '9', '3', '67');

INSERT INTO `score` VALUES ('37', '9', '4', '22');

INSERT INTO `score` VALUES ('38', '10', '1', '90');

INSERT INTO `score` VALUES ('39', '10', '2', '77');

INSERT INTO `score` VALUES ('40', '10', '3', '43');

INSERT INTO `score` VALUES ('41', '10', '4', '87');

INSERT INTO `score` VALUES ('42', '11', '1', '90');

INSERT INTO `score` VALUES ('43', '11', '2', '77');

INSERT INTO `score` VALUES ('44', '11', '3', '43');

INSERT INTO `score` VALUES ('45', '11', '4', '87');

INSERT INTO `score` VALUES ('46', '12', '1', '90');

INSERT INTO `score` VALUES ('47', '12', '2', '77');

INSERT INTO `score` VALUES ('48', '12', '3', '43');

INSERT INTO `score` VALUES ('49', '12', '4', '87');

INSERT INTO `score` VALUES ('52', '13', '3', '87');

-- ----------------------------

-- Table structure for `student`

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

? `sid` int(11) NOT NULL AUTO_INCREMENT,

? `gender` char(1) NOT NULL,

? `class_id` int(11) NOT NULL,

? `sname` varchar(32) NOT NULL,

? PRIMARY KEY (`sid`),

? KEY `fk_class` (`class_id`),

? CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '男', '1', '理解');

INSERT INTO `student` VALUES ('2', '女', '1', '鋼蛋');

INSERT INTO `student` VALUES ('3', '男', '1', '張三');

INSERT INTO `student` VALUES ('4', '男', '1', '張一');

INSERT INTO `student` VALUES ('5', '女', '1', '張二');

INSERT INTO `student` VALUES ('6', '男', '1', '張四');

INSERT INTO `student` VALUES ('7', '女', '2', '鐵錘');

INSERT INTO `student` VALUES ('8', '男', '2', '李三');

INSERT INTO `student` VALUES ('9', '男', '2', '李一');

INSERT INTO `student` VALUES ('10', '女', '2', '李二');

INSERT INTO `student` VALUES ('11', '男', '2', '李四');

INSERT INTO `student` VALUES ('12', '女', '3', '如花');

INSERT INTO `student` VALUES ('13', '男', '3', '劉三');

INSERT INTO `student` VALUES ('14', '男', '3', '劉一');

INSERT INTO `student` VALUES ('15', '女', '3', '劉二');

INSERT INTO `student` VALUES ('16', '男', '3', '劉四');

-- ----------------------------

-- Table structure for `teacher`

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

? `tid` int(11) NOT NULL AUTO_INCREMENT,

? `tname` varchar(32) NOT NULL,

? PRIMARY KEY (`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO `teacher` VALUES ('1', '張磊老師');

INSERT INTO `teacher` VALUES ('2', '李平老師');

INSERT INTO `teacher` VALUES ('3', '劉海燕老師');

INSERT INTO `teacher` VALUES ('4', '朱云海老師');

INSERT INTO `teacher` VALUES ('5', '李杰老師');

#1、查詢所有的課程的名稱以及對應的任課老師姓名

SELECT

? ? course.cname,

? ? teacher.tname

FROM

? ? course

INNER JOIN teacher ON course.teacher_id = teacher.tid;

#2、查詢學生表中男女生各有多少人

SELECT

? ? gender 性別,

? ? count(1) 人數

FROM

? ? student

GROUP BY

? ? gender;

#3、查詢物理成績等于100的學生的姓名

SELECT

? ? student.sname

FROM

? ? student

WHERE

? ? sid IN (

? ? ? ? SELECT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? INNER JOIN course ON score.course_id = course.cid

? ? ? ? WHERE

? ? ? ? ? ? course.cname = '物理'

? ? ? ? AND score.num = 100

? ? );

#4、查詢平均成績大于八十分的同學的姓名和平均成績

SELECT

? ? student.sname,

? ? t1.avg_num

FROM

? ? student

INNER JOIN (

? ? SELECT

? ? ? ? student_id,

? ? ? ? avg(num) AS avg_num

? ? FROM

? ? ? ? score

? ? GROUP BY

? ? ? ? student_id

? ? HAVING

? ? ? ? avg(num) > 80

) AS t1 ON student.sid = t1.student_id;

#5、查詢所有學生的學號，姓名，選課數，總成績(注意：對于那些沒有選修任何課程的學生也算在內)

SELECT

? ? student.sid,

? ? student.sname,

? ? t1.course_num,

? ? t1.total_num

FROM

? ? student

LEFT JOIN (

? ? SELECT

? ? ? ? student_id,

? ? ? ? COUNT(course_id) course_num,

? ? ? ? sum(num) total_num

? ? FROM

? ? ? ? score

? ? GROUP BY

? ? ? ? student_id

) AS t1 ON student.sid = t1.student_id;

#6、 查詢姓李老師的個數

SELECT

? ? count(tid)

FROM

? ? teacher

WHERE

? ? tname LIKE '李%';

#7、 查詢沒有報李平老師課的學生姓名(找出報名李平老師課程的學生，然后取反就可以)

SELECT

? ? student.sname

FROM

? ? student

WHERE

? ? sid NOT IN (

? ? ? ? SELECT DISTINCT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? WHERE

? ? ? ? ? ? course_id IN (

? ? ? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? ? ? course.cid

? ? ? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? ? ? course

? ? ? ? ? ? ? ? INNER JOIN teacher ON course.teacher_id = teacher.tid

? ? ? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? ? ? teacher.tname = '李平老師'

? ? ? ? ? ? )

? ? );

#8、 查詢物理課程比生物課程高的學生的學號(分別得到物理成績表與生物成績表，然后連表即可)

SELECT

? ? t1.student_id

FROM

? ? (

? ? ? ? SELECT

? ? ? ? ? ? student_id,

? ? ? ? ? ? num

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? WHERE

? ? ? ? ? ? course_id = (

? ? ? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? ? ? cid

? ? ? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? ? ? course

? ? ? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? ? ? cname = '物理'

? ? ? ? ? ? )

? ? ) AS t1

INNER JOIN (

? ? SELECT

? ? ? ? student_id,

? ? ? ? num

? ? FROM

? ? ? ? score

? ? WHERE

? ? ? ? course_id = (

? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? cid

? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? course

? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? cname = '生物'

? ? ? ? )

) AS t2 ON t1.student_id = t2.student_id

WHERE

? ? t1.num > t2.num;

#9、 查詢沒有同時選修物理課程和體育課程的學生姓名(沒有同時選修指的是選修了一門的，思路是得到物理+體育課程的學生信息表，然后基于學生分組，統計count(課程)=1)

SELECT

? ? student.sname

FROM

? ? student

WHERE

? ? sid IN (

? ? ? ? SELECT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? WHERE

? ? ? ? ? ? course_id IN (

? ? ? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? ? ? cid

? ? ? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? ? ? course

? ? ? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? ? ? cname = '物理'

? ? ? ? ? ? ? ? OR cname = '體育'

? ? ? ? ? ? )

? ? ? ? GROUP BY

? ? ? ? ? ? student_id

? ? ? ? HAVING

? ? ? ? ? ? COUNT(course_id) = 1

? ? );

#10、查詢掛科超過兩門(包括兩門)的學生姓名和班級(求出<60的表，然后對學生進行分組，統計課程數目>=2)

SELECT

? ? student.sname,

? ? class.caption

FROM

? ? student

INNER JOIN (

? ? SELECT

? ? ? ? student_id

? ? FROM

? ? ? ? score

? ? WHERE

? ? ? ? num < 60

? ? GROUP BY

? ? ? ? student_id

? ? HAVING

? ? ? ? count(course_id) >= 2

) AS t1

INNER JOIN class ON student.sid = t1.student_id

AND student.class_id = class.cid;

#11、查詢選修了所有課程的學生姓名(先從course表統計課程的總數，然后基于score表按照student_id分組，統計課程數據等于課程總數即可)

SELECT

? ? student.sname

FROM

? ? student

WHERE

? ? sid IN (

? ? ? ? SELECT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? GROUP BY

? ? ? ? ? ? student_id

? ? ? ? HAVING

? ? ? ? ? ? COUNT(course_id) = (SELECT count(cid) FROM course)

? ? );

#12、查詢李平老師教的課程的所有成績記錄

SELECT

? ? *

FROM

? ? score

WHERE

? ? course_id IN (

? ? ? ? SELECT

? ? ? ? ? ? cid

? ? ? ? FROM

? ? ? ? ? ? course

? ? ? ? INNER JOIN teacher ON course.teacher_id = teacher.tid

? ? ? ? WHERE

? ? ? ? ? ? teacher.tname = '李平老師'

? ? );

#13、查詢全部學生都選修了的課程號和課程名(取所有學生數，然后基于score表的課程分組，找出count(student_id)等于學生數即可)

SELECT

? ? cid,

? ? cname

FROM

? ? course

WHERE

? ? cid IN (

? ? ? ? SELECT

? ? ? ? ? ? course_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? GROUP BY

? ? ? ? ? ? course_id

? ? ? ? HAVING

? ? ? ? ? ? COUNT(student_id) = (

? ? ? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? ? ? COUNT(sid)

? ? ? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? ? ? student

? ? ? ? ? ? )

? ? );

#14、查詢每門課程被選修的次數

SELECT

? ? course_id,

? ? COUNT(student_id)

FROM

? ? score

GROUP BY

? ? course_id;

#15、查詢之選修了一門課程的學生姓名和學號

SELECT

? ? sid,

? ? sname

FROM

? ? student

WHERE

? ? sid IN (

? ? ? ? SELECT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? GROUP BY

? ? ? ? ? ? student_id

? ? ? ? HAVING

? ? ? ? ? ? COUNT(course_id) = 1

? ? );

#16、查詢所有學生考出的成績并按從高到低排序（成績去重）

SELECT DISTINCT

? ? num

FROM

? ? score

ORDER BY

? ? num DESC;

#17、查詢平均成績大于85的學生姓名和平均成績

SELECT

? ? sname,

? ? t1.avg_num

FROM

? ? student

INNER JOIN (

? ? SELECT

? ? ? ? student_id,

? ? ? ? avg(num) avg_num

? ? FROM

? ? ? ? score

? ? GROUP BY

? ? ? ? student_id

? ? HAVING

? ? ? ? AVG(num) > 85

) t1 ON student.sid = t1.student_id;

#18、查詢生物成績不及格的學生姓名和對應生物分數

SELECT

? ? sname 姓名,

? ? num 生物成績

FROM

? ? score

LEFT JOIN course ON score.course_id = course.cid

LEFT JOIN student ON score.student_id = student.sid

WHERE

? ? course.cname = '生物'

AND score.num < 60;

#19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名

SELECT

? ? sname

FROM

? ? student

WHERE

? ? sid = (

? ? ? ? SELECT

? ? ? ? ? ? student_id

? ? ? ? FROM

? ? ? ? ? ? score

? ? ? ? WHERE

? ? ? ? ? ? course_id IN (

? ? ? ? ? ? ? ? SELECT

? ? ? ? ? ? ? ? ? ? course.cid

? ? ? ? ? ? ? ? FROM

? ? ? ? ? ? ? ? ? ? course

? ? ? ? ? ? ? ? INNER JOIN teacher ON course.teacher_id = teacher.tid

? ? ? ? ? ? ? ? WHERE

? ? ? ? ? ? ? ? ? ? teacher.tname = '李平老師'

? ? ? ? ? ? )

? ? ? ? GROUP BY

? ? ? ? ? ? student_id

? ? ? ? ORDER BY

? ? ? ? ? ? AVG(num) DESC

? ? ? ? LIMIT 1

? ? );

以上是整理的面試題中關于SQL編寫的相關案例，希望大家能認真分析，練習一下，希望對大家面試有所幫助！

? ?原創(chuàng)不易，如轉載請標明出處。