马哥教育N63013-第十一周作业

1、 导入hellodb.sql生成数据库

mysql -uroot -p123456 < hellodb_innodb.sql
(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 |
+--------------+-----+
7 rows in set (0.00 sec)

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

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

(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 |
+---------+----------+
3 rows in set (0.00 sec)

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

MariaDB [hellodb]> select * from students where Name like ‘L%’;
±------±------------±----±-------±--------±----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
±------±------------±----±-------±--------±----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
±------±------------±----±-------±--------±----------+
3 rows in set (0.00 sec)

2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

MariaDB [hellodb]> grant all on hellodb.* to magedu@'192.168.1.%' identified by '123456';