雪月书韵茶香

Oracle-函数与备份

1.数学函数

2.字符函数

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.日期函数

3.1日期格式元素

4.转换函数

#####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数字格式元素

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

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