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)?按數據庫的字符集,算出字符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??兩個字段共同組成一個唯一字段
九、創建表
1. ORACLE常用的字段類型
ORACLE常用的字段類型有
??VARCHAR2 (size) ?可變長度的字符串, 必須規定長度
??CHAR(size)??固定長度的字符串, 不規定長度默認值為１
?NUMBER(p,s) ??數字型p是位數總長度, s是小數的長度, 可存負數
????最長38位. 不夠位時會四舍五入.
?DATE ???日期和時間類型
?LOB???超長字符, 最大可達4G
??CLOB??超長文本字符串?
??BLOB??超長二進制字符串?
??BFILE??超長二進制字符串, 保存在數據庫外的文件里是只讀的.
數字字段類型位數及其四舍五入的結果
原始數值1234567.89
數字字段類型位數??存儲的值
Number????1234567.89
Number(8)???12345678
Number(6)???錯
Number(9,1)???1234567.9
Number(9,3)???錯
Number(7,2)???錯
Number(5,-2)???1234600
Number(5,-4)???1230000
Number(*,1)???1234567.9
2. 創建表時給字段加默認值 和約束條件
創建表時可以給字段加上默認值
例如?: ?日期字段?DEFAULT SYSDATE
這樣每次插入和修改時, 不用程序操作這個字段都能得到動作的時間
創建表時可以給字段加上約束條件
例如:?非空??NOT NULL
?不允許重復 ?UNIQUE
?關鍵字 ?PRIMARY KEY
?按條件檢查?CHECK (條件)
?外鍵??REFERENCES 表名(字段名)
?
3. 創建表的例子
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. 創建表時的命名規則和注意事項
表名和字段名的命名規則：必須以字母開頭，可以含符號A-Z,a-z,0-9,_,$,#
大小寫不區分
不用SQL里的保留字, 一定要用時可用雙引號把字符串括起來．
用和實體或屬性相關的英文符號長度有一定的限制
注意事項:
建表時可以用中文的字段名, 但最好還是用英文的字段名
創建表時要把較小的不為空的字段放在前面, 可能為空的字段放在后面
建表時如果有唯一關鍵字或者唯一的約束條件，建表時自動建了索引
一個表的最多字段個數也是有限制的，254個.
5. 約束名的命名規則和語法
約束名的命名規則約束名如果在建表的時候沒有指明，系統命名規則是SYS_Cn(n是數字)
約束名字符串的命名規則同于表和字段名的命名規則
6. 使用約束時的注意事項
約束里不能用系統函數,如SYSDATE和別的表的字段比較
可以用本表內字段的比較
想在事務處理后, 做約束的檢查
SQL> alter session set constraints deferred.
7. 由實體關系圖到創建表的例子???? s_dept
前提條件:已有region表且含唯一關鍵字的字段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. 較復雜的創建表例子
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.? 通過子查詢建表
通過子查詢建表的例子
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;
只要表的結構.
10. 用子查詢建表的注意事項
可以關連多個表及用集合函數生成新表,注意選擇出來的字段必須有合法的字段名稱,且不能重復。
用子查詢方式建立的表，只有非空NOT NULL的約束條件能繼承過來, 其它的約束條件和默認值都沒有繼承過來.
根據需要，可以用alter table add constraint ……再建立其它的約束條件，如primary key等.
10. Foreign Key的可選參數ON DELETE CASCADE
在創建Foreign Key時可以加可選參數:
ON DELETE CASCADE它的含義是如果刪除外鍵主表里的內容，子表里相關的內容將一起被刪除.
如果沒有ON DELETE CASCADE參數，子表里有內容，父表里的主關鍵字記錄不能被刪除掉.
12. 如果數據庫表里有不滿足的記錄存在，建立約束條件將不會成功.
13. 給表創建和刪除同義詞的例子
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里的數據字典
1. 什么是數據字典ORACLE的數據字典是數據庫的重要組成部分之一，它隨著數據庫
?的產生而產生, 隨著數據庫的變化而變化, 體現為sys用戶下所有的一些表和視圖.
2. 數據字典里存了以下內容：用戶信息
?用戶的權限信息
?所有數據對象信息表的約束條件統計分析數據庫的視圖等
?不能手工修改數據字典里的信息.
3. 常用的數據字典
Dictionary?存放所有數據表，視圖，同義詞名稱和解釋
Dict_columns?數據字典里字段名稱的和解釋
Dba_users?用戶??Dba_tablespaces??表空間
Dba_data_files?數據庫的文件?Dba_free_space????? ?空閑表空間
Dba_rollback_segs 回滾段
User_objects?數據對象?User_constraints?約束條件
User_sequences?序列號??User_views??視圖
User_indexes?索引??User_synonyms??同義詞
Session_roles?用戶的角色?User_role_privs??用戶的角色權限
User_sys_privs?用戶的系統權限?User_tab_privs??用戶的表級權限
V$session?實時用戶情況?V$sysstat??實時系統統計
V$sesstat?實時用戶統計?V$sgastat??實時SGA使用
V$locked_object 實時鎖??V$controlfile??控制文件
V$logfile?日志文件?V$parameter??參數文件
4. 數據字典的分類
數據字典四大類別
User_??用戶下所有數據庫對象
All_??用戶權限范圍內所有的數據庫對象
Dba_??所有的數據庫對象
V$??統計分析數據庫的視圖??賦于oem_monitor權限非DBA用戶也可查詢V$*視圖
5. 查詢數據字典
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';
十一. 控制數據
1 、INSERT(往數據表里插入記錄的語句)
SQL> insert into 表名(字段名1, 字段名2, ……) values ( 值1, 值2, ……);
SQL> insert into 表名(字段名1, 字段名2, ……) select (字段名1, 字段名2, ……)
? from? 另外的表名 where 條件;?
可以用&標記變量的方法多次輸入記錄
快速插入數據的方法, 一般用于大于128M的數據轉移
SQL> insert /*+ append */ into 表名
?select * from? 另外的用戶名 .另外的表名 WHERE 條件;
SQL> commit;
注意事項：
??? ?用INSERT /*+ APPEND */ 的方法會對target_tablename產生級別為6的獨占鎖，
??? ?如果運行此命令時還有對target_tablename的DML操作會排隊在它后面,
??? ?對OLTP系統在用的表操作是不合適的。
2. 插入字符串類型的字段的注意事項:
字符串類型的字段值必須用單引號括起來, 例如: ’GOOD DAY’
如果字段值里包含單引號’ 需要進行字符串轉換, 我們把它替換成兩個?? 單引號’ ’
字符串類型的字段值超過定義的長度會出錯, 最好在插入前進行長度校驗
‘’ 標記是NULL, user 標明當前用戶
日期字段的字段值可以用當前數據庫的系統時間SYSDATE, 精確到秒
用字符串轉換成日期型函數TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE(? )還有很多種日期格式, 可以參看ORACLE DOC.
年-月-日 小時:分鐘:秒 的格式YYYY-MM-DD HH24:MI:SS
INSERT時最大可操作的字符串長度小于等于4000個單字節,
如果要插入更長的字符串, 請考慮字段用CLOB類型, 方法借用ORACLE里自帶的DBMS_LOB程序包.
3、UPDATE (修改數據表里記錄的語句)
SQL> UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… ?WHERE 條件;
如果修改的值N沒有賦值或定義時, 將把原來的記錄內容清為NULL,
最好在修改前進行非空校驗;
值N超過定義的長度會出錯, 最好在插入前進行長度校驗.
新功能，可以修改子查詢后的結果集
例子：SQL> update (select * from s_dept) set id=50 where id=60;
4、DELETE (刪除數據表里記錄的語句)
SQL> DELETE FROM? 表名 WHERE 條件;
注意：刪除記錄并不能釋放ORACLE里被占用的數據塊表空間. 它只把那些?? 被刪除的數據塊標成unused.
如果確實要刪除一個大表里的全部記錄, 可以用 TRUNCATE 命令, 它可以釋放占用的數據塊表空間
SQL> TRUNCATE TABLE 表名;
此操作不可回退.
?
5、 SQL語句的分類
數據定義語言(DDL)：create、alter、drop（創建、修改結構、刪除）（其他：rename）
數據操縱語言(DML)：insert、delete、select、update（增、刪、查、改）（其他：truncate）
數據控制語言(DCL)：grant、revoke（授權、回收）、set role
事務控制：commit、rollback、savepoint（其他：lock table、set constraint(s)、set transaction）
審計控制：audit、noaudit
系統控制：alter system 會話控制：alter session
其他語句：comment（添加注釋）、explain plan、analyze、validate、call
6、ORACLE里事務控制?
Commit??提交事務
Rollback?回退事務
Savepoint?設置斷點, 在事務中標記位置, 事務結束, 斷點釋放
事務結束的情況遇到commit或者rollback遇到DDL和DCL語句發現錯誤，如死鎖用戶退出SQL*PLUS系統重啟或崩潰
6、事物控制和SAVEPOINT命令
7. DML操作的注意事項
以上SQL語句對表都加上了行級鎖, 確認完成后, 必須加上事物處理結束的命令COMMIT 才能正式生效,
否則改變不一定寫入數據庫里.行級鎖也未能得到釋放.
如果想撤回這些操作, 可以用命令 ROLLBACK 復原.
在運行INSERT, DELETE 和 UPDATE 語句前最好估算一下可能操作的記錄范圍,
應該把它限定在較小 (一萬條記錄) 范圍內,. 否則ORACLE處理這個事物用到很大的回退段.
程序響應慢甚至失去響應. 如果記錄數上十萬以上這些操作,
可以把這些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約束只對現存含非空記錄的字段起作用
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 ……]);
加唯一關鍵字或者唯一約束條件時自動建立索引
說明：禁止唯一關鍵字和唯一約束時索引仍然存在，可以被使用.
1. 改變表的幾種情況(3)??運行時會加表級鎖
刪除表里的約束條件
SQL> ALTER TABLE 表名 DROP CONSTRAINTS 約束名 [CASCADE];
??會把約束相關的索引一起刪除. CASCADE能同時刪去外鍵的約束條件.
把表放在或取出數據庫的內存區
SQL> ALTER TABLE 表名 CACHE;
SQL> ALTER TABLE 表名 NOCACHE;
改變表存儲的表空間
SQL> ALTER TABLE 表名 MOVE TABLESPACE 表空間名 ;
注意: 如果被轉移表空間的表含有索引, 表轉移后索引變得不可用.
????? 我們要刪除舊索引,建立新索引
2. 刪除表及表里的數據
刪除表
SQL> DROP TABLE 表名 [CASCADE CONSTRAINTS];
清空表里的記錄
SQL> TRUNCATE TABLE 表名;
按時間清空日志表里的記錄，使用重新命名的方法(應用程序可能有短暫出錯, 可以選擇在不繁忙的時間執行)
按原來表A的建表語句創建新表A1,
把表A重命名為A2（如果表A上有較頻繁的DML操作,會對表加上行級鎖，重命名過程用遞歸的方式循環做，直到DML操作結束，命名成功).
把創建新表A1重命名為A
歷史記錄表A2備份或刪除
3.? 刪除表后應該注意的問題
刪除表后把表里的索引一起刪去.
刪除表后會結束基于它的懸而未決的事物
刪除表后根據表創建的views,synonym,stored procedure,stored function依然存在，但views,synonym變成非法的. 需要手工找出它們并刪除.
如果用了CASCADE CONSTRAINTS會把與它相關的約束一起刪除
此操作不可回退
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 ‘‘;
關于注釋的數據庫字典
?ALL_COL_COMMENTS
?USER_COL_COMMENTS
?ALL_TAB_COMMENTS
?USER_TAB_COMMENTS
十三、創建序列號
1. 創建序列號里各參數的解釋
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 個數字
NOMAXVALUE??缺省值10E+27
NOMINVALUE??缺省值1
NOCYCLE??不循環, 常用于唯一關鍵字
CACHE?n??在內存里緩存n個序列,出錯回退時會丟失
????oracle8i里默認的n是20
序列號的名稱一般可以采用“表名_字段名”的命名規則
2. 插入自動增長序列號字段的方法
INSERT時如果要用到從1開始自動增長的數字做唯一關鍵字, 應該先建立一個序列號.
CREATE SEQUENCE 序列號的名稱 (最好是表名+序列號標記) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE NOCACHE;
其中最大的值按字段的長度來定,比如定義的自動增長的序列NUMBER(6) , 最大值為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的查詢
有子查詢的查詢
表里的缺省值
十四、創建視圖
1. 視圖的概念和優點
視圖是基于一個或多個表及視圖的一些查詢語句, 它象顯示數據的視窗, 它本身是不存儲數據的.
?
視圖可以限制數據庫的訪問, 更好的控制權限
使用戶使用簡單的查詢語句
數據的非依賴性
同一數據的不同表現形式
2. 創建視圖的語法
SQL> CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW ?view[(alias[, alias]...)]
??AS subquery
??[WITH CHECK OPTION [CONSTRAINT constraint]]
??[WITH READ ONLY]
參數解釋:
FORCE???表不存在時,視圖仍然可以創建成功
WITH CHECK OPTION ?只有符合視圖定義的記錄才能被插入或修改
WITH READ ONLY?不允許DML操作
Oracle8i以后創建視圖可以用order by
3. 創建修改視圖的例子
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創建的視圖
??table_name的結構改變后?view要重建或compile后才能顯示新的字段內容
4. 查詢視圖的數據字典
SQL> set long 1600;
SQL> select view_name,text from user_views;
說明: 可以根據視圖text_length來設置set long 數字;
User_updatable_columns視圖能查詢視圖里能被修改的字段
5.? 簡單和復雜的視圖對比
特??? 性 ??簡單視圖?復雜視圖
表的數量 ??一個 ??多個
有函數嗎? ??沒有 ??有
有分組操作嗎? ??沒有 ??有
有基于視圖的DML操作嗎? ?有 ??沒有
6. 在視圖上可以用DML命令嗎?
可以, 但有一定的限制條件
沒有下面的情況, 可以刪除view里的記錄.?group function, group by, distinct
沒有上面和下面的情況, 可以修改view里的記錄.?字段表達式,
例如: salary*12?含rownum的view
沒有上面兩種情況, 且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;
如果運行下面命令會出錯ora-01402
SQL> update empvu41 set dept_id=42 where id=16;
原因:?視圖empvu41里規定只能看部門號為41的記錄?修改后會把記錄排除在視圖empvu41以外
?與它的約束條件沖突
8. 刪除視圖
SQL> DROP VIEW view_name;
十五、創建索引
1.索引的概念
索引是數據庫里的一種數據對象
它利用B*樹, hash, bitmap結構直接快速地訪問數據
它和表是分開存放的兩個實體
索引創建好了后, 由系統自動調用和管理
2. 什么時候創建索引?
自動創建的索引:唯一關鍵字, 唯一的約束條件
手工需要創建的索引:大表查詢時, sql語句where后經常用到的字段或字段組合
? 字段內容差別很大有大量NULL值表很大, 返回記錄數較少
3. B*樹索引的結構?每個索引由字段值和指針或ROWID組成
4.創建索引的語法
CREATE INDEX? 索引名 ON? 表名 ( 字段1, [字段2, ……] ) ?TABLESPACE 表空間名;
5.創建索引的注意事項
?創建索引時會加行級獨占鎖
?一個表的索引最好不要超過三個 (特殊的大表除外)
?最好用單字段索引
?索引最好和表分不同的表空間存放
?結合SQL語句的分析執行情況, 也可以建立多字段的組合索引和基于函數的索引
?大表的索引會占用很大的存儲空間
?不要建唯一的索引, 而應該加唯一的約束條件
6.查詢索引的方法
查詢數據字典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';
注意: 數據字典里存放的字符都是大寫的.
7. 不用索引的地方
表很小
where后不經常使用的比較字段
表被頻繁修改
返回記錄數很多
where后含IS NULL /IS NOT NULL/ like ‘%輸入符%’等條件
8. 重建索引的語法
ALTER INDEX 索引名 REBUILD TABLESPACE 原來表空間名 NOLOGGING;
定期重建索引可以減少索引的碎片, 更有效地使用表空間.
9. 刪除索引
SQL> drop index 索引名;
SQL> alter table 表名 drop constraint 約束名;
十六、控制用戶訪問
1.權限的類別
系統級權限: 針對整個系統操作的權限??
?如: 用戶名/密碼,?使用表空間的限額等?
對象級權限: 針對某個具體object操作的權限?
?如: 針對某個表, 視圖, 表的某個字段的select, update, delete權限
2. 查看當前數據庫的用戶信息
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. 創建新用戶
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 ……] ;
給用戶賦權限
SQL> grant connect, resource to username;
查看當前用戶的權限角色
SQL>? select * from user_role_privs;
查看當前用戶的系統權限和表級權限
SQL>? select * from user_sys_privs;SQL>? select * from user_tab_privs;
4 、常用的角色及其權限
CONNECT????? ??8?privs?連上Oracle,做最基本操作
RESOURCE????? ?8? privs?具有程序開發最的權限
DBA?????????? ?114? privs?數據庫管理員所有權限
EXP_FULL_DATABASE???? ?5? privs?數據庫整個備份輸出的權限
IMP_FULL_DATABASE????? ??64? privs?數據庫整個備份輸入的權限
查看角色明細的系統權限
SQL> select * from role_sys_privs;
5、改變老用戶???? 可以改變老用戶的密碼, 缺省表空間, 臨時表空間, 空間限額.
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 ……] ;
撤銷用戶的角色或權限
SQL> revoke role_name or priv_name from username;
注意事項
???? 撤消用戶的角色dba時, 同時撤消了用戶unlimited tablespace的系統權限, 切記要再次賦予resource角色給此用戶
SQL> grant resource to username;
6、刪除用戶
如果用戶下沒有任何數據對象
SQL> drop user username;
如果用戶下有數據對象
SQL> drop user username cascade;
注意事項
??? 如果用戶下有含clob,blob字段的表, 應該先刪除這些表后,才能用cascade選項完全刪除.
7、角色的概念和管理
角色是命名多個相關權限的組合. 能把它賦于其它的用戶或角色我們能創建角色, 使權限管理更容易一些.
8、賦于系統的權限語法和例子
語法:
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通過中間用戶賦于的系統權限?中間用戶刪除后, 系統權限仍然存在.
9、賦于數據對象級的權限語法和例子
語法:
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通過中間用戶賦于的對象權限?中間用戶刪除后,對象權限就不存在了.???????