oracle数据库(五)

一.表连接

两个表的连接，是通过将一个表中的一列或者多列同另一个表中的列链接而建立起来的。用来连接两张表的表达式组成了连接条件。当连接成功后，第二张表中的数据就同第一张表连接起来了，并形成了复合结果集

有5种基本类型的的连接，inner，outer，natural，cross连接，自连接。

1． 内连接（Inner Join/Join）

Inner join逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，所以内连接用的很少。
还有一点要说明的就是Join 默认就是inner join。 所以我们在写内连接的时候可以省略inner 这个关键字。

select * from emp inner join dept on emp.deptno=dept.deptno 

2． 外连接(Outer Join)

outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种： 左外连接，右外连接，全外连接。 对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成：LEFT/RIGHT/FULL JOIN。
1.left join(左联接)等价于（left outer join） 返回包括左表中的所有记录和右表中联结字段相等的记录；

SELECT e.*,d.* from emp e left join dept  d on e.deptno=d.deptno;

　2.right join(右联接)等价于（right outer join）返回包括右表中的所有记录和左表中联结字段相等的记录；

SELECT e.*,d.* from emp e right join dept  d on e.deptno=d.deptno;

　3. full join （全连接）等价于（full outer join）查询结果等于左外连接和右外连接的和

SELECT e.*,d.* from emp e full join dept  d on e.deptno=d.deptno;

3． 自然连接(Natural Join)

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

select d.*,e.* from dept d, emp e where e.deptno=d.deptno order by d.deptno desc

有关自然连接的一些注意事项：
（1）.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。
（2）.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

4． 交叉连接(Cross Join)

交叉连接不带ON子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到 结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查 询条件的数据行数。

SELECT  E.*, D.* FROM dept d CROSS JOIN emp e where e.sal<1000;

5． 自连接

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

在emp表中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。我们需要查找每一个员工自己的名字和经理的名字,但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。

select work.ename AS worker,mgr.ename AS manager from emp work, emp mgr 
where work.mgr = mgr.empno

二.视图

视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

1权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.

2 语法: create [ or replace ] [ force |noforce] view [<用户方案名>.]<视图名>
[ (column1,column2,…) ]
as
select …
[ with check option [ constraint <约束名> ] ]
[ with read only ];

tips:
1 or replace: 如果存在同名的视图, 则使用新视图”替代”已有的视图
2 force: “强制”创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
3 column1,column2,…：视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
4 with check option: 指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行”检查”,要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下, 在增删改之前”并不会检查”这些行是否能被select查询检索到.
5 with read only：创建的视图只能用于查询数据, 而不能用于更改数据.

create or replace view testview as
select d.dname,d.loc, e.* from dept d,emp e where e.deptno=d.deptno;

查询视图

select * from testview;

删除视图

drop view testview;

三.替换变量查询

1.&替换变量

select * from emp where empno=#

1. define 关键字

define column=deptno;

四.索引

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。
1. 创建索引:

CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] … )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced

其中：
schema ORACLE模式，缺省即为当前帐户
index 索引名
table 创建索引的基表名
column 基表中的列名，一个索引最多有16列，long列、long raw
列不能建索引列
DESC、ASC 缺省为ASC即升序排序
CLUSTER 指定一个聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事务入口数
Tablespace 表空间名
STORAGE 存储参数，同create table 中的storage.
PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
NOSORT 不（能）排序（存储时就已按升序，所以指出不再排序）

CREATE INDEX idx ON emp (ename);

2.查询索引

select index_name,index_type,tablespace_name,uniqueness 
from all_indexes where table_name='EMP';

3.索引重命名

alter index idx rename to index_sno;

4.合并索引

alter index index_sno coalesce;

5.删除索引

drop index index_sno;

四.约束

数据库约束有五种：
主键约束（PRIMARY KEY）
唯一性约束（UNIQUE)
非空约束（NOT NULL)
外键约束（FOREIGN KEY)
检查约束（CHECK)

主键约束（PRIMARY KEY)

主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键，不过Oracle没有遵循此范例要求，Oracle中的表可以没有主键（这种情况不多见）。关于主键有几个需要注意的点：

1.键列必须必须具有唯一性，且不能为空，其实主键约束 相当于 UNIQUE+NOT NULL,一个表只允许有一个主键
2.主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建
3.添加主键（约束的添加可在建表时创建，也可如下所示在建表后添加，一般推荐建表后添加，灵活度更高一些，建表时添加某些约束会有限制）

这里写代码片

唯一性约束（UNIQUE)

唯一性约束可作用在单列或多列上，对于这些列或列组合，唯一性约束保证每一行的唯一性。

UNIQUE需要注意：
1.对于UNIQUE约束来讲，索引是必须的。如果不存在，就自动创建一个（UNIQUE的唯一性本质上是通过索引来保证的）

2.UNIQUE允许null值，UNIQUE约束的列可存在多个null。这是因为，Unique唯一性通过btree索引来实现，而btree索引中不包含null。当然，这也造成了在where语句中用null值进行过滤会造成全表扫描。

添加唯一约束

alter table emp add constraint emp_code_uq unique(ename);

非空约束（NOT NULL)

非空约束作用的列也叫强制列。顾名思义，强制键列中必须有值，当然建表时候若使用default关键字指定了默认值，则可不输入。

添加非空约束，语法较特别

alter table emp modify ename not null;

外键约束（FOREIGN KEY）

外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。外键约束注意以下几点：

1. 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
2. 对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
3. 外键约束列允许NULL值，对应的行就成了孤行了

1>删除时级联删除关联表

alter table emp add constraint emp_deptno_fk foreign key(deptno) 
references dept (deptno) on delete cascade;

2>删除时将字表设为空

alter table emp add constraint emp_deptno_fk foreign key(deptno) 
references dept(deptno) on delete set null;

检查约束（CHECK)

检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。检查的规则必须是一个结果为true或false 的表达式，比如：

alter table emp add constraint emp_sex_ck check(sex in('男','女'));

删除约束

alter table emp drop constraint emp_sex_ck;

说明：

１．本博客为个人技术博客，非商业用途！

２．相关内容来自互联网或学习中遇到的问题，对于来自互联网的文章可能无法标注来源，敬请各位原作者谅解！非常感谢你们的辛苦劳动！