The Eleventh Weeks

The Eleventh Weeks（Lucklyzheng）

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

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

MariaDB [(none)]> source hellodb_innodb.sql
MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbzpp2             |
| dbzpp3             |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.001 sec)

MariaDB [hellodb]> use hellodb;
Database changed
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)         | NO   |     | NULL    |                |
| Age       | tinyint(3) unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.044 sec)

MariaDB [hellodb]> 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 |
+--------------+-----+

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

MariaDB [hellodb]> select ClassID,avg(age) as 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.001 sec)

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

MariaDB [hellodb]> select ClassID,avg(age) as 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.029 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 |
|    26 | Lione       |  40 | M      |       5 |         3 |
|    27 | Litwo       |  20 | M      |       3 |         3 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.035 sec)

MariaDB [hellodb]> select * from students where Name rlike '^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 |
|    26 | Lione       |  40 | M      |       5 |         3 |
|    27 | Litwo       |  20 | M      |       3 |         3 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.096 sec)

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

MariaDB [hellodb]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> create user 'zheng'@'192.168.33.%';
Query OK, 0 rows affected (0.048 sec)

MariaDB [mysql]> select authentication_string,password,user,host from user;
-+-------------+--------------+
| authentication_string                     | Password                                  | User        | Host         |
+-------------------------------------------+-------------------------------------------+-------------+--------------+
|                                           |                                           | mariadb.sys | localhost    |
| *1DA8F4346484CD9DEC3995995493D26581B7F5F8 | *1DA8F4346484CD9DEC3995995493D26581B7F5F8 | root        | localhost    |
| invalid                                   | invalid                                   | mysql       | localhost    |
|                                           |                                           | zheng       | 192.168.33.% |
+--------------------------------
MariaDB [mysql]> set password for 'zheng'@'192.168.33.%' = password('xxxxxxx');
MariaDB [mysql]> grant all privileges on *.* to 'zheng'@'192.168.33.%' identified by 'xxxxxxx';
MariaDB [mysql]> flush privileges;

[20:43:00 root@zpp-master1 ~]#\mysql -uzheng -p'zheng.0830' -h192.168.33.129
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbzpp2 |
| dbzpp3 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+