mysql查询分数从大到小排序_MYSQL 命令练习

A.查询姓'王'的学生的个数；

答：select count(*)from Student where name like '王%';

B.查询“数学”比“语文”成绩高的所有学生的学号；

答:

c.查询平均分大于90的学生的学号和平均成绩；

答:select a.id,avg(c.score) from student a join sc c on a.id =c.sid where avg(c.score) >90;

1.请写出计算所有学生的英语平均成绩的语句;

SELECT AVG(english) FROM mark;

2.现有五个学生，其学号假定为11,22,33,44,55；请用一条sql语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码；

SELECT

cust.Studentno,

cust. Name

cust.Address,

cust.Telno,

mark.math

FROM

cust

JOIN mark ON cust.Studentno = mark.studentno

WHERE

cust.Studentno IN (11, 22, 33, 44, 55);

3.查询所有学生的姓名、计算机成绩、按照计算机成绩从高到低排序；

SELECT

cust. NAME,

mark.computer

FROM

cust

JOIN mark ON cust.studentno = mark.studentno

ORDER BY

mark.computer DESC;

4.查询所有总成绩大于240分学生的学号、姓名、总成绩，按照总成绩由高到低排序；

SELECT

cust.studentno,

cust. NAME,

SUM(

mark.english + mark.computer + mark.math

)

FROM

cust

JOIN mark ON cust.studentno = mark.studentno

GROUP BY

cust.studentno

HAVING

SUM(

mark.english + mark.computer + mark.math

) > 240;

ORDER BY

SUM(

mark.english + mark.computer + mark.math

);