Oracle常见术语、操作、区别

原创转载请注明出处：http://agilestyle.iteye.com/blog/2358549

 

数据库9大对象

1.表（Table ）

数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row） 和列（Column）组成的。列由同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括了若干列信息项。一行数据称为一个或一条记录，它表达有一定意义的信息组合。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表中通常都有一个主关键字，用于惟一地确定一条记录。

 

2.索引（Index）

索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向的列中的数据不重复。

 

3.视图（View）

视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在数据库中并不实际存。在视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。由此可见，视图可以用来控制用户对数据的访问，并能简化数据的显示，即通过视图只显示那些需要的数据信息。

 

4.图表（Diagram）

图表其实就是数据库表之间的关系示意图。利用它可以编辑表与表之间的关系。

 

5.缺省值（Default）

缺省值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。

 

6.规则（Rule）

规则是对数据库表中数据信息的限制。它限定的是表的列。

 

7.触发器（Trigger）

触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。

 

8.存储过程（Stored Procedure）

存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。

 

9.用户（User）

所谓用户就是有权限访问数据库的人。

 

什么是视图？

A view is a SELECT statement that’s stored in the database as a database object.

 

什么是存储过程?

A stored procedure is an executable database object that contains a block of PL/SQL code.

 

什么是函数？

A user-defined function(UDF), which can also be called a stored function or just a function, is an executable database object that contains a block of PL/SQL code.

 

什么是触发器？

A trigger is a named block of PL/SQL code that is executed, or fired, automatically when a particular type of SQL statement is executed.

 

视图能否插数据？

插入视图的条件：

• 如果视图是基于一个基础表产生的，那么这就称为非连接视图，所有的非连接视图都是可以更新的，也就是说可以在该视图上进行，INSERT，UPDATE，DELETE的操作。
• 如果是连接视图，那就要遵守基本更新准则了。现在只对INSERT准则做一下说明：在INSERT语句中不能显式或隐式的引用到任何非码保留基础表中的字段，如果在定义视图中使用了WITH CHECK OPTION子句，那就不能对视图执行INSERT操作。

注：码保留表，非码保留表的解释：

在DEPT中，DEPT_NO是主码，在EMP中，EMP_NO是主码

然后建立连接视图：

create view emp_dept as 
select emp.emp_no，emp.emp_name，emp.dept_no，dept.name 
from emp，dept 
where emp.dept_no=dept.dept_no

这个视图中，EMP_NO仍然充当主码，所以EMP为码保留表，而DEPT中的DEPT_NO就不是主码了，所以为非码保留表。

多张表关联后需要向视图插入数据，建议使用替代触发器

create trigger [触发器名] 
on [视图名] 
instead of insert 
as 
begin 
——声明变量; 
——从inserted表中查出所有列的数据，分别赋给声明好的变量; 
——用上面的数据向第一张表插入数据 
——用上面的数据向第二张表插入数据 
end 

此外通过user_updatable_columns表能够查询到该表或者视图是否可更新。 

 

函数和存储过程主要有两个区别

• 函数总是返回值或者表
• 函数不能对数据库进行修改，比如执行INSERT、UPDATE、DELETE语句等

WHERE和HAVING的区别

• WHERE：是在执行GROUP BY操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数
• HAVING：是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数

union和union all区别

• union在进行表求并集后会去掉重复的元素，所以会对所产生的结果集进行排序运算，删除重复的记录再返回结果 —— 无重复记录
• union all则只是简单地将两个结果集合合并后就返回结果。因此，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据 —— 有重复记录

Clustered Index和Non-Clustered Index区别

最通俗的解释是：聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说，你通过目录翻到新华字典的汉字“爬”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；而不用你到目录，找到汉字“爬”所在的页码，然后根据页码找到这个字（非聚簇索引）。

Clustered Index用于查询

Non-Clustered Index用于更新

http://sql-plsql.blogspot.com/2010/06/non-clustered-indexes.html

 

Oracle表的建立及约束、序列的使用

CREATE TABLE MYPERSON
(
	PID VARCHAR2(18),
	NAME VARCHAR2(20) NOT NULL,
	AGE NUMBER(3) NOT NULL,
	BIRTHDAY DATE,
	SEX VARCHAR2(6) DEFAULT '男',
	CONSTRAINT PERSON_PID_PK PRIMARY KEY(PID),
	CONSTRAINT PERSON_NAME_UK UNIQUE(NAME),
	CONSTRAINT PERSON_AGE_CK CHECK(AGE BETWEEN 0 AND 120),
	CONSTRAINT PERSON_SEX_CK CHECK(SEX IN('男','女','未指定'))
);
INSERT INTO MYPERSON(PID,NAME,AGE,BIRTHDAY,SEX) VALUES
('111111111111111111','Sara',20,TO_DATE('2000-12-21','YYYY-MM-DD'),'女');

CREATE TABLE MYBOOK
(
	BID NUMBER NOT NULL,
	BNAME VARCHAR2(30),
	BPRICE NUMBER(5,2),
	PID VARCHAR2(18),
	CONSTRAINT BOOK_BID_PK PRIMARY KEY(BID),
	CONSTRAINT PERSON_BOOK_PID_FK FOREIGN KEY(PID) REFERENCES MYPERSON(PID) ON DELETE CASCADE
);
INSERT INTO MYBOOK(BID,BNAME,BPRICE,PID) VALUES
(1,'GROOVY',88.0,'111111111111111111');

--先删除子表（有外键的）
DROP TABLE MYBOOK;
--再删除主表
DROP TABLE MYPERSON;

--删除序列：
DROP SEQUENCE myseq;
--创建序列：
CREATE SEQUENCE myseq INCREMENT BY 1 START WITH 1 NOCYCLE;

 

Oracle数据库操作

表的复制

--表结构和数据一起复制

CREATE TABLE myemp AS SELECT * FROM emp;

--只复制表结构

CREATE TABLE myemp AS (SELECT * FROM emp WHERE 1==2);

 

表的删除

DROP TABLE myemp;

 

添加数据

INSERT INTO myemp(empno, ename, job, hiredate, sal, deptno)
VALUES(8888, ‘张益达’,’律师’,TO_DATE(‘2012-12-21’, ‘yyyy-mm-dd’), 9000, 40);

  

修改数据

UPDATE myemp SET comm=1000;
UPDATE myemp SET sal=5000 WHERE empno=7899;

 

删除数据

DELETE FROM myemp;
DELETE FROM myemp WHERE empno=7899;

 

ROWID

ROWID表示的是每一行数据保存的物理地址的编号

select rowid, deptno, dname, loc from dept;

每一条记录的ROWID都不会重复，所以即便表中所有列的数据内容都重复了，ROWID也是不会重复的，而且以一个ROWID为例，说明ROWID组成，例如：AAAR3qAAEAAAACHAAA

• 数据对象号：AAAR3q
• 相对文件号：AAE
• 数据块号：AAAACH
• 数据行号：AAA

请删除表中的重复记录，现在项目之中由于管理不善，所以导致出现了许多重复的信息，为了说明问题，下面为表中增加若干条记录。现在要求删除掉所有的重复记录，保留最早增加的记录。数据插入的早，ROWID就小。

delete from dept
where rowid not in(
    select min(rowid) from dept group by dname, loc
);