Java基础——Oracle（五）

一、Oracle  中的分页

1) select * from emp;

2)select * ,rownum from emp; //这样写不行

3)select ename,job,sal,rownum from emp; //可以

ENAME      JOB             SAL     ROWNUM
---------- --------- --------- ----------
SMITH      CLERK        800.00          1
ALLEN      SALESMAN    1600.00          2
WARD       SALESMAN    1250.00          3
JONES      MANAGER     2975.00          4

4)select a1.*,rownum  from (select * from emp) a1 ;

5)select a1.*,rownum  from (select * from emp) a1 and rownum <=10  ; //只查前10条  用过一次,就不能再用第二次

6)select * from  (select a1.*,rownum 行号 from (select * from emp) a1 where rownum <=10)  where 行号>=5;   查的是5到10条

查询的变化,如果有改动,只要改变里面的查询即可,不管是排序,还是指定列查询

二、Oracle 中的函数

1.字符串函数  

--lower (char) //  select lower('AAA') from dual; ->aaa

--upper(char)

--length() //select length('AAA') from dual -> 3

--substr(char,m,n) 取子串

--replace(char1,search_string,replace_string) //显示所有emp姓名,用 a 替换所有的 A  -> select replace(ename,'A','a') from emp;

--instr(char1,char2,[,n[,m]] 取子串在字符串中的位置

2.数学函数 

--round(n,[m]) 四舍五入,省掉m则四舍五入到整数,如果m为正,则四舍五入到小数点的m位后,如果m是负数,则舍入到小数点的m位前

--trunc (n,[m]) 用于截取数字,省m则截掉小数部分,如果m为正,则四舍五入到小数点的m位后,如果m是负数,则舍入到小数点的m位前

--mod(m,n) 求模

--floor(n) 反回小于或是等于 n的最大整数

--ceil(n) 反回大于或是等于n 的最小整数

3.日期函数

--sysdate 返回系统时间     select sysdate from dual;  mysql 用 now() , sqlserver 用 getDate()

--add_month(d,n)  //查找已经入职 8 个月多的员工 select * from scott.emp where  sysdate>add_months(hiredate,8);

--last_month(d) 返回指定日期所在月份的最后一天  //这个d 就是指定的日期

4.转换函数

用于将数据从一种类型转换成别一种在某些情况下,oracle server 允许值的数据类型和实际的不一样,这时oracle 会隐含的转变数据类型。比如 可以将 '11' 传给 number类型。

反之也可以,将11 传给 varchar2日期是否可以显示时分秒?货币可否加 货币符号

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from scott.emp;
// 本例没写错,上面的确实是 hh24,并且 那个地方确实是 mi

yyyy 表示 年份

mm 表示 月份

dd 表示 天

hh24 表示 小时

mi 表示 分钟

ss 表示 秒

如果要写成  yyyy-MM-dd hh:mm:ss 这样就不对了,因为 Oracle 的烂格式很特

5.系统函数

SYS_context('...','...')     //返回一个指定namespace下的parameter值。该函数可以在SQL和PL/SQL语言中使用。以下的例 子都要写成 select sys_context('userenv','xxxx') from dual 这样的方式：

1 terminal 当前会话客户所对应的终端的标识符   select sys_context('userenv','terminal') from dual; -> AAA-74B992CC317

2 language 语言 ->SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3 db_name 当前数据库名称 ->orcl

4 nls_date_format 当前会话所对应的日期格式 ->DD-MON-RR

5 session_user 当前会话所对应的数据库用户名 ->SA

6 current_schema 当前会话客户所对应的默认方案名 SA

7 host 返回数据库所在的主机名称   ->WORKGROUPAAA-74B992CC317

三、关于 dual 的说明

如下查询  select 10+5 ; 可以发现出错: 未找到要求的from 关键字,但这样的写法在mysql或sqlserver都是可以的，在oracle 中 要改写成 select 10+5 from dual

T-SQL是SQL Server的语言引擎，而Oracle的语言引擎却是 PLSQL。

这两种查询语言都对ANSI SQL-92标准进行了扩展以提供额外的支持力度。

用PLSQL执行数据查询的时候，FROM子句是必须的，这同SQL Server的要求是不一样的。

SELECT语句必须选择针对的数据表。在Oracle数据库内有一种特殊的表Dual。

Dual表是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select中Dual表由Oracle连同数据字典一同创建，所有的用户都可以用名称DUAL访问该表。这个表里只有一列dummy，该列定义为VARCHAR2(1)类型，有一行值X。从DUAL表选择数据常被用来通过SELECT语句计算常数表达式，由于DUAL只有一行数据，所以常数只返回一次。

==几个常见的查询

//查看当前连接的用户
select user from dual;

//查看当前日期
select user from dual;

==几个常见的查询操作    

//用查询结果创建新表
create table T(id,name,sal, deptno)  as select empno,ename,sal,deptno from emp;

//使用特定的格式插入日期 (使用 to_date 函数)
insert into emp values (2000,'张三','XXX',9000,to_date('1999-12-15','yyyy-MM-dd'),3000,500,20); 

//使用子查询插入数据
//例子 : 把emp表中某些符合条件的数据,导入到 T2表中
create table T2(id number(5), stuName varchar2(20),deptNo number(5) ) ;
insert into T2 (id,stuName,deptNO) select empno,ename,deptNO from emp where sal <2000

//使用子查询更新数据
//例子 希望工 scott 的 岗位,工资,奖金 和smith 一样
update emp set (job,sal,comm) =(select job,sal,comm from emp where ename='SCOTT') where ename='SMITH'; 

四、关于 sys 和 system用户

用户和方案的关系 一个用户被创建以后,会创建一个对应的方案,和用户名一样,方案里装的数据对象(表,视图,序列....)

数据库管理员

每个oracle都有一个或多个dba ,他的主要工作是什么

1 安装和升级oracle数据库

2 建库,建表空间,建表,视图,索引

3 定制并实施备份计算和恢复计划

4 数据库的权限管理,调优故障处理

5 高级dba 能参与项目开发,会写sql语句,存储过程,触发器,函数,约束等

管理员管理数据库用的用户是 sys 和 system

最主要的区别 :存储的数据的重要性不同

sys: 所有的数据字典和基表和视图都放在sys用户中,这些东西对oracle的运行是至关重要的,由数据库自已维护，任何用户都不能修改,sys 用户拥有 dba,sysdab,sysoper 角色或权限 ,是高权限用户

sysdba >sysoper >dba

system 用于存放次一级的数据, 比如orcle 的一些特性或工具的管理信息,它有dba,sysdba 角色或权限

sys用户必须以 as sysdba 或 sysoper 的方式登录 不能normal 方式登录

system 可以以sysdba 的身份登录的,如果以这样的方式登录,其实登录的就是sys

五、数据库(表)的逻辑备分与恢复

逻辑备份: 用工具 export 将数据对象的结构和数据以文件的方式导出的过程

逻辑恢复: 用工具 import 从备份的文件把数据对象恢复的过程

物理备份和恢复: 在数库open 的状态下或关闭的状态下均可进行,但逻辑备份和恢复必须在open的状态的才能进行

== 导出

分三种 导出表 ,导出方案, 导出数据库 //用户和方案的关系:一个用户创建以后,对应着一个方案,方案名和用户名一样,方案里装的是数据对象

导出用 exp 命令 ,它有很多常用的选项

-- userid 用于指定执行导出操作的用户名,口令,连接字符串 //scott/scott@orcl

-- tables 用于指定要导出的表

-- owner  执行导出操作的方案

-- full=y 指定导出的是整个数据库

-- inctype 指导出操作的增量类型

-- rows 指定导出操作是否要导出表中的数据 // rows=n 表示不导出数据,只导出表结构

-- file 指定文件名

--导出表

1) 导出自己的表 比如 scott 导出自已的 emp 和 dept 表

exp userid=scott/scott@orcl tables=(emp,dept) file="c:scott.dmp"
//注意 export 是oracle带的工具,在 C:oracleproduct10.2.0db_2in 目录下 叫 exp.exe

2) 导出其他方案中的表

要导出其他方案中的表,要有dba 的权限或  exp_full_database 权限

3) 导出表结构

在上面的操作后面加上 rows=n 即可

4) 使用直接导出的方式

在上面的语句后面加上 direct=y 即可

速度快,数据量大的时候适用,但要求 数据库的字符集和客户端的字符集一致,否则会报错

== 导入

导入有导入表,导入方案,导入数据库

导入用的命令是 imp

常用的选项

-- userid 指定导入用的用户名和密码连接串

-- tables 执行导入的表

-- fromuser 指定源用户

-- touser :指定目标用户

-- file 指定要导入的文件名

-- inctype 指导入操作的增量类型

-- rows 指定导入操作是否要导入表中的数据  //rows=n 表示不导入数据,只导入表结构

-- ignore 如果表存在,则只导入数据

导入表

1) 导入自己的表

imp userid=scott/scott@orcl tables=(emp,dept) file=c:scott.dmp

2) 把某个或某些表导给其他用户

imp userid=system/system@orcl tables=(dept,emp)  file=c:scott.dmp touser=zs
//这里不要加 scott.  还有,要注意约束造成的问题

3 导入表结构

imp userid=scott/scott@orcl tables=(emp,dept) file=c:scott.dmp rows =n

4) 导入数据

imp userid=scott/scott@orcl tables=(emp,dept) file=c:scott.dmp ignore=y

导入方案

1) 导入自己的方案

imp userid=scott/scott file=d:xxx.dmp;

2) 导入其他方案

imp userid system/aaaaaaaa file=d:xxx.dmp fromuser=system touser=scott;

3) 导入数据库

imp userid system/aaaaaaaa  full=y  file=c:database.dmp;