ORACLE數(shù)據(jù)庫基礎(chǔ)知識

一 選擇行 1 簡單的 SELECT 語句 SELECT 字段名 1 AS 字段名 1 解釋 FROM table 2 處理 NULL NVL 函數(shù)可把 NULL 轉(zhuǎn)換成其它類型的符號 編程技巧 NVL 函數(shù)在多條件模糊查詢的時候比較有用 NVL 函數(shù)可返回多種數(shù)據(jù)類型 返回日期 NVL start date 2002 02 01 返回字符串 NVL title no title 返回數(shù)字 NVL salary 1000 3 使用 SQL PLUS 1 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 默認值是 14 SQL set linesize 100 設(shè)定 SQL 語句返回結(jié)果集一行的寬度 100 默認值是 80 4 SQL PLUS 里規(guī)定字段的顯示格式 規(guī)定數(shù)字的顯示格式 SQL column 字段名 format 99999999999 SQL column 字段名 format 999 999 999 999 SQL column 字段名 format a 數(shù)字 word wrapped 規(guī)定 long 字符的顯示寬度 SQL set long 200 規(guī)定字段名的顯示內(nèi)容 SQL column 字段名 heading 字段名顯示內(nèi)容 SQL set heading off 查詢時不顯示字段名稱 規(guī)定字段的對齊方向 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 5 J2EE zxw SQL PLUS 命令只控制 SELECT 結(jié)果集的顯示格式及控制文件 只有 SQL 命令能訪 問數(shù)據(jù)庫 二 限制選擇行 1 按指定的規(guī)則排序 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 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 取絕對值 POWER 乘方 LN 10 為底數(shù)取 0 SQRT 平方根 EXP e 的 n 次乘方 LOG m n m 為底數(shù) n 取 0 數(shù)學(xué)運算函數(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ù)點后的數(shù)值如果 trunc 日期 確省的是去掉時 間 6 J2EE zxw 2 字符函數(shù) CHR 按數(shù)據(jù)庫的字符集由數(shù)字返回字符 CONCAT c1 c2 把兩個字符 c1 c2 組合成一個字符 和 相同 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 返回當月的最后一天的日期 MONTHS BETWEEN d1 d2 兩個日期值間的月份 d1 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ù)字前面補 0 to char 1200 00000 00 小數(shù)點的位置 標記位置的逗號 用在數(shù)字顯示格式的左邊 L 根據(jù)數(shù)據(jù)庫字符集加貨幣符號 to char 1200 L9999 99 B 把數(shù)字 0 顯示為空格 用在數(shù)字顯示格式的右邊 MI 右邊的負數(shù)標記 to char 1200 9999 99MI PR 括起來的負數(shù) to char 1200 9999 99PR EEEE 用指數(shù)方式表示 to char 1200 9999 99EEEE 5 輸入字符 返回數(shù)字的函數(shù) instr c1 c2 字符 c2 出現(xiàn)在 c1 的位置 不出現(xiàn) 返回 0 常用于模糊查詢 length 按數(shù)據(jù)庫的字符集 算出字符 c 的長度 跟數(shù)據(jù)庫的字符集有關(guān) 一個 漢字長度為 1 6 有邏輯比較的函數(shù) NVL EXPR1 EXPR2 函數(shù) 解釋 IF EXPR1 NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE AA0V10R10V20R2 函數(shù) 解釋 IF AA V1 THEN RETURN R1 IF AA V2 THEN RETURN R2 ELSE RETURN NULL 舉例 decode id 1 dept sale 2 dept tech 四 從多個表里選取數(shù)據(jù)記錄 1 數(shù)據(jù)表間的連接 簡單的連接語法 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 不能一起用 8 J2EE zxw 外連接 有一個字段名 沒有滿足的條件補空值 自連接 同一個表自己跟自己連接 例如找重復(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 找到手機用戶的服務(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 個表 需要 N 1 個連接操作 被連接的表最好建一個單字符的別名 字段名前加上這個單字符的別名 BETWEEN AND 比用 AND grant plustrace to 一般用戶下運行 ORACLE HOME rdbms admin utlxplan sql 產(chǎn)生 plan table SQL set time on 說明 打開時間顯示 SQL set autotrace on 說明 打開自動分析統(tǒng)計 并顯示 SQL 語句的運行結(jié) 果 SQL set autotrace traceonly 說明 打開自動分析統(tǒng)計 不顯示 SQL 語句 的運行結(jié)果 接下來你就運行測試 SQL 語句 看到其分析統(tǒng)計結(jié)果了 一般來講 我們的 SQL 語句應(yīng)該避免大表的全表掃描 SQL set autotrace off 說明 關(guān)閉自動分析統(tǒng)計 五 集合函數(shù) 經(jīng)常和 group by 一起使用 1 集合函數(shù)列表 AVG DISTINCT ALL N 取平均值 COUNT DISTINCT ALL N expr 統(tǒng)計數(shù)量 MAX DISTINCT ALL N 取最大值 MIN DISTINCT ALL N 取最小值 SUM DISTINCT ALL N 取合計值 9 J2EE zxw 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 時的注意事項 SELECT COUNT FROM table SELECT COUNT 常量 FROM table 都是統(tǒng)計表中記錄數(shù)量 如果沒有 PK 后者要好一些 SELECT COUNT all 字段名 FROM table SELECT COUNT 字段名 FROM table 不會統(tǒng)計為 NULL 的字段的數(shù)量 SUM AVG 時都會忽略為 NULL 的字段 4 用 group by 時的限制條件 SELECT 字段名不能隨意 要包含在 GROUP BY 的字段里 GROUP BY 后 ORDER BY 時不能用位置符號和別名 限制 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 值的 如果您要計算 NULL 值 用 NVL 函數(shù) Where 語句在 Group By 前把結(jié)果集排除在外 Having 語句在 Group By 后把結(jié)果 集排除在外 六 子查詢 1 查詢語句可以嵌套 例如 SELECT FROM SELECT FROM 表名 1 表名 2 WHERE 條件 WHERE 條件 2 2 何處可用子查詢 當查詢條件是不確定的條件時 DML insert update delete 語句里也可用子查詢 HAVING 里也可用子查詢 3 兩個查詢語句的結(jié)果可以做集合操作 例如 并集 UNION 去掉重復(fù)記錄 并集 UNION ALL 不去掉重復(fù)記錄 10 J2EE zxw 差集 MINUS 交集 INTERSECT 4 子查詢的注意事項 先執(zhí)行括號里面的 SQL 語句 一層層到外面 內(nèi)部查詢只執(zhí)行一次 如果里層的結(jié)果集返回多個 不能用 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 t code t code 說明 父查詢調(diào)用子查詢只執(zhí)行一次 6 取出結(jié)果集的 80 到 100 的 SQL 語句 ORACLE 處理每個結(jié)果集只有一個 ROWNUM 字段標明它的邏輯位置 并且只能 用 ROWNUM80 以下是經(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 select id last name salary from s emp where dept id Enter value for department number 10 old 1 select id last name salary from s emp where dept id new 1 select id last name salary from s emp where dept id 10 SQL SET VERIFY OFF ON 可以關(guān)閉和打開提示確認信息 old 1 和 new 1 的顯 示 3 綁定變量 SQL 語句的例子 2 SQL select id last name salary from s emp where title Enter value for job title Stock Clerk 11 J2EE zxw SQL select id last name salary from s emp where hiredate to date 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 可自定義彈出提示符的內(nèi)容 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 指變量值 定義好了變良值后 執(zhí)行綁定變量的 SQL 語句時不再提示輸入變量 使用 DEFINE 的例子 SQL DEFINE dname sales SQL DEFINE dname DEFINE dname sales CHAR SQL select name from dept where lower name 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 注意事項 一次最多只能獲取 9 個 八 概述數(shù)據(jù)模型和數(shù)據(jù)庫設(shè)計 1 系統(tǒng)開發(fā)的階段 Strategy and Analysis Design Build and Document Transition Production 12 J2EE zxw 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 實體關(guān)系模型 ERM 概念 ERM entity relationship modeling 實體 存有特定信息的目標和事件 例如 客戶 訂單等 屬性 描述實體的屬性 例如 姓名 電話號碼等 關(guān)系 兩個實體間的關(guān)系 例如 訂單和產(chǎn)品等 實體關(guān)系模型圖表里的約定 Dashed line 虛線 可選參數(shù) may be Solid line 實線 必選參數(shù) must be Crow s foot 多線 程度參數(shù) one or more Single line 單線 程度參數(shù) one and only one 4 實體關(guān)系模型例子 每個訂單都必須有一個或幾個客戶 每個客戶可能是一個或幾個訂單的申請者 5 實體關(guān)系的類型 1 1 一對一 例如 的士和司機 M 1 多對一 例如 乘客和飛機 1 M 一對多 例如 員工和技能 6 校正實體關(guān)系的原則 屬性是單一值的 不會有重復(fù) 屬性必須依存于實體 要有唯一標記 沒有非唯一屬性依賴于另一個非唯一的屬性 7 定義結(jié)構(gòu)時的注意事項 減少數(shù)據(jù)冗余 減少完整性約束產(chǎn)生的問題 確認省略的實體 關(guān)系和屬性 8 完整性約束的要求 Primary key 主關(guān)鍵字 唯一非 NULL Foreign key 外鍵 依賴于另一個 Primary key 可能為 NULL Column 字段名 符合定義的類型和長度 Constraint 約束條件 用戶自定義的約束條件 要符合工作流要求 例如 一個銷售人員的提成不能超過它的基本工資 Candidate key 候選主關(guān)鍵字 多個字段名可組成候選主關(guān)鍵字 其組合是唯一 和非 NULL 的 9 把實體關(guān)系圖映射到關(guān)系數(shù)據(jù)庫對象的方法 把簡單實體映射到數(shù)據(jù)庫里的表 把屬性映射到數(shù)據(jù)庫里的表的字段 標明類型和注釋 把唯一標記映射到數(shù)據(jù)庫里的唯一關(guān)鍵字 把實體間的關(guān)系映射到數(shù)據(jù)庫里的外鍵 13 J2EE zxw 其它的考慮 設(shè)計索引 使查詢更快 建立視圖 使信息有不同的呈現(xiàn)面 減少復(fù)雜的 SQL 語句 計劃存儲空間的分配 重新定義完整性約束條件 10 實體關(guān)系圖里符號的含義 PK 唯一關(guān)鍵字的字段 FK 外鍵的字段 FK1 FK2 同一個表的兩個不同的外鍵 FK1 FK1 兩個字段共同組成一個外鍵 NN 非 null 字段 U 唯一字段 U1 U1 兩個字段共同組成一個唯一字段 九 創(chuàng)建表 1 ORACLE 常用的字段類型 ORACLE 常用的字段類型有 VARCHAR2 size 可變長度的字符串 必須規(guī)定長度 CHAR size 固定長度的字符串 不規(guī)定長度默認值為 NUMBER p s 數(shù)字型 p 是位數(shù)總長度 s 是小數(shù)的長度 可存負數(shù) 最長 38 位 不夠位時會四舍五入 DATE 日期和時間類型 LOB 超長字符 最大可達 4G CLOB 超長文本字符串 BLOB 超長二進制字符串 BFILE 超長二進制字符串 保存在數(shù)據(jù)庫外的文件里是只讀的 數(shù)字字段類型位數(shù)及其四舍五入的結(jié)果 原始數(shù)值 1234567 89 數(shù)字字段類型位數(shù) 存儲的值 Number 1234567 89 Number 12345678 Number 錯 Number 9 1 1234567 9 Number 9 3 錯 Number 7 2 錯 Number 5 2 1234600 Number 5 4 1230000 Number 1 1234567 9 2 創(chuàng)建表時給字段加默認值 和約束條件 創(chuàng)建表時可以給字段加上默認值 例如 日期字段 DEFAULT SYSDATE 這樣每次插入和修改時 不用程序操作這個字段都能得到動作的時間 14 J2EE zxw 創(chuàng)建表時可以給字段加上約束條件 例如 非空 NOT NULL 不允許重復(fù) UNIQUE 關(guān)鍵字 PRIMARY KEY 按條件檢查 CHECK 條件 外鍵 REFERENCES 表名 字段名 3 創(chuàng)建表的例子 CREATE TABLE DEPT EPTNO NUMBER 2 CONSTRAINT PK DEPT PRIMARY KEY DNAME VARCHAR2 14 LOC VARCHAR2 13 CREATE TABLE region ID number 2 NOT NULL PRIMARY KEY postcode number default 0 NOT NULL areaname varchar2 30 default NOT NULL 4 創(chuàng)建表時的命名規(guī)則和注意事項 表名和字段名的命名規(guī)則 必須以字母開頭 可以含符號 A Z a z 0 9 大小寫不區(qū)分 不用 SQL 里的保留字 一定要用時可用雙引號把字符串括起來 用和實體或?qū)傩韵嚓P(guān)的英文符號長度有一定的限制 注意事項 建表時可以用中文的字段名 但最好還是用英文的字段名 創(chuàng)建表時要把較小的不為空的字段放在前面 可能為空的字段放在后面 建表時如果有唯一關(guān)鍵字或者唯一的約束條件 建表時自動建了索引 一個表的最多字段個數(shù)也是有限制的 254 個 5 約束名的命名規(guī)則和語法 約束名的命名規(guī)則約束名如果在建表的時候沒有指明 系統(tǒng)命名規(guī)則是 SYS Cn n 是數(shù)字 約束名字符串的命名規(guī)則同于表和字段名的命名規(guī)則 6 使用約束時的注意事項 約束里不能用系統(tǒng)函數(shù) 如 SYSDATE 和別的表的字段比較 可以用本表內(nèi)字段的比較 想在事務(wù)處理后 做約束的檢查 SQL alter session set constraints deferred 7 由實體關(guān)系圖到創(chuàng)建表的例子 s dept 前提條件 已有 region 表且含唯一關(guān)鍵字的字段 id SQL CREATE TABLE s dept id NUMBER 7 CONSTRAINT s dept id pk PRIMARY KEY name VARCHAR2 25 CONSTRAINT s dept name nn NOT NULL region id NUMBER 7 CONSTRAINT s dept region id fk REFERENCES region id CONSTRAINT s dept name region id uk UNIQUE name region id 8 較復(fù)雜的創(chuàng)建表例子 15 J2EE zxw SQL CREATE TABLE s emp id NUMBER 7 CONSTRAINT s emp id pk PRIMARY KEY last name VARCHAR2 25 CONSTRAINT s emp last name nn NOT NULL first name VARCHAR2 25 userid VARCHAR2 CONSTRAINT s emp userid nn NOT NULL CONSTRAINT s emp userid uk UNIQUE start date DATE DEFAULT SYSDATE comments VARCHAR2 25 manager id NUMBER 7 title VARCHAR2 25 dept id NUMBER 7 CONSTRAINT s emp dept id fk REFERENCES s dept id salary NUMBER 11 2 commission pct NUMBER 4 2 CONSTRAINT s emp commission pct ck CHECK commission pct IN 10 12 5 15 17 5 20 9 通過子查詢建表 通過子查詢建表的例子 SQL CREATE TABLE emp 41 AS SELECT id last name userid start date FROM s emp WHERE dept id 41 SQL CREATE TABLE A as select from B where 1 2 只要表的結(jié)構(gòu) 10 用子查詢建表的注意事項 可以關(guān)連多個表及用集合函數(shù)生成新表 注意選擇出來的字段必須有合法的字段 名稱 且不能重復(fù) 用子查詢方式建立的表 只有非空 NOT NULL 的約束條件能繼承過來 其它的約束條件和默認值都沒有繼承過來 根據(jù)需要 可以用 alter table add constraint 再建立其它的約束條件 如 primary key 等 11 Foreign Key 的可選參數(shù) ON DELETE CASCADE 在創(chuàng)建 Foreign Key 時可以加可選參數(shù) ON DELETE CASCADE 它的含義是如果刪除外鍵主表里的內(nèi)容 子表里相關(guān)的內(nèi) 容將一起被刪除 如果沒有 ON DELETE CASCADE 參數(shù) 子表里有內(nèi)容 父表里的主關(guān)鍵字記錄不 能被刪除掉 12 如果數(shù)據(jù)庫表里有不滿足的記錄存在 建立約束條件將不會成功 13 給表創(chuàng)建和刪除同義詞的例子 SQL CREATE SYNONYM d sum 2 FOR dept sum vu SQL CREATE PUBLIC SYNONYM s dept 2 FOR alice s dept SQL DROP SYNONYM s dept 十 ORACLE 里的數(shù)據(jù)字典 1 什么是數(shù)據(jù)字典 ORACLE 的數(shù)據(jù)字典是數(shù)據(jù)庫的重要組成部分之一 它隨著 數(shù)據(jù)庫 的產(chǎn)生而產(chǎn)生 隨著數(shù)據(jù)庫的變化而變化 體現(xiàn)為 sys 用戶下所有的一些表和 視圖 2 數(shù)據(jù)字典里存了以下內(nèi)容 用戶信息 用戶的權(quán)限信息 所有數(shù)據(jù)對象信息表的約束條件統(tǒng)計分析數(shù)據(jù)庫的視圖等 不能手工修改數(shù)據(jù)字典里的信息 16 J2EE zxw 3 常用的數(shù)據(jù)字典 Dictionary 存放所有數(shù)據(jù)表 視圖 同義詞名稱和解釋 Dict columns 數(shù)據(jù)字典里字段名稱的和解釋 Dba users 用戶 Dba tablespaces 表空間 Dba data files 數(shù)據(jù)庫的文件 Dba free space 空閑表空間 Dba rollback segs 回滾段 User objects 數(shù)據(jù)對象 User constraints 約束條件 User sequences 序列號 User views 視圖 User indexes 索引 User synonyms 同義詞 Session roles 用戶的角色 User role privs 用戶的角色權(quán)限 User sys privs 用戶的系統(tǒng)權(quán)限 User tab privs 用戶的表級權(quán)限 V session 實時用戶情況 V sysstat 實時系統(tǒng)統(tǒng)計 V sesstat 實時用戶統(tǒng)計 V sgastat 實時 SGA 使用 V locked object 實時鎖 V controlfile 控制文件 V logfile 日志文件 V parameter 參數(shù)文件 4 數(shù)據(jù)字典的分類 數(shù)據(jù)字典四大類別 User 用戶下所有數(shù)據(jù)庫對象 All 用戶權(quán)限范圍內(nèi)所有的數(shù)據(jù)庫對象 Dba 所有的數(shù)據(jù)庫對象 V Content nbsp 統(tǒng)計分析數(shù)據(jù)庫的視圖 賦于 oem monitor 權(quán)限非 DBA 用戶也 可查詢 V 視圖 5 查詢數(shù)據(jù)字典 SQL select from dictionary where instr comments index 0 SQL select constraint name constraint type 2 search condition r constraint name 3 from user constraints 4 where table name 十一 控制數(shù)據(jù) 1 INSERT 往數(shù)據(jù)表里插入記錄的語句 SQL insert into 表名 字段名 1 字段名 2 values 值 1 值 2 SQL insert into 表名 字段名 1 字段名 2 select 字段名 1 字段 名 2 from 另外的表名 where 條件 可以用 SQL commit 注意事項 用 INSERT APPEND 的方法會對 target tablename 產(chǎn)生級別為 6 的獨占 鎖 如果運行此命令時還有對 target tablename 的 DML 操作會排隊在它后面 對 OLTP 系統(tǒng)在用的表操作是不合適的 17 J2EE zxw 2 插入字符串類型的字段的注意事項 字符串類型的字段值必須用單引號括起來 例如 GOOD DAY 如果字段值里包含單引號 需要進行字符串轉(zhuǎn)換 我們把它替換成兩個 單引 號 字符串類型的字段值超過定義的長度會出錯 最好在插入前進行長度校驗 標記是 NULL user 標明當前用戶 日期字段的字段值可以用當前數(shù)據(jù)庫的系統(tǒng)時間 SYSDATE 精確到秒 用字符串轉(zhuǎn)換成日期型函數(shù) TO DATE 2001 08 01 YYYY MM DD TO DATE 還有很多種日期格式 可以參看 ORACLE DOC 年 月 日 小時 分鐘 秒 的格式 YYYY MM DD HH24 MI SS NSERT 時最大可操作的字符串長度小于等于 4000 個單字節(jié) 如果要插入更長的字符串 請考慮字段用 CLOB 類型 方法借用 ORACLE 里自帶的 DBMS LOB 程序包 3 UPDATE 修改數(shù)據(jù)表里記錄的語句 SQL UPDATE 表名 SET 字段名 1 值 1 字段名 2 值 2 WHERE 條件 如果修改的值 N 沒有賦值或定義時 將把原來的記錄內(nèi)容清為 NULL 最好在修改前進行非空校驗 值 N 超過定義的長度會出錯 最好在插入前進行長度校驗 新功能 可以修改子查詢后的結(jié)果集 例子 SQL update select from s dept set id 50 where id 60 4 DELETE 刪除數(shù)據(jù)表里記錄的語句 SQL DELETE FROM 表名 WHERE 條件 注意 刪除記錄并不能釋放 ORACLE 里被占用的數(shù)據(jù)塊表空間 它只把那些 被刪除的數(shù)據(jù)塊標成 unused 如果確實要刪除一個大表里的全部記錄 可以用 TRUNCATE 命令 它可以釋放 占用的數(shù)據(jù)塊表空間 SQL TRUNCATE TABLE 表名 此操作不可回退 5 SQL 語句的分類 數(shù)據(jù)定義語言 DDL create alter drop 創(chuàng)建 修改結(jié)構(gòu) 刪除 其他 rename 數(shù)據(jù)操縱語言 DML insert delete select update 增 刪 查 改 其他 truncate 數(shù)據(jù)控制語言 DCL grant revoke 授權(quán) 回收 set role 事務(wù)控制 commit rollback savepoint 其他 lock table set constraint set transaction 審計控制 audit noaudit 系統(tǒng)控制 alter system 會話控制 alter session 其他語句 comment 添加注釋 explain plan analyze validate call 6 ORACLE 里事務(wù)控制 Commit 提交事務(wù) Rollback 回退事務(wù) Savepoint 設(shè)置斷點 在事務(wù)中標記位置 事務(wù)結(jié)束 斷點釋放 事務(wù)結(jié)束的情況遇到 commit 或者 rollback 遇到 DDL 和 DCL 語句發(fā)現(xiàn)錯誤 如 死鎖用戶退出 SQL PLUS 系統(tǒng)重啟或崩潰 7 DML 操作的注意事項 18 J2EE zxw 以上 SQL 語句對表都加上了行級鎖 確認完成后 必須加上事物處理結(jié)束的命令 COMMIT 才能正式生效 否則改變不一定寫入數(shù)據(jù)庫里 行級鎖也未能得到釋放 如果想撤回這些操作 可以用命令 ROLLBACK 復(fù)原 在運行 INSERT DELETE 和 UPDATE 語句前最好估算一下可能操作的記錄范圍 應(yīng)該把它限定在較小 一萬條記錄 范圍內(nèi) 否則 ORACLE 處理這個事物用到 很大的回退段 程序響應(yīng)慢甚至失去響應(yīng) 如果記錄數(shù)上十萬以上這些操作 可以把這些 SQL 語句分段分次完成 其間加上 COMMIT 確認事物處理 太過頻繁的 commit 不好 十二 改變表和約束條件 1 改變表的幾種情況 1 運行時會加表級鎖 改變表的名稱 SQL RENAME 表名 1 TO 表名 2 SQL ALTER TABLE 表名 1 RENAME TO 表名 2 在表的后面增加一個字段 SQL ALTER TABLE 表名 ADD 字段名 字段名描述 DEFAULT expr NOT NULL 字段名 2 修改表里字段的定義描述 SQL ALTER TABLE 表名 MODIFY 字段名 1 字段名 1 描述 DEFAULT expr NOT NULL 字段名 2 記錄為空時 可以減少字段長度 改變字段類型修改 DEFAULT 值只作用于修改后的 INSERT 和 UPDATE 的記錄修改 NOT NULL 約束只對現(xiàn)存含非空記錄的字段起作用 1 改變表的幾種情況 2 運行時會加表級鎖 刪除表里的某個字段 SQL ALTER TABLE 表名 DROP 字段名 給表里的字段加上 禁止 啟用約束條件 SQL ALTER TABLE 表名 ADD DISABLE ENABLE CONSTRAINT 約束名 PRIMARY KEY 字段名 1 字段名 2 SQL ALTER TABLE 表名 ADD DISABLE ENABLE CONSTRAINT 約束名 UNIQUE 字段名 1 字段名 2 加唯一關(guān)鍵字或者唯一約束條件時自動建立索引 說明 禁止唯一關(guān)鍵字和唯一約束時索引仍然存在 可以被使用 1 改變表的幾種情況 3 運行時會加表級鎖 刪除表里的約束條件 SQL ALTER TABLE 表名 DROP CONSTRAINTS 約束名 CASCADE 會把約束相關(guān)的索引一起刪除 CASCADE 能同時刪去外鍵的約束條件 把表放在或取出數(shù)據(jù)庫的內(nèi)存區(qū) SQL ALTER TABLE 表名 CACHE SQL ALTER TABLE 表名 NOCACHE 改變表存儲的表空間 SQL ALTER TABLE 表名 MOVE TABLESPACE 表空間名 19 J2EE zxw 注意 如果被轉(zhuǎn)移表空間的表含有索引 表轉(zhuǎn)移后索引變得不可用 我們要刪除舊索引 建立新索引 2 刪除表及表里的數(shù)據(jù) 刪除表 SQL DROP TABLE 表名 CASCADE CONSTRAINTS 清空表里的記錄 SQL TRUNCATE TABLE 表名 按時間清空日志表里的記錄 使用重新命名的方法 應(yīng)用程序可能有短暫出錯 可以選擇在不繁忙的時間執(zhí)行 按原來表 A 的建表語句創(chuàng)建新表 A1 把表 A 重命名為 A2 如果表 A 上有較頻繁 的 DML 操作 會對表加上行級鎖 重命名過程用遞歸的方式循環(huán)做 直到 DML 操作結(jié)束 命 名成功 把創(chuàng)建新表 A1 重命名為 A 歷史記錄表 A2 備份或刪除 3 刪除表后應(yīng)該注意的問題 刪除表后把表里的索引一起刪去 刪除表后會結(jié)束基于它的懸而未決的事物 刪除表后根據(jù)表創(chuàng)建的 views synonym stored procedure stored function 依 然存在 但 views synonym 變成非法的 需要手工找出它們并刪除 如果用了 CASCADE CONSTRAINTS 會把與它相關(guān)的約束一起刪除 此操作不可回退 4 給表加注釋 加注釋的語法 SQL COMMENT ON TABLE 表名 COLUMN 表名 字段名 IS text 加注釋的例子 SQL comment on table s emp is Enployee information SQL comment on column s emp last name is 十三 創(chuàng)建序列號 1 創(chuàng)建序列號里各參數(shù)的解釋 SQL CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n NOMAXVALUE MINVALUE n NOMINVALUE CYCLE NOCYCLE CACHE n NOCACHE INCREMENT BY n 一次增長 n 個數(shù)字 NOMAXVALUE 缺省值 10E 27 NOMINVALUE 缺省值 1 NOCYCLE 不循環(huán) 常用于唯一關(guān)鍵字 CACHE n 在內(nèi)存里緩存 n 個序列 出錯回退時會丟失 oracle8i 里默認的 n 是 20 序列號的名稱一般可以采用 表名 字段名 的命名規(guī)則 2 插入自動增長序列號字段的方法 INSERT 時如果要用到從 1 開始自動增長的數(shù)字做唯一關(guān)鍵字 應(yīng)該先建立一個 序列號 20 J2EE zxw CREATE SEQUENCE 序列號的名稱 最好是表名 序列號標記 INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE NOCACHE 其中最大的值按字段的長度來定 比如定義的自動增長的序列 NUMBER 最大值 為 999999 INSERT 語句插入這個字段值為 序列號的名稱 NEXTVAL 例子 SQL insert into s dept id name region id values s dept id nextval finance 2 1 row created 只有運行了序列號的名稱 nextval 后序列號的名稱 currval 才有效才有值 3 查詢序列號的情況 SQL select sequence name min value max value increment by last number from user sequences 其中 last number 指的是序列號的下一個值 4 改變序列號 SQL ALTER SEQUENCE sequence INCREMENT BY n MAXVALUE n NOMAXVALUE MINVALUE n NOMINVALUE CYCLE NOCYCLE CACHE n NOCACHE 注意 不能改變它的起始值 如果要改變序列的起始值 先把序列號刪除掉 再新建一個 5 刪除序列號 SQL DROP SEQUENCE sequence 6 不能用序列號的 nextval 和 currval 的地方 視圖的查詢 有 distinct 的查詢 有 group by having order by 的查詢 有子查詢的查詢 表里的缺省值 十四 創(chuàng)建視圖 1 視圖的概念和優(yōu)點 視圖是基于一個或多個表及視圖的一些查詢語句 它象顯示數(shù)據(jù)的視窗 它本 身是不存儲數(shù)據(jù)的 視圖可以限制數(shù)據(jù)庫的訪問 更好的控制權(quán)限 使用戶使用簡單的查詢語句 數(shù)據(jù)的非依賴性 同一數(shù)據(jù)的不同表現(xiàn)形式 2 創(chuàng)建視圖的語法 SQL CREATE OR REPLACE FORCE NOFORCE VIEW view alias alias AS subquery WITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY 參數(shù)解釋 FORCE 表不存在時 視圖仍然可以創(chuàng)建成功 WITH CHECK OPTION 只有符合視圖定義的記錄才能被插入或修改 WITH READ ONLY 不允許 DML 操作 Oracle8i 以后創(chuàng)建視圖可以用 order by 21 J2EE zxw 3 創(chuàng)建修改視圖的例子 SQL CREATE OR REPLACE VIEW salvu41 AS SELECT id first name FIRST last name LAST salary MONTHLY SALARY FROM s emp WHERE dept id 41 SQL CREATE VIEW dept sum vu name minsal maxsal avgsal AS SELECT d name MIN e salary MAX e salary AVG e salary FROM s emp e s dept d WHERE e dept id d id GROUP BY d name 注意 如果用 select from table name 創(chuàng)建的視圖 table name 的結(jié)構(gòu)改變后 view 要重建或 compile 后才能顯示新的字段內(nèi)容 4 查詢視圖的數(shù)據(jù)字典 SQL set long 1600 SQL select view