【MySQL】表的约束——空属性、默认值、列描述、zerofill、主键、自增长、唯一键、外键

【MySQL】表的约束——空属性、默认值、列描述、zerofill、主键、自增长、唯一键、外键

mysql

  

表的约束

  MySQL中的表的约束是一种规则,用于限制或保护表中数据的完整性和合法性。约束可以确保数据在插入、更新或删除时满足特定的条件,从而维护数据的正确性和一致性。

1. 空属性

  两个值:null(默认的)和not null(不为空)

  数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因为数据为空没办法参与运算。

创建一个数据表其中的id1为NULL,id2为NOT NULL:

create table test1(id1 int null,id2 int not null);


  

查看表中的结构:

desc test1;

我们看到对于数据id1,是可以填写NULL值的,而id2则无法填写空值。

  

向test1插入不同的数据并且打印:

insert into test1(id1,id2) values(10,20); // 插入成功
 
insert into test1(id2) values(20); // 插入成功,id1可以为空值

insert into test1(id1) values(20); // 插入失败,id2不可以是空值
//ERROR 1364 (HY000): Field 'id2' doesn't have a default value

  所以我们在设计数据库表的时候,一定要在表中进行限制,满足上面条件的数据就不能插入到表中。这就是“约束”。

  

2. 默认值

  默认值:某一种数据会经常性的出现某个具体的值,可以在一开始就指定好,在需要真实数据的时候,用户可以选择性的使用默认值。

  注意:not null和defalut一般不需要同时出现,因为default本身有默认值,不会为空。

创建一个表,包含不能为空的姓名,默认值为0的年龄,默认值为男的性别:

mysql> create table test2(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 0,
    -> sex char(2) default '男'
    -> );


  

查看表中的结构:

desc test2;

  可以看到我们的年龄和性别都成功的设置好了默认值0和男。当我们在插入数据的时候不给该字段赋值,就使用默认值。

  

向表中插入数据并打印:

mysql> insert into test2(name,age,sex) values('张三',18,'男');
mysql> insert into test2(name,age,sex) values('小红',20,'女');
mysql> insert into test2(name,age) values('李四',22); // 只使用sex的默认值
mysql> insert into test2(name) values('王五'); // 只填了姓名,使用age和sex的默认值

  

3. 列描述

  列描述:***ment,没有实际含义,专门用来描述字段,会根据表创建语句保存,用来给程序员或DBA来进行了解,类似注释。

创建和上面类似的代码同时加上列描述:

mysql> create table test3(
    -> name varchar(20) not null ***ment '姓名',
    -> age tinyint default 0 ***ment '年龄',
    -> sex char(2) default '男' ***ment '性别'
    -> );

  

此时不能desc查看到列描述,但是可以使用show操作:

desc test3;
show create table test3\G

  

4. zerofill

  zerofill:将自动为该列的值填充前导零,以确保该列的总宽度(包括符号位,如果有的话)与列定义中指定的显示宽度相匹配,类似一种格式化输出。

创建一张表,其中int类型a使用zerofill,而int类型b不加任何约束:

mysql> create table test4(
    -> a int(5) zerofill,
    -> b int(10)
    -> );

  

查看表的结构:

desc test4;

  

插入数据并且打印:

mysql> insert into test4(a,b) values(1,1);
mysql> insert into test4(a,b) values(10,20);
mysql> select * from test4;

  

  int既然是四个字节,那是否int(10)和int(5)是一样的呢?

  其实没有zerofill这个属性,括号内的数字是毫无意义的。这个数字并不代表该列可以存储的最大整数值或存储大小。相反,这个数字是所谓的“显示宽度”(display width),它主要用于控制当查询结果返回时该列的显示宽度。

我们使用十六进制查看a的数值:

mysql> select a,hex(a) from test4;

  所以可以看出数据库内部存储的还是1和10,00001和00010只是设置了zerofill属性后的一种格式化输出而已。

  

5. 主键

  主键:primary key用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键;主键所在的列通常是整数类型。

创建一张表,其中包括int类型的id和varchar类型的name,其中id为主键:

mysql> create table test5(
    -> id int unsigned primary key,
    -> name varchar(20)
    -> );

  

查看表结构:

desc test5;

  其中id的Key就标记上了主键标志PRI。

  

向表中插入数据并且打印:

mysql> insert into test5 values(1,'张三');
mysql> insert into test5 values(2,'李四');
mysql> insert into test5 values(3,'王五');

mysql> insert into test5 values(3,'赵六');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

  我们看到了当表中有id为3的数据时,此时在插入id=3的数据时就会直接报错,这样就很好的约束了我们表中由主键标记的数据是唯一的。

  

删除主键(id):

mysql> alter table test5 drop primary key;

  

如果创建的表中没有主键,我们可以向某一个字段(这里向id)追加主键:

alter table test5 add primary key(id);

  

上面,如果追加的字段有重复的字段,就无法继续追加主键,需要删除相同的字段:

  此时的表中有重复的id。

  因为有重复的id,所以添加主键失败。

  删除重复包含的id的数据。

  再次插入主键成功。

  

6. 自增长

  auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。

  自增长的特点:

  (1)任何一个字段要做自增长,前提是本身是一个索引(key一栏有值);

  (2)自增长字段必须是整数;

  (3)一张表最多只能有一个自增长。

  

创建一张表,其中的id为主键且自增长,name为varchar类型:

mysql> create table test6(
    -> id int primary key auto_increment,
    -> name varchar(10)
    -> );

  

查看表结构:

desc test6;

  

插入数据并且打印:

mysql> insert into test6 values(1,'张三');
mysql> insert into test6 values(2,'李四');
mysql> insert into test6(name) values('王五');

mysql> select * from test6;

  可以看到我们在没有输入id的时候,自增长会帮我们自动的向最大值进行+1操作。

  

7. 唯一键

  一张表中有往往有很多字段需要唯一性,数据不能重复,但是一张表中只能有一个主键:唯一键就可以解决表中有多个字段需要唯一性约束的问题。

  唯一键的本质和主键差不多,唯一键允许为空,而且可以多个为空,空字段不做唯一性比较。

  关于唯一键和主键的区别:

  我们可以简单理解成,主键更多的是标识唯一性的。而唯一键更多的是保证在业务上,不要和别的信息出现重复。 就像我们在学校一样,我们的学号就是我们的主键是唯一标识的,而学生的手机号设置为唯一键(可能会人没有手机号的情况)。

  学号字段是主键,它保证了每个学生都有一个唯一的学号,并且学号不能为空。手机号是唯一键,它确保了在这个表中手机号的唯一性。因为我们不会使用每个人的手机号做区分的标识。主键和唯一键不冲突,相互补充。

  

创建表,其中的id为主键,phone为唯一键:

mysql> create table test7(
    -> id int primary key,
    -> phone int unique
    -> );


  

查看表结构:

desc test7;


  

插入数据并且打印:

mysql> insert into test7 values(1,111);
mysql> insert into test7 values(2,222);
mysql> insert into test7 values(3,333);

mysql> insert into test7 values(4,333);
ERROR 1062 (23000): Duplicate entry '333' for key 'phone' // phone重复,直接报错

mysql> select *from test7;

  

8. 外键

  外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,主表则必须是有主键约束或unique约束。当定义外键后,要求外键列数据必须在主表的主键列存在或为null。


  

创建主键表包含id和name:

create table myclass (
	id int primary key,
	name varchar(30) not null ***ment'班级名'
);

  

创建从表包含id,name,class_id,外连接myclass的id:

create table stu (
	id int primary key,
	name varchar(30) not null ***ment '学生名',
	class_id int,
	foreign key (class_id) references myclass(id)
);


  

查看两张表的结构:

  

插入数据并且打印:

mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
mysql>  insert into stu values(100, '张三', 10),(101, '李四',20);

mysql> insert into stu values(102, 'wangwu',30); // 没有这个班级,插入失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`constraint_test`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))
mysql> select* from myclass;
mysql> select* from stu;


  

笛卡尔积打印两张表的组合:

mysql> select* from stu,myclass;


  

筛选出有关联的数据:

mysql> select* from stu,myclass where stu.class_id=myclass.id;

  

转载请说明出处内容投诉
CSS教程_站长资源网 » 【MySQL】表的约束——空属性、默认值、列描述、zerofill、主键、自增长、唯一键、外键

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买