北大青鳥oracle學(xué)習(xí)資料TP2

1、第二章SQL 查詢和 SQL 函數(shù) Oracle 網(wǎng)絡(luò)配置Oracle基本用戶管理Oracle 中用于訪問數(shù)據(jù)庫的主要查詢工具有 SQL*Plus、iSQL*Plus 和 PL/SQL Oracle 企業(yè)管理器是用于管理、診斷和調(diào)整多個數(shù)據(jù)庫的工具回顧本章任務(wù)使用SQL執(zhí)行數(shù)據(jù)查詢控制數(shù)據(jù)的對象權(quán)限 34技能目標(biāo)了解 Oracle 數(shù)據(jù)類型 了解數(shù)據(jù)定義語言和數(shù)據(jù)控制語言掌握 SQL 操作符和 SQL 函數(shù)5SQL 簡介 2-1SQL 是 Structured Query Language（結(jié)構(gòu)化查詢語言）的首字母縮寫詞SQL 是數(shù)據(jù)庫語言，Oracle 使用該語言存儲和檢索信息表是主要的數(shù)據(jù)

2、庫對象，用于存儲數(shù)據(jù)通過 SQL可以實(shí)現(xiàn)與 Oracle 服務(wù)器的通信SELECT ename FROM Emp;發(fā)送 SQL 查詢Oracle 服務(wù)器enameBLAKESMITH ALLEN DAVID MARTIN發(fā)送命令輸出到用戶端用戶6SQL 簡介 2-2SQL 支持下列類別的命令：數(shù)據(jù)定義語言（DDL）數(shù)據(jù)操縱語言（DML）事務(wù)控制語言（TCL）數(shù)據(jù)控制語言（DCL）數(shù)據(jù)定義語言 CREATEALTERDROP數(shù)據(jù)操縱語言 INSERTSELECTDELETEUPDATE事務(wù)控制語言COMMITSAVEPOINTROLLBACK數(shù)據(jù)控制語言 GRANTREVOKE7Oracle 數(shù)

3、據(jù)類型 5-1創(chuàng)建表時，必須為各個列指定數(shù)據(jù)類型以下是 Oracle常用 數(shù)據(jù)類型的類別：數(shù)據(jù)類型字符數(shù)值日期時間8Oracle 數(shù)據(jù)類型5-2字符數(shù)據(jù)類型CHARVARCHAR2LONG當(dāng)需要固定長度的字符串時，使用 CHAR 數(shù)據(jù)類型。CHAR 數(shù)據(jù)類型存儲字母數(shù)字值。CHAR 數(shù)據(jù)類型的列長度可以是 1 到 2000 個字節(jié)。VARCHAR2數(shù)據(jù)類型支持可變長度字符串VARCHAR2數(shù)據(jù)類型存儲字母數(shù)字值VARCHAR2數(shù)據(jù)類型的大小在1至4000個字節(jié)范圍內(nèi)LONG 數(shù)據(jù)類型存儲可變長度字符數(shù)據(jù)LONG 數(shù)據(jù)類型最多能存儲 2GB9Oracle 數(shù)據(jù)類型 5-3數(shù)值數(shù)據(jù)類型可以存儲正數(shù)

4、，負(fù)數(shù)，零，定點(diǎn)數(shù)和浮點(diǎn)數(shù)浮點(diǎn)數(shù)最高精度為 38 位數(shù)值數(shù)據(jù)類型的聲明語法：NUMBER ( p, s)P表示精度，S表示小數(shù)點(diǎn)的位數(shù)日期時間數(shù)據(jù)類型存儲日期和時間值，包括年、月、日，小時、分鐘、秒主要的日期時間類型有：DATE - 存儲日期和時間部分，精確到整個的秒TIMESTAMP - 存儲日期、時間和時區(qū)信息，秒值精確到小數(shù)點(diǎn)后6位10數(shù)據(jù)定義語言數(shù)據(jù)定義語言用于改變數(shù)據(jù)庫結(jié)構(gòu)，包括創(chuàng)建、更改和刪除數(shù)據(jù)庫對象用于操縱表結(jié)構(gòu)的數(shù)據(jù)定義語言命令有：CREATE TABLEALTER TABLETRUNCATE TABLEDROP TABLE創(chuàng)建表創(chuàng)建表命令用途語法：CREATE TABLE

5、schema.table (column datatype,column datatype ,)11SQL CREATE TABLE vendor_master(vencode VARCHAR2(5),venname VARCHAR2(20),venadd1 VARCHAR2(20),venadd2 VARCHAR2(20),venadd3 VARCHAR2(20);表命名規(guī)則表名應(yīng)該嚴(yán)格遵循下列命名規(guī)則1.表名首字符應(yīng)該為字母2.不能使用oracle保留字來為表命名3.表名的最大長度為30個字符4.同一用戶模式下的不同表不能具有相同的名稱5.可以使用下劃線，數(shù)字和字母，但不能使用空格和單引號

6、6.Oracle中的表名（還有列名，用戶名和其他對象名）不區(qū)分大小寫，系統(tǒng)會自動轉(zhuǎn)換成大寫12更改表命令用途：有以下幾種情況添加新列更改列的數(shù)據(jù)類型或數(shù)據(jù)類型的寬度添加或刪除完整性約束條件刪除現(xiàn)有列13更改表語法ALTER TABLE MODIFY (column definition);ALTER TABLE ADD (column definition);ALTER TABLE DROP COLUMN column;14SQLALTER TABLE vendor_master MODIFY(venname varchar2(25); SQLALTER TABLE vendor_master

7、 ADD (tel_no varchar2(12),tngst_no_number(12); SQLALTER TABLE vendor_master DROP COLUMN tngst_no刪除表用途語法DROP TABLE15SQLDROP TABLE vendor_master;16數(shù)據(jù)控制語言數(shù)據(jù)控制語言為用戶提供權(quán)限控制命令 用于權(quán)限控制的命令有：GRANT 授予權(quán)限REVOKE 撤銷已授予的權(quán)限SQL GRANT SELECT, UPDATE ON order_master TO MARTIN;SQL GRANT UPDATE(qty_hand, re_level) ON item

8、file TO MARTIN;SQL GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;SQL REVOKE SELECT, UPDATE ON order_master FROM MARTIN;17SQL 操作符SQL 操作符集合操作符邏輯操作符比較操作符算術(shù)操作符連接操作符Oracle 支持的 SQL 操作符分類如下：18算術(shù)操作符算術(shù)操作符用于執(zhí)行數(shù)值計(jì)算可以在SQL語句中使用算術(shù)表達(dá)式，算術(shù)表達(dá)式由數(shù)值數(shù)據(jù)類型的列名、數(shù)值常量和連接它們的算術(shù)操作符組成算術(shù)操作符包括加(+)、減(-)、乘(*)、除(/)SQL SEL

9、ECT itemdesc, max_level - qty_hand avble_limit FROM itemfile WHERE p_category=spares;SQL SELECT itemdesc, itemrate*(max_level - qty_hand) FROM itemfile WHERE p_category=spares;19比較操作符 比較操作符用于比較兩個表達(dá)式的值比較操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等SQL SELECT itemdesc, re_level FROM itemfile WHERE qty_ha

10、nd SELECT orderno FROM order_master WHERE del_date IN (06-1月-05,05-2月-05);SQL SELECT vencode,venname,tel_no FROM vendor_master WHERE venname LIKE j_s;20邏輯操作符SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date SELECT orderno FROM order_master UNION SELECT orderno FROM order_detail;UNION

11、ALL操作符合并兩個查詢選定的所有行，包括重復(fù)的行SQLSELECT orderno FROM order_master UNION ALL SELECT orderno FROM order_detail;SQLSELECT qty_hand,max_level FROM itemfile UNION SELECT qty_ord,qty_deld FROM order_detail ORDER BY 2;22集合操作符集合操作符將兩個查詢的結(jié)果組合成一個結(jié)果集合操作符UNIONUNION ALLINTERSECTMINUSINTERSECT 操作符只返回兩個查詢都有的行SQLSELECT

12、orderno FROM order_master INTERSECT SELECT orderno FROM order_detail;MINUS操作符只返回由第一個查詢選定但是沒有被第二個查詢選定的行，也就是在第一個查詢結(jié)果中排除在第二個查詢結(jié)果中出現(xiàn)的行SQLSELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail;23連接操作符連接操作符用于將多個字符串或數(shù)據(jù)值合并成一個字符串SQL SELECT (供應(yīng)商|venname| 的地址是 |venadd1| |venadd2 | |venadd3) ad

13、dress FROM vendor_master WHERE vencode=V001;通過使用連接操作符可以將表中的多個列合并成邏輯上的一行列24操作符的優(yōu)先級SQL 操作符的優(yōu)先級從高到低的順序是：算術(shù)操作符 -最高優(yōu)先級連接操作符比較操作符NOT 邏輯操作符AND 邏輯操作符OR 邏輯操作符 -最低優(yōu)先級 25SQL 函數(shù)Oracle 提供一系列用于執(zhí)行特定操作的函數(shù)SQL 函數(shù)帶有一個或多個參數(shù)并返回一個值以下是SQL函數(shù)的分類：SQL 函數(shù)標(biāo)量函數(shù)分組函數(shù)26標(biāo)量函數(shù)分類標(biāo)量（單行）函數(shù)對于從表中查詢的每一行只返回一個值可以出現(xiàn)在 SELECT 子句中和 WHERE 子句中 標(biāo)量函數(shù)

14、可以大致劃分為：日期函數(shù)數(shù)字函數(shù)字符函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)27日期函數(shù)日期函數(shù)對日期值進(jìn)行運(yùn)算，并生成日期數(shù)據(jù)類型或數(shù)值類型的結(jié)果日期函數(shù)包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT日期函數(shù)ADD_MONTHS函數(shù)返回給指定的日期加上指定的月數(shù)后的日期值。格式為ADD_MONTHS(d,n),其中d是日期，n表示月數(shù)28SQLSELECT del_date,ADD_MONTHS(del_date,2) FROM order_master;日期函數(shù)MONTHS_BETWEEN函數(shù)返回兩個日期之間的月數(shù)格式為MONTHS_B

15、ETWEEN（d1，d2）詳細(xì)：d1和d2是日期，輸出結(jié)果為一個數(shù)值。如果d1晚于d2，則結(jié)果為正數(shù)；否則為負(fù)數(shù)。如果d1和d2為某月中的同一天或月份的最后一天，則結(jié)果始終為整數(shù)；否則oracle將根據(jù)一個月31天來計(jì)算結(jié)果的小數(shù)部分，并考慮d1和d2時間部分的差。LAST_DAY函數(shù)返回指定日期當(dāng)月的最后一天的日期值格式為LAST_DAY(d)29SQLSELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;日期函數(shù)ROUND函數(shù)返回日期值，此日期四舍五入為格式模型指定的單位。格式為ROUND(d，fmt)詳細(xì)：d是日期，fmt是格式模型。fmt是一個可選項(xiàng)，日

16、期默認(rèn)舍入為最靠近的那一天。如果指定格式為年”YEAR”,則舍入到年的開始，即1月1日；如果格式為月”MONTH”,則舍入到月的第一日；如果格式為周”DAY”,則舍入到最靠近的星期日。30SQLSELECT del_date,ROUND(del_date,YEAR) FROM order_master WHERE vencoe=V001;SQLSELECT ROUND(DATE2005-5-10,MONTH),ROUND(DATE2005-6-16,MONTH) FROM dual;日期函數(shù)NEXT_DAY函數(shù)返回指定的下一個星期幾的日期。格式為NEXT_DAY(d,day)詳細(xì)：d是日期，d

17、ay指周內(nèi)任何一天。31SQLSELECT NEXT_DAY(SYSDATE,星期二) FROM dual;日期函數(shù)TRUNC函數(shù)將指定日期截?cái)酁橛筛袷侥Ｐ椭付ǖ膯挝坏娜掌?，與ROUND函數(shù)不同的是它只舍不入格式為TRUNC(d,fmt)它使用與ROUND函數(shù)相同的格式模型。如果不指定fmt，則日期被截?cái)酁樘煸敿?xì)：d是日期時間表達(dá)式，fmt是要提取的部分的格式。格式的取值可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,注意此處的格式不用使用單引號。32SQLSELECT TRUNC(sysdate,YEAR) FROM dual;SQLSELECT TRUNC(sys

18、date,DAY) FROM dual;SQLSELECT TRUNC(sysdate) FROM dual;日期函數(shù)EXTRACT函數(shù)提取日期時間類型中都特定部分。格式為EXTRACT(fmt FROM d)詳細(xì)：d是日期時間表達(dá)式，fmt是要提取的部分的格式。格式的取值可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,注意此處的格式不用使用單引號。33SQLSELECT EXTRACT (YEAR FROM SYSDATE) FROM dual;34字符函數(shù) 2-1 函數(shù) 輸入 輸出Initcap(char) Select initcap(hello) from d

19、ual;Hello Lower(char) Select lower(FUN) from dual;fun Upper(char) Select upper(sun) from dual;SUN Ltrim(char,set) Select ltrim( xyzadams,xyz) from dual; adamsRtrim(char,set) Select rtrim(xyzadams,ams) from dual; xyzad Translate(char, from, to) Select translate(jack,j ,b) from dual; back Replace(char

20、, searchstring,rep string) Select replace(jack and jue ,j,bl) from dual;black and blue Instr (char, m, n) Select instr (worldwide,d) from dual; 5 Substr (char, m, n) Select substr(abcdefg,3,2) from dual; cd Concat (expr1, expr2) Select concat (Hello, world) from dual; Hello world字符函數(shù)字符函數(shù)接受字符輸入并返回字符或

21、數(shù)值35字符函數(shù) 2-2SQL SELECT CHR(67) FROM dual;以下是一些其它的字符函數(shù)：CHR和ASCIILPAD和RPADTRIMLENGTHDECODESQL SELECT LPAD(function,15,=) FROM dual;SQL SELECT TRIM(9 from 9999876789999) FROM dual;SQL SELECT LENGTH(frances) FROM dual;SQL SELECT vencode, DECODE(venname,frances,Francis) name FROM vendor_master WHERE venc

22、ode=v001;36數(shù)字函數(shù)數(shù)字函數(shù)接受數(shù)字輸入并返回?cái)?shù)值結(jié)果 函數(shù) 輸入 輸出Abs(n) Select abs(-15) from dual; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Cosh(n) Select cosh(0) from dual; 1Floor(n) Select floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select m

23、od(10,3) from dual; 1Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n)Select sign(-30) from dual;-1數(shù)字函數(shù)37轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型常用的轉(zhuǎn)換函數(shù)有：TO_CHARTO_DATETO_NUMBERSELECT TO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SS)FROM dual;SELECT TO_CHAR(itemrate,C99999) FROM itemfile;SELECT TO_DATE(2005-12-06 , yyyy-mm-dd)FROM dual;SELECT TO_NUMBER(100) FROM dual;38其它函數(shù)以下是幾個用來轉(zhuǎn)換空值的函數(shù)：NVLNVL2NULLIFSELECT itemdesc, NVL(re_level,0) FROM itemfile;SELECT itemdesc, NVL2(re_level,re_level,max_level)