开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL之用户管理 -> 正文阅读

[大数据]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工具更改和查询