数据库索引与自增序列

索引

索引的优缺点

优点：创建索引可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：降低了系统的维护速度和增大了空间需求
第一，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

主键,外键

（primary key[列名，[列名]])

create table emp (  
	id number(6) primary key,--主键  
	name varchar2(20) not null,--非空  
	sex number(1),  
	age number(3) default 18,    
	email varchar2(25) unique,--唯一  
	deptno number(2) references dept(deptno)—外键  
);  

  
  

  
  
1
2
3
4
5
6
7
8

带约束名称：

create table emp (  
	id number(6) constraint id_pk primary key,  
	name varchar2(20) constraint name_nn not null,  
	sex number(1),  
	age number(3) default 18,   
	email varchar2(25) constraint email_uqe unique,  
	deptno number(2) constraint dept_deptno_ref references dept(deptno)  
);  

  
  

  
  
1
2
3
4
5
6
7
8

列模式：

create table emp (  
	id number(6),  
	name varchar2(20),  
	sex number(1),  
	age number(3) default 18,  
	email varchar2(25),  
	deptno number(2),  
	constraint id_pk primary key (id),--也可以两个以上，联合主键  
	constraint dept_deptno_ref foreign key (deptno) references dept(deptno),  
	constraint emial_name_uqe unique (email, name)  
); 

  
  

  
  
1
2
3
4
5
6
7
8
9
10
11

主键索引

主键一定是唯一性索引，唯一性索引并不一定就是主键。

主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。
主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

**唯一索引 **

• **create unique index idx_emp_ename on 表名(column); **

创建索引

普通索引：

• **create index 索引名 on dept(column); **

联合索引：

• create index 索引名 on dept(column[, column[, column]]);

**反向键索引 **

• **create index 索引名 on 表名(column) reverse; **

**位图索引 **

• **create bitmap index 索引名 on 表名(column); **

**索引组织表,一定要有主键 **

create table tab (  
	id int primary key,  
	name varchar2(20)  
) organization index; 

  
  

  
  
1
2
3
4

注：索引组织表的insert效率非常低

**分区表索引 **
create index 索引名 on table(column) local/global;

提示：当给表创建主键或唯一键约束时，系统也会创建一个约束给该字段；同样创建索引也会占用数据库空间；索引在访问、查询的时候效率有提高，但是在修改表的时候效率就会降低；

自动增长序列

mysql

mysql不自带sequence，但是可以设置自动增长序列，设置是必须要有主键（primary key）

创建表时创建

creat table emp(

id int auto_increment primary key,
…

);

• 1
• 2
• 3
• 4
• 5
• 6

创建表后添加

• alter table emp add id int auto_increment primary key;

设置自增序列的启始值

• alter table emp auto_increment=2;

注：也可以让自增序列值从123变为2，从2重新开始；

oracle

创建自增序列

• **create sequence 序列名; **

• select seq.nextval from dual;

使用时的名字为

• **序列名.nextval **

**create sequence 序列名
start with 2 –从2开始
increment by 3—每次加3
nomaxvalue—没有最大值
minvalue 1—最小值1
nocycle—不循环
nocache;–不缓存 **

**修改序列的序列值 **

**alter sequence seqtab
maxvalue 1000; **

注：不能修改为起始值

修改序列值可以先查询出来再increment修改回来

select sql.nextval from dual;

alter sequence seq increment by 79;

序列已更改

创建视图

如果需要经常执行某项复杂的查询，可以基于复杂查询建立此视图即可，简化复杂查询；
视图本质上就是一条select语句，所以当访问视图时，只能访问到所对应的的select语句中涉及到的列，对基表中的其他列起到安全和保密的作用，可以限制数据访问

授权创建视图

• crant create view to 用户

创建视图

• crate view 视图名 as select * from 表名 where ···

查看视图结构

• select * from 视图名