当前位置: 首页 > news >正文

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: 0mysql> 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: 0mysql> 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: 0mysql> alter table student add foreign key (classname) references class(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 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: 0mysql> 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: 0mysql> 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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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: 1mysql> 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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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>

相关文章:

MySQL数据库管理 二

1、数据表高级操作 (1)克隆表 方法一: create table 新表名 like 旧表名; #克隆表结构 insert into 新表名 select * from 旧表名; #克隆表数据 #此方法能保证 新表的表结构、表数据 跟旧表都是一致的 方法二&#x…...

android system UI 基础的基础

Android 系统中的 SystemUI 是一种特殊的应用程序,它负责管理和显示设备的用户界面组件,例如状态栏、导航栏和最近任务列表等。SystemUI 是在 Android 启动过程中由 Zygote 进程启动的。以下是 SystemUI 启动过程的详细步骤: SystemUI 启动过…...

ARM32开发——GD32F4定时器查询

🎬 秋野酱:《个人主页》 🔥 个人专栏:《Java专栏》《Python专栏》 ⛺️心若有所向往,何惧道阻且长 文章目录...

【机器学习】第7章 集成学习(小重点,混之前章节出题但小题)

一、概念 1.集成学习,顾名思义,不是一个玩意,而是一堆玩意混合到一块。 (1)基本思想是先 生成一定数量基学习器,再采用集成策略 将这堆基学习器的预测结果组合起来,从而形成最终结论。 &#x…...

代码随想录——子集Ⅱ(Leecode 90)

题目链接 回溯 class Solution {List<List<Integer>> res new ArrayList<List<Integer>>();List<Integer> list new ArrayList<Integer>();boolean[] used; public List<List<Integer>> subsetsWithDup(int[] nums) {use…...

vue关闭页面时触发的函数(ai生成)

在Vue中&#xff0c;可以通过监听浏览器的beforeunload事件来在关闭页面前触发函数。这里是一个简单的示例&#xff1a; new Vue({el: #app,methods: {handleBeforeUnload(event) {// 设置returnValue属性以显示确认对话框event.returnValue 你确定要离开吗&#xff1f;;// 在…...

马尔可夫性质与Q学习在强化学习中的结合

马尔可夫性质是强化学习&#xff08;RL&#xff09;算法的基础&#xff0c;特别是在Q学习中。马尔可夫性质指出&#xff0c;系统的未来状态只依赖于当前状态&#xff0c;而与之前的状态序列无关。这一性质简化了学习最优策略的问题&#xff0c;因为它减少了状态转移的复杂性。 …...

【LeetCode 5.】 最长回文子串

一道题能否使用动态规划就在于判断最优结构是否是通过最优子结构推导得到&#xff1f;如果显然具备这个特性&#xff0c;那么就应该朝动态规划思考。如果令dp[i][j]表示串s[i:j1]是否是回文子串&#xff0c;那么判断dp[i][j] 是否是回文子串&#xff0c;相当于判断s[i] 与 s[j]…...

联邦学习周记|第四周

论文&#xff1a;Active Federated Learning 链接 将主动学习引入FL&#xff0c;每次随机抽几个Client拿来train&#xff0c;把置信值低的Client概率调大&#xff0c;就能少跑几次。 论文&#xff1a;Active learning based federated learning for waste and natural disast…...

机器学习课程复习——逻辑回归

1. 激活函数 Q:激活函数有哪些? SigmoidS型函数Tanh 双曲正切函数...

Rocky Linux 更换CN镜像地址

官方镜像列表&#xff0c;下拉查找 官方镜像列表&#xff1a;https://mirrors.rockylinux.org/mirrormanager/mirrorsCN 开头的站点。 一键更改镜像地址脚本 以下是更改从默认更改到阿里云地址 cat <<EOF>>/RackyLinux_Update_repo.sh #!/bin/bash # -*- codin…...

Linux rm命令由于要删的文件太多报-bash: /usr/bin/rm:参数列表过长,无法删除的解决办法

银河麒麟系统&#xff0c;在使用rm命令删除文件时报了如下错误&#xff0c;删不掉&#xff1a; 查了一下&#xff0c;原因就是要删除的文件太多了&#xff0c;例如我当前要删的文件共有这么多&#xff1a; 查到了解决办法&#xff0c;记录在此。需要使用xargs命令来解决参数列表…...

【包管理】Node.JS与Ptyhon安装

文章目录 Node.JSPtyhon Node.JS Node.js的安装通常包括以下几个步骤&#xff1a; 访问Node.js官网&#xff1a; 打开Node.js的官方网站&#xff08;如&#xff1a;https://nodejs.org/zh-cn/download/&#xff09;。 下载安装包&#xff1a; 根据你的操作系统选择对应的Node…...

SpringMVC系列四: Rest-优雅的url请求风格

Rest请求 &#x1f49e;Rest基本介绍&#x1f49e;Rest风格的url-完成增删改查需求说明代码实现HiddenHttpMethodFilter机制注意事项和细节 &#x1f49e;课后作业 上一讲, 我们学习的是SpringMVC系列三: Postman(接口测试工具) 现在打开springmvc项目 &#x1f49e;Rest基本介…...

Hexo 搭建个人博客(ubuntu20.04)

1 安装 Nodejs 和 npm 首先登录NodeSource官网&#xff1a; Nodesource Node.js DEB 按照提示安装最新的 Node.js 及其配套版本的 npm。 &#xff08;1&#xff09;以 sudo 用户身份运行下面的命令&#xff0c;下载并执行 NodeSource 安装脚本&#xff1a; sudo curl -fsSL…...

【论文阅读】-- Attribute-Aware RBFs:使用 RT Core 范围查询交互式可视化时间序列颗粒体积

Attribute-Aware RBFs: Interactive Visualization of Time Series Particle Volumes Using RT Core Range Queries 摘要1 引言2 相关工作2.1 粒子体渲染2.2 RT核心方法 3 渲染彩色时间序列粒子体积3.1 场重构3.1.1 密度场 Φ3.1.2 属性字段 θ3.1.3 优化场重建 3.2 树结构构建…...

A类IP介绍

1&#xff09;A类ip给谁用&#xff1a; 给广域网用&#xff0c;公网ip使用A类地址&#xff0c;作为公网ip时&#xff0c;Ip地址是全球唯一的。 2&#xff09;基本介绍 ip地址范围 - 理论范围 0.0.0.0 ~127.255.255.255&#xff1a;00000000 00000000 00000000 00000000 ~ 0111…...

HTML5基本语法

文章目录 HTML5基本语法一、基础标签1、分级标题2、段标签3、换行及水平线标签4、文本格式标签 二、图片标签1、格式2、属性介绍 三、音频标签1、格式2、属性介绍 四、视频标签1、格式2、属性介绍 五、链接标签1、格式2、显示特点3、属性介绍4、补充&#xff08;空链接&#xf…...

正则表达式常用表示

视频教程&#xff1a;10分钟快速掌握正则表达式 正则表达式在线测试工具&#xff08;亲测好用&#xff09;&#xff1a;测试工具 正则表达式常用表示 限定符 a*&#xff1a;a出现0次或多次a&#xff1a;a出现1次或多次a?&#xff1a;a出现0次或1次a{6}&#xff1a;a出现6次a…...

【OpenHarmony4.1 之 U-Boot 2024.07源码深度解析】007 - evb-rk3568_defconfig 配置编译全过程

【OpenHarmony4.1 之 U-Boot 2024.07源码深度解析】007 - evb-rk3568_defconfig 配置编译全过程 一、编译后目录列表二、make distclean三、生成.config文件:make V=1 ARCH=arm64 CROSS_COMPILE=aarch64-linux-gnu- evb-rk3568_defconfig四、开始编译:CROSS_COMPILE=aarch64-…...

基于算法竞赛的c++编程(28)结构体的进阶应用

结构体的嵌套与复杂数据组织 在C中&#xff0c;结构体可以嵌套使用&#xff0c;形成更复杂的数据结构。例如&#xff0c;可以通过嵌套结构体描述多层级数据关系&#xff1a; struct Address {string city;string street;int zipCode; };struct Employee {string name;int id;…...

龙虎榜——20250610

上证指数放量收阴线&#xff0c;个股多数下跌&#xff0c;盘中受消息影响大幅波动。 深证指数放量收阴线形成顶分型&#xff0c;指数短线有调整的需求&#xff0c;大概需要一两天。 2025年6月10日龙虎榜行业方向分析 1. 金融科技 代表标的&#xff1a;御银股份、雄帝科技 驱动…...

VB.net复制Ntag213卡写入UID

本示例使用的发卡器&#xff1a;https://item.taobao.com/item.htm?ftt&id615391857885 一、读取旧Ntag卡的UID和数据 Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click轻松读卡技术支持:网站:Dim i, j As IntegerDim cardidhex, …...

基于ASP.NET+ SQL Server实现(Web)医院信息管理系统

医院信息管理系统 1. 课程设计内容 在 visual studio 2017 平台上&#xff0c;开发一个“医院信息管理系统”Web 程序。 2. 课程设计目的 综合运用 c#.net 知识&#xff0c;在 vs 2017 平台上&#xff0c;进行 ASP.NET 应用程序和简易网站的开发&#xff1b;初步熟悉开发一…...

ffmpeg(四):滤镜命令

FFmpeg 的滤镜命令是用于音视频处理中的强大工具&#xff0c;可以完成剪裁、缩放、加水印、调色、合成、旋转、模糊、叠加字幕等复杂的操作。其核心语法格式一般如下&#xff1a; ffmpeg -i input.mp4 -vf "滤镜参数" output.mp4或者带音频滤镜&#xff1a; ffmpeg…...

学习STC51单片机32(芯片为STC89C52RCRC)OLED显示屏2

每日一言 今天的每一份坚持&#xff0c;都是在为未来积攒底气。 案例&#xff1a;OLED显示一个A 这边观察到一个点&#xff0c;怎么雪花了就是都是乱七八糟的占满了屏幕。。 解释 &#xff1a; 如果代码里信号切换太快&#xff08;比如 SDA 刚变&#xff0c;SCL 立刻变&#…...

iview框架主题色的应用

1.下载 less要使用3.0.0以下的版本 npm install less2.7.3 npm install less-loader4.0.52./src/config/theme.js文件 module.exports {yellow: {theme-color: #FDCE04},blue: {theme-color: #547CE7} }在sass中使用theme配置的颜色主题&#xff0c;无需引入&#xff0c;直接可…...

【Android】Android 开发 ADB 常用指令

查看当前连接的设备 adb devices 连接设备 adb connect 设备IP 断开已连接的设备 adb disconnect 设备IP 安装应用 adb install 安装包的路径 卸载应用 adb uninstall 应用包名 查看已安装的应用包名 adb shell pm list packages 查看已安装的第三方应用包名 adb shell pm list…...

淘宝扭蛋机小程序系统开发:打造互动性强的购物平台

淘宝扭蛋机小程序系统的开发&#xff0c;旨在打造一个互动性强的购物平台&#xff0c;让用户在购物的同时&#xff0c;能够享受到更多的乐趣和惊喜。 淘宝扭蛋机小程序系统拥有丰富的互动功能。用户可以通过虚拟摇杆操作扭蛋机&#xff0c;实现旋转、抽拉等动作&#xff0c;增…...

Oracle11g安装包

Oracle 11g安装包 适用于windows系统&#xff0c;64位 下载路径 oracle 11g 安装包...