数据库的约束条件与用户管理

一、约束条件
1、主键约束
主键约束 （primary key）：用于标识表中的主键列的值，而且这个值是全表当中唯一的，而且值不能null，一个表只能有一个主键 。

在创建表的时候创建主键

#创建表和主键的方法一
CREATE table stu01(
id int(5),
name char(10),
score DECIMAL(5,2),
address VARCHAR(128),
PRIMARY KEY (id)#创建主键
);
#创建表和主键的方法二
CREATE table stu01(
id int(5) PRIMARY KEY,
name char(10),
score DECIMAL(5,2),
address VARCHAR(128)#创建主键
);

查看表结构

desc stu01;

向表中插入一条数据

#插入一条数据
INSERT into stu01 VALUES (1,'张三',89.9,'地址不详');

查看表中信息

SELECT * from stu01;

id为主键，插入一条id相同的数据，提示信息报错，不可插入。

INSERT into stu01 VALUES (1,'李四',89.9,'上海');

删除主键

ALTER TABLE stu01 DROP PRIMARY key ;

以上展示了主键的约束效果。

 2、外键约束
外键：用来建立表与表之间的关系，确保外键中的值与另一个表的主键值匹配，保证数据引用的完整性。

适应场景：多表联查，不要超过三张，三张以上会降低查询效率。

创建两个表，一个主表，一个从表

#创建学生表作为主表
CREATE TABLE if not EXISTS student(
#表示在创建表时自动检测表是否存在，如果不存在就创建。
crad_id int(5) PRIMARY KEY auto_increment,
#atuo_increment 自增长字段
#每条记录会自动递增1，自增长必须是主键，也不可以重复。如果添加失败下一跳记录也会自动增加
stu_name CHAR(10) not null,
stu_email VARCHAR(128) not null UNIQUE key
);
创建class表作为从表
CREATE TABLE class(
stu_id int(11) zerofill PRIMARY KEY auto_increment,
address VARCHAR(128) DEFAULT '地址不详',
crad_id int(5) not null,
#格式要与主表一样
foreign key (crad_id) references student (crad_id)
#声明外键
);

插入数据

先插入主表数据在插入从表数据，且从表的外键要与主表所关联是数据要一致，否则插入不成功
INSERT into student VALUES(320323,'张三','125426@qq,com');
INSERT into class VALUES(1,'南京',320323);

删除外键

先查询外键的索引名称之后才能删除外键

删除外键的索引名称

ALTER TABLE class DROP foreign key class_ibfk_1;

总结

外键就是和主表进行关联的列，不需要设置为从表的主键，但是不能为空，必须和主表的数据类型保持一致。

外键的值和主键的值要相同，先插入主表的数据，在插入从表的数据

删除表的外键不是直接删除外键的列名，而是删除外键的索引，show create table 表名：查看表的详细信息

删除主键不需要加上主键的列名，如果有extra的额外属性，比如自增长，要先移除属性，然后才能删除主键。

删除主键的方式：alter进行修改

3、非空约束
非空约束（ not NULL）：保证列中的值不含null值

创建表的时候，在字段类型后面设置not null 在插入数据时，不可为空，否则报错。

CREATE table stu02(
id int(5),
name char(10) not NULL ，#非空约束
score DECIMAL(5,2) not NULL ,#非空约束
address VARCHAR(128),#没有not null 可以为空
PRIMARY KEY (id)
);

4、唯一约束
唯一约束（unique key ）：确保列中的所有值都是唯一的，类似主键，但是一个表可以有多个唯一约束 。

创建表的时候创建唯一约束

CREATE table stu02(
id int(5),
name char(10) not NULL UNIQUE key,#UNIQUE key唯一约束，前提条件不可为空
score DECIMAL(5,2) not NULL UNIQUE key,#UNIQUE key唯一约束，前提条件不可为空
address VARCHAR(128),
PRIMARY KEY (id)
);

name设置唯一约束，新增name一样的数据，提示错误，name被设置唯一，不可重复

score设置唯一约束，新增score一样的数据，成绩一样，提示错误score被设置唯一，不可重复。

5、自增约束
 自增约束：在列生成的每一行都会自动生成一个唯一标识符，通常和主键一起使用，每次插入新行时，自增列的值会自动增加。

CREATE TABLE if not EXISTS test01(
#表示在创建表时自动检测表是否存在，如果不存在就创建。
id int(5) zerofill PRIMARY KEY auto_increment,
#zerofill 5位，不足5位会自动填0
#atuo_increment 自增长字段
#每条记录会自动递增1，自增长必须是主键，也不可以重复。如果添加失败下一跳记录也会自动增加
name CHAR(10),
score DECIMAL(5,2),
address VARCHAR(128)
);

插入数据

INSERT into test01 VALUES(1,'张三',90,'东京');
INSERT into test01 VALUES( 2,'李四',89,'东京');
INSERT into test01 VALUES( NULL,'王瑞',33,'东京');

由于数据库软件效果不明显，到Linux数据库查询id自增

删除id为3的数据，并继续插入一条新数据且id为null，不输入。

#删除id=3的
DELETE from test01 where id=3;
#新增
INSERT into test01 VALUES( NULL,'王为',99,'东京');

查询发现id在3的基础上自增到4

6、对表的其他操作
 复制表

create table 新表名 like 旧表；

复制数据

insert into 新表名，select * from 旧表；

复制表和数据同时进行

create table 新表（select * from 旧表）；

临时表： 临时表创建完成之后，在库里面是看不到的，但是依然可以增删改查，但是重新连接数据之后，临时表会消失不见

清空表

drop 表名 #删除表
delete from 表名 ；# 是一行一行的清空表数据，速度比较慢，如果有自增长字段，delete情况之后，会继续按照原来的序号，继续自增。
 
truncate table 表名；#清空表，保留表的结构，但是清空之后原有的记录全部抹去，自增长也将从头开始，速度比较快

二、数据库的用户管理
 1、在数据库软件中查询用户

root都是相同的

Host：可以登录的主机，localhost指的是本地登录，%：任意主机（ip地址）

权限上localhost>%的权限。

2、创建用户
root@localhost 安装完mysql之后就有了，不需要额外设置，其他的需要人工创建，其他的用户也不设置成localhost，都是设置主机名。

创建用户，允许用户名可登录的主机是192.168.10.62，表示可以通过192.168.10.62上主机的用户访问mysql1：192.168.10.61上的数据库。

create user  'cq' @'192.168.10.62';

常见用户密码

CREATE USER 'cq' @'192.168.10.62' IDENTIFIED BY '123456';

用62的主机用户登录远程连接数据库

[root@localhost ~]# mysql -h 192.168.10.61 -u root -p #由于未设置密码因此不需要输入

删除用户

drop user 'cq'@'192.168.10.62';

3、用户权限
查看数据库

mysql> show databases;

因为没有权限因此只能看

information_schema :包含了mydql服务器中所有其他数据库，表，列，索引权限等详细的元数据的信息，查询数据库的结构和元信息

performance_schema 包含mysql的服务性能和资源利用情况。查询语句和执行时间和锁定等信息

在数据库61上设置用户的所有权限

grant all privileges on * . * to  'cq' @'192.168.10.62';
#grant 赋权语句
 
#all 给与所有权限，远程登录，数据库的操作权限（增删改查）
 
#* . *   左边* 表示库，右*：库里的表
 
#identified by '123456' 登录密码

刷新权限，让其刚刚的配置生效

flush privileges；

在61上查看该用户的权限配置成功，拥有所有权限

show grants for 'cq'@'192.168.10.62';

使用mysql2查看数据库，可以查询到数据库内容

在61主机对用户进行取消权限

revoke all privileges on * .  * from 'cq'@'192.168.10.62';
#revoke  取消权限

刷新权限，让其刚刚的配置生效

flush privileges；

使用62查看数据库，不能查询到数据库内容，权限被取消

只能查看权限

grant select on xy102.* to 'zlm'@'192.168.10.62';

刷新权限，让其刚刚的配置生效

flush privileges；

使用62查看数据库，只能查询到xy102数据库，其他数据库不可见

给多个权限，权限之间使用逗号隔开

grant select,insert,update on xy102.* to 'zlm'@'192.168.10.62';