最近看了一下数据库方面的面试题,发现有些情况不常用的都忘了怎么解决了,所以在此做一个总结,方便之后复习。
先建一个这样的数据库表student_score表,结构及数据如下图所示:
创建表及插入数据的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
)
目前想到的就是这些,以后再遇到别的类型再添加,如果有读者看到有想补充的可以评论,我后期补上。