【原】oracle筆記整理

小傅哥 2021-12-13

展開全文

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

===========start和@
說明：運(yùn)行sql腳本
案例：sql>@ d:\a.sql 或者 sql>START d:\a.sql
===========edit
說明：該命令可以編輯指定的sql腳本
案例：sql>edit d:\a.sql
===========spool
說明：該命令可以將sql*plus屏幕上的內(nèi)容輸出到指定的文件中去

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

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

===========設(shè)置行長
set linesize 120;

===========打開時(shí)間如：（10:53:05 SQL>)
set time on

===========打開執(zhí)行語句時(shí)間
set timing on;

===========顯示用戶
show user

===========連接用戶(conn,connect無區(qū)別)
conn system/manager
connect system/manager
conn system/manager as sysdba

===========創(chuàng)建用戶(必須是有創(chuàng)建用戶權(quán)限的登陸者，才能創(chuàng)建)
create user xiaoming identified by m123;

===========更改密碼(在oracle自帶的SQL Plus下使用)--回車后，根據(jù)提示做
password xiaoming;

passw

===========刪除用戶
--單一刪除用戶
drop user xiaoming
--連帶把用戶建的表也刪除
drop user xiaoming cascade

===========授權(quán)用戶(connect普通，dba最大，resource表空間建表)
grant connect to xiaoming
grant dba to xiaoming
grant resource to xiaoming

===========授權(quán)查詢其他表(在sys,system,emp表所屬人可以授權(quán))
grant select on emp to xiaoming授予查詢權(quán)限
..... update .. ... .. ........授予更新權(quán)限
..... all? ? .. ... .. ........授予所有權(quán)限

===========對象授權(quán)(讓下一用戶，可以繼續(xù)傳遞這種權(quán)限給其他人)
grant select on emp to xiaoming with grant option

===========系統(tǒng)授權(quán)(對象和系統(tǒng)有區(qū)別一個(gè)是grant一個(gè)是admin)
system給xiaoming授權(quán)時(shí)：
grant connect to xiaoming with admin option;

===========收回權(quán)限(在sys,system,emp表所屬人可以授權(quán))
revoke select on emp to xiaoming授予查詢權(quán)限
.....? update .. ... .. ........授予更新權(quán)限
.....? all? ? .. ... .. ........授予所有權(quán)限

============賬戶鎖定
--設(shè)定文件
create profile lock_account
limit failed_login_attempts 3 password_lock_time 2;

--把鎖賦給用戶
alter user xiaoming profile lock_account

--把用戶解鎖
alter user xiaoming account unlock

===========終止口令(設(shè)定用戶每10天延遲兩天必須改密碼)
--設(shè)定文件
create profile myprofile
limit password_life_time 10
password_grace_time 2;
--口令賦給用戶
alter user xiaoming profile myprofile

===========口令歷史(指定時(shí)間內(nèi)必須更改密碼，而且不能和以前一樣)
--設(shè)定文件
create profile password_history limit
password_life_time 10 password_grace_time 2
password_reuse_time 10
--口令賦給用戶
alter user xiaoming profile password_history

===========刪除設(shè)定文件
drop profile filename

===========查詢非本用戶下的授權(quán)表
select * from scott.emp;

===========看表結(jié)構(gòu)
desc test

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

============字符型
char 定長最大2000字符
char(10) 那么用不用10個(gè)都是占10個(gè)，但是查詢時(shí)候快，適合做身份證
varchar2(20) 用多少空間算多少空間

============數(shù)字類型
number
-10的38次方到10的38次方
number(5,2)表示一個(gè)小數(shù)的5位整數(shù)，2位小數(shù)
-999.99---999.9
number(5)
-99999 - 99999

=============日期類型
date???? ? 包括年月日時(shí)分秒
timestamp? 更加精確的

=============圖片、聲音、視頻
blob???? ? 二進(jìn)制數(shù)據(jù)? 可以存放圖片/聲音 4G

***********************************************
=============創(chuàng)建表(有resource權(quán)限才能創(chuàng)建表)
create table test(userId varchar2(30),userName varchar2(30))

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

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

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

=============刪除一個(gè)字段
alter table student drop column sal;

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

=============刪除表
drop table student;

=============插入數(shù)據(jù)（時(shí)間類型需注意）
insert into student values(1,'小明','男','11-12月-1997',2453.2,14);

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

--也可以插入的時(shí)候設(shè)置
('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,'紅紅',女);

**********************
增刪查改控制
**********************

=============瘋狂復(fù)制插入
insert into student(studentNumber,studentName,sex) select * from student;

=============插入多行數(shù)據(jù)
insert into kkk (Myid,myname,mydept) select empno,ename,deptno from emp where deptno = 10;

=============刪除數(shù)據(jù)

--刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以回復(fù)，速度慢
delete from student;

--刪除表的結(jié)構(gòu)和數(shù)據(jù)
drop table student;

--刪除一條記錄
delete from student where xh='1001';

--刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快。
truncate table student;

==============改一個(gè)字段
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';

==============修改多個(gè)字段
update student set sex='男',birthday='1989-04-01'
where xh='1001';

=============查詢?yōu)榭张c不為空
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 % _)..一個(gè)下劃線代表一個(gè)字符
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）一個(gè)升序一個(gè)降序
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 "年薪"；

=============條件查詢（數(shù)據(jù)分組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 值大于期中一個(gè)就可以了
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 里面使用子查詢）內(nèi)嵌試圖
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';

=============條件查詢(查詢結(jié)果，創(chuàng)建表)
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中出現(xiàn)，那么就得刪了它，因?yàn)樗颓懊娴暮瘮?shù)不屬于同一列
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 對分組進(jìn)行篩選
select avg(sal),max(sal),deptno from emp group by deptno having avg(avg) > 2000;

=============條件查詢（多表查詢）
--查詢兩個(gè)表建立= 以作鏈接
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 在什么范圍內(nèi)
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'

=============算數(shù)查詢
select sal*13,ename from emp;

=============查詢?nèi)e名
select sal*13 "年工資" ,ename from emp;

=============查詢nvl處理null函數(shù)
select sal*13+nvl(comm,0)*13 "年工資",ename,comm from emp;

=============刪除重復(fù)的
select distinct deptno,job from emp;

**********************
sql函數(shù)
**********************

==============字符函數(shù)

--將字符串轉(zhuǎn)化為小寫
lower(char)
select lower(ename) from emp;

--將字符串轉(zhuǎn)換為大寫
upper(char)
select upper(ename) from emp;

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

--取字符串的字串(從1開始取取3個(gè)字符)
substr(char,m,n)
select substr(ename,1,3) from emp;

--替換函數(shù)
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;

==============數(shù)學(xué)函數(shù)
包括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])截取數(shù)字

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

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

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

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

--abs(n) 返回?cái)?shù)字n的絕對值
--select abs(-13) from dual;
--acos(n) 返回?cái)?shù)字的反余旋
--atan(n) 返回?cái)?shù)字反正切
--cos(n)
--exp(n)? 返回e的n次冪
--log(m,n) 返回對數(shù)值
--power(m,n)返回m的n次冪

==============日期函數(shù)

--oracle默認(rèn)日期格式
dd-mon-yy 既12-7月-1988

--sysdate返回系統(tǒng)時(shí)間
select sysdate from emp;

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

select trunc(sysdate-hiredate) "入職天數(shù)",ename from emp;

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

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

==============轉(zhuǎn)換函數(shù)(類型之間自動(dòng)轉(zhuǎn)換)
--例子
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:兩位數(shù)字的年份 2004--04?
yyyy:四位數(shù)字的年份 2004年?
mm：兩位數(shù)字的年份????????? ?
dd：2位數(shù)字的天 30-->30?? ?
hh24：8點(diǎn) --》20????????? ?
hh12：8點(diǎn) --》08????????? ?
mi，ss --》顯示分鐘\秒 ?
<---------------------------->
9:顯示數(shù)字，并忽略前面0
0:顯示數(shù)字，位數(shù)不足用0補(bǔ)充
.:在指定位置顯示小數(shù)點(diǎn)
,:在指定位置顯示逗號
$:在數(shù)字前面加美元*to_char(sal,'$99,999.99')*?
L:本地的字符集符號
C:國際貨幣符號
G:在指定位置顯示組分隔符
D:在指定位置顯示小數(shù)點(diǎn)符號
<---------------------------->

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

1) terminal:當(dāng)前回話客戶所對應(yīng)的中斷標(biāo)識符
2) lanuage:語言
3) db_name:當(dāng)前數(shù)據(jù)庫名稱
4) nls_date_format:當(dāng)前回話客戶所對應(yīng)的日期格式
5) session_user:當(dāng)前回話客戶所對應(yīng)的數(shù)據(jù)庫用戶名
6) current_schema:當(dāng)前回話客戶所對應(yīng)默認(rèn)方案名
7) host:返回?cái)?shù)據(jù)庫所在主機(jī)的名稱

查詢所使用的是哪個(gè)數(shù)據(jù)庫
select sys_context('userenv','db_name') from dual;

**********************
事務(wù)管理
**********************

=============提交事務(wù)（讓事務(wù)提交，也就是刪了保存點(diǎn)）
commit

=============建立保存點(diǎn)
savepoint a1;

savepoint a2;

=============返回事務(wù)
--返回第一個(gè)保存點(diǎn)
rollback to a1;

--返回第二個(gè)保存點(diǎn)
rollback to a2;

--返回所有設(shè)置的保存點(diǎn)
rollback;

==============只讀事務(wù)
set transaction read only;

**********************
數(shù)據(jù)庫管理
**********************

=========登錄方式
sys 必須以sysdba 或者 sysoper形式登錄不能以normal方式登錄數(shù)據(jù)庫

system 可以以normal方式登錄

=========關(guān)閉數(shù)據(jù)庫啟動(dòng)數(shù)據(jù)庫
--關(guān)閉
shutdown;
--開啟
startup;

=========顯示參數(shù)
show parameter;

=========備份和恢復(fù)（導(dǎo)出導(dǎo)入）oracle->bin->exp.exe(并把此文件路徑復(fù)制給dos下執(zhí)行)
--邏輯備份（open下）

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

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

導(dǎo)出其他方案的表
exp_full_database的權(quán)限，比如system就可以導(dǎo)出scott的表
exp userid=system/manager@orcl tables=(scott.emp)? file=d:\e1.demp

導(dǎo)出表結(jié)構(gòu)(無數(shù)據(jù)只有結(jié)構(gòu))
exp userid=scott/tigger@orcl tables=(emp)? file=d:\e1.demp rows = n

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

導(dǎo)出方案
exp userid=scott/tigger@orcl owner=scott file=d:\e1.demp

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

<------------------------->
導(dǎo)出分為：導(dǎo)出表，導(dǎo)出方案，導(dǎo)出數(shù)據(jù)庫三種方式
導(dǎo)出exp命令，常用選項(xiàng)
userid:用于指定執(zhí)行導(dǎo)出操作的用戶名，口令，連接字符串
tables:用于指定執(zhí)行導(dǎo)出操作的表
owner: 用于指定執(zhí)行導(dǎo)出操作的方案
full=y:用于指定執(zhí)行導(dǎo)出操作的數(shù)據(jù)庫
inctype:用于指定執(zhí)行導(dǎo)出操作的增量類型
rows:用于指定執(zhí)行導(dǎo)出操作是否要導(dǎo)出表中的數(shù)據(jù)
file:用于指定導(dǎo)出文件名
<------------------------->

**********************
數(shù)據(jù)恢復(fù)
**********************

=========1:建立回滾點(diǎn)
savepoint aa;

=========2:刪除數(shù)據(jù)(可回復(fù)刪除)
delete from student;

=========3:恢復(fù)數(shù)據(jù)
rollback to aa;

=========自增長歸零
alter table onehref auto_increment = 0;

**********************
java操作oracle數(shù)據(jù)庫
**********************

=========jdbc_odbc連接
--第一步
控制面板->管理工具->數(shù)據(jù)源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里面控制事務(wù)
--設(shè)置不能默認(rèn)提交（默認(rèn)不讓執(zhí)行一句提交一句）
ct.setAutoCommit(false);

--提交事務(wù)（執(zhí)行完sql語句提交）
ct.commit();

--異?；貪L(在發(fā)生異常里面寫)
ct.rollback();