十一周 MySQL数据库二、三

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

[root@centos7 ~]#mysql <hellodb_innodb.sql
[root@centos7 ~]#mysql hellodb

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

MariaDB [(hellodb)]>select name,age from students where age>25 and gender='M';

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

MariaDB [(hellodb)]>select classid,avg(age) from students group by classid;

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

MariaDB [(hellodb)]>select classid,avg(age) from students group by classid having avg(age)>30;

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

MariaDB [(hellodb)]>select * from students where name like 'L%';

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

MariaDB [(hellodb)]> create user magedu@'192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(hellodb)]> select user,host,password from mysql.user where user='magedu';
+--------+-------------+-------------------------------------------+
| user   | host        | password                                  |
+--------+-------------+-------------------------------------------+
| magedu | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-------------+-------------------------------------------+


MariaDB [mysql]>grant all on *.* to magedu@'192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]>show grants for magedu@'192.168.0.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for magedu@192.168.0.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'magedu'@'192.168.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#已有账户修改权限
MariaDB [mysql]>update user set host='192.168.0.%' where user='magedu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]>flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]>select user,host,password from user where user='magedu';
+--------+-------------+-------------------------------------------+
| user   | host        | password                                  |
+--------+-------------+-------------------------------------------+
| magedu | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-------------+-------------------------------------------+
1 row in set (0.00 sec)