oracle新手学习总结

DML查询语句（select）注意点：
1、in语句在oracle中经常用到，同样的也有not in的操作符，有个注意点：
在使用NOT IN的时候必须保证查询的范围数据之中没有NULL，一旦出现了NULL，不会有任何数据显示。
2、like语句表示模糊查询，但是在真正的开发应用中这种查询性能很低基本上都不使用，而是使用另一种方式。使用instr()字符串代替。Instr(a,b)表示判断a中是否包含有b，为true的话返回该记录，false返回null。
3、关于to_char()函数的使用，此函数在开发中使用的次数很平凡，它的主要作用是把日期转换格式。To_char(字段名，‘格式’)；
4、oracle中有2个通用函数，NVL(),DECODE()。NVL(a,0)表示的是如果a字段有值为null则用0代替。DECODE(字段，判断值1，显示结果1，判断值2，显示结果2•••)相当于if…else if…else.
5、开发中对于多表查询是很普遍的事情，大家都知道多表查询一定需要消除笛卡尔积，所以绝对需要一个消除笛卡尔积的过滤条件，2张表需要一个，3张表需要两个，4张表需要三个…以此类推。但是需要注意的是多表查询对性能的影响很大，一般最多关联查询4张表。
6、左右关联，在oracle中支持（+）的关联方式，在别的数据库中只能使用[left|right|full outer join..]on().
7、在开发中经常对很多数据都需要进行统计，所以统计函数的使用也很多。常用的统计函数个有count(),avg(),sum(),max(),min()五中。Count（）和sum（）表示的都是统计总数的意思，区别是sum（）只是针对数字类型而count（）可以是所有类型，avg（）表示的是平均数，max（）和min（）表示的是最大和最小，这里有个特别的用途他们还可以针对日期字段来统计
8、开发中很多数据统计完成以后还需要进行分组，分组函数的使用也不少，GROUP BY 分组字段,分组字段,...但是有个注意点SELECT子句之后只能够出现分组字段和统计函数，其他字段不能出现。所有的分组函数都允许嵌套使用，但是嵌套之后的SELECT查询之中，不能出现任何的字段，包括分组字段。
9、在最近的工作中有很多结果都不是简单就能查询出来的，很多地方都需要用到子查询。子查询可以出现在一条查询语句的任意位置上，主要是在SELECT、FROM、WHERE、HAVING等地方上出现
• WHERE出现子查询：多行单列、单行单列、单行多列；
• HAVING出现子查询：单行单列；
同样的也可以使用in语句的子查询例如：
SELECT * FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE job='MANAGER') ;需要注意的地方时in语句后面的查询结果必须和where后的条件相同。
还有两个用得比较少的any，all关键字，这个跟in关键字有些相同，=any就是相当于in，>any表示的是比查询之中最大的要小，<any表示的是比查询之中最小的要大。
>all表示的是比查询之中最大的要大，<all表示的是比查询之中最小的要小。


• FROM出现子查询：多行多列。
FROM之后一般出现的数据表（数据来源），表的组成一定是多行多列的数据，所以一旦子查询返回的是多行多列，那么就可以当成一张临时表出现，在FROM后编写。例子：
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d,( SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
FROM emp
GROUP BY deptno) temp
WHERE d.deptno=temp.dno(+) ;
在开发中使用子查询可以明显的解决多表查询所带来的性能问题，所以在任何的开发之中，子查询的使用是最为广泛的。
DML语句操作（增删改）
10、增加语句：insert into 表名称 （字段1，字段2，•••）values（值1，值2，•••）
需要特别注意的是值可分为字符串，数字，日期三中类型。其中字符串值需要单引号包含起来，数字直接编写数字，日期类型需要按照日期格式编写或者使用to_date（）函数将字符串转换为日期类型。
修改语句：update 表名称 字段1=值1，字段2=值2•••where 修改条件，在开发中只要是修改语句肯定需要设置修改条件。
删除语句：delete from 表名称 where 删除条件，同样的在开发中删除语句也是一定需要设置删除条件。
事物回滚：
事物回滚包含两个命令，提交（commit），回滚（rollback）如果多个事物同时更新一条数据会有可能出现死锁。
数据伪劣
Oracle中存在三个关键的数据伪劣，ROWNUM,ROWID，SYSDATE
ROWNUM表示查询结果进行行号标记，主要作用是取出数据的第N行或者作为查询分页使用。
ROWID表示oracle数据库记录这条记录的物理地址，具有唯一性。主要作用是删除数据库中重复的数据。
DELETE FROM mydept WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc) ;
DDL数据管理
1、开发中一张数据表主要使用以下几种数据类型：
• VARCHAR2：字符串，200个字以内的信息都使用VARCHAR2表示；
• NUMBER：数字：
|- NUMBER(n)：表示正好n位的数字，或者简单的使用INT表示；
|- NUMBER(n,m)：表示小数位是m位，整数是n-m位，或者使用FLOAT表示；
• DATE：日期，包含了日期时间；
• CLOB：大文本对象，例如保存一部《红楼梦》等小说，最多可以保存4G的文字信息；
• BLOB：二进制数据，例如音乐、电影、小说，最多可以保存4G的文字信息；
2、创建数据表
表的数据类型已经知晓以后我们就可以创建一张数据表了，命令如下：
CREATE TABLE 表名称 (
字段 数据类型 [DEFAULT 默认值] ,
字段 数据类型 [DEFAULT 默认值] ,
..
字段 数据类型 [DEFAULT 默认值]
在Oracle之中，只要发生了DDL操作，则所有未提交的事务将自动提交。
3、清空表中的数据释放其所暂用的所有空间。命令：truncate table 表名称 。
4、删除表：
命令Drop table 表名称。此时表只是被删除到回收站中 如果想恢复还能从回收站中恢复过来，命令：FLASHBACK TABLE 表名称 TO BEFORE DROP ;
如果想直接删除不经过回收站可用以下命令：
Drop table 表名称 purge；
5、为数据表增加字段：
语法ALTER TABLE 表名称 ADD (字段 类型 [DEFAULT 默认值] , 字段 类型 [DEFAULT 默认值] ,  ...) ;
6、修改数据表字段：
语法ALTER TABLE 表名称 MODIFY (字段 类型 [DEFAULT 默认值] , 字段 类型 [DEFAULT 默认值] ,  ...) ;
约束
数据表创建完成之后，肯定要保存各种数据，但是对于数据很多时候也会有一些保存的要求，而这些要求就是约束，在数据库之中，约束一共有六种：数据类型、非空、唯一、主键、检查、外键；
非空：表示该项数据不能为空。命令：只要在创建表的字段后加上 not null 即可。
唯一：表示该数据不能重复，一般用于用户名或者邮箱这类数据。唯一约束使用unique进行定义。
主键：表示 非空+唯一=主键 使用primary key定义。
检查：表示在字段上设置若干个过滤条件 使用check 定义。
外键：表示两张表以上的关联关系 使用foreign key 完成。
例如以下分别创建了2张表并且给他们设置主外键的关系：
DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member (
mid NUMBER ,
name VARCHAR2(50) ,
CONSTRAINT pk_mid PRIMARY KEY(mid)
) ;
CREATE TABLE book (
bid NUMBER  ,
title VARCHAR2(50) NOT NULL,
price NUMBER ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY (bid) ,
CONSTRAINT ck_price CHECK (price>0) ,
CONSTRAINT fk_mid FOREIGN KEY (mid) REFERENCES member(mid)
) ;
当表之间存在主外键关系之后，随着也带来不少的问题，首先是表的删除。
在删除父表之间一定要先删除字表。然后是删除表中数据的问题，不能直接删除父表记录，在删除父表记录之前应该先将对应子表记录全部删除。
默认情况下，设置的外键约束只是起到一个数据范围的限制作用，但是对于主表数据删除时如果不想受到子表的影响，可以有两个方法可以解决：
1. 级联删除：当主表数据删除时，对应的子表数据自动删除。
加上后面红色字体CONSTRAINT fk_mid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE CASCADE。
2. 级联设置为null：当主表数据被删除之后，如果想保留子表数据，则可以将与之关联的字段设置null
加上后面红色字体CONSTRAINT fk_mid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE SET NULL。
序列
主键的递增是人为通过业务类型定义出来实现递增功能的(比如，每插入一条新记录就+1操作，可能会是通过触发器来实现的)，而序列(sequence)是oracle内置的东西，开销会比通过触发器(会涉及到io以及事务锁等方面的东西)来做少很多。
语法：
CREATE SEQUENCE 序列名称
[INCREMENT BY 步长] [START WITH 开始值]
[MAXVALUE 增长最大值 | NOMAXVALUE] [MINVALUE 最小值 | NOMINVALUE]
[CYCLE | NOCYCLE] [CACHE | NOCACHE] ;累加，循环
当一个序列创建完成之后，可以使用序列之中的两个伪列进行操作：
CURRVAL=返回 sequence的当前值（第一查询时会报未初始化，调用一次NEXTVAL后可用）
NEXTVAL=增加sequence的值，然后返回 sequence 值
可以使用sequence的地方：
- 不包含子查询、snapshot、VIEW的 SELECT 语句
- INSERT语句的子查询中- NSERT语句的VALUES中
- UPDATE 的 SET中
视图
视图本身属于数据库的对象，对象的定义语法：
create view 视图名称 as 子查询；
视图最大的功能是方便检索，同时提供了一定的安全性，因为用户只需要操作视图，而不需要知道有那些数据表。
删除视图语法：
Drop view 视图名称；