(1) 在students表中,查询年龄大于25岁,且为男性的同学的
名字和年龄
MariaDB [hellodb]> SELECT name,age FROM students WHERE age>25 AND Gender='M';
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
(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) FROM students GROUP BY ClassID HAVING avg(age)>30;
+---------+----------+
| ClassID | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE name LIKE 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
+-------+-------------+-----+--------+---------+-----------+
(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 |
+-------+-------------+-----+--------+---------+-----------+
(6) 以年龄排序后,显示年龄最大的前10位同学的信息
MariaDB [hellodb]> SELECT * FROM students ORDER BY age DESC LIMIT 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
(7)查询年龄大于等于20岁,小于等于25岁的同学的信息
MariaDB [hellodb]> SELECT * FROM students WHERE age BETWEEN 20 AND 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 |
+-------+---------------+-----+--------+---------+-----------+
(8)以ClassID分组,显示每班的同学的人数
MariaDB [hellodb]> SELECT ClassID,COUNT(name) FROM students GROUP BY ClassID;
+---------+-------------+
| ClassID | COUNT(name) |
+---------+-------------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+-------------+
(9)以Gender分组,显示其年龄之和
MariaDB [hellodb]> SELECT Gender,SUM(age) FROM students GROUP BY Gender;
+--------+----------+
| Gender | SUM(age) |
+--------+----------+
| F | 190 |
| M | 495 |
+--------+----------+
(10)以ClassID分组,显示其平均年龄大于25的班级
MariaDB [hellodb]> SELECT ClassID,AVG(age) FROM students GROUP BY ClassID HAVING AVG(age) >25;
+---------+----------+
| ClassID | AVG(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
(11)以Gender分组,显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> SELECT s.Gender,SUM(s.age) FROM (SELECT Gender,age FROM students WHERE age>20) AS s GROUP BY s.Gender;
+--------+------------+
| Gender | SUM(s.age) |
+--------+------------+
| F | 22 |
| M | 476 |
+--------+------------+
(12)显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> SELECT s.stuid,stu.name,s.course,s.score FROM (SELECT sco.stuid,cour.course,sco.score FROM scores as sco INNER JOIN courses as cour ON sco.CourseID=cour.CourseID) as s INNER JOIN students AS stu ON s.stuid=stu.stuid WHERE s.stuid<=5;
+-------+-------------+----------------+-------+
| stuid | name | course | score |
+-------+-------------+----------------+-------+
| 1 | Shi Zhongyu | Kuihua Baodian | 77 |
| 1 | Shi Zhongyu | Weituo Zhang | 93 |
| 2 | Shi Potian | Kuihua Baodian | 47 |
| 2 | Shi Potian | Daiyu Zanghua | 97 |
| 3 | Xie Yanke | Kuihua Baodian | 88 |
| 3 | Xie Yanke | Weituo Zhang | 75 |
| 4 | Ding Dian | Daiyu Zanghua | 71 |
| 4 | Ding Dian | Kuihua Baodian | 89 |
| 5 | Yu Yutong | Hamo Gong | 39 |
| 5 | Yu Yutong | Dagou Bangfa | 63 |
+-------+-------------+----------------+-------+
(13)显示其成绩高于80的同学的名称及课程;
MariaDB [hellodb]> SELECT * FROM (SELECT s.stuid,stu.name,s.course,s.score FROM (SELECT sco.stuid,cour.course,sco.score FROM scores as sco INNER JOIN courses as cour ON sco.CourseID=cour.CourseID) as s INNER JOIN students AS stu ON s.stuid=stu.stuid) AS scor1 WHERE scor1.score > 80;
+-------+-------------+----------------+-------+
| stuid | name | course | score |
+-------+-------------+----------------+-------+
| 1 | Shi Zhongyu | Weituo Zhang | 93 |
| 2 | Shi Potian | Daiyu Zanghua | 97 |
| 3 | Xie Yanke | Kuihua Baodian | 88 |
| 4 | Ding Dian | Kuihua Baodian | 89 |
| 6 | Shi Qing | Hamo Gong | 96 |
| 7 | Xi Ren | Hamo Gong | 86 |
| 7 | Xi Ren | Dagou Bangfa | 83 |
| 8 | Lin Daiyu | Jinshe Jianfa | 93 |
(14)求前8位同学每位同学自己两门课的平均成绩,并按降序排列
MariaDB [hellodb]> SELECT scor1.name,avg(scor1.score) FROM (SELECT s.stuid,stu.name,s.course,s.score FROM (SELECT sco.stuid,cour.course,sco.score FROM scores as sco INNER JOIN courses as cour ON sco.CourseID=cour.CourseID) as s INNER JOIN students AS stu ON s.stuid=stu.stuid) AS scor1 GROUP BY scor1.name ORDER BY scor1.score DESC;
+-------------+------------------+
| name | avg(scor1.score) |
+-------------+------------------+
| Shi Qing | 96.0000 |
| Xie Yanke | 81.5000 |
| Xi Ren | 84.5000 |
| Shi Zhongyu | 85.0000 |
| Ding Dian | 80.0000 |
| Lin Daiyu | 75.0000 |
| Shi Potian | 72.0000 |
| Yu Yutong | 51.0000 |
+-------------+------------------+
(15)显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> SELECT s.course,COUNT(s.course) FROM (SELECT scor.Stuid,cour.Course FROM scores as scor INNER JOIN courses as cour ON scor.CourseID=cour.CourseID) AS s GROUP BY s.Course;
+----------------+-----------------+
| Course | COUNT(s.course) |
+----------------+-----------------+
| Dagou Bangfa | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Jinshe Jianfa | 1 |
| Kuihua Baodian | 4 |
| Taiji Quan | 1 |
| Weituo Zhang | 2 |
(16)如何显示其年龄大于平均年龄的同学的名字
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 |
+--------------+-----+
(19)如何显示其学习的课程为第1、2,4或第7门课的同学的名字
MariaDB [hellodb]> SELECT name,ClassID FROM students WHERE ClassID<=2
-> UNION
-> SELECT name,ClassID FROM students WHERE ClassID=4 OR ClassID=7;
+--------------+---------+
| name | ClassID |
+--------------+---------+
| Shi Zhongyu | 2 |
| Shi Potian | 1 |
| Xie Yanke | 2 |
| Wen Qingqing | 1 |
| Tian Boguang | 2 |
| Xu Zhu | 1 |
| Xiao Qiao | 1 |
| Ding Dian | 4 |
| Lin Daiyu | 7 |
| Duan Yu | 4 |
| Lin Chong | 4 |
| Hua Rong | 7 |
| Diao Chan | 7 |
| Ma Chao | 4 |
+--------------+---------+
(20)如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> SELECT stu.name,stu.ClassID , stu.age FROM students as stu inner join (SELECT classid,COUNT(name),avg(age) as aage FROM students group by classid having COUNT(name) >=3) as avg_age on stu.classid = avg_age.classid WHERE stu.age > avg_age.aage and stu.classid=avg_age.classid ORDER BY ClassID;
+---------------+---------+-----+
| name | ClassID | age |
+---------------+---------+-----+
| Shi Potian | 1 | 22 |
| Xu Zhu | 1 | 21 |
| Xie Yanke | 2 | 53 |
| Yu Yutong | 3 | 26 |
| Lin Chong | 4 | 25 |
| Ding Dian | 4 | 32 |
| Yuan Chengzhi | 6 | 23 |
| Huang Yueying | 6 | 22 |
| Hua Rong | 7 | 23 |
+---------------+---------+-----+