数据库（mysql）之用户管理

目录

一、mysql中常见的的约束/规则

1、主键和外键的关系

2、主键和外键表的创建

二、数据表的操作

1、克隆表

 2、清空表 

2.1方法一：delete

2.2方法二：truncate

3、临时表

三、数据库用户管理

1、新建用户

1、使用明文创建用户

2、查看用户信息

3、重命名用户

4、删除用户

三、密码管理

1、修改当前用户密码

2、修改其他用户密码

3、root密码遗忘 

四、数据库授权

1、查看权限

2、授权用户

 3.取消授权

---

一、mysql中常见的的约束/规则

1.主键约束（primary key）

2. 外键约束（foreign key）： 如果同一个属性字段在表一中为主键，而在表二中不是主键，则字段称为表二的外键。 

3. 非空约束（not null）

4. 唯一性约束（unique key）: 唯一，可为空，但空值 值允许出现一次。

5. 默认值约束（defaulst）： 当为空时，默认值

6. 自增长约束（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 复制的新表 like 被复制的表;   复制表的格式及内容

格式：insert into 新表 select * from 复制的表;  复制表的格式，在插入内容

insert into test1 select * from test;  插入内容 

create table test1 like 召唤师峡谷;   #复制 test 表的结构为 test1

insert into test1 select * from 召唤师峡谷;  #复制 test 表的内容插入到 test1

 

 

复制表的格式及内容

create table test2 (select * from 召唤师峡谷);  #复制整个召唤师峡谷表格 ，包括内容 并新建表

 克隆后表的索引结构还是不一样 

 小结：克隆数据表只能用来备份数据，无法完全代替其功能

 2、清空表 

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

2.1方法一：delete

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

 自增长

在某张表中，存在一个id列（整数），在添加记录的时候，该列从1开始，自动增长。

字段名 整型 primary key auto_increment

 delete from   ;   #删除表中的数据

2.2方法二：truncate

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

 drop、delete、truncate的区别

不在需要一张表的时候，用 drop

想删除部分数据行时候，用 delete，并且带身上 where 语句

保留表而删除所有数据的时候用 truncate。

速度：drop > truncate >delete（最安全）

3、临时表

临时是在内存中创建的，当退出数据库后，临时表将会消失，在没有退出之前，可以像正常表一样进行操作。主要是用来进行测试的。

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、新建用户

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

1、使用明文创建用户

mysql -u root -p  登录数据库

create user 'mh'@'localhost' identified by '123456';  创建登录数据库的新用户
quit  退出
mysql -u xg -p  用新用户登录

 

2、查看用户信息

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

use mysql;   #使用mysql库 
select user from user\G;     或者 select user,host from user;  查看用户

 

3、重命名用户

rename user 'mh'@'localhost' to 'gg'@'localhost';
#将登录数据库的用户 mh改名为 gg用户

  

4、删除用户

drop user 'gg'@'localhost';  删除登录数据库的gg用户

 

三、密码管理

1、修改当前用户密码

set password = password('456789');

 

2、修改其他用户密码

set password for 'wxg'@'localhost' = password('456789');

 修改mh用户的登录密码

 

 新密码登录成功

3、root密码遗忘 

修改配置文件，添加配置，免密登录MySQL

修改/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;   给root用户设置登录密码

登录前需要把配置文件/etc/my.conf  文件里的 skip-grant-tables 注释或删除，重启mysql服务。

 

 

 

四、数据库授权

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

1、查看权限

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

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

 

2、授权用户

格式: grant 权限列表 on 数据库名.表名 to ‘用户名’@‘来源地址’ [identified by ‘密码’ ]；

数据库名.表名 : 用于指定授权操作的数据库和表的名称，其中可以使用通配符 "*"。例如：使用"ku.*" 表示授权操作的对象为ku数据库中的所有的表。
‘用户名@来源地址’ : 用于指定用户名称和允许访问的客户机地址，就是谁能连接，能从哪里连接，来源地址可用是域名，ip，还可以使用%通配符，表示某个区域网段内的所有地址，如“%.mh.com”、"20.0.0.%"等。
indentified by : 用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“identified by” 部分，则用户的密码将为空。

 

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

 

  再次登录到hao用户，发现可以查看到英雄联盟库中表的数据

 3.取消授权

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

revoke select on 英雄联盟.* from 'hao'@'localhost';  取消查看的权限

revoke all on *.* from 'hao'@'localhost';   取消所有权限