MySQL数据库

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

环境：MySQL8.0.26

root@Centos8 ~]# ll
total 16
-rw-------. 1 root root 1470 Sep 27 19:52 anaconda-ks.cfg
-rw-------. 1 root root  319 Sep 27 20:56 dead.letter
-rw-r--r--  1 root root 7786 Sep  1 18:14 hellodb_innodb.sql
[root@Centos8 ~]# mysql -uroot -p123456 < hellodb_innodb.sql #方法1，命令行导入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@Centos8 ~]# mysql -uroot -p123456 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.26 Source distribution

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.

06:22:14(root@localhost) [(none)]> showdatabaes;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'showdatabaes' at line 1
06:22:26(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

06:29:41(root@localhost) [information_schema]> \. /root/hellodb_innodb.sql #第二种方法，数据库中导入
Query OK, 0 rows affected (0.00 sec)

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

06:32:44(root@localhost) [hellodb]> select name,age from students where age>25;

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

06:46:34(root@localhost) [hellodb]> select classid 班级编号,avg(age) 平均年龄 from students group by classid;

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

06:53:06(root@localhost) [hellodb]> select classid 班级编号,avg(age) 平均年龄 from students group by classid having avg(age)>30;

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

07:04:17(root@localhost) [hellodb]> SELECT * FROM students WHERE name LIKE 'L%';

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

MySQL数据库主机

07:30:46(root@localhost) [mysql]> create user 'magedu'@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec) #mysql8.0以上版本创建账号及授权需分开

07:31:41(root@localhost) [mysql]> grant all privileges on *.* to 'magedu'@'192.168.1.0' with grant option;
ERROR 1410 (42000): You are not allowed to create a user with GRANT #第一次授权提示权限不够，
产生用户不能授权的原因是mysql 数据库中user 表中的特定用户(root) 的host 的属性值为localhost.
07:36:52(root@localhost) [mysql]> update user set host='%' where user='root'; #修改特定用户host
Query OK, 1 row affected (0.00 sec)
07:37:17(root@localhost) [mysql]> grant all privileges on test.* to root@'%'; #指定用户的授权
Query OK, 0 rows affected (0.00 sec)

07:37:46(root@localhost) [mysql]> grant all privileges on *.* to 'magedu'@'192.168.1.%' with grant option;
Query OK, 0 rows affected (0.01 sec) #对magedu账户授权

远程连接主机

[root@Centos8 ~]# mysql -h192.168.1.100 -umagedu -p123456 登录验证
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.26 Source distribution

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 8.0.26 for Linux on x86_64 (Source distribution)

Connection id:		16
Current database:	
Current user:		magedu@192.168.1.200
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.26 Source distribution
Protocol version:	10
Connection:		192.168.1.100 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Binary data as:		Hexadecimal
Uptime:			6 hours 47 min 10 sec

Threads: 4  Questions: 505  Slow queries: 0  Opens: 338  Flush tables: 3  Open tables: 236  Queries per second avg: 0.020
--------------

mysql>