oracle_约束_视图_索引_同义词

##约束（constraint）

####约束是表级的强制规定
有以下五种约束

NOT NULL 不为空
UNIQUE 唯一注意空值之间不认为是违反唯一性约束的
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 检查的条件

创建emp2表 constraint 是修改约束名不修改的话就是默认的约束名

create table emp2(
id number(10) constraint emp2_id_nn not null,
name varchar2(20) not null,
salary number(10,2)
)

使用约束来创建表表级约束就是列级约束之后加上括号指定要约束的列

create table emp4(
--列级约束
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_unique not null,
salary number(10,2),
email varchar2(20),
--表级约束
constraint emp4_email_uk unique(email)
)

将创建的emp6表和departments表通过department_id来连接

create table emp6(
--列级约束
id number(10),
name varchar2(20) constraint emp6_name_unique not null,
salary number(10,2),
email varchar2(20),
department_id number(10),
--表级约束
constraint emp6_email_uk unique(email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_fk foreign key(department_id) references departments(department_id)
)

级联删除
- 当父表中的列被删除的时候，子表中相应的列也被删除
级联置空
- 子表中相应的列置空

使用外键级联置空

create table emp7  (
--列级约束
id number(10),
name varchar2(20) constraint emp7_name_unique not null,
salary number(10,2),
email varchar2(20),
department_id number(10),
--表级约束
constraint emp7_email_uk unique(email),
constraint emp7_id_pk primary key(id),
--级联置空
constraint emp7_dept_fk foreign key(department_id) references departments(department_id) on delete set null
)

外键使用级联删除

create table emp8  (
--列级约束
id number(10),
name varchar2(20) constraint emp8_name_unique not null,
salary number(10,2),
email varchar2(20),
department_id number(10),
--表级约束
constraint emp8_email_uk unique(email),
constraint emp8_id_pk primary key(id),
--级联删除
constraint emp8_dept_fk foreign key(department_id) references departments(department_id) on delete cascade
)

使用check约束来约束工资的条件

create table emp9  (
--列级约束
id number(10),
name varchar2(20) constraint emp9_name_unique not null,
salary number(10,2) check(salary>1500 and salary<30000),
email varchar2(20),
department_id number(10),
--表级约束
constraint emp9_email_uk unique(email),
constraint emp9_id_pk primary key(id),
--级联置空
constraint emp9_dept_fk foreign key(department_id) references departments(department_id) on delete cascade
)

##添加约束的语法
使用ALTER TABLE 语句

添加或删除约束但是不能修改约束
有效化或无效化约束
添加not null约束有使用modify语句

添加not null约束(只有not null使用的是modify)

alter table emp4
modify(salary number(10,2) not null)

删除约束是根据约束名来删除的

alter table emp4
drop constraint emp4_name_unique

添加unique约束跟之前的表级约束很像

alter table emp4
add constraint emp4_name_uk unique(name)

无效化约束

alter table emp3
disable constraint emp3_email_uk

有效化约束

alter table emp3
enable constraint emp3_email_uk

查询约束如果想要查询其他表的约束，只需要改table_name 就行

select constraint_name,constraint_type,
search_condition
from user_constraints
where table_name = 'EMPLOYEES'

查询约束作用在的行

select constraint_name,column_name
from user_cons_columns
where table_name = 'EMPLOYEES'

####约束练习

1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）

alter table emp2
add constraint my_emp_id_pk primary key(id)

2.向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
alter table emp2
add (dept_id number(10) constraint emp2_dept_fk references dept2(id) on delete set null)

##视图
从表中抽取的逻辑相关的数据集合

向视图提供的的数据内容的语句为select内容，可以将视图理解为存储起来的select语句

创建视图

create view empview
as
select employee_id,last_name,salary
from employees
where department_id = 80

视图的更新操作能直接修改原表的数据

update empview
set salary = 20000
where employee_id = 179

创建多表连接的视图

create view empview2
as 
select employee_id,last_name,salary,department_name
from employees e,departments d
where e.department_id = d.department_id

覆盖原有的视图

create or replace view empview2
as 
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id

使用 with read only来使这个视图只能读

create or replace view empview2
as 
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id
with read only

###简单视图和复杂视图
如果创建的视图使用到了分组函数就是复杂视图

创建复杂视图注意在复杂视图中是不能进行增删改的操作的

create or replace view empview3
as
select department_name dept_nane,avg(salary) avg_sal
from employees e ,departments d
where e.department_id = d.department_id
group by department_name

##TOP - N 分析
查询一个列中最大或最小的n个值

最大和最小的值是TOP - N 所关心的

使用排序好的rownum来获取工资最高的前十的列

--将排序好的rownum来获取工资最高的前十的列
select rownum,employee_id,last_name,salary
from(
    select employee_id,last_name,salary
    from employees
    order by salary desc
)
where rownum <11

可以将嵌套的方法来将rownum变成存在的实列，这样可以实现分页

之所以嵌套两次是因为伪列只能使用< <= 不能使用 > >=

--可以将 嵌套的方法来将rownum变成存在的实列，这样可以实现分页
select  rn,employee_id,last_name,salary
from(
select rownum rn,employee_id,last_name,salary
from(
    select employee_id,last_name,salary
    from employees
    order by salary desc
    )
)
where rn>40 and rn<50

####视图练习
1.使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).

--使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME）
--，员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).
create or replace view employee_vo 
as
select last_name,employee_id,department_id
from employees

2.将视图中的数据限定在部门号是80的范围内将视图改变成只读视图

--使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME）
--，员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).
--将视图中的数据限定在部门号是80的范围内
--将视图改变成只读视图
create or replace view employee_vo 
as
select last_name,employee_id,department_id
from employees
where department_id = 80
with read only

##序列
提供有规律的数值

自动提供的唯一数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率

创建一个序列

create sequence empseq
increment by 10 -- 每次增长10
start with 10 -- 从10开始
maxvalue 100 -- 最大值100
cycle -- 需要循环
nocache -- 不需要缓存登录

序列首次使用必须使用NextValue

select empseq.nextval from dual;

然后才能使用

select empseq.currval from dual;

使用序列来作为表的主键

insert into emp01
values(empseq.nextval,'cc',2300)

修改序列（注意初始值只能通过删除序列之后重建序列来完成）

alter sequence empseq 
increment by 1  --每次增长1
nocycle         --不循环

序列在下列情况下会出现裂缝

回滚
系统异常
多个表同时使用同一个序列

查询所有序列的命令

select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences

删除序列

drop sequence empseq

##索引
提高查询的效率

索引被删除或损坏不会对表产生影响，其影响的只是查询的速度
在删除一个表的时候所有基于该表的索引都会被自动删除
通过指针加速oracle服务器的查询速度
通过快速定位数据的方法减少磁盘io
索引一旦建立，oracle管理系统会自动对他进行维护，而且由oracle管理系统来决定何时使用索引，用户不用在查询语句中指定使用哪个索引

####创建索引

自动创建
- 在定义primary key 或unique约束系统自动在相应的列上创建唯一性约束
手动创建
- 用户可以在其他列上创建非唯一的索引以加速查询

为 emp01的employee_id 来创建索引

create index emp01_id_ix
on emp01(employee_id)

删除索引

drop index emp01_id_ix

什么时候创建索引？

当列中数据值分布范围很广的时候
列经常在where子句或连接条件中出现
表经常被访问而且数据量很大，访问的数据大概占数据总量的%2到%4

什么时候不要创建索引？

表很小
列不经常作为连接条件出现在where子句中
查询的数据大于%2到%4
表经常更新

##同义词-synonym
给对象起别名

给employees表起别名e

create synonym e for employees

删除同义词

drop synonym e

what-why-how

总结：

表table

视图view

序列sequence

索引index

同义词synonym

####练习
1.创建序列dept_id_seq，开始值为200，每次增长10，最大值为10000

create sequence dept_id_seq
increment by 10
start with 200
maxvalue 10000

2.使用序列向表dept中插入数据

insert into dept
values(dept_id_seq.nextval,'a')



附：
create table dept as 
select department_id id,department_name name 
from departments
where 1=2

Died line