第十一周作业

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

数据库文件链接：https://files.cnblogs.com/files/blogs/726215/hellodb_innodb.zip?t=1653788830

# 上传已有的数据库文件，并导入数据库中
[root@Rocky8-mini2 ~]# ls
hellodb_innodb.sql
[root@Rocky8-mini2 ~]# mysql < hellodb_innodb.sql
MariaDB [(none)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> use hellodb;
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 [(none)]> use hellodb;
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 [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.001 sec)

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

MariaDB [hellodb]> select Name,Age,Gender from students
    -> where age>25 and Gender='M';
+--------------+-----+--------+
| Name         | Age | Gender |
+--------------+-----+--------+
| Xie Yanke    |  53 | M      |
| Ding Dian    |  32 | M      |
| Yu Yutong    |  26 | M      |
| Shi Qing     |  46 | M      |
| Tian Boguang |  33 | M      |
| Xu Xian      |  27 | M      |
| Sun Dasheng  | 100 | M      |
+--------------+-----+--------+
7 rows in set (0.000 sec)

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

MariaDB [hellodb]> select ClassID ,avg(Age) 平均年龄  from students
    -> group 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.001 sec)

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

MariaDB [hellodb]> select ClassID ,avg(Age) 平均年龄  from students
    -> group by ClassID
    -> having avg(Age) > 30; 
    +---------+--------------+
    | ClassID | 平均年龄     |   
    +---------+--------------+
    |    NULL |      63.5000 |
    |       2 |      36.0000 |
    |       5 |      46.0000 |
    +---------+--------------+
    3 rows in set (0.001 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.000 sec)

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

# 查看当前数据库中的用户
MariaDB [hellodb]> select user,host from mysql.user;
+-------------+--------------+
| User        | Host         |
+-------------+--------------+
|             | localhost    |
| mariadb.sys | localhost    |
| mysql       | localhost    |
| root        | localhost    |
|             | rocky8-mini2 |
+-------------+--------------+
5 rows in set (0.001 sec)

# 增加指定的用户
MariaDB [hellodb]> create user magedu@'192.168.119.%';
Query OK, 0 rows affected (0.009 sec)
# 查看是否创建成功
MariaDB [hellodb]> select user,host,password from mysql.user;  
+-------------+---------------+-------------------------------------------+
| User        | Host          | Password                                  |
+-------------+---------------+-------------------------------------------+
| mariadb.sys | localhost     |                                           |
| root        | localhost     | *2690FEEA8D4D050D9B1000360EAE7496905DC8B2 |
| mysql       | localhost     | invalid                                   |
|             | localhost     |                                           |
|             | rocky8-mini2  |                                           |
| magedu      | 192.168.119.% |                                           |
+-------------+---------------+-------------------------------------------+
6 rows in set (0.001 sec)

# 使用magedu账户测试登录
[root@centos7-mini2 ~]# mysql -umagedu -h192.168.119.138
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.5-10.4.25-MariaDB MariaDB Server

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.33, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          17
Current database:
Current user:           magedu@192.168.119.147
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.5-10.4.25-MariaDB MariaDB Server
Protocol version:       10
Connection:             192.168.119.138 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 6 hours 14 min 19 sec

Threads: 7  Questions: 187  Slow queries: 0  Opens: 39  Flush tables: 1  Open tables: 25  Queries per second avg: 0.008