【原】oracle笔记整理

*****************
文件操作命令
*****************

===========start和@
说明：运行sql脚本
案例：sql>@ d:\a.sql 或者 sql>START d:\a.sql
===========edit
说明：该命令可以编辑指定的sql脚本
案例：sql>edit d:\a.sql
===========spool
说明：该命令可以将sql*plus屏幕上的内容输出到指定的文件中去

******************
登录操作命令scott tigger
******************

===========清屏命令
clear;

===========设置行长
set linesize 120;

===========打开时间 如：（10:53:05 SQL>)
set time on

===========打开执行语句时间
set timing on;

===========显示用户
show user

===========连接用户(conn,connect无区别)
conn system/manager
connect system/manager
conn system/manager as sysdba

===========创建用户(必须是有创建用户权限的登陆者，才能创建)
create user xiaoming identified by m123;

===========更改密码(在oracle自带的SQL Plus下使用)--回车后，根据提示做
password xiaoming;

passw

===========删除用户
--单一删除用户
drop user xiaoming
--连带把用户建的表也删除
drop user xiaoming cascade

===========授权用户(connect普通，dba最大，resource表空间建表)
grant connect to xiaoming
grant dba to xiaoming
grant resource to xiaoming

===========授权查询其他表(在sys,system,emp表所属人可以授权)
grant select on emp to xiaoming授予查询权限
..... update .. ... .. ........授予更新权限
..... all    .. ... .. ........授予所有权限

===========对象授权(让下一用户，可以继续传递这种权限给其他人)
grant select on emp to xiaoming with grant option

===========系统授权(对象和系统有区别一个是grant一个是admin)
system给xiaoming授权时：
grant connect to xiaoming with admin option;

===========收回权限(在sys,system,emp表所属人可以授权)
revoke select on emp to xiaoming授予查询权限
.....  update .. ... .. ........授予更新权限
.....  all    .. ... .. ........授予所有权限

============账户锁定
--设定文件
create profile lock_account
limit failed_login_attempts 3 password_lock_time 2;

--把锁赋给用户
alter user xiaoming profile lock_account

--把用户解锁
alter user xiaoming account unlock

===========终止口令(设定用户每10天延迟两天必须改密码)
--设定文件
create profile myprofile
limit password_life_time 10
password_grace_time 2;
--口令赋给用户
alter user xiaoming profile myprofile

===========口令历史(指定时间内必须更改密码，而且不能和以前一样)
--设定文件
create profile password_history limit
password_life_time 10 password_grace_time 2
password_reuse_time 10
--口令赋给用户
alter user xiaoming profile password_history

===========删除设定文件
drop profile filename

===========查询非本用户下的授权表
select * from scott.emp;

===========看表结构
desc test

*********************************
表的管理
*********************************

============字符型
char 定长 最大2000字符
char(10) 那么用不用10个都是占10个，但是查询时候快，适合做身份证
varchar2(20) 用多少空间算多少空间

============数字类型
number
-10的38次方到10的38次方
number(5,2)表示一个小数的5位整数，2位小数
-999.99---999.9
number(5)
-99999 - 99999

=============日期类型
date       包括年月日时分秒
timestamp  更加精确的

=============图片、声音、视频
blob       二进制数据  可以存放图片/声音 4G

***********************************************
=============创建表(有resource权限才能创建表)
create table test(userId varchar2(30),userName varchar2(30))

=============添加一个字段
alter table student add(classid number(2));

=============修改字段长度
alter table student modify (xm varchar2(30));

=============修改字段类型或者名字
alter table student modify (xm char(30));

=============删除一个字段
alter table student drop column sal;

=============修改表的名字
rename student to stu;

=============删除表
drop table student;

=============插入数据（时间类型需注意）
insert into student values(1,'小明','男','11-12月-1997',2453.2,14);

=============更改日期格式
alter session set nls_date_format='yyyy-mm-dd';

--也可以插入的时候设置
('13-03月-1988')
to_date('1988-12-12','yyyy-mm-dd')
to_date('1989/12/12','yyyy/mm/dd')

=============指定字段插入
insert into student(studentNumber,studentName,sex) values(123,'红红',女);

 

**********************
增删查改控制
**********************

=============疯狂复制插入
insert into student(studentNumber,studentName,sex) select * from student;

=============插入多行数据
insert into kkk (Myid,myname,mydept) select empno,ename,deptno from emp where deptno = 10;

=============删除数据

--删除所有记录，表结构还在，写日志，可以回复，速度慢
delete from student;

--删除表的结构和数据
drop table student;

--删除一条记录
delete from student where xh='1001';

--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
truncate table student;

==============改一个字段
update student set sex='女' where xh='1001';

==============子查询，多条件，更新
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';

==============修改多个字段
update student set sex='男',birthday='1989-04-01'
where xh='1001';

=============查询为空与不为空
select * from student where birthday is null;
select * from student where birthday is not null;

=============查询指定列
select ename,sal,job from emp;

=============条件查询（where）
select ename,sal,job from emp where ename='SMITH';

=============条件查询（where,<）
select ename,hiredate from emp where hiredate>'1-1月-1982';

=============条件查询（where,and,< = >）
select ename,sal from emp where sal>=2000 and sal<=2500;

=============条件查询(like % _)..一个下划线代表一个字符
select ename,sal from emp where ename like '__O%';

=============条件查询（like %）
select ename,sal from emp where ename like 'S%';

=============条件查询（or in）..in可以代替or使用
select * from emp where empno=123 or empno=456 or...;

select * from emp where empno in(123,456,...);

=============条件查询（null,not null）..空查询
select * from emp where mgr is null;

select * from emp where mgr is not null;

=============条件查询（or and）
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

=============条件查询（order by）按照 什么什么排序 顺序排列
select * from emp order by sal;

=============条件查询（order by desc）倒叙
select * from emp order by sal desc;

=============条件查询（order by asc desc）一个升序一个降序
select * from emp order by deptno asc,sal desc;

=============条件查询（order by as）多字段 as 别名 按照别名排序
select ename,(sal+nvl(comm,0))*13 as "年薪" from emp order by "年薪"；

=============条件查询（数据分组max min avg sum count）
select max(sal),min(sal) from emp;

=============条件查询（max min 子查询）
select ename,sal from emp where sal=(select max(sal) from emp);

select ename,sal from emp where sal=(select max(sal) from emp) or sal = (select min(sal) from emp);

=============条件查询（单表 单行子查询 =）
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

=============条件查询（单表 多行子查询 in）
select * from emp where job in (select distinct job from emp where deptno = 10);

=============条件查询（单表 多行子查询 all）大于all大于全部
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);

=============条件查询（单表 多行子查询any）大于any 值大于期中一个就可以了
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno = 30);

=============条件查询（单表 多行 多列查询）
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

=============条件查询（单表 多行 max）
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);

=============条件查询（from 里面使用子查询）内嵌试图
select a1.ename,a1.sal,a1.deptno,a2.mysal
from emp a1,(select deptno,avg(sal) mysql from emp) group by deptno) a2
where a1.deptno = a2.deptno and a1.sal > a2.mysal;

=============条件查询（三种分页之 rownum）
--查询视图
select * from emp;
--第一次分页（分尾）
select a1.*,rownum rn from (select * from emp) a1 where rownum <=10;
--第二次分页（分头）
select * from (select a1.*,rownum rn from (selct * from emp) a1 where rownum <= 10) where rn >= 5;

select * from (select a1.*,rownum rn from emp a1 where rownum < 10) where rn >=5;

select a2.* from (select a1.*,rownum rn from emp a1 where rownum < 10) a2 where rn >=5;

=============条件查询（三种分页之 rowid 分页）
select * from t_xiaoxi where rowid in(select rid from (select fownum rn,rid from(select rowid rid,cid from
t_xiaoxi order by cid desc) where rownum < 10000) where rn > 9980) roder by cid desc;

=============条件查询（合并查询union,union all,intersect,minus）
select ename,sal,job from emp where sal > 2500 union
select ename,sal,job from emp where job='manager';

=============条件查询(查询结果，创建表)
create table myemp2 (id,ename) as select empno,ename from emp;

=============条件查询(avg)平均查询
select avg(sal) from emp;

=============条件查询(avg 子查询)
select * from emp where sal > (select avg(sal) from emp);

=============条件查询（group by）【 group by， having， order by】使用顺序
--如果下面的deptno不是在后面group by中出现，那么就得删了它，因为它和前面的函数不属于同一列
select avg(sal),max(sal),deptno from emp group by deptno;

select avg(sal),max(sal),deptno,jop from emp group by deptno,job;

=============条件查询（having）having 对分组进行筛选
select avg(sal),max(sal),deptno from emp group by deptno having avg(avg) > 2000;

=============条件查询（多表查询）
--查询两个表 建立= 以作链接
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;

=============条件查询（多表 and）
select e.ename,e.sal,d.name from emp e,dept d where e.deptno = d.deptno and d.deptno = 10;

=============条件查询（多表 between）between 在什么范围内
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

=============条件查询（多表 order by）
select a1.ename,a2.dname,a1.sal from emp a1,empt a2 where a1.deptno = a2.deptno order by a1.deptno;

=============条件查询（一表 多用 and）
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno;

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD'

=============算数查询
select sal*13,ename from emp;

=============查询取别名
select sal*13 "年工资" ,ename from emp;

=============查询nvl处理null函数
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;

=============删除重复的
select distinct deptno,job from emp;

**********************
sql函数
**********************

==============字符函数

--将字符串转化为小写
lower(char)
select lower(ename) from emp;

--将字符串转换为大写
upper(char)
select upper(ename) from emp;

--返回字符串的长度
length(char)
select * from emp where length(ename)=5;

--取字符串的字串(从1开始取 取3个字符)
substr(char,m,n)
select substr(ename,1,3) from emp;

--替换函数
replace(char1,search_string,replace_string)
select replace(ename,'A','老鼠') from emp;

--找字串的位置
instr(char1,char2,[,n[,m]])

--合并字符串（||合并符号）
 select upper(substr(ename,1,1)) ||lower(substr(ename,2,length(ename)-1)) from emp;

==============数学函数
包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round

--round(n,[m])四舍五入

select round(sal) from emp;

select round(comm,1),comm from emp;
意思：55.7 55.66

--trunc(n,[m])截取数字

select trunc(comm,1),comm from emp;
意思：55.6 55.66

--mod(m,n)取摸
select sal(10,2) from emp;

--floor(n)返回小于或是等于n的最大整数
select floor(comm),comm from emp;

--ceil(n)返回大于或是等于n的最小正数
select ceil(comm),comm from emp;

--abs(n) 返回数字n的绝对值
--select abs(-13) from dual;
--acos(n) 返回数字的反余旋
--atan(n) 返回数字反正切
--cos(n)
--exp(n)  返回e的n次幂
--log(m,n) 返回对数值
--power(m,n)返回m的n次幂

==============日期函数

--oracle默认日期格式
dd-mon-yy 既12-7月-1988

--sysdate返回系统时间
select sysdate from emp;

--hirdate(xx,8)加上8个月
select * from emp where sysdate > add_months(hiredate,8);

select trunc(sysdate-hiredate) "入职天数",ename from emp;

--last_day(hiredate)返回本月的最后一天
select hiredate,last_day(hiredate) from emp;

select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

==============转换函数(类型之间自动转换)
--例子
create table t1(id int);
insert into t1 values('10')

create table t2(id varchar2(10));
insert into t2 values(1);

--to_char(hiredate,"YYYY-mm-dd hh24:mi:ss")
select ename,to_char(hiredate,'YYYY-mm-dd hh24:mi:ss') from emp;

--to_char(sal,"L99999.99")
select ename,to_char(hiredate,'YYYY-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;

找出1980入职的
select * from emp where to_char(hiredate,'YYYY') = 1980;

----------------------------
yy:两位数字的年份 2004--04 
yyyy:四位数字的年份 2004年 
mm：两位数字的年份           
dd：2位数字的天 30-->30    
hh24：8点 --》20           
hh12：8点 --》08           
mi，ss --》显示分钟\秒  
<---------------------------->
9:显示数字，并忽略前面0
0:显示数字，位数不足用0补充
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前面加美元*to_char(sal,'$99,999.99')* 
L:本地的字符集符号
C:国际货币符号
G:在指定位置显示组分隔符
D:在指定位置显示小数点符号
<---------------------------->

==============sys_context('userenv','db_name')

1) terminal:当前回话客户所对应的中断标识符
2) lanuage:语言
3) db_name:当前数据库名称
4) nls_date_format:当前回话客户所对应的日期格式
5) session_user:当前回话客户所对应的数据库用户名
6) current_schema:当前回话客户所对应默认方案名
7) host:返回数据库所在主机的名称

查询所使用的是哪个数据库
select sys_context('userenv','db_name') from dual;

**********************
事务管理
**********************

=============提交事务（让事务提交，也就是删了保存点）
commit

=============建立保存点
savepoint a1;

savepoint a2;

=============返回事务
--返回第一个保存点
rollback to a1;

--返回第二个保存点
rollback to a2;

--返回所有设置的保存点
rollback;

==============只读事务
set transaction read only;

**********************
数据库管理
**********************

=========登录方式
sys 必须以sysdba 或者 sysoper形式登录 不能以normal方式登录数据库

system 可以以normal方式登录

=========关闭数据库启动数据库
--关闭
shutdown;
--开启
startup;

=========显示参数
show parameter;

=========备份和恢复（导出导入）oracle->bin->exp.exe(并把此文件路径复制给dos下执行)
--逻辑备份（open下）

导出自己的一张表
exp userid=scott/tigger@orcl tables(emp) file=d:\e1.demp

导出自己的多张表
exp userid=scott/tigger@orcl tables(emp,dept) file=d:\e1.demp

导出其他方案的表
exp_full_database的权限，比如system就可以导出scott的表
exp userid=system/manager@orcl tables=(scott.emp)  file=d:\e1.demp

导出表结构(无数据只有结构)
exp userid=scott/tigger@orcl tables=(emp)  file=d:\e1.demp rows = n

直接导出方式（常用作处理大表）
exp userid=scott/tigger@orcl talbes=(emp) file=d:\e1.demp direct = y

导出方案
exp userid=scott/tigger@orcl owner=scott file=d:\e1.demp

导出其他方案
exp userid=system/manager@orcl owner(system,scott) file=d:\e1.demp
--物理备份（任何下）

<------------------------->
导出分为：导出表，导出方案，导出数据库三种方式
导出exp命令，常用选项
userid:用于指定执行导出操作的用户名，口令，连接字符串
tables:用于指定执行导出操作的表
owner: 用于指定执行导出操作的方案
full=y:用于指定执行导出操作的数据库
inctype:用于指定执行导出操作的增量类型
rows:用于指定执行导出操作是否要导出表中的数据
file:用于指定导出文件名
<------------------------->

**********************
数据恢复
**********************

=========1:建立回滚点
savepoint aa;

=========2:删除数据(可回复删除)
delete from student;

=========3:恢复数据
rollback to aa;

=========自增长归零
alter table onehref auto_increment = 0;

**********************
java操作oracle数据库
**********************

=========jdbc_odbc连接
--第一步
控制面板->管理工具->数据源ODBC->添加Oracle in OraDb10_home1->完成->OK->确定

--第二部java代码
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection ct = DriverManager.getConnection("jdbc:odbc:test","scott","tigger");

=========jdbc连接
--第一步
加载jar包

--第二步
import java.sql.*;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tigger")

=========java里面控制事务
--设置不能默认提交（默认不让执行一句提交一句）
ct.setAutoCommit(false);

--提交事务（执行完sql语句提交）
ct.commit();

--异常回滚(在发生异常里面写)
ct.rollback();