MySQL数据库用户管理与用户授权

---

一、克隆/复制一个表

1.1 方法一

​create table 新表名 like 复制的表名;  ​
​复制格式，能够复制表的格式到新表，但是没有内容​
​insert into 新表名 select * from 复制的表名; ​
​复制原表内容到新表

原始数据表和表结构

新表的表名 like 要复制的旧表表名

insert into 新表 (select * from 源表); #给新表插入数据

1.2 方法二

create table 新表名 (select * from 复制的表名)
​数据结构和数据一起复制过来了，克隆

二、清空表，删除表内所有数据

2.1 方法一

delete from demo;

注意：此表是克隆表的第一个方法复制新表的，如用克隆表第二种方法，id直接从0开始。

2.2 方法二

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

2.3 drop、truncate、delete对比

①.drop table name

1. 属于DDL
2. 不可回滚（无法恢复）
3. 不可带where
4. 表内容和结构删除
5. 删除速度快

②.truncate table table_name

1. 属于DDL
2. 不可回滚
3. 不可带where
4. 表内容删除
5. 删除速度快

③.delete from table_name

6. 属于DML
7. 可回滚（可回复）
8. 可带where
9. 表结构在，表内容要看where执行的情况
10. 删除速度慢，需要逐行删除

小结：

1. 不再需要一张表的时候用drop
2. 想删除部分数据行的时候用delete，并且带上where子句
3. 保留表而删除所有数据的时候用truncate
4. 删除速度：drop>truncate> delete
5. 安全性 delete 最好

三、创建临时的表

##添加临时表demo3
create temporary table demo3 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
 
## 查看当前库中所有表
show tables; 
##在临时表中添加数据
insert into demo3 values(1,'zhangsan',123456789,'watch a film'); 
##查看当前表中所有数据
select * from demo3;
##退出数据库
quit      
 
##重新登录后进行查看  
mysql -u root -p
##查看之前创建的临时表中所有数据，发现已经被自动销毁
select * from demo3; 

四、用户管理

4.1 新建用户

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

4.1-1 使用明文创建用户

CREATE USER 'myb'@'localhost' IDENTIFIED BY '123456';

4.1-2 使用密文创建用户

select password('123456');
create user 'mayinbin'@'localhost' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
 
 
查看用户信息
创建后的用户保存在mysql 数据库的user表里
use mysql
select user,authentication_string,host from user;

4.2 查看用户信息

创建后的用户保存在 mysql 数据库的 user 表里

use mysql;
select User,authentication_string,Host from user;

4.3 重命名用户

rename user 'mayinbin'@'localhost' to 'zhangsan'@'localhost';
select user,authentication_string,host from user;

4.4 删除用户

drop user 'zhangsan'@'localhost';
select user,authentication_string,host from user;

4.5 修改当前用户登录密码

set password = password('654321');

4.6 修改其他用户的密码

set password for 'myb'@'localhost' = password('654321');

4.7 忘记root密码的解决措施

4.7-1 修改配置文件，添加配置，使登录mysql不使用授权表

vim /etc/my.cnf
#添加此行
skip-grant-tables
 
systemctl restart mysqld.service 
mysql

4.7-2 使用update修改root密码，刷新数据库

update mysql.user set authentication_string = password('001122') where user='root';
 
flush privileges;

五、用户授权

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

5.1 授权

GRANT 权限列表 ON 数据库名/表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

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

授权列表

权限	功能
select	查询数据
insert	插入数据
update	更新数据
delete	删除数据
create	创建库、表
drop	删除库、表
index	建立索引
alter	更改表属性
event	事件
trigger on	创建触发器

5.1-1 数据库授权

show grants for myb@localhost;
#查看用户权限

指定用户可以查看哪个数据库/表，别的数据库无权访问

grant select on SCHOOL.* to myb@localhost;
#用户myb只有SCHOOL库下所有表的查询权限

切换用户进行验证

5.1-2 撤销权限

revoke select on mayinbin.* from myb@localhost;

六、总结

当不在需要一张表时，用drop；想删除部分数据行时，用delete，并带上where；删除数据保留表时用truncate
因克隆表时无法克隆索引等信息，所以克隆表只能用于临时备份数据，在实际生产中也仅用于备份数据，防止误删除数据，造成不必要的损失
在遗忘MySQL的root密码时，更改配置文件并更改密码后，记得要将配置文件还原，否则其它用户可以直接免密登录。