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这三张表
- 在执行exp命令时,提示输入登录的用户名和密码,登录成功后提示设置提取数据的缓冲区大小,默认是4096字节。按回车后提示导出到磁盘的哪个文件中
- 准备将数据导出到f盘的tables.tab文件中,回车后提示是导出用户及其下的所有对象,还是只导出表,默认是导出用户。输入T表示导出表
- 提示除了导出表的结构以外,是否也导出表中的数据,默认是yes
- 提示是否压缩,默认是yes
- 提示输入要导出的表名称,在这里准备导出dept、emp和history表,所以输入表的名称后按回车
- 如果不需要再导出了,就直接按回车结束数据备份过程。之后在f盘下生成了一个tables.tab文件,该文件中包含了dept表、emp表和history表的结构定义及其所有数据
imp
- 登录数据库删除history,dept,emp三张表,(为了练习导入备份)
- cmd控制台下键入imp命令,提示并输入登录的用户名和密码后,输入刚才备份文件的路径及名称
- 按回车后提示输入数据提取缓冲区的大小,默认是30720字节
- 继续按回车后提示是否只列出导入文件的内容,而不将内容真正导入。当然是no
- 此时提示如果对象已经存在的话,就忽略创建错误。默认是no,不忽略。所以直接按回车
- 提示是否同时导入scott用户对这些表的访问权限,默认是yes。直接回车
- 此时提示是否导入表数据,默认是yes。所以直接回车
- 提示是否连tables.tab文件也一并导入,这个不需要,我们只需要其中的数据即可。默认是no
- 之后提示输入用户的名称,输入后提示是导入所有的表,还是有选择性的导入某些表。如果导入所有的表就直接回车,否则输入要导入的表名称。这里导入所有的表,所以直接回车
- 登录后,查看导入的表和数据是否完整