您好,登錄后才能下訂單哦!
數(shù)據(jù)冗余是指數(shù)據(jù)庫中存在一些重復(fù)的數(shù)據(jù),數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)能夠正確反應(yīng)實際情況。
數(shù)據(jù)的完整性是指數(shù)據(jù)的可靠性和準確性,數(shù)據(jù)完整性類型有四種:
A、實體完整性:實體的完整性強制表的標識符列或主鍵的完整性(通過唯一約束,主鍵約束或標識列屬性)。
B、域完整性:限制類型(數(shù)據(jù)類型),格式(通過檢查約束和規(guī)則),可能值范圍(通過外鍵約束,檢查約束,默認值定義,非空約束和規(guī)則)。
C、引用完整性:在刪除和輸入記錄時,引用完整性保持表之間已定義的關(guān)系。引用完整性確保鍵值在所有表中一致,不能引用不存在的值.如果一個鍵。
D、自定義完整性:用戶自己定義的業(yè)務(wù)規(guī)則,比如使用觸發(fā)器實現(xiàn)自定義業(yè)務(wù)規(guī)則。
MySQL不支持Check約束,雖然可以在列上添加check約束,但不起作用。
實體完整性的實現(xiàn)有兩種方式:
A、主鍵約束:一張表只能有一列設(shè)置主鍵,值必須唯一,不允許為空,innoDB存儲引擎,主鍵就是索引。
B、唯一值約束:一張表可以有多個列添加唯一值約束,一直允許一條記錄為空值。
實體完整性,由主鍵和唯一性約束來實現(xiàn),確保表中記錄有一列唯一標識。主鍵又分為Primary key 和AUTO_INCREMENT PRIMARY KEY兩種。
MySQL的主鍵名總是PRIMARY, 當(dāng)創(chuàng)建主鍵約束時,如果表的存儲引擎是innoDB,系統(tǒng)默認會在所在的列和列組合上建立對應(yīng)的唯一索引。
主鍵約束相當(dāng)于唯一約束與非空約束的組合,主鍵約束列不允許重復(fù),也不允許出現(xiàn)空值;多列組合的主鍵約束,列都不允許為空值,并且組合的值不允許重復(fù)。每個表最多只允許一個主鍵,建立主鍵約束可以在列級別創(chuàng)建,也可以在表級別上創(chuàng)建。
A、創(chuàng)建表時指定主鍵
創(chuàng)建表時指定主鍵的方式一:
create table product
(
productID int PRIMARY KEY,
pName VARCHAR(10),
price DOUBLE
)ENGINE=MyISAM default CHARSET=utf8;
創(chuàng)建表時指定主鍵的方式二:
create table product
(
productID int,
pName VARCHAR(10),
price DOUBLE,
CONSTRAINT pk_s_productID PRIMARY KEY(productID)
)ENGINE=MyISAM default CHARSET=utf8;
在指定主鍵的表中插入記錄時,不允許插入重復(fù)的ID,如果不指定主鍵的值,默認為0。
MyISAM類型的存儲引擎不會在主鍵列上創(chuàng)建索引,表中記錄的存儲順序與插入順序相同。
InnoDB存儲引擎會自動在主鍵列上創(chuàng)建索引,插入的記錄會根據(jù)主鍵的值的順序排放。alter table product ENGINE=InnoDB;
B、增加主鍵alter table TStudent add primary key(studentid);
C、刪除主鍵alter table TStudent drop primary key;
AUTO_INCREMENT PRIMARY KEY
如果不指定主鍵值,會自動在現(xiàn)有主鍵值的最大值上自動增加1作為新記錄的主鍵,主鍵值默認從1開始??梢栽跀?shù)據(jù)數(shù)據(jù)類型整數(shù)型的列上添加自增主鍵。
A、創(chuàng)建表時指定自增自增列
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;
刪除自增列,仍然時主鍵,但是沒有自增長功能
使用表的兩列或多列創(chuàng)建主鍵。
A、創(chuàng)建表時指定復(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;
UNIQUE KEY,唯一約束,指定某列和幾列組合的數(shù)據(jù)不能重復(fù)。
A、創(chuàng)建表時指定唯一性約束
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ù)值,不允許添加唯一性約束。可以通過聚合函數(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;
在表中插入一條新的記錄時,如果沒有為該字段賦值,那么數(shù)據(jù)庫系統(tǒng)會自動為該字段賦一條默認值。
create table st
(
sid INT not null primary key auto_increment,
sname varchar(10),
subject varchar(20) default '軟件工程',
entertime TIMESTAMP default now()
);
給表中一列添加默認值約束:alter table st modify column subject VARCHAR(20) default '計算機科學(xué)與技術(shù)';
刪除表中一列的默認值約束:alter table st modify column subject VARCHAR(20) default NULL;
非空約束用于確保當(dāng)前列的值不為空值,非空約束只能出現(xiàn)在表對象的列上。
Null類型特征: 所有的類型的值都可以是null,包括int、float等數(shù)據(jù)類型 空字符串是不等于NULL,0也不等于NULL。
A、創(chuàng)建表時給列指定非空約束
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;
check關(guān)鍵字,在插入新行或者更改已有行時才起作用,作用是阻止不滿足條件的值進入該列,對null值無效,因為插入null就相當(dāng)于沒有插入。一個列可有多個check。
age int check(age between 10 and 20);
目前MySQL不支持check約束,微軟MSSQL支持Check約束,但創(chuàng)建表時可以指定Check約束,但不起作用。
MySQL參照完整性一般是通過MySQL外鍵(foreign key)實現(xiàn)的。
外鍵(僅innoDB支持)所引用表的列必須是主鍵。
外鍵聲明包括三個部分:
A、哪個列或列組合是外鍵
B、指定外鍵參照的表和列
C、參照動作[cascade(級聯(lián)操作),restrict(拒絕操作),set null(設(shè)為空),no action,set default]。
如果外鍵約束指定了參照動作,主表記錄做修改,刪除,從表引用的列會做相應(yīng)修改,或不修改,拒絕修改或設(shè)置為默認值。
引用表的列名必須是主鍵,且在刪除引用表時必須刪除引用關(guān)系或者刪除當(dāng)前表。
創(chuàng)建兩張表,學(xué)生表student和成績表score,成績表的sid列的取值參照學(xué)生表(學(xué)生表student的sid列設(shè)置為主鍵,且表的存儲引擎為innodb,成績表score的存儲引擎也必須設(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,'孫悟空')
在成績表插入一條記錄,學(xué)號是1,成功。insert into score values (1,98)
在成績表插入一條記錄,學(xué)號是2,失敗。insert into score values (2,88)
在學(xué)生表插入學(xué)號是2的一條記錄insert into student values (2,'唐僧')
再在成績表插入一條學(xué)號是2的記錄,成功,證明外鍵參照成功。insert into score values (2,88);
ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
ALTER TABLE
scoreADD CONSTRAINT
score_fk2FOREIGN KEY (
sid) REFERENCES
student(
sid);
在score表創(chuàng)建的參照完整性,刪除動作和更新動作的參照動作選擇了cascade(級聯(lián)操作),當(dāng)學(xué)生表的sid更新時,分數(shù)表score的相應(yīng)的sid也會更新,當(dāng)學(xué)生被刪除,分數(shù)表對應(yīng)的sid的記錄也會自動刪除。
更新學(xué)生表學(xué)號是1的學(xué)生的學(xué)號為10update student set sid=10 where sid=1
查看成績表,可以看到以前學(xué)號是1的已經(jīng)變成了10select * from score
刪除學(xué)生表學(xué)號是2的學(xué)生delete from student where sid=2
可以看到成績表,該學(xué)生的成績已經(jīng)級聯(lián)刪除select * from score
級聯(lián)動作設(shè)置為NO ACTION,如果子表中有匹配的記錄,則不允許對父表對應(yīng)候選鍵進行update/delete操作。
Restrict動作同no action,都是立即檢查外鍵約束。
將參照動作設(shè)置為no action,如果成績表score有該學(xué)生sid,將不能更改學(xué)生表student表的學(xué)生sid列,也不能刪除該學(xué)生。除非你先刪除該學(xué)生的成績,再刪除該學(xué)生。
刪除score表的外鍵約束ALTER TABLE
scoreDROP FOREIGN KEY
score_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é)號是10的學(xué)生的學(xué)號,失敗update student set sid=11 where sid=10
刪除學(xué)號是10的學(xué)生,失敗delete from student where sid=10
需要先刪除該學(xué)生成績表中的記錄,再刪除該學(xué)生。
delete from student where sid=10
delete from score where sid=10;
在父表上update/delete記錄時,將子表上匹配記錄的列設(shè)為null?,要注意子表的外鍵列不能為not null。
刪除成績表的外鍵約束ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
增加成績表的sid列外鍵約束,參照動作為set nullALTER TABLE
scoreADD CONSTRAINT
score_fkFOREIGN KEY (
sid) REFERENCES
student(
sid) ON DELETE SET NULL ON UPDATE SET NULL;
修改成績表的sid列默認值為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é)號1的學(xué)生delete from student where sid=1
查看成績表,成績表中學(xué)號為1的列為NULLselect * from score
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。