Oracle入门基础知识

Oracle入门基础知识


是基于对象的关系数据库，Oracle产品免费，服务收费；
客户端工具有默认的sql*Plus， 第三方工具PL/SQL Developer，还有企业管理器，从Oracle 10g开始支持B/S的企业管理器；
开始工作前，Oracle服务器需要启动监听服务，客户端需要配置本地网络服务名；
startup/shutdown 启动和关闭数据库；
权限分为系统权限和数据库对象权限，可以把角色或权限授权给用户，普通用户拥有Connect和Resource角色。


和SQL Server不同的数据类型：
NUMBER(p，s) - 既可表示整数，也可表示浮点数；
CLOB - 存储大的文本，比如存储非结构化的XML文档
BLOB - 存储二进制对象，如图形、视频、声音等。
DATE - 存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
内置函数有sysdate。
TIMESTAMP - 不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
内置函数有systimestamp




SELECT SYSDATE, SYSTIMESTAMP FROM DUAL - 查询单行函数或者常量的时候需要select from Dual表，比保证select语法完整性。
INSERT TABLE1 SELECT NAME, AGE FROM TABLE2 WHERE NAME LIKE 'Zhang%' FROM DUAL




创建表和约束（如主键约束，外键约束，UNIQUE约束，CHECK约束）与SQL Server类似。




CREATE TABLE INFOS_NEW SELECT * FROM INFOS WHERE NAME = 'Xueyi'
创建新表，插入数据但不复制constraint。




default关键字 - 创建表时可以定义列的默认值，并且可以通过 insert/updat使用默认值，通过alter table改变默认值，应用程序就可以使用新的默认值。




算术运算符 - 只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。
关系运算和逻辑运算与SQL Server一致。


字符串连接操作符（||）


SELECT (ENAME || 'is a ' || JOB) AS "Employee Details"
FROM EMP
WHERE SAL>2000; 




集合运算


集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括： 


INTERSECT(交集)，返回两个查询共有的记录。  
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。 
UNION(并集)，返回各个查询的所有记录，不包括重复记录。  
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩 余的记录。
当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。






ANY子查询
where age <any(select age * from table1):比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么 同理>any表示比子查询结果中最小的还大。


ALL子查询
>ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。 
<ALL表示比最小值还要小。






Oracle中的伪列


在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作


。
ROWID - 表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一


行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。


ROWNUM - 在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查


询结果集中返回的行数。




查询出员工表中前5名员工的姓名，工作，工资。
SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;


查询出工资最高的前5名员工的姓名、工资和工资。
SELECT ROWNUM,T.* FROM 
   (SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC) T 
   WHERE ROWNUM<=5


查询出表EMP中第5条到第10条之间的记录。
SELECT * FROM
(SELECT ROWNUM R,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=10)
WHERE R>5




常用函数：


日期函数：
ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。 d表示日期，n表示要加的月数。
LAST_DAY(d)，返回指定日期当月的最后一天。
ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式 模型。默认fmt为DDD，即月中的某一天。   如果fmt为“YEAR”则舍入到某年的1月1日，


即前半年舍去，后半年作为下 一年。   如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一 月。   默认为“DDD”，即月中的某一天，最靠近的


天，前半天舍去，后半天作为第 二天。   如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下 一周周日。


与ROUND对应的函数时TRUNC(d[,fmt])对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。


EXTRACT(fmt FROM d)，提取日期中的特定部分。




转换函数：
TO_CHAR(d|n[,fmt])   把日期和数字转换为制定格式的字符串。fmt是格式化字符串，日期的格式化字符串前面已经学习过。


代码演示：TO_CHAR对日期的处理 （在格式化字符串中，使用双引号对非格式化字符进行引用）
SQL> SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date"  
FROM DUAL;   
date 
 ----------------------- 
2009年08月11日 12:06:00


TO_DATE(x [,fmt]) 把一个字符串以fmt格式转换为一个日期类型，前面已经学习过。     
TO_NUMBER(x[,fmt])   把一个字符串以fmt格式转换为一个数字。


NVL(x,value)   如果x为空，返回value，否则返回x。 类似于Sybase的isnull函数。
NVL2(x,value1,value2)   如果x非空，返回value1，否则返回value2。






同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：   私有同义词：拥有


CREATE SYNONYM权限的用户（包括非管理员用户）即可创建私 有同义词，创建的私有同义词只能由当前用户使用。   公有同义词：系统管理员可以创建公


有同义词，公有同义词可以被所有用户访问。


Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所 有数据库对象的集合称为Schema（中文称为模式或者方案），用户名就是


Schema名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。


序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。
序列创建之后，可以通过序列对象的CURRVAL和NEXTVAL两个“伪列”分别访问该序列的当前值和下一个值。   
代码演示：
序列使用  
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; 
 NEXTVAL 
----------    
1   
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;  
NEXTVAL 
  ---------- 
2
SQL> SELECT MYSEQ.CURRVAL FROM DUAL;  
CURRVAL 
----------    
2


使用ALTER SEQUENCE可以修改序列，在修改序列时有如下限制： 1. 不能修改序列的初始值。 2. 最小值不能大于当前值。 3. 最大值不能小于当前值。


索引
Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有


负面影响。






SQL> CREATE INDEX IDX_SAL ON EMP(SAL);  
Index created   
SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); 
Index created


在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对 JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这


种索引称为基于函数的索引。


在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。  Oracle


可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。关于Oracle更多的索引类型（比如反向键索引等），请参考Oracle官方文档。

有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。