MySQL表的約束(5)

文章目錄

前言
一、空屬性
二、默認值
三、列描述
四、zerofill
五、主鍵
六、自增長
七、唯一鍵
八、外鍵
總結

前言

??真正約束字段的是數據類型，但是數據類型約束很單一，需要有一些額外的約束，更好的保證數據的合法性，從業務邏輯角度保證數據的正確性。這就是表的約束由來~

一、空屬性

??數據庫默認字段基本都是允許為空的，但在實際開發中我們要盡可能保證字段不為空，因為空值無法參與運算。

在這里插入圖片描述
??如果要讓某個字段不允許為空，在創建表的時候就可以給對應字段設置 not null 屬性。比如我們創建一個班級表，表當中包含班級名和該班級所在的教室，如果插入數據時不想讓這兩個字段為空，就可以在創建表時給這兩個字段設置 not null 屬性

create table if not exists myclass(class_name varchar(20) not null,class_room varchar(20) not null,other varchar(20)
);

在這里插入圖片描述

在這里插入圖片描述
??我們發現other這一列我們是只寫了一個 varchar ，沒有指定 not null ，默認是 null 的，然后面加了一個 default null
??這里表示你想插就插，不插這一類就給默認值null。

??那我們就來插入個數據試試水：

??改個配色方案了，之前一直黑底白字，現在改為白底黑字換個口味

在這里插入圖片描述

所以說，如果某列設置了not null

必須要插具體值
不插因為后面沒有默認值就報錯
插入null也報錯

如果設置為默認null，可以不插用的是后面帶的默認值。

二、默認值

如果某一個字段會經常性的出現某個值，那么就可以考慮將這個值設置成該字段的默認值
向表中插入數據時如果不給帶有默認值的字段賦值，那么就會使用默認值進行插入

??可以說默認值的目的就是為了可以簡化數據插入操作，提高數據一致性。

create table if not exists student(name varchar(20) not null,age tinyint unsigned default 18,gender varchar(10) default '男'
);

??在此表中，age 默認值為 18，gender 默認值為“男”。當插入數據時，如未指定這些列的值，將使用默認值

在這里插入圖片描述
總結：default 和 not nul 并不沖突，而是互相補充的。

當用戶指明這一列要插的時候，受 null 和 not null 約束，要么插 null ，要么插合法數據。
總之用戶指明這一列要插，not null 來約束。
當用戶忽略這一列的時候，如果設置了默認值使用默認值，如果沒有就直接報錯。
總結用戶忽略這一列要插， default 來約束

??如果建表的時候，不給某一列添加任何約束，我們會發現MySQL會對 sql語句優化，默認會帶上default null。所以不插入的時候在表示會顯示null。

三、列描述

comment：用于給列添加注釋說明，便于程序員和數據庫管理員理解字段用途。
該屬性不會對數據插入產生約束效果。

create table if not exists t5(name varchar(20) not null comment '用戶的用戶名',age tinyint unsigned default 18 comment '用戶的年齡'
);

在這里插入圖片描述

四、zerofill

zerofill：在數字前補零，使顯示字符長度符合指定的位數。
數據庫存儲的數值不變，僅用于展示效果。

create table if not exists t6(a int unsigned not null,b int unsigned zerofill not null
);

在這里插入圖片描述
??這個 int(10) unsigned zerofill 的 10 意味著什么呢？請看下文

??我們會發現，zerofill 還是挺形象的，填滿 0~
??如果以后你想顯示出的是001，002就可以設置zerofill。

??這里其實有個小細節，為什么 int() 后面帶的是 10 ？更準確的是為什么 unsigned int() 是10，int() 是11？

??int占4個字節，有符號取值范圍 -2^31 - 2^31-1，無符號取值范圍 2^32-1，無論是 2^31，還是 2^32 轉成10進制是21億多，42億多，這么大的數字其實最后表示處理也就是10位，8位千萬，9位億，10位十億。

??用10就可以把所有整數數據位全都表示出來。

??而 int 多出一個1是因為它是有符號的，多一位標識符號位。

五、主鍵

primary key：用于標識表中的唯一記錄，不允許重復或為空。
表中最多只能有一個主鍵列。
主鍵可以通過復合主鍵的方式使用多列聯合唯一標識。

create table if not exists t8(id int unsigned primary key,name varchar(20) not null
);

??在此示例中，id 被設為主鍵，數據庫自動為主鍵列添加 not null 約束。

在這里插入圖片描述

主鍵約束對于程序員來講，未來想往這個表里面插對應插入的數據主鍵列不能沖突，一旦沖突不讓你插入，所以倒逼程序員插的時候盡量不要出現主鍵沖突。
其次站在 mysql視角凡是插入這個表里面的數據主鍵一定是不沖突的。這樣的好處是根據主鍵絕對能拿出來確定的一條記錄！–唯一性
有了主鍵可以有針對性的對數據進行增刪查改

在這里插入圖片描述
創建主鍵有兩種方法：

創建表的時候就把主鍵設置好
表建好之和但沒有主鍵，可以追加主鍵

接下來就是刪除 | 添加主鍵的SQL 語句
在這里插入圖片描述

雖然一張表中最多只能有一個主鍵，但是并不意味著一個表中的主鍵只能添加給一列！
也就是說一個主鍵可以被添加到一列，或者多列上。
一個主鍵被添加到多列上的數據我們就叫做復合主鍵

??在創建表的時候，在所有字段之后，使用 primary key(主鍵字段列表) 來創建主鍵，如果有多個字段作為主鍵，可以使用復合主鍵。

??下面我們就可以用兩列合起來當一個主鍵，如創建一張表讓一個學生不能選修同樣的課程

create table t9(id int unsigned,course_id int unsigned comment '課程編號',score tinyint unsigned comment '課程得分',primary key(id,course_id));

??可以看到 id 和 course_id 都叫做主鍵。

在這里插入圖片描述
??因為一位同學可以選修多門課，而一門課又可以被多位同學選，所以我們把學生id 和課程id 合為一個主鍵，這就是復合主鍵的一個實際運用

在這里插入圖片描述

換言之，可以選擇一列作為主鍵，也可以選擇多列作為主鍵
但是多個合起來做一個主鍵，都不一樣可以插，有一個不一樣可以插，只有多個同時和歷史數據一樣才會出現主鍵沖突。這就是復合主鍵。
復合主鍵理解：將多列看成一個整體，全部同時沖突，才會約束
后面可以支持通過主鍵進行快速查找。

六、自增長

就是字段自動增長，從當前最大值加 1，通常配合主鍵使用，確保值唯一。

任何一個字段要做自增長，前提：本身是一個索引（key一欄有值）
自增長字段必須是整數
一張表最多只能有一個自增長

create table t10(id int unsigned primary key auto_increment,name varchar(20) not null
);

create table t10(id int unsigned primary key auto_increment,name varchar(20) not null
);

在這里插入圖片描述
??在此示例中，id 列自增長，無需顯式插入，系統自動為其賦值。

在這里插入圖片描述
??插入時，我們可以指定插入其他列，id這一列就不管了。可以看到雖然我并沒有告訴id要插什么，但是id是自動幫我們插入的，并且是增長的。

??和別人不沖突并且連續的，這就是自增長主鍵。

??當我們指定id要插入的時候，也能插進行。然后再插入id相同值的時候，確實能夠履行主鍵的職責發生主鍵沖突。

自增主鍵的插入機制

默認行為：自增主鍵在插入時若未設置任何默認值，則默認從1開始插入。
手動設置起始值：如果手動插入一個新的起始值，且該值大于歷史值，則自增主鍵將從新的起始值開始進行插入。
創建表時，除了在表內設置 auto_increment約束外，還可以在表外設置auto_increment 的值，這代表下一次插入的起始值。
可以使用 last_insert_id() 函數來獲取最后一次插入的 AUTO_INCREMENT 值。

七、唯一鍵

??在一張表中，唯一鍵用于對多個需要唯一性約束的字段進行限制。雖然表中只能有一個主鍵，但可以使用多個唯一鍵來確保數據唯一性。

區別：

主鍵：標識唯一性，主要用于唯一標識記錄。
唯一鍵：更多地用于業務邏輯上的唯一性約束，允許字段為空，并且多個空值不會影響唯一性比較。

mysql> create table student (-> id char(10) unique comment '學號，不能重復，但可以為空',-> name varchar(10)-> );mysql> insert into student(id, name) values('01', 'aaa');
mysql> insert into student(id, name) values('01', 'bbb'); -- 觸發唯一約束錯誤
ERROR 1062 (23000): Duplicate entry '01' for key 'id'mysql> insert into student(id, name) values(null, 'bbb'); -- 允許為空

在這里插入圖片描述

??唯一鍵和主鍵不沖突，可以理解為對主鍵的補充設置，并且只能有一個主鍵，但可以有多個唯一鍵

八、外鍵

對于外鍵，我們主要理解兩個內容：

從表和主表的關聯關系
產生外鍵約束

外鍵用于確保表間數據的一致性，例如防止插入一個不存在班級的學生或刪除一個還有學生的班級。

定義：外鍵約束用于建立主表和從表之間的關聯關系，主要定義在從表上，主表必須包含主鍵或唯一鍵。

外鍵用于定義主表和從表之間的關系：

外鍵約束主要定義在從表上
主表則必須是有主鍵約束或unique約束
當定義外鍵后，要求外鍵列數據必須在主表的主鍵列存在或為NULL

設置外鍵約束SQL語句：

foreign key (字段名) references 主表(列)

-- 主鍵表：班級表
create table 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)
);-- 插入班級數據
mysql> insert into myclass values(10, '高三(9)班'),(20, '高三(19)班');-- 插入學生數據
mysql> insert into stu values(100, '張三', 10), (101, '李四', 20);-- 插入無效班級號的數據，觸發外鍵約束錯誤
mysql> insert into stu values(102, '王五', 30); -- 無效插入