MySQL数据库管理基本操作（二）

目录

一、数据表操作

1.1、克隆表，然后复制数据

 1.2、克隆表数据的同时创建表

1.3、清空表

delete清空表

truncate清空表

1.4 drop、truncate、delete区别

1.5、临时表

二、MySQL中6种常见的约束/规则

2.1、主键和外键的关系

2.2、主键和外键表的创建

三、数据库用户管理

3.1、查看用户

 3.2、新建用户

 3.3、重命名用户

 3.4、删除用户

四、用户密码管理

4.1、修改当前用户的密码

4.2、修改其他用户的密码

4.3、忘记root密码的解决方法

 五、数据库授权

5.1、查看权限

5.2、授权

5.3、取消授权

5.4、查看授权情况

---

一、数据表操作

1.1、克隆表，然后复制数据

在创建数据表的时候，我们有的时候需要使用另外一个表的结构或数据，如果一个个添加，过于繁琐，这时我们可以使用克隆。

我们这里有一张初始的表 

create table test1 like test;
#通过like方法，复制test的表结构，生成test1表
insert into test1 select * from test；
#将test表的数据，备份到test1表中

 1.2、克隆表数据的同时创建表

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

1.3、清空表

delete清空表

delete from test2；
#清空表后，返回的结果内有删除的记录条目
#delete是一行一行进行删除记录数据的
#delete清空表后，自增长字段会按之前最大的记录自增

truncate清空表

truncate table test；
#truncate 清空表后，没有返回被删除的条目
#其工作时是将表结构按原样重新建立
#因此其清空速度比delete快
#truncate清空后，自增长字段会重新开始

1.4 drop、truncate、delete区别

当不在需要一张表时，用drop
想删除部分数据行时，用delete，并带上where
删除数据保留表时用truncate

drop table 表名    属于DDL类型，不可进行回滚，无法恢复，不可带where，删除表内容和结构，删除速度最快
truncate table 表名    属于DDL类型 ，不可进行回滚，不可带where，表内容删除，删除速度快
delete from 表名    属于DML类型，可回滚，可带where，表结构在，表内容要看where执行情况

1.5、临时表

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

##添加临时表test3
create temporary table test3 (
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 test3 values(1,'zhangsan',123456789,'watch a film');    
##查看当前表中所有数据
select * from test3;
##退出数据库
quit                    
 
##重新登录后进行查看        
mysql -u root -p
##查看之前创建的临时表中所有数据，发现已经被自动销毁
select * from test3;        

 

 但是我们退出，再进入数据库，这张临时表就没了

二、MySQL中6种常见的约束/规则

主键约束（primary key）
外键约束（foreign key）:如果同一个属性字段x在表一中是主键，而在表二中不是主主键，则字段称为表二的外键
非空约束（not null）
唯一性约束（unique key）:唯一，可为空，但空值只允许出现一次
默认值约束（default）:当为空时，默认值
自增约束（auto_increment）:随着记录增加，基于最新的记录的id，进行自加1的增长

2.1、主键和外键的关系

创建外键约束作用（误删，修改），保证数据的完整性和一致性。

主键表和外键表的理解：

• 以公共关键字作为主键的表为主键表（父表，主表）
• 以公共关键字作为外键的表为外键表（从表，外表）

注意：以外键关联的主表的字段必须设置主键，要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型，字符长度和约束（不包括主键约束）。

2.2、主键和外键表的创建

create table test1 (hobid int(4),hobname varchar(50));
#创建一个主表，先不定义主键，也可以定义好
 
create table test2 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));
#创建从表
 
alter table test1 add constraint pk_hobid primary key(hobid);
#添加主表test1一个主键约束，主键的名字以pk开头
 
alter table test2 add constraint fk_hobid foreign key(hobid) references test1(hobid);
#为从表test2添加外键，并将test2表的hobid字段和test1字段简历外键关联，外键名建议以"fk_"开头
references:引用（主表的主键）
 
show create table  test2;
#使用查询表语句结构查看外键关联
 
#插入数据的时候，需要先插入主表，再插入从表，不然会报错。
#删除数据的时候，需要先删除从表记录，再删除主表的记录。

 

insert into test1 values(1,'y');
#为test1添加数据
 
insert into test2(name,age,hobid) values('yy',11,1);
#为test2添加数据

 

#删表得先删从表，再删主表
drop table test2;
drop table test1;

 

三、数据库用户管理

3.1、查看用户

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

use mysql
select user,host from user;
#先进入到mysql库中，再使用select查看user和host字段的内容
 
或
select user,host from mysql.user;
#直接查看mysql库下面的user表的user和host表

 3.2、新建用户

格式: create user ‘用户名’@‘来源地址’ 【IDENTIFIED BY [PASSWORD] ‘密码’ 】；

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

create user 'yy'@'localhost' identified by '123456';
#使用明文密码创建用户，且用户仅在本地登陆
 
select password('123456')
#生成‘123456’的密文
create user 'zz'@'localhost' identified by '6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
#使用密文密码创建用户，且用户仅在本地登陆

 

 3.3、重命名用户

rename user 'yy'@'localhost' to 'yyy'@'localhost';
#将yy用户更名为yyy用户

 3.4、删除用户

drop user 'zz'@'localhost';
#删除用户zz

四、用户密码管理

4.1、修改当前用户的密码

set password = password('新密码');
#修改当前用户的密码
 
无论使用哪个用户，都可以使用此方法修改自己的密码。

4.2、修改其他用户的密码

set password for 'yyy'@'localhost' = password('新密码');
#修改用户ydq的密码为‘abc123’（需要在root用户下修改别的用户）

4.3、忘记root密码的解决方法

如果忘记了普通用户的密码，可以进入root用户进行修改普通用户的密码
 
如果忘了root用户的密码呢？
修改/etc/my.cnf 配置文件，设置免密登录mysql的root用户
vim /etc/my.cnf 
在[mysqld]下
skip-grant-tables  #添加，使得登录mysql不使用授权表
 
systemctl restart mysqld
mysql
update mysql.user set authentication_string = password('123456') where user='root';
flush privileges; 
 
最后重新登录，登录前需要把配置文件/etc/my.conf 文件里的ship-grant-tables 删除，重启mysql服务。

直接mysql进入数据库 

 五、数据库授权

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

5.1、查看权限

每个用户都有一个默认的权限，USAGE权限

USAGE权限只能用于数据库登陆，不难执行任何操作，USAGE权限不能被回收，所以REVOKE不难删除用户。

格式：
show grants for 用户名@来源地址;
 
show grants for yyy@localhost; #查看yyy用户在本机登陆的权限
show grants for yyy@192.168.130.15; #查看yyy用户在192.168.130.15上登陆的权限

5.2、授权

格式: grant 权限列表 on 数据库名.表名 to ‘用户名’@‘来源地址’ [identified by ‘密码’ ]；
 
#权限列表: 用于列出权限使用的各种数据库操作，以逗号进行分隔，如“select ，insert，update”。如果使用“all” 表示所有权限，可授权执行的任何操作。
#数据库名.表名: 用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如：使用"kgc.*"表示授权操作的对象为kgc数据库中的所有的表。
‘用户名@来源地址’ : 用于指定用户名称和允许访问的客户机地址，就是谁能连接，能从哪里连接，来源地址可#用是域名，ip，还可以使用%通配符，表示某个区域网段内的所有地址，如“%.ydq.com”、"20.0.0.%"等。
#indentified by : 用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“identified by” 部分，则用户的密码将为空。

 我们登陆上面创建的yyy用户

我们切换为root用户为yyy赋权 

grant select on test.* to 'yyy'@'localhost' identified by '123456';
#允许用户yyy在本地查询test数据库中所有表的数据记录，但禁止查询其它数据库中的表的记录。

 我们再切换为yyy用户

5.3、取消授权

revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘来源地址’;
 
revoke select on test.* from 'yyy'@'localhost';

5.4、查看授权情况

show grants;
#默认查看root用户权限
 
show grant for 'yyy'@'localhost';
#查看yyy用户权限