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 返回数据库所在的主机名称 ->WORKGROUP\AAA-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:\oracle\product\10.2.0\db_2\bin 目录下 叫 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;
