MySQL表的约束
MySQL表的约束
在MySQL中,表的约束用于确保数据的完整性和一致性。主要的约束类型包括NOT NULL、DEFAULT、COMMENT、ZEROFILL、PRIMARY KEY、FOREIGN KEY、UNIQUE和AUTO_INCREMENT。NOT NULL约束确保列中不能有NULL值。DEFAULT约束为列指定默认值,如果插入数据时未提供该列的值,则使用默认值。COMMENT用于给列添加注释,解释该列的用途或其他相关信息。ZEROFILL用于将整数列中的值填充为零,直到达到指定的显示宽度。PRIMARY KEY约束用于唯一标识表中的每一行,不能有重复值,并且每行都必须有值。FOREIGN KEY约束确保一个表中的值与另一个表中的值相对应,维护两个表之间的关系。UNIQUE约束确保列中的所有值都是唯一的。AUTO_INCREMENT用于指定列的值在插入新记录时自动递增,通常用于主键列。
注意:所有约束都是放在字段属性的后面。
如:
id int comment '学号'
1、空属性
关键字:
null
和not null
。数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因为数据为空没办法参与运算。
mysql> select null; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select 1+null; +--------+ | 1+null | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) mysql>
案例:
- 创建一个学生表,学生的学号和姓名一定不能为空
- 但是年龄可以为空
mysql> create table t13 ( -> id int not null, -> name varchar(20) not null, -> age int -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc t13; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | NO | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t13 values(1,'xx',22); Query OK, 1 row affected (0.01 sec) mysql> insert into t13(id,name) values(2,'pp'); Query OK, 1 row affected (0.01 sec) mysql> insert into t13(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value mysql> select * from t13; +----+------+------+ | id | name | age | +----+------+------+ | 1 | xx | 22 | | 2 | pp | NULL | +----+------+------+ 2 rows in set (0.00 sec)
可以看到,给不为空的字段
name
不插入数据(null
)会报错!
2、默认值
默认值:用户可以选择使用经常会用到的默认值来指定在没有给该字段赋值的时候填入。可以类比C++里面的缺省值,不赋值就给默认的值,赋值则用新值。
关键字:
default
案例:
mysql> create table t14(id int not null,name varchar(20) default '徐'); Query OK, 0 rows affected (0.03 sec) mysql> desc t14; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | 徐 | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t14 values(1,'xx'); Query OK, 1 row affected (0.01 sec) mysql> insert into t14(id) values(2); Query OK, 1 row affected (0.01 sec) mysql> select * from t14; +----+------+ | id | name | +----+------+ | 1 | xx | | 2 | 徐 | +----+------+ 2 rows in set (0.00 sec) mysql>
3.1、default和not null
当一个字段指定了
not null
和default
两个约束,那么优先满足哪个呢?下面案例来验证一下。案例:
mysql> create table tt(id int,name varchar(10) not null default 'xx'); Query OK, 0 rows affected (0.03 sec) mysql> desc tt; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | NO | | xx | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into tt(id) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from tt; +------+------+ | id | name | +------+------+ | 1 | xx | +------+------+ 1 row in set (0.00 sec) mysql>
可以看到,优先满足
default
!因此当我们需要同时满足
not null
和default
两个约束的时候,直接写default约束就行!当然,一般我们不会同时指定
not null
和default
两个约束这样写。
3、字段(列)描述
字段描述:没有实际的含义,就是用来描述字段的,可以给看这个表的程序员看对应字段表示的含义。
关键字:
comment
案例:
mysql> create table t15(id int comment '学号',name varchar(20) not null comment '姓名'); Query OK, 0 rows affected (0.03 sec) mysql> desc t15; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show create table t15; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | t15 | CREATE TABLE `t15` ( `id` int(11) DEFAULT NULL COMMENT '学号', `name` varchar(20) NOT NULL COMMENT '姓名' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
可以看到,通过
desc
看不到描述信息,需要show
才可以看到。
4、零填充
关键字:
zerofill
通过show看看t16表的建表语句:
mysql> create table t16( -> a int, -> b int unsigned -> ); Query OK, 0 rows affected (0.04 sec) mysql> show create table t16\G *************************** 1. row *************************** Table: t16 Create Table: CREATE TABLE `t16` ( `a` int(11) DEFAULT NULL, `b` int(10) unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) mysql>
我们看到字段a类型和字段b类型括号中的不一样,并且也疑惑这是用来干什么的?
其实不加zerofill约束,该数字是用来表示能表示的数据的最长长度,比如int可以表示10亿级别(长度10),unsigned int能表示100亿级别(长度11)。但是当长度少于10或者11的时候,不会在前面补0。
mysql> insert into t16 values(1,2); Query OK, 1 row affected (0.00 sec) mysql> select * from t16; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec) mysql>
加上zerofill约束,就会在少于长度的时候在前面补0。
mysql> alter table t16 modify a int(5) zerofill; Query OK, 1 row affected (0.12 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table t16 modify b int(6) unsigned zerofill; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t16; +-------+--------+ | a | b | +-------+--------+ | 00001 | 000002 | +-------+--------+ 1 row in set (0.01 sec) mysql>
我们可以看到在加入zerofill后,短于指定长度的时候会在数字前面补0。
注意:比如a虽然显示是00001,但底层其实还是存储的1。使用hex函数来验证一下。
mysql> select a,hex(a) from t16; +-------+--------+ | a | hex(a) | +-------+--------+ | 00001 | 1 | +-------+--------+ 1 row in set (0.00 sec) mysql>
可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已。
5、主键
主键:主键用来约束该字段的各数据的唯一性。每个数据的该字段不能重复、不能为空,并且一张表只能有一个主键(可以多个字段共同组成一个主键)。一般主键是整数类型。
关键字:
primary key
案例:
mysql> create table t17( -> id int primary key comment '主键', -> name varchar(11) comment '名字' -> ); Query OK, 0 rows affected (0.04 sec) mysql> desc t17; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t17 values(1,'xx'); Query OK, 1 row affected (0.01 sec) mysql> insert into t17 values(1,'zz'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t17 values(2,'zz'); Query OK, 1 row affected (0.01 sec) mysql> select * from t17; +----+------+ | id | name | +----+------+ | 1 | xx | | 2 | zz | +----+------+ 2 rows in set (0.00 sec) mysql>
可以看到,主键列的属性描述Key下有PRI,并且主键字段不能插入相同的值!
- 删除主键:
alter table 表名 drop primary key;
mysql> alter table t17 drop primary key; Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc t17; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
- 添加主键:
alter table 表名 add primary key(字段名);
mysql> alter table t17 add primary key(name); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t17; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(11) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
- 复合主键:使用多个字段组成一个主键。
mysql> create table t18( -> id int unsigned, -> name varchar(11) not null, -> addr varchar(20), -> primary key(id,name,addr) -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc t18; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(11) | NO | PRI | NULL | | | addr | varchar(20) | NO | PRI | NULL | | +-------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t18 values(1,'xx','123'); Query OK, 1 row affected (0.01 sec) mysql> insert into t18 values(1,'xx','123'); ERROR 1062 (23000): Duplicate entry '1-xx-123' for key 'PRIMARY' mysql> insert into t18 values(1,'xx','1234'); Query OK, 1 row affected (0.01 sec) mysql> insert into t18 values(1,'xxx','1234'); Query OK, 1 row affected (0.00 sec) mysql> select * from t18; +----+------+------+ | id | name | addr | +----+------+------+ | 1 | xx | 123 | | 1 | xx | 1234 | | 1 | xxx | 1234 | +----+------+------+ 3 rows in set (0.00 sec) mysql>
这里我们看到主键列的属性描述Key下的所有字段有PRI,这并不是表示一个表中可以有多个主键,而是多个字段共同组成的一个主键,这样只要各条插入数据不是这几个字段的对应的各字段都相同,就可以插入!
6、自增长
自增长:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。
自增长的特点:
任何一个字段要做自增长,前提是本身是一个索引(key一栏有值)
自增长字段必须是整数
一张表最多只能有一个自增长
关键字:
auto_increment
案例:
mysql> create table t19( -> id int unsigned primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) mysql> desc t19; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.02 sec) mysql> show create table t19\G *************************** 1. row *************************** Table: t19 Create Table: CREATE TABLE `t19` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into t19 values(1,'xx'); Query OK, 1 row affected (0.01 sec) mysql> insert into t19(name) values('zz'); Query OK, 1 row affected (0.00 sec) mysql> insert into t19(name) values('yy'); Query OK, 1 row affected (0.01 sec) mysql> insert into t19(name) values('ll'); Query OK, 1 row affected (0.01 sec) mysql> select * from t19; +----+------+ | id | name | +----+------+ | 1 | xx | | 2 | zz | | 3 | yy | | 4 | ll | +----+------+ 4 rows in set (0.00 sec) mysql> show create table t19\G *************************** 1. row *************************** Table: t19 Create Table: CREATE TABLE `t19` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into t19(name) values('pp'); Query OK, 1 row affected (0.01 sec) mysql> select * from t19; +----+------+ | id | name | +----+------+ | 1 | xx | | 2 | zz | | 3 | yy | | 4 | ll | | 5 | pp | +----+------+ 5 rows in set (0.00 sec) mysql>
在插入后获取上次插入的 AUTO_INCREMENT 的值(批量插入获取的是第一个值)
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) mysql>
- 索引:
- 索引是一种数据结构,用于快速查询和检索表中的记录。索引的主要目的是提高数据库查询的性能。索引可以显著提高SELECT查询的速度,没有索引时,数据库必须扫描整个表来找到匹配的记录,有了索引后,数据库可以直接访问相关记录。
- 索引有几种类型,PRIMARY KEY是唯一标识表中每一行的索引,不允许NULL值,每个表只能有一个。UNIQUE索引确保列中的值唯一,但允许NULL值,一个表可以有多个UNIQUE索引。普通索引(INDEX)用于加速查询,允许重复值和NULL值。FULLTEXT索引用于全文搜索,适用于CHAR、VARCHAR和TEXT列。SPATIAL索引用于地理数据类型,如GEOMETRY,用于空间数据查询。
7、唯一键
唯一键是数据库中的一种约束,用于确保表中某一列或多列的值是唯一的,即不允许重复值。与主键类似,唯一键也可以用来唯一标识表中的每一行,但与主键不同的是,唯一键允许NULL值(在MySQL中,每个列上的唯一索引允许一个NULL值)。在一个表中可以有多个唯一键,这些键可以包含单个列或多个列的组合。
总结:主键只能有一个,唯一键可以有多个;主键字段不能为空,唯一键字段可以为空。
关键字:
unique
案例:
mysql> create table t20( -> id int primary key, -> name varchar(20), -> email varchar(20) unique, -> course varchar(10) unique -> ); Query OK, 0 rows affected (0.05 sec) mysql> desc t20; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | email | varchar(20) | YES | UNI | NULL | | | course | varchar(10) | YES | UNI | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into t20 values(1,'xx','1234','语文'); Query OK, 1 row affected (0.01 sec) mysql> insert into t20 values(1,'zz','222','数学'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into t20 values(2,'xx','1234','语文'); ERROR 1062 (23000): Duplicate entry '1234' for key 'email' mysql> insert into t20 values(2,'xx','12345','语文'); ERROR 1062 (23000): Duplicate entry '语文' for key 'course' mysql> insert into t20 values(2,'xx','12345','数学'); Query OK, 1 row affected (0.00 sec) mysql> insert into t20(id,name) values(2,'xx'); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' mysql> insert into t20(id,name) values(3,'xx'); Query OK, 1 row affected (0.00 sec) mysql> select * from t20; +----+------+-------+--------+ | id | name | email | course | +----+------+-------+--------+ | 1 | xx | 1234 | 语文 | | 2 | xx | 12345 | 数学 | | 3 | xx | NULL | NULL | +----+------+-------+--------+ 3 rows in set (0.00 sec) mysql>
8、外键
外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,**主表则必须是有主键约束或unique约束。**当定义外键后,要求外键列数据必须在主表的主键列存在或为null。
语法:
foreign key (字段名) references 主表(字段名)
案例:
如果把班级表和课程表中的数据设计在每个学生表的后面,会出现数据冗余,所以我们设计成
student->course_id
和course->id
,student->class_id
和classs->id
形成关联的关系=>外键约束。对上述示意图进行设计:
创建主键表:
class表
:mysql> create table class( -> id int unsigned primary key, -> name varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) mysql> desc class; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into class values(1,'C++方向1班'); Query OK, 1 row affected (0.00 sec) mysql> insert into class values(2,'C++方向2班'); Query OK, 1 row affected (0.00 sec) mysql> select * from class; +----+---------------+ | id | name | +----+---------------+ | 1 | C++方向1班 | | 2 | C++方向2班 | +----+---------------+ 2 rows in set (0.00 sec) mysql>
course
表:mysql> create table course( -> id int unsigned primary key auto_increment, -> name varchar(20) not null -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc course; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into course values(1,'C++程序设计'); Query OK, 1 row affected (0.00 sec) mysql> insert into course(name) values('Linux系统编程'); Query OK, 1 row affected (0.01 sec) mysql> insert into course(name) values('Linux网络编程'); Query OK, 1 row affected (0.00 sec) mysql> select * from course; +----+-------------------+ | id | name | +----+-------------------+ | 1 | C++程序设计 | | 2 | Linux系统编程 | | 3 | Linux网络编程 | +----+-------------------+ 3 rows in set (0.00 sec) mysql>
再创建从表
student
:mysql> create table student( -> id int unsigned primary key auto_increment, -> name varchar(20) not null, -> course_id int unsigned, -> class_id int unsigned, -> foreign key(course_id) references course(id), -> foreign key(class_id) references class(id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> desc student; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | course_id | int(10) unsigned | YES | MUL | NULL | | | class_id | int(10) unsigned | YES | MUL | NULL | | +-----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into student values(1,'xx',2,2); Query OK, 1 row affected (0.01 sec) mysql> insert into student values(2,'yy',3,1); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(3,'zz',1,2); Query OK, 1 row affected (0.01 sec) mysql> insert into student values(4,'ll',1,3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test4`.`student`, CONSTRAINT `student_ibfk_2` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)) mysql> insert into student values(4,'ll',4,2); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test4`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)) mysql> select * from student; +----+------+-----------+----------+ | id | name | course_id | class_id | +----+------+-----------+----------+ | 1 | xx | 2 | 2 | | 2 | yy | 3 | 1 | | 3 | zz | 1 | 2 | +----+------+-----------+----------+ 3 rows in set (0.00 sec)
我们可以看到,在插入主表不存在的主键时,插入会不成功!
- 在查询语句
select * from student
后,我们只能看到主表对应的id,如果我们想查询到学生对应id
对应的名字,可以使用下面语句(后面会讲,这里先用):mysql> SELECT -> s.name AS student_name, -> c.name AS course_name, -> cl.name AS class_name -> FROM -> student s -> INNER JOIN course c ON s.course_id = c.id -> INNER JOIN class cl ON s.class_id = cl.id; +--------------+-------------------+---------------+ | student_name | course_name | class_name | +--------------+-------------------+---------------+ | xx | Linux系统编程 | C++方向2班 | | yy | Linux网络编程 | C++方向1班 | | zz | C++程序设计 | C++方向2班 | +--------------+-------------------+---------------+ 3 rows in set (0.01 sec) mysql>
OKOK,MySQL表的约束就到这里,如果你对Linux和C++也感兴趣的话,可以看看我的主页哦。下面是我的github主页,里面记录了我的学习代码和leetcode的一些题的题解,有兴趣的可以看看。
原文地址:https://blog.csdn.net/qq_44121078/article/details/140269564
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!