MySQL面试经典50题

VIP文章传说三哥

已于 2023-04-11 13:45:07 修改

阅读量873

点赞数 3

文章标签： mysql mysql 面试题面试题 mysql查询 mysql查询语句 Powered by 金山文档

于 2023-02-07 09:59:49 首次发布

本文链接：https://blog.csdn.net/m0_37449634/article/details/128912017

版权

前言

推荐

MySQL笔记：第06章_多表查询

MySQL笔记：第09章_子查询

SQL面试必会50题

说明

练习此篇之前，建议看一看推荐文章，了解MQL：如连接查询和子查询

或者了解一定数据库原理：如关系代数：选择、投影、连接、除运算

SQL经典面试50题

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

教师表：
Teacher(t_id,t_name) –教师编号,教师姓名

成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

据以上信息按照下面要求写出对应的SQL语句。

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画关联图，然后再编写对应的SQL语句就比较容易了。下图是我在草稿纸上画的这4张表的关系图，不好理解，你可以列举一些数据案例来辅助理解：

数据

-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);


-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

-- 插入学生表测试数据
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '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' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');




-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

-- 成绩表测试数据
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);

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

# 法一
#zx
SELECT a.s_id"s_no" ,c.`s_name`"s_name",a.s_score"s_01",b.s_score"s_02" FROM 

(SELECT s_id,c_id,s_score FROM score WHERE c_id='01')
 AS a
INNER JOIN
(SELECT s_id,c_id,s_score FROM score WHERE c_id='02') 
AS b  ON a.s_id=b.s_id
INNER JOIN student  AS c ON c.`s_id`=a.s_id

WHERE a.s_score>b.s_score

#1、查询"01""课程比"O2""课程成绩高的学生的信息及课程分数

# 法二
SELECT student.*,score.c_id,score.s_score
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE student.s_id IN(
  SELECT a.s_id FROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01') AS a
  LEFT JOIN (SELECT s_id,c_id,s_score FROM score WHERE c_id='02') AS b  
  ON a.s_id=b.s_id
  WHERE a.s_score>b.s_score OR b.s_id IS NULL
)

#me
SELECT s_id
FROM student
WHERE s_id IN(
  SELECT a.s_id
  FROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01') AS a 
  INNER JOIN 
  (SELECT s_id,c_id,s_score  FROM score WHERE c_id='02') AS b
  ON a.s_id=b.s_id
  WHERE a.s_score>b.s_score
)

查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

#zx
SELECT 
s_id,AVG(s_score)
FROM score
GROUP BY s_id HAVING AVG(s_score)>60

#me
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score) > 60

#查询平均成绩大于60分的学生的学号和平均成绩 类似的题目（重点）

#法一：

-- 9、查询所有课程成绩小于60分的学生的学号、姓名
SELECT *
FROM student
WHERE s_id NOT IN
(SELECT s_id FROM score WHERE s_score>=60);

#错误的答案（不包含无成绩的）

SELECT *
FROM student
WHERE s_id IN
(SELECT s_id FROM score WHERE s_score<60);

# 法二

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
#IFNULL如果第一个值不为NULL返回第一个值，否则返回第二个值
SELECT  student.s_id,student.s_name,AVG(IFNULL(score.s_score,0)) AS AVG
FROM student 
LEFT JOIN score
ON student.s_id=score.s_id
GROUP BY student.s_id
HAVING AVG(score.s_score)IS NULL OR  AVG(score.s_score)<60