一、關(guān)系型數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)則

遵循ER模型和三范式

• E entity 代表實(shí)體的意思 對(duì)應(yīng)到數(shù)據(jù)庫(kù)當(dāng)中的一張表
• R relationship 代表關(guān)系的意思

三范式：

1、列不能拆分

2、唯一標(biāo)識(shí)

3、關(guān)系引用主鍵

具體體現(xiàn)

• 將數(shù)據(jù)放到表中，表再放到庫(kù)中。
• 一個(gè)數(shù)據(jù)庫(kù)中可以有多個(gè)表，每個(gè)表都有一個(gè)名字，用來(lái)標(biāo)識(shí)自己。表名具有唯一性。
• 表具有一些特性，這些特性定義了數(shù)據(jù)在表中如何存儲(chǔ)，類似java和python 中 “類”的設(shè)計(jì)。
• 表由列組成，我們也稱為字段。每個(gè)字段描述了它所含有的數(shù)據(jù)的意義，數(shù)據(jù)表的設(shè)計(jì)實(shí)際上就是對(duì)字段的設(shè)計(jì)。創(chuàng)建數(shù)據(jù)表時(shí)，為每個(gè)字段分配一個(gè)數(shù)據(jù)類型，定義它們的數(shù)據(jù)長(zhǎng)度和字段名。每個(gè)字段類似java 或者python中的“實(shí)例屬性”。
• 表中的數(shù)據(jù)是按行存儲(chǔ)的，一行即為一條記錄。每一行類似于java或python中的“對(duì)象”。

二、數(shù)據(jù)完整性和約束與索引的概念

1、數(shù)據(jù)完整性（Data Integrity）是指數(shù)據(jù)的精確性（Accuracy）和可靠性（Reliability。它是應(yīng)防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤信息的輸入輸出造成無(wú)效操作或錯(cuò)誤信息而提出的。

數(shù)據(jù)的完整性要從以下四個(gè)方面考慮：

• 實(shí)體完整性（Entity Integrity）：例如，同一個(gè)表中，不能存在兩條完全相同無(wú)法區(qū)分的記錄
• 域完整性（Domain Integrity）：例如：年齡范圍0-120，性別范圍“男/女”
• 引用完整性（Referential Integrity）：例如：?jiǎn)T工所在部門(mén)，在部門(mén)表中要能找到這個(gè)部門(mén)
• 用戶自定義完整性（User-defined Integrity）：例如：用戶名唯一、密碼不能為空等，本部門(mén)經(jīng)理的工資不得高于本部門(mén)職工的平均工資的5倍。

2、約束（CONSTRAINTS）

約束是用來(lái)對(duì)數(shù)據(jù)業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性進(jìn)行實(shí)施、維護(hù)。約束的作用范圍僅限在當(dāng)前數(shù)據(jù)庫(kù)，約束可以被當(dāng)做數(shù)據(jù)庫(kù)對(duì)象來(lái)處理，它們具有名稱和關(guān)聯(lián)模式，是邏輯約束，不會(huì)因?yàn)樵O(shè)置約束而額外占用空間。

3、索引（INDEX）

索引是一個(gè)單獨(dú)、物理的存儲(chǔ)在數(shù)據(jù)頁(yè)上的數(shù)據(jù)庫(kù)結(jié)構(gòu)，它是表中一列或若干列值的集合和相應(yīng)的指向表中數(shù)據(jù)值的物理標(biāo)識(shí)數(shù)據(jù)頁(yè)的邏輯指針清單（類似于新華字典的目錄索引頁(yè)）。索引的存在會(huì)增加數(shù)據(jù)庫(kù)的存儲(chǔ)空間，也會(huì)使插入、修改數(shù)據(jù)的時(shí)間開(kāi)銷變多(因?yàn)椴迦牒托薷臄?shù)據(jù)時(shí)，索引也要隨之變動(dòng)），但是可以大大提高查詢速度。因此應(yīng)該在鍵列、或其他經(jīng)常要查詢、排序、按范圍查找的列上建立索引，而對(duì)于在查詢中很少使用和參考的列、修改非常頻繁的列，值很少的列（例如性別只有男和女）等列上不應(yīng)該創(chuàng)建索引。

①M(fèi)ysql會(huì)在主鍵、唯一鍵、外鍵列上自動(dòng)創(chuàng)建索引，其他列需要建立索引的話，需要手動(dòng)創(chuàng)建。

②主鍵刪除，對(duì)應(yīng)的索引也會(huì)刪除

③刪除唯一鍵的方式是通過(guò)刪除對(duì)應(yīng)的索引來(lái)實(shí)現(xiàn)的

④刪除外鍵，外鍵列上的索引還在，如果需要?jiǎng)h除，需要單獨(dú)刪除索引

三、約束的應(yīng)用

1、查看某個(gè)表的約束和索引

#查看某個(gè)表的約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
或
SHOW CREATE TABLE 表名;
#查看某個(gè)表的索引
SHOW INDEX FROM 表名稱;

2、主鍵約束：primary key

（1）主鍵分為單列主鍵和復(fù)合主鍵（復(fù)合主鍵不建議使用，因?yàn)閺?fù)合主鍵違背三范式。）：

#單個(gè)字段設(shè)置主鍵
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)
);
#多個(gè)字段設(shè)置聯(lián)合主鍵
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;

主鍵的特點(diǎn)：

• 1、一張表中只能有一個(gè)主鍵
• 2、設(shè)置為主鍵的字段的值唯一且非空
• 3、若主鍵有多個(gè)字段組成，此時(shí)不能在字段后面設(shè)置主鍵，應(yīng)該在所有字段后面使用"primary key(字段,字段)"
• 4、聯(lián)合主鍵中，組成主鍵的每個(gè)字段都非空，可以單獨(dú)重復(fù)，但是不能同時(shí)重復(fù)
• 5、創(chuàng)建主鍵會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引，同樣刪除主鍵對(duì)應(yīng)的索引也會(huì)刪除。

3、自增約束：auto_increment

create table t_user(
	id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
);
#建表后指定自增長(zhǎng)列
alter table [數(shù)據(jù)庫(kù).]表名 modify 自增字段名 數(shù)據(jù)類型 auto_increment;
#刪除自增約束
alter table 表名 modify 自增字段名 數(shù)據(jù)類型;

自增約束的特點(diǎn)：

1、設(shè)置為自增的字段必須為整型，且一張表中只有一個(gè)字段可以設(shè)置為自增，且該字段必須為鍵 并且要求非空。（即設(shè)置了鍵約束的字段，例如主鍵、唯一鍵、外鍵），一般只設(shè)置在主鍵上

2、設(shè)置為自增的字段，從1開(kāi)始自增；每次添加數(shù)據(jù)，都會(huì)在該字段最大值的基礎(chǔ)上+1

3、使字段自增的方式：

• 如果是空或者0，則實(shí)際插入的將是自動(dòng)增長(zhǎng)后的值。
• 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 (字段名列表);
#如果沒(méi)有指定約束名，(字段名列表)中只有一個(gè)字段的，默認(rèn)是該字段名，如果是多個(gè)字段的默認(rèn)是字段名列表的第1個(gè)字段名。也可以通過(guò)show index from 表名;來(lái)查看
#刪除唯一鍵約束
ALTER TABLE 表名稱 DROP INDEX 唯一性約束名;
#注意：如果忘記名稱，可以通過(guò)“show index from 表名稱;”查看

唯一鍵約束的特點(diǎn)：

• 1、設(shè)置唯一鍵約束的字段值唯一，但是可以為null
• 2、一張表可以設(shè)置多個(gè)唯一鍵約束，也可以設(shè)置聯(lián)合唯一鍵，即多個(gè)字段設(shè)置一個(gè)唯一約束，但是不能使用"unique key"寫(xiě)在字段后設(shè)置，必須寫(xiě)在所有字段后，使用"unique key(字段,字段)"
• 3、聯(lián)合唯一鍵要求組成唯一約束的字段可以單獨(dú)重復(fù)，不能同時(shí)重復(fù)
• 4、 MySQL會(huì)給唯一約束的列上默認(rèn)創(chuàng)建一個(gè)唯一索引。
• 5、刪除唯一鍵只能通過(guò)刪除對(duì)應(yīng)索引的方式刪除，刪除時(shí)需要指定唯一鍵索引名

5、非空約束：not null

create table t_user(
	id int primary key auto_increment,
    username varchar(20) unique key not null,
    password varchar(20)
);
#在建表后指定某個(gè)字段非空
ALTER TABLE 表名稱 MODIFY 字段名 數(shù)據(jù)類型 NOT NULL 【default 默認(rèn)值】;
#如果該字段原來(lái)設(shè)置了默認(rèn)值約束，要跟著一起再寫(xiě)一遍，否則默認(rèn)值約束會(huì)丟失
#取消某個(gè)字段非空
ALTER TABLE 表名稱 MODIFY 字段名 數(shù)據(jù)類型 【default 默認(rèn)值】;
#如果該字段原來(lái)設(shè)置了默認(rèn)值約束，要跟著一起再寫(xiě)一遍，否則默認(rèn)值約束會(huì)丟失

非空約束的特點(diǎn)：

設(shè)置為非空約束的字段的值不能為null

6、默認(rèn)值約束：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 '男'
);

添加數(shù)據(jù)時(shí)使用默認(rèn)值的方式：

不為該字段賦值或使用關(guān)鍵字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); //此方式不可以，會(huì)為該字段賦值為null

7、外鍵約束：foreign key

表關(guān)系：

1、一對(duì)一

2、多對(duì)一，在多的一方引用一的主鍵

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

3、一對(duì)多，在多的一方引用一的主鍵

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

4、多對(duì)多

• 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) 
    #外鍵只能在所有字段列表后面單獨(dú)指定
);
#在建表后指定外鍵約束
alter table 從表名稱 add 【constraint 外鍵約束名】 foreign key (從表字段名) references 主表名(主表被參照字段名) 【on update xx】[on delete xx];
#刪除外鍵約束
ALTER TABLE 表名稱 DROP FOREIGN KEY 外鍵約束名;
#查看某個(gè)表的約束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
或
SHOW CREATE TABLE 表名;
#刪除外鍵約束不會(huì)刪除對(duì)應(yīng)的索引，如果需要?jiǎng)h除索引，需要用ALTER TABLE 表名稱 DROP INDEX 索引名;
#查看索引名 show index from 表名稱;

（1）外鍵特點(diǎn)

• 外鍵約束是保證一個(gè)或兩個(gè)表之間的參照完整性,外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系。
• 在創(chuàng)建外鍵約束時(shí)，如果不給外鍵約束名稱，默認(rèn)名不是列名，而是自動(dòng)產(chǎn)生一個(gè)外鍵名（例如 student_ibfk_1;），也可以指定外鍵約束名。
• 當(dāng)創(chuàng)建外鍵約束時(shí)，系統(tǒng)默認(rèn)會(huì)在所在的列上建立對(duì)應(yīng)的普通索引。但是索引名是列名，不是外鍵的約束名。
• 刪除外鍵時(shí)，關(guān)于外鍵列上的普通索引需要單獨(dú)刪除。

（2）要求

• 在從表上建立外鍵，而且主表要先存在。
• 一個(gè)表可以建立多個(gè)外鍵約束
• 從表的外鍵列，在主表中引用的只能是鍵列（主鍵，唯一鍵，外鍵），推薦引用主表的主鍵。
• 從表的外鍵列與主表被參照的列名字可以不相同，但是數(shù)據(jù)類型必須一樣

（3）約束關(guān)系：約束是針對(duì)雙方的

• 添加了外鍵約束后，主表的修改和刪除數(shù)據(jù)受約束
• 添加了外鍵約束后，從表的添加和修改數(shù)據(jù)受約束
• 在從表上建立外鍵，要求主表必須存在
• 刪除主表時(shí)，要求從表先刪除，或?qū)谋碇型怄I引用該主表的關(guān)系先刪除

（4）5個(gè)約束等級(jí)

• Cascade方式：在父表上update/delete記錄時(shí)，同步update/delete掉子表的匹配記錄
• Set null方式：在父表上update/delete記錄時(shí)，將子表上匹配記錄的列設(shè)為null，但是要注意子表的外鍵列不能為not null
• No action方式：如果子表中有匹配的記錄,則不允許對(duì)父表對(duì)應(yīng)候選鍵進(jìn)行update/delete操作
• Restrict方式：同no action, 都是立即檢查外鍵約束
• Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時(shí),子表將外鍵列設(shè)置成一個(gè)默認(rèn)的值，但I(xiàn)nnodb不能識(shí)別

如果沒(méi)有指定等級(jí)，就相當(dāng)于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,'李四','妖');
使用枚舉類型解決如上問(wèn)題：
create table stu(
	sid int primary key,
	sname varchar(20),
	gender enum ('男','女')
);

到此這篇關(guān)于MySQL約束與索引概念詳解的文章就介紹到這了,更多相關(guān)MySQL約束與索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論