MySQL数据库管理二

1、数据表高级操作

（1）克隆表

方法一：
create table 新表名 like 旧表名; #克隆表结构
insert into 新表名 select * from 旧表名; #克隆表数据

#此方法能保证新表的表结构、表数据跟旧表都是一致的

方法二：
CREATE TABLE 新表名 (SELECT * from 旧表名);

#此方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致

show create table test02\G #获取数据表的表结构、索引等信息
SELECT * from test02;

mysql> show tables;
+-----------------+
| Tables_in_zx101 |
+-----------------+
| zx              |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from zx;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | zx   | 男   |   22 |
|  2 | ctt  | 女   |   23 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> create table zx1 like zx;    ##复制表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_zx101 |
+-----------------+
| zx              |
| zx1             |
+-----------------+
2 rows in set (0.00 sec)

mysql> insert into zx1 select * from zx;  ##复制表数据
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from zx1;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | zx   | 男   |   22 |
|  2 | ctt  | 女   |   23 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> create table zx2 (select * from zx);   ##或者直接复制表和数据但可能会出现两个表的结构不一致
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from zx2;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | zx   | 男   |   22 |
|  2 | ctt  | 女   |   23 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql>

（2）清空表数据

方法一：
delete from xy102;
#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

方法二：
truncate table xy102;
#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

方法一

mysql>  create table zx4(id int AUTO_INCREMENT, name varchar(20), sex char(2), age int, primary key(id));   ##设置id自增长
Query OK, 0 rows affected (0.00 sec)

自己添加表内容

mysql> select * from zx4;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | jlh  | 男   |   24 |
|  2 | zx   | 男   |   22 |
|  3 | zyr  | 男   |   20 |
|  4 | tc   | 男   |   19 |
+----+------+------+------+
4 rows in set (0.00 sec)

mysql> show create table zx4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| zx4   | CREATE TABLE "zx4" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "name" varchar(20) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |   ##自增长数据为5
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> delete from zx4;   ##删除表数据
Query OK, 4 rows affected (0.00 sec)

mysql> show create table zx4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| zx4   | CREATE TABLE "zx4" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "name" varchar(20) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |  ##自增长数据仍为5
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into zx4(name, sex, age) values( 'tc', '男', 19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from zx4;     ##添加数据时id按之前的数据自增1
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  5 | tc   | 男   |   19 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql>

方法二

mysql> truncate table zx4;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table zx4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| zx4   | CREATE TABLE "zx4" (
  "id" int(11) NOT NULL AUTO_INCREMENT,   ##id为自增长
  "name" varchar(20) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into zx4(name, sex, age) values( 'tc', '男', 19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from zx4;    ##添加内容id从1开始
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | tc   | 男   |   19 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql>

（3）创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

创建临时表，但show tables;查不到

mysql>  create temporary table linshi(id int AUTO_INCREMENT, name varchar(20), sex char(2), age int, pri
mary key(id));
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_zx101 |
+-----------------+
| zx              |
| zx1             |
| zx2             |
| zx4             |
+-----------------+
4 rows in set (0.00 sec)

mysql>

临时表可以进行增删改查操作

mysql> insert into linshi values(1, 'tc', '男', 19);
Query OK, 1 row affected (0.00 sec)

mysql> insert into linshi values(2, 'zx', '男', 20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from linshi;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | tc   | 男   |   19 |
|  2 | zx   | 男   |   20 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> delete from linshi where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from linshi;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | tc   | 男   |   19 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql>

2、外键约束（保证数据的完整性和一致性）

外键的定义：如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

（1）创建主从表

#创建主表 class
create table class (name varchar(10), price int);

#创建从表 student
create table student (id int, username varchar(20), ageint, sex char(2), classname varchar(10));

#为主表class添加一个主键约束,主键名建议以“PK_”开头，可以省略不写，系统会自动分配一个名称
alter table class add primary key (name);

#为从表student表添加外键，并将class表的name字段和 student表的classname字段建立外键关联。外键名建议以“FK_”开头，可以省略不写，系统会自动分配一个名称
alter table student add foreign key (classname) references class(name);

desc class; #查看主键表的表结构
desc student; #查看外键表的表结构
show create table student; #可以查看表结构的详细信息

mysql> create table class (name varchar(10), price int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table student (id int, username varchar(20), age int, sex char(2), classname varchar(10));

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> alter table class add primary key (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add foreign key (classname) references class(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class\G
*************************** 1. row ***************************
       Table: class
Create Table: CREATE TABLE "class" (
  "name" varchar(10) NOT NULL,
  "price" int(11) DEFAULT NULL,
  PRIMARY KEY ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE "student" (
  "id" int(11) DEFAULT NULL,
  "username" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "classname" varchar(10) DEFAULT NULL,
  KEY "classname" ("classname"),
  CONSTRAINT "student_ibfk_1" FOREIGN KEY ("classname") REFERENCES "class" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

（2）主从表中插入数据

#插入新的数据记录时，要先主表再从表
insert into class values('云计算', 24000);
insert into class values('JAVA', 25000);
insert into student values(1, 'zhangsan', 2, '男', 'JAVA');

mysql> insert into class values('云计算', 24000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values('JAVA', 25000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+-----------+-------+
| name      | price |
+-----------+-------+
| JAVA      | 25000 |
| 云计算    | 24000 |
+-----------+-------+
2 rows in set (0.00 sec)

mysql> insert into student values(1, 'zhangsan', 20, '男', 'JAVA');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+----------+------+------+-----------+
| id   | username | age  | sex  | classname |
+------+----------+------+------+-----------+
|    1 | zhangsan |   20 | 男   | JAVA      |
+------+----------+------+------+-----------+
1 row in set (0.00 sec)

mysql> insert into student values(2, 'lisi', 21, '男', 'JSP');  ##classname的值只能是class表中name值中的两个之一
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("zx101"."student", CONSTRAINT "student_ibfk_1" FOREIGN KEY ("classname") REFERENCES "class" ("name"))
mysql>

（3）主从表中删除数据

#删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。

delete from student where id=1;

delete from class where name='JAVA';

mysql> select * from student;
+------+----------+------+------+-----------+
| id   | username | age  | sex  | classname |
+------+----------+------+------+-----------+
|    1 | zhangsan |   20 | 男   | JAVA      |
+------+----------+------+------+-----------+
1 row in set (0.00 sec)

mysql> insert into student values(2, 'lisi', 21, '男', '云计算');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+----------+------+------+-----------+
| id   | username | age  | sex  | classname |
+------+----------+------+------+-----------+
|    1 | zhangsan |   20 | 男   | JAVA      |
|    2 | lisi     |   21 | 男   | 云计算    |
+------+----------+------+------+-----------+
2 rows in set (0.00 sec)

mysql> delete from class where name='JAVA'; ##想要删除主从表中的数据，必须先删除从表中外键约束的内容，再删除主键内容，负责无法删除
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ("zx101"."student", CONSTRAINT "student_ibfk_1" FOREIGN KEY ("classname") REFERENCES "class" ("name"))
mysql> delete from student where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from class where name='JAVA';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+----------+------+------+-----------+
| id   | username | age  | sex  | classname |
+------+----------+------+------+-----------+
|    2 | lisi     |   21 | 男   | 云计算    |
+------+----------+------+------+-----------+
1 row in set (0.00 sec)

mysql> select * from class;
+-----------+-------+
| name      | price |
+-----------+-------+
| 云计算    | 24000 |
+-----------+-------+
1 row in set (0.00 sec)

mysql>

（4）删除外键约束

#查看和删除外键约束

show create table student;

desc student;

alter table student drop foreign key student_ibfk_1;

alter table student drop key classname;

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE "student" (
  "id" int(11) DEFAULT NULL,
  "username" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "classname" varchar(10) DEFAULT NULL,
  KEY "classname" ("classname"),
  CONSTRAINT "student_ibfk_1" FOREIGN KEY ("classname") REFERENCES "class" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table student drop foreign key student_ibfk_1; ##想删除外键，先删除外键别名
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE "student" (
  "id" int(11) DEFAULT NULL,
  "username" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "classname" varchar(10) DEFAULT NULL,
  KEY "classname" ("classname")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table student drop key classname; ##再删除外键键名
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE "student" (
  "id" int(11) DEFAULT NULL,
  "username" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "classname" varchar(10) DEFAULT NULL  ##外键被删除
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

3、数据库用户管理

（1）新建用户

create user '用户名'@'来源地址' [identified by [password] '密码'];
'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

mysql> create user 'zx'@'%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx            | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql>

（2）查看用户信息

#创建后的用户保存在 mysql 数据库的 user 表里
USE mysql;
SELECT User,authentication_string,Host from user;
#查看当前登录用户
select user();

[root@zx1 ~]# mysql -u zx -pabc123 -h 20.0.0.10
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 3
Server version: 5.7.44 Source distribution

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> select user();
+--------------+
| user()       |
+--------------+
| zx@20.0.0.10 |
+--------------+
1 row in set (0.00 sec)

mysql>

（3）重命名用户

RENAME USER '用户名'@'来源地址' TO '新用户名'@'来源地址';

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx            | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> rename user 'zx'@'%' to 'zx1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx1           | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql>

（4）删除用户

DROP USER '用户名'@'来源地址';

mysql> rename user 'zx'@'%' to 'zx1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx1           | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

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

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

（5）修改当前登录用户密码

方法1

SET PASSWORD = PASSWORD('123456');

方法2

ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123';

方法一

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@zx1 ~]# mysql -u root -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 5
Server version: 5.7.44 Source distribution

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> alter user 'root'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@zx1 ~]# mysql -u root -pabc123
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 6
Server version: 5.7.44 Source distribution

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>

（6）修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123');

mysql> create user 'zx'@'%' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx            | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> alter user 'root'@'localhost' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'zx'@'%' = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

（7）忘记 root 密码的解决办法

（1）修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加，使登录mysql不使用授权表

systemctl restart mysqld

mysql #直接登录

（2）使用 update 修改 root 密码，刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123

[root@zx1 ~]# vim /etc/my.cnf
[root@zx1 ~]# systemctl restart mysqld
[root@zx1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 Source distribution

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> update mysql.user set authentication_string = password('abc123') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 1

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

mysql> quit
Bye
[root@zx1 ~]# mysql -u root -pabc123
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 3
Server version: 5.7.44 Source distribution

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>

在server-id = 1下一行添加此内容

4、数据库用户授权

（1）授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
#mysql 5.7或之前的版本支持使用 IDENTIFIED BY '密码' 创建用户和授予权限，8.0开始只能用于授予权限。

----------------------------------------------------------------------------------------------------------
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“mydb.*”表示授权操作的对象为 mydb数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.mydb.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

zx用户只有登录权限

[root@zx1 ~]# mysql -u zx -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 4
Server version: 5.7.44 Source distribution

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql>

进入root用户给zx用户授权

[root@zx1 ~]# mysql -u root -pabc123
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 5
Server version: 5.7.44 Source distribution

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zx0105             |
| zx101              |
+--------------------+
6 rows in set (0.00 sec)

mysql> use zx101;
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
mysql> show tables;
+-----------------+
| Tables_in_zx101 |
+-----------------+
| class           |
| student         |
| zx              |
| zx1             |
| zx2             |
| zx4             |
+-----------------+
6 rows in set (0.00 sec)

mysql> grant select on zx101.* to 'zx'@'%'; ##授权zx用户数据库zx101的select权限
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
[root@zx1 ~]# mysql -u zx -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 8
Server version: 5.7.44 Source distribution

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> select user();
+--------------+
| user()       |
+--------------+
| zx@localhost |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| zx101              |
+--------------------+
2 rows in set (0.00 sec)

mysql> use zx101;
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
mysql> show tables;
+-----------------+
| Tables_in_zx101 |
+-----------------+
| class           |
| student         |
| zx              |
| zx1             |
| zx2             |
| zx4             |
+-----------------+
6 rows in set (0.00 sec)

mysql> select * from zx101.zx;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | zx   | 男   |   22 |
|  2 | ctt  | 女   |   23 |
|  3 | jlh  | 男   |   21 |
|  4 | zyr  | 男   |   20 |
+----+------+------+------+
4 rows in set (0.00 sec)

mysql> delect from zx101.zx where id=1; ##只有select的权限，没有其他权限
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 'delect from zx101.zx where id=1' at line 1
mysql>

（2）查看权限

SHOW GRANTS FOR 用户名@来源地址;

[root@zx1 ~]# mysql -u root -pabc123
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 9
Server version: 5.7.44 Source distribution

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> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zx            | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> show grants for 'zx'@'%';
+---------------------------------------+
| Grants for zx@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'zx'@'%'        |
| GRANT SELECT ON "zx101".* TO 'zx'@'%' |
+---------------------------------------+
2 rows in set (0.00 sec)

mysql>

（3）撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

mysql> revoke select on zx101.* from 'zx'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'zx'@'%';
+--------------------------------+
| Grants for zx@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO 'zx'@'%' |
+--------------------------------+
1 row in set (0.00 sec)

mysql>