mysql进阶-用户的创建_修改_删除

1. 使用mysql单次查询

[root@VM-4-6-centos /]# mysql -h localhost -P 3306 -p mytest -e "select * from book1";
Enter password: 
+----+-------------+--------------------+------+
| id | category_id | book_name          | num  |
+----+-------------+--------------------+------+
|  1 |           3 | 平凡的世界         |  450 |
|  2 |           1 | 刺杀小说家         |  660 |
|  3 |           2 | 鲁滨孙漂流记       |  330 |
|  4 |           4 | 活着               |  200 |
+----+-------------+--------------------+------+

过程： 连接mysql，查询，然后自动关闭

2. 查看mysql的用户

mysql中有一个默认存在的数据库mysql，该数据库下面user表中存储了系统用户数据

2.1 登入并使用mysql系统数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 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

2.2 查看用户数据

mysql> select * from user\G;

或者

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.01 sec)

3 创建系统用户

3.1 添加用户

mysql> CREATE USER 'zhang3' identified by '@ZhangSan123456';
Query OK, 0 rows affected (0.01 sec)

注意： 系统默认密码必须同时包含大写字母，小写字母，数字，特殊字符，如果不满足会报错。

mysql> CREATE USER 'zhang3' identified by 'zhangsan123456...';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

如果想设置简单一点的密码，需要修改密码等级，参考 修改密码等级

3.2 重新查看系统用户
%： 用户可以任何ip访问，可本地可远程
localhost： 用户只能本地连接访问

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | zhang3        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

3.3 添加一个只能本地连接的系统用户
两个名字为zhang3的用户，一个可以远程，一个只能本地。

mysql> CREATE USER 'zhang3'@'localhost' identified by '@ZhangSan123456';
mysql> SELECT host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | zhang3        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zhang3        |
+-----------+---------------+
6 rows in set (0.00 sec)

4.修改用户信息

4.1 本质就是修改user表中的数据，操作语法的sql的更新语法一致

mysql> update user set user= 'li4' where user = 'zhang3' and host = '%';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | li4           |
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zhang3        |
+-----------+---------------+
6 rows in set (0.00 sec)

4.2 修改完刷新一下权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

否则会跟我一样，报了一个错还一脸懵逼

[root@VM-4-6-centos /]# mysql -u li4 -p@ZhangSan123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'li4'@'localhost' (using password: YES)
[root@VM-4-6-centos /]# mysql -uli4 -p@ZhangSan123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'li4'@'localhost' (using password: YES)

4.3 刷新完，重新登入

[root@VM-4-6-centos /]# mysql -u li4 -p@ZhangSan123456
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 348
Server version: 5.7.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> 

mysql: [Warning] Using a password on the command line interface can be insecure.
意思是登入的时候把密码暴露在外面不安全

4.4 发现新增的用户没什么权限

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'li4'@'%' to database 'mysql'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> 

5.删除用户

5.1 默认删除 % ，不需要刷新

mysql> drop user 'li4';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zhang3        |
+-----------+---------------+
5 rows in set (0.00 sec)

5.2 删除localhost的用户
drop user 'zhang3' @'localhost';

mysql> drop user 'zhang3' ;
ERROR 1396 (HY000): Operation DROP USER failed for 'zhang3'@'%'
mysql> drop user 'zhang3' @'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)