面试-Sql专题-1-必会的10个sql

1. 不管是JavaEE工程师还是大数据工程师，都需要良好的SQL功底。
2. 良好的SQL功底也成了JavaEE工程师和大数据工程师求职必备技能。
3. 收集整理网上一些经典的Sql以及一些Sql笔试真题，写成几篇文章供大家参考。
4. 基于Mysql。

创建数据库

CREATE DATABASE mianshi_1 CHARACTER SET utf8

创建表

CREATE TABLE `stuscore` (
  `name` varchar(50) DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `score` int DEFAULT NULL,
  `stuid` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

insert into stuscore values ('张三','数学',89,1); 
insert into stuscore values ('张三','语文',80,1); 
insert into stuscore values ('张三','英语',70,1); 
insert into stuscore values ('李四','数学',90,2); 
insert into stuscore values ('李四','语文',70,2); 
insert into stuscore values ('李四','英语',80,2); 
insert into stuscore values ('王五','数学',77,3);
insert into stuscore values ('王五','语文',88,3); 
insert into stuscore values ('王五','英语',99,3); 

效果

1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

sql

SELECT
	NAME,
	SUM(score) AS allscore
FROM
	stuscore
GROUP BY
	NAME
ORDER BY
	allscore;

效果

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩

sql

SELECT
	stuid,
	NAME,
	SUM(score) AS allscore
FROM
	stuscore
GROUP BY
	NAME,
	stuid
ORDER BY
	allscore;

效果

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

sql

SELECT
	t1.stuid,
	t1. NAME,
	t1. SUBJECT,
	t1.score
FROM
	stuscore t1,
	(
		SELECT
			stuid,
			max(score) AS maxscore
		FROM
			stuscore
		GROUP BY
			stuid
	) t2
WHERE
	t1.stuid = t2.stuid
AND t1.score = t2.maxscore;

效果

4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

sql

SELECT
	stuid,
	NAME,
	AVG(score) avgscore
FROM
	stuscore
GROUP BY
	stuid;

效果

5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

sql

SELECT
	t1.stuid,
	t1. NAME,
	t1. SUBJECT,
	t1.score
FROM
	stuscore t1,
	(
		SELECT
			max(score) maxsore,
			SUBJECT
		FROM
			stuscore
		GROUP BY
			SUBJECT
	) t2
WHERE
	t1. SUBJECT = t2. SUBJECT
AND t1.score = t2.maxsore;

效果

6.列出各门课程成绩,最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

这个是典型的topN问题
mysql没有此top n语法,mysql用limit来实现相关功能

sql

(select stuid,name,subject,score from stuscore where subject='语文' order by score desc limit 2)
UNION ALL
(select stuid,name,subject,score from stuscore where subject='数学' order by score desc limit 2)
UNION ALL
(select stuid,name,subject,score from stuscore where subject='英语' order by score desc limit 2)

效果

7．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

sql

SELECT
	t1.* ,(
		SELECT
			COUNT(1)
		FROM
			stuscore t2
		WHERE
			subject='数学' and t2.score >= t1.score
	) rank
FROM
	stuscore t1
where subject='数学'
ORDER BY
	rank

效果

8.列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

###sql

select stuid,name,subject,score from stuscore where subject='数学' order by score desc limit 1,2

###效果

9. 查询出如下效果

###效果

###sql

select stuid 学号,name 姓名,
sum(case when subject='语文' then score else 0 end )as 语文,
sum(case when subject='数学' then score else 0 end )as 数学,
sum(case when subject='英语' then score else 0 end )as 英语,
SUM(score)总分,avg(score)平均分 from stuscore
group by stuid,name order by 总分;

10. 查询出如下效果

课程 不及格（0-59）个 良（60-80）个 优（81-100）个
###效果

###sql

 select subject 科目,
 sum(case when score between 0 and 59 then 1 else 0 end) as 不及格,
 sum(case when score between 60 and 80 then 1 else 0 end) as 良,
 sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 
 from stuscore
 group by subject;