约束与索引
非空约束 Not Null
create table emp
(emp_id nvarchar(9) not null,
emp_name nvarchar(50) not null,
)
当一列被创建时,可以使用not null约束,空值null 等于没有或未知道,该表的两个列不允许插入null或者不设置值。
主键约束 primary key
用于标识表中一行数据是唯一的,主键通常由一列组成,但也可以多列共同组成主键。主键是一个隐性约束
create table emp
(emp_id nvarchar(9) not null primary key,
emp_name nvarchar(50) not null,
)
alter table emp add constraint emp_name_pk primary key(emp_name)
改变一个表的约束
唯一约束 UNIQUE
是指约束在指定列上的值必须唯一,它和主键约束类似,当在一个表上的一列放置主键约束时,可以在另一列上放置唯一约束。主键比约束唯一多了数据提供顺序和在查询中用于连接表之外,主键约束和唯一约束没有太大区别。
create table emp
(emp_id nvarchar(9) not null primary key ,
emp_name nvarchar(50) not null unique,)
外键约束 foreign key (父子表)
create table emp_pay
(emp_id nvarchar(9) not null,
posititon varchar(15) not null,
pay_rate nvarchar(50) not null,
)
go
alter table emp_pay add constraint emp_id_fk foreign key(emp_id) references emp(emp_id);
go
校验约束
用来检查输入到表中某一特定列的数据的有效性,一般应用程序在前台也要校验数据是否有效,数据库的校验提供了另一种实现数据的保护方式。
ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)
add constraint chk_emp_zip check ( emp_zip = '050021')
add constraint chk_emp_zip check ( emp_zip in ( '050021','050000',021111'))
add constraint chk_emp_pay check (pay_rate > 12.50)
默认约束
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress
--添加默认约束(如果地址不填,默认为“地址不详”)
使用约束的要点
首先必须理解数据库中各个表之间的关系,看看哪些表依赖于别的表,哪些列依赖于别的列,哪些可以可空还是必须有数据
使用约束的次序
当使用create table命令创建约束时或者用alter table命令在表上添加约束时,必须以正确的顺序制定约束,例如必须在定义参照某个主键的外键时,必须先定义这个主键。如果想删除一个主键,必须先删除参照这个主键的所有外键。再譬如父子记录,必须首先有父记录,才能创建子记录,删除时必须先删除子记录,才能删除父记录。
在表上创建索引以提高速度——快速的获取数据
索引是一种基于表中数据的对象,与view不同,索引需要占用物理存储。它提供的数据顺序不同于属于在磁盘上的物理存储数据,特殊作用就是在表内重新排列记录的物理位置,索引基于表的一列或多列组合建立。通常在正确的字段上建立索引,可以使性能显著的提高,特别是在表之间的连接字段上建立索引。
技巧
对于较小的表,索引不会有大的性能改变;在数据变化幅度较大的(唯一值最多的)列上建立索引,会产生最大程度的性能改变,在带有索引的列上进行大量的插入、删除或更新会产生性能影响,应该及时地删除索引,完成操作后在建立索引。
复合索引
create index id_cmpd_index on bills(account_id,amount); --即使两个字段相结合,也只是建立了一个实际的索引。
UNIQUE 唯一性索引
由于主键中的每个值必须是唯一的,所以数据库将隐含的创建索引,主键和唯一约束都使系统生成索引。
聚簇索引
主键一般是应该设为聚集索引的,但现在有了新的认识,主键主要意义在于“唯一标识记录”,而索引主要是“提高数据库性能”,尤其是“聚集索引”。
而现在我会注重数据库索引的建立,最关键的就是聚集索引,“聚集索引”与“非聚集索引”的理解很简单:“聚集索引”就是物理上的排列方式,而“非聚集索引”是使用索引表(相当于书的目录),使用索引之所以能提高性能,也就是“避免全表扫描”。
因此一个表只能有一个“聚集索引”,因为在物理上,不可能会有“两种”物理存放方式吧。也真是如此“聚集索引”对查询性能的影响最大,所以要非常注意“聚集索引”的建立,我目前意识到、也是Freedk所推荐的“单据日期”作为聚集索引,我说的“单据日期”是指日常业务性的记录,一般都是会有一个日期字段的,而80%查询中都会用到此"日期字段",而且把它放在Where的第一个效果最佳,因为会在指定的区域内进行其他条件的查询,范围少了很多很多,性能效果提高明显。
至于其他的索引,这只要根据业务的需要,对于一些会进行查询的字段进行一定的索引,也就是尽可能的“避免全表扫描”就可以提高系统性能。当然过多的索引也会引起系统负担,想想要建那么多“目录”自然会增加负担了。
还有“聚集索引”由于是物理存储的,性能上固然是提高了,但对于插入与删除,会引起数据的“移动”(在中间插入一条记录,会引起后半部分记录的后移),当然数据库有自己的存储机制,会采用“页”来进行分块存储,可以避免一定的“移动”,所以“聚集索引”最好是在“大部分”情况下可以“递增”,我们目前采用的“单据日期”也刚好是符合这个条件的。
避免全表扫描
如果没有索引,当执行select或者更新的时候就会执行全表扫描,严重的影响sql的性能。应该分析数据库的执行过程,找到经常需要查询的字段(where子句的),将之使用索引,或者聚集索引(只能有一个聚集索引)以提供性能。 但有时全表扫描又是必须的:例如从一个表中几乎需要选择所有的表、修改表中的所有的行、表比较小。
注意不要使用 %张%
少使用 in or 语句这会使索引实效~