当前位置： 首页 > article >正文

MySQL之表的约束

article 2024/12/28 22:55:02

目录

前言

一：空属性

二：默认值

 三:列描述

四:zerofill

五:主键

六:自增长 

七:唯一键

八：外键

接下来的日子会顺顺利利，万事胜意，生活明朗-----------林辞忧

前言

表中一定要有各种约束，通过约束，让我们插入数据库表中的数据是符合预期的。约束的本质是通过技术手段，让使用者插入正确的数据，使其保证数据的完整性和可预期性

一：空属性

两个值：null（默认的）和not null(不为空)
数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算

举例：

mysql> create table t11(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t11 (class_name,class_room) values ('高三1班','基础一201教室');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t11 (class_name) values ('高三2班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value
mysql> insert into t11 (class_room) values ('基础一202教室');
ERROR 1364 (HY000): Field 'class_name' doesn't have a default value
mysql> insert into t11 (class_name,class_room) values (null,'基础一202教室');
ERROR 1048 (23000): Column 'class_name' cannot be null

mysql> insert into t11 (class_name,class_room) values (null,null);
ERROR 1048 (23000): Column 'class_name' cannot be null

 只要给列设置not null约束的话,就可以拦截任何插入null操作，在mysql看来，插入的数据是不为null,且必须插入对应值，对应列不能为空

二：默认值

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

default:如果设置了，用户将来插入，有具体的数据，就用用户的，没有就用默认的

如果我们没有明确指定一列要插入的话，用的是default;如果建表中，对应列默认没有设置default值，无法直接插入。

default(当用户忽略这一列的时候，使用默认值，没有设置的话会直接报错)与 not null(用户插入的是null/合法数据)是不冲突的，而是相互补充的，但not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

举例：

mysql> create table t1(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 0,
    -> gender char(2) default '男'
    -> );
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into t1 (name,age,gender) values ('刘备',30,'男');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (name) values ('孙权');
Query OK, 1 row affected (0.00 sec)

mysql> select* from t1;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 刘备   |   30 | 男     |
| 孙权   |    0 | 男     |
+--------+------+--------+

 三:列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

举例：

mysql> create table t2(
    -> name varchar(20) not null comment '姓名',
    -> age tinyint unsigned default 0 comment '年龄',
    -> gender char(2) default '男' comment '性别'
    -> );
Query OK, 0 rows affected (0.03 sec)

#使用desc 是查看不到comment描述的
mysql> desc t2;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 0       |       |
| gender | char(2)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#使用show create table 表名 \G 是可以查看到的
mysql> show create table t2 \G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
  `gender` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

四:zerofill

在创建数字类型时，对于后面默认带的数字长度我们是有点懵的，接下来将介绍这其中的知识

mysql> create table t3(
    -> a int unsigned,
    -> b int 
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t3 \G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 当建表时设置数据类型为int的话，显示时就会变为int(11),这是因为写的建表相关的sql会被mysqld接收，而mysqld会对sql语句做优化，执行的是show create table 表名 \G中的内容，而内容中会变为int(11),即创建时不带数字，而mysql会自动添加

zreofill是给特定的一列添加zerofill属性，结果为在显示的时候，如果显示的宽度小于限定的宽度，会自动填充0，反之，正常显示，显示的表格是等宽的

mysql> create table t4(
    -> id int(4) unsigned zerofill,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4  values (1,'刘备');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4  values (11111,'张飞');
Query OK, 1 row affected (0.00 sec)

mysql> select* from t4;
+-------+--------+
| id    | name   |
+-------+--------+
|  0001 | 刘备   |
| 11111 | 张飞   |
+-------+--------+
2 rows in set (0.00 sec)

mysql> select* from t4 where id=1;
+------+--------+
| id   | name   |
+------+--------+
| 0001 | 刘备   |
+------+--------+

 这里说明在数据库中存储的仍然是正常数据，不会添加0，zerofill不会影响存储，只影响显示

五:主键

(1) 当创建表要保存各种数据的时候，此时数据就需要有一种唯一性的数据来表示该数据信息的唯    一性

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

有了主键之后，可以针对性的准确根据主键来对想要的数据进行相关操作

默认当只设置主键时，同样的建表语句会自动添加not null非空约束

举例:

mysql> create table t5(
    -> id tinyint unsigned primary key comment '学号不能为空',
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t5 \G;
      
Create Table: CREATE TABLE `t5` (
  `id` tinyint(3) unsigned NOT NULL COMMENT '学号不能为空',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> desc t5;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)         | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+


ysql> insert into t5 (id ,name) values (1,'刘备');
Query OK, 1 row affected (0.00 sec)

#1发生主键冲突
mysql> insert into t5 (id ,name) values (1,'孙权');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t5 (id ,name) values (2,'孙权');
Query OK, 1 row affected (0.00 sec)

mysql> select* from t5;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 孙权   |
+----+--------+
2 rows in set (0.00 sec)

mysql> update t5 set name='曹操' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from t5;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 曹操   |
+----+--------+

(2) 删除主键

alter table 表名 drop primary key

(3)当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key 列名称

(4)复合主键

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> create table t6(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );

Query OK, 0 rows affected (0.01 sec)
mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | | <= 这两列合成主键
| course | char(10) | NO | PRI | | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
mysql> insert into tt14 (id,course)values(1, '123');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt14 (id,course)values(1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主键冲突

六:自增长 

(1)auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点:
任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表最多只能有一个自增长

(2)举例:

mysql> create table t6(
    -> id tinyint unsigned primary key auto_increment,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t6 (id,name) values (1,'刘备');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 (name) values ('曹操');
Query OK, 1 row affected (0.00 sec)

mysql> select* from t6;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 曹操   |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into t6 (id,name) values (10,'孙权');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 (name) values ('赵云');
Query OK, 1 row affected (0.01 sec)

mysql> select* from t6;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 曹操   |
| 10 | 孙权   |
| 11 | 赵云   |
+----+--------+

(3)当自增主键在做插入时，没有设置任何的默认值，默认是从1开始的，当手动插入一个起始值且比任何历史值都要大时，就会从新的起始值开始进行插入

那mysql是如何得知下次插入时的值的?

这是因为在创建表时，有一个auto_increment。可以通过select last_insert_id()这样的操作获取到最后一次插入的 AUTO_INCREMENT 的值

mysql> select* from t6;
+----+--------+
| id | name   |
+----+--------+
|  1 | 刘备   |
|  2 | 曹操   |
| 10 | 孙权   |
| 11 | 赵云   |
+----+--------+

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               11 |
+------------------+

 (4)除了在创建表时设置auto_increment这样的约束，还可以在表外设置对应的auto_increment值的，这个数字代表下次插入时的起始值

create table 表名(

...

)auto_increment=起始值;

(5)索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息

七:唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较

关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复，所以这两个是相互补充的，主键保证表中记录的唯一性，唯一键保证表中某列数据不出现重复，侧重点不一样

举例:

ysql> create table t7(
    -> id tinyint unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> telphone varchar(20) unique key
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)         | NO   |     | NULL    |                |
| telphone | varchar(20)         | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table t7 add qq varchar(20) unique key after telphone;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)         | NO   |     | NULL    |                |
| telphone | varchar(20)         | YES  | UNI | NULL    |                |
| qq       | varchar(20)         | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into t7 (name,telphone,qq) values ('刘备','1234567','1201');
Query OK, 1 row affected (0.01 sec)

#受到唯一键约束，不能插入，保证唯一性
mysql> insert into t7 (name,telphone,qq) values ('张飞','1234567','1201');
ERROR 1062 (23000): Duplicate entry '1234567' for key 'telphone'
mysql> insert into t7 (name,telphone,qq) values ('张飞','12345678','1201');
ERROR 1062 (23000): Duplicate entry '1201' for key 'qq'


mysql> insert into t7 (name,telphone,qq) values ('张飞','12345678','1202');
Query OK, 1 row affected (0.01 sec)

mysql> select* from t7;
+----+--------+----------+------+
| id | name   | telphone | qq   |
+----+--------+----------+------+
|  1 | 刘备   | 1234567  | 1201 |
|  4 | 张飞   | 12345678 | 1202 |
+----+--------+----------+------+

#唯一键允许为NULL,且允许插入多个NULL
mysql> insert into t7 (name,telphone,qq) values ('赵云',NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 (name,telphone,qq) values ('关羽',NULL,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select* from t7;
+----+--------+----------+------+
| id | name   | telphone | qq   |
+----+--------+----------+------+
|  1 | 刘备   | 1234567  | 1201 |
|  4 | 张飞   | 12345678 | 1202 |
|  5 | 赵云   | NULL     | NULL |
|  6 | 关羽   | NULL     | NULL |
+----+--------+----------+------+

唯一键是用来保证某一列的数据的唯一性的，只要我们设置了唯一键约束，这一列就不会出现重复内容(NULL除外) 

八：外键

在实际生活中常常有上述这样两个产生关联关系的表，此时我们在stu表中插入数据时，可能由于疏忽大意插入一个class_id对应不存在的班级，也可能在myclass表中删除id数据时，还对应有学生，这样的话，就会非常错乱，没有约束，因此这里使用外键来约束解决

在这里stu表称为从表，myclass表称为主表

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

语法:foreign key (从表列名) references 主表(主表列名)

mysql> create table myclass (
    -> id int primary key,
    -> name varchar(30) not null comment'班级名'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table stu (
    -> id int primary key,
    -> name varchar(30) not null comment '学生名',
    -> class_id int,
    -> foreign key (class_id) references myclass(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

#受到外键约束，不能插入不存在的数据/删除未空数据
mysql>  insert into stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`d2`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))
mysql> delete from myclass where id=20;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d2`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

因此外键的作用:产生约束关系/产生主从表之间的关联关系 


http://www.kler.cn/a/305945.html

相关文章：

  • 龙智出席2024零跑智能汽车技术论坛，分享功能安全、需求管理、版本管理、代码扫描等DevSecOps落地实践
  • Java 深拷贝全面解析
  • layui多图上传,tp8后端接收处理
  • 微积分复习(微分方程)
  • 【Go学习】从一个出core实战问题看Go interface赋值过程
  • 使用 pyreqs 快速创建 requirements.txt PyCharm 中 UnicodeDecodeError 问题
  • 基于springboot的校企招聘管理系统的设计与实现
  • HTTPS的加密流程：保护你的数据传输
  • 关于决策树的一些介绍（二）
  • 物联网之Arduino编程语言
  • 【stm32笔记】使用rtt-studio与stm32CubeMx联合创建项目
  • 鸿蒙 ArkUI组件一
  • 三十七、Gin完成登陆功能
  • solidity-20-sendeth
  • MySQL——数据库的高级操作（三）权限管理（2）授予权限
  • 自动驾驶自动泊车场景应用总结
  • RAII 与 std::lock_guard 在 C++ 中的应用：自动化互斥锁管理与线程安全
  • 【6大设计原则】迪米特法则：解密软件设计中的“最少知识原则”
  • 创建一个Java项目在IntelliJ IDEA中
  • 【AI绘画】Midjourney进阶：景别详解
  • 数学建模常用模型---“算法”总结（含特性和应用场景）
  • 聊天组件 Vue3-beautiful-chat
  • 【QT】实现TCP服务器，客户端之间的通信
  • 国风编曲：了解国风 民族调式 五声音阶 作/编曲思路 变化音 六声、七声调式
  • 【开源免费】基于SpringBoot+Vue.JS在线旅游网站（JAVA毕业设计）
  • 威胁建模网络与云威胁