帝国做视频网站,在线做行测的网站,网站建设包括什么科目,服装品牌网站怎么做1、数据表高级操作
#xff08;1#xff09;克隆表 方法一#xff1a; create table 新表名 like 旧表名; #克隆表结构 insert into 新表名 select * from 旧表名; #克隆表数据 #此方法能保证 新表的表结构、表数据 跟旧表都是一致的 方法二#x…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)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 | ##自增长数据为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)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 | ##自增长数据仍为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)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)mysql3创建临时表 临时表创建成功之后使用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 id2;
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)mysql2、外键约束保证数据的完整性和一致性 外键的定义如果同一个属性字段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)
) ENGINEInnoDB DEFAULT CHARSETutf8
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)
) ENGINEInnoDB DEFAULT CHARSETutf8
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 id1; delete from class where nameJAVA; 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 nameJAVA; ##想要删除主从表中的数据必须先删除从表中外键约束的内容再删除主键内容负责无法删除
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 id1;
Query OK, 1 row affected (0.00 sec)mysql delete from class where nameJAVA;
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)
) ENGINEInnoDB DEFAULT CHARSETutf8
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)
) ENGINEInnoDB DEFAULT CHARSETutf8
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 ##外键被删除
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)mysql3、数据库用户管理
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)mysql2查看用户信息 #创建后的用户保存在 mysql 数据库的 user 表里
USE mysql;
SELECT User,authentication_string,Host from user;
#查看当前登录用户
select user(); [rootzx1 ~]# 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() |
--------------
| zx20.0.0.10 |
--------------
1 row in set (0.00 sec)mysql3重命名用户 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 zx1localhost;
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)mysql4删除用户 DROP USER 用户名来源地址; mysql rename user zx% to zx1localhost;
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 zx1localhost;
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 rootlocalhost IDENTIFIED BY abc123; 方法一
mysql select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
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
[rootzx1 ~]# 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 rootlocalhost identified by abc123;
Query OK, 0 rows affected (0.00 sec)mysql quit
Bye
[rootzx1 ~]# 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.mysql6修改其他用户密码 SET PASSWORD FOR user1localhost 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 rootlocalhost 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)mysql7忘记 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 userroot; FLUSH PRIVILEGES; quit mysql -u root -pabc123 [rootzx1 ~]# vim /etc/my.cnf
[rootzx1 ~]# systemctl restart mysqld
[rootzx1 ~]# 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 userroot;
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
[rootzx1 ~]# 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用户只有登录权限
[rootzx1 ~]# 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用户授权
[rootzx1 ~]# 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
[rootzx1 ~]# 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() |
--------------
| zxlocalhost |
--------------
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 id1; ##只有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 id1 at line 1
mysql
2查看权限 SHOW GRANTS FOR 用户名来源地址; [rootzx1 ~]# 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)mysql3撤销权限 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
