表的约束:在表中会有各种约束,通过约束,让我们在未来插入数据表中的数据是符合预期的。
约束本质就是通过技术手段,倒逼程序员插入正确的数据。反过来,站在mysql的视角,凡是插入进来的数据,都是符合数据约束的!
所以约束的最终目的就是:保证数据的完整性和可预期性。
1.非空约束
mysql的NULL和C语言的NULL是不一样的,C语言的NULL既可以表示空,还可以表示0,但是mysql这里的NULL它只表示空,并且mysql ' ' 和 " "都可以表示空串。不过一般用单引号。
另外NULL是不参与计算的,比如1 + NULL还是NULL。
对于表中某一列的属性,可以设置为null(默认的,表示可以为空),还有not null(不可以为空)。
比如我们创建一张班级表
create table myclass(
class_name varchar(20) not null,
class_room varchar(10) not null
);
在查看表结构这里,Null这一列都是NO,表示不可以为空。
当我们插入数据时,如果这一列的属性是not null的话,我们如果不插入数据的话,就会报错。
2.默认值
在创建表时,我们可以给某一列加上一个默认值,当我们对这一列插入空的时候,mysql会自动帮我们插入当初设置的默认值,在刚刚学的非空约束那里,比如null的默认值就是null。
比如我们创建一张个人信息表
create table t3(
name varchar(20) not null,
age tinyint unsigned default 0,
sex char(2) default '男'
);
发现在Default列那里,age和sex的值就是当初我们建表时设置的默认值。
所以我们插入数据的时候,也可以只插入name列,其他两列都可以用默认值。
空属性和默认值
关于not null,有一个细节:
依旧使用上面的表,我们显示的对name列插入NULL,
它的报错信息是说,name列不能为空,接着,我们忽略name列进行插入
而这一次的报错信息就变成了,name列没有默认值,所以插入失败。
两次的报错原因并不一致。查看一下建表信息
发现,如果我们设置了not null之后,它是没有默认值的。
然后我们再创建一个表
create table t4 (
name varchar(20) not null,
age tinyint not null default 18
);
这个表看似有点奇怪,我们对于年龄列设置了not null,但是又给了它默认值,再来做插入测试
先显示的给age列插入空,
发现,报错信息提示age列不能为空,那么我们忽略age列进行插入呢?
发现它竟然插入成功了
结论:
如果我们没有明确指明哪一列要插入,那么就会使用默认值,如果建表中没有默认值,那么就会报错。default和not null不是冲突的,而是互补的。
不过一般not null 和default是不需要同时出现的,因为它不符合一般的设计逻辑。
3.列描述(comment)
列描述没有实际意义,它像C/C++或者其他语言里面的注释。
比如我们创建一张表
create table t5 (
name varchar(20) not null comment '用户名',
age tinyint default 18 comment '用户的年龄',
sex char(1) default '男' comment '用户的性别'
);
desc一下
发现没有什么两样,包括后边的插入也是。
那我们再show一下
在这里我们就发现当初我们写下的注释。
其实comment严格来说它不是mysql的约束,它就是注释,为了提醒程序员关于表的信息的。
4.zerofill
这是一个显示方面的约束。填充零。
我们之前学过mysql的数据类型,很多数据类型后面带一个圆括号,里面有一个数字,不同的数据类型这个数据都有它的意义,但是int()里面所带的数字是什么意思呢?
首先创建一张表
create table t6(
a int unsigned default NULL,
b int unsigned default NULL
);
然后我们desc一下
我们发现在类型那里,int后面带了一个10。先不说这个10有什么意义,我们在创建的时候没有带上10,但是为什么这里带了10呢?这是因为mysql是一个网络服务,我们通过mysql的客户端把sql语句提交上去,mysqld会给我们交上去的sql语句进行优化之后,执行优化后的sql语句,如果想看到mysqld到底执行的是那样的sql语句,我们可以show create一下
这就是mysqld真正执行的sql语句。
再回到zerofill,我们先给表插入字段
接着我们改一下b列的属性
alter table t6 modify b int unsigned zerofill not null;
我们再查一下表的内容,
发现b的数字算上填充零刚好是10位。这里也说明了int圆括号里面表示的是数据需要补零的宽度,而zerofill的作用就是将不满宽度的位置用0补齐,当然数据库里面存的还是原来的值,比如我们用where条件查找0000000002这一列时,依旧用where b=2即可。
如果数据的宽度已经超过了需要补零的范围,那么数据是多少就显示多少,只要在int的范围即可。
比如我们将int再修改一下,将补零的宽度改为5
alter table t6 modify b int(5) unsigned zerofill not null;
再插入111和111111,然后再查看
所以zerofill只是在显示上有约束,也就是帮我们做格式化显示的,并不会影响数据。我们也可以手动的用16进制来显示b列,
select a,hex(b) from t6;
(在查之前还插入了200)
最后需要注意的是,有符号的int它圆括号里面的数字默认是11,而无符号的是10,这是因为有符号的要拿一位表示符号,所以要多1。
5.主键
create table t7 (
id int unsigned primary key comment '学号不能为空',
name varchar(20) not null
);
alter table t7 drop primary key;
alter table t7 add primary key(id);
注意在添加主键的时候就要指明是哪一列了。
另外,如果在表中id的值有重复的,那么添加主键的操作就会失败,我们要先确保表中id列没有重复值,才能对id列添加主键成功。
复合主键
虽然说一个表中只能有一个主键,但是不意味着一个表中的主键只能添加给一列,也就说一个主键可以被添加到一列或者多列上。
我们把添加到多列上的主键称为复合主键。
我们创建一张选课表,我们想让一个学生(id)不能选重复的课,那么我们可以让id和课程id称为复合主键,让它俩合起来是表中唯一的存在。
create table pick_course(
id int unsigned,
course_id int unsigned comment '课程编号',
score tinyint unsigned comment '得分',
primary key (id,course_id)
);
发现表结构中,确实有两个字段都是主键属性。说明有两个PRI不是说它有两个主键,而是说明有两列是主键属性,它俩合起来才称为一个主键。
关于复合主键的冲突,只有当复合主键对应的列的值全部重复时,才会冲突报错,部分列的值是可以重复的。
6.自增长
还是先创建一个测试表
create table t8 (
id int unsigned primary key auto_increment,
name varchar(20) not null
);
查一下表结构发现,id这一列的补充的内容表示id是自增的。
我们先分别插入a,b
发现确实不需要我们管,id这一列是自增的。当我们在显示地对id列插入1000之后,它又是从1001开始自增的了,那么mysql是怎么知道它该从哪里开始自增呢?
当我们show一下
我们发现,在这里就记录了下一次id列默认的值是多少。
另外,我们也可以在建表的时候,手动设置下一次自增长起始值是多少
create table t9 (
id int unsigned primary key auto_increment,
name varchar(20) not null
)auto_increment=500;
7.唯一键
创建一张学生表来测试
create table t10 (
id int unsigned unique comment 'id是唯一键',
name varchar(20) not null
);
唯一键默认是可以为空的。插入多个NULL时不会冲突。
主键和唯一键它们之间的关系也是互补的,比如有些场景下,一张学生表,它的学生id必须要唯一,且不能为空,那么就适合作为主键,而每个学生的电话或者qq号也必须唯一,但是可以为空,那么就可以做唯一键。
一般不建议将与业务相关的列作为主键,将与业务强相关的设置为唯一键,这样业务调整时不会对主键做过大的调整。
8.外键
语法:
![](https://img-blog.csdnimg.cn/direct/9ce429510201447ca94482a7cf20e1ba.png)
外键一定是在从表当中建立的,在主表中能被从表作为外键的列一般都是主键或者唯一键。
测试:
先创建主键表
create table if not exists myclass(
id int primary key,
name varchar(30) not null comment'班级名'
);
再创建从表
create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);
首先往主表里插入一些数据
insert into myclass values(1,'C++班');
insert into myclass values(2,'Java班');
接着我们来往从表里面插入数据
insert into stu values(100,'亚索',1);
insert into stu values(101,'永恩',2);
但是根据外键约束,如果我们插入的班级id不存在,那么就会直接报错
这里就体现了对从表的外键约束。
如果我们删除主表的数据
因为我们删除的1号班级是有学生的,所以外键约束使我们不能删除这项数据。
这就是外键约束。
另外,我们show一下从表
我们发现在外键那一列有一个CONSTRAINT
意思就是可以给我们的外键起名字,如果不写就用默认的。
总结:
外键:1.从表和主表的关联关系。2.产生外键约束。
两张表在业务上是有相关性的,但是在业务上没有建立约束关系,那么就可能出现问题。
到这里我们学到的都是ddl语言,也就是数据定义语言。
9.综合案例
-- 创建数据库
create database if not exists bit32mall
default character set utf8 ;
-- 选择数据库
use bit32mall;
-- 创建数据库表
-- 商品
create table if not exists goods
(
goods_id int primary key auto_increment comment '商品编号',
goods_name varchar(32) not null comment '商品名称',
unitprice int not null default 0 comment '单价,单位分',
category varchar(12) comment '商品分类',
provider varchar(64) not null comment '供应商名称'
);
-- 客户
create table if not exists customer
(
customer_id int primary key auto_increment comment '客户编号',
name varchar(32) not null comment '客户姓名',
address varchar(256) comment '客户地址',
email varchar(64) unique key comment '电子邮箱',
sex enum('男','女') not null comment '性别',
card_id char(18) unique key comment '身份证'
);
-- 购买
create table if not exists purchase
(
order_id int primary key auto_increment comment '订单号',
customer_id int comment '客户编号',
goods_id int comment '商品编号',
nums int default 0 comment '购买数量',
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id)
);