前言:
而今姐妹们对“mysql练习题目”可能比较着重,你们都想要分析一些“mysql练习题目”的相关资讯。那么小编同时在网络上搜集了一些对于“mysql练习题目””的相关知识,希望同学们能喜欢,各位老铁们快快来学习一下吧!假设有一个名为“students”的表,包含以下字段:
- id:学生ID,整数类型
- name:学生姓名,字符串类型
- gender:学生性别,字符串类型
- birthday:学生生日,日期类型
- grade:学生年级,整数类型
- score:学生分数,浮点数类型
请完成以下练习题:
1. 查询每个年级的学生人数和平均分数,并按照平均分数从高到低排序。
```
SELECT grade, COUNT(id) AS count, AVG(score) AS avg_scoreFROM studentsGROUP BY gradeORDER BY avg_score DESC;
```
2. 查询男女学生人数,并按照人数从高到低排序。
```
SELECT gender, COUNT(id) AS countFROM studentsGROUP BY genderORDER BY count DESC;
```
3. 查询每个年级的男女学生人数和平均分数,并按照平均分数从高到低排序。
```
SELECT grade, gender, COUNT(id) AS count, AVG(score) AS avg_scoreFROM studentsGROUP BY grade, genderORDER BY grade ASC, avg_score DESC;
```
4. 查询每个年级的最高分数和最低分数。
```
SELECT grade, MAX(score) AS max_score, MIN(score) AS min_scoreFROM studentsGROUP BY grade;
```
5. 查询每个学生距离生日还有多少天,并按照距离生日从近到远排序。
```
SELECT name, DATEDIFF(DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday), 1, 0) YEAR), CURDATE()) AS days_leftFROM studentsORDER BY days_left ASC;
```
6. 查询每个性别的学生数量和平均分数,并按照平均分数从高到低排序。
```sql
SELECT gender, COUNT(id) AS num_of_students, AVG(score) AS avg_scoreFROM studentsGROUP BY genderORDER BY avg_score DESC;
```
7. 查询每个年龄段(以10年为一个段)的学生数量和平均分数,并按照年龄段从小到大排序。
```sql
SELECT FLOOR(DATEDIFF(CURRENT_DATE, birthday) / 365 / 10) * 10 AS age_range, COUNT(id) AS num_of_students, AVG(score) AS avg_scoreFROM studentsGROUP BY age_rangeORDER BY age_range ASC;
```
8. 查询每个月份的学生数量和平均分数,并按照月份从小到大排序。
```sql
SELECT MONTH(birthday) AS month, COUNT(id) AS num_of_students, AVG(score) AS avg_scoreFROM studentsGROUP BY monthORDER BY month ASC;
```
9. 查询离每个学生生日还有多少天,并按照剩余天数从小到大排序。
```sql
SELECT name, DATEDIFF(DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), '-', MONTH(birthday), '-', DAY(birthday)), '%Y-%m-%d'), CURRENT_DATE) AS days_leftFROM studentsORDER BY days_left ASC;
标签: #mysql练习题目 #mysql数据简单查询题目