ORACLE SQL*PLUS 2008-07-02 15:00
分類(lèi)：
oracle字號(hào)：
大大
中中 小
前言: 關(guān)系數(shù)據(jù)庫(kù)的簡(jiǎn)單介紹
1970.Dr.E.F.codded 創(chuàng)建了關(guān)系數(shù)據(jù)庫(kù)的模型（類(lèi)似現(xiàn)在常用的二維表格）
關(guān)系數(shù)據(jù)庫(kù)由許多數(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語(yǔ)言寫(xiě)的.
ORACLE的簡(jiǎn)單介紹
ORACLE公司是一家提供綜合技術(shù)產(chǎn)品、方案、服務(wù)的數(shù)據(jù)庫(kù)公司
ORACLE數(shù)據(jù)庫(kù)設(shè)計(jì)成可大量存儲(chǔ)數(shù)據(jù)，快速查詢數(shù)據(jù)，保證數(shù)據(jù)的安全和一致性，
跨網(wǎng)絡(luò)的分布式管理及客戶-服務(wù)器的配置等。
ORACLE SQL語(yǔ)法符合ANSI 1986標(biāo)準(zhǔn)
SQL 和數(shù)據(jù)庫(kù)交流的命令式語(yǔ)言
SQL*PLUS ORACLE的一種工具, 用來(lái)運(yùn)行SQL和PL/SQL語(yǔ)句
PL/SQL ORACLE的過(guò)程化編程語(yǔ)言
一、選擇行
1. 簡(jiǎn)單的SELECT 語(yǔ)句
SELECT [DISTINCT] {*,COLUMN [ALIAS], ……} FROM table_name;
字段的非唯一的結(jié)果集
DISTINCT 字段名1 [ ,字段名2]
數(shù)字類(lèi)型字段名之間可以進(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)換成其它類(lèi)型的符號(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ù)類(lèi)型:
返回日期 NVL(start_date,'2002-02-01')
返回字符串 NVL(title,'no title')
返回?cái)?shù)字 NVL(salary,1000)
3. 使用SQL*PLUS（1）
登錄SQL*PLUS的方法
用戶名，密碼，數(shù)據(jù)庫(kù)連接字符串
數(shù)據(jù)庫(kù)連接字符串可以用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”(回車(chē))后翻頁(yè)
SQL> set pagesize 100; 設(shè)定SQL語(yǔ)句返回結(jié)果集一頁(yè)的行數(shù)100, 默認(rèn)值是14
SQL> set linesize 100; 設(shè)定SQL語(yǔ)句返回結(jié)果集一行的寬度100, 默認(rèn)值是80
3. 使用SQL*PLUS（2）SQL*PLUS里的編輯命令
最近一條SQL命令語(yǔ)句存在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 打開(kāi)編輯窗口，編輯sql buffer里的SQL命令
EDIT filename 打開(kāi)編輯窗口，編輯文件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];
說(shuō)明: 一行只顯示數(shù)字位的長(zhǎng)度, 超過(guò)長(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';
說(shuō)明：如果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命令能訪問(wèn)數(shù)據(jù)庫(kù).
二、限制選擇行
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ù)庫(kù)的字符集由數(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處開(kāi)始取n位字符,m等于0和1一樣,
m小與0,字符c從后面m處開(kāi)始取n位字符
TRANSLATE(c,f1,t1) 字符c按f1到t1的規(guī)則轉(zhuǎn)換成新的字符串
INITCAP 字符首字母大寫(xiě),其它字符小寫(xiě)
LOWER 字符全部小寫(xiě)
UPPER 字符全部大寫(xiě)
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)
(說(shuō)明：周計(jì)是按ISO標(biāo)準(zhǔn),從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)
DD 日 DAY DY
HH24 小時(shí) HH12 HH
MI 分鐘
SS 秒
如果想固定日期的顯示格式可以在數(shù)據(jù)庫(kù)的參數(shù)文件initorasid.ora里新寫(xiě)一行參數(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ù)庫(kù)字符集加貨幣符號(hào) to_char(-1200,'L9999.99')
B 把數(shù)字0顯示為空格,用在數(shù)字顯示格式的右邊
MI 右邊的負(fù)數(shù)標(biāo)記 to_char(-1200,'9999.99MI')
PR 括起來(lái)的負(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(c) 按數(shù)據(jù)庫(kù)的字符集,算出字符c的長(zhǎng)度,跟數(shù)據(jù)庫(kù)的字符集有關(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)單的連接語(yǔ)法:
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ǔ)空值
連接的分類(lèi):
等于的連接 =
不等于的連接 != BETWEEN … AND … IN 注意IN和OR不能一起用
外連接 有一個(gè)字段名(+) , 沒(méi)有滿足的條件補(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ù)字類(lèi)型
有外連接時(shí), 不能用OR或IN的比較操作
4. 如何分析和執(zhí)行SQL語(yǔ)句
寫(xiě)多表連接SQL語(yǔ)句時(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; 說(shuō)明：打開(kāi)時(shí)間顯示
SQL> set autotrace on; 說(shuō)明：打開(kāi)自動(dòng)分析統(tǒng)計(jì)，并顯示SQL語(yǔ)句的運(yùn)行結(jié)果
SQL> set autotrace traceonly; 說(shuō)明：打開(kāi)自動(dòng)分析統(tǒng)計(jì)，不顯示SQL語(yǔ)句的運(yùn)行結(jié)果
接下來(lái)你就運(yùn)行測(cè)試SQL語(yǔ)句，看到其分析統(tǒng)計(jì)結(jié)果了。
一般來(lái)講，我們的SQL語(yǔ)句應(yīng)該避免大表的全表掃描。
SQL> set autotrace off; 說(shuō)明：關(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ù)的語(yǔ)法
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ù)量,如果沒(méi)有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語(yǔ)句在Group By前把結(jié)果集排除在外Having語(yǔ)句在Group By后把結(jié)果集排除在外
7. 在SQL*PLUS里可使用的其它命令:
Ctrl^C 終止正在運(yùn)行的SQL語(yǔ)句
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語(yǔ)句
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. 查詢語(yǔ)句可以嵌套
例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……] WHERE 條件) WHERE 條件2;
2. 何處可用子查詢?
當(dāng)查詢條件是不確定的條件時(shí)
DML(insert, update,delete)語(yǔ)句里也可用子查詢
HAVING里也可用子查詢
3. 兩個(gè)查詢語(yǔ)句的結(jié)果可以做集合操作
例如:
并集UNION(去掉重復(fù)記錄)
并集UNION ALL(不去掉重復(fù)記錄)
差集MINUS,
交集INTERSECT
4. 子查詢的注意事項(xiàng)
先執(zhí)行括號(hào)里面的SQL語(yǔ)句，一層層到外面
內(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語(yǔ)句是對(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);
說(shuō)明：父查詢調(diào)用子查詢只執(zhí)行一次．
6.取出結(jié)果集的80 到100的SQL語(yǔ)句
ORACLE處理每個(gè)結(jié)果集只有一個(gè)ROWNUM字段標(biāo)明它的邏輯位置,
并且只能 用ROWNUM<100, 不能用ROWNUM>80。
以下是經(jīng)過(guò)分析后較好的兩種ORACLE取得結(jié)果集80到100間的SQL語(yǔ)句( ID是唯一關(guān)鍵字的字段名 )：
語(yǔ)句寫(xiě)法：
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語(yǔ)句時(shí)綁定變量
1. 接收和定義變量的SQL*PLUS命令
ACCEPT
DEFINE UNDEFINE
&
2. 綁定變量SQL語(yǔ)句的例子(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)閉和打開(kāi)提示確認(rèn)信息old 1和new 1的顯示.
3. 綁定變量SQL語(yǔ)句的例子(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
把綁定字符串和日期類(lèi)型變量時(shí)，變量外面要加單引號(hào)
也可綁定變量來(lái)查詢不同的字段名
輸入變量值的時(shí)候不要加;等其它符號(hào)
4. ACCEPT的語(yǔ)法和例子
SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
說(shuō)明： variable 指變量名 datatype 指變量類(lèi)型，如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的語(yǔ)法和例子
SQL> DEFINE variable = value
說(shuō)明： variable 指變量名 value 指變量值
定義好了變量值后, 執(zhí)行綁定變量的SQL語(yǔ)句時(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ù)庫(kù)里,要修改sql*plus環(huán)境變量define
SQL> set define off;
八、概述數(shù)據(jù)模型和數(shù)據(jù)庫(kù)設(shè)計(jì)
1. 系統(tǒng)開(kāi)發(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)系的類(lèi)型
1:1 一對(duì)一 例如: 的士和司機(jī)
M:1 多對(duì)一 例如: 乘客和飛機(jī)
1:M 一對(duì)多 例如: 員工和技能
6. 校正實(shí)體關(guān)系的原則
屬性是單一值的, 不會(huì)有重復(fù)
屬性必須依存于實(shí)體, 要有唯一標(biāo)記
沒(méi)有非唯一屬性依賴于另一個(gè)非唯一的屬性
7. 定義結(jié)構(gòu)時(shí)的注意事項(xiàng)
減少數(shù)據(jù)冗余
減少完整性約束產(chǎn)生的問(wèn)題
確認(rèn)省略的實(shí)體,關(guān)系和屬性
8. 完整性約束的要求
Primary key 主關(guān)鍵字 唯一非NULL
Foreign key 外鍵 依賴于另一個(gè)Primary key,可能為NULL
Column 字段名 符合定義的類(lèi)型和長(zhǎng)度
Constraint 約束條件 用戶自定義的約束條件,要符合工作流要求
例如: 一個(gè)銷(xiāo)售人員的提成不能超過(guò)它的基本工資
Candidate key 候選主關(guān)鍵字 多個(gè)字段名可組成候選主關(guān)鍵字, 其組合是唯一和非NULL的
9. 把實(shí)體關(guān)系圖映射到關(guān)系數(shù)據(jù)庫(kù)對(duì)象的方法
把簡(jiǎn)單實(shí)體映射到數(shù)據(jù)庫(kù)里的表
把屬性映射到數(shù)據(jù)庫(kù)里的表的字段, 標(biāo)明類(lèi)型和注釋
把唯一標(biāo)記映射到數(shù)據(jù)庫(kù)里的唯一關(guān)鍵字
把實(shí)體間的關(guān)系映射到數(shù)據(jù)庫(kù)里的外鍵
其它的考慮:
設(shè)計(jì)索引,使查詢更快
建立視圖,使信息有不同的呈現(xiàn)面, 減少?gòu)?fù)雜的SQL語(yǔ)句
計(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. 實(shí)體關(guān)系圖的例子
ORACLE SQL and SQL*PLUS
九、創(chuàng)建表
1. ORACLE常用的字段類(lèi)型
ORACLE常用的字段類(lèi)型有
VARCHAR2 (size) 可變長(zhǎng)度的字符串, 必須規(guī)定長(zhǎng)度
CHAR(size) 固定長(zhǎng)度的字符串, 不規(guī)定長(zhǎng)度默認(rèn)值為１
NUMBER(p,s) 數(shù)字型p是位數(shù)總長(zhǎng)度, s是小數(shù)的長(zhǎng)度, 可存負(fù)數(shù)
最長(zhǎng)38位. 不夠位時(shí)會(huì)四舍五入.
DATE 日期和時(shí)間類(lèi)型
LOB 超長(zhǎng)字符, 最大可達(dá)4G
CLOB 超長(zhǎng)文本字符串
BLOB 超長(zhǎng)二進(jìn)制字符串
BFILE 超長(zhǎng)二進(jìn)制字符串, 保存在數(shù)據(jù)庫(kù)外的文件里是只讀的.
數(shù)字字段類(lèi)型位數(shù)及其四舍五入的結(jié)果
原始數(shù)值1234567.89
數(shù)字字段類(lèi)型位數(shù) 存儲(chǔ)的值
Number 1234567.89
Number(8) 12345678
Number(6) 錯(cuò)
Number(9,1) 1234567.9
Number(9,3) 錯(cuò)
Number(7,2) 錯(cuò)
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9
2. 創(chuàng)建表時(shí)給字段加默認(rèn)值 和約束條件
創(chuàng)建表時(shí)可以給字段加上默認(rèn)值
例如 : 日期字段 DEFAULT SYSDATE
這樣每次插入和修改時(shí), 不用程序操作這個(gè)字段都能得到動(dòng)作的時(shí)間
創(chuàng)建表時(shí)可以給字段加上約束條件
例如: 非空 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(6) default '0' NOT NULL,
areaname varchar2(30) default ' ' NOT NULL);
4. 創(chuàng)建表時(shí)的命名規(guī)則和注意事項(xiàng)
表名和字段名的命名規(guī)則：必須以字母開(kāi)頭，可以含符號(hào)A-Z,a-z,0-9,_,$,#
大小寫(xiě)不區(qū)分
不用SQL里的保留字, 一定要用時(shí)可用雙引號(hào)把字符串括起來(lái)．
用和實(shí)體或?qū)傩韵嚓P(guān)的英文符號(hào)長(zhǎng)度有一定的限制
注意事項(xiàng):
建表時(shí)可以用中文的字段名, 但最好還是用英文的字段名
創(chuàng)建表時(shí)要把較小的不為空的字段放在前面, 可能為空的字段放在后面
建表時(shí)如果有唯一關(guān)鍵字或者唯一的約束條件，建表時(shí)自動(dòng)建了索引
一個(gè)表的最多字段個(gè)數(shù)也是有限制的，254個(gè).
5. 約束名的命名規(guī)則和語(yǔ)法
約束名的命名規(guī)則約束名如果在建表的時(shí)候沒(méi)有指明，系統(tǒng)命名規(guī)則是SYS_Cn(n是數(shù)字)
約束名字符串的命名規(guī)則同于表和字段名的命名規(guī)則
6. 使用約束時(shí)的注意事項(xiàng)
約束里不能用系統(tǒng)函數(shù),如SYSDATE和別的表的字段比較
可以用本表內(nèi)字段的比較
想在事務(wù)處理后, 做約束的檢查
SQL> alter session set constraints deferred.
7. 由實(shí)體關(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)建表例子
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(8)
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)));
8. 通過(guò)子查詢建表
通過(guò)子查詢建表的例子
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. 用子查詢建表的注意事項(xiàng)
可以關(guān)連多個(gè)表及用集合函數(shù)生成新表,注意選擇出來(lái)的字段必須有合法的字段名稱,且不能重復(fù)。
用子查詢方式建立的表，只有非空NOT NULL的約束條件能繼承過(guò)來(lái), 其它的約束條件和默認(rèn)值都沒(méi)有繼承過(guò)來(lái).
根據(jù)需要，可以用alter table add constraint ……再建立其它的約束條件，如primary key等.
10. Foreign Key的可選參數(shù)ON DELETE CASCADE
在創(chuàng)建Foreign Key時(shí)可以加可選參數(shù):
ON DELETE CASCADE它的含義是如果刪除外鍵主表里的內(nèi)容，子表里相關(guān)的內(nèi)容將一起被刪除.
如果沒(méi)有ON DELETE CASCADE參數(shù)，子表里有內(nèi)容，父表里的主關(guān)鍵字記錄不能被刪除掉.
12. 如果數(shù)據(jù)庫(kù)表里有不滿足的記錄存在，建立約束條件將不會(huì)成功.
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ù)庫(kù)的重要組成部分之一，它隨著數(shù)據(jù)庫(kù)
的產(chǎn)生而產(chǎn)生, 隨著數(shù)據(jù)庫(kù)的變化而變化, 體現(xiàn)為sys用戶下所有的一些表和視圖.
2. 數(shù)據(jù)字典里存了以下內(nèi)容：用戶信息
用戶的權(quán)限信息
所有數(shù)據(jù)對(duì)象信息表的約束條件統(tǒng)計(jì)分析數(shù)據(jù)庫(kù)的視圖等
不能手工修改數(shù)據(jù)字典里的信息.
3. 常用的數(shù)據(jù)字典
Dictionary 存放所有數(shù)據(jù)表，視圖，同義詞名稱和解釋
Dict_columns 數(shù)據(jù)字典里字段名稱的和解釋
Dba_users 用戶 Dba_tablespaces 表空間
Dba_data_files 數(shù)據(jù)庫(kù)的文件 Dba_free_space 空閑表空間
Dba_rollback_segs 回滾段
User_objects 數(shù)據(jù)對(duì)象 User_constraints 約束條件
User_sequences 序列號(hào) User_views 視圖
User_indexes 索引 User_synonyms 同義詞
Session_roles 用戶的角色 User_role_privs 用戶的角色權(quán)限
User_sys_privs 用戶的系統(tǒng)權(quán)限 User_tab_privs 用戶的表級(jí)權(quán)限
V$session 實(shí)時(shí)用戶情況 V$sysstat 實(shí)時(shí)系統(tǒng)統(tǒng)計(jì)
V$sesstat 實(shí)時(shí)用戶統(tǒng)計(jì) V$sgastat 實(shí)時(shí)SGA使用
V$locked_object 實(shí)時(shí)鎖 V$controlfile 控制文件
V$logfile 日志文件 V$parameter 參數(shù)文件
4. 數(shù)據(jù)字典的分類(lèi)
數(shù)據(jù)字典四大類(lèi)別
User_ 用戶下所有數(shù)據(jù)庫(kù)對(duì)象
All_ 用戶權(quán)限范圍內(nèi)所有的數(shù)據(jù)庫(kù)對(duì)象
Dba_ 所有的數(shù)據(jù)庫(kù)對(duì)象
V$Content$nbsp; 統(tǒng)計(jì)分析數(shù)據(jù)庫(kù)的視圖 賦于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 = ‘&table_name';
十一. 控制數(shù)據(jù)
1 、INSERT(往數(shù)據(jù)表里插入記錄的語(yǔ)句)
SQL> insert into 表名(字段名1, 字段名2, ……) values ( 值1, 值2, ……);
SQL> insert into 表名(字段名1, 字段名2, ……) select (字段名1, 字段名2, ……)
from 另外的表名 where 條件;
可以用&標(biāo)記變量的方法多次輸入記錄
快速插入數(shù)據(jù)的方法, 一般用于大于128M的數(shù)據(jù)轉(zhuǎn)移
SQL> insert /*+ append */ into 表名
select * from 另外的用戶名 .另外的表名 WHERE 條件;
SQL> commit;
注意事項(xiàng)：
用INSERT /*+ APPEND */ 的方法會(huì)對(duì)target_tablename產(chǎn)生級(jí)別為6的獨(dú)占鎖，
如果運(yùn)行此命令時(shí)還有對(duì)target_tablename的DML操作會(huì)排隊(duì)在它后面,
對(duì)OLTP系統(tǒng)在用的表操作是不合適的。
2. 插入字符串類(lèi)型的字段的注意事項(xiàng):
字符串類(lèi)型的字段值必須用單引號(hào)括起來(lái), 例如: ’GOOD DAY’
如果字段值里包含單引號(hào)’ 需要進(jìn)行字符串轉(zhuǎn)換, 我們把它替換成兩個(gè) 單引號(hào)’ ’
字符串類(lèi)型的字段值超過(guò)定義的長(zhǎng)度會(huì)出錯(cuò), 最好在插入前進(jìn)行長(zhǎng)度校驗(yàn)
‘’ 標(biāo)記是NULL, user 標(biāo)明當(dāng)前用戶
日期字段的字段值可以用當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)時(shí)間SYSDATE, 精確到秒
用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE( )還有很多種日期格式, 可以參看ORACLE DOC.
年-月-日 小時(shí):分鐘:秒 的格式Y(jié)YYY-MM-DD HH24:MI:SS
INSERT時(shí)最大可操作的字符串長(zhǎng)度小于等于4000個(gè)單字節(jié),
如果要插入更長(zhǎng)的字符串, 請(qǐng)考慮字段用CLOB類(lèi)型, 方法借用ORACLE里自帶的DBMS_LOB程序包.
3、UPDATE (修改數(shù)據(jù)表里記錄的語(yǔ)句)
SQL> UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 條件;
如果修改的值N沒(méi)有賦值或定義時(shí), 將把原來(lái)的記錄內(nèi)容清為NULL,
最好在修改前進(jìn)行非空校驗(yàn);
值N超過(guò)定義的長(zhǎng)度會(huì)出錯(cuò), 最好在插入前進(jìn)行長(zhǎng)度校驗(yàn).
新功能，可以修改子查詢后的結(jié)果集
例子：SQL> update (select * from s_dept) set id=50 where id=60;
4、DELETE (刪除數(shù)據(jù)表里記錄的語(yǔ)句)
SQL> DELETE FROM 表名 WHERE 條件;
注意：刪除記錄并不能釋放ORACLE里被占用的數(shù)據(jù)塊表空間. 它只把那些 被刪除的數(shù)據(jù)塊標(biāo)成unused.
如果確實(shí)要?jiǎng)h除一個(gè)大表里的全部記錄, 可以用 TRUNCATE 命令, 它可以釋放占用的數(shù)據(jù)塊表空間
SQL> TRUNCATE TABLE 表名;
此操作不可回退.
5、 SQL語(yǔ)句的分類(lèi)
數(shù)據(jù)定義語(yǔ)言(DDL)：create、alter、drop（創(chuàng)建、修改結(jié)構(gòu)、刪除）（其他：rename）
數(shù)據(jù)操縱語(yǔ)言(DML)：insert、delete、select、update（增、刪、查、改）（其他：truncate）
數(shù)據(jù)控制語(yǔ)言(DCL)：grant、revoke（授權(quán)、回收）、set role
事務(wù)控制：commit、rollback、savepoint（其他：lock table、set constraint(s)、set transaction）
審計(jì)控制：audit、noaudit
系統(tǒng)控制：alter system 會(huì)話控制：alter session
其他語(yǔ)句：comment（添加注釋?zhuān)?、explain plan、analyze、validate、call
6、ORACLE里事務(wù)控制
Commit 提交事務(wù)
Rollback 回退事務(wù)
Savepoint 設(shè)置斷點(diǎn), 在事務(wù)中標(biāo)記位置, 事務(wù)結(jié)束, 斷點(diǎn)釋放
事務(wù)結(jié)束的情況遇到commit或者rollback遇到DDL和DCL語(yǔ)句發(fā)現(xiàn)錯(cuò)誤，如死鎖用戶退出SQL*PLUS系統(tǒng)重啟或崩潰
6、事物控制和SAVEPOINT命令
7. DML操作的注意事項(xiàng)
以上SQL語(yǔ)句對(duì)表都加上了行級(jí)鎖, 確認(rèn)完成后, 必須加上事物處理結(jié)束的命令COMMIT 才能正式生效,
否則改變不一定寫(xiě)入數(shù)據(jù)庫(kù)里.行級(jí)鎖也未能得到釋放.
如果想撤回這些操作, 可以用命令 ROLLBACK 復(fù)原.
在運(yùn)行INSERT, DELETE 和 UPDATE 語(yǔ)句前最好估算一下可能操作的記錄范圍,
應(yīng)該把它限定在較小 (一萬(wàn)條記錄) 范圍內(nèi),. 否則ORACLE處理這個(gè)事物用到很大的回退段.
程序響應(yīng)慢甚至失去響應(yīng). 如果記錄數(shù)上十萬(wàn)以上這些操作,
可以把這些SQL語(yǔ)句分段分次完成, 其間加上COMMIT 確認(rèn)事物處理.
太過(guò)頻繁的commit不好
十二、改變表和約束條件
1. 改變表的幾種情況(1) 運(yùn)行時(shí)會(huì)加表級(jí)鎖
改變表的名稱
SQL> RENAME 表名1 TO 表名2; SQL> ALTER TABLE 表名1 RENAME TO 表名2;
在表的后面增加一個(gè)字段
SQL> ALTER TABLE 表名 ADD 字段名 字段名描述 [ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];
修改表里字段的定義描述
SQL> ALTER TABLE 表名 MODIFY 字段名1 字段名1描述 [ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……]; 記錄為空時(shí)，可以減少字段長(zhǎng)度，改變字段類(lèi)型修改DEFAULT值只作用于修改后的INSERT和UPDATE的記錄修改NOT NULL約束只對(duì)現(xiàn)存含非空記錄的字段起作用
1. 改變表的幾種情況(2) 運(yùn)行時(shí)會(huì)加表級(jí)鎖
刪除表里的某個(gè)字段
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)鍵字或者唯一約束條件時(shí)自動(dòng)建立索引
說(shuō)明：禁止唯一關(guān)鍵字和唯一約束時(shí)索引仍然存在，可以被使用.
1. 改變表的幾種情況(3) 運(yùn)行時(shí)會(huì)加表級(jí)鎖
刪除表里的約束條件
SQL> ALTER TABLE 表名 DROP CONSTRAINTS 約束名 [CASCADE];
會(huì)把約束相關(guān)的索引一起刪除. CASCADE能同時(shí)刪去外鍵的約束條件.
把表放在或取出數(shù)據(jù)庫(kù)的內(nèi)存區(qū)
SQL> ALTER TABLE 表名 CACHE;
SQL> ALTER TABLE 表名 NOCACHE;
改變表存儲(chǔ)的表空間
SQL> ALTER TABLE 表名 MOVE TABLESPACE 表空間名 ;
注意: 如果被轉(zhuǎn)移表空間的表含有索引, 表轉(zhuǎn)移后索引變得不可用.
我們要?jiǎng)h除舊索引,建立新索引
2. 刪除表及表里的數(shù)據(jù)
刪除表
SQL> DROP TABLE 表名 [CASCADE CONSTRAINTS];
清空表里的記錄
SQL> TRUNCATE TABLE 表名;
按時(shí)間清空日志表里的記錄，使用重新命名的方法(應(yīng)用程序可能有短暫出錯(cuò), 可以選擇在不繁忙的時(shí)間執(zhí)行)
按原來(lái)表A的建表語(yǔ)句創(chuàng)建新表A1,
把表A重命名為A2（如果表A上有較頻繁的DML操作,會(huì)對(duì)表加上行級(jí)鎖，重命名過(guò)程用遞歸的方式循環(huán)做，直到DML操作結(jié)束，命名成功).
把創(chuàng)建新表A1重命名為A
歷史記錄表A2備份或刪除
3. 刪除表后應(yīng)該注意的問(wèn)題
刪除表后把表里的索引一起刪去.
刪除表后會(huì)結(jié)束基于它的懸而未決的事物
刪除表后根據(jù)表創(chuàng)建的views,synonym,stored procedure,stored function依然存在，但views,synonym變成非法的. 需要手工找出它們并刪除
.
如果用了CASCADE CONSTRAINTS會(huì)把與它相關(guān)的約束一起刪除
此操作不可回退
4. 給表加注釋
加注釋的語(yǔ)法
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 ‘‘;
關(guān)于注釋的數(shù)據(jù)庫(kù)字典
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
十三、創(chuàng)建序列號(hào)
1. 創(chuàng)建序列號(hào)里各參數(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 一次增長(zhǎng)n 個(gè)數(shù)字
NOMAXVALUE 缺省值10E+27
NOMINVALUE 缺省值1
NOCYCLE 不循環(huán), 常用于唯一關(guān)鍵字
CACHE n 在內(nèi)存里緩存n個(gè)序列,出錯(cuò)回退時(shí)會(huì)丟失
oracle8i里默認(rèn)的n是20
序列號(hào)的名稱一般可以采用“表名_字段名”的命名規(guī)則
2. 插入自動(dòng)增長(zhǎng)序列號(hào)字段的方法
INSERT時(shí)如果要用到從1開(kāi)始自動(dòng)增長(zhǎng)的數(shù)字做唯一關(guān)鍵字, 應(yīng)該先建立一個(gè)序列號(hào).
CREATE SEQUENCE 序列號(hào)的名稱 (最好是表名+序列號(hào)標(biāo)記) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE NOCACHE;
其中最大的值按字段的長(zhǎng)度來(lái)定,比如定義的自動(dòng)增長(zhǎng)的序列NUMBER(6) , 最大值為999999
INSERT 語(yǔ)句插入這個(gè)字段值為: 序列號(hào)的名稱.NEXTVAL
例子: SQL> insert into s_dept(id, name, region_id) values (s_dept_id.nextval, 'finance', 2);
1 row created.
只有運(yùn)行了序列號(hào)的名稱. nextval后序列號(hào)的名稱. currval 才有效才有值.
3. 查詢序列號(hào)的情況
SQL> select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
其中l(wèi)ast_number指的是序列號(hào)的下一個(gè)值.
4. 改變序列號(hào)
SQL> ALTER SEQUENCE sequence [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
注意: 不能改變它的起始值
如果要改變序列的起始值, 先把序列號(hào)刪除掉, 再新建一個(gè).
5. 刪除序列號(hào)
SQL>DROP SEQUENCE sequence;
6. 不能用序列號(hào)的nextval和currval的地方
視圖的查詢
有distinct的查詢
有g(shù)roup by,having,order by的查詢
有子查詢的查詢
表里的缺省值
十四、創(chuàng)建視圖
1. 視圖的概念和優(yōu)點(diǎn)
視圖是基于一個(gè)或多個(gè)表及視圖的一些查詢語(yǔ)句, 它象顯示數(shù)據(jù)的視窗, 它本身是不存儲(chǔ)數(shù)據(jù)的.
視圖可以限制數(shù)據(jù)庫(kù)的訪問(wèn), 更好的控制權(quán)限
使用戶使用簡(jiǎn)單的查詢語(yǔ)句
數(shù)據(jù)的非依賴性
同一數(shù)據(jù)的不同表現(xiàn)形式
2. 創(chuàng)建視圖的語(yǔ)法
SQL> CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
參數(shù)解釋:
FORCE 表不存在時(shí),視圖仍然可以創(chuàng)建成功
WITH CHECK OPTION 只有符合視圖定義的記錄才能被插入或修改
WITH READ ONLY 不允許DML操作
Oracle8i以后創(chuàng)建視圖可以用order by
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_name,text from user_views;
說(shuō)明: 可以根據(jù)視圖text_length來(lái)設(shè)置set long 數(shù)字;
User_updatable_columns視圖能查詢視圖里能被修改的字段
5. 簡(jiǎn)單和復(fù)雜的視圖對(duì)比
特 性 簡(jiǎn)單視圖 復(fù)雜視圖
表的數(shù)量 一個(gè) 多個(gè)
有函數(shù)嗎? 沒(méi)有 有
有分組操作嗎? 沒(méi)有 有
有基于視圖的DML操作嗎? 有 沒(méi)有
6. 在視圖上可以用DML命令嗎?
可以, 但有一定的限制條件
沒(méi)有下面的情況, 可以刪除view里的記錄. group function, group by, distinct
沒(méi)有上面和下面的情況, 可以修改view里的記錄. 字段表達(dá)式,
例如: salary*12 含rownum的view
沒(méi)有上面兩種情況, 且view里含基表里所有非空字段的情況, 可以往view里插入記錄.
7. 在視圖里使用 WITH CHECK OPTION約束條件
SQL> create or replace view empvu41
as select * from s_emp where dept_id = 41
with check option constraint empvu41_ck;
如果運(yùn)行下面命令會(huì)出錯(cuò)ora-01402
SQL> update empvu41 set dept_id=42 where id=16;
原因: 視圖empvu41里規(guī)定只能看部門(mén)號(hào)為41的記錄 修改后會(huì)把記錄排除在視圖empvu41以外
與它的約束條件沖突
8. 刪除視圖
SQL> DROP VIEW view_name;
十五、創(chuàng)建索引
1.索引的概念
索引是數(shù)據(jù)庫(kù)里的一種數(shù)據(jù)對(duì)象
它利用B*樹(shù), hash, bitmap結(jié)構(gòu)直接快速地訪問(wèn)數(shù)據(jù)
它和表是分開(kāi)存放的兩個(gè)實(shí)體
索引創(chuàng)建好了后, 由系統(tǒng)自動(dòng)調(diào)用和管理
2. 什么時(shí)候創(chuàng)建索引?
自動(dòng)創(chuàng)建的索引:唯一關(guān)鍵字, 唯一的約束條件
手工需要?jiǎng)?chuàng)建的索引:大表查詢時(shí), sql語(yǔ)句where后經(jīng)常用到的字段或字段組合
字段內(nèi)容差別很大有大量NULL值表很大, 返回記錄數(shù)較少
3. B*樹(shù)索引的結(jié)構(gòu) 每個(gè)索引由字段值和指針或ROWID組成
4.創(chuàng)建索引的語(yǔ)法
CREATE INDEX 索引名 ON 表名 ( 字段1, [字段2, ……] ) TABLESPACE 表空間名;
5.創(chuàng)建索引的注意事項(xiàng)
創(chuàng)建索引時(shí)會(huì)加行級(jí)獨(dú)占鎖
一個(gè)表的索引最好不要超過(guò)三個(gè) (特殊的大表除外)
最好用單字段索引
索引最好和表分不同的表空間存放
結(jié)合SQL語(yǔ)句的分析執(zhí)行情況, 也可以建立多字段的組合索引和基于函數(shù)的索引
大表的索引會(huì)占用很大的存儲(chǔ)空間
不要建唯一的索引, 而應(yīng)該加唯一的約束條件
6.查詢索引的方法
查詢數(shù)據(jù)字典user_indexes和user_ind_columns
例子:
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position col_pos,ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = 'S_EMP';
注意: 數(shù)據(jù)字典里存放的字符都是大寫(xiě)的.
7. 不用索引的地方
表很小
where后不經(jīng)常使用的比較字段
表被頻繁修改
返回記錄數(shù)很多
where后含IS NULL /IS NOT NULL/ like ‘%輸入符%’等條件
8. 重建索引的語(yǔ)法
ALTER INDEX 索引名 REBUILD TABLESPACE 原來(lái)表空間名 NOLOGGING;
定期重建索引可以減少索引的碎片, 更有效地使用表空間.
9. 刪除索引
SQL> drop index 索引名;
SQL> alter table 表名 drop constraint 約束名;
十六、控制用戶訪問(wèn)
1.權(quán)限的類(lèi)別
系統(tǒng)級(jí)權(quán)限: 針對(duì)整個(gè)系統(tǒng)操作的權(quán)限
如: 用戶名/密碼, 使用表空間的限額等
對(duì)象級(jí)權(quán)限: 針對(duì)某個(gè)具體object操作的權(quán)限
如: 針對(duì)某個(gè)表, 視圖, 表的某個(gè)字段的select, update, delete權(quán)限
2. 查看當(dāng)前數(shù)據(jù)庫(kù)的用戶信息
SQL>select username,default_tablespace,temporary_tablespace from dba_users;
查看在線用戶信息
SQL>select count(*) “number”,username “current username” from v$session group by username;
用戶查看自己的缺省表空間SQL>select username,default_tablespace from user_users;
3. 創(chuàng)建新用戶
SQL> create user username identified by password
default tablespace tablespace_name temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
給用戶賦權(quán)限
SQL> grant connect, resource to username;
查看當(dāng)前用戶的權(quán)限角色
SQL> select * from user_role_privs;
查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限
SQL> select * from user_sys_privs;SQL> select * from user_tab_privs;
4 、常用的角色及其權(quán)限
CONNECT 8 privs 連上Oracle,做最基本操作
RESOURCE 8 privs 具有程序開(kāi)發(fā)最的權(quán)限
DBA 114 privs 數(shù)據(jù)庫(kù)管理員所有權(quán)限
EXP_FULL_DATABASE 5 privs 數(shù)據(jù)庫(kù)整個(gè)備份輸出的權(quán)限
IMP_FULL_DATABASE 64 privs 數(shù)據(jù)庫(kù)整個(gè)備份輸入的權(quán)限
查看角色明細(xì)的系統(tǒng)權(quán)限
SQL> select * from role_sys_privs;
5、改變老用戶 可以改變老用戶的密碼, 缺省表空間, 臨時(shí)表空間, 空間限額.
SQL> alter user username identified by password
default tablespace tablespace_name
temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
撤銷(xiāo)用戶的角色或權(quán)限
SQL> revoke role_name or priv_name from username;
注意事項(xiàng)
撤消用戶的角色dba時(shí), 同時(shí)撤消了用戶unlimited tablespace的系統(tǒng)權(quán)限, 切記要再次賦予resource角色給此用戶
SQL> grant resource to username;
6、刪除用戶
如果用戶下沒(méi)有任何數(shù)據(jù)對(duì)象
SQL> drop user username;
如果用戶下有數(shù)據(jù)對(duì)象
SQL> drop user username cascade;
注意事項(xiàng)
如果用戶下有含clob,blob字段的表, 應(yīng)該先刪除這些表后,才能用cascade選項(xiàng)完全刪除.
7、角色的概念和管理
角色是命名多個(gè)相關(guān)權(quán)限的組合. 能把它賦于其它的用戶或角色我們能創(chuàng)建角色, 使權(quán)限管理更容易一些.
8、賦于系統(tǒng)的權(quán)限語(yǔ)法和例子
語(yǔ)法:
SQL> GRANT sys_priv TO {user|role|PUBLIC} [WITH ADMIN OPTION];
例子:
SQL> GRANT create session TO sue, rich;
SQL> GRANT create table To scott, manager;
注意: 如果用WITH ADMIN OPTION通過(guò)中間用戶賦于的系統(tǒng)權(quán)限 中間用戶刪除后, 系統(tǒng)權(quán)限仍然存在.
9、賦于數(shù)據(jù)對(duì)象級(jí)的權(quán)限語(yǔ)法和例子
語(yǔ)法:
SQL> GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];
例子:
SQL> GRANT select ON s_emp TO sue, rich;
SQL> GRANT update (name, region_id)
ON s_dept TO scott, manager;
注意: 如果用WITH GRANT OPTION通過(guò)中間用戶賦于的對(duì)象權(quán)限 中間用戶刪除后,對(duì)象權(quán)限就不存在了.
