MySQL數(shù)據(jù)庫(kù)高級(jí)（一）——數(shù)據(jù)完整性

一、數(shù)據(jù)完整性簡(jiǎn)介

1、數(shù)據(jù)完整性簡(jiǎn)介

數(shù)據(jù)冗余是指數(shù)據(jù)庫(kù)中存在一些重復(fù)的數(shù)據(jù)，數(shù)據(jù)完整性是指數(shù)據(jù)庫(kù)中的數(shù)據(jù)能夠正確反應(yīng)實(shí)際情況。
數(shù)據(jù)的完整性是指數(shù)據(jù)的可靠性和準(zhǔn)確性，數(shù)據(jù)完整性類型有四種：
A、實(shí)體完整性：實(shí)體的完整性強(qiáng)制表的標(biāo)識(shí)符列或主鍵的完整性(通過(guò)唯一約束，主鍵約束或標(biāo)識(shí)列屬性)。
B、域完整性：限制類型(數(shù)據(jù)類型)，格式(通過(guò)檢查約束和規(guī)則)，可能值范圍(通過(guò)外鍵約束，檢查約束，默認(rèn)值定義，非空約束和規(guī)則)。
C、引用完整性：在刪除和輸入記錄時(shí)，引用完整性保持表之間已定義的關(guān)系。引用完整性確保鍵值在所有表中一致，不能引用不存在的值.如果一個(gè)鍵。
D、自定義完整性：用戶自己定義的業(yè)務(wù)規(guī)則，比如使用觸發(fā)器實(shí)現(xiàn)自定義業(yè)務(wù)規(guī)則。

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到淮安區(qū)網(wǎng)站設(shè)計(jì)與淮安區(qū)網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名與空間、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋淮安區(qū)地區(qū)。

2、數(shù)據(jù)完整性實(shí)現(xiàn)方式

MySQL不支持Check約束，雖然可以在列上添加check約束，但不起作用。

二、實(shí)體完整性實(shí)現(xiàn)

1、實(shí)體完整性的實(shí)現(xiàn)簡(jiǎn)介

實(shí)體完整性的實(shí)現(xiàn)有兩種方式：
A、主鍵約束：一張表只能有一列設(shè)置主鍵，值必須唯一，不允許為空，innoDB存儲(chǔ)引擎，主鍵就是索引。
B、唯一值約束：一張表可以有多個(gè)列添加唯一值約束，一直允許一條記錄為空值。
實(shí)體完整性，由主鍵和唯一性約束來(lái)實(shí)現(xiàn)，確保表中記錄有一列唯一標(biāo)識(shí)。主鍵又分為Primary key 和AUTO_INCREMENT PRIMARY KEY兩種。

2、主鍵

MySQL的主鍵名總是PRIMARY， 當(dāng)創(chuàng)建主鍵約束時(shí)，如果表的存儲(chǔ)引擎是innoDB，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。
主鍵約束相當(dāng)于唯一約束與非空約束的組合，主鍵約束列不允許重復(fù)，也不允許出現(xiàn)空值；多列組合的主鍵約束，列都不允許為空值，并且組合的值不允許重復(fù)。每個(gè)表最多只允許一個(gè)主鍵，建立主鍵約束可以在列級(jí)別創(chuàng)建，也可以在表級(jí)別上創(chuàng)建。
A、創(chuàng)建表時(shí)指定主鍵
創(chuàng)建表時(shí)指定主鍵的方式一：

create table product
 (
 productID int PRIMARY KEY,
 pName VARCHAR(10),
 price DOUBLE
 )ENGINE=MyISAM default CHARSET=utf8;

創(chuàng)建表時(shí)指定主鍵的方式二：

create table product
 (
 productID int,
 pName VARCHAR(10),
 price DOUBLE,
 CONSTRAINT pk_s_productID PRIMARY KEY(productID)
 )ENGINE=MyISAM default CHARSET=utf8;

在指定主鍵的表中插入記錄時(shí)，不允許插入重復(fù)的ID，如果不指定主鍵的值，默認(rèn)為0。
MyISAM類型的存儲(chǔ)引擎不會(huì)在主鍵列上創(chuàng)建索引，表中記錄的存儲(chǔ)順序與插入順序相同。

InnoDB存儲(chǔ)引擎會(huì)自動(dòng)在主鍵列上創(chuàng)建索引，插入的記錄會(huì)根據(jù)主鍵的值的順序排放。
alter table product ENGINE=InnoDB;

B、增加主鍵
alter table TStudent add primary key(studentid);
C、刪除主鍵
alter table TStudent drop primary key;

3、自增主鍵

AUTO_INCREMENT PRIMARY KEY
如果不指定主鍵值，會(huì)自動(dòng)在現(xiàn)有主鍵值的最大值上自動(dòng)增加1作為新記錄的主鍵，主鍵值默認(rèn)從1開(kāi)始。可以在數(shù)據(jù)數(shù)據(jù)類型整數(shù)型的列上添加自增主鍵。

A、創(chuàng)建表時(shí)指定自增自增列

create table product
 (
 productID int PRIMARY KEY AUTO_INCREMENT not NULL,
 pName VARCHAR(10),
 price DOUBLE
 )ENGINE=MyISAM default CHARSET=utf8;

B、為現(xiàn)有的表指定自增列
alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT;

C、刪除表中自增列
alter table TStudent modify column studentID int not NULL;
刪除自增列，仍然時(shí)主鍵，但是沒(méi)有自增長(zhǎng)功能

4、復(fù)合主鍵

使用表的兩列或多列創(chuàng)建主鍵。
A、創(chuàng)建表時(shí)指定復(fù)合主鍵

create table student
 (
 studentID int,
 id INT,
 sname VARCHAR(10),
 score int,
 PRIMARY KEY(studentid,id)
 )ENGINE=MyISAM default CHARSET=utf8;

B、給表增加復(fù)合主鍵
alter table student add PRIMARY KEY(sudentID,id);
C、刪除復(fù)合主鍵
alter table student drop PRIMARY KEY;

5、唯一約束

UNIQUE KEY，唯一約束，指定某列和幾列組合的數(shù)據(jù)不能重復(fù)。
A、創(chuàng)建表時(shí)指定唯一性約束

create table score
 (sname VARCHAR(10) UNIQUE,
 score int not NULL
 );

B、給現(xiàn)有列增加唯一性約束
alter table score add CONSTRAINT uc_sname UNIQUE(sname);
如果表中現(xiàn)有記錄有重復(fù)值，不允許添加唯一性約束?？梢酝ㄟ^(guò)聚合函數(shù)，查找有重復(fù)值的記錄，刪除，再創(chuàng)建唯一性約束。
C、創(chuàng)建復(fù)合唯一性約束

create table student
 (
 studentID int,
 id INT,
 sname VARCHAR(10),
 score int,
 CONSTRAINT uc_id UNIQUE(studentID, id)
 )ENGINE=MyISAM default CHARSET=utf8;

D、刪除列的唯一性約束
alter table score drop index uc_sname;

三、域完整性

1、默認(rèn)值

在表中插入一條新的記錄時(shí)，如果沒(méi)有為該字段賦值，那么數(shù)據(jù)庫(kù)系統(tǒng)會(huì)自動(dòng)為該字段賦一條默認(rèn)值。

create table st
(
sid INT not null primary key auto_increment,
sname varchar(10),
subject varchar(20) default '軟件工程',
entertime TIMESTAMP default now()
);

給表中一列添加默認(rèn)值約束：
alter table st modify column subject VARCHAR(20) default '計(jì)算機(jī)科學(xué)與技術(shù)';
刪除表中一列的默認(rèn)值約束：
alter table st modify column subject VARCHAR(20) default NULL;

2、創(chuàng)建非空約束

非空約束用于確保當(dāng)前列的值不為空值，非空約束只能出現(xiàn)在表對(duì)象的列上。
Null類型特征： 所有的類型的值都可以是null，包括int、float等數(shù)據(jù)類型 空字符串是不等于NULL，0也不等于NULL。
A、創(chuàng)建表時(shí)給列指定非空約束

create table score
 (sname VARCHAR(10) not NULL,
 score int not NULL
 );

B、給指定列指定非空約束
alter table score modify column score int not NULL;
C、刪除非空約束
alter table score modify column score int;

3、檢查check

check關(guān)鍵字，在插入新行或者更改已有行時(shí)才起作用，作用是阻止不滿足條件的值進(jìn)入該列，對(duì)null值無(wú)效，因?yàn)椴迦雗ull就相當(dāng)于沒(méi)有插入。一個(gè)列可有多個(gè)check。
age int check(age between 10 and 20);
目前MySQL不支持check約束，微軟MSSQL支持Check約束，但創(chuàng)建表時(shí)可以指定Check約束，但不起作用。

四、參照完整性

1、參照完整性簡(jiǎn)介

MySQL參照完整性一般是通過(guò)MySQL外鍵(foreign key)實(shí)現(xiàn)的。
外鍵（僅innoDB支持）所引用表的列必須是主鍵。
外鍵聲明包括三個(gè)部分：
A、哪個(gè)列或列組合是外鍵
B、指定外鍵參照的表和列
C、參照動(dòng)作[cascade（級(jí)聯(lián)操作），restrict（拒絕操作），set null（設(shè)為空），no action，set default]。
如果外鍵約束指定了參照動(dòng)作，主表記錄做修改，刪除，從表引用的列會(huì)做相應(yīng)修改，或不修改，拒絕修改或設(shè)置為默認(rèn)值。
引用表的列名必須是主鍵，且在刪除引用表時(shí)必須刪除引用關(guān)系或者刪除當(dāng)前表。

2、創(chuàng)建表時(shí)指定外鍵

創(chuàng)建兩張表，學(xué)生表student和成績(jī)表score，成績(jī)表的sid列的取值參照學(xué)生表（學(xué)生表student的sid列設(shè)置為主鍵，且表的存儲(chǔ)引擎為innodb，成績(jī)表score的存儲(chǔ)引擎也必須設(shè)置為innodb）。

create table student
(
sid int not null primary key,
sname varchar(20)
) engine=innodb;
create table score
(
sid int not null,
mark INT,
constraint score_fk FOREIGN KEY (sid)
references student(sid) on delete cascade on update cascade
) engine=innodb;

在學(xué)生表插入一條記錄
insert into student values (1,'孫悟空')
在成績(jī)表插入一條記錄，學(xué)號(hào)是1，成功。
insert into score values (1,98)
在成績(jī)表插入一條記錄，學(xué)號(hào)是2，失敗。
insert into score values (2,88)
在學(xué)生表插入學(xué)號(hào)是2的一條記錄
insert into student values (2,'唐僧')
再在成績(jī)表插入一條學(xué)號(hào)是2的記錄，成功，證明外鍵參照成功。
insert into score values (2,88);

3、刪除參照約束

ALTER TABLEscoreDROP FOREIGN KEYscore_fk;

4、給現(xiàn)有表增加參照約束

ALTER TABLEscoreADD CONSTRAINTscore_fk2FOREIGN KEY (sid) REFERENCESstudent(sid);

5、驗(yàn)證級(jí)聯(lián)動(dòng)作刪除和更新

在score表創(chuàng)建的參照完整性，刪除動(dòng)作和更新動(dòng)作的參照動(dòng)作選擇了cascade（級(jí)聯(lián)操作），當(dāng)學(xué)生表的sid更新時(shí)，分?jǐn)?shù)表score的相應(yīng)的sid也會(huì)更新，當(dāng)學(xué)生被刪除，分?jǐn)?shù)表對(duì)應(yīng)的sid的記錄也會(huì)自動(dòng)刪除。
更新學(xué)生表學(xué)號(hào)是1的學(xué)生的學(xué)號(hào)為10
update student set sid=10 where sid=1
查看成績(jī)表，可以看到以前學(xué)號(hào)是1的已經(jīng)變成了10
select * from score
刪除學(xué)生表學(xué)號(hào)是2的學(xué)生
delete from student where sid=2
可以看到成績(jī)表，該學(xué)生的成績(jī)已經(jīng)級(jí)聯(lián)刪除
select * from score

6、驗(yàn)證級(jí)聯(lián)動(dòng)作No Aaction

級(jí)聯(lián)動(dòng)作設(shè)置為NO ACTION，如果子表中有匹配的記錄,則不允許對(duì)父表對(duì)應(yīng)候選鍵進(jìn)行update/delete操作。
Restrict動(dòng)作同no action，都是立即檢查外鍵約束。
將參照動(dòng)作設(shè)置為no action，如果成績(jī)表score有該學(xué)生sid，將不能更改學(xué)生表student表的學(xué)生sid列，也不能刪除該學(xué)生。除非你先刪除該學(xué)生的成績(jī)，再刪除該學(xué)生。
刪除score表的外鍵約束
ALTER TABLEscoreDROP FOREIGN KEYscore_fk;
增加score表的sid列外鍵約束

ALTER TABLE `score` ADD CONSTRAINT `score_fk` 
FOREIGN KEY (`sid`) REFERENCES `student` (`sid`)
 ON DELETE NO ACTION ON UPDATE NO ACTION;

更新學(xué)號(hào)是10的學(xué)生的學(xué)號(hào)，失敗
update student set sid=11 where sid=10
刪除學(xué)號(hào)是10的學(xué)生，失敗
delete from student where sid=10
需要先刪除該學(xué)生成績(jī)表中的記錄，再刪除該學(xué)生。

delete from student where sid=10
delete from score where sid=10;

7、驗(yàn)證級(jí)聯(lián)動(dòng)作Set NULL

在父表上update/delete記錄時(shí)，將子表上匹配記錄的列設(shè)為null?，要注意子表的外鍵列不能為not null。
刪除成績(jī)表的外鍵約束
ALTER TABLEscoreDROP FOREIGN KEYscore_fk;
增加成績(jī)表的sid列外鍵約束，參照動(dòng)作為set null
ALTER TABLEscoreADD CONSTRAINTscore_fkFOREIGN KEY (sid) REFERENCESstudent(sid) ON DELETE SET NULL ON UPDATE SET NULL;
修改成績(jī)表的sid列默認(rèn)值為NULL

ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER(11) DEFAULT NULL;
insert into student values (1,'孫悟空')
insert into student values (2,'豬八戒')
insert into score values (1,98)
insert into score values (2,88)

刪除學(xué)生表中學(xué)號(hào)1的學(xué)生
delete from student where sid=1
查看成績(jī)表，成績(jī)表中學(xué)號(hào)為1的列為NULL
select * from score