索引和约束和增删改查唯一约束

最新推荐文章于 2023-02-04 14:40:25 发布

MapSet

最新推荐文章于 2023-02-04 14:40:25 发布

阅读量377

点赞数

分类专栏：学习记录工作记录

本文链接：https://blog.csdn.net/copyright0501/article/details/109309265

版权

学习记录同时被 2 个专栏收录

53 篇文章 3 订阅

订阅专栏

工作记录

16 篇文章 0 订阅

订阅专栏

索引和约束和增删改查唯一约束

索引

索引是一个单独和物理地并存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为索引也要随之变动）。注：索引可常驻内存进行处理

在这些列上创建索引：
在经常需要搜索的列上，可以加快搜索速度；在主键列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序；在经常使用where子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引的列：
对于在查询中很少使用和参考的列不应该创建索引。增加索引会降低系统的维护速度和增大空间需求；对于数据值很少的列也不应增加索引。由于这些列的取值很少，例如：人事表的性别列，在查询结果中，结果集的数据行占了表中数据行的很大比例，在表中搜索的数据行的比例很大，索引不能明显加快检索速度；对于定义为text, image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么很少；当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。索引会提高检索性能，但是会降低修改性能。

索引的分类–按结构分
索引按照结构可以分为有序索引（ordered index）和散列索引（hash index）两种基本类型。其中有序索引是基于值的顺序排序，根据值的排序进行索引值的查找。而散列索引则是基于将值平均分布到若干散列桶（hash bucket）。根据散列函数确定索引值所在的散列桶。
一、有序索引：稠密索引、稀疏索引、多级索引和B+树索引。
二、散列索引：静态散列、散列桶、散列函数、桶溢出和动态散。

索引的分类–按功能分
一、唯一索引（unique index）和非唯一索引（nonunique index）
create unique index indexname on table tabname(colname)
/* 允许值为空，但不允许存在的值重复 */

二、主键索引（primary index）
/* 唯一索引+非空约束=主索引，主键创建时会默认自生成索引 */

三、集群索引（clustered index）和非集群索引（nonclustered index）
create index indexname on table tabname（colname desc） cluster
/* desc降序排列，在没有注明情况下默认是按asc升序排列 */

集群索引是按物理结构排序的，它会对表中的数据进行重新排序，所以表中只能有一个该索引。数据库管理器会尝试按照相应键在索引页面中的顺序来保存数据到页面中。如果对表进行了重组，那么会按照索引键的顺序将数据插入数据页面中，数据库管理器不会维护数据的任何顺序。

集群索引具有查询优势和插入上的疲软。由于需要保持索引值和数据行的物理顺序一致，插入数据时会进行大量迁移，为防止此种情况出现，我们允许聚簇索引存在无序状态，当聚簇率低于85%时就得进行重组。

DB2集群索引的叶结点和非集群索引一样，都是指向相应数据页的逻辑指针。即索引数据页和常规数据页是严格分开的。但是包括Oracle，SQL Server在内的大部分数据库的聚簇索引的页结点不是指向数据页的指针，而是叶结点本身就是数据页。

约束

约束的五种类型：
一、主键约束（primary key constraint）
主键创建时默认会自生成约束，也可通过显示声明。
alter table stuInfo add constraint PK_stuNo primary key (stuNo)
/* 主键可以是一列或多列的组合用以唯一标识表中某行数据。*/

二、外键约束（foreign key constraint）
外键创建时默认会自生成约束，也可通过显示声明。
alter table stuMarks add constraint FK_stuNo foreign key(stuNo) references stuinfo(stuNo)
/* 主表stuInfo 从表stuMarks */

三、检查约束（check constraint）
alter table stuInfo add constraint CK_stuAge check (stuAge between 15 and 40)
/* 年龄限制在15-40岁之间 */

四、唯一约束（unique constraint）
alter table stuInfo add constraint UQ_stuID unique(stuID)
/* 身份证号唯一 */

五、默认约束（default constraint）
alter table stuInfo add constraint DF_stuAddress default (‘地址不详’) for stuAddress
/* 地址信息不填，默认为地址信息不详 */

六、信息约束（informational constraint）
/* 不是由DB2管理器来负责实施SQL 编译器可用它来提高查询性能 */

查表,编辑

select t.* , t.rowid from 表名 t;

创建唯一约束(之前数据无重复)

ALTER TABLE 表名 ADD CONSTRAINT  自己起个约束名  UNIQUE (列名)

创建唯一约束(之前数据有重复)

--先创建普通索引
CREATE INDEX IDX_表名_列名 ON 起个索引名(列名);
--再添加唯一键
ALTER TABLE 表名 ADD CONSTRAINT 起个约束名 UNIQUE (列名) ENABLE NOVALIDATE;

查看约束

SELECT T.* USER_CONSTRAINTS T WHERE TABLE_NAME ='约束名';

删除约束

alter table 表名 drop constraint 约束名;

查询数据库里全部约束

select 
    USER_CONS_COLUMNS.CONSTRAINT_NAME AS 约束表,
    USER_CONS_COLUMNS.TABLE_NAME AS 表名,
    USER_CONS_COLUMNS.COLUMN_NAME AS 列名,
    USER_CONS_COLUMNS.POSITION AS 位置
FROM
    USER_CONSTRAINTS
    JOIN  USER_CONS_COLUMNS
    ON  ( USER_CONSTRAINTS.CONSTRAINT_NAME = USER_CONS_COLUMNS.CONSTRAINT_NAME )
WHERE
    CONSTRAINT_TYPE='C';
--
--注: 最后那里的 WHERE 填写的条件的注意：
--C (check constraint on a table) C 表示 CHECK 约束。
--P (primary key) P 表示主键
--U (unique key) U 表示唯一
--R (referential integrity) R 表示引用(外键)
--V (with check option, on a view) 指定强制检查通过视图修改数据操作
--O (with read only, on a view)  表示创建视图只能检索数据能修改数据