oracleocp知識點
1.如何確定數據庫是否啟動
su - oracle
ps -ef |grep ora_|head -2
兩種關系數據庫是ora或者是自動存儲管理的asm開頭的，
查看進程可以知道數據庫實例至少已經啟動，但是數據庫是否掛載，是否打開，不得而知
asm
crs_stat -t
updb.db online
srvctl status database -d updb
db
sqlplus -s / as sysdba
select open_mode from database;
通過查詢該視圖可以知道數據庫是否啟動，該視圖顯示了數據的狀態
asm
cat .asm
export ORACLE_HOME=/u01/grid/11g
export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export ORACLE_SID=+ASM
grep asm .bashrc
alias asm=". ~/.asm"
cre_stop ora.updb.db
srvctl status/stop database -d updb
srvctl status/stop/start database -d updb -o
2.如何確定數據庫實例名
ps -ef |grep ora_|head -1 |cut -d '_' -f3
sqlplus / as sysdba
select instance_name from v$instance;
後台進程和數據庫查詢兩種方式
export ORACLE_SID=updb
echo $ORACLE_SID
source !$
. !$
oracle_sid在windows不區分大小寫，但是其他系統區分大小寫
grep SID .db11g
ps -ef|grep ora_|cut -d '_' -f3|sort|uniq
多個實例，只需要通過export ORACLE_SID=updb切換
3.如何連接到sqlplus
[oracle@oracle ~]$ tail -l /etc/oratab
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
wyzc10g:/u01/oracle/10g:N
wyzc11g:/u01/oracle/11g:N # line added by Agent
wyzc:/u01/oracle/12c:N: # line added by Agent
export ORACLE_SID=wyzc10g
export ORACLE_HOME=/u01/oracle/10g
export PATH=
which sqlplus
sqlplus /nolog
解鎖用戶
show user
alter user scott indentified by tiger account unlock;
4.查看用戶下有哪些表
select * from tab;
select table_name from user_tables;
5.如何查看表結構
set linesize 50
desc dept
6.select 語句的功能
列查詢、行查詢、多表查詢
書寫規則：
不區分大小寫
可以分行書寫
關鍵詞不能簡寫或分割跨行
通常一個子句一行
雙引號加起的語句必須區分大小寫
sqldelevoper
/u01/oracle/11g/sqldevdloper/sqldeveloper.sh
7.默認列標題
sqlplus
字符型、日期靠左對齊
number 靠右對齊
開發工具
靠左
8.算數表達式
空值 defining a null value
null is value that is unavailable,unassigned,,unknown,or inapplicable
null is not the same as zero or a blank space
不能進行算數運算，否則為空
要進行算數運算，需要空值函數運算，nvl(字段,0)
9.別名
重命名列標題，有益於運算，直接跟在列名之後，as是可選的字符
包含空格、特殊字符、區分大小寫使用""引起來
非法語句合法化，如下
create table e1 as select deptno,sum(sal) from emp group by deptno;執行此語句會提示錯誤
修改為create table e1 as select deptno,sum(sal) salcomm from emp group by deptno;
連接符：將列連接起來，最後的結果肯定為字符串 ||
字面字符：number,data,字符型,必須用單引號引起來
select 'name: '|| empno from emp
name； 7900
name: 7800
select ename || 'is work '|| job from emp;
兩個單引號在一起表示一個單引號
select ename || '''is work '|| job from emp;
select ename||q'['s work ]'||job from emp;
此處的符號[]可以替換為任意的符號，但是必須成對出現
10.去除重復行
select distinct deptno from emp; 關鍵字 考試一般選擇此項
select distinct(deptno) from emp; 函數
select unique deptno from emp; 關鍵字
select unique(deptno) from emp; 函數
11.限制行的查詢
select * from emp where condtion(s);where子句必須放在from之後
select * from emp where empno='7900';
字符與日期使用單引號引起來
字符區分大小寫，日期格式敏感
默認日期顯示格式DD-MON—RR
比較操作符
=, >, <, >=, <=, <> ^= !=, in, like, between and, is null,
取反操作
select ename,sal from emp where sal not between 2000 and 3000；
select ename,sal from emp where not sal between 2000 and 3000；兩次運算，效率低下
select ename,sal from emp where sal not in(2000,3000);
select ename,sal from emp where not sal in(2000,3000);運算兩次，
select ename from emp where ename like '__A%';
select ename from emp where ename like 'A%';
select ename from emp where ename like '%S';
select ename from emp where ename like '%/_%' escape '/'; 轉義，轉義的字符可以是任意的字符
select ename from emp where ename like '%/%%' escape '/';
select ename from emp where ename like '%1%%' escape '1';
字符串增減單引號，日期的注意格式
select sysdate from dual;
select ename,hiredate from emp where hiredate='1981-12-03';過濾時間時，確認系統的時間是什麼樣的
c/_/% 替換操作
邏輯運算
and,or,not
and 要求所有條件都為true
or 任意一個條件滿足都為true
操作符優先級規則
7 not 8 and 9 or
排序數據使用order by asc 升序默認 desc 降序
***空值排序永遠都是第一條，(null)***
***排序列用別名排序***
select ename,sal,comm,sal+nvl(comm,0) from emp order by sal+nvl(comm,0);兩次運算，如何一次一運算，修改為如下
select ename,sal,comm,sal+nvl(comm,0) ts from emp order by ts;
***排序用列所在的數字位數***
select ename,sal,comm,sal+nvl(comm,0) from emp order by 4；
select ename,ename||' '||sal ename from emp order by 2;列名相同
多列排序
select ename,sal from emp order by sal desc;
替代變量
臨時變量& 調用替代變量用&&(永久變量)
SQL> select ename,sal from emp where empno=&e;
Enter value for e: 7788
old 1: select ename,sal from emp where empno=&e
new 1: select ename,sal from emp where empno=7788
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select &e,sal from emp where &&e=7788; //需要輸入兩次empno，sql語句執行從右到左，兩次調用
Enter value for e: empno
Enter value for e: empno
old 1: select &e,sal from emp where &&e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
SQL> undefine e
SQL> select &&e,sal from emp where &e=7788;
Enter value for e: empno
old 1: select &&e,sal from emp where &e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
define 定義一個變量
define v1=7788
select ename,sal from emp where empno=&v1;
***set verfify on/off 顯示新舊值設置
undefine 取消一個變量
undefine v1
unset NLS_LANG
11.單行函數
處理數據
單行函數：每行返回一個結果
字符函數：lower,uppper,initcap(注意此函數)
SQL> select initcap('king_scott') from dual;
INITCAP('K
----------
King_Scott
SQL> l
1* select initcap('king_scott') from dual
SQL> c/_/0
1* select initcap('king0scott') from dual
SQL> /
INITCAP('K
----------
King0scott
字符處理類：
concat 連接函數，只能連接兩個表達式，結果為字符型
SQL> select concat(ename,'is work'),job from emp where deptno=10;
CONCAT(ENAME,'ISW JOB
----------------- ---------
CLARKis work MANAGER
KINGis work PRESIDENT
MILLERis work CLERK
SQL> select concat(concat(ename,'is work'),job)from emp where deptno=10; 函數嵌套
CONCAT(CONCAT(ENAME,'ISWOR
--------------------------
CLARKis workMANAGER
KINGis workPRESIDENT
MILLERis workCLERK
substr(expr,m,n) m從哪開始，n取多少個，n>0
SQL> select ename from emp where substr(ename,2,1)='A';
ENAME
----------
WARD
MARTIN
JAMES
SQL> select ename from emp where substr(ename,-2,1)='E';
ENAME
----------
ALLEN
JONES
TURNER
JAMES
MILLER
避免這種寫法select ename from emp where substr(ename,1)='KING';
length 長度函數
SQL> select length(ename),ename from emp;
LENGTH(ENAME) ENAME
------------- ----------
5 SMITH
5 ALLEN
4 WARD
5 JONES
6 MARTIN
5 BLAKE
5 CLARK
5 SCOTT
4 KING
6 TURNER
5 ADAMS
lengthb,lengthc,length2,length4區別詳見官網，中文能看見區別
SQL> select length(ename),lengthb(ename),lengthc(ename) from emp where deptno=10;
LENGTH(ENAME) LENGTHB(ENAME) LENGTHC(ENAME)
------------- -------------- --------------
5 5 5
4 4 4
6 6 6
instr查找指定字符在哪裡出現
SQL> select instr(ename,'A'), ename from emp;
INSTR(ENAME,'A') ENAME
---------------- ----------
0 SMITH
1 ALLEN
2 WARD
0 JONES
2 MARTIN
3 BLAKE
3 CLARK
0 SCOTT
0 KING
0 TURNER
1 ADAMS
SQL> select ename from emp where instr(ename,'A')=1;
ENAME
----------
ALLEN
ADAMS
SQL> select ename,instr(ename,'LL') from emp;
ENAME INSTR(ENAME,'LL')
---------- -----------------
SMITH 0
ALLEN 2
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
rpad右邊填充字符
SQL> select rpad(deptno,10,' ') deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
lpad左邊填充
col dname just right 行標題對齊方式
trim(both|leading|trailing 'char' from expr)
SQL> select trim('A' from 'AAABBBAAA') t from dual;
T
---
BBB
ltrim(expr,'char'),rtirm 版本大於等於10g才會有的
replace(expr,old,new);替換字符
chr(10)換行符 官方文檔sql第五章
數字函數
round() 近似值
trunc() 截取
mod() 取余
abs() 絕對值
ceil()取整
4 5 6 . 7 8 9
-3 -2 -1 0 1 2 3
SQL> select round(456.789,2),trunc(456.789,2)from dual;
ROUND(456.789,2) TRUNC(456.789,2)
---------------- ----------------
456.79 456.78
SQL> c/2/-1
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> l
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> /
ROUND(456.789,-1) TRUNC(456.789,2)
----------------- ----------------
460 456.78
日期函數
default value DD-MON-RR
****0-49,50-99 加減一
current last 2 number ---<50
last 2 number ---0-49<50
50-99 -1 世紀
--->=50
>=50
<50 0-49 +1
SQL> select sysdate from dual;
SYSDATE
---------
19-JUL-14
2014 14
2014 14 2014
**SQL> show parameter nls_date_format;
**SQL> alter session set nls_date_format='yyyy-mm-dd'
日期可以進行算數運算，也可以進行round,trunc運算，trunc(sysdate,'day')
months_between
SQL> select ename,months_between(sysdate,hiredate) s from emp;
ENAME S
---------- ----------
SMITH 403.07913
ALLEN 400.982356
WARD 400.91784
JONES 399.563001
MARTIN 393.724291
BLAKE 398.595259
CLARK 397.337195
SCOTT 327
KING 392.07913
TURNER 394.369453
ADAMS 325.885582
add_months()
next_day()
last_day()
***注意日期的格式***
轉換函數
=顯式轉化
to_char(date,'format_model')
SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> l
1* select to_char(hiredate,'yyyy-mm-dd')from emp
SQL> c/yyyy/fmyyyy
1* select to_char(hiredate,'fmyyyy-mm-dd')from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1987-4-19
1981-11-17
1981-9-8
1987-5-23
SQL> c/dd/fmdd
1* select to_char(hiredate,'fmyyyy-mm-fmdd')from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-02
1981-9-28
1981-5-01
1981-6-09
1987-4-19
1981-11-17
1981-9-08
1987-5-23
9 0 $ L . ,特殊符號
SQL> select ename,to_char(sal,'9,999.00') sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
SQL> select ename,to_char(sal,'9G999D00') sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SQL> select ename,to_char(sal,'$9G999D00') sal from emp;
ENAME SAL
---------- ----------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
SQL> select to_char(16,'x') from dual;
TO
--
##
to_number(char[,'format_model']);
SQL> select to_number('f','xxx') from dual;
TO_NUMBER('F','XXX')
--------------------
15
================================================
注意以下兩個區別，列的對齊方式
SQL> select ename,sal from emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> c/sal/to_char(sal) sal/
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> l
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> /
ENAME SAL
---------- ----------------------------------------
CLARK 2450
KING 5000
MILLER 1300
==================================================================
to_date(char[,'format_model']);
SQL> select to_date('19811111','yyyy/mm/dd') from dual;
TO_DATE('
---------
11-NOV-81
空值函數
nvl(expr1,expr2)
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
nvl2(expr1,expr2,expr3)
SQL> select nvl2(2,1,0) from dual;
NVL2(2,1,0)
-----------
1
SQL> c/2/null
1* select nvl2(null1,0) from dual
SQL> /
SQL> select nvl2(null,1,0) from dual;
NVL2(NULL,1,0)
--------------
0
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
不停地查找，返回不為空的值
=隱式轉化 oracle服務器自動完成
字符串轉number
字符串轉日期
條件表達式(分支函數)
case expr when ... then ...
else ...
end
SQL> select ename,deptno,sal,case deptno when 10 then sal*1.1
2 when 20 then sal*1.3
3 else sal
4 end isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 1040
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3867.5
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SQL> l
1 select ename,deptno,sal, case when deptno=10 then sal*1.1
2 when deptno=20 then sal*1.2
3 else sal
4* end isal from emp
SQL> /
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
decode 可讀性沒有case好
SQL> select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,sal) isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
SQL> select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy');
TO_C COUNT(*)
---- ----------
1987 2
1980 1
1982 1
1981 10
行轉列
SQL> select sum(decode(to_char(hiredate,'yyyy'),'1980',1)) "1980",sum(decode(to_char(hiredate,'yyyy'),'1981',1)) "1981" from emp;
1980 1981
---------- ----------
1 10
多行函數：每個行組集返回一個結果
分組函數 group by having限制分組個數
統計函數不統計空值
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select count(nvl(comm,0)) from emp; //效率低下
COUNT(NVL(COMM,0))
------------------
14
avg(),min(),max(),sum()
SQL> select job,min(sal) from emp group by job;
JOB MIN(SAL)
--------- ----------
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
SQL> select job,min(sal) from emp group by job having min(sal)>2000;
JOB MIN(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
多表查詢語句(實踐必須掌握優化技巧)
等值連接與不等值連接
A.外連接
左外連接
右外連接
B.自然連接 要求列名相同、數據類型相同
SQL> select ename,dname from emp natural join dept;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
列名不相同用以下語句
SQL> select ename,dname from emp join dept on emp.deptno=dept.id;
數據類型不同，做自然連接會報錯，可以修改為以下
select ename,dname from emp join dept using(deptno);
join on ,join using,natural join n-1個連接條件
full join
cross join 交叉連接
子查詢
在主查詢執行之前，子查詢會執行一次，子查詢結果被用於主查詢
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
SQL> select ename from emp where sal in (select sal from emp where sal>2999);
ENAME
----------
FORD
SCOTT
KING
單行子查詢
多行子查詢 子查詢結果為空，可能導致查詢結果為空需要注意
any all in
SQL> select ename,sal from emp where sal > any(2500,3000);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
SQL> c/any/all
1* select ename,sal from emp where sal > all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
KING 5000
SQL> c/>/<
1* select ename,sal from emp where sal < all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
9 rows selected.
SQL> c/all/any
1* select ename,sal from emp where sal < any(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
11 rows selected.
****some與any 等價****
exists
SQL> select ename from emp where empno in (select mgr from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能低下，6次循環
SQL> select ename from emp e where exists (select 1 from emp where mgr=e.empno);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能高，一次全表掃描，此處的1為標簽，
SQL> c/where/where not
1* select ename from emp e where not exists (select 1 from emp where mgr=e.empno)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
set操作
交並補
union 去除重復行,排序,升序,union all 不去除重復行,不排序
intersect 交集,沒有重復
minus 補集A-B
表達式在數量上匹配，數據類型前後必須一致
括號可變優先級，order by 語句可以出現在語句最後
SQL> select deptno,null job,max(sal) from emp group by deptno
2 union all
3 select null ,job,max(sal) from emp group by job;
DEPTNO JOB MAX(SAL)
---------- --------- ----------
30 2850
20 3000
10 5000
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
以上是構造偽列
以下是10g以後才會支持的
SQL> c/setss/sets
1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)
SQL> /
DEPTNO JOB MAX(SAL)
---------- --------- ----------
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
30 2850
20 3000
10 5000
