一.SQL語言簡介1. SQL概述
SQL是一種面向數(shù)據(jù)庫的通用數(shù)據(jù)處理語言規(guī)范，能完成以下幾類功能：提取查詢數(shù)據(jù)，插入修改刪除數(shù)據(jù)，生成修改和刪除數(shù)據(jù)庫對象，數(shù)據(jù)庫安全控制，數(shù)據(jù)庫完整性及數(shù)據(jù)保護控制。

數(shù)據(jù)庫對象包括表、視圖、索引、同義詞、簇、觸發(fā)器、函數(shù)、過程、包、數(shù)據(jù)庫鏈、快照等（表空間、回滾段、角色、用戶）。數(shù)據(jù)庫通過對表的操作來管理存儲在其中的數(shù)據(jù)。
2. 數(shù)據(jù)庫查詢
1)    用SELECT語句從表中提取查詢數(shù)據(jù)。語法為

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

說明：SELECT子句用于指定檢索數(shù)據(jù)庫的中哪些列，F(xiàn)ROM子句用于指定從哪一個表或視圖中檢索數(shù)據(jù)。

2)    SELECT中的操作符及多表查詢WHERE子句。(LIKE,IS,…)

  WHERE子句中的條件可以是一個包含等號或不等號的條件表達式，也可以是一個含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比較運算符的條件式，還可以是由單一的條件表達通過邏輯運算符組合成復(fù)合條件。

比較操作符 =  >  <  >=  <=  !=  <>

SQL操作符 BETWEEN … AND…          IN   LIKE            IS NULL     

              NOT BETWEEN … AND… NOT IN NOT LIKE   IS NOT NULL

邏輯操作符 AND      OR  NOT

3)    ORDER BY   子句

ORDER BY 子句使得SQL在顯示查詢結(jié)果時將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表達式的值確定。

4)    連接查詢

利用SELECT語句進行數(shù)據(jù)庫查詢時，可以把多個表、視圖的數(shù)據(jù)結(jié)合起來，使得查詢結(jié)果的每一行中包含來自多個表達式或視圖的數(shù)據(jù)，這種操作被稱為連接查詢。

連接查詢的方法是在SELECT命令的FROM子句中指定兩個或多個將被連接查詢的表或視圖，并且在WHERE子句告訴ORACLE如何把多個表的數(shù)據(jù)進行合并。根據(jù)WHERE子句中的條件表達式是等還是不等式，可以把連接查詢分為等式連接和不等式連接。

5)    子查詢

如果某一個SELECT命令（查詢1）出現(xiàn)在另一個SQL命令（查詢2）的一個子句中，則稱查詢1是查詢2的子查詢。

 
3.     基本數(shù)據(jù)類型（NUMBER,VARCHAR2,DATE） 
 ORACEL支持下列內(nèi)部數(shù)據(jù)類型：

l       VARCHAR2 變長字符串，最長為2000字符。

l       NUMBER   數(shù)值型。

l       LONG      變長字符數(shù)據(jù)，最長為2G字節(jié)。

l       DATE       日期型。

l       RAW       二進制數(shù)據(jù)，最長為255字節(jié)。

l       LONG RAW  變長二進制數(shù)據(jù)，最長為2G字節(jié)。

l       ROWID      二六進制串，表示表的行的唯一地址。

l       CHAR       定長字符數(shù)據(jù)，最長為255。

 
4.     常用函數(shù)用法：
     一個函數(shù)類似于一個算符，它操作數(shù)據(jù)項，返回一個結(jié)果。函數(shù)在格式上不同于算符，它個具有變元，可操作0個、一個、二個或多個變元，形式為：

函數(shù)名（變元，變元，…）

函數(shù)具有下列一般類形：

l         單行函數(shù)

l         分組函數(shù)

1）單行函數(shù)對查詢的表或視圖的每一行返回一個結(jié)果行。它有數(shù)值函數(shù)，字符函數(shù)，日期函數(shù)，轉(zhuǎn)換函數(shù)等。

2）分組函數(shù)返回的結(jié)果是基于行組而不是單行，所以分組函數(shù)不同于單行函數(shù)。在許多分組函數(shù)中可有下列選項：

l         DISTRNCT 該選項使分組函數(shù)只考慮變元表達式中的不同值。

l         ALL該選項使分組函數(shù)考慮全部值，包含全部重復(fù)。

全部分組函數(shù)（除COUNT（*）外）忽略空值。如果具有分組函數(shù)的查詢，沒有返回行或只有空值（分組函數(shù)的變元取值的行），則分組函數(shù)返回空值。

l       單行函數(shù)

1)        數(shù)字函數(shù)

ABS  取絕對值  POWER 乘方   LN   10為底數(shù)取冪

SQRT 平方根     EXP       e的n次乘方  LOG(m,n)   m為底數(shù)n取冪        

數(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ù)值如果trunc(日期), 確省的是去掉時間

 

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

MONTHS_BETWEEN(d1,d2)               兩個日期值間的月份,d1<d2 返回負數(shù)

NEXT_DAY(d)                 返回日期值下一天的日期

SYSDATE                  當(dāng)前的系統(tǒng)時間

                      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)                        用于計算或者比較大小

 

5)        轉(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標準,從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)

DD         日   DAY             DY 

HH24            小時      HH12            HH 

MI         分鐘     

SS          秒

如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參數(shù) NL2005-9-26_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX環(huán)境變量或者NT的注冊表里的設(shè)置 NL2005-9-26_FORMAT=yyyy-mm-dd hh24:mi:ss

 

6)        轉(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

 

7)        轉(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')

 

8)        輸入字符,返回數(shù)字的函數(shù)

instr(c1,c2)     字符c2出現(xiàn)在c1的位置, 不出現(xiàn), 返回0, 常用于模糊查詢

length(c)  按數(shù)據(jù)庫的字符集,算出字符c的長度,跟數(shù)據(jù)庫的字符集有關(guān), 一個漢字長度為1

 

9)        有邏輯比較的函數(shù)NVL(EXPR1, EXPR2)函數(shù)

解釋:      IF EXPR1=NULL      RETURN EXPR2

        ELSE    RETURN EXPR1

10)    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')

l        集合函數(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)                     取合計值

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;
5. 數(shù)據(jù)操縱語言命令：
數(shù)據(jù)庫操縱語言（DML）命令用于查詢和操縱模式對象中的數(shù)據(jù)，它不隱式地提交當(dāng)前事務(wù)。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面簡單介紹一下：

1)        UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：S     QL>UPDATE EMP

            SET JOB =’MANAGER’

             WHERE ENAME=’MAPTIN’;

              SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的數(shù)據(jù)庫是EMP,并用WHERE子句限制了只對名字(ENAME)為’MARTIN’的職工的數(shù)據(jù)進行修改,SET子句則說明修改的方式,即把’MARTION’的工作名稱(JOB)改為’MARAGER’.

 

2)        INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如：SQL>SELECT INTO DEPT（DNAME， DEPTNO）

                     VALUES （‘ACCOUNTING’，10）

 

3)        DELETE FROM tablename WHERE {conditions};

例如：SQL>DELETE FROM EMP

                     WHERE EMPNO = 7654;

DELETE命令刪除一條記錄,而且DELETE命令只能刪除整行,而不能刪除某行中的部分數(shù)據(jù).

 

 

4)        事務(wù)控制命令

提交命令(COMMIT):可以使數(shù)據(jù)庫的修改永久化.設(shè)置AUTOCOMMIT為允許狀態(tài):SQL >SET AUTOCOMMIT ON;

回滾命令(ROLLBACK):消除上一個COMMIT命令后的所做的全部修改,使得數(shù)據(jù)庫的內(nèi)容恢復(fù)到上一個COMMIT執(zhí)行后的狀態(tài).使用方法是:

SQL>ROLLBACK;

 
二.Oracle擴展PL/SQL簡介1. PL/SQL概述。
PL/SQL是Oracle對SQL規(guī)范的擴展，是一種塊結(jié)構(gòu)語言，即構(gòu)成一個PL/SQL程序的基本單位（過程、函數(shù)和無名塊）是邏輯塊，可包含任何數(shù)目的嵌套了快。這種程序結(jié)構(gòu)支持逐步求精方法解決問題。一個塊（或子塊）將邏輯上相關(guān)的說明和語句組合在一起，其形式為：

DECLARE

---說明

BEGIN

---語句序列

EXCEPTION

---例外處理程序

END；

它有以下優(yōu)點:

l        支持SQL;

l        生產(chǎn)率高;

l        性能好;

l        可稱植性;

l        與ORACLE集成.

 
2. PL/SQL體系結(jié)構(gòu)
PL/SQL運行系統(tǒng)是種技術(shù)，不是一種獨立產(chǎn)品，可認為這種技術(shù)是PL/SQL塊和子程序的一種機，它可接收任何有效的PL/SQL塊或子程序。如圖所示：

 

 


PL/SQL塊

              PL/SQL機

 

                     

PL/SQL塊

過程性語句

執(zhí)行器

SQL語句
 

 

 

 

 

 

 

 

 

 

 

PL/SQL機可執(zhí)行過程性語句，而將SQL語句發(fā)送到ORACLE服務(wù)器上的SQL語句執(zhí)行器。在ORACLE預(yù)編譯程序或OCI程序中可嵌入無名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/SQL塊（子程序）可單獨編譯，永久地存儲在數(shù)據(jù)庫中，準備執(zhí)行。

 

 
3. PL/SQL基礎(chǔ)：
PL/SQL有一字符集、保留字、標點、數(shù)據(jù)類型、嚴密語法等，它與SQL有相同表示，現(xiàn)重點介紹。

1)        數(shù)據(jù)類型：如下表所示

 

 

數(shù)據(jù)類型

子類型

純量類型

數(shù)值

BINARY_INTEGER

NATURAL,POSITIVE

NUMBER

DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符

CHAR

CHARACTER,STRING

VARCHAR2

VARCHAR

LONG

 

LONG RAW

 

RAW

 

RAWID

 

邏輯

BOOLEAN

 

日期

DATE

 

組合

類型

記錄

RECORD

 

表

TABLE

 

 

2)        變量和常量

在PL/SQL程序中可將值存儲在變量和常量中，當(dāng)程序執(zhí)行時，變量的值可以改變，而常量的值不能改變。

 

3)        程序塊式結(jié)構(gòu)：

DECLARE

        變量說明部分；

BEGIN

        執(zhí)行語句部分；

[EXCEPTION

        例外處理部分；]

END;

 
4. 控制語句：
分支語句：

IF condition THEN

        Sequence_of_statements;

END IF;

 

IF condition THEN

        Sequence_of_statement1;

ELSE

        Sequence_of_statement2;

END IF;

 

IF condition1 THEN

        Sequence_of_statement1;

ELSIF condition2 THEN

        Sequence_of_statement2;

ELSIF condition3 THEN

        Sequence_of_statement3;

END IF;

 

 
5. 循環(huán)語句：
LOOP

              Sequence_of_statements;

              IF condition THEN

                     EXIT;

              END IF;

END LOOP;

      WHILE condition LOOP

              Sequence_of_statements;

END LOOP;

 

FOR counter IN lower_bound..higher_bound LOOP

              Sequence_of_statements;

END LOOP;

 
6. 子程序：
存儲過程：

              CREATE PROCEDURE 過程名 (參數(shù)說明1，參數(shù)說明2， 。。。) IS

                     [局部說明]

              BEGIN

                     執(zhí)行語句；

              END 過程名；

 

存儲函數(shù)：

              CREATE FUNCTION 函數(shù)名 (參數(shù)說明1，參數(shù)說明2， 。。。)

RETURN 類型 IS

                     [局部說明]

              BEGIN

                     執(zhí)行語句；

                     END 函數(shù)名；
三.Decode()函數(shù)使用技巧1. 含義解釋：
  DECODE(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)

該函數(shù)的含義如下：
IF 條件=值1 THEN
　　　　RETURN(翻譯值1)
ELSIF 條件=值2 THEN
　　　　RETURN(翻譯值2)
　　　　......
ELSIF 條件=值n THEN
　　　　RETURN(翻譯值n)

ELSE
　　　　RETURN(缺省值)
END IF
2. 使用方法：
1)            比較大小
select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; --取較小值
sign()函數(shù)根據(jù)某個值是0、正數(shù)還是負數(shù)，分別返回0、1、-1

例如：
變量1=10，變量2=20
則sign(變量1-變量2)返回-1，decode解碼結(jié)果為“變量1”，達到了取較小值的目的。

表、視圖結(jié)構(gòu)轉(zhuǎn)化
現(xiàn)有一個商品銷售表sale，表結(jié)構(gòu)為：
month　　　 char(6)　　　　　 --月份
sell　　　　number(10,2)　　　--月銷售金額

現(xiàn)有數(shù)據(jù)為：
200001　　1000
200002　　1100
200003　　1200
200004　　1300
200005　　1400
200006　　1500
200007　　1600
200101　　1100
200202　　1200
200301　　1300

想要轉(zhuǎn)化為以下結(jié)構(gòu)的數(shù)據(jù)：
year　　　char(4)　　　　　 --年份
month1　　number(10,2)　　　--1月銷售金額
month2　　number(10,2)　　　--2月銷售金額
month3　　number(10,2)　　　--3月銷售金額
month4　　number(10,2)　　　--4月銷售金額
month5　　number(10,2)　　　--5月銷售金額
month6　　number(10,2)　　　--6月銷售金額
month7　　number(10,2)　　　--7月銷售金額
month8　　number(10,2)　　　--8月銷售金額
month9　　number(10,2)　　　--9月銷售金額
month10　　number(10,2)　　　--10月銷售金額
month11　　number(10,2)　　　--11月銷售金額
month12　　number(10,2)　　　--12月銷售金額

結(jié)構(gòu)轉(zhuǎn)化的SQL語句為：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
　　　　select
　　　　substrb(month,1,4),
　　　　sum(decode(substrb(month,5,2),'01',sell,0)),
　　　　sum(decode(substrb(month,5,2),'02',sell,0)),
　　　　sum(decode(substrb(month,5,2),'03',sell,0)),
　　　　sum(decode(substrb(month,5,2),'04',sell,0)),
　　　　sum(decode(substrb(month,5,2),'05',sell,0)),
　　　　sum(decode(substrb(month,5,2),'06',sell,0)),
　　　　sum(decode(substrb(month,5,2),'07',sell,0)),
　　　　sum(decode(substrb(month,5,2),'08',sell,0)),
　　　　sum(decode(substrb(month,5,2),'09',sell,0)),
　　　　sum(decode(substrb(month,5,2),'10',sell,0)),
　　　　sum(decode(substrb(month,5,2),'11',sell,0)),
　　　　sum(decode(substrb(month,5,2),'12',sell,0))
　　　　from sale
　　　　group by substrb(month,1,4);
四.NULL 使用詳解1. 含義解釋：
問：什么是NULL？
答：在我們不知道具體有什么數(shù)據(jù)的時候，也即未知，可以用NULL，我們稱它為空，ORACLE中，含有空值的表列長度為零。
ORACLE允許任何一種數(shù)據(jù)類型的字段為空，除了以下兩種情況：
1、主鍵字段（primary key），
2、定義時已經(jīng)加了NOT NULL限制條件的字段

說明：
1、等價于沒有任何值、是未知數(shù)。
2、NULL與0、空字符串、空格都不同。
3、對空值做加、減、乘、除等運算操作，結(jié)果仍為空。
4、NULL的處理使用NVL函數(shù)。
5、比較時使用關(guān)鍵字用“is null”和“is not null”。
6、空值不能被索引，所以查詢時有些符合條件的數(shù)據(jù)可能查不出來，count(*)中，用nvl(列名,0)處理后再查。
7、排序時比其他數(shù)據(jù)都大（索引默認是降序排列，小→大），所以NULL值總是排在最后。
2. 使用方法：
SQL> select 1 from dual where null=null;

 

沒有查到記錄

 

SQL> select 1 from dual where null='';

 

沒有查到記錄

 

SQL> select 1 from dual where ''='';

 

沒有查到記錄

 

SQL> select 1 from dual where null is null;

 

        1

---------

        1

 

SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

 

        1

---------

        1

 

對空值做加、減、乘、除等運算操作，結(jié)果仍為空。

SQL> select 1+null from dual;

SQL> select 1-null from dual;

SQL> select 1*null from dual;

SQL> select 1/null from dual;

 

 

查詢到一個記錄.

 

注：這個記錄就是SQL語句中的那個null

 

設(shè)置某些列為空值

update table1 set 列1=NULL where 列1 is not null;

 

 

現(xiàn)有一個商品銷售表sale，表結(jié)構(gòu)為：

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月銷售金額

 

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

insert into sale(month) values('200009');（注意：這條記錄的sell值為空）

commit;

共輸入12條記錄

 

SQL> select * from sale where sell like '%';

 

MONTH       SELL

------ ---------

200001      1000

200002      1100

200003      1200

200004      1300

200005      1400

200006      1500

200007      1600

200101      1100

200202      1200

200301      1300

200008      1000

 

查詢到11記錄.

 

結(jié)果說明：

查詢結(jié)果說明此SQL語句查詢不出列值為NULL的字段

此時需對字段為NULL的情況另外處理。

SQL> select * from sale where sell like '%' or sell is null;

SQL> select * from sale where nvl(sell,0) like '%';

 

MONTH       SELL

------ ---------

200001      1000

200002      1100

200003      1200

200004      1300

200005      1400

200006      1500

200007      1600

200101      1100

200202      1200

200301      1300

200008      1000

200009

 

查詢到12記錄.

 

Oracle的空值就是這么的用法，我們最好熟悉它的約定，以防查出的結(jié)果不正確。
五.如何查找、刪除表中重復(fù)的記錄1. 問題提出：
當(dāng)我們想要為一個表創(chuàng)建唯一索引時，如果該表有重復(fù)的記錄，則無法創(chuàng)建成功。
2. 方法原理：
1)       Oracle中，每一條記錄都有一個rowid，rowid在整個數(shù)據(jù)庫中是唯一的，
　 rowid確定了每條記錄是在ORACLE中的哪一個數(shù)據(jù)文件、塊、行上。

2)       在重復(fù)的記錄中，可能所有列的內(nèi)容都相同，但rowid不會相同，所以只要確定出重復(fù)記錄中
　 那些具有最大rowid的就可以了，其余全部刪除。

3)       以下語句用到了3項技巧：rowid、子查詢、別名。
3. 實現(xiàn)方法：
SQL> create table a (

  2  bm char(4),            --編碼

  3  mc varchar2(20)               --名稱

  4  )

  5  /

 

表已建立.

 

SQL> insert into a values('1111','1111');

SQL> insert into a values('1112','1111');

SQL> insert into a values('1113','1111');

SQL> insert into a values('1114','1111');

 

SQL> insert into a select * from a;

 

插入4個記錄.

 

SQL> commit;

 

完全提交.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

------------------ ---- -------

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

 

查詢到8記錄.

 

 

查出重復(fù)記錄

SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

ROWID              BM   MC

------------------ ---- --------------------

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

 

刪除重復(fù)記錄

SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

刪除4個記錄.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

------------------ ---- --------------------

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111
六.如何正確利用Rownum來限制查詢所返回的行數(shù)1. 含義解釋：
1)       rownum是oracle系統(tǒng)順序分配為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽字段可以用于限制查詢返回的總行數(shù)。

2)       rownum不能以任何基表的名稱作為前綴。
2. 使用方法：
現(xiàn)有一個商品銷售表sale，表結(jié)構(gòu)為：

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月銷售金額

 

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

commit;

 

SQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回記錄條數(shù)的地方，保證不出錯，如：隱式游標）

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        1 200001      1000

 

SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到記錄）

 

沒有查到記錄

 

SQL> select rownum,month,sell from sale where rownum>5;

（由于rownum是一個總是從1開始的偽列，Oracle 認為這種條件不成立，查不到記錄）

 

 

沒有查到記錄

 

只返回前3條紀錄

SQL> select rownum,month,sell from sale where rownum<4;

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        1 200001      1000

        2 200002      1100

        3 200003      1200

 

 

如何用rownum實現(xiàn)大于、小于邏輯？（返回rownum在4—10之間的數(shù)據(jù)）（minus操作，速度會受影響）

SQL> select rownum,month,sell from sale where rownum<10

  2  minus

  3  select rownum,month,sell from sale where rownum<5;

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        5 200005      1400

        6 200006      1500

        7 200007      1600

        8 200101      1100

        9 200202      1200

 

想按日期排序，并且用rownum標出正確序號（有小到大）

SQL> select rownum,month,sell from sale order by month;

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        1 200001      1000

        2 200002      1100

        3 200003      1200

        4 200004      1300

        5 200005      1400

        6 200006      1500

        7 200007      1600

       11 200008      1000

        8 200101      1100

        9 200202      1200

       10 200301      1300

 

查詢到11記錄.

 

可以發(fā)現(xiàn)，rownum并沒有實現(xiàn)我們的意圖，系統(tǒng)是按照記錄入庫時的順序給記錄排的號，rowid也是順序分配的

 

SQL> select rowid,rownum,month,sell from sale order by rowid;

 

ROWID                 ROWNUM MONTH       SELL

------------------ --------- ------ ---------

000000E4.0000.0002         1 200001      1000

000000E4.0001.0002         2 200002      1100

000000E4.0002.0002         3 200003      1200

000000E4.0003.0002         4 200004      1300

000000E4.0004.0002         5 200005      1400

000000E4.0005.0002         6 200006      1500

000000E4.0006.0002         7 200007      1600

000000E4.0007.0002         8 200101      1100

000000E4.0008.0002         9 200202      1200

000000E4.0009.0002        10 200301      1300

000000E4.000A.0002        11 200008      1000

 

查詢到11記錄.

 

正確用法，使用子查詢

SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        1 200001      1000

        2 200002      1100

        3 200003      1200

        4 200004      1300

        5 200005      1400

        6 200006      1500

        7 200007      1600

        8 200008      1000

        9 200101      1100

       10 200202      1200

       11 200301      1300

 

按銷售金額排序，并且用rownum標出正確序號（有小到大）

SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;

 

   ROWNUM MONTH       SELL

--------- ------ ---------

        1 200001      1000

        2 200008      1000

        3 200002      1100

        4 200101      1100

        5 200003      1200

        6 200202      1200

        7 200004      1300

        8 200301      1300

        9 200005      1400

       10 200006      1500

       11 200007      1600

 

查詢到11記錄.

 

利用以上方法，如在打印報表時，想在查出的數(shù)據(jù)中自動加上行號，就可以利用rownum。

 

返回第5—9條紀錄，按月份排序

SQL> select * from (select rownum row_id ,month,sell

  2  from (select month,sell from sale group by month,sell))

  3  where row_id between 5 and 9;

 

    ROW_ID MONTH        SELL

---------- ------ ----------

         5 200005       1400

         6 200006       1500

         7 200007       1600

         8 200008       1000

         9 200101       1100