数据库MySQL经典面试题之SQL语句

古城微笑少年丶 2023-02-27 05:48 39阅读 0赞

学生表

CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `student_name` varchar(255) DEFAULT NULL,
      `age` datetime DEFAULT NULL,
      `sex` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

教师表

CREATE TABLE `teacher` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `teacher_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

课程表

CREATE TABLE `course` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `course_name` varchar(255) DEFAULT NULL,
      `teacher_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

分数表

CREATE TABLE `score` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `student_id` int(11) DEFAULT NULL,
      `course_id` int(11) DEFAULT NULL,
      `score` decimal(18,1) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `course`(`id`, `course_name`, `teacher_id`) VALUES (1, '语文', 2);
    INSERT INTO `course`(`id`, `course_name`, `teacher_id`) VALUES (2, '数学', 1);
    INSERT INTO `course`(`id`, `course_name`, `teacher_id`) VALUES (3, '英语', 3);
    
    
    
    
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (1, 1, 1, 80.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (2, 1, 2, 90.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (3, 1, 3, 99.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (4, 2, 1, 70.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (5, 2, 2, 60.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (6, 2, 3, 80.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (7, 3, 1, 80.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (8, 3, 2, 80.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (9, 3, 3, 80.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (10, 4, 1, 50.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (11, 4, 2, 30.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (12, 4, 3, 20.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (13, 5, 1, 76.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (14, 5, 2, 87.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (15, 6, 1, 31.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (16, 6, 3, 34.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (17, 7, 2, 89.0);
    INSERT INTO `score`(`id`, `student_id`, `course_id`, `score`) VALUES (18, 7, 3, 98.0);
    
    
    
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (1, '赵雷', '1990-01-01 00:00:00', '男');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (2, '钱电', '1990-12-21 00:00:00', '男');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (3, '孙风', '1990-05-20 00:00:00', '男');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (4, '李云', '1990-08-06 00:00:00', '男');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (5, '周梅', '1991-12-01 00:00:00', '女');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (6, '吴兰', '1992-03-01 00:00:00', '女');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (7, '郑竹', '1989-07-01 00:00:00', '女');
    INSERT INTO `student`(`id`, `student_name`, `age`, `sex`) VALUES (8, '王菊', '1990-01-20 00:00:00', '女');
    
    
    
    INSERT INTO `teacher`(`id`, `teacher_name`) VALUES (1, '张三');
    INSERT INTO `teacher`(`id`, `teacher_name`) VALUES (2, '李四');
    INSERT INTO `teacher`(`id`, `teacher_name`) VALUES (3, '王五');

### sql语句面试题 ###

**1、查询语文比数学成绩高的学生的信息及课程分数**

SELECT
    	t1.student_id,
    	t1.score 语文,
    	t2.score 数学 
    FROM
    	(
    	SELECT
    		sc.*,
    		c.course_name 
    	FROM
    		score sc
    		INNER JOIN course c 
    	WHERE
    		sc.course_id = c.id 
    		AND c.course_name = '语文' 
    	) t1,
    	(
    	SELECT
    		sc.*,
    		c.course_name 
    	FROM
    		score sc
    		INNER JOIN course c 
    	WHERE
    		sc.course_id = c.id 
    		AND c.course_name = '数学' 
    	) t2 
    WHERE
    	t1.score > t2.score

![20200716154512710.png][]

**2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩**

SELECT
    	sc.student_id,
    	s.student_name,
    	avg( sc.score ) 
    FROM
    	score sc
    	INNER JOIN student s 
    WHERE
    	sc.student_id = s.id 
    GROUP BY
    	student_id 
    HAVING
    	avg( sc.score )> 60

![20200716163330157.png][]

**3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩**

SELECT
    	s.id,
    	s.student_name,
    	count( sc.course_id ) 选课总数,
    	SUM( sc.score ) 总成绩 
    FROM
    	student s,
    	score sc 
    WHERE
    	s.id = sc.student_id 
    GROUP BY
    	sc.student_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70][]

**4、查询"李"姓老师的数量 **

SELECT
    	count(*) 
    FROM
    	teacher 
    WHERE
    	teacher_name LIKE '李%'

![20200716170844711.png][]

**5、 查询学过"张三"老师授课的同学的信息**

SELECT
    	s.*
    FROM
    teacher t
    INNER JOIN course c
    	ON t.id=c.teacher_id
    	INNER JOIN score sc on
    	sc.course_id=c.id
    	INNER JOIN student s
    	ON s.id=sc.student_id
    	
    WHERE
    	t.teacher_name="张三"

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 1][]

**6、查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息 **

SELECT
    	* 
    FROM
    	student 
    WHERE
    	id IN ( SELECT student_id FROM `score` WHERE course_id = 1 ) 
    	AND id IN (
    	SELECT
    		student_id 
    	FROM
    		`score` 
    WHERE
    	course_id = 2)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 2][]

**7、查询没有学全所有课程的同学的信息 **

SELECT * FROM student WHERE id IN ( 
    	SELECT student_id 
    	FROM score 
    	GROUP BY student_id 
    	HAVING COUNT( course_id )<( SELECT COUNT(*) FROM course ) 
    )

![20200717100008535.png][]

**8、查询至少有一门课与学号为"1"的同学所学相同的同学的信息 **

SELECT
    	DISTINCT s.* 
    FROM
    	score sc,
    	student s 
    WHERE
    	sc.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) 
    	AND student_id != 1 
    	AND s.id = sc.student_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 3][]

** 9、查询和"1"号的同学学习的课程完全相同的其他同学的信息**

**把其他学生选课信息拼成字符串**

SELECT
    		sc.student_id ,	GROUP_CONCAT( course_id ORDER BY course_id ASC )
    	FROM
    		score sc 
    	GROUP BY
    		student_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 4][]

**和****把学生1选课信息拼成字符串进行比较，得到结果。**

SELECT
    	* 
    FROM
    	student 
    WHERE
    	id IN (
    	SELECT
    		sc.student_id 
    	FROM
    		score sc 
    	WHERE
    		student_id != 1 
    	GROUP BY
    		student_id 
    	HAVING
    		GROUP_CONCAT( course_id ORDER BY course_id ASC )=(
    		SELECT
    			GROUP_CONCAT( course_id ORDER BY course_id ASC ) 
    		FROM
    			score sc 
    		WHERE
    		student_id = 1 
    	))

![20200717160257986.png][]

**10、查询没学过"张三"老师讲授的任一门课程的学生姓名**

SELECT
    	* 
    FROM
    	student 
    WHERE
    	id NOT IN (
    	SELECT DISTINCT
    		sc.student_id 
    	FROM
    		teacher t
    		INNER JOIN course c ON t.id = c.teacher_id
    		INNER JOIN score sc ON c.id = sc.course_id 
    	WHERE
    	t.teacher_name = '张三' 
    	)

![20200720150405889.png][]

** 11、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩**

SELECT
    	s.id,
    	s.student_name,
    	t.avgScore 
    FROM
    	student s,
    	( SELECT COUNT( course_id ), student_id, AVG( score ) avgScore 
            FROM score sc WHERE sc.score < 60 GROUP BY student_id ) t 
    WHERE
    	s.id = t.student_id

![20200720152408333.png][]

**12、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 **

SELECT
    	sc.student_id,
    	SUM( CASE sc.course_id WHEN 1 THEN sc.score ELSE 0 END ) 语文,
    	SUM( CASE sc.course_id WHEN 2 THEN sc.score ELSE 0 END ) 数学,
    	SUM( CASE sc.course_id WHEN 3 THEN sc.score ELSE 0 END ) 英语,
    	AVG( sc.score ) 平均成绩 
    FROM
    	score sc 
    GROUP BY
    	sc.student_id 
    ORDER BY
    	平均成绩 DESC

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 5][]

**13、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率**

及格为>=60，

中等为：70-80，

优良为：80-90，

优秀为：>=90

先把学生每科成绩进行划分等级

SELECT
    	sc.*,
    	( CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END ) pass,
    	( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END ) mid,
    	( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END ) good,
    	( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END ) exellent 
    FROM
    	score sc 
    ORDER BY
    	sc.student_id,course_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 6][]

按课目求出学生的最高最低分

SELECT
    	sc.course_id,
    	c.course_name,
    	MAX( sc.score ) 最高分,
    	MIN( sc.score ) 最低分 
    FROM
    	score sc
    	INNER JOIN course c ON sc.course_id = c.id 
    GROUP BY
    	sc.course_id

![20200720162321335.png][]

合起来

5.7及以上由于sql\_mode默认开启ONLY\_FULLY\_GROUP\_BY会报错

1055 - Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.sc.course\_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

SELECT
    	sc.course_id,
    	c.course_name,
    	MAX( sc.score ) 最高分,
    	MIN( sc.score ) 最低分,
    	sum( t.pass )* 100 / count( sc.student_id ) 及格率,
    	sum( t.mid )* 100 / count( sc.student_id ) 中等率,
    	sum( t.good )* 100 / count( sc.student_id ) 优良率,
    	sum( t.exellent )* 100 / count( sc.student_id ) 优秀率 
    FROM
    	score sc
    	INNER JOIN course c ON sc.course_id = c.id
    	INNER JOIN (
    	SELECT
    		sc.course_id,
    		sc.id,
    		( CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END ) pass,
    		( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END ) mid,
    		( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END ) good,
    		( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END ) exellent 
    	FROM
    		score sc 
    	ORDER BY
    		sc.student_id,
    		course_id 
    	) t ON t.id = sc.id 
    GROUP BY
    	t.course_id

如果开启了ONLY\_FULLY\_GROUP\_BY 将id相等转换成 student\_id 和course\_id相等

SELECT
    	sc.course_id,
    	c.course_name,
    	MAX( sc.score ) 最高分,
    	MIN( sc.score ) 最低分,
    	sum( t.pass )* 100 / count( sc.student_id ) 及格率,
    	sum( t.mid )* 100 / count( sc.student_id ) 中等率,
    	sum( t.good )* 100 / count( sc.student_id ) 优良率,
    	sum( t.exellent )* 100 / count( sc.student_id ) 优秀率 
    FROM
    	score sc
    	INNER JOIN course c ON sc.course_id = c.id
    	INNER JOIN (
    	SELECT
    		sc.course_id,
    		sc.id,sc.student_id,
    		( CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END ) pass,
    		( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END ) mid,
    		( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END ) good,
    		( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END ) exellent 
    	FROM
    		score sc 
    	ORDER BY
    		sc.student_id,
    		course_id 
    	) t ON t.course_id = sc.course_id AND t.student_id=sc.student_id
    	
    GROUP BY
    	t.course_id

![20200720163806558.png][]

**14、查询学生的科目1的成绩并进行排名**

SELECT
    	student_id,
    	score,(
    		@rowNum := @rowNum + 1 
    	) rowNum,
    	@rowinc :=
    CASE
    		
    		WHEN @rowTotal = score THEN
    		@rowinc 
    		WHEN @rowTotal := score THEN
    		@rowNum 
    	END AS rank 
    FROM
    	score,(
    	SELECT
    		@rowNum := 0,
    		@rowTotal := NULL,
    		@rowinc := 0 
    	) t 
    WHERE
    	course_id =1 
    ORDER BY
    	score DESC

SELECT
    	sc.score,(
    	SELECT
    		COUNT( s.score )+ 1 
    	FROM
    		score s 
    	WHERE
    		s.score > sc.score 
    		AND s.course_id = 1 
    	) rank 
    FROM
    	score sc 
    WHERE
    	sc.course_id = 1 
    ORDER BY
    	sc.score DESC;

**15、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩**

**16、查询各科成绩前三名的记录**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 7][]

[20200716154512710.png]: /images/20230209/e56e60e3f9054fe392f61798bfa71abd.png
[20200716163330157.png]: /images/20230209/1d09e379a47d4b328b6d950623d95255.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70]: /images/20230209/e9a2c24583984fff96e816e707f807ad.png
[20200716170844711.png]: /images/20230209/99134811fc7349c7a68f929960cc4693.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 1]: /images/20230209/bc64c8ac70c1451994acc0bb71649165.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 2]: /images/20230209/49170f1b50ca4e82b30d176e8ac0dbbd.png
[20200717100008535.png]: /images/20230209/b89347b770da40698a9ab6854e787563.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 3]: /images/20230209/fe9a31f387164c7f81f249af81debe50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 4]: /images/20230209/8e5ec26dcdaa463e9561171bee8cf6f2.png
[20200717160257986.png]: /images/20230209/b4d6883af6d2439d93a70fa4e0bda6c6.png
[20200720150405889.png]: /images/20230209/8d06afcdeb4946f2b1e9c13055ed06a8.png
[20200720152408333.png]: /images/20230209/1bc276a1d871483bb62465569553338d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 5]: /images/20230209/ef92e04879e548d692500cf0929753b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 6]: /images/20230209/9455e60bbbdc454c854346dcd6a7faab.png
[20200720162321335.png]: /images/20230209/8beb3d698ff246759b2c51315af12812.png
[20200720163806558.png]: /images/20230209/e1e57cc1932945ae96aac6f2ffa40196.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 7]: /images/20230209/53fd10d94dfb449f8ebe9c315768ccfe.png