第十一周提交

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

# mysql -u root -p -S /var/lib/mysql/mysql.sock < /data/hellodb_innodb.sql

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

mysql> 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.03 sec)

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

mysql> 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 |

+---------+----------+

8 rows in set (0.40 sec)

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

mysql> 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)

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

mysql> 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

mysql>CREATE USER magedu@’192.168.1.%’ IDENTIFIED BY ‘password’;

mysql> GRANT ALL ON hellodb.* TO mgedu@'192.168.1.%';