Linux学习 第十一周

导入hellodb.sql生成数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> /opt/hellodb_innodb.sql mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | teachers          | | toc               | +-------------------+ 7 rows in set (0.00 sec)

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select * from students where age>25 and Gender="M"; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name         | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ |     3 | Xie Yanke    |  53 | M      |       2 |        16 | |     4 | Ding Dian    |  32 | M      |       4 |         4 | |     5 | Yu Yutong    |  26 | M      |       3 |         1 | |     6 | Shi Qing     |  46 | M      |       5 |      NULL | |    13 | Tian Boguang |  33 | M      |       2 |      NULL | |    24 | Xu Xian      |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL | +-------+--------------+-----+--------+---------+-----------+ 7 rows in set (0.00 sec)

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> select ClassID,avg(Age) 平均年龄 from students group by classid order by classid; +---------+--------------+ | ClassID | 平均年龄     | +---------+--------------+ |    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.00 sec)

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

?

1 2 3 4 5 6 7 8 9

mysql> select ClassID,avg(age) 平均年龄 from students group by ClassID having 平均年龄>30; +---------+--------------+ | ClassID | 平均年龄     | +---------+--------------+ |    NULL |      63.5000 | |       2 |      36.0000 | |       5 |      46.0000 | +---------+--------------+ 3 rows in set (0.00 sec)

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

?

1 2 3 4 5 6 7 8 9

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

?

1 2	mysql> GRANT ALL PRIVILEGES ON *.* TO 'magedu'@'192.168.1.0/24' IDENTIFIED BY 'magedu'; Query OK, 0 rows affected (0.00 sec)