MySQL約束與索引概念是什麼

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

遵循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 自增字段名 数据类型;

自增約束的特點：

要求一個表中只有一個主鍵為自增長類型的字段，該字段必須為整數且不能為空。通常只在主鍵上設定鍵約束，如主鍵約束、唯一鍵約束、外鍵約束

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約束與索引概念是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！