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

遵循ER模型和三范式

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

三范式：

1、列不能拆分

2、唯一標識

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

具體體現(xiàn)

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

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

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

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

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

2、約束（CONSTRAINTS）

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

3、索引（INDEX）

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

①Mysql會在主鍵、唯一鍵、外鍵列上自動創(chuàng)建索引，其他列需要建立索引的話，需要手動創(chuàng)建。

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

③刪除唯一鍵的方式是通過刪除對應(yīng)的索引來實現(xiàn)的

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

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

1、查看某個表的約束和索引

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

2、主鍵約束：primary key

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

#單個字段設(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)
);
#多個字段設(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;

主鍵的特點：

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

3、自增約束：auto_increment

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

自增約束的特點：

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

2、設(shè)置為自增的字段，從1開始自增；每次添加數(shù)據(jù)，都會在該字段最大值的基礎(chǔ)上+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、設(shè)置唯一鍵約束的字段值唯一，但是可以為null
• 2、一張表可以設(shè)置多個唯一鍵約束，也可以設(shè)置聯(lián)合唯一鍵，即多個字段設(shè)置一個唯一約束，但是不能使用"unique key"寫在字段后設(shè)置，必須寫在所有字段后，使用"unique key(字段,字段)"
• 3、聯(lián)合唯一鍵要求組成唯一約束的字段可以單獨重復(fù)，不能同時重復(fù)
• 4、 MySQL會給唯一約束的列上默認創(chuàng)建一個唯一索引。
• 5、刪除唯一鍵只能通過刪除對應(yīng)索引的方式刪除，刪除時需要指定唯一鍵索引名

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 字段名 數(shù)據(jù)類型 NOT NULL 【default 默認值】;
#如果該字段原來設(shè)置了默認值約束，要跟著一起再寫一遍，否則默認值約束會丟失
#取消某個字段非空
ALTER TABLE 表名稱 MODIFY 字段名 數(shù)據(jù)類型 【default 默認值】;
#如果該字段原來設(shè)置了默認值約束，要跟著一起再寫一遍，否則默認值約束會丟失

非空約束的特點：

設(shè)置為非空約束的字段的值不能為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 '男'
);

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

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

7、外鍵約束：foreign key

表關(guān)系：

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 表名;
#刪除外鍵約束不會刪除對應(yīng)的索引，如果需要刪除索引，需要用ALTER TABLE 表名稱 DROP INDEX 索引名;
#查看索引名 show index from 表名稱;

（1）外鍵特點

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

（2）要求

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

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

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

（4）5個約束等級

• Cascade方式：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
• Set null方式：在父表上update/delete記錄時，將子表上匹配記錄的列設(shè)為null，但是要注意子表的外鍵列不能為not null
• No action方式：如果子表中有匹配的記錄,則不允許對父表對應(yīng)候選鍵進行update/delete操作
• Restrict方式：同no action, 都是立即檢查外鍵約束
• Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時,子表將外鍵列設(shè)置成一個默認的值，但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 ('男','女')
);

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

最新評論