面试之数据库SQL编写实战案例

好多同学在面试的过程中会碰到关于SQL查询的相关案例，让手写SQL语句，直接蒙蔽的不少哦，下面以几个面试题为例，谈谈SQL的编写，有题有答案，大家可以看看参考一下：数据库的基本准备：/*Navicat MySQL Data TransferSource Server: localhostSource Server Version : 50168Source Host: localhost:3306

编程指南针

374人浏览 · 2021-11-14 08:27:10

编程指南针 · 2021-11-14 08:27:10 发布

好多同学在面试的过程中会碰到关于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编写的相关案例，希望大家能认真分析，练习一下，希望对大家面试有所帮助！

原创不易，如转载请标明出处。

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Js分割字符串(单个分割符、多个分割符、正则)

1.单个分割符1.1定义和用法split() 方法用于把一个字符串分割成字符串数组。1.2语法stringObject.split(separator,howmany)参数描述separator必需。字符串或正则表达式，从该参数指定的地方分割 stringObject。howmany可选。该参数可指定返回的数组的最大长度。如果设置了该参数，返回的子串不会多于这个参数指定的数组。如果没有设置该参数，

腾讯云开发者社区

curl

什么是curl命令？curl是利用URL语法在命令行方式下工作的开源文件传输工具。它被广泛应用在Unix、多种Linux发行版中，并且有DOS和Win32、Win64下的移植版本。如何在windows下使用curl命令？第一步：进入curl下载官网，下载合适的版本，我这里下载的是windows 64位的curl。其中我下载的zip文件。另外CAB文件也是压缩文件，这...

腾讯云开发者社区

Zynq7000 USB2.0协议解析及USB控制器详解

USB 2.0规范及控制器文章目录USB 2.0规范及控制器USB2.0Univerasl Serial BusUSBHostUSB总线接口标准总线信号USB拓扑结构数据流模型数据编解码和位填充USB逻辑部件USB时间基准USB 描述符USB设备USB设备供电方式USB设备分层USB设备插入检测机制USB设备状态USB总线枚举USB传输传输类型包(Packet)事务(transaction)传输(