mysql之数据表高级操作

一、克隆/复制一个表

方法一：

create table 新表名 like 复制的表名;
复制格式，能够复制表的格式到新表，但是没有内容
insert into 新表名 select * from 复制的表名;
复制原表内容到新表
在这里插入图片描述

方法二：

create table 新表名 (select * from 复制的表名)
数据结构和数据一起复制过来了，克隆
在这里插入图片描述

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

2.1 方法一：

delete from CLASS;
#DELETE清空表后，返回的结果内有删除的记录条目；
工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM
删除所有记录后，在此添加的记录会从原来最大的记录id后面继续自增写入数据
在这里插入图片描述

2.2 方法二：

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

2.3 drop、truncate、delete对比

2.3.1 drop table table_name
1.属于DDL
2.不可回滚（无法恢复）
3.不可带where表内容和结构删除
4.删除速度快
2.3.2 truncate table table_name
1.属于DDL
2.不可回滚
3.不可带where
4.表内容删除
5.删除速度快
2.3.3 delete from table_name
1.属于DML
2.可回滚（可回复）
3.可带where表结构在，表内容要看where执行的情况
4.删除速度慢，需要逐行删除

三、创建临时表

##添加临时表CLASS2
create temporary table CLASS2 (
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 test03 values(1,‘zhangsan’,123456789,‘watch a film’);
##查看当前表中所有数据
select * from CLASS2;
##退出数据库
quit

##重新登录后进行查看
mysql -u root -p
##查看之前创建的临时表中所有数据，发现已经被自动销毁
select * from CLASS2;

四、用户管理

4.1 新建用户

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

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

4.1.1 使用明文创建用户
CREATE USER ‘lic’@‘localhost’ IDENTIFIED BY ‘123456’;
在这里插入图片描述

4.1.2 使用密文创建用户
select password(‘123456’);
create user ‘lic’@‘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 ‘dzh1’@‘lockhost’ to ‘XDJ’@‘lockhost’;
select user,authentication_string,host from user;
在这里插入图片描述

4.4 删除用户
drop user 'XDJ '@‘lockhost’;
select user,authentication_string,host from user;
在这里插入图片描述

4.5 修改当前登录用户密码

set password = password(‘654321’);
在这里插入图片描述

4.6 修改其他用户密码

set password for 'dzh@‘llocalhost’ = password(‘123321’);

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(‘123456’) where user=‘root’;