Oracle-函数与备份

Oracle-函数与备份

1.数学函数

函数	输入	输出
Abs(n)	Select abs(-15) from dual;	15
Ceil(n)	Select ceil(44.778) from dual;	45
Cos(n)	Select cos(180) from dual;	-.5984601
Floor(n)	Select floor(100.2) from dual;	100
Power(m,n)	Select power(4,2) from dual;	16
Mod(m,n)	Select mod(10,3) from dual;	1
Round(m,n)	Select round(100.256,2) from dual;	100.26
Trunc(m,n)	Select trunc(100.256,2) from dual;	100.25
Sqrt(n)	Select sqrt(4) from dual;	2
Sign(n)	Select sign(-30) from dual;	-1

2.字符函数

函数	输入	输出
Initcap(char)	Select initcap(‘hello’) from dual;	Hello
Lower(char)	Select lower(‘FUN’) from dual;	fun
Upper(char)	Select upper(‘sun’) from dual;	SUN
Length(char)	Select Length(‘sun’) from dual;	3
Ltrim(char,set)	Select ltrim( ‘xyzadams’,’xyz’) from dual;	adams
Rtrim(char,set)	Select rtrim(‘xyzadams’,’ams’) from dual;	xyzad
Translate(char, from, to)	Select translate(‘jack’,’j’ ,’b’) from dual;	back
Replace(char, searchstring,[rep string])	Select replace(‘jack and jue’ ,’j’,’bl’) from dual;	black and blue
Instr (char, m, n)	Select instr (‘worldwide’,’d’) from dual;	5
Substr (char, m, n)	Select substr(‘abcdefg’,3,2) from dual;	cd
Concat (expr1, expr2)	Select concat (‘Hello’,’ world’) from dual;	Hello world

concat(s1,s2)

    连接字符串s1和s2，返回连接后的字符串

substr(s,m,[n])

    取子串函数，从s串中第m个字符起，取长度为n的一个子串，如果n省略，一直到s串的尾部

length(s)

    返回字符串s的长度

lower(s)

    将字符串s中的所有字母变为小写

upper(s)

    将字符串s中的所有字母变为大写

ltrim(s)

    移走左边的空格字符

rtrim(s)

    移走右边的空格字符

replace(s1,s2,[s3])

    字符串s1中查找s2字符串，并用s3字符串代替，如果s3省略，删除s1中所有的s2串

instr(s1,s2)

    s1串中查找s2串出现的位置，并作为函数返回值，如果未查到返回0

3.日期函数

名称	说明
sysdate	获取日期和时间
months_between	获取两个日期之间的月份间隔
add_months(date,n)	在指定日期上添加月份，返回新日期
next_day(date,n)	获取指定日期所在周的第n天的日期。周日是一个星期的第一天
last_day(date)	每月的最后一天

3.1日期格式元素

元素	用途
YYYY YY	年的表示：YYYY表示4位数字，YY表示2位数字
MM MONTH MON	月份表示：MM月份的数字表示,MONTH有为月的名称，MON为月名称的缩写,在中文环境下，MONTH,MON都是“X月” 的形式
DD DDD	DD：当月的第几天;DDD：当年的第几天
D DY DAY	表示星期几:D-数字表示；DAY-英文表示，DY-缩写，在中文环境下，DAY和DY都是“星期X”形式
HH HH24 SS MI	时间表示：HH为小时、SS为秒、MI为分。HH24为24小时形式
AM　PM	表示时间的上午和下午
-/,.;:	在结果中插入起分隔作用的标点符号

4.转换函数

名称	说明
to_char(date,m)	将日期转换为m所指定的格式
to_date(m,n)	将字符串m转换为日期类型，n用于描述字符串m的格式
to_number(m,n)	将字符串m转换为数值，n用于描述字符串m的格式

to_date(m,n)

    函数用于将字符串转为日期类型，第二个参数用来描述第一个参数的格式，以告诉sqlplus字符串m中哪部分是年份，哪部分是月份，哪部分是日期等等

select to_date('2013-05-14','yyyy-mm-dd') from dual;

select to_date('14-05-2013','dd-mm-yyyy') from dual;

select to_date('05-14-2013','mm-dd-yyyy') from dual;

to_number(m,n)

    函数用于将字符串m转换为数值，参数n用以说明m的格式

将字符串’$12,345.6’转换为数值

select to_number('$12,345.6','$99,999.9') from dual;

将‘￥12,345.6’转换为数值

select to_number('￥12,345.6','L99,999.99') from dual;

4.1数字格式元素

数字格式	规则
9	每一个9代表一个字符
0	先导符号0
.	在指定的位置插入小数点
,	在指定的位置插入逗号
L	匹配先导的￥符号
$	匹配先导的$符号

5.其他高级查询

nvl(m,n)

    如果m列的值为null，则显示n

首先将部门表中某条记录的tel列的值更新为null；

update dept set tel=null where dname='市场部';

select d.did,d.dname,nvl(d.tel,'无') from dept d; 

伪列

    利用伪列ruwnum查询当前的纪录

select rownum,d.* from dept d where rownum between 1 and 3 

高级子查询

    成对比较，即在查询时，可以使用两个列同时和子查询的结果进行比较，子查询的结构也是两个列组成

查询和jacky在同一部门且性别相同的员工信息

select * from emp 
where (sex,did) in
(select sex,did from emp
where ename='jacky');

标量子查询

case

    用于标量子查询，例如查询员工信息，将m替换成“男”，f替换成“女”

select eid,ename,
    case sex
        when 'm' then '男'
        when 'f' then '女'
        else sex
    end
from emp;

exists

    用于存在性测试，通常在子查询中，需要将子、父查询的表相连接作为查询条件

查询存在员工的部门

select d.* from dept d where exists 
    (select e.* from emp e where e.did=d.did);

查询不存在员工的部门

select d.* from dept d where not exists 
    (select e.* from emp e where e.did=d.did);

with

    定义一个变量来表示一个语句，通过引用变量来引用子句，实现重用，提高效率

6.使用exp/imp备份与恢复数据

exp

    用于将数据表或用户导出，导出后可以将数据保存到磁盘文件中，以实现数据的备份。注意该命令是在控制台中运行，而非在sqlplus中运行。例如导出dept ,emp,history这三张表

1. 在执行exp命令时，提示输入登录的用户名和密码，登录成功后提示设置提取数据的缓冲区大小，默认是4096字节。按回车后提示导出到磁盘的哪个文件中
2. 准备将数据导出到f盘的tables.tab文件中，回车后提示是导出用户及其下的所有对象，还是只导出表，默认是导出用户。输入T表示导出表
3. 提示除了导出表的结构以外，是否也导出表中的数据，默认是yes
4. 提示是否压缩，默认是yes
5. 提示输入要导出的表名称，在这里准备导出dept、emp和history表，所以输入表的名称后按回车
6. 如果不需要再导出了，就直接按回车结束数据备份过程。之后在f盘下生成了一个tables.tab文件，该文件中包含了dept表、emp表和history表的结构定义及其所有数据
   
   

imp

1. 登录数据库删除history，dept，emp三张表，（为了练习导入备份）
2. cmd控制台下键入imp命令，提示并输入登录的用户名和密码后，输入刚才备份文件的路径及名称
3. 按回车后提示输入数据提取缓冲区的大小，默认是30720字节
4. 继续按回车后提示是否只列出导入文件的内容，而不将内容真正导入。当然是no
5. 此时提示如果对象已经存在的话，就忽略创建错误。默认是no，不忽略。所以直接按回车
6. 提示是否同时导入scott用户对这些表的访问权限，默认是yes。直接回车
7. 此时提示是否导入表数据，默认是yes。所以直接回车
8. 提示是否连tables.tab文件也一并导入，这个不需要，我们只需要其中的数据即可。默认是no
9. 之后提示输入用户的名称，输入后提示是导入所有的表，还是有选择性的导入某些表。如果导入所有的表就直接回车，否则输入要导入的表名称。这里导入所有的表，所以直接回车
10. 登录后，查看导入的表和数据是否完整