简单数据库设计

数据库设计

学生表：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 ("语文","数学","英语")
)

我们也可以使用下面方法得到同样结果

SELECT
    NAME,
    '语文' AS subject ,
    MAX("语文") AS score
FROM student1 GROUP BY NAME
UNION
SELECT
    NAME,
    '数学' AS subject ,
    MAX("数学") AS score
FROM student1 GROUP BY NAME
UNION
SELECT
    NAME,
    '英语' 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返回优秀

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：

CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

更多查询链接