数据库概述02（完整性约束）

完整性约束

DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供针对插入的数据进行检查。
开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法的数据，则会自动报错，拒绝插入
实际有6种约束，可以分为3大类完整性约束。有些老外的书种提出的是4种【域完整性】
实体完整性，由主键约束实现
参照完整性，由外键约束实现
用户自定义完整性，由其他4种约束实现
实际开发中除了主键约束外，其它约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便
1、非空约束，指定列值不允许为空
插入null值有2种情况：直接插入null，或者没有在表上定义default而不插入数据，则默认null
2、默认约束default
一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效
3、主键约束，又叫做实体完整性，不允许数据重复存储
主键可以唯一标识一行数据，一个表中只能有一个主键，但是主键允许是由多个列构成
主键约束含义是非空、唯一
create table t1(id int not null); --不允许id列值为null
create table t2(id int null); – 允许id列值为null
create table 3(id int);-- 允许id列值为null
create table t1(id int not null default 0); – not null不是必须的
create table t2(id datetime default now()); – 正确的，过去只有timestamp default
current_timestamp

使用InnoDB存储引擎时，如果数据表没有设置主键，那么Innodb会给该表设置一个不可见，长度为6字节的默认主键 row_id。Innodb维护了一个全局的dict_sys.row_id值，这个值，被所有无主键的数据表共同使用，每个无主键的数据表，插入一行数据，都会是当前的dict_sys.row_id的值增加1
其实row_id的值在存储时，数据长度为8字节，只不过Innodb只使用后6个字节。那么row_id的值，写到数据表中时就有一下两个特点：
1.row_id写入表中的值范围，是从0-2^48-1。
2.当row_id的值为2的48次方时，再进行数据插入，那么row_id的后6个字节的值，就全部为0了。也就是说，当row_id的值到了2^48次方-1后，再次插入数据，下一个值就是0，然后开始循环。不过和自定义主键不同的是，row_id标识的主键，没有唯一性约束，当插入数据的row_id值，在表中已经存在的话，那么写入的数据会"悄无声息"覆盖已存在的数据。
表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的，比如魔兽，虽然说row_id上限高达281万亿，但是覆盖数据显然是不可接受的。根据主键是否有业务含义可以分为业务主键和代理主键
4、唯一约束 unique
不允许添加唯一性约束的列出现重复值
可以没有null值约束，而且也不能针对null进行唯一性判定
5、外键约束，又叫做参照完整性
6、检查约束check
create table t1(id int primary key,…); – 单一主键，
create table t2(
id int not null,…
primary key(id) – 注意这里的主键约束允许使用复合主键,多个列构成的主键
);
复合主键中的任一列即使没有添加not null约束，也不允许为null

create table t1(id int unique,name varchar(20));
insert into t1 values(1,‘zhangsan’);
insert into t1(name) values(‘zhao4’);-- id为null
insert into t1(name) values(‘zhao4’);-- id为null，两次null并不会报错
insert into t1 values(1,‘li4’); – 报错
mysql> create table t23(
-> id int,
-> name varchar(32),
-> unique(id,name)); – 多个列的组合不允许重复，单一列允许重复

DDL表操作
创建操作
快速创建一个表和另外一个表的结构相同，只是克隆表结构，不会克隆数据
修改表结构，语法规则为 alter table 表名称 add/drop/modify 列名称 [类型 约束]
修改列的名称
删除表 drop table 表名称
查看当前数据库种的所有表
查看表结构
mysql> create table t24(
-> id boolean default 1,
-> check(id in(1,0)));

create table tb_student(
id bigint primary key auto_increment,
name varchar(10) not null comment ‘说明信息’, – comment用于对表或者列添加说明信息
birth date,
sex boolean default 1,
salary numeric(8,2)
)engine=innodb default charset utf8;

create table 新表名称 like 已经存在的旧表名称;
create table 新表名称 as
select id emp_no,name emp_name from 克隆表的名称
where 1=2;
alter table tb_student add class_name varchar(20) default ‘软件19’; – 新增列
alter table tb_student drop column salary; – 删除指定列
alter table tb_student modify class_name int; – 修改列定义时，要求已经存在的数据必须符合规则，否则修改失败。modify一般用于修改列的数据类型和长度，但是如果有数据则必须和目标类型匹配，否则修改失败

1 alter table 表名称 change 旧有列名称 新列名称 类型 约束;
drop table tb_student;
drop table if exists tb_student;

show tables;
show tables from sys;

查看创建表的sql语句
其它操作
重命名表 RENAME TABLE emp TO myemp;
ALTER table dept RENAME [TO] detail_dept;
清空表
TRUNCATE TABLE语句：删除表中所有的数据、释放表的存储空间
TRUNCATE TABLE detail_dept; TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
阿里开发规范：
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

MyS