ORACLE数据库入门再在屋里坐会 (8)

当一个查询是另一个查询的条件时，称之为子查询。子查询是一个SELECT 语 句 ， 它 嵌 套 在 一 个 SELECT 、 SELECT...INTO 语 句 、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。

1）在 CREATE TABLE 语句中使用子查询

　　创建表并拷贝数据

　　　　create table temp(id,name,sal) as select empno,ename,sal from emp

　　在 INSERT 语句中使用子查询

　　　　当前表拷贝

　　　　　　insert into temp(id,name,sal) select * from temp

　　　　从其他表指定字段拷贝

　　　　　　insert into temp(id,name,sal) select empno,ename,sal from emp

2）在 DELETE 语句中使用子查询

　　删除 SALES 部门中的所有雇员

　　　　delete from emp where deptno in

　　　　(select deptno from dept where dname='SALES')

3）在 UPDATE 语句中使用子查询

　　修改 scott 用户的工资和 smith 的工资一致

　　　　minus:取出 2 个表中不同数据

　　　　　　create table emp2 as select * from emp where rownum<=5

　　　　取出 emp 中与 emp2 不同数据

　　　　　　select * from emp minus select * from emp2

　　　　Intersect:取出 2 个表中共有数据【交集】

　　　　　　select * from emp intersect select * from emp2

　　　　　　update emp set sal=(select sal from emp where ename='SMITH') where ename='SCOTT'

　　修改 black 用户的工作,工资,奖金和 scott 一致

　　　　update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SCOTT') where ename='BLAKE'

4）在 SELECT 语句中使用子查询

　　查询和 ALLEN 同一部门的员工信息

　　　　select * from emp where deptno in (select deptno from emp where ename='ALLEN')

　　查询工资大于部门平均工资的雇员信息

　　　　select * from emp e

　　　　(select avg(sal) sal,deptno from emp group by deptno) t 

　　　　where e.deptno=t.deptno and e.sal>t.asal

TCL语言 -> 事务控制语言 1、COMMIT

commit --提交事务

2、ROLLBACK

rollback to p1 --回滚到指定的保存点

rollback --回滚所有的保存点

3、SAVEPOINT

savepoint p1 --设置保存点

4、只读事务

只读事务是指只允许执行查询的操作，而不允许执行任何其它 dml 操作的事务，它的作用是确保用户只能取得某时间点的数据。

set transaction read only

六、ORACLE函数 函数的定义

具有某种功能的代码段

实现代码重用，模块化编程

分类

1.系统函数,用户自定义函数

2.参数,返回值

         1)无参无返

         2)无参有返

         3)有参无返

         4)有参有返

函数中有两个角色：主调函数，被调函数

参数：主调函数给被调函数传递的信息（数据）

       参数的数量：0个或多个

       形式参数(形参)：在定义函数时的参数

       实际参数(实参)：在调用函数时的参数

返回值：被调函数给主调函数传递的信息（数据）

       返回值的数量：0个或１个

ORACLE提供的系统函数 1、数学函数

求绝对值

　　select abs(-4) from dual

　　power(n,m):n的m次方

　　select power(2,3) from dual

返回大于或等于n最小整数值（3，4，5...）

　　select ceil(2.48) from dual ;     --3

　　select ceil(2.68) from dual ;     --3

返回小于或等于n最大整数值（2，1，0，-1...）

　　select floor(2.48) from dual ;   --2

　　select floor(2.68) from dual ;   --2

四舍五入

　　select round(2.48) from dual ; --2

　　select round(2.68) from dual ; --3

四舍五入，设置保留位数

　　select round(2.48,1) from dual ;  --2.5

　　select round(2.163,2) from dual ;  --2.16

随机数

1）小数(0 ~ 1)

　　select dbms_random.value from dual;

2）指定范围内的小数 ( 0 ~ 100 )

　　select dbms_random.value(0,100) from dual;

3）指定范围内的整数 ( 0 ~ 100 )

　　select round(dbms_random.value(0,100),0) from dual;

　　select round(dbms_random.value(0,100)) from dual;

4）长度为20的随机数字串

　　select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;

5）随机字符串

　　select dbms_random.string(opt, length) from dual;

　　其中：opt为选项，规则如下所示：

　　'u','U'    :    大写字母

　　'l','L'    :    小写字母

　　'a','A'    :    大、小写字母

　　'x','X'    :    数字、大写字母

　　'p','P'    :    可打印字符

　　length为随机字符串的长度

　　select dbms_random.string('a',10) from dual;

6）生成GUID：32位十六进制字符串

　　select sys_guid() from dual;

　　select length(sys_guid()) from dual;

2、字符串函数

length:求字符串的长度

　　print len('hello,world')

　　select length(ename),ename from emp ;

lower/upper:大小写

　　select ename,lower(ename),upper(ename) from emp ;

concat/||：字符串连接

　　select concat('hello ','world') from dual ;

　　select 'hello ' || 'world' from dual ;

substr("字符串",start,n):截取字符串，从start开始截取n个字符

　　select substr('hello,world',1,3) from dual;

　　select ename,substr(ename,1,3) from emp ;

replace:替换字符串

　　replace('字符串','被替换子字符串','替换字符串')

　　select replace('hello world','world','china') from dual ;

instr:查找字符串

instr('字符串','查找字符/字符串','起始位置'),返回下标位置（从1开始）

　　select instr('abcabc','c',-2) from dual;

　　select instr('abcabc','c',0) from dual;

　　select instr('abcabc','c') from dual;

注：起始位置中，正数从左向右、负数从右向左查找

trim:去掉字符串左边、右边两边的空格

ltrim:去掉字符串左边空格

rtrim:去掉字符串右边空格

　　select 'AAA' || '   BBB    ' ||  'CCC' from dual ;

　　select 'AAA' || trim( '   BBB    ') ||  'CCC' from dual ;

3、日期函数 getdate()

sysdate：返回当前session所在时区的默认时间

获取当前系统时间

　　select sysdate from dual; 

　　add_months：返回指定日期月份+n之后的值，n可以为任何整数

查询当前系统月份+2 的时间

　　select add_months(sysdate,2) from dual;

查询当前系统月份-2 的时间

　　select add_months(sysdate,-2) from dual;

　　last_day：返回指定时间所在月的最后一天

获取当前系统月份的最后一天

　　select last_day(sysdate) from dual;

　　months_between：返回月份差，结果可正可负，当然也有可能为 0

获取入职日期距离当前时间多少天

　　select months_between(sysdate, hiredate) from emp;

　　select months_between(hiredate, sysdate) from emp;

　　trunc（number,num_digits）

用法一：截取日期值

　　select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06

　　select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.

　　select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天

　　select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日

　　select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天

　　select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天

　　select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35

　　select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确

用法二：截取数值

number:需要截尾取整的数字。

num_digits:用于指定取整精度的数字。Num_digits 的默认值为 0。

trunc()函数截取时不进行四舍五入

　　select trunc(123.458) from dual --123

　　select trunc(123.458,0) from dual --123

　　select trunc(123.458,1) from dual --123.4

　　select trunc(123.458,-1) from dual --120

　　select trunc(123.458,-4) from dual --0

　　select trunc(123.458,4) from dual --123.458

　　select trunc(123) from dual --123

　　select trunc(123,1) from dual --123

　　select trunc(123,-1) from dual --120

4、转换函数

to_char：日期转换

　　select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

　　select to_char(sysdate, 'yyyy-mm-dd hh12:mi:ss') from dual

to_char：数字转换

　　select to_char(-100.789999999999,'L99G999D999') from dual

　　select to_char(-100000.789999999999,'L99G999D99') from dual

　　select to_char(-100000.789999999999,'L999G999D99') from dual

　　9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)

　　0 强制显示该位，如果当前位有数字，显示数字，否则显示 0

　　. （句点） 小数点

　　, （逗号） 分组（千）分隔符

　　PR 尖括号内负值

　　S    带负号的负值（使用本地化）

　　$   美元符号显示

　　L    货币符号（使用本地化）

　　D   小数点（使用本地化）

　　G   分组分隔符（使用本地化）   10,000

　　MI 在指明的位置的负号（如果数字 < 0）

　　PL  在指明的位置的正号（如果数字 > 0）

　　SG 在指明的位置的正/负号

　　RN 罗马数字（输入在 1 和 3999 之间）

　　TH or th      转换成序数

　　V   移动 n 位（小数）（参阅注解）

　　EEEE  科学记数,现在不支持

to_date：将字符串转换成日期对象

　　select to_date('2011-11-11 11:11:11', 'yyyy-mm-ddhh24:mi:ss') from dual

to_number：将字符转换成数字对象

　　select to_number('209.976')*5 from dual

　　select to_number('209.976', '9G999D999')*5 from dua

5、空值判断函数

　　nvl(内容,data):空值函数，类似 SQLServer中的 null()函数,如果内容为空，则值设置为data

　　select ename,comm,nvl(comm,0) from emp;

nvl2(内容,data1,data2):如果内容不为空，则值设置为data1,否则设置为data2

　　select ename,comm,nvl2(comm,comm+200,200) from emp;

　　nullif(a,b):如果 a,b 的值相等，返回 null，如果不相等，返回a

　　select nullif(10,10) from dual; --空，神马都没有

　　select nullif(10,11) from dual; --返回 10

6、分析函数

row_number() over (order by 字段 asc|desc):为有序组中的每一行（划分组的行或查询行）返回一个唯一的排序值

　　select ename,sal,row_number() over(order by sal desc) 名次 from emp;

rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次跳过相应次数

　　select ename,sal,row_number() over(order by sal desc) 名次,rank() over(order by sal desc) 名次 from emp;

dense_rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次不跳过相应次数

　　select

     　　 ename,

    　　  sal,row_number() over(order by sal desc) 名次1,

 　　     rank() over(order by sal desc) 名次2,

   　　   dense_rank() over(order by sal desc) 名次3

　　from emp;

七、数据库对象