oracle中值集限定词为灰色,《Oracle从入门到精通》读书笔记第四章 SQL语言基础之一...
4.1?SQL語言概述
4.1.1?SQL語言的特點(diǎn)
1、集合性
2、統(tǒng)一性
3、易于移植性
4.1.2?SQL語言的分類
1、數(shù)據(jù)查詢語言(DQL)-?select語句，查詢數(shù)據(jù)
2、數(shù)據(jù)操縱語言(DML)-?insert、update和delete語句，改變數(shù)據(jù)
3、事務(wù)控制語言(TCL)-?commit、rollback和savepoint語句，維護(hù)數(shù)據(jù)一致性
4、數(shù)據(jù)定義語言(DDL)-?create、alter和drop語句，改變數(shù)據(jù)庫對象
5、數(shù)據(jù)控制語言(DCL)-?grant和revoke語句，權(quán)限授予和回收
4.1.3?SQL語言的編寫規(guī)則
1、關(guān)鍵字不區(qū)分大小寫
2、字符值區(qū)分大小寫
3、以分號作為結(jié)束符
4、2?用戶模式
4.2.1?模式與模式對象
模式是一個(gè)數(shù)據(jù)庫對象的集合
模式為一個(gè)數(shù)據(jù)庫用戶所有，并且具有與該用戶相同的名稱。
模式對象是由用戶創(chuàng)建的邏輯結(jié)構(gòu)，用以存儲(chǔ)或引用數(shù)據(jù)。比如段，約束，視圖，同義詞，過程，程序包等。
不屬于某個(gè)用戶所擁有的數(shù)據(jù)庫對象就不能稱之為模式對象，比如角色，表空間，目錄等。
4.2.2?實(shí)例模式SCOTT
SQL>?conn?scott/tiger
Connected.
SQL>?select?table_name?from?user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SQL>?select?table_name?from?dba_tables?where?owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
4.3?檢索數(shù)據(jù)
語法格式：
select?{[distinct|all]column|*}
[into?table_name]
from?{tables|views|other?select}
[where?conditions]
[group?by?columns]
[having?conditions]
[order?by?columns]
4.3.1?簡單查詢
1、檢索所有的(*)
SQL>?select?*?from?scott.dept;
DEPTNO?DNAME??????????LOC
----------?--------------?-------------
10?ACCOUNTING?????NEW?YORK
20?RESEARCH???????DALLAS
30?SALES??????????CHICAGO
40?OPERATIONS?????BOSTON
SQL>?select?*?from?scott.dept,scott.salgrade;
DEPTNO?DNAME??????????LOC????????????????GRADE??????LOSAL??????HISAL
----------?--------------?-------------?----------?----------?----------
10?ACCOUNTING?????NEW?YORK???????????????1????????700???????1200
10?ACCOUNTING?????NEW?YORK???????????????2???????1201???????1400
10?ACCOUNTING?????NEW?YORK???????????????3???????1401???????2000
10?ACCOUNTING?????NEW?YORK???????????????4???????2001???????3000
10?ACCOUNTING?????NEW?YORK???????????????5???????3001???????9999
20?RESEARCH???????DALLAS?????????????????1????????700???????1200
20?RESEARCH???????DALLAS?????????????????2???????1201???????1400
20?RESEARCH???????DALLAS?????????????????3???????1401???????2000
20?RESEARCH???????DALLAS?????????????????4???????2001???????3000
20?RESEARCH???????DALLAS?????????????????5???????3001???????9999
30?SALES??????????CHICAGO????????????????1????????700???????1200
DEPTNO?DNAME??????????LOC????????????????GRADE??????LOSAL??????HISAL
----------?--------------?-------------?----------?----------?----------
30?SALES??????????CHICAGO????????????????2???????1201???????1400
30?SALES??????????CHICAGO????????????????3???????1401???????2000
30?SALES??????????CHICAGO????????????????4???????2001???????3000
30?SALES??????????CHICAGO????????????????5???????3001???????9999
40?OPERATIONS?????BOSTON?????????????????1????????700???????1200
40?OPERATIONS?????BOSTON?????????????????2???????1201???????1400
40?OPERATIONS?????BOSTON?????????????????3???????1401???????2000
40?OPERATIONS?????BOSTON?????????????????4???????2001???????3000
40?OPERATIONS?????BOSTON?????????????????5???????3001???????9999
20?rows?selected.
2、檢索指定的列
SQL>?select?job,ename,empno?from?emp;
JOB???????ENAME???????????EMPNO
---------?----------?----------
CLERK?????SMITH????????????7369
SALESMAN??ALLEN????????????7499
SALESMAN??WARD?????????????7521
MANAGER???JONES????????????7566
SALESMAN??MARTIN???????????7654
MANAGER???BLAKE????????????7698
MANAGER???CLARK????????????7782
ANALYST???SCOTT????????????7788
PRESIDENT?KING?????????????7839
SALESMAN??TURNER???????????7844
CLERK?????ADAMS????????????7876
JOB???????ENAME???????????EMPNO
---------?----------?----------
CLERK?????JAMES????????????7900
ANALYST???FORD?????????????7902
CLERK?????MILLER???????????7934
14?rows?selected.
SQL>?select?rowid,job,ename?from?emp;
ROWID??????????????JOB???????ENAME
------------------?---------?----------
AAAR3xAAEAAAACXAAA?CLERK?????SMITH
AAAR3xAAEAAAACXAAB?SALESMAN??ALLEN
AAAR3xAAEAAAACXAAC?SALESMAN??WARD
AAAR3xAAEAAAACXAAD?MANAGER???JONES
AAAR3xAAEAAAACXAAE?SALESMAN??MARTIN
AAAR3xAAEAAAACXAAF?MANAGER???BLAKE
AAAR3xAAEAAAACXAAG?MANAGER???CLARK
AAAR3xAAEAAAACXAAH?ANALYST???SCOTT
AAAR3xAAEAAAACXAAI?PRESIDENT?KING
AAAR3xAAEAAAACXAAJ?SALESMAN??TURNER
AAAR3xAAEAAAACXAAK?CLERK?????ADAMS
ROWID??????????????JOB???????ENAME
------------------?---------?----------
AAAR3xAAEAAAACXAAL?CLERK?????JAMES
AAAR3xAAEAAAACXAAM?ANALYST???FORD
AAAR3xAAEAAAACXAAN?CLERK?????MILLER
14?rows?selected.
3、帶有表達(dá)式的select子句
SQL>?select?sal*(1+0.1),sal?from?emp;
SAL*(1+0.1)????????SAL
-----------?----------
880????????800
1760???????1600
1375???????1250
3272.5???????2975
1375???????1250
3135???????2850
2695???????2450
3300???????3000
5500???????5000
1650???????1500
1210???????1100
SAL*(1+0.1)????????SAL
-----------?----------
1045????????950
3300???????3000
1430???????1300
14?rows?selected.
4、為列指定別名
可以使用as關(guān)鍵字，也可以直接指定
SQL>?select?empno?as?"員工編號"?from?emp;
員工編號
------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
員工編號
------------
7900
7902
7934
14?rows?selected.
SQL>?select?empno?"員工編號"?from?emp;
員工編號
------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
員工編號
------------
7900
7902
7934
14?rows?selected.
5、顯示不重復(fù)記錄
使用distinct關(guān)鍵字去除重復(fù)記錄
SQL>?select?distinct?job?from?emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
4.3.2?篩選查詢
語法格式：
select?columns_list
from?table_name
where?conditional_expression
1、比較篩選
基本比較篩選有以下6種情況:A=B
A!B或A<>B
A>B
A>=B
A
A<=B
SQL>?select?empno,ename,sal?from?emp?where?sal>1500;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7499?ALLEN????????????1600
7566?JONES????????????2975
7698?BLAKE????????????2850
7782?CLARK????????????2450
7788?SCOTT????????????3000
7839?KING?????????????5000
7902?FORD?????????????3000
7?rows?selected.
特殊比較篩選有以下兩種情況：
A{operator}ANY(B):A與B中任何一個(gè)元素進(jìn)行operator運(yùn)算符的比較，只要有一個(gè)比較值為true，就返回?cái)?shù)據(jù)行
A{operator}ALL(B):A與B中所有元素進(jìn)行operator運(yùn)算符的比較，只有所有元素比較值為true，才返回?cái)?shù)據(jù)行
SQL>?select?empno,ename,sal?from?emp?where?sal<>all(3000,950,800);
EMPNO?ENAME?????????????SAL
----------?----------?----------
7499?ALLEN????????????1600
7521?WARD?????????????1250
7566?JONES????????????2975
7654?MARTIN???????????1250
7698?BLAKE????????????2850
7782?CLARK????????????2450
7839?KING?????????????5000
7844?TURNER???????????1500
7876?ADAMS????????????1100
7934?MILLER???????????1300
10?rows?selected.
2、使用特殊關(guān)鍵字篩選
(1)like關(guān)鍵字：字符串匹配
常用通配符：
_：代表任意一個(gè)字符
%：代表任意數(shù)量字符
SQL>?select?empno,ename,job?from?emp?where?ename?like?'S%';
EMPNO?ENAME??????JOB
----------?----------?---------
7369?SMITH??????CLERK
7788?SCOTT??????ANALYST
SQL>?select?empno,ename,job?from?emp?where?ename?not?like?'S%';
EMPNO?ENAME??????JOB
----------?----------?---------
7499?ALLEN??????SALESMAN
7521?WARD???????SALESMAN
7566?JONES??????MANAGER
7654?MARTIN?????SALESMAN
7698?BLAKE??????MANAGER
7782?CLARK??????MANAGER
7839?KING???????PRESIDENT
7844?TURNER?????SALESMAN
7876?ADAMS??????CLERK
7900?JAMES??????CLERK
7902?FORD???????ANALYST
EMPNO?ENAME??????JOB
----------?----------?---------
7934?MILLER?????CLERK
12?rows?selected.
(2)in關(guān)鍵字：測試一個(gè)數(shù)據(jù)值是否匹配一組目標(biāo)值中的一個(gè)
SQL>?select?empno,ename,job?from?emp?where?job?in('PRESIDENT','MANAGER','ANALYST');
EMPNO?ENAME??????JOB
----------?----------?---------
7566?JONES??????MANAGER
7698?BLAKE??????MANAGER
7782?CLARK??????MANAGER
7788?SCOTT??????ANALYST
7839?KING???????PRESIDENT
7902?FORD???????ANALYST
6?rows?selected.
SQL>?select?empno,ename,job?from?emp?where?job?not?in('PRESIDENT','MANAGER','ANALYST');
EMPNO?ENAME??????JOB
----------?----------?---------
7369?SMITH??????CLERK
7499?ALLEN??????SALESMAN
7521?WARD???????SALESMAN
7654?MARTIN?????SALESMAN
7844?TURNER?????SALESMAN
7876?ADAMS??????CLERK
7900?JAMES??????CLERK
7934?MILLER?????CLERK
8?rows?selected.
(3)between關(guān)鍵字：返回某一個(gè)數(shù)據(jù)值是否位于兩個(gè)給的的值之間
SQL>?select?empno,ename,sal?from?emp?where?sal?between?2000?and?3000;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7566?JONES????????????2975
7698?BLAKE????????????2850
7782?CLARK????????????2450
7788?SCOTT????????????3000
7902?FORD?????????????3000
SQL>?select?empno,ename?sal?from?emp?where?sal?not?between?2000?and?3000;
EMPNO?SAL
----------?----------
7369?SMITH
7499?ALLEN
7521?WARD
7654?MARTIN
7839?KING
7844?TURNER
7876?ADAMS
7900?JAMES
7934?MILLER
9?rows?selected.
(4)is?null?關(guān)鍵字：檢測是否為空
SQL>?select?empno,ename,comm?from?emp?where?comm?is?null;
EMPNO?ENAME????????????COMM
----------?----------?----------
7369?SMITH
7566?JONES
7698?BLAKE
7782?CLARK
7788?SCOTT
7839?KING
7876?ADAMS
7900?JAMES
7902?FORD
7934?MILLER
10?rows?selected.
3、邏輯篩選：AND,OR,NOT
SQL>?select?empno,ename,sal?from?emp?where?sal>=2000?and?sal<=3000;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7566?JONES????????????2975
7698?BLAKE????????????2850
7782?CLARK????????????2450
7788?SCOTT????????????3000
7902?FORD?????????????3000
SQL>?select?empno,ename,sal?from?emp?where?sal<2000?or?sal>3000;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7369?SMITH?????????????800
7499?ALLEN????????????1600
7521?WARD?????????????1250
7654?MARTIN???????????1250
7839?KING?????????????5000
7844?TURNER???????????1500
7876?ADAMS????????????1100
7900?JAMES?????????????950
7934?MILLER???????????1300
9?rows?selected.
4.3.3?分組查詢
語法格式：
select?columns_list
from?table_name
[where?conditional_expression]
group?by?columns_list
SQL>?select?deptno,job?from?emp?group?by?deptno,job?order?by?deptno;
DEPTNO?JOB
----------?---------
10?CLERK
10?MANAGER
10?PRESIDENT
20?ANALYST
20?CLERK
20?MANAGER
30?CLERK
30?MANAGER
30?SALESMAN
9?rows?selected.
SQL>?select?deptno?as?部門編號,avg(sal)?as?平均工資?from?emp?group?by?deptno;
部門編號?平均工資
------------?------------
30???1566.66667
20?????????2175
10???2916.66667
group?by列表需要包括非聚集表達(dá)式內(nèi)的所有列或者與選擇列表表達(dá)式完全匹配
SQL>?select?deptno,avg(sal)?from?emp?group?by?deptno?having?avg(sal)<2000;
DEPTNO???AVG(SAL)
----------?----------
30?1566.66667
4.3.4?排序查詢
語法格式：
select?columns_list
from?table_name
[where?conditional_expression]
[group?by?columns_list]
order?by?{order_by_expression[asc|desc]}[,...n]
SQL>?select?deptno,empno,ename?from?emp?order?by?deptno,empno;
DEPTNO??????EMPNO?ENAME
----------?----------?----------
10???????7782?CLARK
10???????7839?KING
10???????7934?MILLER
20???????7369?SMITH
20???????7566?JONES
20???????7788?SCOTT
20???????7876?ADAMS
20???????7902?FORD
30???????7499?ALLEN
30???????7521?WARD
30???????7654?MARTIN
DEPTNO??????EMPNO?ENAME
----------?----------?----------
30???????7698?BLAKE
30???????7844?TURNER
30???????7900?JAMES
14?rows?selected.
4.3.5?多表關(guān)聯(lián)查詢
1、表別名
SQL>?select?e.empno?as??員工編號,e.ename?as?員工名稱,d.dname?as?部門
2??from?emp?e,dept?d
3??where?e.deptno=d.deptno
4??and?e.job='MANAGER';
員工編號?員工名?部門
------------?----------?--------------
7782?CLARK??????ACCOUNTING
7566?JONES??????RESEARCH
7698?BLAKE??????SALES
2、內(nèi)連接
語法格式：
select?columns_list
from?table_name1?[inner]?join?table_name2
on?join_condition;
SQL>?select?e.empno,e.ename,d.dname
2??from?emp?e?inner?join?dept?d
3??on?e.deptno=d.deptno;
EMPNO?ENAME??????DNAME
----------?----------?--------------
7782?CLARK??????ACCOUNTING
7839?KING???????ACCOUNTING
7934?MILLER?????ACCOUNTING
7566?JONES??????RESEARCH
7902?FORD???????RESEARCH
7876?ADAMS??????RESEARCH
7369?SMITH??????RESEARCH
7788?SCOTT??????RESEARCH
7521?WARD???????SALES
7844?TURNER?????SALES
7499?ALLEN??????SALES
EMPNO?ENAME??????DNAME
----------?----------?--------------
7900?JAMES??????SALES
7698?BLAKE??????SALES
7654?MARTIN?????SALES
14?rows?selected.
3、外連接
分類：
左外連接：關(guān)鍵字為left?outer?join或left?join，會(huì)包含左表中不滿足連接條件的數(shù)據(jù)行
右外連接：關(guān)鍵字為right?outer?join或right?join，會(huì)包含右表中不滿足連接條件的數(shù)據(jù)行
完全外連接：關(guān)鍵字為full?outer?join或full?join
SQL>?insert?into?emp(empno,ename,job)?values(9527,'EAST','SALESMAN');
1?row?created.
SQL>?select?e.empno,e.ename,e.job,d.deptno,d.dname
2??from?emp?e?left?join?dept?d
3??on?e.deptno=d.deptno
4??;
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7934?MILLER?????CLERK?????????????10?ACCOUNTING
7839?KING???????PRESIDENT?????????10?ACCOUNTING
7782?CLARK??????MANAGER???????????10?ACCOUNTING
7902?FORD???????ANALYST???????????20?RESEARCH
7876?ADAMS??????CLERK?????????????20?RESEARCH
7788?SCOTT??????ANALYST???????????20?RESEARCH
7566?JONES??????MANAGER???????????20?RESEARCH
7369?SMITH??????CLERK?????????????20?RESEARCH
7900?JAMES??????CLERK?????????????30?SALES
7844?TURNER?????SALESMAN??????????30?SALES
7698?BLAKE??????MANAGER???????????30?SALES
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7654?MARTIN?????SALESMAN??????????30?SALES
7521?WARD???????SALESMAN??????????30?SALES
7499?ALLEN??????SALESMAN??????????30?SALES
9527?EAST???????SALESMAN
15?rows?selected.
SQL>?select?e.empno,e.ename,e.job,d.deptno,d.dname
2??from?emp?e?right?join?dept?d
3??on?e.deptno=d.deptno;
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7782?CLARK??????MANAGER???????????10?ACCOUNTING
7934?MILLER?????CLERK?????????????10?ACCOUNTING
7839?KING???????PRESIDENT?????????10?ACCOUNTING
7566?JONES??????MANAGER???????????20?RESEARCH
7369?SMITH??????CLERK?????????????20?RESEARCH
7788?SCOTT??????ANALYST???????????20?RESEARCH
7902?FORD???????ANALYST???????????20?RESEARCH
7876?ADAMS??????CLERK?????????????20?RESEARCH
7521?WARD???????SALESMAN??????????30?SALES
7844?TURNER?????SALESMAN??????????30?SALES
7499?ALLEN??????SALESMAN??????????30?SALES
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7900?JAMES??????CLERK?????????????30?SALES
7654?MARTIN?????SALESMAN??????????30?SALES
7698?BLAKE??????MANAGER???????????30?SALES
40?OPERATIONS
15?rows?selected.
SQL>?select?e.empno,e.ename,e.job,d.deptno,d.dname
2??from?emp?e?full?join?dept?d
3??on?e.deptno=d.deptno;
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7369?SMITH??????CLERK?????????????20?RESEARCH
7499?ALLEN??????SALESMAN??????????30?SALES
7521?WARD???????SALESMAN??????????30?SALES
7566?JONES??????MANAGER???????????20?RESEARCH
7654?MARTIN?????SALESMAN??????????30?SALES
7698?BLAKE??????MANAGER???????????30?SALES
7782?CLARK??????MANAGER???????????10?ACCOUNTING
7788?SCOTT??????ANALYST???????????20?RESEARCH
7839?KING???????PRESIDENT?????????10?ACCOUNTING
7844?TURNER?????SALESMAN??????????30?SALES
7876?ADAMS??????CLERK?????????????20?RESEARCH
EMPNO?ENAME??????JOB???????????DEPTNO?DNAME
----------?----------?---------?----------?--------------
7900?JAMES??????CLERK?????????????30?SALES
7902?FORD???????ANALYST???????????20?RESEARCH
7934?MILLER?????CLERK?????????????10?ACCOUNTING
9527?EAST???????SALESMAN
40?OPERATIONS
16?rows?selected.
4、自然連接
使用natural?join關(guān)鍵字，很少使用，不能為列指定限定詞(即表名或者表的別名)
SQL>?select?empno,ename,job,dname
2??from?emp?natural?join?dept
3??where?sal?>?2000;
EMPNO?ENAME??????JOB???????DNAME
----------?----------?---------?--------------
7782?CLARK??????MANAGER???ACCOUNTING
7839?KING???????PRESIDENT?ACCOUNTING
7902?FORD???????ANALYST???RESEARCH
7788?SCOTT??????ANALYST???RESEARCH
7566?JONES??????MANAGER???RESEARCH
7698?BLAKE??????MANAGER???SALES
6?rows?selected.
5、自連接
SQL>?select?em2.ename?manager,em1.ename?employee
2??from?emp?em1?left?join?emp?em2
3??on?em1.mgr=em2.empno
4??order?by?em1.mgr;
MANAGER????EMPLOYEE
----------?----------
JONES??????FORD
JONES??????SCOTT
BLAKE??????JAMES
BLAKE??????TURNER
BLAKE??????MARTIN
BLAKE??????WARD
BLAKE??????ALLEN
CLARK??????MILLER
SCOTT??????ADAMS
KING???????CLARK
KING???????BLAKE
MANAGER????EMPLOYEE
----------?----------
KING???????JONES
FORD???????SMITH
EAST
KING
15?rows?selected.
6、交叉連接
不需要任何連接條件的連接，關(guān)鍵字cross?join,執(zhí)行結(jié)果是一個(gè)笛卡爾積。
語法格式：
select?columns_list
from?table_name1?cross?join?table_name2
SQL>?select?count(*)
2??from?dept?cross?join?emp;
COUNT(*)
----------
60
4.4?Oracle常用系統(tǒng)函數(shù)
4.4.1?字符類函數(shù)
1、ascii(c)函數(shù)和chr(i)函數(shù)
字符與ascii碼的轉(zhuǎn)換
SQL>?select?ascii('Z')Z,ascii('H')H,ascii('D')D,ascii('?')space?from?dual;
Z??????????H??????????D??????SPACE
----------?----------?----------?----------
90?????????72?????????68?????????32
SQL>?select?chr(90),chr(72),chr(68),chr(32)S?from?dual;
C?C?C?S
-?-?-?-
Z?H?D
2、concat(s1,s2)函數(shù)
將字符串s2連接到字符串s1后面
SQL>?select?concat('hello?','world')?information?from?dual;
INFORMATION
-----------
hello?world
3、initcap(s)函數(shù)
將字符數(shù)s的每個(gè)單詞的第一個(gè)字母大寫，其他字母小寫。
SQL>?select?initcap('oh?my?god!')?information?from?dual;
INFORMATIO
----------
Oh?My?God!
4、instr(s1,s2[,i][,j])函數(shù)
返回字符s2在字符串s1中第j次出現(xiàn)時(shí)的位置，搜索從字符串s1的第i個(gè)字符開始，當(dāng)沒有發(fā)現(xiàn)要查找的字符時(shí)，返回0。其中s1和s2均為字符串，i和j均為整數(shù)，默認(rèn)為1。
SQL>?select?instr('oracle?11g','1',3,2)?abc?from?dual;
ABC
----------
9
5、length(s)函數(shù)
返回字符串s的長度
SQL>?select?e.empno,e.ename,d.dname
2??from?emp?e?inner?join?dept?d
3??on?e.deptno=d.deptno
4??where?length(e.ename)>5;
EMPNO?ENAME??????DNAME
----------?----------?--------------
7654?MARTIN?????SALES
7844?TURNER?????SALES
7934?MILLER?????ACCOUNTING
6、lower(s)函數(shù)和upper(s)函數(shù)
分別返回字符串s的小寫形式和大寫形式，這兩個(gè)函數(shù)經(jīng)常出現(xiàn)在where子句中。
SQL>?select?empno,ename,job?from?emp?where?lower(job)?like?'s%';
EMPNO?ENAME??????JOB
----------?----------?---------
7499?ALLEN??????SALESMAN
7521?WARD???????SALESMAN
7654?MARTIN?????SALESMAN
7844?TURNER?????SALESMAN
9527?EAST???????SALESMAN
7、ltrim(s1,s2)函數(shù)，rtrim(s1,s2)函數(shù)和trim(s1,s2)函數(shù)
分別用來刪除字符串s1左邊的字符串s2，刪除字符串s1右邊的字符串s2，刪除字符串s1左右兩端字符串s2，如果不指定字符串s2，則刪除相應(yīng)方位的空格。
SQL>?select?ltrim('###east###','#')?ltrim?from?dual;
LTRIM
-------
east###
SQL>?select?rtrim('###east###','#')?rtrim?from?dual;
RTRIM
-------
###east
SQL>?select?trim('#'?from?'###east###')?from?dual;
TRIM
----
east
8、replace(s1,s2[,s3])函數(shù)
使用s3字符串替換出現(xiàn)在s1字符串中的所有s2字符串，并返回替換后的新字符串，其中，s3字符串默認(rèn)為空字符串。
SQL>?select?replace('bad?luck?bad?girl','bad','good')?from?dual;
REPLACE('BADLUCKBAD
-------------------
good?luck?good?girl
9、substr(s,i,[j])函數(shù)
從字符串s的第i個(gè)位置開始截取長度為j的子字符串。如果省略參數(shù)j，則直接截取到尾部。
SQL>?select?substr('messagebox',8,3)?from?dual;
SUB
---
box
4.4.2?數(shù)字類函數(shù)
1、ceil(n)函數(shù)
大于或等于數(shù)值n的最小整數(shù)，天花板函數(shù)
SQL>?select?ceil(7.3),ceil(7),ceil(-7.3)?from?dual;
CEIL(7.3)????CEIL(7)?CEIL(-7.3)
----------?----------?----------
8??????????7?????????-7
2、round(n1,n2)函數(shù)
返回舍入小數(shù)點(diǎn)右邊n2位的n1的值，n2默認(rèn)為0
SQL>?select?round(3.1415926,2)?from?dual;
ROUND(3.1415926,2)
------------------
3.14
3、power(n1,n2)函數(shù)
返回n1的n2次方
SQL>?select?power(2,3)?from?dual;
POWER(2,3)
----------
8
4.4.3?日期和時(shí)間類函數(shù)
1、sysdate()函數(shù)
返回系統(tǒng)當(dāng)前的日期
SQL>?select?sysdate?from?dual;
SYSDATE
------------
01-DEC-13
2、add_months(d,i)函數(shù)
返回日期d加上i個(gè)月之后的結(jié)果
SQL>?select?add_months(sysdate,6)?from?dual;
ADD_MONTHS(S
------------
01-JUN-14
4.4.4?轉(zhuǎn)換類函數(shù)
1、to_char(x[,format])函數(shù)
將表達(dá)式轉(zhuǎn)換成字符串，format表示字符串格式
SQL>?select?sysdate,to_char(sysdate,'YYYY-MM-DD')?from?dual;
SYSDATE??????TO_CHAR(SY
------------?----------
01-DEC-13????2013-12-01
2、to_number(s[,format[lan]])函數(shù)
返回字符串s代表的數(shù)字
SQL>?select?to_number('18','xxx')?from?dual;
TO_NUMBER('18','XXX')
---------------------
24
4.4.5?集合類函數(shù)
SQL>?select?count(empno)?as?sum,round(avg(sal),2)?as?avg?from?emp;
SUM????????AVG
----------?----------
14????2073.21
4.5?子查詢的用法
4.5.1?子查詢
SQL>?select?empno,ename,job?from?emp
2??where?deptno=(select?deptno?from?dept?where?dname='RESEARCH');
EMPNO?ENAME??????JOB
----------?----------?---------
7369?SMITH??????CLERK
7566?JONES??????MANAGER
7788?SCOTT??????ANALYST
7876?ADAMS??????CLERK
7902?FORD???????ANALYST
SQL>?select?empno,ename,job
2??from?emp?join?dept
3??on?emp.deptno=dept.deptno
4??where?dept.dname='RESEARCH';
EMPNO?ENAME??????JOB
----------?----------?---------
7566?JONES??????MANAGER
7902?FORD???????ANALYST
7876?ADAMS??????CLERK
7369?SMITH??????CLERK
7788?SCOTT??????ANALYST
子查詢相對對表關(guān)聯(lián)查詢要慢一些。
使用子查詢時(shí)，應(yīng)注意一下規(guī)則：
子查詢必須用括號()括起來
子查詢中不能包括order?by子句
子查詢允許嵌套多層，但不能超過255層
子查詢分為單行子查詢，多行子查詢和關(guān)聯(lián)子查詢
4.5.2?單行子查詢
返回一行數(shù)據(jù)的子查詢語句。當(dāng)在where子句中引用單行子查詢時(shí)，可以使用單行比較運(yùn)算符(=，>,=和<>)。
SQL>?select?empno,ename,sal?from?emp
2??where?sal>(select?min(sal)?from?emp)?and?sal
EMPNO?ENAME?????????????SAL
----------?----------?----------
7499?ALLEN????????????1600
7521?WARD?????????????1250
7566?JONES????????????2975
7654?MARTIN???????????1250
7698?BLAKE????????????2850
7782?CLARK????????????2450
7788?SCOTT????????????3000
7844?TURNER???????????1500
7876?ADAMS????????????1100
7900?JAMES?????????????950
7902?FORD?????????????3000
EMPNO?ENAME?????????????SAL
----------?----------?----------
7934?MILLER???????????1300
12?rows?selected.
4.5.3?多行子查詢
返回多行數(shù)據(jù)的子查詢語句。當(dāng)在where子句中使用多行子查詢時(shí)，必須使用多行運(yùn)算符(in，any，all)
1、使用in運(yùn)算符
使用in運(yùn)算符時(shí)，外查詢會(huì)嘗試與子查詢結(jié)果中的任何一個(gè)結(jié)果進(jìn)行匹配，只要有一個(gè)匹配成功，則外查詢返回當(dāng)前檢索的記錄。
SQL>?select?empno,ename,job?from?emp
2??where?deptno?in?(select?deptno?from?dept?where?dname<>'SALES');
EMPNO?ENAME??????JOB
----------?----------?---------
7782?CLARK??????MANAGER
7839?KING???????PRESIDENT
7934?MILLER?????CLERK
7566?JONES??????MANAGER
7902?FORD???????ANALYST
7876?ADAMS??????CLERK
7369?SMITH??????CLERK
7788?SCOTT??????ANALYST
8?rows?selected.
2、使用any運(yùn)算符
any運(yùn)算符必須與單行比較運(yùn)算符結(jié)合使用，并且返回行只要匹配子查詢的任何一個(gè)結(jié)果即可。
SQL>?select?empno,ename,sal?from?emp
2??where?sal?>?any(select?sal?from?emp?where?deptno=10)?and?deptno<>10;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7788?SCOTT????????????3000
7902?FORD?????????????3000
7566?JONES????????????2975
7698?BLAKE????????????2850
7499?ALLEN????????????1600
7844?TURNER???????????1500
6?rows?selected.
3、使用all運(yùn)算符
all運(yùn)算符必須與單行運(yùn)算符結(jié)合使用，并且返回行必須匹配所有子查詢結(jié)果。
SQL>?select?deptno,ename,sal?from?emp
2??where?sal?>?all(select?sal?from?emp?where?deptno=30);
DEPTNO?ENAME?????????????SAL
----------?----------?----------
20?JONES????????????2975
20?SCOTT????????????3000
20?FORD?????????????3000
10?KING?????????????5000
4.5.4?關(guān)聯(lián)子查詢
內(nèi)查詢和外查詢相互關(guān)聯(lián)
SQL>?select?empno,ename,sal
2??from?emp?f
3??where?sal>(select?avg(sal)?from?emp?where?job=f.job)
4??order?by?job;
EMPNO?ENAME?????????????SAL
----------?----------?----------
7876?ADAMS????????????1100
7934?MILLER???????????1300
7566?JONES????????????2975
7698?BLAKE????????????2850
7499?ALLEN????????????1600
7844?TURNER???????????1500
6?rows?selected.
4.6?操作數(shù)據(jù)庫
4.6.1?插入數(shù)據(jù)(insert語句)
1、單條插入數(shù)據(jù)
語法格式：
insert?into?table_name(column_name1[,column_name2]...)]
value(express1[,express2]...)
SQL>?insert?into?dept(deptno,dname,loc)
2??values(88,'design','beijing');
1?row?created.
SQL>?insert?into?jobs
2??values('PRO','程序員',5000,10000);
1?row?created.
2、批量插入數(shù)據(jù)
語法格式：
insert?into?table_name?[(column_name1[,column_name2]...)]?select?subquery
SQL>?create?table?jobs_temp(
2??job_id?varchar2(10)?primary?key,
3??job_title?varchar2(35)?not?null,
4??min_salary?number(6),
5??max_salary?number(6));
Table?created.
SQL>?desc?jobs_temp;
Name??????????????????????????????????????Null?????Type
-----------------------------------------?--------?----------------------------
JOB_ID????????????????????????????????????NOT?NULL?VARCHAR2(10)
JOB_TITLE?????????????????????????????????NOT?NULL?VARCHAR2(35)
MIN_SALARY?????????????????????????????????????????NUMBER(6)
MAX_SALARY?????????????????????????????????????????NUMBER(6)
SQL>?insert?into?jobs_temp
2??select?*?from?jobs
3??where?jobs.max_salary>10000;
9?rows?created.
4.6.2?更新數(shù)據(jù)(update語句)
語法格式：
update?table_name
set?{column_name1=express1[,column_name2=express2...]
|(column_name1[,column_name2...])=(selectsubquery)}
[where?condition]
SQL>?update?emp
2??set?sal=sal*1.2
3??where?job='SALESMAN';
4?rows?updated.
SQL>?update?emp
2??set?sal=(select?avg(sal)?from?emp?where?job='MANAGER')
3??where?sal<2000;
8?rows?updated.
4.6.3?刪除數(shù)據(jù)(delete語句和truncate語句)
1、delete語句
刪除數(shù)據(jù)庫中的所有記錄和指定范圍的記錄
語法格式：
delete?from?table_name
[where?condition]
SQL>?delete?from?jobs
2??where?job_id='PRO';
1?row?deleted.
2、truncate語句
刪除表中的所有記錄，比delete快，不產(chǎn)生回滾記錄，無法使用rollback語句撤銷。
SQL>?truncate?table?jobs_temp;
Table?truncated.
總結(jié)
以上是生活随笔為你收集整理的oracle中值集限定词为灰色,《Oracle从入门到精通》读书笔记第四章 SQL语言基础之一...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
