余楓編寫?2002.10
前言:?關(guān)系數(shù)據(jù)庫的簡(jiǎn)單介紹?
1970.Dr.E.F.codded?創(chuàng)建了關(guān)系數(shù)據(jù)庫的模型（類似現(xiàn)在常用的二維表格）
關(guān)系數(shù)據(jù)庫由許多數(shù)據(jù)對(duì)象組成,?被關(guān)系操作SQL命令管理著,?數(shù)據(jù)之間有完整性的約束條件.
RDBMS (?relational?database?management?system?)
ORDBMS (?object?relational?database?management?system?)
ORACLE?RDBMS常稱為ORACLE?SERVER
它的內(nèi)核是用C語言寫的.
ORACLE的簡(jiǎn)單介紹?
ORACLE公司是一家提供綜合技術(shù)產(chǎn)品、方案、服務(wù)的數(shù)據(jù)庫公司
ORACLE數(shù)據(jù)庫設(shè)計(jì)成可大量存儲(chǔ)數(shù)據(jù)，快速查詢數(shù)據(jù)，保證數(shù)據(jù)的安全和一致性，
跨網(wǎng)絡(luò)的分布式管理及客戶-服務(wù)器的配置等。
ORACLE?SQL語法符合ANSI?1986標(biāo)準(zhǔn)
SQL 和數(shù)據(jù)庫交流的命令式語言
SQL*PLUS ORACLE的一種工具,?用來運(yùn)行SQL和PL/SQL語句
PL/SQL ORACLE的過程化編程語言
一、選擇行
1.?簡(jiǎn)單的SELECT?語句
SELECT?[DISTINCT]?{*,COLUMN?[ALIAS],?……}?FROM?table_name;
字段的非唯一的結(jié)果集
DISTINCT?字段名1?[?,字段名2]?
數(shù)字類型字段名之間可以進(jìn)行算術(shù)運(yùn)算
+??-?*?/??等
例如:?(字段名1*字段名2)/3
合并字段內(nèi)容的連接字符?||
字段名1?||?字段名2
字段可以有別名
SELECT?字段名1?[AS]?'字段名1?解釋'?FROM?table;
2.?處理NULL
NULL?未定義，不可操作，什么都不是
NULL?!=?0 NULL!='空格'
和NULL的任何運(yùn)算都返回NULL
ORACLE里未定義的變量值都為NULL
NVL函數(shù)可把NULL轉(zhuǎn)換成其它類型的符號(hào)
NVL(EXPR1,?EXPR2)函數(shù)
解釋: IF?EXPR1=NULL? RETURN?EXPR2
ELSE? RETURN?EXPR1
編程技巧:?NVL函數(shù)在多條件模糊查詢的時(shí)候比較有用
nvl('+input_value+','0')='0'?or?filed_name?like?'%+input_value+%'
NVL函數(shù)可返回多種數(shù)據(jù)類型:
返回日期 NVL(start_date,'2002-02-01')
返回字符串 NVL(title,'no?title')
返回?cái)?shù)字 NVL(salary,1000)
3.?使用SQL*PLUS（1）
登錄SQL*PLUS的方法
用戶名，密碼，數(shù)據(jù)庫連接字符串
數(shù)據(jù)庫連接字符串可以用net8?easy?configuration建立本地網(wǎng)絡(luò)服務(wù)名配置，
也可以手工編輯$ORACLE_HOME/network/admin/tnsnames.ora文件。
在UNIX系統(tǒng)下不要用
$sqlplus?username/password@dbname
這樣別的用戶用$ps命令能看出用戶的密碼
SQL>?desc?table; 顯示表結(jié)構(gòu)
SQL>?select?*?from?tab; 查看用戶下所有的表
SQL>?set?pause?on; 可以使大量結(jié)果集在用戶按“Enter”(回車)后翻頁
SQL>?set?pagesize?100; 設(shè)定SQL語句返回結(jié)果集一頁的行數(shù)100,?默認(rèn)值是14
SQL>?set?linesize?100; 設(shè)定SQL語句返回結(jié)果集一行的寬度100,?默認(rèn)值是80
3.?使用SQL*PLUS（2）SQL*PLUS里的編輯命令
最近一條SQL命令語句存在ORACLE內(nèi)存（sql?buffer）里,但SQL*PLUS命令卻不會(huì)存進(jìn)去。
SQL*PLUS里的編輯命令:
A[PPEND]?text 把text增加到當(dāng)前行后面
C[HANGE]?/old/new/ 把當(dāng)前行old符號(hào)替換成new符號(hào),new為空，刪除old符號(hào)
CL[EAR]?BUFF[ER] 從sql?buffer里刪除所有的行
DEL?n 刪除sql?buffer里第n行
I[NPUT]?text 在sql?buffer后面插入text
L[IST]?n 顯示sql?buffer里第n行
n 使第n行為當(dāng)前行
n????text 第n行替換成text
0????text 在第一行前面插入一行
3.?使用SQL*PLUS（3）?SQL*PLUS里的文件命令:
SAV[E]?filename?[REP[LACE]?|?APP[END]?]
把sql?buffer里的SQL命令存到OS下一個(gè)文件filename,默認(rèn)的文件名后綴為.sql.
REP[LACE]替換filename里的SQL命令
APP[END] 把sql?buffer里的SQL命令添加到filename后
GET?filename 從filename里把SQL命令讀到sql?buffer
STA[RT]?filename 運(yùn)行上次保存到文件filename里的SQL命令
@filename 運(yùn)行上次保存到文件filename里的SQL命令
EDIT 打開編輯窗口，編輯sql?buffer里的SQL命令
EDIT?filename 打開編輯窗口，編輯文件filename里的SQL命令
SPO[OL]?filename?[OFF?|?OUT] 把SQL命令結(jié)果輸出到OS下一個(gè)文件filename? OFF結(jié)束輸出到文件,?OUT結(jié)束輸出到文件并打印文件?
???????? 4.?SQL*PLUS里規(guī)定字段的顯示格式
規(guī)定數(shù)字的顯示格式
SQL>column?字段名?format?99999999999;
SQL>column?字段名?format?999,999,999,999;
規(guī)定字符串的顯示寬度?
SQL>column?字段名?format?a數(shù)字?[word_wrapped];
說明:?一行只顯示數(shù)字位的長(zhǎng)度,?超過長(zhǎng)度折行,加word_wrapped后,?單詞不會(huì)折行
規(guī)定long字符的顯示寬度
SQL>set?long?200;
規(guī)定字段名的顯示內(nèi)容
SQL>?column?字段名?heading?'字段名顯示內(nèi)容';
SQL>?set?heading?off; 查詢時(shí)不顯示字段名稱
規(guī)定字段的對(duì)齊方向
SQL>?column?字段名?justify?[left?|?right?|?center];
清除字段的格式
SQL>?column?字段名?clear;
5.?SQL*PLUS里規(guī)定字段的顯示格式例子
SQL>?column?last_name?heading?'Employee|Name'?format?a15;
SQL>?column?salary?justify?right?format?$99,999.99;
SQL>?column?start_date?format?a10?null?'Not?Hired';
說明：如果start_date為null,?顯示字符串'Not?Hired'
6.?判斷題(T/F)
(1).?SQL?command?are?always?held?in?sql?buffer. [T]
(2).?SQL*PLUS?command?assit?with?query?data. [T]
SQL*PLUS命令只控制SELECT結(jié)果集的顯示格式及控制文件.只有SQL命令能訪問數(shù)據(jù)庫.
二、限制選擇行
1.?按指定的規(guī)則排序
SELECT?expr?FROM?table?[ORDER?BY?{column,?expr}?[ASC?|?DESC]?];
默認(rèn)的排序是ASC升序(由小到大)
還可以O(shè)RDER?BY?字段名的位置[1]|?[2]?ASC|?DESC;
當(dāng)字段名很復(fù)雜或者是算術(shù)表達(dá)式時(shí)用字段名顯示的位置排序很方便.
2.?用WHERE限制選擇行(1)
比較操作符 =?>?<?>=?<=?!=?<>?^= 與NULL比較不能用上面的比較操作符 ANY SOME ALL
SQL操作符 BETWEEN?…?AND… IN LIKE IS?NULL ?
NOT?BETWEEN?…?AND…? NOT?IN NOT?LIKE IS?NOT?NULL
邏輯操作符 AND OR NOT
3.?用WHERE限制選擇行(2)
比較順序(可以用括號(hào)改變它們的順序)
(1).?=?<?>?>=?<=?in?like?is?null?between
(2).?and
(3).?Or
注意:?char和varchar2的比較規(guī)則有不同:?
char比較時(shí)會(huì)忽略字符串后面的空格.?varchar2會(huì)計(jì)算字符串后面的空格
4.?LIKE操作
% 零到任意多個(gè)字符 _ 一個(gè)字符
例如:?字段名?like?'M%' 字段名?like?'%m%'?字段名?like?'job_'
如果要找含下劃線的字符,?要加反斜線?例如:字段名?like?'%X/_Y%'?escape?'/'
5.?日期字段的比較
舉例:
日期字段?between?to_date('2001-12-12','YYYY-MM-DD')?and?to_date('2002-02-01','YYYY-MM-DD')
日期字段>?to_date('2001-12-12','YYYY-MM-DD')?and日期字段<=?to_date('2002-02-01','YYYY-MM-DD');?
6.?不能用到索引的比較操作符
IS?NULL
IS?NOT?NULL
LIKE?'%m%'
三、單行函數(shù)
1.?數(shù)字函數(shù)
ABS?取絕對(duì)值 POWER?乘方?LN?10為底數(shù)取冪
SQRT?平方根 EXP?e的n次乘方?LOG(m,n)?m為底數(shù)n取冪? ?
數(shù)學(xué)運(yùn)算函數(shù):ACOS?ATAN?ATAN2?COS?COSH?SIGN?SIN?SINH?TAN?TANH?
CEIL? ?大于或等于取整數(shù)
FLOOR? ?小于或等于取整數(shù)
MOD? ?取余數(shù)
ROUND(n,m)?按m的位數(shù)取四舍五入值如果round(日期):?中午12以后將是明天的日期.?round(sysdate,'Y')是年的第一天
TRUNC(n,m)?按m的位數(shù)取前面的數(shù)值如果trunc(日期),?確省的是去掉時(shí)間
2.?字符函數(shù)
CHR? 按數(shù)據(jù)庫的字符集由數(shù)字返回字符?
CONCAT(c1,c2)? 把兩個(gè)字符c1,c2組合成一個(gè)字符,?和?||?相同
REPLACE(c,s,r)? 把字符c里出現(xiàn)s的字符替換成r,?返回新字符?
SUBSTR(c,m,n) m大于0,字符c從前面m處開始取n位字符,m等于0和1一樣,?
m小與0,字符c從后面m處開始取n位字符
TRANSLATE(c,f1,t1)? 字符c按f1到t1的規(guī)則轉(zhuǎn)換成新的字符串
INITCAP? 字符首字母大寫,其它字符小寫
LOWER? 字符全部小寫
UPPER 字符全部大寫
LTRIM(c1,c2) 去掉字符c1左邊出現(xiàn)的字符c2
RTRIM(c1,c2)?
TRIM(c1,c2)? 去掉字符c1左右兩邊的字符c2
LPAD(c1,n,c2)? 字符c1按制定的位數(shù)n顯示不足的位數(shù)用c2字符串替換左邊的空位
RPAD(c1,n,c2)
3.?日期函數(shù)
ADD_MONTHS(d,n) 日期值加n月
LAST_DAY(d)? 返回當(dāng)月的最后一天的日期
MONTHS_BETWEEN(d1,d2)? 兩個(gè)日期值間的月份,d1<d2?返回負(fù)數(shù)
NEXT_DAY(d)? 返回日期值下一天的日期
SYSDATE? 當(dāng)前的系統(tǒng)時(shí)間
DUAL是SYS用戶下一個(gè)空表，它只有一個(gè)字段dummy
4.?轉(zhuǎn)換函數(shù)(1)
TO_CHAR(date,'日期顯示格式')?
TO_CHAR(number)? 用于顯示或報(bào)表的格式對(duì)齊
TO_DATE(char,'日期顯示格式')?
TO_LOB ? 把long字段轉(zhuǎn)換成lob字段
TO_NUMBER(char) 用于計(jì)算或者比較大小?
4.?轉(zhuǎn)換函數(shù)(2)
to_date里日期顯示格式
YYYY 年 YEAR YYY YY Y
Q 季度
MM 月 MONTH MON
W 星期?(week?of?month) WW,?IW?(week?of?year)?
(說明：周計(jì)是按ISO標(biāo)準(zhǔn),從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)?
DD 日 DAY DY
HH24 小時(shí) HH12 HH
MI 分鐘
SS 秒
如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參數(shù) NLS_DATE_FORMAT=yyyy-mm-dd?hh24:mi:ss可以在UNIX環(huán)境變量或者NT的注冊(cè)表里的設(shè)置 NLS_DATE_FORMAT=yyyy-mm-dd?hh24:mi:ss
4.?轉(zhuǎn)換函數(shù)(3)
如果想固定日期的顯示格式可以用alter?session命令改變
SQL>?alter?session?set?nls_date_format='yyyy-mm-dd?hh24:mi:ss';
它的作用順序如下:
initialization?parameter
Environment?variable
ALTER?SESSION?command
4.?轉(zhuǎn)換函數(shù)?(4)
to_char(number)里數(shù)字顯示格式
9 數(shù)字位
0 數(shù)字前面補(bǔ)0? to_char(-1200,'00000.00')
. 小數(shù)點(diǎn)的位置
, 標(biāo)記位置的逗號(hào) 用在數(shù)字顯示格式的左邊
L 根據(jù)數(shù)據(jù)庫字符集加貨幣符號(hào) to_char(-1200,'L9999.99')
B 把數(shù)字0顯示為空格,用在數(shù)字顯示格式的右邊
MI 右邊的負(fù)數(shù)標(biāo)記? ? to_char(-1200,'9999.99MI')
PR? 括起來的負(fù)數(shù) ? to_char(-1200,'9999.99PR')
EEEE 用指數(shù)方式表示 ? to_char(-1200,'9999.99EEEE')
5.?輸入字符,返回?cái)?shù)字的函數(shù)
instr(c1,c2) 字符c2出現(xiàn)在c1的位置,?不出現(xiàn),?返回0,?常用于模糊查詢
length? 按數(shù)據(jù)庫的字符集,算出字符c的長(zhǎng)度,跟數(shù)據(jù)庫的字符集有關(guān),?一個(gè)漢字長(zhǎng)度為1
6.?有邏輯比較的函數(shù)NVL(EXPR1,?EXPR2)函數(shù)
解釋: IF?EXPR1=NULL? RETURN?EXPR2
ELSE? RETURN?EXPR1
DECODE(AA，V1，R1，V2，R2....)函數(shù)
解釋:?IF?AA=V1?THEN?RETURN?R1
?IF?AA=V2?THEN?RETURN?R2
..…
?ELSE
?RETURN?NULL
舉例:?decode(id,1,'dept?sale',2,'dept?tech')
四、從多個(gè)表里選取數(shù)據(jù)記錄
1.?數(shù)據(jù)表間的連接
簡(jiǎn)單的連接語法:
SELECT?字段名1,?字段名2,?……?FROM?表名1,?[表名2,?……]?
WHERE?表名1.字段名?=?表名2.?字段名?[?AND?……]?;?
SELECT?字段名1,?字段名2,?……?FROM?表名1,?[表名2,?……]
? WHERE?表名1.字段名?=?表名2.?字段名(+)?[?AND?……]?;?
有(+)號(hào)的字段位置自動(dòng)補(bǔ)空值?
連接的分類:
等于的連接 ?=
不等于的連接 != BETWEEN?…?AND?… IN 注意IN和OR不能一起用
外連接 有一個(gè)字段名(+)?,?沒有滿足的條件補(bǔ)空值
自連接 同一個(gè)表自己跟自己連接 例如找重復(fù)記錄
2.?數(shù)據(jù)表間的連接例子
刪除table_name表里字段名email重復(fù)的記錄:
SQL>delete?from?table_name?t1
where?t1.rowid?>
(select?min(rowid)?from?table_name?t2
where?t1.email?=?t2.email
group?by?email?
having?count(email)?>?1);
找到手機(jī)用戶的服務(wù)區(qū)域:
SQL>?select?a.handphoneno,nvl(c.name,'null'),a.totalscore?
?from?topscore?a,chargeoperator?cc,chargeoperatorinfo?c?
?where?substr(a.handphoneno,1,7)=cc.hpnohead(+)?
?and?cc.chargetype=c.chargetype(+)
?order?by?a.totalscore?desc;
3.?數(shù)據(jù)表間的連接技巧
連接N個(gè)表,?需要N-1個(gè)連接操作
被連接的表最好建一個(gè)單字符的別名,?字段名前加上這個(gè)單字符的別名
BETWEEN?..?AND..?比用?>=?AND?<=?要好
連接操作的字段名上最好要有索引
連接操作的字段最好用整數(shù)數(shù)字類型
有外連接時(shí),?不能用OR或IN的比較操作
4.?如何分析和執(zhí)行SQL語句
寫多表連接SQL語句時(shí)要知道它的分析執(zhí)行計(jì)劃的情況.?
Sys用戶下運(yùn)行@/ORACLE_HOME/sqlplus/admin/plustrce.sql?
產(chǎn)生plustrace角色
Sys用戶下把此角色賦予一般用戶?SQL>?grant?plustrace?to?&username;
一般用戶下運(yùn)行@/ORACLE_HOME/rdbms/admin/utlxplan.sql?
產(chǎn)生plan_table
SQL>?set?time?on; 說明：打開時(shí)間顯示?
SQL>?set?autotrace?on;? 說明：打開自動(dòng)分析統(tǒng)計(jì)，并顯示SQL語句的運(yùn)行結(jié)果
SQL>?set?autotrace?traceonly; 說明：打開自動(dòng)分析統(tǒng)計(jì)，不顯示SQL語句的運(yùn)行結(jié)果
接下來你就運(yùn)行測(cè)試SQL語句，看到其分析統(tǒng)計(jì)結(jié)果了。
一般來講，我們的SQL語句應(yīng)該避免大表的全表掃描。
SQL>?set?autotrace?off;? 說明：關(guān)閉自動(dòng)分析統(tǒng)計(jì)
五、集合函數(shù) 經(jīng)常和group?by一起使用?
1.?集合函數(shù)列表
AVG? (DISTINCT?|?ALL?|?N) 取平均值
COUNT (DISTINCT?|?ALL?|?N?|?expr?|?*?) 統(tǒng)計(jì)數(shù)量
MAX (DISTINCT?|?ALL?|?N) 取最大值
MIN? (DISTINCT?|?ALL?|?N) 取最小值
SUM? (DISTINCT?|?ALL?|?N) 取合計(jì)值
STDDEV? (DISTINCT?|?ALL?|?N) 取偏差值,如果組里選擇的內(nèi)容都相同,結(jié)果為0
VARIANCE? (DISTINCT?|?ALL?|?N) ? 取平方偏差值
2.?使用集合函數(shù)的語法
SELECT?column,?group_function?FROM?table
WHERE?condition GROUP?BY?group_by_expression
HAVING?group_condition ORDER?BY?column;
3.?使用count時(shí)的注意事項(xiàng)
SELECT?COUNT(*)?FROM?table;
SELECT?COUNT(常量)?FROM?table;
都是統(tǒng)計(jì)表中記錄數(shù)量,如果沒有PK后者要好一些
SELECT?COUNT(all?字段名)?FROM?table;
SELECT?COUNT(字段名)?FROM?table;
不會(huì)統(tǒng)計(jì)為NULL的字段的數(shù)量
SUM,AVG時(shí)都會(huì)忽略為NULL的字段
4.?用group?by時(shí)的限制條件
SELECT字段名不能隨意,?要包含在GROUP?BY的字段里
?GROUP?BY后ORDER?BY時(shí)不能用位置符號(hào)和別名
?限制GROUP?BY的顯示結(jié)果,?用HAVING條件
?
5.?例子
SQL>?select?title,sum(salary)?payroll?from?s_emp
where?title?like?'VP%' group?by?title
having?sum(salary)>5000 order?by?sum(salary)?desc;
找出某表里字段重復(fù)的記錄數(shù),?并顯示
SQL>?select?(duplicate?field?names)?from?table_name
?group?by?(list?out?fields)?having?count(*)>1;
6.?判斷題(T/F)
(1)?Group?functions?include?nulls?in?calculations [F]
(2)?Using?the?having?clause?to?exclude?rows?from?a?group?calculation [F]
解釋:
Group?function?都是忽略NULL值的 如果您要計(jì)算NULL值,?用NVL函數(shù)
Where語句在Group?By前把結(jié)果集排除在外Having語句在Group?By后把結(jié)果集排除在外
7.?在SQL*PLUS里可使用的其它命令:
Ctrl^C 終止正在運(yùn)行的SQL語句
remark?/*...*/?-- 注釋符號(hào)
HOST? 可執(zhí)行的操作系統(tǒng)下的命令 有些unix可以用?!
BREAK?ON?column_name?SKIP?n?[?ON?column_name?SKIP?n?]?
?按字段的名稱column_name分隔顯示,更清晰,SKIP?n?是在分隔處空行的數(shù)量n
?BREAK?ON?ROW?SKIP?n 每一行間隔都放n個(gè)空行
COMPUTE?集合運(yùn)算符?OF?字段1?ON?字段2 按字段2對(duì)字段1進(jìn)行集合運(yùn)算
COMPUTE后面可以跟的集合運(yùn)算符:
SUM?MINIMUM?MAXIMUM?AVG?STD?VARIANCE?COUNT?NUMBER
8.在SQL*PLUS里可使用的其它命令舉例:(scott用戶)
?
?BREAK?ON?REPORT
?COMPUTE?SUM?LABEL?TOTAL?OF?SAL?ON?REPORT 在全部結(jié)果集后面算合計(jì)
?select?ename,sal?from?emp?where?job='SALESMAN';
?
?COMPUTE?AVG?LABEL?avg?OF?SAL?ON?REPORT 在全部結(jié)果集后面算平均值
?/ 再次執(zhí)行上次的sql語句
?
?break?on?DEPTNO?skip?2?on?JOB?skip?1? 在BREAK字段結(jié)果集后面算合計(jì)
COMPUTE?SUM?OF?SAL?ON?DEPTNO
?SELECT?DEPTNO,JOB,ENAME,SAL?FROM?EMP?ORDER?BY?DEPTNO,JOB;
?
SQL>?CLEAR?BREAKS; 清除設(shè)置的BREAK條件
SQL>?CLEAR?COMPUTES; 清除設(shè)置的COMPUTE條件
六、子查詢
1.?查詢語句可以嵌套?
例如:?SELECT?……?FROM?(SELECT?……?FROM表名1,?[表名2,?……]?WHERE?條件)?WHERE?條件2;?
2.?何處可用子查詢?
當(dāng)查詢條件是不確定的條件時(shí)
DML(insert,?update,delete)語句里也可用子查詢
HAVING里也可用子查詢
3.?兩個(gè)查詢語句的結(jié)果可以做集合操作?
例如:?
并集UNION(去掉重復(fù)記錄)
并集UNION?ALL(不去掉重復(fù)記錄)?
差集MINUS,? ?
交集INTERSECT?
4.?子查詢的注意事項(xiàng)
先執(zhí)行括號(hào)里面的SQL語句，一層層到外面
內(nèi)部查詢只執(zhí)行一次
如果里層的結(jié)果集返回多個(gè)，不能用=?>?<?>=?<=等比較符要用IN.
5.?子查詢的例子(1)
SQL>?select?title,avg(salary)?from?s_emp
? group?by?title? Having?avg(salary)?=
? (select?min(avg(salary))?from?s_emp
? group?by?title);
找到最低平均工資的職位名稱和工資
5.?子查詢的例子(2)
子查詢可以用父查詢里的表名
這條SQL語句是對(duì)的:
SQL>select?cty_name?from?city?where?st_code?in
(select?st_code?from?state?where?st_name='TENNESSEE'?and?
?city.cnt_code=state.cnt_code);
說明：父查詢調(diào)用子查詢只執(zhí)行一次．
6.取出結(jié)果集的80?到100的SQL語句
ORACLE處理每個(gè)結(jié)果集只有一個(gè)ROWNUM字段標(biāo)明它的邏輯位置,
并且只能?用ROWNUM<100,?不能用ROWNUM>80。
以下是經(jīng)過分析后較好的兩種ORACLE取得結(jié)果集80到100間的SQL語句(?ID是唯一關(guān)鍵字的字段名?)：?
語句寫法：?
SQL>select?*?from?(
(?select?rownum?as?numrow,?c.*?from?(
select?[field_name,...]?from?table_name where?條件1?order?by?條件2)?c)
?where?numrow?>?80?and?numrow?<=?100?)
?order?by?條件3;?
七、在執(zhí)行SQL語句時(shí)綁定變量
1.?接收和定義變量的SQL*PLUS命令
ACCEPT
DEFINE UNDEFINE
&
2.?綁定變量SQL語句的例子(1)
SQL>?select?id,?last_name,?salary?from s_emp?where?dept_id?=?&department_number;
Enter?value?for?department_number:?10
old?1:?select?id,?last_name,?salary?from?s_emp?where?dept_id=&department_number;
new?1:?select?id,?last_name,?salary?from?s_emp?where?dept_id=?10
SQL>?SET?VERIFY?OFF?|?ON;可以關(guān)閉和打開提示確認(rèn)信息old?1和new?1的顯示.
3.?綁定變量SQL語句的例子(2)
SQL>?select?id,?last_name,?salary
? from s_emp
? ?where ?title?=?'&job_title';
Enter?value?for?job_title:?Stock?Clerk
SQL>?select?id,?last_name,?salary
? from s_emp
? ?where ?hiredate?>to_date(?'&start_hire_date','YYYY-MM-DD');
Enter?value?for?start_hire_date?:?2001-01-01
把綁定字符串和日期類型變量時(shí)，變量外面要加單引號(hào)
也可綁定變量來查詢不同的字段名
輸入變量值的時(shí)候不要加;等其它符號(hào)
4.?ACCEPT的語法和例子
SQL>?ACCEPT?variable?[datatype]?[FORMAT]?[PROMPT?text]?[HIDE]
說明： variable 指變量名 datatype 指變量類型，如number,char等 format 指變量顯示格式 prompt?text 可自定義彈出提示符的內(nèi)容text hide 隱藏用戶的輸入符號(hào)
使用ACCEPT的例子：
ACCEPT?p_dname?PROMPT?'Provide?the?department?name:?'
ACCEPT?p_salary?NUMBER?PROMPT?'Salary?amount:?'
ACCEPT?pswd?CHAR?PROMPT?'Password:?'?HIDE
ACCEPT?low_date?date?format?'YYYY-MM-DD'?PROMPT“Enter?the?low?date?range('YYYY-MM-DD'):”
4.?DEFINE的語法和例子
SQL>?DEFINE?variable?=?value
說明： variable 指變量名 value 指變量值
定義好了變良值后,?執(zhí)行綁定變量的SQL語句時(shí)不再提示輸入變量
使用DEFINE的例子：
SQL>?DEFINE?dname?=?sales
SQL>?DEFINE?dname
DEFINE?dname?=?“sales”?(CHAR)
SQL>?select?name?from?dept?where?lower(name)='&dname';
NAME
-------------------------
sales
sales
SQL>?UNDEFINE?dname
SQL>?DEFINE?dname
Symbol?dname?is?UNDEFINED
5.?SQL*PLUS里傳遞參數(shù)到保存好的*.sql文件里
SQL>?@?/路徑名/文件名?參數(shù)名1[,參數(shù)名2,?….]
SQL>?start?/路徑名/文件名?參數(shù)名1[,參數(shù)名2,?….]
注意事項(xiàng):
一次最多只能獲取9個(gè)&變量,?變量名稱只能是從&1,&2到&9
變量名后不要加特殊的結(jié)束符號(hào)
如果在SQL*PLUS里要把&符號(hào)保存在ORACLE數(shù)據(jù)庫里,要修改sql*plus環(huán)境變量define
SQL>?set?define?off;
八、概述數(shù)據(jù)模型和數(shù)據(jù)庫設(shè)計(jì)
1.?系統(tǒng)開發(fā)的階段:
Strategy?and?Analysis
Design
Build?and?Document
Transition
Production
2.?數(shù)據(jù)模型
Model?of?system?in?client's?mind
Entity?model?of?client's?model
Table?model?of?entity?model
Tables?on?disk
3.?實(shí)體關(guān)系模型?(ERM)概念
ERM?(?entity?relationship?modeling)
實(shí)體 存有特定信息的目標(biāo)和事件 ?例如:?客戶,訂單等
屬性 描述實(shí)體的屬性 ?例如:?姓名,電話號(hào)碼等
關(guān)系 兩個(gè)實(shí)體間的關(guān)系 ?例如:訂單和產(chǎn)品等
實(shí)體關(guān)系模型圖表里的約定
Dashed?line (虛線) 可選參數(shù)?“may?be”
Solid?line (實(shí)線) 必選參數(shù)?“must?be”
Crow's?foot (多線) 程度參數(shù)?“one?or?more”
Single?line (單線) 程度參數(shù)?“one?and?only?one”
4.?實(shí)體關(guān)系模型例子
每個(gè)訂單都必須有一個(gè)或幾個(gè)客戶
每個(gè)客戶可能是一個(gè)或幾個(gè)訂單的申請(qǐng)者
5.?實(shí)體關(guān)系的類型
1:1 一對(duì)一 例如:?的士和司機(jī)?
M:1 多對(duì)一 例如:?乘客和飛機(jī)
1:M 一對(duì)多 例如:?員工和技能
6.?校正實(shí)體關(guān)系的原則
屬性是單一值的,?不會(huì)有重復(fù)
屬性必須依存于實(shí)體,?要有唯一標(biāo)記
沒有非唯一屬性依賴于另一個(gè)非唯一的屬性
7.?定義結(jié)構(gòu)時(shí)的注意事項(xiàng)
減少數(shù)據(jù)冗余
減少完整性約束產(chǎn)生的問題
確認(rèn)省略的實(shí)體,關(guān)系和屬性
8.?完整性約束的要求
Primary?key 主關(guān)鍵字 唯一非NULL
Foreign?key 外鍵 依賴于另一個(gè)Primary?key,可能為NULL
Column 字段名 符合定義的類型和長(zhǎng)度
Constraint 約束條件 用戶自定義的約束條件,要符合工作流要求
例如:?一個(gè)銷售人員的提成不能超過它的基本工資
Candidate?key 候選主關(guān)鍵字 多個(gè)字段名可組成候選主關(guān)鍵字,?其組合是唯一和非NULL的
9.?把實(shí)體關(guān)系圖映射到關(guān)系數(shù)據(jù)庫對(duì)象的方法
把簡(jiǎn)單實(shí)體映射到數(shù)據(jù)庫里的表
把屬性映射到數(shù)據(jù)庫里的表的字段,?標(biāo)明類型和注釋
把唯一標(biāo)記映射到數(shù)據(jù)庫里的唯一關(guān)鍵字
把實(shí)體間的關(guān)系映射到數(shù)據(jù)庫里的外鍵
其它的考慮:
設(shè)計(jì)索引,使查詢更快
建立視圖,使信息有不同的呈現(xiàn)面,?減少復(fù)雜的SQL語句
計(jì)劃存儲(chǔ)空間的分配
重新定義完整性約束條件
10.?實(shí)體關(guān)系圖里符號(hào)的含義
PK 唯一關(guān)鍵字的字段
FK 外鍵的字段
FK1,FK2 同一個(gè)表的兩個(gè)不同的外鍵
FK1,FK1 兩個(gè)字段共同組成一個(gè)外鍵
NN 非null字段
U 唯一字段
U1,U1 兩個(gè)字段共同組成一個(gè)唯一字段
11.DELPHI中用TStoredProc使用包中的存儲(chǔ)過程
?? sp1: TStoredProc;
begin
? sp1.UnPrepare;
? sp1.StoredProcName := 'CMES.MYPACKAGE.MYPROCEDURE';
? sp1.Params.Clear;
? sp1.Params.CreateParam(ftString?,'IN_VARIABLE',ptInput?);
?? sp1.Params.CreateParam(ftString?,'RES',ptOutput??);
? sp1.Prepare;
? sp1.Params[0].AsString := 'in';
? sp1.ExecProc;
//? sp1.GetResults;
? showmessage(sp1.Parambyname('res').AsString);
end;
12.使用ADO返回?cái)?shù)據(jù)集
var
AResult , BResult : integer;
begin
ADODataSet1.Close ;
ADODataSet1.CommandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法調(diào)用oracle過程時(shí)，參數(shù)必須由?來傳， 即使你要傳的參數(shù)為常理
//輸出游標(biāo)的參數(shù)不需要指定!!!!!!，本來此函數(shù)帶三個(gè)參數(shù)，我們這里只需要傳兩個(gè)參數(shù).
ADODataSet1.CommandText := '{call PKG_JCCTEST1.GetSubAndSum2(?,?)}' ;
//***C 順序有關(guān)，createparam必須放在commandtext賦值語句之后.
// 創(chuàng)建第一個(gè)參數(shù)，對(duì)應(yīng)call中的第一個(gè)?,ftinteger為類型,10為長(zhǎng)度，45為傳入的實(shí)參值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,45);
//創(chuàng)建第二個(gè)參數(shù)，根據(jù)createparameter的順序 自動(dòng)與call中的第二個(gè)參數(shù)對(duì)應(yīng)
ADODataSet1.Parameters.CreateParameter('p2',ftinteger,pdinput,10,4);
//下面調(diào)用ADODataSet1 的open方法，返回?cái)?shù)據(jù)集(對(duì)應(yīng)包過程的游標(biāo))
ADODataSet1.Open ;
//根據(jù)存儲(chǔ)過程，數(shù)據(jù)集只有一條記錄，所以不需要用while do 來遍歷數(shù)據(jù)集，直接取數(shù)據(jù)了
//此處的字段名根據(jù)包過程中的返回游標(biāo) 對(duì)應(yīng)的字段名來取
//定義的存儲(chǔ)過程返回游標(biāo)如： open ResultCursor for
// select p1-p2 as "sum", p1+p2 as "sub" from dual;
//把對(duì)應(yīng)的字段值取出來即可
AResult := ADODataSet1.Fields.FieldByName('sub').Value ;
BResult := ADODataSet1.Fields.FieldByName('sum').Value ;
//顯示結(jié)果
showmessage(inttostr(AResult)) ;
showmessage(inttostr(BResult)) ;
end;
前言:?關(guān)系數(shù)據(jù)庫的簡(jiǎn)單介紹?
1970.Dr.E.F.codded?創(chuàng)建了關(guān)系數(shù)據(jù)庫的模型（類似現(xiàn)在常用的二維表格）
關(guān)系數(shù)據(jù)庫由許多數(shù)據(jù)對(duì)象組成,?被關(guān)系操作SQL命令管理著,?數(shù)據(jù)之間有完整性的約束條件.
RDBMS (?relational?database?management?system?)
ORDBMS (?object?relational?database?management?system?)
ORACLE?RDBMS常稱為ORACLE?SERVER
它的內(nèi)核是用C語言寫的.
ORACLE的簡(jiǎn)單介紹?
ORACLE公司是一家提供綜合技術(shù)產(chǎn)品、方案、服務(wù)的數(shù)據(jù)庫公司
ORACLE數(shù)據(jù)庫設(shè)計(jì)成可大量存儲(chǔ)數(shù)據(jù)，快速查詢數(shù)據(jù)，保證數(shù)據(jù)的安全和一致性，
跨網(wǎng)絡(luò)的分布式管理及客戶-服務(wù)器的配置等。
ORACLE?SQL語法符合ANSI?1986標(biāo)準(zhǔn)
SQL 和數(shù)據(jù)庫交流的命令式語言
SQL*PLUS ORACLE的一種工具,?用來運(yùn)行SQL和PL/SQL語句
PL/SQL ORACLE的過程化編程語言
一、選擇行
1.?簡(jiǎn)單的SELECT?語句
SELECT?[DISTINCT]?{*,COLUMN?[ALIAS],?……}?FROM?table_name;
字段的非唯一的結(jié)果集
DISTINCT?字段名1?[?,字段名2]?
數(shù)字類型字段名之間可以進(jìn)行算術(shù)運(yùn)算
+??-?*?/??等
例如:?(字段名1*字段名2)/3
合并字段內(nèi)容的連接字符?||
字段名1?||?字段名2
字段可以有別名
SELECT?字段名1?[AS]?'字段名1?解釋'?FROM?table;
2.?處理NULL
NULL?未定義，不可操作，什么都不是
NULL?!=?0 NULL!='空格'
和NULL的任何運(yùn)算都返回NULL
ORACLE里未定義的變量值都為NULL
NVL函數(shù)可把NULL轉(zhuǎn)換成其它類型的符號(hào)
NVL(EXPR1,?EXPR2)函數(shù)
解釋: IF?EXPR1=NULL? RETURN?EXPR2
ELSE? RETURN?EXPR1
編程技巧:?NVL函數(shù)在多條件模糊查詢的時(shí)候比較有用
nvl('+input_value+','0')='0'?or?filed_name?like?'%+input_value+%'
NVL函數(shù)可返回多種數(shù)據(jù)類型:
返回日期 NVL(start_date,'2002-02-01')
返回字符串 NVL(title,'no?title')
返回?cái)?shù)字 NVL(salary,1000)
3.?使用SQL*PLUS（1）
登錄SQL*PLUS的方法
用戶名，密碼，數(shù)據(jù)庫連接字符串
數(shù)據(jù)庫連接字符串可以用net8?easy?configuration建立本地網(wǎng)絡(luò)服務(wù)名配置，
也可以手工編輯$ORACLE_HOME/network/admin/tnsnames.ora文件。
在UNIX系統(tǒng)下不要用
$sqlplus?username/password@dbname
這樣別的用戶用$ps命令能看出用戶的密碼
SQL>?desc?table; 顯示表結(jié)構(gòu)
SQL>?select?*?from?tab; 查看用戶下所有的表
SQL>?set?pause?on; 可以使大量結(jié)果集在用戶按“Enter”(回車)后翻頁
SQL>?set?pagesize?100; 設(shè)定SQL語句返回結(jié)果集一頁的行數(shù)100,?默認(rèn)值是14
SQL>?set?linesize?100; 設(shè)定SQL語句返回結(jié)果集一行的寬度100,?默認(rèn)值是80
3.?使用SQL*PLUS（2）SQL*PLUS里的編輯命令
最近一條SQL命令語句存在ORACLE內(nèi)存（sql?buffer）里,但SQL*PLUS命令卻不會(huì)存進(jìn)去。
SQL*PLUS里的編輯命令:
A[PPEND]?text 把text增加到當(dāng)前行后面
C[HANGE]?/old/new/ 把當(dāng)前行old符號(hào)替換成new符號(hào),new為空，刪除old符號(hào)
CL[EAR]?BUFF[ER] 從sql?buffer里刪除所有的行
DEL?n 刪除sql?buffer里第n行
I[NPUT]?text 在sql?buffer后面插入text
L[IST]?n 顯示sql?buffer里第n行
n 使第n行為當(dāng)前行
n????text 第n行替換成text
0????text 在第一行前面插入一行
3.?使用SQL*PLUS（3）?SQL*PLUS里的文件命令:
SAV[E]?filename?[REP[LACE]?|?APP[END]?]
把sql?buffer里的SQL命令存到OS下一個(gè)文件filename,默認(rèn)的文件名后綴為.sql.
REP[LACE]替換filename里的SQL命令
APP[END] 把sql?buffer里的SQL命令添加到filename后
GET?filename 從filename里把SQL命令讀到sql?buffer
STA[RT]?filename 運(yùn)行上次保存到文件filename里的SQL命令
@filename 運(yùn)行上次保存到文件filename里的SQL命令
EDIT 打開編輯窗口，編輯sql?buffer里的SQL命令
EDIT?filename 打開編輯窗口，編輯文件filename里的SQL命令
SPO[OL]?filename?[OFF?|?OUT] 把SQL命令結(jié)果輸出到OS下一個(gè)文件filename? OFF結(jié)束輸出到文件,?OUT結(jié)束輸出到文件并打印文件?
???????? 4.?SQL*PLUS里規(guī)定字段的顯示格式
規(guī)定數(shù)字的顯示格式
SQL>column?字段名?format?99999999999;
SQL>column?字段名?format?999,999,999,999;
規(guī)定字符串的顯示寬度?
SQL>column?字段名?format?a數(shù)字?[word_wrapped];
說明:?一行只顯示數(shù)字位的長(zhǎng)度,?超過長(zhǎng)度折行,加word_wrapped后,?單詞不會(huì)折行
規(guī)定long字符的顯示寬度
SQL>set?long?200;
規(guī)定字段名的顯示內(nèi)容
SQL>?column?字段名?heading?'字段名顯示內(nèi)容';
SQL>?set?heading?off; 查詢時(shí)不顯示字段名稱
規(guī)定字段的對(duì)齊方向
SQL>?column?字段名?justify?[left?|?right?|?center];
清除字段的格式
SQL>?column?字段名?clear;
5.?SQL*PLUS里規(guī)定字段的顯示格式例子
SQL>?column?last_name?heading?'Employee|Name'?format?a15;
SQL>?column?salary?justify?right?format?$99,999.99;
SQL>?column?start_date?format?a10?null?'Not?Hired';
說明：如果start_date為null,?顯示字符串'Not?Hired'
6.?判斷題(T/F)
(1).?SQL?command?are?always?held?in?sql?buffer. [T]
(2).?SQL*PLUS?command?assit?with?query?data. [T]
SQL*PLUS命令只控制SELECT結(jié)果集的顯示格式及控制文件.只有SQL命令能訪問數(shù)據(jù)庫.
二、限制選擇行
1.?按指定的規(guī)則排序
SELECT?expr?FROM?table?[ORDER?BY?{column,?expr}?[ASC?|?DESC]?];
默認(rèn)的排序是ASC升序(由小到大)
還可以O(shè)RDER?BY?字段名的位置[1]|?[2]?ASC|?DESC;
當(dāng)字段名很復(fù)雜或者是算術(shù)表達(dá)式時(shí)用字段名顯示的位置排序很方便.
2.?用WHERE限制選擇行(1)
比較操作符 =?>?<?>=?<=?!=?<>?^= 與NULL比較不能用上面的比較操作符 ANY SOME ALL
SQL操作符 BETWEEN?…?AND… IN LIKE IS?NULL ?
NOT?BETWEEN?…?AND…? NOT?IN NOT?LIKE IS?NOT?NULL
邏輯操作符 AND OR NOT
3.?用WHERE限制選擇行(2)
比較順序(可以用括號(hào)改變它們的順序)
(1).?=?<?>?>=?<=?in?like?is?null?between
(2).?and
(3).?Or
注意:?char和varchar2的比較規(guī)則有不同:?
char比較時(shí)會(huì)忽略字符串后面的空格.?varchar2會(huì)計(jì)算字符串后面的空格
4.?LIKE操作
% 零到任意多個(gè)字符 _ 一個(gè)字符
例如:?字段名?like?'M%' 字段名?like?'%m%'?字段名?like?'job_'
如果要找含下劃線的字符,?要加反斜線?例如:字段名?like?'%X/_Y%'?escape?'/'
5.?日期字段的比較
舉例:
日期字段?between?to_date('2001-12-12','YYYY-MM-DD')?and?to_date('2002-02-01','YYYY-MM-DD')
日期字段>?to_date('2001-12-12','YYYY-MM-DD')?and日期字段<=?to_date('2002-02-01','YYYY-MM-DD');?
6.?不能用到索引的比較操作符
IS?NULL
IS?NOT?NULL
LIKE?'%m%'
三、單行函數(shù)
1.?數(shù)字函數(shù)
ABS?取絕對(duì)值 POWER?乘方?LN?10為底數(shù)取冪
SQRT?平方根 EXP?e的n次乘方?LOG(m,n)?m為底數(shù)n取冪? ?
數(shù)學(xué)運(yùn)算函數(shù):ACOS?ATAN?ATAN2?COS?COSH?SIGN?SIN?SINH?TAN?TANH?
CEIL? ?大于或等于取整數(shù)
FLOOR? ?小于或等于取整數(shù)
MOD? ?取余數(shù)
ROUND(n,m)?按m的位數(shù)取四舍五入值如果round(日期):?中午12以后將是明天的日期.?round(sysdate,'Y')是年的第一天
TRUNC(n,m)?按m的位數(shù)取前面的數(shù)值如果trunc(日期),?確省的是去掉時(shí)間
2.?字符函數(shù)
CHR? 按數(shù)據(jù)庫的字符集由數(shù)字返回字符?
CONCAT(c1,c2)? 把兩個(gè)字符c1,c2組合成一個(gè)字符,?和?||?相同
REPLACE(c,s,r)? 把字符c里出現(xiàn)s的字符替換成r,?返回新字符?
SUBSTR(c,m,n) m大于0,字符c從前面m處開始取n位字符,m等于0和1一樣,?
m小與0,字符c從后面m處開始取n位字符
TRANSLATE(c,f1,t1)? 字符c按f1到t1的規(guī)則轉(zhuǎn)換成新的字符串
INITCAP? 字符首字母大寫,其它字符小寫
LOWER? 字符全部小寫
UPPER 字符全部大寫
LTRIM(c1,c2) 去掉字符c1左邊出現(xiàn)的字符c2
RTRIM(c1,c2)?
TRIM(c1,c2)? 去掉字符c1左右兩邊的字符c2
LPAD(c1,n,c2)? 字符c1按制定的位數(shù)n顯示不足的位數(shù)用c2字符串替換左邊的空位
RPAD(c1,n,c2)
3.?日期函數(shù)
ADD_MONTHS(d,n) 日期值加n月
LAST_DAY(d)? 返回當(dāng)月的最后一天的日期
MONTHS_BETWEEN(d1,d2)? 兩個(gè)日期值間的月份,d1<d2?返回負(fù)數(shù)
NEXT_DAY(d)? 返回日期值下一天的日期
SYSDATE? 當(dāng)前的系統(tǒng)時(shí)間
DUAL是SYS用戶下一個(gè)空表，它只有一個(gè)字段dummy
4.?轉(zhuǎn)換函數(shù)(1)
TO_CHAR(date,'日期顯示格式')?
TO_CHAR(number)? 用于顯示或報(bào)表的格式對(duì)齊
TO_DATE(char,'日期顯示格式')?
TO_LOB ? 把long字段轉(zhuǎn)換成lob字段
TO_NUMBER(char) 用于計(jì)算或者比較大小?
4.?轉(zhuǎn)換函數(shù)(2)
to_date里日期顯示格式
YYYY 年 YEAR YYY YY Y
Q 季度
MM 月 MONTH MON
W 星期?(week?of?month) WW,?IW?(week?of?year)?
(說明：周計(jì)是按ISO標(biāo)準(zhǔn),從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)?
DD 日 DAY DY
HH24 小時(shí) HH12 HH
MI 分鐘
SS 秒
如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參數(shù) NLS_DATE_FORMAT=yyyy-mm-dd?hh24:mi:ss可以在UNIX環(huán)境變量或者NT的注冊(cè)表里的設(shè)置 NLS_DATE_FORMAT=yyyy-mm-dd?hh24:mi:ss
4.?轉(zhuǎn)換函數(shù)(3)
如果想固定日期的顯示格式可以用alter?session命令改變
SQL>?alter?session?set?nls_date_format='yyyy-mm-dd?hh24:mi:ss';
它的作用順序如下:
initialization?parameter
Environment?variable
ALTER?SESSION?command
4.?轉(zhuǎn)換函數(shù)?(4)
to_char(number)里數(shù)字顯示格式
9 數(shù)字位
0 數(shù)字前面補(bǔ)0? to_char(-1200,'00000.00')
. 小數(shù)點(diǎn)的位置
, 標(biāo)記位置的逗號(hào) 用在數(shù)字顯示格式的左邊
L 根據(jù)數(shù)據(jù)庫字符集加貨幣符號(hào) to_char(-1200,'L9999.99')
B 把數(shù)字0顯示為空格,用在數(shù)字顯示格式的右邊
MI 右邊的負(fù)數(shù)標(biāo)記? ? to_char(-1200,'9999.99MI')
PR? 括起來的負(fù)數(shù) ? to_char(-1200,'9999.99PR')
EEEE 用指數(shù)方式表示 ? to_char(-1200,'9999.99EEEE')
5.?輸入字符,返回?cái)?shù)字的函數(shù)
instr(c1,c2) 字符c2出現(xiàn)在c1的位置,?不出現(xiàn),?返回0,?常用于模糊查詢
length? 按數(shù)據(jù)庫的字符集,算出字符c的長(zhǎng)度,跟數(shù)據(jù)庫的字符集有關(guān),?一個(gè)漢字長(zhǎng)度為1
6.?有邏輯比較的函數(shù)NVL(EXPR1,?EXPR2)函數(shù)
解釋: IF?EXPR1=NULL? RETURN?EXPR2
ELSE? RETURN?EXPR1
DECODE(AA，V1，R1，V2，R2....)函數(shù)
解釋:?IF?AA=V1?THEN?RETURN?R1
?IF?AA=V2?THEN?RETURN?R2
..…
?ELSE
?RETURN?NULL
舉例:?decode(id,1,'dept?sale',2,'dept?tech')
四、從多個(gè)表里選取數(shù)據(jù)記錄
1.?數(shù)據(jù)表間的連接
簡(jiǎn)單的連接語法:
SELECT?字段名1,?字段名2,?……?FROM?表名1,?[表名2,?……]?
WHERE?表名1.字段名?=?表名2.?字段名?[?AND?……]?;?
SELECT?字段名1,?字段名2,?……?FROM?表名1,?[表名2,?……]
? WHERE?表名1.字段名?=?表名2.?字段名(+)?[?AND?……]?;?
有(+)號(hào)的字段位置自動(dòng)補(bǔ)空值?
連接的分類:
等于的連接 ?=
不等于的連接 != BETWEEN?…?AND?… IN 注意IN和OR不能一起用
外連接 有一個(gè)字段名(+)?,?沒有滿足的條件補(bǔ)空值
自連接 同一個(gè)表自己跟自己連接 例如找重復(fù)記錄
2.?數(shù)據(jù)表間的連接例子
刪除table_name表里字段名email重復(fù)的記錄:
SQL>delete?from?table_name?t1
where?t1.rowid?>
(select?min(rowid)?from?table_name?t2
where?t1.email?=?t2.email
group?by?email?
having?count(email)?>?1);
找到手機(jī)用戶的服務(wù)區(qū)域:
SQL>?select?a.handphoneno,nvl(c.name,'null'),a.totalscore?
?from?topscore?a,chargeoperator?cc,chargeoperatorinfo?c?
?where?substr(a.handphoneno,1,7)=cc.hpnohead(+)?
?and?cc.chargetype=c.chargetype(+)
?order?by?a.totalscore?desc;
3.?數(shù)據(jù)表間的連接技巧
連接N個(gè)表,?需要N-1個(gè)連接操作
被連接的表最好建一個(gè)單字符的別名,?字段名前加上這個(gè)單字符的別名
BETWEEN?..?AND..?比用?>=?AND?<=?要好
連接操作的字段名上最好要有索引
連接操作的字段最好用整數(shù)數(shù)字類型
有外連接時(shí),?不能用OR或IN的比較操作
4.?如何分析和執(zhí)行SQL語句
寫多表連接SQL語句時(shí)要知道它的分析執(zhí)行計(jì)劃的情況.?
Sys用戶下運(yùn)行@/ORACLE_HOME/sqlplus/admin/plustrce.sql?
產(chǎn)生plustrace角色
Sys用戶下把此角色賦予一般用戶?SQL>?grant?plustrace?to?&username;
一般用戶下運(yùn)行@/ORACLE_HOME/rdbms/admin/utlxplan.sql?
產(chǎn)生plan_table
SQL>?set?time?on; 說明：打開時(shí)間顯示?
SQL>?set?autotrace?on;? 說明：打開自動(dòng)分析統(tǒng)計(jì)，并顯示SQL語句的運(yùn)行結(jié)果
SQL>?set?autotrace?traceonly; 說明：打開自動(dòng)分析統(tǒng)計(jì)，不顯示SQL語句的運(yùn)行結(jié)果
接下來你就運(yùn)行測(cè)試SQL語句，看到其分析統(tǒng)計(jì)結(jié)果了。
一般來講，我們的SQL語句應(yīng)該避免大表的全表掃描。
SQL>?set?autotrace?off;? 說明：關(guān)閉自動(dòng)分析統(tǒng)計(jì)
五、集合函數(shù) 經(jīng)常和group?by一起使用?
1.?集合函數(shù)列表
AVG? (DISTINCT?|?ALL?|?N) 取平均值
COUNT (DISTINCT?|?ALL?|?N?|?expr?|?*?) 統(tǒng)計(jì)數(shù)量
MAX (DISTINCT?|?ALL?|?N) 取最大值
MIN? (DISTINCT?|?ALL?|?N) 取最小值
SUM? (DISTINCT?|?ALL?|?N) 取合計(jì)值
STDDEV? (DISTINCT?|?ALL?|?N) 取偏差值,如果組里選擇的內(nèi)容都相同,結(jié)果為0
VARIANCE? (DISTINCT?|?ALL?|?N) ? 取平方偏差值
2.?使用集合函數(shù)的語法
SELECT?column,?group_function?FROM?table
WHERE?condition GROUP?BY?group_by_expression
HAVING?group_condition ORDER?BY?column;
3.?使用count時(shí)的注意事項(xiàng)
SELECT?COUNT(*)?FROM?table;
SELECT?COUNT(常量)?FROM?table;
都是統(tǒng)計(jì)表中記錄數(shù)量,如果沒有PK后者要好一些
SELECT?COUNT(all?字段名)?FROM?table;
SELECT?COUNT(字段名)?FROM?table;
不會(huì)統(tǒng)計(jì)為NULL的字段的數(shù)量
SUM,AVG時(shí)都會(huì)忽略為NULL的字段
4.?用group?by時(shí)的限制條件
SELECT字段名不能隨意,?要包含在GROUP?BY的字段里
?GROUP?BY后ORDER?BY時(shí)不能用位置符號(hào)和別名
?限制GROUP?BY的顯示結(jié)果,?用HAVING條件
?
5.?例子
SQL>?select?title,sum(salary)?payroll?from?s_emp
where?title?like?'VP%' group?by?title
having?sum(salary)>5000 order?by?sum(salary)?desc;
找出某表里字段重復(fù)的記錄數(shù),?并顯示
SQL>?select?(duplicate?field?names)?from?table_name
?group?by?(list?out?fields)?having?count(*)>1;
6.?判斷題(T/F)
(1)?Group?functions?include?nulls?in?calculations [F]
(2)?Using?the?having?clause?to?exclude?rows?from?a?group?calculation [F]
解釋:
Group?function?都是忽略NULL值的 如果您要計(jì)算NULL值,?用NVL函數(shù)
Where語句在Group?By前把結(jié)果集排除在外Having語句在Group?By后把結(jié)果集排除在外
7.?在SQL*PLUS里可使用的其它命令:
Ctrl^C 終止正在運(yùn)行的SQL語句
remark?/*...*/?-- 注釋符號(hào)
HOST? 可執(zhí)行的操作系統(tǒng)下的命令 有些unix可以用?!
BREAK?ON?column_name?SKIP?n?[?ON?column_name?SKIP?n?]?
?按字段的名稱column_name分隔顯示,更清晰,SKIP?n?是在分隔處空行的數(shù)量n
?BREAK?ON?ROW?SKIP?n 每一行間隔都放n個(gè)空行
COMPUTE?集合運(yùn)算符?OF?字段1?ON?字段2 按字段2對(duì)字段1進(jìn)行集合運(yùn)算
COMPUTE后面可以跟的集合運(yùn)算符:
SUM?MINIMUM?MAXIMUM?AVG?STD?VARIANCE?COUNT?NUMBER
8.在SQL*PLUS里可使用的其它命令舉例:(scott用戶)
?
?BREAK?ON?REPORT
?COMPUTE?SUM?LABEL?TOTAL?OF?SAL?ON?REPORT 在全部結(jié)果集后面算合計(jì)
?select?ename,sal?from?emp?where?job='SALESMAN';
?
?COMPUTE?AVG?LABEL?avg?OF?SAL?ON?REPORT 在全部結(jié)果集后面算平均值
?/ 再次執(zhí)行上次的sql語句
?
?break?on?DEPTNO?skip?2?on?JOB?skip?1? 在BREAK字段結(jié)果集后面算合計(jì)
COMPUTE?SUM?OF?SAL?ON?DEPTNO
?SELECT?DEPTNO,JOB,ENAME,SAL?FROM?EMP?ORDER?BY?DEPTNO,JOB;
?
SQL>?CLEAR?BREAKS; 清除設(shè)置的BREAK條件
SQL>?CLEAR?COMPUTES; 清除設(shè)置的COMPUTE條件
六、子查詢
1.?查詢語句可以嵌套?
例如:?SELECT?……?FROM?(SELECT?……?FROM表名1,?[表名2,?……]?WHERE?條件)?WHERE?條件2;?
2.?何處可用子查詢?
當(dāng)查詢條件是不確定的條件時(shí)
DML(insert,?update,delete)語句里也可用子查詢
HAVING里也可用子查詢
3.?兩個(gè)查詢語句的結(jié)果可以做集合操作?
例如:?
并集UNION(去掉重復(fù)記錄)
并集UNION?ALL(不去掉重復(fù)記錄)?
差集MINUS,? ?
交集INTERSECT?
4.?子查詢的注意事項(xiàng)
先執(zhí)行括號(hào)里面的SQL語句，一層層到外面
內(nèi)部查詢只執(zhí)行一次
如果里層的結(jié)果集返回多個(gè)，不能用=?>?<?>=?<=等比較符要用IN.
5.?子查詢的例子(1)
SQL>?select?title,avg(salary)?from?s_emp
? group?by?title? Having?avg(salary)?=
? (select?min(avg(salary))?from?s_emp
? group?by?title);
找到最低平均工資的職位名稱和工資
5.?子查詢的例子(2)
子查詢可以用父查詢里的表名
這條SQL語句是對(duì)的:
SQL>select?cty_name?from?city?where?st_code?in
(select?st_code?from?state?where?st_name='TENNESSEE'?and?
?city.cnt_code=state.cnt_code);
說明：父查詢調(diào)用子查詢只執(zhí)行一次．
6.取出結(jié)果集的80?到100的SQL語句
ORACLE處理每個(gè)結(jié)果集只有一個(gè)ROWNUM字段標(biāo)明它的邏輯位置,
并且只能?用ROWNUM<100,?不能用ROWNUM>80。
以下是經(jīng)過分析后較好的兩種ORACLE取得結(jié)果集80到100間的SQL語句(?ID是唯一關(guān)鍵字的字段名?)：?
語句寫法：?
SQL>select?*?from?(
(?select?rownum?as?numrow,?c.*?from?(
select?[field_name,...]?from?table_name where?條件1?order?by?條件2)?c)
?where?numrow?>?80?and?numrow?<=?100?)
?order?by?條件3;?
七、在執(zhí)行SQL語句時(shí)綁定變量
1.?接收和定義變量的SQL*PLUS命令
ACCEPT
DEFINE UNDEFINE
&
2.?綁定變量SQL語句的例子(1)
SQL>?select?id,?last_name,?salary?from s_emp?where?dept_id?=?&department_number;
Enter?value?for?department_number:?10
old?1:?select?id,?last_name,?salary?from?s_emp?where?dept_id=&department_number;
new?1:?select?id,?last_name,?salary?from?s_emp?where?dept_id=?10
SQL>?SET?VERIFY?OFF?|?ON;可以關(guān)閉和打開提示確認(rèn)信息old?1和new?1的顯示.
3.?綁定變量SQL語句的例子(2)
SQL>?select?id,?last_name,?salary
? from s_emp
? ?where ?title?=?'&job_title';
Enter?value?for?job_title:?Stock?Clerk
SQL>?select?id,?last_name,?salary
? from s_emp
? ?where ?hiredate?>to_date(?'&start_hire_date','YYYY-MM-DD');
Enter?value?for?start_hire_date?:?2001-01-01
把綁定字符串和日期類型變量時(shí)，變量外面要加單引號(hào)
也可綁定變量來查詢不同的字段名
輸入變量值的時(shí)候不要加;等其它符號(hào)
4.?ACCEPT的語法和例子
SQL>?ACCEPT?variable?[datatype]?[FORMAT]?[PROMPT?text]?[HIDE]
說明： variable 指變量名 datatype 指變量類型，如number,char等 format 指變量顯示格式 prompt?text 可自定義彈出提示符的內(nèi)容text hide 隱藏用戶的輸入符號(hào)
使用ACCEPT的例子：
ACCEPT?p_dname?PROMPT?'Provide?the?department?name:?'
ACCEPT?p_salary?NUMBER?PROMPT?'Salary?amount:?'
ACCEPT?pswd?CHAR?PROMPT?'Password:?'?HIDE
ACCEPT?low_date?date?format?'YYYY-MM-DD'?PROMPT“Enter?the?low?date?range('YYYY-MM-DD'):”
4.?DEFINE的語法和例子
SQL>?DEFINE?variable?=?value
說明： variable 指變量名 value 指變量值
定義好了變良值后,?執(zhí)行綁定變量的SQL語句時(shí)不再提示輸入變量
使用DEFINE的例子：
SQL>?DEFINE?dname?=?sales
SQL>?DEFINE?dname
DEFINE?dname?=?“sales”?(CHAR)
SQL>?select?name?from?dept?where?lower(name)='&dname';
NAME
-------------------------
sales
sales
SQL>?UNDEFINE?dname
SQL>?DEFINE?dname
Symbol?dname?is?UNDEFINED
5.?SQL*PLUS里傳遞參數(shù)到保存好的*.sql文件里
SQL>?@?/路徑名/文件名?參數(shù)名1[,參數(shù)名2,?….]
SQL>?start?/路徑名/文件名?參數(shù)名1[,參數(shù)名2,?….]
注意事項(xiàng):
一次最多只能獲取9個(gè)&變量,?變量名稱只能是從&1,&2到&9
變量名后不要加特殊的結(jié)束符號(hào)
如果在SQL*PLUS里要把&符號(hào)保存在ORACLE數(shù)據(jù)庫里,要修改sql*plus環(huán)境變量define
SQL>?set?define?off;
八、概述數(shù)據(jù)模型和數(shù)據(jù)庫設(shè)計(jì)
1.?系統(tǒng)開發(fā)的階段:
Strategy?and?Analysis
Design
Build?and?Document
Transition
Production
2.?數(shù)據(jù)模型
Model?of?system?in?client's?mind
Entity?model?of?client's?model
Table?model?of?entity?model
Tables?on?disk
3.?實(shí)體關(guān)系模型?(ERM)概念
ERM?(?entity?relationship?modeling)
實(shí)體 存有特定信息的目標(biāo)和事件 ?例如:?客戶,訂單等
屬性 描述實(shí)體的屬性 ?例如:?姓名,電話號(hào)碼等
關(guān)系 兩個(gè)實(shí)體間的關(guān)系 ?例如:訂單和產(chǎn)品等
實(shí)體關(guān)系模型圖表里的約定
Dashed?line (虛線) 可選參數(shù)?“may?be”
Solid?line (實(shí)線) 必選參數(shù)?“must?be”
Crow's?foot (多線) 程度參數(shù)?“one?or?more”
Single?line (單線) 程度參數(shù)?“one?and?only?one”
4.?實(shí)體關(guān)系模型例子
每個(gè)訂單都必須有一個(gè)或幾個(gè)客戶
每個(gè)客戶可能是一個(gè)或幾個(gè)訂單的申請(qǐng)者
5.?實(shí)體關(guān)系的類型
1:1 一對(duì)一 例如:?的士和司機(jī)?
M:1 多對(duì)一 例如:?乘客和飛機(jī)
1:M 一對(duì)多 例如:?員工和技能
6.?校正實(shí)體關(guān)系的原則
屬性是單一值的,?不會(huì)有重復(fù)
屬性必須依存于實(shí)體,?要有唯一標(biāo)記
沒有非唯一屬性依賴于另一個(gè)非唯一的屬性
7.?定義結(jié)構(gòu)時(shí)的注意事項(xiàng)
減少數(shù)據(jù)冗余
減少完整性約束產(chǎn)生的問題
確認(rèn)省略的實(shí)體,關(guān)系和屬性
8.?完整性約束的要求
Primary?key 主關(guān)鍵字 唯一非NULL
Foreign?key 外鍵 依賴于另一個(gè)Primary?key,可能為NULL
Column 字段名 符合定義的類型和長(zhǎng)度
Constraint 約束條件 用戶自定義的約束條件,要符合工作流要求
例如:?一個(gè)銷售人員的提成不能超過它的基本工資
Candidate?key 候選主關(guān)鍵字 多個(gè)字段名可組成候選主關(guān)鍵字,?其組合是唯一和非NULL的
9.?把實(shí)體關(guān)系圖映射到關(guān)系數(shù)據(jù)庫對(duì)象的方法
把簡(jiǎn)單實(shí)體映射到數(shù)據(jù)庫里的表
把屬性映射到數(shù)據(jù)庫里的表的字段,?標(biāo)明類型和注釋
把唯一標(biāo)記映射到數(shù)據(jù)庫里的唯一關(guān)鍵字
把實(shí)體間的關(guān)系映射到數(shù)據(jù)庫里的外鍵
其它的考慮:
設(shè)計(jì)索引,使查詢更快
建立視圖,使信息有不同的呈現(xiàn)面,?減少復(fù)雜的SQL語句
計(jì)劃存儲(chǔ)空間的分配
重新定義完整性約束條件
10.?實(shí)體關(guān)系圖里符號(hào)的含義
PK 唯一關(guān)鍵字的字段
FK 外鍵的字段
FK1,FK2 同一個(gè)表的兩個(gè)不同的外鍵
FK1,FK1 兩個(gè)字段共同組成一個(gè)外鍵
NN 非null字段
U 唯一字段
U1,U1 兩個(gè)字段共同組成一個(gè)唯一字段
11.DELPHI中用TStoredProc使用包中的存儲(chǔ)過程
?? sp1: TStoredProc;
begin
? sp1.UnPrepare;
? sp1.StoredProcName := 'CMES.MYPACKAGE.MYPROCEDURE';
? sp1.Params.Clear;
? sp1.Params.CreateParam(ftString?,'IN_VARIABLE',ptInput?);
?? sp1.Params.CreateParam(ftString?,'RES',ptOutput??);
? sp1.Prepare;
? sp1.Params[0].AsString := 'in';
? sp1.ExecProc;
//? sp1.GetResults;
? showmessage(sp1.Parambyname('res').AsString);
end;
12.使用ADO返回?cái)?shù)據(jù)集
var
AResult , BResult : integer;
begin
ADODataSet1.Close ;
ADODataSet1.CommandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法調(diào)用oracle過程時(shí)，參數(shù)必須由?來傳， 即使你要傳的參數(shù)為常理
//輸出游標(biāo)的參數(shù)不需要指定!!!!!!，本來此函數(shù)帶三個(gè)參數(shù)，我們這里只需要傳兩個(gè)參數(shù).
ADODataSet1.CommandText := '{call PKG_JCCTEST1.GetSubAndSum2(?,?)}' ;
//***C 順序有關(guān)，createparam必須放在commandtext賦值語句之后.
// 創(chuàng)建第一個(gè)參數(shù)，對(duì)應(yīng)call中的第一個(gè)?,ftinteger為類型,10為長(zhǎng)度，45為傳入的實(shí)參值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,45);
//創(chuàng)建第二個(gè)參數(shù)，根據(jù)createparameter的順序 自動(dòng)與call中的第二個(gè)參數(shù)對(duì)應(yīng)
ADODataSet1.Parameters.CreateParameter('p2',ftinteger,pdinput,10,4);
//下面調(diào)用ADODataSet1 的open方法，返回?cái)?shù)據(jù)集(對(duì)應(yīng)包過程的游標(biāo))
ADODataSet1.Open ;
//根據(jù)存儲(chǔ)過程，數(shù)據(jù)集只有一條記錄，所以不需要用while do 來遍歷數(shù)據(jù)集，直接取數(shù)據(jù)了
//此處的字段名根據(jù)包過程中的返回游標(biāo) 對(duì)應(yīng)的字段名來取
//定義的存儲(chǔ)過程返回游標(biāo)如： open ResultCursor for
// select p1-p2 as "sum", p1+p2 as "sub" from dual;
//把對(duì)應(yīng)的字段值取出來即可
AResult := ADODataSet1.Fields.FieldByName('sub').Value ;
BResult := ADODataSet1.Fields.FieldByName('sum').Value ;
//顯示結(jié)果
showmessage(inttostr(AResult)) ;
showmessage(inttostr(BResult)) ;
end;
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
