Mysql基礎02-約束

約束與索引

概念

1、資料完整性（Data Integrity）是指資料的精確性（Accuracy）和可靠性（Reliability），

• 物體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄

• 域完整性（Domain Integrity）：例如：年齡范圍0-120，性別范圍“男/女”

• 參考完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門

• 用戶自定義完整性（User-defined Integrity）：例如：用戶名唯一、密碼不能為空等

2、約束

• 鍵約束：主鍵約束、外鍵約束、唯一鍵約束

• Not NULL約束：非空約束

• Check約束：檢查約束

• Default約束：默認值約束

• 自增約束

3、約束（CONSTRAINTS）與索引（INDEX）

約束是用來對資料業務規則和資料完整性進行實施、維護，約束的作用范圍僅限在當前資料庫，是邏輯約束，不會因為設定約束而額外占用空間，

索引是一個單獨、物理的存盤在資料頁上的資料庫結構，它是表中一列或若干列值的集合和相應的指向表中資料值的物理標識資料頁的邏輯指標清單（類似于新華字典的目錄索引頁），可以大大提高查詢速度，應該在鍵列、或其他經常要查詢、排序、按范圍查找的列上建立索引，

Mysql會在主鍵、唯一鍵、外鍵列上自動創建索引，其他列需要建立索引的話，需要手動創建，

其中主鍵洗掉，對應的索引也會洗掉

洗掉唯一鍵的方式是通過洗掉對應的索引來實作的

洗掉外鍵，外鍵列上的索引還在，如果需要洗掉，需要單獨洗掉索引

查看某個表的約束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
或
SHOW CREATE TABLE 表名;

查看某個表的索引

SHOW INDEX FROM 表名稱;

主鍵約束：primary key

主鍵分為單列主鍵和復合主鍵

主鍵的特點：

（1）唯一并且非空（2）一個表只能有一個主鍵約束（3）主鍵約束名就叫做PRIMARY（4）創建主鍵會自動創建對應的索引，同樣洗掉主鍵對應的索引也會洗掉，

建表時指定主鍵約束

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　....
);
或
create table 【資料名.】表名(
　　欄位名1 資料型別,
　　....,
　　primary key(欄位名1)
);
或
create table 【資料名.】表名(
　　欄位名1 資料型別,
　　欄位名2 資料型別,
　　....,
　　primary key(復合主鍵欄位串列)　　#如果是復合主鍵，那么就需要在所有欄位串列后面使用這種形式指定，不能在欄位后面直接加primary key
);

建表后指定主鍵約束

alter table 表名稱 add primary key (主鍵欄位串列);

洗掉主鍵約束

alter table 表名稱 drop primary key;

唯一鍵約束：unique key

唯一鍵約束特點:

(1)同一個表可以有多個唯一約束，(2)唯一約束可以是某一個列的值唯一，也可以多個列組合值的唯一，(3)MySQL會給唯一約束的列上默認創建一個唯一索引，(4)洗掉唯一鍵只能通過洗掉對應索引的方式洗掉，洗掉時需要指定唯一鍵索引名

建表時指定唯一鍵約束

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別 unique key,
　　....
);

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別,
　　欄位名3 資料型別,
　　....,
　　unique key(復合唯一欄位串列)　　#如果是復合唯一鍵，那么就需要在所有欄位串列后面使用這種形式指定，不能在欄位后面直接加unique key
);

建表后增加唯一鍵約束

alter table 表名稱 add 【constraint 約束名】 unique 【key】 (欄位名串列);

#如果沒有指定約束名，(欄位名串列)中只有一個欄位的，默認是該欄位名，如果是多個欄位的默認是欄位名串列的第1個欄位名，

洗掉唯一鍵約束

ALTER TABLE 表名稱 DROP INDEX 唯一鍵約束名;

外鍵約束：foreign key

外鍵特點：

• 外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構建于一個表的兩個欄位或是兩個表的兩個欄位之間的參照關系，

• 在創建外鍵約束時，如果不給外鍵約束名稱，默認名不是列名，而是自動產生一個外鍵名（例如 student_ibfk_1;），也可以指定外鍵約束名，

• 當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引，但是索引名是列名，不是外鍵的約束名，

• 洗掉外鍵時，關于外鍵列上的普通索引需要單獨洗掉，

要求

• 在從表上建立外鍵，而且主表要先存在，

• 一個表可以建立多個外鍵約束

• 從表的外鍵列，在主表中參考的只能是鍵列（主鍵，唯一鍵，外鍵），推薦參考主表的主鍵，

• 從表的外鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣

約束關系：約束是針對雙方的

添加了外鍵約束后，主表的修改和洗掉受約束

添加了外鍵約束后，從表的添加和修改受約束

5個約束等級

• Cascade方式：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄

• Set null方式：在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但是要注意子表的外鍵列不能為not null

• No action方式：如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作

• Restrict方式：同no action, 都是立即檢查外鍵約束

• Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時,子表將外鍵列設定成一個默認的值，但Innodb不能識別

如果沒有指定等級，就相當于Restrict方式

建表時指定外鍵約束

create table 【資料名.】從表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別 【unique key】,
　　....,
　　【constraint 外鍵約束名】 foreign key (從表欄位) references 主表名(主表欄位) 【on update 外鍵約束等級】【on delete 外鍵約束等級】
);

#外鍵只能在所有欄位串列后面單獨指定
#如果要自己命名外鍵約束名，建議 主表名_從表名_關聯欄位名_fk

建表后指定外鍵約束

alter table 從表名稱 add 【constraint 外鍵約束名】 foreign key (從表欄位名) references 主表名(主表被參照欄位名) 【on update xx】【on delete xx】;

洗掉外鍵約束

ALTER TABLE 表名稱 DROP FOREIGN KEY 外鍵約束名;

#洗掉外鍵約束不會洗掉對應的索引

洗掉索引

ALTER TABLE 表名稱 DROP INDEX 索引名;

非空約束：not null

規定某個欄位不能為空

建表時指定非空約束

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別 【unique key】 【not null】,
　　欄位名2 資料型別 【not null】,
　　....,
);

建表后指定非空約束

ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 NOT NULL 【default 默認值】;

#如果該欄位原來設定了默認值約束，要跟著一起再寫一遍，否則默認值約束會丟失

洗掉非空約束

ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 【default 默認值】;

#如果該欄位原來設定了默認值約束，要跟著一起再寫一遍，否則默認值約束會丟失

默認值約束：default

建表時指定默認約束

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別 【unique key】 【not null】 【default 默認值】,
　　欄位名3 資料型別 【not null】 【default 默認值】,
　　....,
);

建表后指定默認值約束

ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 【default 默認值】 【NOT NULL】;

#如果該欄位原來設定了非空約束，要跟著一起再寫一遍，否則非空約束會丟失

洗掉默認值約束

ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 【NOT NULL】;

#如果該欄位原來設定了非空約束，要跟著一起再寫一遍，否則非空約束會丟失

檢查約束，mysql暫不支持

自增約束：auto_increment

特點：

• 一個表最多只能有一個自增長列

• 自增長列必須是鍵列（主鍵列，唯一鍵列，外鍵列），并且要求非空，

• 自增列必須是整數型別

• InnoDB表的自動增長列可以手動插入，但是插入的值如果是慷訓者0，則實際插入的將是自動增長后的值，

建表時指定自增長

create table 【資料名.】表名(
　　欄位名1 資料型別 primary key auto_increment,
　　欄位名2 資料型別 【unique key】 【not null】 【default 默認值】,
　　....
);
或
create table 【資料名.】表名(
　　欄位名1 資料型別 primary key ,
　　欄位名2 資料型別 【unique key not null】 auto_increment,
　　....
);

建表后指定自增長

alter table 【資料名.】表名 modify 自增欄位名 資料型別 auto_increment;

洗掉自增約束

alter table 【資料名.】表名 modify 自增欄位名 資料型別;

添加資料時，如果某列有自增約束，怎么添加該欄位的值

insert into 【資料庫名.]表名稱 values(值串列)；#在值串列中，對應自增列可以賦值為null和0

insert into 【資料庫名.]表名稱(部分欄位串列) values(值串列)；#自增列在(部分欄位串列)中不寫就可以

添加資料時，如果某列有默認值約束，怎么添加、修改該欄位的值

insert into 【資料庫名.]表名稱 values(值串列)；#在值串列中，對應默認值列，如果想用默認值，用default

insert into 【資料庫名.]表名稱(部分欄位串列) values(值串列)；#對應默認值列，如果想用默認值，在(部分欄位串列)中不寫就可以

修改資料

update 【資料庫名.]表名稱 set 欄位名1 = 值1, 欄位名2 = 值2 ，，， 【where 條件】; #對應默認值列，如果想用默認值，寫欄位名 = default就可以

標籤：MySQL

上一篇：元類， pymysql

下一篇：MySQL面試總結