第一篇：黑馬程序員c語言教程：Oracle概念

一、選擇行

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, 默認(rèn)值是14 SQL> set linesize 100;設(shè)定SQL語句返回結(jié)果集一行的寬度100, 默認(rèn)值是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] ];默認(rèn)的排序是ASC升序(由小到大)還可以O(shè)RDER 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 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%'

三、單行函數(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(日期), 確省的是去掉時間

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 返回當(dāng)月的最后一天的日期

MONTHS_BETWEEN(d1,d2)兩個日期值間的月份,d1

DUAL是SYS用戶下一個空表，它只有一個字段dummy 4.轉(zhuǎn)換函數(shù)(1)TO_CHAR(date,'日期顯示格式')TO_CHAR(number)用于顯示或報表的格式對齊 TO_DATE(char,'日期顯示格式')TO_LOB 把long字段轉(zhuǎn)換成lob字段

TO_NUMBER(char)用于計算或者比較大小 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)(說明：周計是按ISO標(biāo)準(zhǔn),從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)DD 日 DAY DY HH24 小時 HH12 HH MI 分鐘 SS 秒

如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參數(shù)

NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX環(huán)境變量或者NT的注冊表里的設(shè)置 NLS_DATE_FORMAT=yyyy-mm-dd 7

J2EE @ zxw 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ù)字前面補0 to_char(-1200,'00000.00').小數(shù)點的位置 , 標(biāo)記位置的逗號 用在數(shù)字顯示格式的左邊

L 根據(jù)數(shù)據(jù)庫字符集加貨幣符號 to_char(-1200,'L9999.99')B 把數(shù)字0顯示為空格,用在數(shù)字顯示格式的右邊 MI 右邊的負(fù)數(shù)標(biāo)記 to_char(-1200,'9999.99MI')PR 括起來的負(fù)數(shù) to_char(-1200,'9999.99PR')EEEE 用指數(shù)方式表示 to_char(-1200,'9999.99EEEE')5.輸入字符,返回數(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')

第二篇：黑馬程序員c語言教程：Oracle概念（推薦）

七、在執(zhí)行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;可以關(guān)閉和打開提示確認(rèn)信息old 1和new 1的顯示.3.綁定變量SQL語句的例子(2)SQL> select id, last_name, salary from s_emp where title = '&job_title';Enter value for job_title: Stock Clerk 11

J2EE @ zxw 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 可自定義彈出提示符的內(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)='&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, ?.] 注意事項: 一次最多只能獲取9個&變量, 變量名稱只能是從&1,&2到&9 變量名后不要加特殊的結(jié)束符號

如果在SQL*PLUS里要把&符號保存在ORACLE數(shù)據(jù)庫里,要修改sql*plus環(huán)境變量define SQL> set define off;

八、概述數(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)實體 存有特定信息的目標(biāo)和事件 例如: 客戶,訂單等 屬性 描述實體的屬性 例如: 姓名,電話號碼等 關(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ù)

屬性必須依存于實體, 要有唯一標(biāo)記

沒有非唯一屬性依賴于另一個非唯一的屬性 7.定義結(jié)構(gòu)時的注意事項 減少數(shù)據(jù)冗余

減少完整性約束產(chǎn)生的問題 確認(rè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ù)庫里的表的字段, 標(biāo)明類型和注釋 把唯一標(biāo)記映射到數(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ī)定長度默認(rèn)值為１

NUMBER(p,s)數(shù)字型p是位數(shù)總長度, s是小數(shù)的長度, 可存負(fù)數(shù) 最長38位.不夠位時會四舍五入.DATE 日期和時間類型

LOB 超長字符, 最大可達(dá)4G CLOB 超長文本字符串 BLOB 超長二進(jìn)制字符串

BFILE 超長二進(jìn)制字符串, 保存在數(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)建表時給字段加默認(rèn)值 和約束條件 創(chuàng)建表時可以給字段加上默認(rèn)值

例如 : 日期字段 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)));

第三篇：黑馬程序員c語言教程：Oracle概念

四、從多個表里選取數(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 <= 要好 連接操作的字段名上最好要有索引 連接操作的字段最好用整數(shù)數(shù)字類型

有外連接時, 不能用OR或IN的比較操作 4.如何分析和執(zhí)行SQL語句

寫多表連接SQL語句時要知道它的分析執(zhí)行計劃的情況.Sys用戶下運行@/ORACLE_HOME/sqlplus/admin/plustrce.sql 產(chǎn)生plustrace角色

Sys用戶下把此角色賦予一般用戶 SQL> grant plustrace to &username;一般用戶下運行@/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.何處可用子查詢? 當(dāng)查詢條件是不確定的條件時

DML(insert, update,delete)語句里也可用子查詢 HAVING里也可用子查詢

3.兩個查詢語句的結(jié)果可以做集合操作 例如: 并集UNION(去掉重復(fù)記錄)并集UNION ALL(不去掉重復(fù)記錄)10

J2EE @ zxw 差集MINUS, 交集INTERSECT 4.子查詢的注意事項

先執(zhí)行括號里面的SQL語句，一層層到外面 內(nèi)部查詢只執(zhí)行一次

如果里層的結(jié)果集返回多個，不能用= ><>= <=等比較符要用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);說明：父查詢調(diào)用子查詢只執(zhí)行一次． 6.取出結(jié)果集的80 到100的SQL語句

ORACLE處理每個結(jié)果集只有一個ROWNUM字段標(biāo)明它的邏輯位置, 并且只能 用ROWNUM<100, 不能用ROWNUM>80。

以下是經(jīng)過分析后較好的兩種ORACLE取得結(jié)果集80到100間的SQL語句(ID是唯一關(guān)鍵字的字段名)語句寫法：

SQL>select * from((select rownum as numrow, c.* from(select [field_name,...] from table_name where 條件1 order by 條件2)c)where numrow > 80 and numrow <= 100)order by 條件3;

第四篇：黑馬程序員c語言教程：Oracle概念(7)

十六、控制用戶訪問 1.權(quán)限的類別

系統(tǒng)級權(quán)限: 針對整個系統(tǒng)操作的權(quán)限 如: 用戶名/密碼, 使用表空間的限額等

對象級權(quán)限: 針對某個具體object操作的權(quán)限

如: 針對某個表, 視圖, 表的某個字段的select, update, delete權(quán)限 23

J2EE @ zxw 2.查看當(dāng)前數(shù)據(jù)庫的用戶信息

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)限和表級權(quán)限

SQL> select * from user_sys_privs;SQL> select * from user_tab_privs;4、常用的角色及其權(quán)限

CONNECT 8 privs 連上Oracle,做最基本操作 RESOURCE 8 privs 具有程序開發(fā)最的權(quán)限 DBA 114 privs 數(shù)據(jù)庫管理員所有權(quán)限

EXP_FULL_DATABASE 5 privs 數(shù)據(jù)庫整個備份輸出的權(quán)限 IMP_FULL_DATABASE 64 privs 數(shù)據(jù)庫整個備份輸入的權(quán)限 查看角色明細(xì)的系統(tǒng)權(quán)限

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 ??];撤銷用戶的角色或權(quán)限

SQL> revoke role_name or priv_name from username;注意事項

撤消用戶的角色dba時, 同時撤消了用戶unlimited tablespace的系統(tǒng)權(quán)限, 切記要再次賦予resource角色給此用戶 SQL> grant resource to username;

6、刪除用戶

如果用戶下沒有任何數(shù)據(jù)對象 SQL> drop user username;如果用戶下有數(shù)據(jù)對象

SQL> drop user username cascade;注意事項

如果用戶下有含clob,blob字段的表, 應(yīng)該先刪除這些表后,才能用cascade選項完全刪除.7、角色的概念和管理 24

J2EE @ zxw 角色是命名多個相關(guān)權(quán)限的組合.能把它賦于其它的用戶或角色我們能創(chuàng)建角色, 使權(quán)限管理更容易一些.8、賦于系統(tǒng)的權(quán)限語法和例子 語法: 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通過中間用戶賦于的系統(tǒng)權(quán)限中間用戶刪除后, 系統(tǒng)權(quán)限仍然存在.9、賦于數(shù)據(jù)對象級的權(quán)限語法和例子 語法: 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通過中間用戶賦于的對象權(quán)限 中間用戶刪除后,對象權(quán)限就不存在了.-------------

----------------------------完--------------------------補充：

rownum表中的行號，自動生成的，只能用<,<=操作符 rowid用來唯一表示數(shù)據(jù)庫表中的一行(1)在oralce上的分頁需要用到偽列,只所以這么做，是因為oralce不支持rownum比較 比如說找出第三行紀(jì)錄，那就是 select last_name, salary from(select rownum a, b.* from s_emp b)where a=3 如果找出第１０行到第２０行的數(shù)據(jù)的話，那就必須用到 select last_name, salary from(select rownum a, b.* from s_emp b)where a > 10 and a < 2;這是sql級別的分頁，優(yōu)點是速度快，缺點是可移植性差；(2)COURSEID COURSENAME SCORE------------------------------1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 25

J2EE @ zxw SQL> select courseid, coursename ,score ,decode(sign(score-61),-1,'fail','pass')as mark from course_v;COURSEID COURSENAME SCORE MARK----------------------------------1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass

(3)已經(jīng)知道原表

year salary---------------------------------------2000 1000 2001 2000 2002 3000 2003 4000 顯示查詢結(jié)果

year salary---------------------------------------2000 1000 2001 3000 2002 6000 2003 10000 即salary為以前年的工資的和； 解答過程如下：

select b.year,sum(a.salary)from test a,test b where a.year<=b.year group by b.year order by year

/*============================== *Author: MartriWang@gmail.com *Date: 09/05/2007 *Description:ORACLE Summary

*============================*/ 匯總：

set autotrace on set autotrace traceonly explain set timing on 或通過SQL*PLUS trace，然后查看user_dump_dest下的跟蹤文件，使用tkprof工具格式化后閱覽。

alter session set events '10046 trace name context forever,level 12';alter session set events '10046 trace name context off';SELECT p.spid,s.username FROM v$session s,v$process p WHERE s.audsid=USERENV('sessionid')AND s.paddr = p.addr;使用方法示例：

DBserver% sqlplus perf/perf

SQL*Plus: Release 9.2.0.6.064bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0-Production SQL> set timing on SQL> set autotrace on SQL> select count(*)from perf_sdcch_nn where start_time =(select max(start_time)from perf_sdcch_nn);COUNT(*)----------638 Elapsed: 00:00:00.80 Execution Plan---------------------------0 SELECT STATEMENT Optimizer=CHOOSE(Cost=1 Card=1 Bytes=8)1 0 SORT(AGGREGATE)2 1 INDEX(RANGE SCAN)OF 'IDX02_PERF_SDCCH_NN'(NON-UNIQUE)(Cost=2 Card=1495 Bytes=11960)3 2 SORT(AGGREGATE)4 3 INDEX(FULL SCAN(MIN/MAX))OF 'IDX02_PERF_SDCCH_NN'(NON-UNIQUE)(Cost=1 Card=3852090 Bytes=30816720)Statistics---------------------------0 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 492 bytes sent via SQL*Net to client 656 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts(memory)0 sorts(disk)1 rows processed SQL> 從上面的示例我們可以看到，該SQL語句執(zhí)行花了0.8秒，select語句和inline view，都使用了名為'IDX02_PERF_SDCCH_NN'的索引

物理讀為0，redo log size為0，沒有生成REDO日志。

第五篇：黑馬程序員c語言教程：Oracle簡介

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的約束條件能繼承過來, 其它的約束條件和默認(rèn)值都沒有繼承過來.根據(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 = ‘&table_name';十一.控制數(shù)據(jù)、INSERT(往數(shù)據(jù)表里插入記錄的語句)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;注意事項：

用INSERT /*+ APPEND */ 的方法會對target_tablename產(chǎn)生級別為6的獨占鎖，如果運行此命令時還有對target_tablename的DML操作會排隊在它后面, 對OLTP系統(tǒng)在用的表操作是不合適的。17

J2EE @ zxw 2.插入字符串類型的字段的注意事項: 字符串類型的字段值必須用單引號括起來, 例如: ’GOOD DAY’

如果字段值里包含單引號’ 需要進(jìn)行字符串轉(zhuǎn)換, 我們把它替換成兩個 單引號’ ’

字符串類型的字段值超過定義的長度會出錯, 最好在插入前進(jìn)行長度校驗 ‘’ 標(biāo)記是NULL, user 標(biāo)明當(dāng)前用戶

日期字段的字段值可以用當(dāng)前數(shù)據(jù)庫的系統(tǒng)時間SYSDATE, 精確到秒

用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()還有很多種日期格式, 可以參看ORACLE DOC.年-月-日 小時:分鐘:秒 的格式Y(jié)YYY-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, 最好在修改前進(jìn)行非空校驗;值N超過定義的長度會出錯, 最好在插入前進(jì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ù)塊標(biāo)成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ù)中標(biāo)記位置, 事務(wù)結(jié)束, 斷點釋放

事務(wù)結(jié)束的情況遇到commit或者rollback遇到DDL和DCL語句發(fā)現(xiàn)錯誤，如死鎖用戶退出

SQL*PLUS系統(tǒng)重啟或崩潰 7.DML操作的注意事項 18

J2EE @ zxw 以上SQL語句對表都加上了行級鎖, 確認(rèn)完成后, 必須加上事物處理結(jié)束的命令COMMIT 才能正式生效, 否則改變不一定寫入數(shù)據(jù)庫里.行級鎖也未能得到釋放.如果想撤回這些操作, 可以用命令 ROLLBACK 復(fù)原.在運行INSERT, DELETE 和 UPDATE 語句前最好估算一下可能操作的記錄范圍, 應(yīng)該把它限定在較小(一萬條記錄)范圍內(nèi),.否則ORACLE處理這個事物用到很大的回退段.程序響應(yīng)慢甚至失去響應(yīng).如果記錄數(shù)上十萬以上這些操作, 可以把這些SQL語句分段分次完成, 其間加上COMMIT 確認(rèn)事物處理.太過頻繁的commit不好