mysql小题

1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> select name,age from students where age>25;
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.000 sec)

2. 以ClassID为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select classid,avg(age) from students group by classid ;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+

3. 显示第2题中平均年龄大于30的分组及平均年龄

MariaDB [hellodb]> select classid,avg(age) vage from students group by classid having vage>30;
+---------+---------+
| classid | vage    |
+---------+---------+
|    NULL | 63.5000 |
|       2 | 36.0000 |
|       5 | 46.0000 |
+---------+---------+

4. 显示以L开头的名字的同学的信息

MariaDB [hellodb]> select name from students where name like  ‘l%‘;
+-------------+
| name        |
+-------------+
| Lin Daiyu   |
| Lu Wushuang |
| Lin Chong   |
+-------------+
3 rows in set (0.000 sec)

5. 显示TeacherID非空的同学的相关信息

MariaDB [hellodb]> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.000 sec)

6. 以年龄排序后，显示年龄最大的前10位同学的信息

MariaDB [hellodb]> select * from students  order by age limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

7. 查询年龄大于等于20岁，小于等于25岁的同学的信息

MariaDB [hellodb]> select * from students where age>=20 and age<=25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

8. 以ClassID分组，显示每班的同学的人数

MariaDB [hellodb]> select classid,count(classid) 人数 from students  group by classid ;
+---------+--------+
| classid | 人数   |
+---------+--------+
|    NULL |      0 |
|       1 |      4 |
|       2 |      3 |
|       3 |      4 |
|       4 |      4 |
|       5 |      1 |
|       6 |      4 |
|       7 |      3 |
+---------+--------+
8 rows in set (0.000 sec)

9. 以Gender分组，显示其年龄之和

MariaDB [hellodb]> select sum(age),gender from students group by gender ;
+----------+--------+
| sum(age) | gender |
+----------+--------+
|      190 | F      |
|      495 | M      |
+----------+--------+
2 rows in set (0.000 sec)

10. 以ClassID分组，显示其平均年龄大于25的班级

MariaDB [hellodb]> select classid,avg(age) 平均年纪 from students group by classid having 平均年纪>25;
+---------+--------------+
| classid | 平均年纪     |
+---------+--------------+
|    NULL |      63.5000 |
|       2 |      36.0000 |
|       5 |      46.0000 |
+---------+--------------+
3 rows in set (0.001 sec)

11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和

MariaDB [hellodb]> select sum(age),gender from (select age,gender from students where age > 25) as s;
+----------+--------+
| sum(age) | gender |
+----------+--------+
|      317 | M      |
+----------+--------+
1 row in set (0.000 sec)

12. 显示前5位同学的姓名、课程及成绩

MariaDB [hellodb]> select st.stuid,st.name,co.course,sc.score from students st inner join  scores sc on  st.stuid=sc.stuid inner join courses co on sc.courseid=sc.courseid  order by sc.score desc limit 5;

13. 显示其成绩高于80的同学的名称及课程

MariaDB [hellodb]> select st.stuid,st.name,co.course,sc.score from students st inner join  scores sc on  st.stuid=sc.stuid inner join courses co on sc.courseid=sc.courseid and  sc.score>80;
+-------+-------------+----------------+-------+
| stuid | name        | course         | score |
+-------+-------------+----------------+-------+
|     1 | Shi Zhongyu | Hamo Gong      |    93 |
|     1 | Shi Zhongyu | Kuihua Baodian |    93 |
|     1 | Shi Zhongyu | Jinshe Jianfa  |    93 |
|     1 | Shi Zhongyu | Taiji Quan     |    93 |
|     1 | Shi Zhongyu | Daiyu Zanghua  |    93 |
|     1 | Shi Zhongyu | Weituo Zhang   |    93 |
|     1 | Shi Zhongyu | Dagou Bangfa   |    93 |
|     2 | Shi Potian  | Hamo Gong      |    97 |
|     2 | Shi Potian  | Kuihua Baodian |    97 |
|     2 | Shi Potian  | Jinshe Jianfa  |    97 |
|     2 | Shi Potian  | Taiji Quan     |    97 |
|     2 | Shi Potian  | Daiyu Zanghua  |    97 |
|     2 | Shi Potian  | Weituo Zhang   |    97 |

14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

MariaDB [hellodb]> select st.stuid,st.name,co.course,avg(sc.score) from students st inner join  scores sc on  st.stuid=sc.stuid inner join courses co on sc.courseid=sc.courseid group by st.name order by sc.score desc limit 3;
+-------+-----------+-----------+---------------+
| stuid | name      | course    | avg(sc.score) |
+-------+-----------+-----------+---------------+
|     6 | Shi Qing  | Hamo Gong |       96.0000 |
|     3 | Xie Yanke | Hamo Gong |       81.5000 |
|     7 | Xi Ren    | Hamo Gong |       84.5000 |
+-------+-----------+-----------+---------------+

15. 显示每门课程课程名称及学习了这门课的同学的个数

MariaDB [hellodb]> select st.stuid,count(co.course) 报名人数,co.course 课程 from students st inner join  scores sc on  st.stuid=sc.stuid inner join courses co on sc.courseid=sc.courseid group by co.course;
+-------+--------------+----------------+
| stuid | 报名人数     | 课程           |
+-------+--------------+----------------+
|     1 |           15 | Dagou Bangfa   |
|     1 |           15 | Daiyu Zanghua  |
|     1 |           15 | Hamo Gong      |
|     1 |           15 | Jinshe Jianfa  |
|     1 |           15 | Kuihua Baodian |
|     1 |           15 | Taiji Quan     |
|     1 |           15 | Weituo Zhang   |

16. 显示其年龄大于平均年龄的同学的名字

MariaDB [hellodb]> select age,name from students where age > ( select avg(age) from students);
+-----+--------------+
| age | name         |
+-----+--------------+
|  53 | Xie Yanke    |
|  32 | Ding Dian    |
|  46 | Shi Qing     |
|  33 | Tian Boguang |
| 100 | Sun Dasheng  |
+-----+--------------+

17. 显示其学习的课程为第1、2，4或第7门课的同学的名字

MariaDB [hellodb]>  select st.stuid,st.name,co.course,co.courseid from students st inner join  scores sc on  st.stuid=sc.stuid inner join courses co on sc.courseid=sc.courseid and co.courseid in (1,2,4,7);
+-------+-------------+----------------+----------+
| stuid | name        | course         | courseid |
+-------+-------------+----------------+----------+
|     1 | Shi Zhongyu | Hamo Gong      |        1 |
|     1 | Shi Zhongyu | Kuihua Baodian |        2 |
|     1 | Shi Zhongyu | Taiji Quan     |        4 |
|     1 | Shi Zhongyu | Dagou Bangfa   |        7 |
|     1 | Shi Zhongyu | Hamo Gong      |        1 |
|     1 | Shi Zhongyu | Kuihua Baodian |        2 |
|     1 | Shi Zhongyu | Taiji Quan     |        4 |
|     1 | Shi Zhongyu | Dagou Bangfa   |        7 |
|     2 | Shi Potian  | Hamo Gong      |        1 |

18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

#写不来 算是抄的
MariaDB [hellodb]> select age,name,g from students s1 inner join (select count(classid) cl,classid,avg(age) g from students group by classid having cl>=3) as s2  on s1.classid=s2.classid and s1.age>g;  
+-----+---------------+---------+
| age | name          | g       |
+-----+---------------+---------+
|  22 | Shi Potian    | 20.5000 |
|  53 | Xie Yanke     | 36.0000 |
|  32 | Ding Dian     | 24.7500 |
|  26 | Yu Yutong     | 20.2500 |
|  23 | Yuan Chengzhi | 20.7500 |
|  21 | Xu Zhu        | 20.5000 |
|  25 | Lin Chong     | 24.7500 |
|  23 | Hua Rong      | 19.6667 |
|  22 | Huang Yueying | 20.7500 |
+-----+---------------+---------+

19. 统计各班级中年龄大于全校同学平均年龄的同学

MariaDB [hellodb]> select name,age from students where age>(select avg(age) from students); 
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |

mysql小题

原文：https://www.cnblogs.com/catastrophe/p/14756820.html