MySQL数据库管理（2）

目录

一. 数据表高级操作

1. 克隆表

2. 清空表

2.1  drop table 表名;

2.2 truncate table 表名;

2.3 delete from 表名;

3. 临时表

二. 用户管理

1. 创建新用户

1.1 使用明文创建

1.2 使用密文创建

2. 查看用户信息

3. 用户重命名

4. 删除用户

三. 密码管理

1. 修改当前用户密码

2.  修改指定用户密码

3. 忘记密码

3.1 修改配置文件，设置免密登录

3.2 设置密码，不使用免密 使用密码登录

四. 数据库用户授权

1. 授权

2. 数据库授权

3. 远程登录授权

4. 撤销权限

---

一. 数据表高级操作

1. 克隆表

create table test2 like test1;
#通过like方法，复制test1的表结构，生成test2表

insert into test2 select * from test1;
#将test1表的数据，备份到test2表中

create table test4 (select * from test2);
#复制xy01表的数据，到xy04中

select * from test4;

show create table test1\G
#获取数据表的结构、索引等信息

查看数据表结构，索引信息等

2. 清空表

2.1  drop table 表名;

• 不需要表的时候使用
• 属于DDL，不可恢复，不带where，表的内容和结构会一起删除，速度快

2.2 truncate table 表名;

• 保留表而删除所有数据的时候使用
• 属于DDL，不可恢复，不带where，表内容删除结构不会，速度快

2.3 delete from 表名;

• 想删除部分数据时使用，并带上where
• 属于DML，可恢复，可带where，表结构在，表内容看where执行的情况，速度慢，要逐行删除

3. 临时表

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

#创建临时表test5
mysql> create temporary table test5(
    -> id int(4) zerofill primary key auto_increment,
    -> name varchar(10) not null,
    -> cardid int(18) not null unique key,
    -> hobby varchar(50));

#在临时表test5中添加数据
insert into test5 values (1,'zhangsan','123456','running');

#查看表中数据
select * from test5;

#退出数据库
quit

#重新登录后进行查看
mysql -uroot -p

#查看创建的临时的表test5，会发现没了
select * from  test5;

二. 用户管理

1. 创建新用户

CREATE USER '用户名'@'来源地址'[IDENTIFIED BY [PASSWORD] '密码'];

'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主键上登录，可使用IP地址，网段，主机名的形式，本地用户可用localhost，允许任意主机登录

1.1 使用明文创建

create user 'cq'@'localhost' identified by 'abc123';

使用明文密码，直接输入密码，插入到数据库时MySQL将自动加密

1.2 使用密文创建

select password('abc123');

create user 'eb'@'localhost' identified by PASSWORD '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';

使用密文创建需先使用SELECT PASSWORD ('密码')来获取密文，再在语句中添加PASSWORD '密文'；
若省略"IDENTIFIED BY"部分，用户密码将为空（不建议使用）

2. 查看用户信息

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

#使用mysql库
use mysql;

select user from user\G

3. 用户重命名

rename user 'eb'@'localhost' to 'oz'@'localhost';

4. 删除用户

drop user 'oz'@'localhost';

三. 密码管理

1. 修改当前用户密码

2.  修改指定用户密码

3. 忘记密码

3.1 修改配置文件，设置免密登录

vim /etc/my.cnf
#添加此行
skip-grant-tables

#重启服务
systemctl restart mysqld.service

重启服务

配置免密登录后可不用密码直接登录

3.2 设置密码，不使用免密 使用密码登录

update mysql.user set authentication_string = password('abc123') where user='root';

flush privileges;  #刷新 

四. 数据库用户授权

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

1. 授权

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

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

权限列表

privilege权限
insert	插入数据
select	查询数据
update	更新表的数据
delete	删除表中数据
create	创建库，表
drop	删除库，表
refernces
index	建立索引
alter	更改表属性
create temp orary tableslock tables	锁表
execute
create view	创建视图
show view	显示视图
create routine	创建存储过程
alter routine	修改存储过程
event	事件
trigger on	创建触发器

2. 数据库授权

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

切换用户进行验证

3. 远程登录授权

grant all on *.* to 'cq'@'%' identified by '123456';

4. 撤销权限

revoke select on ky19.* from cq@localhost;

删除权限

查看用户权限

切换用户再次访问，没有权限无法访问