MySQL約束與索引概念詳解

2023-04-08 06:02:15

一、關係型資料庫設計規則

遵循ER模型和三正規化

E entity 代表實體的意思對應到資料庫當中的一張表
R relationship 代表關係的意思

三正規化：

1、列不能拆分

2、唯一標識

3、關係參照主鍵

具體體現

將資料放到表中，表再放到庫中。
一個資料庫中可以有多個表，每個表都有一個名字，用來標識自己。表名具有唯一性。
表具有一些特性，這些特性定義了資料在表中如何儲存，類似java和python 中 “類”的設計。
表由列組成，我們也稱為欄位。每個欄位描述了它所含有的資料的意義，資料表的設計實際上就是對欄位的設計。建立資料表時，為每個欄位分配一個資料型別，定義它們的資料長度和欄位名。每個欄位類似java 或者python中的“範例屬性”。
表中的資料是按行儲存的，一行即為一條記錄。每一行類似於java或python中的“物件”。

二、資料完整性和約束與索引的概念

1、資料完整性（Data Integrity）是指資料的精確性（Accuracy）和可靠性（Reliability。它是應防止資料庫中存在不符合語意規定的資料和防止因錯誤資訊的輸入輸出造成無效操作或錯誤資訊而提出的。

資料的完整性要從以下四個方面考慮：

實體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
域完整性（Domain Integrity）：例如：年齡範圍0-120，性別範圍“男/女”
參照完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門
使用者自定義完整性（User-defined Integrity）：例如：使用者名稱唯一、密碼不能為空等，本部門經理的工資不得高於本部門職工的平均工資的5倍。

2、約束（CONSTRAINTS）

約束是用來對資料業務規則和資料完整性進行實施、維護。約束的作用範圍僅限在當前資料庫，約束可以被當做資料庫物件來處理，它們具有名稱和關聯模式，是邏輯約束，不會因為設定約束而額外佔用空間。

3、索引（INDEX）

索引是一個單獨、物理的儲存在資料頁上的資料庫結構，它是表中一列或若干列值的集合和相應的指向表中資料值的物理標識資料頁的邏輯指標清單（類似於新華字典的目錄索引頁）。索引的存在會增加資料庫的儲存空間，也會使插入、修改資料的時間開銷變多(因為插入和修改資料時，索引也要隨之變動），但是可以大大提高查詢速度。因此應該在鍵列、或其他經常要查詢、排序、按範圍查詢的列上建立索引，而對於在查詢中很少使用和參考的列、修改非常頻繁的列，值很少的列（例如性別只有男和女）等列上不應該建立索引。

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

②主鍵刪除，對應的索引也會刪除

③刪除唯一鍵的方式是通過刪除對應的索引來實現的

④刪除外來鍵，外來鍵列上的索引還在，如果需要刪除，需要單獨刪除索引

三、約束的應用

1、檢視某個表的約束和索引

#檢視某個表的約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
或
SHOW CREATE TABLE 表名;
#檢視某個表的索引
SHOW INDEX FROM 表名稱;

2、主鍵約束：primary key

（1）主鍵分為單列主鍵和複合主鍵（複合主鍵不建議使用，因為複合主鍵違背三正規化。）：

#單個欄位設定主鍵
create table t_user(
	id int primary key,
    username varchar(20),
    password varchar(20)
);
create table t_user(
	id int,
    username varchar(20),
    password varchar(20),
    primary key(id)
);
#多個欄位設定聯合主鍵
drop table t_user;
create table t_user(
	id int,
    username varchar(20),
    password varchar(20),
    primary key(id,username)
);
#瞭解
#在建表後指定主鍵約束
alter table 表名稱 add primary key (主鍵欄位列表);
#刪除主鍵約束
alter table 表名稱 drop primary key;

主鍵的特點：

1、一張表中只能有一個主鍵
2、設定為主鍵的欄位的值唯一且非空
3、若主鍵有多個欄位組成，此時不能在欄位後面設定主鍵，應該在所有欄位後面使用"primary key(欄位,欄位)"
4、聯合主鍵中，組成主鍵的每個欄位都非空，可以單獨重複，但是不能同時重複
5、建立主鍵會自動建立對應的索引，同樣刪除主鍵對應的索引也會刪除。

3、自增約束：auto_increment

create table t_user(
	id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
);
#建表後指定自增長列
alter table [資料庫.]表名 modify 自增欄位名 資料型別 auto_increment;
#刪除自增約束
alter table 表名 modify 自增欄位名 資料型別;

自增約束的特點：

1、設定為自增的欄位必須為整型，且一張表中只有一個欄位可以設定為自增，且該欄位必須為鍵並且要求非空。（即設定了鍵約束的欄位，例如主鍵、唯一鍵、外來鍵），一般只設定在主鍵上

2、設定為自增的欄位，從1開始自增；每次新增資料，都會在該欄位最大值的基礎上+1

3、使欄位自增的方式：

如果是空或者0，則實際插入的將是自動增長後的值。
a> insert into t_user(username,password) values(‘admin’,‘123456’);
b> insert into t_user values(null,‘root’,‘123456’); (推薦使用)
c> insert into t_user values(0,‘root’,‘123456’);

4、唯一鍵約束：unique key

create table t_user(
	id int primary key auto_increment,
    username varchar(20) unique key,
    password varchar(20) unique key
);
create table t_user(
	id int primary key auto_increment,
    username varchar(20),
    password varchar(20),
    unique key(username,password)
);
#在建表後增加唯一鍵約束
alter table 表名稱 add 【constraint 約束名】 unique key (欄位名列表);
#如果沒有指定約束名，(欄位名列表)中只有一個欄位的，預設是該欄位名，如果是多個欄位的預設是欄位名列表的第1個欄位名。也可以通過show index from 表名;來檢視
#刪除唯一鍵約束
ALTER TABLE 表名稱 DROP INDEX 唯一性約束名;
#注意：如果忘記名稱，可以通過「show index from 表名稱;」檢視

唯一鍵約束的特點：

1、設定唯一鍵約束的欄位值唯一，但是可以為null
2、一張表可以設定多個唯一鍵約束，也可以設定聯合唯一鍵，即多個欄位設定一個唯一約束，但是不能使用"unique key"寫在欄位後設定，必須寫在所有欄位後，使用"unique key(欄位,欄位)"
3、聯合唯一鍵要求組成唯一約束的欄位可以單獨重複，不能同時重複
4、 MySQL會給唯一約束的列上預設建立一個唯一索引。
5、刪除唯一鍵只能通過刪除對應索引的方式刪除，刪除時需要指定唯一鍵索引名

5、非空約束：not null

create table t_user(
	id int primary key auto_increment,
    username varchar(20) unique key not null,
    password varchar(20)
);
#在建表後指定某個欄位非空
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 NOT NULL 【default 預設值】;
#如果該欄位原來設定了預設值約束，要跟著一起再寫一遍，否則預設值約束會丟失
#取消某個欄位非空
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 【default 預設值】;
#如果該欄位原來設定了預設值約束，要跟著一起再寫一遍，否則預設值約束會丟失

非空約束的特點：

設定為非空約束的欄位的值不能為null

6、預設值約束：default

create table t_user(
	id int primary key auto_increment,
    username varchar(20) unique key not null,
    password varchar(20),
    gender char not null default '男'
);

新增資料時使用預設值的方式：

不為該欄位賦值或使用關鍵字default

insert into t_user(username,password) values(‘root’,‘123’);
insert into t_user values(null,‘admin123’,‘123’,default);
insert into t_user values(null,‘admin’,‘123’,null); //此方式不可以，會為該欄位賦值為null

7、外來鍵約束：foreign key

表關係：

1、一對一

2、多對一，在多的一方參照一的主鍵

student(sid,sname,age,sex,cid)–clazz(cid,cname,location)

3、一對多，在多的一方參照一的主鍵

clazz(cid,cname,location)–student(sid,sname,age,sex,cid)

4、多對多

user(uid,username,password)
order(oid,create_time,total_count,total_amount,status,user_id)
order_goods(id,oid,gid)
goods(gid,gname,price,sales,stock)

create table t_dept(
	id int primary key auto_increment,
    name varchar(20)
);
create table t_emp(
	id int primary key auto_increment,
    name varchar(20),
    age int,
    gender char,
    dept_id int,
    foreign key(dept_id) references t_dept(id) 
    #外來鍵只能在所有欄位列表後面單獨指定
);
#在建表後指定外來鍵約束
alter table 從表名稱 add 【constraint 外來鍵約束名】 foreign key (從表欄位名) references 主表名(主表被參照欄位名) 【on update xx】[on delete xx];
#刪除外來鍵約束
ALTER TABLE 表名稱 DROP FOREIGN KEY 外來鍵約束名;
#檢視某個表的約束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
或
SHOW CREATE TABLE 表名;
#刪除外來鍵約束不會刪除對應的索引，如果需要刪除索引，需要用ALTER TABLE 表名稱 DROP INDEX 索引名;
#檢視索引名 show index from 表名稱;

（1）外來鍵特點

外來鍵約束是保證一個或兩個表之間的參照完整性,外來鍵是構建於一個表的兩個欄位或是兩個表的兩個欄位之間的參照關係。
在建立外來鍵約束時，如果不給外來鍵約束名稱，預設名不是列名，而是自動產生一個外來鍵名（例如 student_ibfk_1;），也可以指定外來鍵約束名。
當建立外來鍵約束時，系統預設會在所在的列上建立對應的普通索引。但是索引名是列名，不是外來鍵的約束名。
刪除外來鍵時，關於外來鍵列上的普通索引需要單獨刪除。

（2）要求

在從表上建立外來鍵，而且主表要先存在。
一個表可以建立多個外來鍵約束
從表的外來鍵列，在主表中參照的只能是鍵列（主鍵，唯一鍵，外來鍵），推薦參照主表的主鍵。
從表的外來鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣

（3）約束關係：約束是針對雙方的

新增了外來鍵約束後，主表的修改和刪除資料受約束
新增了外來鍵約束後，從表的新增和修改資料受約束
在從表上建立外來鍵，要求主表必須存在
刪除主表時，要求從表先刪除，或將從表中外來鍵參照該主表的關係先刪除

（4）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方式

8、檢查約束：check

檢查約束，mysql暫不支援

create table stu(
	sid int primary key,
	sname varchar(20),
	gender char check ('男'or'女')
);
insert into stu values(1,'張三','男');
insert into stu values(2,'李四','妖');
使用列舉型別解決如上問題：
create table stu(
	sid int primary key,
	sname varchar(20),
	gender enum ('男','女')
);

到此這篇關於MySQL約束與索引概念詳解的文章就介紹到這了,更多相關MySQL約束與索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL約束與索引概念詳解

目錄

一、關係型資料庫設計規則

二、資料完整性和約束與索引的概念

三、約束的應用

熱門文章