mysql常见面试题

最新推荐文章于 2022-07-28 19:04:39 发布

小小白@

最新推荐文章于 2022-07-28 19:04:39 发布

阅读量897

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_43824520/article/details/121978282

版权

（1）单表查询

表结构及数据

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : cdb

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 17/12/2021 10:05:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` int(11) NOT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `stu_subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `stu_score` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, 1, '张三', '语文', 85);
INSERT INTO `students` VALUES (2, 1, '张三', '数学', 86);
INSERT INTO `students` VALUES (3, 2, '李斯', '英语', 96);
INSERT INTO `students` VALUES (4, 2, '李斯', '语文', 94);
INSERT INTO `students` VALUES (5, 2, '李斯', '数学', 98);
INSERT INTO `students` VALUES (6, 3, '王五', '数学', 56);
INSERT INTO `students` VALUES (7, 3, '王五', '语文', 88);
INSERT INTO `students` VALUES (8, 3, '王五', '英语', 86);
INSERT INTO `students` VALUES (9, 4, '赵六', '英语', 99);
INSERT INTO `students` VALUES (10, 4, '赵六', '数学', 97);

SET FOREIGN_KEY_CHECKS = 1;

查询每个人的总成绩并按从高到低排名(要求显示字段：学号，姓名，总成绩)

考察聚合函数SUM()，返回数值列的总分，GROUP BY 分组（配合聚合函数使用），ORDER BY排序

SELECT
	stu_no AS 学号,
	stu_name AS 姓名,
	SUM( stu_score ) 总分
FROM
	students
GROUP BY
	stu_name,
	stu_no 
ORDER BY
	SUM( stu_score ) DESC;

查询每个人单科最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

考察临时表（需要别名），MAX()函数，用于返回一列中的最大值，NULL 值不包括在计算中。先查学号与最高成绩，再把学号，姓名，课程，最高成绩查出来

SELECT
	t1.stu_no AS 学号,
	t1.stu_name AS 姓名,
	t1.stu_subject AS 科目,
	t1.stu_score AS 最高成绩
FROM
	cdb.students AS t1,
	( SELECT stu_no, MAX( stu_score ) AS maxscore FROM students GROUP BY stu_no ) AS t2 
WHERE
	t1.stu_no = t2.stu_no 
	AND t1.stu_score = t2.maxscore;

查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

考察AVG()函数，用于返回数值列的平均值，NULL 值不包括在计算中。和分组GROUP BY用法

SELECT
	stu_no AS 学号,
	stu_name AS 姓名,
	AVG( stu_score ) AS 均分 
FROM
	students
GROUP BY
	stu_no,
	stu_name

查询各门课程成绩最好的学生(要求显示字段: 学号，姓名，科目，成绩)

类似第二题，把学号换成科目

SELECT
	t1.stu_no AS 学号,
	t1.stu_name AS 姓名,
	t1.stu_subject AS 科目,
	t1.stu_score AS 最高成绩 
FROM
	cdb.students AS t1,
	( SELECT stu_subject, MAX( stu_score ) AS maxscore FROM students GROUP BY stu_subject ) AS t2 
WHERE
	t1.stu_subject = t2.stu_subject 
	AND t1.stu_score = t2.maxscore;

查询各门课程成绩最好的两位学生(要求显示字段: 科目，姓名，成绩)

用top应该更快（找到科目相同的，成绩的，前两列）

SELECT
	t1.stu_subject AS 科目,
	t1.stu_name AS 姓名,
	t1.stu_score AS 成绩 
FROM
	cdb.students AS t1 
WHERE
	( SELECT COUNT(*) FROM students t2 WHERE t1.stu_subject = t2.stu_subject AND t2.stu_score >= t1.stu_score )<= 2 
ORDER BY
	stu_subject DESC;

统计各科目成绩;(要求显示：学号，姓名，语文，数学，英语，总分，平均分)

考察CASE WHEN函数。SUM(CASE WHEN… ELSE 0 END )固定用法

SELECT
	stu_no AS 学号,
	stu_name AS 姓名,
	SUM( CASE WHEN stu_subject = '语文' THEN stu_score ELSE 0 END ) AS 语文,
	SUM( CASE WHEN stu_subject = '数学' THEN stu_score ELSE 0 END ) AS 数学,
	SUM( CASE WHEN stu_subject = '英语' THEN stu_score ELSE 0 END ) AS 英语,
	SUM( stu_score ) AS 总分,
	AVG( stu_score ) AS 平均分 
FROM
	cdb.students 
GROUP BY
	stu_no,
	stu_name

查询各门课程的平均成绩（要求显示字段：课程，平均成绩）

考察AVG函数，GROUP BY 分组

SELECT
	stu_subject AS 科目,
	AVG( stu_score ) AS 均分
FROM
	students 
GROUP BY
	stu_subject;

查询数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

考察COUNT用法，ORDER BY排序，注意：比较的次数+1 = 排名

SELECT 
	stu_no AS 学号, 
	stu_name AS 姓名, 
	stu_score AS 数学成绩, 
	( SELECT COUNT(*) FROM students t1 WHERE stu_subject = '数学' AND t1.stu_score > t2.stu_score )+ 1 AS 名次 
FROM
	students t2 
WHERE
	stu_subject = '数学' 
ORDER BY
	stu_score DESC;

查询数学成绩在2-3名的学生（要求显示字段：学号，姓名，科目，成绩）

依据上题进行where过滤

SELECT
	t3.* 
FROM
	(
	SELECT
		stu_no AS 学号,
		stu_name AS 姓名,
		stu_score AS 数学成绩,
		( SELECT COUNT(*) FROM students t1 WHERE stu_subject = '数学' AND t1.stu_score > t2.stu_score )+ 1 AS 名次 
	FROM
		students t2 
	WHERE
		stu_subject = '数学' 
	) t3 
WHERE
	t3.名次 BETWEEN 2 
	AND 3 
ORDER BY
	t3.名次;

查询李斯的数学成绩的排名

依据上题进行where过滤

SELECT
	t3.* 
FROM
	(
	SELECT
		stu_no AS 学号,
		stu_name AS 姓名,
		stu_score AS 数学成绩,
		( SELECT COUNT(*) FROM students t1 WHERE stu_subject = '数学' AND t1.stu_score > t2.stu_score )+ 1 AS 名次 
	FROM
		students t2 
	WHERE
		stu_subject = '数学' 
	) t3 
WHERE
	t3.姓名 = '李斯'

统计每门课程不及格（0-59），良（60-80），优（80-100）情况

考察CASE WHEN

SELECT
	stu_subject AS 科目,
	SUM( CASE WHEN stu_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END ) AS 不及格,
	SUM( CASE WHEN stu_score BETWEEN 60 AND 80 THEN 1 ELSE 0 END ) AS 良,
	SUM( CASE WHEN stu_score BETWEEN 80 AND 100 THEN 1 ELSE 0 END ) AS 优秀 
FROM
	students 
GROUP BY
	stu_subject;

SELECT
	stu_subject AS 科目,
	( SELECT COUNT(*) FROM students WHERE stu_score < 60 AND stu_subject = b.stu_subject ) AS 不及格,
	( SELECT COUNT(*) FROM students WHERE stu_score BETWEEN 60 AND 80 AND stu_subject = b.stu_subject ) AS 良,
	( SELECT COUNT(*) FROM students WHERE stu_score > 80 AND stu_subject = b.stu_subject ) AS 优秀 
FROM
	students AS b 
GROUP BY
	stu_subject

查询成绩相同的学生信息，显示：学号，姓名，科目，成绩

考察重复数据查询

SELECT
	stu_no AS 学号,
	stu_name AS 姓名,
	stu_subject AS 科目,
	stu_score AS 成绩
FROM
	students 
WHERE
	stu_score IN ( SELECT stu_score FROM students GROUP BY stu_score HAVING count( stu_score ) > 1 )

（2）多表查询

表结构及数据

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : cdb

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 16/12/2021 18:06:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1001, '张三', 18);
INSERT INTO `student` VALUES (1002, '张四', 19);
INSERT INTO `student` VALUES (1003, '张五', 20);
INSERT INTO `student` VALUES (1004, '张六', 19);

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `no` int(11) NOT NULL,
  `id` int(11) NULL DEFAULT NULL,
  `kemu` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, 1001, '语文', 85);
INSERT INTO `grade` VALUES (2, 1001, '数学', 86);
INSERT INTO `grade` VALUES (3, 1002, '英语', 96);
INSERT INTO `grade` VALUES (4, 1002, '语文', 94);
INSERT INTO `grade` VALUES (5, 1002, '数学', 98);
INSERT INTO `grade` VALUES (6, 1003, '数学', 56);
INSERT INTO `grade` VALUES (7, 1003, '语文', 69);
INSERT INTO `grade` VALUES (8, 1003, '英语', 68);
INSERT INTO `grade` VALUES (9, 1004, '英语', 99);
INSERT INTO `grade` VALUES (10, 1004, '数学', 97);

SET FOREIGN_KEY_CHECKS = 1;

查询所有学生的数学成绩，显示字段：姓名, 分数，由高到低

SELECT
	a.`name` AS 姓名,
	b.score AS 分数 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
	AND kemu = '数学' 
ORDER BY
	b.score DESC

统计每个学生的总成绩,显示字段：姓名，总成绩

SELECT
	a.`name` AS 姓名,
	SUM( b.score ) AS 总成绩 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
GROUP BY
	a.`name` DESC

统计单科最好成绩，显示字段：学号，姓名，科目，分数

SELECT
	c.id AS 学号,
	a.`name` AS 姓名,
	c.kemu AS 科目,
	c.score AS 分数 
FROM
	grade c,
	student a,
	( SELECT b.kemu, MAX( b.score ) AS max_score FROM grade b GROUP BY kemu ) t 
WHERE
	c.kemu = t.kemu 
	AND c.score = t.max_score 
	AND a.id = c.id

查询各门课程成绩最好的 2 位学生，显示字段：学号，姓名，科目，分数

SELECT
	b.id AS 学号,
	a.`name` AS 姓名,
	b.kemu AS 科目,
	b.score AS 分数 
FROM
	grade b,
	student a 
WHERE
	( SELECT COUNT(*) FROM grade t2 WHERE b.kemu = t2.kemu AND t2.score > b.score )< 2 
	AND a.id = b.id 
ORDER BY
	b.kemu,
	b.score DESC

计算学生平均分数，显示字段：学号，姓名，平均分数

SELECT
	a.id AS 学号,
	a.`name` AS 姓名,
	c.avg_score AS 均分 
FROM
	student a,
	( SELECT b.id, AVG( b.score ) AS avg_score FROM grade b GROUP BY b.id ) c 
WHERE
	a.id = c.id

统计各科目成绩;计算每个人的成绩，显示字段：学号，姓名，语文，数学，英语，总分，平均分

SELECT
	a.id AS 学号,
	a.NAME AS 姓名,
	SUM( CASE WHEN b.kemu = '语文' THEN score ELSE 0 END ) AS 语文,
	SUM( CASE WHEN b.kemu = '数学' THEN score ELSE 0 END ) AS 数学,
	SUM( CASE WHEN b.kemu = '英语' THEN score ELSE 0 END ) AS 英语,
	SUM( b.score ) AS 总分,
	AVG( b.score ) AS 平均分 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
GROUP BY
	b.id

列出数学成绩的排名，显示字段：学号，姓名，成绩，排名在查询结果表里面添加一个变量@paiming,让它自动加 1

SELECT
	t.id,
	t.score AS 数学分数,
	@paiming := @paiming + 1 AS 排名 
FROM
	( SELECT b.id, b.score FROM grade b WHERE b.kemu = '数学' ORDER BY score DESC ) AS t,
	( SELECT @paiming := 0 ) r

统计每门课程不及格、一般、优秀

SELECT
	b.kemu,
	( SELECT COUNT(*) FROM grade WHERE score < 60 AND kemu = b.kemu ) AS 不及格,
	( SELECT COUNT(*) FROM grade WHERE score BETWEEN 60 AND 80 AND kemu = b.kemu ) AS 一般,
	( SELECT COUNT(*) FROM grade WHERE score > 80 AND kemu = b.kemu ) AS 优秀 
FROM
	grade b 
GROUP BY
	kemu

小小白@

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql常见面试题

建表CREATE TABLE `stu_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stu_no` int(11) NOT NULL, `stu_name` varchar(255) DEFAULT NULL, `stu_subject` varchar(255) DEFAULT NULL, `stu_score` int(25) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB A
复制链接

扫一扫