好多同学在面试的过程中会碰到关于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编写的相关案例，希望大家能认真分析，练习一下，希望对大家面试有所帮助！
\
原创不易，如转载请标明出处。
