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