11.数据库练习

---

前言

上次完成了数据库的安装，下面来完成一些练习。

---

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

导入hellodb数据库用来做为练习的材料。

[root@localhost ~]# mysql -uroot < hellodb_innodb.sql
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.24-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.000 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 [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.001 sec)

MariaDB [hellodb]> 

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

MariaDB [hellodb]> select Name,age from students where Age>25 && Gender="M";
+--------------+-----+
| 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.001 sec)

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

MariaDB [hellodb]> select AVG(age) as AVG_age,classid from students group by classid ;
+---------+---------+
| AVG_age | classid |
+---------+---------+
| 63.5000 |    NULL |
| 20.5000 |       1 |
| 36.0000 |       2 |
| 20.2500 |       3 |
| 24.7500 |       4 |
| 46.0000 |       5 |
| 20.7500 |       6 |
| 19.6667 |       7 |
+---------+---------+
8 rows in set (0.001 sec)

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

MariaDB [hellodb]> create view avg_GroupbyClassid AS select AVG(age) as AVG_age,classid from students group by classid having AVG_age>30;
Query OK, 0 rows affected (0.036 sec)

MariaDB [hellodb]> show tables;
+--------------------+
| Tables_in_hellodb  |
+--------------------+
| avg_GroupbyClassid |
| classes            |
| coc                |
| courses            |
| scores             |
| students           |
| teachers           |
| toc                |
+--------------------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select AVG(AVG_age) from avg_GroupbyClassid;
+--------------+
| AVG(AVG_age) |
+--------------+
|  48.50000000 |
+--------------+
1 row in set (0.001 sec)

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

MariaDB [hellodb]> select * from students where Name="L%";
Empty set (0.001 sec)

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.001 sec)

MariaDB [hellodb]>

二、用户与权限

2.1 创建了2个用户，并给予了不同的权限

MariaDB [hellodb]> create user jiangde@192.168.1.33 identified by 'jiangde@123';
Query OK, 0 rows affected (0.037 sec)

MariaDB [mysql]> grant all privileges on *.* to 'jiangde'@'192.168.1.33';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> create user jiangde1@192.168.1.32 identified by 'test@123';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> grant all privileges on hellodb.* to 'jiangde1'@'192.168.1.32';
Query OK, 0 rows affected (0.029 sec)

MariaDB [mysql]>

2.2 测试用户权限

下面输出可以看到192.168.1.33主机有所有的权限，而192.168.1.32只有hellodb数据库的权限。

#192.168.1.33主机
[root@CentOS7 ~]# hostname -I
192.168.1.33
[root@CentOS7 ~]# mysql -h 192.168.1.42 -u'jiangde' -p'jiangde@123'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.24-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]>
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  |
+--------------------+
| avg_GroupbyClassid |
| classes            |
| coc                |
| courses            |
| scores             |
| students           |
| teachers           |
| toc                |
+--------------------+
8 rows in set (0.00 sec)

#192.168.1.32主机
[root@localhost ~]# mysql -h 192.168.1.42 -u'jiangde' -p'jiangde@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '192.168.1.32' is not allowed to connect to this MariaDB server
[root@localhost ~]# mysql -h 192.168.1.42 -u'jiangde1' -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.5.5-10.4.24-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> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

---

总结

学习了数据库的相关操作，还有很多未包含在内，而且也很不熟练，需要多加练习才行。