oracle数据查询与操作 博客分类： ORACLE sqloracle

数据查询
1、查询的基本用法:
1) ROWNUM:虚列。用来显示行号
2) 算术运算符：+ - * / 用于显示计算列
3) as:用来起别名，用于改变列表表头的显示。（as可省略，但列名与其别名之间得用空格隔开。别名中若含有空格、特殊字符、或大小写敏感，则需要用双引号将之引起来）
4) ||：连接运算符。可以将两个字符串连接在一起
5) DISTINCT:消除重复行。此关键字要紧跟在 SELECT 之后
2、查询结果的排序
ORDER BY 字 段 名 1  [ASC|DESC][, 字 段 名 2 [ASC|DESC]...];
order by从句在select语句的最后。ASC表示升序，DESC表示降序。默认为升序。
条件查询
1、 简单条件查询
比较运算符

字符串比较区分大小写。Eg: WHERE job=‘SALESMAN’ 。条件中的字符串和日期型数据的值是包含在单引号中的。字符的值对大小写敏感，在emp表中存放的职务字符串全部是大写。
2、 符合条件查询
逻辑运算符

运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号。
3、 条件特殊表示法



BETWEEN…AND查询范围中包含上下限的值。Exists比IN更快，最慢的是NOT操作。不等于是<>.
select /*+INDEX(emp1 I_deptno)*/ * from emp1 where exists (select 1 from dept where dept.deptno=emp1.deptno and deptno=20 )
like中%：代表0个或多个任意字符。 _ ：代表一个任意字符
虚表dual
Oracle系统中dual表是一个“神秘”的表，该表只有一行一列, oracle保证dual里面永远只有一条记录
1) 查看当前用户 : select user from dual;
2) 获得当前系统时间: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
3) from dual; select sysdate from dual;
4) 获得一个随机数: select dbms_random.random from dual;
5) 获得序列sequence的下一个值: select my_seq.nextval from dual;
6) 获 得 主 机 名 : select SYS_CONTEXT('USERENV','TERMINAL') from dual
函数
1、 数值型函数

cos函数的输入参数应为弧度
round函数按照第二个参数指定的位置对第一个数进行四舍五入。2代表对小数点后第三位进行四舍五入，0 代表对小数位进行四舍五入，?1代表对个位进行四舍五入。
trunc该函数按照第二个参数指定的位置对第一个数进行截断。2代表对小数点后第三位进行截断，0 代表对小数位进行截断，-1代表对个位进行截断。
------递归查
SELECT ORGNUM, ORGNAME, ROLLUPORGNUM, ORGLEVEL FROM ORG CONNECT BY PRIOR ORGNUM = ROLLUPORGNUM START WITH ORGNUM = 3 order by orglevel;
2、 字符型函数

rpad函数向字符串的右侧添加字符，以达到指定宽度。concat函数是连接两个字符串
instr(ename,'S‘,1,1)函数返回ename中从第一个字符位置开始，字符串“S”第一次出现的位置。如果函数返回0，则说明ename中不包含字符串“S”；如果函数返回值大于0，则说明ename中包含字符串“S”
3、 日期型函数
SYSDATE是返回系统当前时间的虚列函数。日期显示格式为默认格式，如“06-2月-03”表示03年2月6日。

* 对日期的值加减一个天数，得到新的日期。
* 对两个日期相减，得到相隔天数。
* 通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。
4、 转换函数
Oracle的类型转换分为自动类型转换和强制类型换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER。TO_CHAR转换成字符串类型TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY')；TO_DATE转换成日期类型。to_date('2004-11-27 13:34:43','yyyy-mm-dd hh24:mi:ss')。TO_NUMBER转化成数值类型
高级查询
1、 多表联合查询
通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。忘记说明表的连接条件时，将会产生表连接的笛卡尔积(即一个表中的每条记
录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。
两个表的连接有四种连接方式：
* 相等连接（内连接）
Where中增加的条件用AND连接。
Eg:显示雇员的名称和所在的部门的编号和名称。
执行以下查询：SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;
使用内连接的方式如下：SELECT emp.ename,emp.deptno,dept.dname FROM emp inner join dept on emp.deptno=dept.deptno；
* 不等连接。
* 外连接
外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧(左外连接)或右侧(右外连接)。
Eg:显示雇员的名称、工资和所在的部门名称及没有任何雇员的部门。
执行以下查询：SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno(+)=dept.deptno;
left outer join 即左外连接方式：
SELECT ename,sal,dname FROM emp left outer join dept on emp.deptno=dept.deptno;
* 自连接。
2、 统计查询
组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。
常用的组函数：

分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。使用GROUP BY 从句可以对数据进行分组。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。
在分组查询的查询列中，只能包含分组的列和聚合函数列，不能使用分组列以外的其他列，否则会产生错误信息。
对分组查询的结果进行过滤，要使用HAVING从句。HAVING 从句过滤分组后的结果，它只能出现在GROUP BY 从句之后 ，而WHERE从句要出现在GROUP BY从句之前。
3、 子查询：第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中
子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。
子查询比主查询先执行，结果作为主查询的条件,在书写上要用圆括号扩起来，并放在比较运算符的右侧。
子查询可以嵌套使用，最里层的查询最先执行
Eg:查询雇员表中排在第 6 ～ 9 位置上的雇员。
执行以下查询：
SELECT  * FROM  (SELECT  rownum  as num,ename,sal FROM emp WHERE rownum<=9 ) WHERE num>=6;
4、 集合运算

union 和 union all 的区别。union 比 union all 多做了一步 distinct 操作。能用 union all 的情况下尽量不用 union。

数据操作
一、 数据库操作语句

1、插入数据
1)数据插入基本语法（[]里的内容可省略）
INSERT INTO 表名[(字段列表)] VALUES ( 表达式列表);
插入字段的值的类型要和字段的类型一一对应。字符串类型的字段值必须用单引号括起来。字段列表如果省略则代表全部字段，且插入数据的顺序必须与表的字段默认顺序保持一致。如果不知道表的字段默认顺序，可以用DESCRIBE命令查看。
2) 复制数据另一种插入数据(相当于复制)方法的语法格式是：
INSERT INTO 表名(字段列表) SELECT(字段名1,字段名2, ...) FROM 另外的表名;
该形式一次可以插入多行数据。
eg:将emp表拷贝数据到manager：
步骤1：创建一个新表manager：
CREATE TABLE manager AS SELECT empno,ename,sal,job FROM emp WHERE job='MANAGER';
步骤2：从emp表拷贝数据到manager：
INSERT INTO manager SELECT empno, ename, sal,job FROM emp  WHERE job = 'CLERK';
3）使用序列
序列是一个要预先定义的有序的数值序列, 应该先建立一个序列，然后在插入语句中使用.
创建序列：CREATE SEQUENCE seq_abc INCREMENT BY 1 START WITH 2000 MAXVALUE 99999 CYCLE NOCACHE;
NOCACHE-出入内存, CYCLE-循环使用,nocycle到最大值会报错, MAXVALUE-最大
在INSERT 语句使用序列，序列的名称为abc：
INSERT INTO manager VALUES(seq_abc.nextval,' 小王 ',2500, 'CLERK');
select abc.currval from dual 当前序列值
select abc.nextval from dual 下一个序列值
2、修改数据
修改数据的语句UPDATE对表中指定字段的数据进行修改，一般需要通过添加WHERE条件来限定要进行修改的行，如果不添加WHERE条件，将对所有的行进行修改。
1) 修改数据的语句UPDATE的基本语法如下：
UPDATE 表名 SET 字段名1=表达式1, 字段名2=表达式2, ... WHERE 条件;
如果修改的值没有赋值或定义，将把原来字段的内容清为NULL。若修改值的长度超过定义的长度，则会出错。不能省略WHERE条件，否则将会修改表的所有行。
2) UPDATE语句的另外一种用法：
UPDATE 表名 SET(字段名1, 字段名2, ...)=SELECT (字段名1, 字段名2, ...) FROM 另外的表名WHERE条件;
3、删除数据
1）删除数据的基本语法如下：
DELETE FROM 表名 WHERE 条件;
要从表中删除满足条件的记录，WHERE条件一般不能省略，如果省略就会删除表的全部数据。
删除记录并不能释放Oracle 中被占用的数据块表空间，它只是把那些被删
除的数据块标成 unused 。
2）如果确实要删除一个大表里的全部记录，可以用TRUNCATE 命令，它可以释放占用的数据块表空间，
语法为：
TRUNCATE TABLE 表名;
TRUNCATE TABLE 命令用来删除表的全部数据而不是删除表，表依旧存在。数据不能恢复 , 不用 commit, 不能 rollback;
3）删除重复记录
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的， rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。
实现方法：
SQL> create table TA (
bm char(4), --编码
mc varchar2(20) --名称
)
insert into ta values(1,'a'); insert into ta values(2,'b');
insert into ta values(3,'c'); insert into ta values(1,'a');
insert into ta values(1,'a');
查出重复记录
SQL> select rowid,bm,mc from TA a where a.rowid!=(select max(rowid)
from TA b where a.bm=b.bm and a.mc=b.mc);
删除重复记录
SQL> delete from TA a where a.rowid!=(select max(rowid) from TA b
where a.bm=b.bm and a.mc=b.mc);

数据库事务
1、 事务
由相关操作构成的一个完整的操作单元，具有同时成功或同时失败的特点.
2、  事务的特征：ACID
1）原子性(Atomicity) ： 数据库中的事务执行是作为原子,不可再分，整个语句要么执行，要么不执行
2）一致性(Consistency) ： 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏（如外键、not null）
3）理解隔离性（Isolation) ： 事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据
4）理解持久性（Durability) ： 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
3、 Oracle的事务控制：
1）Dirty read: 脏读意味着一个事务读取了另一个事务未提交的数据
2）Phantom read: 幻读，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。如果第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样
3）Unrepeatable Read ：不可重复读，即一个事务范围内两个相同的查询却返回了不同数据
4）Lost update: 丢失更新，即两个并发的事务，后提交的事务把先提交事务的修改结果覆盖了
4、Oracle的事务控制：
1）Read uncommited ：性能最佳，但允许出现脏读
2）Read commited ： 不允许脏读，可能出现不可重复读，幻读
3) Repeatable Read： 可以重复读，没有脏读，可能出现幻读
4）Serializable: 性能最差，但安全性最高
Oracle默认的事务级别为read commited
5、 数据库事务的应用
数据库事务处理可分为隐式和显式两种。显式事务操作通过命令实现，隐式事务由系统自动完成提交或撤销(回退)工作，无需用户的干预。隐式提交的情况包括：当用户正常退出SQL*Plus或执行CREATE、DROP、GRANT、REVOKE等命令时会发生事务的自动提交。
如 果 把 系 统 的 环 境 变 量AUTOCOMMIT设置为ON(默认状态为OFF)，则每当执行一条INSERT、DELETE或UPDATE命令对数据进行修改后，就会马上自动提交。设置命令格式如下：  SET AUTOCOMMIT ON/OFF
隐式回退的情况包括：当异常结束SQL*Plus或系统故障发生时，会发生事务的自动回退。
显式事务处理的数据库事务操作语句有3条：

6、 表的锁定
1）隐式加锁
在Oracle数据库中，修改数据操作时需要一个隐式的独占锁，以锁定修改的行 ，直到修改被提交或撤销为止。
如果一个会话锁定了数据，那么第二个会话要想对数据进行修改，只能等到第一个会话对修改使用COMMIT命令进行提交或使用ROLLBACK命令进行回滚撤销后，才开始执行。
2）显式加锁

锁定行时，直接在SELECT语句后加for update.
LOCK语句用于对整张表进行锁定。语法如下：
LOCK TABLE 表名 IN {SHARE|EXCLUSIVE} MODE
对表的锁定可以是共享(SHARE)或独占(EXCLUSIVE)模式。共享模式下，其他会话可以加共享锁，但不能加独占锁。在独占模式下，其他会话不能加共享或独占锁。
说明：当使用LOCK语句显式锁定一张表时，死锁的概率就会增加。同样地，使用COMMIT或ROLLBACK命令可以释放锁。
注意：必须没有其他会话对该表的任何记录加锁，此操作才能成功
乐观锁和悲观锁:
乐观锁（ Optimistic Locking ）：乐观假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁通常采用检查version、时间戳、关键数据等方式
Eg: select ver from emp where empno=7782;
update emp set sal =1909,ver=ver+1 where empno=7782 and ver=20;
悲观锁（Pessimistic Locking）：在整个数据处理过程中，采取保守态度，将数据全部处于锁定 状态（不允许修改、删除）
如： SELECT * FROM emp WHERE deptno=10 FOR UPDATE；
（只允许查询，不允许修改和删除锁定的数据）
由于悲观锁在并发环境中影响性能，建议尽量使用乐观锁！