MySQL之用户管理

---

一、MySQL中6种常见的约束

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

1.主键和外键的关系

创建外键约束作用（误删、修改），保证数据的完整性和一致性
主键表和外键表的理解：

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

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

2.主表和从表的创建

create table test01 (hobid int(4),hobname varchar(40));
#创建主表test01
create table test02 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));
#创建从表test02
alter table test01 add constraint PK_hobid primary key(hobid);
#为主表test01添加一个主键约束。主键名为PK_hobid
alter table test02 add constraint FK_hobid foreign key(hobid) references test01(hobid);
#为从表test02添加外键，并将test02表的hobid与test01表的hobid字段建立外键关联
show create table test02;
#查询表语句结构查看外键关联
插入数据的时候，需要先插入主表，再插入从表，不然会报错
删除数据的时候，需要先删除从表的记录，再删除主表的记录

创建主表test01

创建从表test02

分别为主表test01添加主键，从表test02添加外键，并将主从表中的hobid字段建立外键关联


查看外键关联


插入数据时，要先主表，再从表

删除数据时，要先从表，再主表，也就是说删除主键表时，必须先删除其他与之关联的表

查看和删除外键约束
如果要删除外键约束字段先删除外键约束，再删除外键名

---

二、数据表的操作

1.复制格式，然后备份内容

create table info1 like info;   #复制格式，通过LIKE方法，复制info结构生成info1
insert into info1 select * from info;     #备份内容

复制表

查看表结构，格式与info一样

2.克隆表

将数据表的数据记录生成到新的表中

create table info2 (select * from info);   #复制info表数据到info2中
show create table info2\G    #查看表的表结构、索引等信息，对比info表

可以发现这种方法只会复制表的结构和数据，但是约束不会复制

3.清空表

以下两种方法，均不会删除表的结构，只会删除表的内容

3.1方法一：delete

delete清空表后，返回的结果内有删除的记录条目；
delete工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用delete from 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录

delete from info;   #删除表中的数据

3.2方法二：truncate

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

trun table info;

3.3删除类型（drop、delete、truncate）

drop table table_name

• 属于DDL
• 不可回滚（无法恢复）
• 不可带where
• 表内容和结构删除
• 删除速度快

truncate table table_name

• 属于DDL
• 不可回滚
• 不可带where
• 表内容删除
  PS：truncate 操作过程是：先保留要删除的表的表结构，然后删除表，再重建这张表的表结构，所以，例如aotu_increment 自增长的这种属性，会从头开始重新记录
• 删除速度快

delete from table_name

• 属于DML
• 可回滚
• 可带where
• 表结构在，表内容要看where执行的情况（执行更为麻烦）
• 删除速度慢，需要逐行删除

速度：drop > truncate > delete
不再需要一张表的时候，用drop
想删除部分数据行的时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

4.临时表

临时表创建成功之后，使用show tables 命令是看不到创建的临时表的，临时表会在连接退出后被销毁
如果在退出连接之前，也可以执行增删改查等操作，比如drop table 语句手动直接删除临时表
PS：无法创建外键

create temporary table 表名 (字段1 数据类型，字段2 数据类型[,...] [,primary key (主键名)]);

SQL写入→数据表中→先把数据保存在内存中→写入到磁盘
insert into info→info这张表，会先复制一份表数据到内存里面，给我们进行修改→敲完回车之后，确定提交了，才会写入数据表中→再保存在磁盘里面

create temporary table info→只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉

示例：
create temporary table test (id int(4) zerofill primary key auto_increment,name varchar(10) not null,cardid int(18) not null unique key,hobby varchar(50));

---

三、数据库用户管理

1.查看用户

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

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

2.新建用户

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

create user 'ngs'@'localhost' identified by '123456';
#使用明文密码创建用户，且用户仅在本地登录
select password('abc123');
create user 'sen'@'localhost' identified by password '密文'
#先使用select生成'abc123'的密文，然后使用密文进行创建用户，用户仅在本地才能登录

3.重命名用户

rename user 'sen'@'localhost' to 'na'@'localhost';
#将sen用户更名为na用户

4.删除用户

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

四、用户密码管理

1.修改当前用户的密码

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

2.修改其他用户的密码

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

3.忘记root密码的解决办法

如果忘记了普通用户的密码，可以进入root用户进行修改普通用户的密码

如果忘了root密码
修改/etc/my.cnf 配置文件，设置免密登录mysql的root用户
vim /etc/my.cnf
[mysqld]
skip-grant-tables   #添加，使得登录mysql不使用授权表

systemctl restart mysqld       #重启mysql
mysql       #直接输入mysql
updata mysql.user set authentication_string = password('ngs123.') where user='root';     #将密码修改为ngs123.
flush privileges;    #刷新
最后重新登录，登陆前需要把配置文件中添加的skip-grant-tables删除，重启mysql

---

五、数据库授权

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

1.查看权限

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

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

格式：show grants for 用户名@来源地址;

show grants for ngs@localhost;    #查看ngs用户在本机登录的权限

2.授权

格式：grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址'[identified by '密码'];

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

我们登录之前创建的普通用户ngs

我们可以通过root用户为ngs用户赋权

great select on nba1.* to 'ngs'@'localhost' identified by 'ngs123';
#给ngs用户在本机添加可以查看nba1数据库下面所有表的权限，且修改ngs的登录密码为ngs123

再次登录到ngs用户，可以查看nba1库，并且可以查看该库中表的数据

3.取消授权

格式：revoke 权限列表 on 数据库名.表名 from '用户名'@'来源地址';

revoke select on nba1.* from 'ngs'@'localhost';
#取消ngs用户在nba1库中的查看权限

---

六、实现Navicat工具连接Linux中的MySQL数据库

要使用navicat工具远程连接数据库就需要给与MySQL中的用户权限

具体操作如下

首先我们使用root用户登录数据库，执行以下命令授予ngs用户权限
grant all privileges on *.* to 'ngs'@'%' identified by 'ngs123'
#给与ngs用户所有数据库中的所有表的所有权限，授予通过任何ip访问的用户，密码为ngs123
flush privileges     #刷新

然后打开navicat工具

填写一下内容

可以点击连接测试测试一下，然后点击确定

此时我们就可以使用navicat工具更改和查询

---

总结