mysql建立约束限制值>1_mysql-约束介绍

最新推荐文章于 2022-09-08 16:27:57 发布

wicky gao

最新推荐文章于 2022-09-08 16:27:57 发布

阅读量602

点赞数

文章标签： mysql建立约束限制值>1

本文链接：https://blog.csdn.net/weixin_34792402/article/details/113609333

版权

一、约束介绍

约束是一种限制，它通过对表的行或列的数据做出限制，来确保数据的完整性、一致性。约束条件与数据类型宽度一样都是可选参数。

常用约束：

PRIMARY KEY(PK) 标识该字段为该表的主键，可以唯一的标识记录FOREIGN KEY(FK) 标识该字段为该表的外键NOT NULL标识该字段不能为空UNIQUE KEY(UK) 标识该字段的值是唯一的

AUTO_INCREMENT 标识该字段的值自动增长(整数类型，而且为主键)DEFAULT为该字段设置默认值

UNSIGNED 无符号

ZEROFILL 使用0填充

(一)、not null与default

not null 用于约束列不允许为空

null 列的默认约束为null 允许为空

default 默认值，创建列时可以指定其默认值，插入数据为设置时，自动添加为默认值。

mysql> create table test1(id int not null,name varchar(11) default ‘aaa‘);

Query OK,0 rows affected (0.26sec)

mysql> insert into test1 value(null,‘abc‘);

ERROR1048 (23000): Column ‘id‘ cannot be nullmysql> insert into test1(id) value(1);

Query OK,1 row affected (0.29sec)

mysql> select * fromtest1;+----+------+

| id | name |

+----+------+

| 1 | aaa |

+----+------+

1 row in set (0.00 sec)

test

(二)、unique

设置唯一约束，当你需要限定你的某个表字段每个值都唯一,没有重复值时使用。

允许为空

mysql> create table test2(id int not null ,name varchar(11),phone int(11) unique);

Query OK,0 rows affected (0.50sec)

mysql> desctest2;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| phone | int(11) | YES | UNI | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql> insert into test2 value(1,‘abc‘,111111);

Query OK,1 row affected (0.29sec)

mysql> insert into test2 value(1,‘abc‘,111111);

ERROR1062 (23000): Duplicate entry ‘111111‘ for key ‘phone‘mysql>

test

联合唯一：(多个唯一)

mysql> create table test3 (id int not null,name varchar(11),phone int(11),unique(id,phone));

Query OK,0 rows affected (0.47sec)

mysql> insert into test3 value(1,‘aaa‘,123);

Query OK,1 row affected (0.29sec)

mysql> insert into test3 value(1,‘bbb‘,123);

ERROR1062 (23000): Duplicate entry ‘1-123‘ for key ‘id‘mysql> insert into test3 value(1,‘bbb‘,555);

Query OK,1 row affected (0.29 sec)

test

(四)、primary key

站在约束角度看primary key=not null unique。主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

主键约束列不允许重复，也不允许出现空值。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。(通常id字段被设置为主键)

--基本模式

create table temp(

idint primary key,

namevarchar(20)

);--组合模式

create table temp(

idint,

namevarchar(20),

pwdvarchar(20),primary key(id, name)

);

(五)、auto_increment

约束字段为自动增长

#不指定id，则自动增长create tablestudent(

idint primary keyauto_increment,

namevarchar(20),

sex enum(‘male‘,‘female‘) default ‘male‘);

mysql> descstudent;+-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | enum(‘male‘,‘female‘) | YES | | male | |

+-------+-----------------------+------+-----+---------+----------------+

mysql> insert into student(name) values

-> (‘aaa‘),-> (‘bbb‘)->;

mysql> select * fromstudent;+----+------+------+

| id | name | sex |

+----+------+------+

| 1 | aaa | male |

| 2 | bbb | male |

+----+------+------+

#也可以指定id

mysql> insert into student values(4,‘asb‘,‘female‘);

Query OK,1 row affected (0.00sec)

mysql> insert into student values(7,‘wsb‘,‘female‘);

Query OK,1 row affected (0.00sec)

mysql> select * fromstudent;+----+------+--------+

| id | name | sex |

+----+------+--------+

| 1 | aaa | male |

| 2 | bbb | male |

| 4 | asb | female |

| 7 | wsb | female |

+----+------+--------+

#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

mysql> delete fromstudent;

Query OK,4 rows affected (0.00sec)

mysql> select * fromstudent;

Emptyset (0.00sec)

mysql> insert into student(name) values(‘ysb‘);

mysql> select * fromstudent;+----+------+------+

| id | name | sex |

+----+------+------+

| 8 | ysb | male |

+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

mysql> truncatestudent;

Query OK,0 rows affected (0.01sec)

mysql> insert into student(name) values(‘aaa‘);

Query OK,1 row affected (0.01sec)

mysql> select * fromstudent;+----+------+------+

| id | name | sex |

+----+------+------+

| 1 | aaa | male |

+----+------+------+

row in set (0.00 sec)

test

(六)、foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

现在有两个表，第一个学生表有三个字段，学号、姓名、班级第二个学校表有班级，老师等字段，每个学生都有班级，那班级这个字段就需要重复存储，很浪费资源，我们可以建一个班级表，让学生关联这个班级表。

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一create tableclass(

idint primary key,

namevarchar(20) not null)

#cls_id外键，关联父表(department主键id)，同步更新，同步删除create tablestudent(

idint primary key,

namevarchar(20) not null,

dpt_idint,constraint fk_name foreign key(cls)referencesclass(id)on delete cascade

on update cascade)engine=innodb;

foreign key注意：

1、被关联的字段必须是一个key，通常是id字段

2、创建表时：必须先建立被关联的表，才能建立关联表

3、插入记录时：必须先往被关联的表插入记录，才能往关联表中插入记录

4、删除时：应该先删除关联表中的记录，再删除被关联表对应的记录

二、表与表之间的关系

如何才能找出两张表之间的关系呢？

分析步骤：

#1、先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreignkey右表一个字段(通常是id)

#2、再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreignkey左表一个字段(通常是id)

#3、总结：

#多对一：

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

#多对多

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:

如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

建立表与表之间的关系：

多对一：

=====================多对一=====================

create tablepress(

idint primary keyauto_increment,

namevarchar(20)

);create tablebook(

idint primary keyauto_increment,

namevarchar(20),

press_idint not null,foreign key(press_id) referencespress(id)on delete cascade

on update cascade);insert into press(name) values(‘北京工业地雷出版社‘),

(‘人民音乐不好听出版社‘),

(‘知识产权没有用出版社‘)

;insert into book(name,press_id) values(‘九阳神功‘,1),

(‘九阴真经‘,2),

(‘九阴白骨爪‘,2),

(‘独孤九剑‘,3),

(‘降龙十巴掌‘,2),

(‘葵花宝典‘,3)

;

View Code

多对多：

=====================多对多=====================

create tableauthor(

idint primary keyauto_increment,

namevarchar(20)

);

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了create tableauthor2book(

idint not null uniqueauto_increment,

author_idint not null,

book_idint not null,constraint fk_author foreign key(author_id) referencesauthor(id)on delete cascade

on update cascade,constraint fk_book foreign key(book_id) referencesbook(id)on delete cascade

on update cascade,primary key(author_id,book_id)

);

#插入四个作者，id依次排开insert into author(name) values(‘egon‘),(‘alex‘),(‘yuanhao‘),(‘wpq‘);

#每个作者与自己的代表作如下1egon:1九阳神功2九阴真经3九阴白骨爪4独孤九剑5降龙十巴掌6葵花宝典2alex:1九阳神功6葵花宝典3yuanhao:4独孤九剑5降龙十巴掌6葵花宝典4wpq:1九阳神功insert into author2book(author_id,book_id) values(1,1),

(1,2),

(1,3),

(1,4),

(1,5),

(1,6),

(2,1),

(2,6),

(3,4),

(3,5),

(3,6),

(4,1)

;

View Code

一对一：

#一定是student来foreign key表customer，这样就保证了：

#1学生一定是一个客户，

#2客户不一定是学生，但有可能成为一个学生create tablecustomer(

idint primary keyauto_increment,

namevarchar(20) not null,

qqvarchar(10) not null,

phonechar(16) not null);create tablestudent(

idint primary keyauto_increment,

class_namevarchar(20) not null,

customer_idint unique, #该字段一定要是唯一的foreign key(customer_id) referencescustomer(id) #外键的字段一定要保证uniqueon delete cascade

on update cascade);

#增加客户insert into customer(name,qq,phone) values(‘李飞机‘,‘31811231‘,13811341220),

(‘王大炮‘,‘123123123‘,15213146809),

(‘守榴弹‘,‘283818181‘,1867141331),

(‘吴坦克‘,‘283818181‘,1851143312),

(‘赢火箭‘,‘888818181‘,1861243314),

(‘战地雷‘,‘112312312‘,18811431230)

;

#增加学生insert into student(class_name,customer_id) values(‘美术一班‘,3),

(‘声乐二班‘,4),

(‘美术一班‘,5)

;

View Code

wicky gao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql建立约束限制值>1_mysql-约束介绍

一、约束介绍约束是一种限制，它通过对表的行或列的数据做出限制，来确保数据的完整性、一致性。约束条件与数据类型宽度一样都是可选参数。常用约束：PRIMARY KEY(PK) 标识该字段为该表的主键，可以唯一的标识记录FOREIGN KEY(FK) 标识该字段为该表的外键NOT NULL标识该字段不能为空UNIQUE KEY(UK) 标识该字段的值是唯一的AUTO_INCREMENT...
复制链接

扫一扫