简单数据库设计
数据库设计
学生表：student 字段：
studentnum(学号)，studentname(学生姓名)，classname(班级名称)，sex（男：1，女：2），birthday（出生日期）
问题1：查出student表中各个班级的人数，并按人数从多到少排列
答案：
SELECT classname,COUNT(classname) FROM student GROUP BY classname ORDER BY COUNT(classname) DESC
注意：GROUP BY分组，ORDER BY…DESC（对某某字段进行降序排列，也就是从多到少显示，ASC是升序）
问题2：统计打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
sum(case when score <60 then 1 else 0 end) AS "[<60]"
from sc,course c
where sc.cno=c.cno
group by sc.cno ,c.cname
问题3：查找重复姓名的sql语句
方式一：
select * from 学生表 where 姓名 in(select 姓名 from 学生表 group by 姓名 having count(姓名）>=2)
分析：from 学生表 ：找到要查询的表名， where 姓名 in:过滤条件让姓名符合小括号里面内容 group by 姓名 ：按照姓名来分组，也就是说姓名相同的会放在同一组里面，其他字段可能包括多条信息，having count(姓名）>=2：过滤分组内容中姓名达到两个以及以上的信息）
方式二：
select 姓名,count(姓名) from 学生表 group by 姓名 having count(姓名)>=2
分析：select 姓名 from 学生表 group by 姓名 having count(姓名)>=2即可，count(姓名）是自己又在返回的视图看到了另一个字段，这个字段用来显示出现的重复姓名的次数。
注意：因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。
HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。 having是分组（group by）后的筛选条件，分组后的数据组内再筛选，where则是在分组前筛选
问题4：查询各科成绩前三名的记录:(不考虑成绩并列情况)
select*from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4
问题5：SQL 行转列，列转行
行列转换在做报表分析时还是经常会遇到的，如何实现行列转换
行转列–要把数据变成行，一目了然的看到一个学生的所有科目成绩，SQL如下：
SELECT *
FROM student
PIVOT (SUM(score) FOR subject IN (语文, 数学, 英语)
)
注：PIVOT 后跟一个聚合函数来拿到结果，FOR 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。
当然我们也可以用 CASE WHEN 得到同样的结果，就是写起来麻烦一点。
SELECT Name,
MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
FROM StudentScores
GROUP BY Name
使用 CASE WHEN 可以得到和 PIVOT 同样的结果，没有 PIVOT 简单直观。
这里解释一下SQL，查询的时候用case when then选择需要进行转行的字段以及字段结果，即当Subject是xx的时候选择Subject对应的Score作为Subject的成绩，这里需要注意case when then的结果要用max函数包裹，不然结果也会变成行，但是每行只有一科的成绩，用max包裹就是选择最大成绩，把多行合并成一行完成行转列。
列转行–通过 UNPIVOT 即可得到如下结果:
SELECT *
FROM student1
UNPIVOT (score FOR subject IN ("语文","数学","英语")
)
我们也可以使用下面方法得到同样结果
SELECTNAME,'语文' AS subject ,MAX("语文") AS score
FROM student1 GROUP BY NAME
UNION
SELECTNAME,'数学' AS subject ,MAX("数学") AS score
FROM student1 GROUP BY NAME
UNION
SELECTNAME,'英语' AS subject ,MAX("英语") AS score
FROM student1 GROUP BY NAME
问题6：SQL数据库 查询平均分在90分以上的学生的成绩信息，并且按成绩降序排序。
select *
from SC
where S in(select S from SC group by S having avg(score)>90)
order by score desc
问题7：查出student表中各班年龄最小的女生班级号，学号，姓名和出生日期，并按班级号升序排列
答案：
SELECT classname,studentnum,studentname,birthday
FROM student
WHERE age in(SELECT MIN(age) FROM student WHERE sex='2' GROUP BY classname )
ORDER BY classname ASC
注意点：以上标红已说明，大概思路就是：既然要查找年龄最小的，则需要用到min函数。
各班，则需要用到分组gruop by 班级。where age in 的意思是我要查找的年龄条件是什么，在年龄条件符合的条件下，性别必须是女生，所以要加in里面加个where 条件，最后则是按照题目的意思，按班级号升序，就用到了order by 班级号 ASC。
问题8：想统计出各班的男生和女生分别 多少人
答案：
SELECT classname AS '班级',SUM(CASE WHEN sex='1' THEN sex ELSE 0 END )
AS '男生',SUM(CASE WHEN sex='2' THEN sex ELSE 0 END ) AS '女生'
FROM student
GROUP BY classname
注：如果case函数中，把then后面的sex改成数字1同样也能得到正确答案，但是如果改为2之后，得到的结果则会计算有误。
简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。
CASE WHEN条件表达式函数：类似JAVA中的IF ELSE语句。
格式：
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。
常用场景：
有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀
SELECTSTUDENT_NAME,(CASE WHEN score < 60 THEN '不及格'WHEN score >= 60 AND score < 80 THEN '及格'WHEN score >= 80 THEN '优秀'ELSE '异常' END) AS REMARK
FROMTABLE
注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：
CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END
更多查询链接
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！