龙空技术网

mysql-常见sql练习题2

bobo棒 97

前言:

而今姐妹们对“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数据简单查询题目