MySQL常见笔试题类型及解法总结

桃扇骨 2022-02-24 01:14 222阅读 0赞

> 最近看了一下数据库方面的面试题，发现有些情况不常用的都忘了怎么解决了，所以在此做一个总结，方便之后复习。

先建一个这样的数据库表student\_score表，结构及数据如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ExNDA2Njg5NDIz_size_16_color_FFFFFF_t_70]  
创建表及插入数据的SQL脚本如下：

CREATE TABLE student_score (
      `pk_id` varchar(36) NOT NULL,
      `student_id` int(15) NOT NULL,
      `student_name` varchar(24) NOT NULL,
      `category` varchar(10) NOT NULL,
      `score` int(10) DEFAULT NULL,
      PRIMARY KEY (`pk_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO student_score 
    (pk_id,student_id,student_name,category,score)
    VALUES
    ('aabbcc','10011','张三','数学','56'),
    ('aabbcd','10011','张三','语文','88'),
    ('aabbdc','10011','张三','英语','67'),
    ('aabdcc','10012','李四','语文','67'),
    ('aadbcc','10012','李四','数学','88'),
    ('adbbcc','10012','李四','英语','30'),
    ('aabscc','10013','王五','数学','66'),
    ('afbbcc','10013','王五','英语','76'),
    ('vabbcc','10013','王五','语文','88'),
    ('aasdcc','10014','赵七','数学','88'),
    ('agbbcc','10014','赵七','英语','96'),
    ('agbbsa','10014','赵七','英语','96'),
    ('aubbcc','10014','赵七','语文','80'),
    ('adcdsd','10015','马六','数学','33'),
    ('dfasdv','10015','马六','英语','45'),
    ('dsdfgd','10015','马六','语文','55'),
    ('dsdfad','10015','马六','语文','55');

有了以上数据，就可以进行操作了，以下是一些常见题型，包含了基本操作和一些相对较复杂的操作：

#查询所有数据
    SELECT
    	*
    FROM
    	student_score;
    
    #查询学生总数,不加临时表名会报错
    SELECT
    	count(1)
    FROM
    	(
    		SELECT DISTINCT
    			student_name
    		FROM
    			student_score
    	) t1;
    
    #查询姓名为张三的成绩
    SELECT
    	student_id,
    	student_name,
    	score
    FROM
    	student_score
    WHERE
    	student_name = '张三';
    
    #查询所有学生的数学成绩
    SELECT
    	student_id,
    	student_name,
    	score
    FROM
    	student_score
    WHERE
    	category = '数学';
    
    #查询张三的数学成绩
    SELECT
    	student_id,
    	student_name,
    	score
    FROM
    	student_score
    WHERE
    	student_name = '张三'
    AND category = '数学';
    
    #查询所有成绩存在不及格的学生的学号和姓名
    SELECT DISTINCT
    	student_id,
    	student_name
    FROM
    	student_score
    WHERE
    	score < 60;
    
    #查询所有成绩都及格的学生的学号和姓名
    SELECT DISTINCT
    	student_id,
    	student_name
    FROM
    	student_score
    WHERE
    	student_name NOT IN (
    		SELECT
    			student_name
    		FROM
    			student_score
    		WHERE
    			score < 60
    		GROUP BY
    			student_name
    	);
    
    #查询成绩至少两门及格的学生的学号和姓名
    SELECT DISTINCT
    	student_id,
    	student_name
    FROM
    	student_score
    WHERE
    	student_name NOT IN (
    		SELECT
    			student_name
    		FROM
    			student_score
    		WHERE
    			score < 60
    		GROUP BY
    			student_name
    		HAVING
    			count(category) > 1
    	);
    	
    #或者：
    
    SELECT
    	student_id,
    	student_name
    FROM
    	student_score
    WHERE
    	score >= 60
    GROUP BY
    	student_name
    HAVING
    	count(category) >= 2;
    
    #查询成绩至少两门及格的学生的学号和姓名，以及显示出及格的科目
    SELECT
    	student_id,
    	student_name,
    	GROUP_CONCAT(category)
    FROM
    	student_score
    WHERE
    	score >= 60
    GROUP BY
    	student_name
    HAVING
    	count(category) >= 2;
    
    #查找每门课都在80以上的学生的学号和姓名
    SELECT DISTINCT
    	student_id,
    	student_name
    FROM
    	student_score
    WHERE
    	student_name NOT IN (
    		SELECT DISTINCT
    			student_name
    		FROM
    			student_score
    		WHERE
    			score < 80
    	);
    
    #删除除pk_id外其他其他数据都相同的冗余数据
    DELETE
    FROM
    	student_score
    WHERE
    	pk_id NOT IN (
    		SELECT
    			*
    		FROM
    			(
    				SELECT
    					min(pk_id)
    				FROM
    					student_score
    				GROUP BY
    					student_id,
    					student_name,
    					score,
    					category
    			) t1
    	) 
    #找到分数大于80的最多的科目及人数
    SELECT
    	category,
    	count
    FROM
    	(
    		SELECT
    			category,
    			COUNT(category) AS count
    		FROM
    			(
    				SELECT
    					category
    				FROM
    					student_score
    				WHERE
    					score >= 80
    			) t1
    		GROUP BY
    			category
    	) t2
    WHERE
    	count = (
    		SELECT
    			max(count)
    		FROM
    			(
    				SELECT
    					category,
    					COUNT(category) AS count
    				FROM
    					(
    						SELECT
    							category
    						FROM
    							student_score
    						WHERE
    							score >= 80
    					) t1
    				GROUP BY
    					category
    			) t2
    	)

目前想到的就是这些，以后再遇到别的类型再添加，如果**有读者看到有想补充的可以评论**，我后期补上。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ExNDA2Njg5NDIz_size_16_color_FFFFFF_t_70]: /images/20220224/e38c9388925f4ac5b1f6d03c1d2fa243.png