1.首先把new_data.sql的数据全部导入到mysql中去
mysql -uroot -proot
use geng;
粘贴new_data.sql数据 创建成功
2.统计出0001和0002分别有多少人
首先 查询数据
mysql> select * from grades;
+----------------+----------------+-------+
| student_number | subject_number | grade |
+----------------+----------------+-------+
| 202404001 | 0001 | 90 |
| 202404002 | 0001 | 89 |
| 202404003 | 0001 | 88 |
| 202404004 | 0001 | 87 |
| 202404005 | 0001 | 86 |
| 202404006 | 0001 | 85 |
| 202404007 | 0001 | 84 |
| 202404008 | 0001 | 83 |
| 202404009 | 0001 | 82 |
| 202404017 | 0001 | 91 |
| 202404016 | 0001 | 91 |
| 202404011 | 0001 | 91 |
| 202404010 | 0001 | 81 |
| 202404001 | 0002 | 80 |
| 202404002 | 0002 | 79 |
| 202404003 | 0002 | 78 |
| 202404004 | 0002 | 77 |
| 202404005 | 0002 | 76 |
| 202404006 | 0002 | 75 |
| 202404007 | 0002 | 74 |
| 202404008 | 0002 | 73 |
| 202404009 | 0002 | 72 |
| 202404010 | 0002 | 71 |
| 202404012 | 0001 | 91 |
| 202404013 | 0001 | 91 |
| 202404014 | 0001 | 91 |
| 202404015 | 0001 | 91 |
| 202404016 | 0001 | 91 |
+----------------+----------------+-------+
进行查询
mysql> select count(*)from grades group by subject_number;
+----------+
| count(*) |
+----------+
| 18 |
| 10 |
+----------+
2 rows in set (0.01 sec)
3.统计出0001中age 大于18岁的人数
查询数据
mysql> select * from students where age>18;
+-----------+----------+------+------------+
| number | name | age | birth |
+-----------+----------+------+------------+
| 202404004 | Stefer | 19 | 2001-01-04 |
| 202404005 | Steven | 20 | 2000-01-05 |
| 202404006 | Mark | 21 | 1999-01-06 |
| 202404007 | Stark | 22 | 1999-01-07 |
| 202404008 | Tonny | 23 | 1999-01-08 |
| 202404009 | Jarvis | 24 | 1999-01-09 |
| 202404010 | ZhangSan | 25 | 1999-01-10 |
| 202404011 | lisi | 23 | 1990-04-10 |
| 202404012 | wanger | 20 | 1991-01-10 |
| 202404013 | mazi | 21 | 1993-01-13 |
| 202404014 | xiaoxing | 80 | 1995-01-22 |
| 202404015 | hundan | 36 | 1998-05-10 |
| 202404016 | xiaowang | 30 | 1999-07-10 |
+-----------+----------+------+------------+
13 rows in set (0.00 sec)
排序
mysql> select * from grades order by subject_number;
+----------------+----------------+-------+
| student_number | subject_number | grade |
+----------------+----------------+-------+
| 202404001 | 0001 | 90 |
| 202404002 | 0001 | 89 |
| 202404003 | 0001 | 88 |
| 202404004 | 0001 | 87 |
| 202404005 | 0001 | 86 |
| 202404006 | 0001 | 85 |
| 202404007 | 0001 | 84 |
| 202404008 | 0001 | 83 |
| 202404009 | 0001 | 82 |
| 202404017 | 0001 | 91 |
| 202404016 | 0001 | 91 |
| 202404011 | 0001 | 91 |
| 202404010 | 0001 | 81 |
| 202404012 | 0001 | 91 |
| 202404013 | 0001 | 91 |
| 202404014 | 0001 | 91 |
| 202404015 | 0001 | 91 |
| 202404016 | 0001 | 91 |
| 202404001 | 0002 | 80 |
| 202404002 | 0002 | 79 |
| 202404003 | 0002 | 78 |
| 202404004 | 0002 | 77 |
| 202404005 | 0002 | 76 |
| 202404006 | 0002 | 75 |
| 202404007 | 0002 | 74 |
| 202404008 | 0002 | 73 |
| 202404009 | 0002 | 72 |
| 202404010 | 0002 | 71 |
+----------------+----------------+-------+
28 rows in set (0.00 sec)
思路是将 subject_number 进行排序 然后取出前面18条001的值 在跟age>18的数据连接
18条001取不出来 丢失001的数据 取出002的数据
mysql> select * from grades limit 18;
+----------------+----------------+-------+
| student_number | subject_number | grade |
+----------------+----------------+-------+
| 202404001 | 0001 | 90 |
| 202404002 | 0001 | 89 |
| 202404003 | 0001 | 88 |
| 202404004 | 0001 | 87 |
| 202404005 | 0001 | 86 |
| 202404006 | 0001 | 85 |
| 202404007 | 0001 | 84 |
| 202404008 | 0001 | 83 |
| 202404009 | 0001 | 82 |
| 202404017 | 0001 | 91 |
| 202404016 | 0001 | 91 |
| 202404011 | 0001 | 91 |
| 202404010 | 0001 | 81 |
| 202404001 | 0002 | 80 |
| 202404002 | 0002 | 79 |
| 202404003 | 0002 | 78 |
| 202404004 | 0002 | 77 |
| 202404005 | 0002 | 76 |
+----------------+----------------+-------+
18 rows in set (0.00 sec)
