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

主键约束（primary key）

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

非空约束（not null）

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

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

自增约束（auto_increment）： 随着记录增加，基于最新的记录的id，进行自加1的增长。

1、主键和外键的关系

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

主键表和外键表的理解:

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

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

2、主键和外键表的创建

create table test01 (hobid int(4),hobname varchar(50));
#创建一个主表，先不定义主键，也可以定义好

create table test02 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int (4));
#创建从表

alter table test01 add constraint pk_hobid primary key(hobid);
#添加主表test01一个主键约束，主键的名字以pk开头

alter table test02 add constraint fk_hobid foreign key(hobid) references test01(hobid);
#为从表test02添加外键，并将test02表的hobid字段和test01字段建立外键关联，外键名建议以"FK_"开头
references ：引用（主表的主键）

show create table test02;
#使用查询表语句结构查看外键关联

插入数据的时候。需要先插入主表，在插入从表，不然会报错。
删除数据的时候，需要先删除从表的记录，在删除主表的记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

二、数据表的操作

1、克隆表

格式：create table 创建的表 like 复制的表;

#复制表的结构

格式：insert into 新表 select * from 复制的表;

#复制表的内容

create table ky20_new like ky20;   #复制ky20表的结构为ky20_new

insert into ky20_new select * from ky20;  #复制ky20表的内容插入到ky20_new

create table ky20_new (select * from ky20);  #复制整个ky20表，包括内容
1
2
3
4
5

2、清空表

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

2.1 方法一:delete

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

delete from text_01;
1

2.2 方法二:truncate

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

truncate table text_01;
1

2.3 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
2

三、数据库用户管理

1、查看用户

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

use mysql
select user,host from user;
#先进入到mysql库中，再使用select查看user和host字段的内容

或
select user,host from mysql.user;
直接查看mysql库下面的user表的user和host表
1
2
3
4
5
6
7

2、新建用户

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

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

create user 'libai'@'localhost' identified by '123456';
#使用明文密码创建用户，且用户仅在本地登录


select password('abc123');
create user 'liubei'@'localhost' identified by password '密文';

#先使用select生成‘abc123’的密文，然后使用密文进行创建用户，用户仅在本地才能登录
1
2
3
4
5
6
7
8

3、重命名用户

rename user 'libai'@'localhost' to 'baijuyi'@'localhost';
#将libai用户更名为baijuyi用户。
1
2

4、删除用户

drop user 'baijuyi'@'localhost';
#删除用户baijuyi
1
2

四、用户密码管理

1、修改当前用户的密码

set password = password('abc123');
#修改当前用户的密码

无论使用哪个用户，都可以使用此方法修改自己的密码。
1
2
3
4

2、修改其它用户的密码

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

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('123') where user='root';
flush privileges;

最后重新登录，登录前需要把配置文件/etc/my.conf 文件里的ship-grant-tables 删除，重启mysql服务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

五、数据库授权

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

1、查看权限

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

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

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

show grants for ydq@localhost;   #查看ydq用户在本机登录的权限
show grants for ydq@20.0.0.1;    #查看ydq用户在20.0.0.1上登录的权限
1
2

2、授权

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

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

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

使用ydq用户登录，发现只能看到自己默认的数据库，没有权限看其它的数据库

通过登录root用户，添加ydq用户在本机上，可以查看school数据下面所有表的权限。

再次登录到ydq用户，发现可以查看到school数据的权限

grant all [privileges] on *.* to 'ydq'@'20.0.0.1' identified by 'ydq1';
#允许用户libai在所有终端远程连接mysql，并拥有所有权限。
1
2

创建ydq用户在20.0.0.1主机上登录

在20.0.0.1主机上使用软件navicat进行登录，发现也是没有权限

再回来本机主机上，添加ydq用户在20.0.0.1主机上所有的权限，并修改登录密码

再次通过软件在20.0.0.1主机上登录到ydq用户。发现可以查看到所有的数据库。

3、取消授权

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

revoke select on school.* from 'ydq'@'localhost';
1