本文所用的表信息

学生表stu

字段名

数据类型

约束条件

s_id

varchar(10)

primary key

s_name

varchar(10)

not null

s_birth

date


s_sex

varchar(10)


课程表co

字段名

数据类型

约束条件

c_id

varchar(10)

primary key

c_name

varchar(10)


t_id

varchar(10)


教师表te

字段名

数据类型

约束条件

t_id

varchar(10)

primary key

t_name

varchar(10)


成绩表sc

字段名

数据类型

约束条件

s_id

varchar(10)


c_id

varchar(10)


score

int


面试题

查询所有学生的课程及分数情况

分析,课程与分数情况,用到学生表及成绩表,两张表都是一维表,将其转化为右图所示的二维表。



mysql 存储 二维数组_mysql 存储 二维数组

第一步,将两张表格连接为一张表格

SELECT stu.*,c_id,score
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id;

第二步,将上面得到的结果转换为二维表



mysql 存储 二维数组_less_02

语句

SELECT stu.s_id,stu.s_name,
		SUM(if(c_id='01',score,0)) '01',
		SUM(if(c_id='02',score,0)) '02',
        SUM(if(c_id='03',score,0)) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

另外两种写法

SELECT stu.s_id,stu.s_name,
		SUM(CASE WHEN c_id='01' THEN score ELSE 0 END) '01',
		SUM(CASE WHEN c_id='02' THEN score ELSE 0 END) '02',
        SUM(CASE WHEN c_id='03' THEN score ELSE 0 END) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,
		SUM((c_id='01')*score) '01',
        SUM((c_id='02')*score) '02',
        SUM((c_id='03')*score) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

练习

1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析,"有学生的所有课程的成绩",需要用到学生表、成绩表。"平均成绩"需要按学生聚合GROUP BY并求平均值AVG。"按平均成绩从高到低显示"需要用ORDER BY排序。

SELECT stu.s_id,
		SUM((c_id='01')*score) '语文',
        SUM((c_id='02')*score) '数学',
        SUM((c_id='03')*score) '英语',
		AVG(score) '平均分'
FROM  stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id
ORDER BY AVG(score) DESC;

结果

s_id

语文

数学

英语

平均分

07

0

89

98

93.5000

01

80

90

99

89.6667

05

76

87

0

81.5000

03

80

80

80

80.0000

02

70

60

80

70.0000

04

50

30

20

33.3333

06

31

0

34

32.5000

08





2、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率,及格为:>=60,中等为:70-80,优良为:80-90,优秀为:>=90

分析,从需要显示的列看出需要用到课程表和成绩表。各科成绩最高分、最低分和平均分,需要按照课程聚合并求平均分。

SELECT sc.c_id,c_name,max(score) 最高分,min(score) 最低分,AVG(score) 平均分,
		SUM(score>=60 and score<70)/(SELECT COUNT(s_id) FROM stu) 及格率,
        SUM(score>=70 and score<80)/(SELECT COUNT(s_id) FROM stu) 中等率,
        SUM(score>=80 and score<90)/(SELECT COUNT(s_id) FROM stu) 优良率,
        SUM(score>=90)/(SELECT COUNT(s_id) FROM stu) 优秀率
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;

结果

c_id

c_name

最高分

最低分

平均分

及格率

中等率

优良率

优秀率

01

语文

80

31

64.5000

0.0000

0.2500

0.2500

0.0000

02

数学

90

30

72.6667

0.1250

0.0000

0.3750

0.1250

03

英语

99

20

68.5000

0.0000

0.0000

0.2500

0.2500

3、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT sc.c_id, c_name,
       SUM(score BETWEEN 0 AND 60)/COUNT(sc.c_id) AS '[0-60]所占百分比',
       SUM(score BETWEEN 60 AND 70)/COUNT(sc.c_id) AS '[60-70]所占百分比',
       SUM(score BETWEEN 70 AND 85)/COUNT(sc.c_id) AS '[70-85]所占百分比',
       SUM(score BETWEEN 85 AND 100)/COUNT(sc.c_id) AS '[85-100]所占百分比'
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;

结果

c_id

c_name

[0-60]所占百分比

[60-70]所占百分比

[70-85]所占百分比

[85-100]所占百分比

01

语文

0.3333

0.1667

0.6667

0.0000

02

数学

0.3333

0.1667

0.1667

0.5000

03

英语

0.3333

0.0000

0.3333

0.3333


-- 数据STUDIO --