MySQL约束和基本操作

目录

完整性约束

DDL表操作

修改表结构

修改列的名称

删除表 drop table 表名称

查看当前数据库种的所有表

查看表结构

查看创建表的sql语句

其它操作

重命名表 

清空表

---

完整性约束

DBMS 为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供针对插入的数据进行检查。

• 开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法的数据，则会自动报错，拒绝插入
• 实际有6种约束，可以分为3大类完整性约束。有些老外的书种提出的是4种【域完整性】
  •  实体完整性，由主键约束实现
  • 参照完整性，由外键约束实现 
  • 用户自定义完整性，由其他 4 种约束实现
• 实际开发中除了主键约束外，其它约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便

 1、非空约束，指定列值不允许为空

create table t1(id int not null); --不允许id列值为null
create table t2(id int null); -- 允许id列值为null
create table 3(id int);-- 允许id列值为null

2 、默认约束 default

一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效

create table t1(id int not null default 0);

3 、主键约束，又叫做实体完整性，不允许数据重复存储

• 主键可以唯一标识一行数据
• 主键约束含义是非空、唯一

create table t1(id int primary key,....);
create table t2(
id int not null,...
primary key(id) -- 注意这里的主键约束允许使用复合主键,多个列构成的主键
);

使用 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 值，在表中已经存在的话，那么写入的数据会" 悄无声息 " 覆盖已存在的数据。

3、表尽可能都要设置主键，主键尽量使用 bigint 类型， 21 亿的上限还是有可能达到的，比如魔兽，虽然说row_id上限高达 281 万亿，但是覆盖数据显然是不可接受的。

4 、唯一约束 unique

• 不允许添加唯一性约束的列出现重复值
• 可以没有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'); -- 报错

5 、外键约束，又叫做参照完整性

6 、检查约束

DDL表操作

创建操作

create table tb_student(
id bigint primary key auto_increment,
name varchar(10) not null comment '说明信息',
birth date,
sex boolean default 1,
salary numeric(8,2)
)engine=innodb default charset utf8;

快速创建一个表和另外一个表的结构相同，只是克隆表结构，不会克隆数据

create table 新表名称 like 已经存在的旧表名称;

修改表结构

语法规则为 alter table 表名称 add/drop/modify 列名称 [ 类型 约束 ]

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一般用于修改列的数据类型和长度，但是如果有数据则必须和目标类型匹
配，否则修改失败

修改列的名称

alter table 表名称 change 旧有列名称 新列名称 类型 约束;

删除表 drop table 表名称

drop table tb_student;
drop table if exists tb_student;

查看当前数据库种的所有表

show tables;

查看表结构

desc 表名称;

查看创建表的sql语句

show create table tb_student;

其它操作

重命名表 

重命名表 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 语句相同。