Introduction to Oracle9iSQL.docx

Introduction to Oracle9iSQL.docx

《Introduction to Oracle9iSQL.docx》由会员分享，可在线阅读，更多相关《Introduction to Oracle9iSQL.docx（77页珍藏版）》请在冰豆网上搜索。

IntroductiontoOracle9iSQL

WritingBasicSQLSelectStatements[撰寫簡單的SQLSelect敘述]

[]ListthecapabilitiesofSQLSELECTstatements描述一個SQLSELECT敘述

[]ExecuteabasicSELECTstatement執行一個簡單的SELECT敘述

[]DifferentiatebetweenSQLstatementsandiSQL*Pluscommands瞭解SQL與iSQL*Plus命令間的差異

RestrictingandSortingData[限制及排序資料]

[]Limittherowsretrievedbyaquery限制請求取回的列資料

[]Sorttherowsretrievedbyaquery排序請求取回的列資料

Single-RowFunctions[單列函數]

[]DescribevarioustypesoffunctionsavailableinSQL說明SQL中各種有用的函數類型

[]Usecharacter,number,anddatefunctionsinSELECTstatement在SELECT敘述中使用字元,數字,日期函數

[]Useconversionfunctions使用轉換函數

DisplayingDatafromMultipleTables[從多表單中顯示資料]

[]WriteSELECTstatementstoaccessdatafrommorethanonetableusingequalityandnonequalityjoins

[]Viewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoins

[]Joinatabletoitselfusingaself-join

AggregatingDatausingGroupFunctions[使用群組函數來統計資料]

[]Identifytheavailablegroupfunctions分辨使用群組函數的時機

[]Usegroupfunctions使用群組函數

[]GroupdatausingtheGROUPBYclause使用GROUPBY子句來群組資料

[]IncludeorexcludegroupedrowsbyusingtheHAVINGclause

Subqueries[子查詢]

[]Describethetypesofproblemsthatsubqueriescansolve[描寫「次查詢」能夠解決的問題]

[]Definesubqueries[撰寫「次查詢」指令]

[]Listthetypesofsubqueries[辨識不同種類的「次查詢」]

[]Writesingle-rowandmultiple-rowsubqueries[撰寫「單列次查詢」與「多列次查詢」]

ProducingReadableOutputwithiSQL*Plus[在SQL*Plus產生美觀的輸出報表]

[]Producequeriesthatrequireasubstitutionvariable[使用替代變數撰寫SQL敘述句]

[]Producemorereadableoutput[產生可讀性較高的輸出結果]

[]Createandexecutescriptfiles[建立與執行指令]

ManipulatingData[資料處理動作]

[]DescribeeachDMLstatement[熟悉各種DML敘述句]

[]Insertrowsintoatable[將資料新增至表格]

[]Updaterowsinatable[更改表格既有資料]

[]Deleterowsfromatable[刪除表格資料]

[]Mergerowsinatable[合併表格資料]

[]Controltransactions[控制資料庫交易]

CreatingandManagingTables[建立與維護表格]

[]Describethemaindatabaseobjects[描述主要的資料庫物件]

[]Createtables[定義表格欄位時可使用的資料型別]

[]Describethedatatypesthatcanbeusedwhenspecifyingcolumndefinition

[定義表格欄位時可使用的資料型別]

[]Altertabledefinitions[更改表格定義]

[]Drop,renameandtruncatetables[刪除、更名與截斷表格]

IncludingConstraints[加入約束條件]

[]Describeconstraints[瞭解「約束條件」contraints的用途]

[]Createandmaintainconstraints[建立與維護約束條件]

CreatingViews[建立視觀表]

[]Describeaview[描述「視觀表」view的用途]

[]Create,alterthedefinition,anddropaview[建立、更改與移除視觀表]

[]Retrievedatathroughaview[查詢視觀表的資料]

[]Insert,updateanddeletedatathroughaview[透過視觀表執行新增、修改與刪除資料]

[]Createanduseaninlineview[建立「內嵌視觀表」（inlineview）]

[]PerformTop'N'Analysis[執行Top-N查詢]

CreatingOtherDatabaseObjects[建立其他資料物件]

[]Create,maintainandusesequences[建立、維護與使用「序列」（sequences）]

[]Createandmaintainindexes[建立與維護「索引」（index）]

[]Createprivateandpublicsynonyms[建立公用與私有「同義字」synonym]

ControllingUserAccess[使用者權限管理]

[]Createusers[建立使用者帳號]

[]CreateRolestoeasesetupandmaintenanceofthesecuritymodel

[利用「角色」來簡化資料庫安全性管理機制]

[]UsetheGRANTandREVOKEstatementstograntandrevokeobjectprivileges

[執行grant與revoke敘述來授予撤回權力]

1.WritingBasicSQLSelectStatements[撰寫簡單的SQLSelect敘述]

1-1.ListthecapabilitiesofSQLSELECTstatements描述一個SQLSELECT敘述

1-2.ExecuteabasicSELECTstatement執行一個簡單的SELECT敘述

1-3.DifferentiatebetweenSQLstatementsandiSQL*Pluscommands瞭解SQL與iSQL*Plus命令間的差異

SELECT*|{[DISTINCT]column|expression[alias],...}

FROMtable;

SQL的語句可以分多行來表示，但關鍵字與變數不可分割斷行.

DISTINCT過濾重複值

*distinct必須放置在所有column之前.

當一個欄位時,只傳回唯一值.

當二個欄位以上時,傳回欄位組合的唯一值.

Aslias欄位別名

*在SELECT選取欄位後可接as或空白，來替代欄位別名。

SQL>SELECTjob職位FROMemp;或

SQL>SELECTjob“職位”FROMemp;或

SQL>SELECTjobAS職位FROMemp;兩者結果相同

FROM表格來源

*在FROM子句中可接表格或用子查詢來替代表格.

SQL>SELECT*FROMemp;

SQL>SELECT*FROM（SELECTjobFROMemp）;

ArithmeticExpressions數學表示式

運算優先順序*/+-

SQL>SELECT120*salary+100FROMemployees;要注意salary先乘120再加100

*在使用欄位運算時，欄位值如果是CHAR型態，回傳時會自動轉型為NUMBER

SQL>CREATETABLEb（b1CHAR（10））;

SQL>INSERTINTObVALUES（‘2’）;

SQL>SELECTb1*2FROMb;success!

!

SQL>INSERTINTObVALUES（‘a’）;

SQL>SELECTb1*2FROMb;false!

!

當欄位運算遇到NULL時,則傳回NULL

SQL>SELECTNULL*2COLUMN1,123*2COLUMN2FROMdual;

COLUMN1123*2

246

ConcatenationOperator連結運算元，注意連接字串時是使用’’單引號，而欄位別名是使用””雙引號

連結運算元||用來連接兩個欄位值,其值不分number,char,date型別.

SELECTlast_name||'isa'||job_id

AS"EmployeeDetails"

FROMemployees;

比較SQLandiSQL*Plus的差異

SQL

iSQL*Plus

是一個與OracleServer通訊儲存資料的語言

可識別SQL敘述並傳送到Server

美國國際標準協會（ANSI）標準SQL

是Oracle專有執行SQL敘述的介面

在資料庫操作定義表單

在資料庫不允許操作數值

不可以將字串斷行

可以利用dash（_）來作字串斷行

不能縮短

可縮短

使用函數來執行一些資料格式化

使用指令來格式化資料

DisplayingTableStructure顯示表格結構

DESC[RIBE]tablename

傳回欄位名稱（Name）、是否存入NULL（Null?

）、資料型別（Type）

2.RestrictingandSortingData[限制及排序資料]

2-1.Limittherowsretrievedbyaquery限制請求取回的列資料

2-2.Sorttherowsretrievedbyaquery排序請求取回的列資料

SELECT*|{[DISTINCT]column|expression[alias],...}

FROMtable

[WHEREcondition（s）];

ComparisonConditions比較條件

運算元

表示意義

=

相等

>

大於

>=

大於或等於

<

小於

<=

小於或等於

<>

!

=與^=皆表示不相等

BETWEEN...AND...

包含兩個數值之間的值（相異）

IN（set）

符合清單數值中的任一筆數值

LIKE

符合部分字元

ISNULL

是一個NULL值

BETWEENcondition（BETWEENMIN_VALUEANDMAX_VALUE）

使用BETWEEN條件，要注意大小值放置順序，當大小反置，將不回傳回任何數值.

SELECTlast_name,salary

FROMemployees

WHEREsalaryBETWEEN2500AND3500;

或→WHEREsalaryBETWEEN3500AND2500;將沒有任何資料列被選取

※如果MIN_VALUE與MAX_VALUE值相等,同樣沒有任何資料列被選取

INCondition（IN（100,101,201））

SELECTemployee_id,last_name,salary,manager_id

FROMemployees

WHEREmanager_idIN（100,101,201）;

Orlast_nameIN（'Hartstein','Vargas'）;

凡符合（100,101,201）或（'Hartstein','Vargas'）值條件

LIKECondition（字圖比對方式）

%包含零個或多個字元

_只包含一個字元

SELECTlast_name

FROMemployees

WHERElast_nameLIKE'_o%';

*從employees表單取回last_name欄位，第二位字元是o的字串.

*如果需查詢包含_字符的字串時，可用\符號.

SELECTemployee_id,last_name,job_id

FROMemployees

WHEREjob_idLIKE'%SA\_%'ESCAPE'\';→注意要加ESCAPE‘\‘;

相同如果要搜尋包含SA_的字串時→selectafrombwherealike'%SA\_%'ESCAPE'_';

NULLConditions（包含NULL的欄位）

SELECTlast_name,manager_id

FROMemployees

WHEREmanager_idISNULL;←注意!

!

NULL值是用ISNULL，不是xxx=NULL

運算元

表示意義

AND

兩者同時成立傳回TRUE,否則傳回FALSE

OR

兩者其中成立傳回TRUE

NOT

如果條件是FALSE則傳回TRUE

UsingtheANDOperator

SELECTemployee_id,last_name,job_id,salary

FROMemployees

WHEREsalary>=10000

ANDjob_idLIKE'%MAN%';

AND真值表

OR真值表

NOT真值表

*注意真值表NULL結果

註記:

NOT運算元也可用於其他SQL運算元,像是BETWEEN,LIKE,及NULL.

...WHEREjob_idNOTIN（'AC_ACCOUNT','AD_VP'）

...WHEREsalaryNOTBETWEEN10000AND15000

...WHERElast_nameNOTLIKE'%A%'

...WHEREcommission_pctISNOTNULL

優先次序規則

評定次序

運算元

1

數學運算元＊／＋－

2

連接運算元||

3

比較條件>,<,=,>=,<=…etc

4

IS[NOT]NULL,LIKE,[NOT]IN

5

[NOT]BETWEEN

6

NOT邏輯條件式

7

AND邏輯條件式

8

OR邏輯條件式

SELECTlast_name,job_id,salary

FROMemployees

WHEREjob_id='SA_REP'

ORjob_id='AD_PRES'

ANDsalary>15000;

*AND比OR優先,所以先AND後再OR運算

SELECTlast_name,job_id,salary

FROMemployees

WHERE（job_id='SA_REP'

ORjob_id='AD_PRES'）

ANDsalary>15000;

*括號優先運算,然後再AND運算

ORDERBY子句

SELECTexpr

FROMtable

[WHEREcondition（s）]

[ORDERBY{column,expr}[ASC|DESC]];

–ASC:

ascendingorder遞增（這是預設值）

–DESC:

descendingorder遞減

*ORDERBY也可以使用多欄排序及代號表示,如SELECT*FROMempORDERBY3asc,4desc;

使用欄位別名來排序

SQL>SELECTemployee_id,last_name,salary*12annsal

FROMemployees

ORDERBYannsal;

注意!

!

Subquerice中不能使用orderbyclause

3.Single-RowFunctions[單列函數]

3-1.DescribevarioustypesoffunctionsavailableinSQL說明SQL中各種有用的函數類型

3-2.Usecharacter,number,anddatefunctionsinSELECTstatement在SELECT敘述中使用字元,數字,日期函數

3-3.Useconversionfunctions使用轉換函數

SQL函數的型別分為Single-Row與Multiple-Row，先看看單列函數：

•Character字串函數,像LOWER（）,UPPER（）,INITCAP,SUBSTR（）…etc.

•Number數值函數,像MAX（）,MIN（）,ROUND（）,SQRT（）,FLOOR（）,CEIL（）…etc.

•Date日期函數,像ADD_MONTHS（Date,Number）,NEXT_DAY（）,LAST_DAY（）…etc

•Conversion轉換函數,像TO_CHAR（）,TO_NUMBER（）,TO_DATE（）…etc.

•General一般函數,像NVL（）,NVL2（）,NULLIF（）,COALSECE（）,CASE,DECODE

*Multiple-Row在後面的課程將會說明,像是MAX（）,MIN（）,AVG（）,SUM（）

字串函數

函數

動作

LOWER（column|expression）

小寫轉換

UPPER（column|expression）

大寫轉換

INITCAP（column|expression）

字首轉換,即使全大寫或全小寫字串,一律轉換成字首大寫其餘小寫.

CONCAT（column1|expression1

column2|expression2）

連結字串,將兩個欄位或字串連結;相當於（||）運算元

SUBSTR（column|expression,m[,n]）

取出子字串,將欄位或字串中取出子字串,m表示起始字數,n表示取出字數

Exp.

SQL>SELECTename,SUBSTR（ename,2）,SUBSTR（ename,2,3）FROMemp;

ENAMESUBSTR（ENAME,2）SUBSTR（ENAME,2,3）

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

SMITHMITHMIT

ALLENLLENLLE

WARDARDARD

：

：

字串函數（續）

函數

動作

LENGTH（column|expression）

傳回一個NUMBER的字串字元數.

INSTR（column|expression,

’string’,[,m],[n]）

傳回string在column|expression中的位置的數字.

1.當傳入只有一個數值,也就是指m字數開始搜尋

2.當傳入兩個數值時,指從m到n之間搜尋

3.當傳入一個負數值,指從右方搜尋,正數值則從左方搜尋

LPAD（column|expression,n,

'string'）

RPAD（column|expression,n,

'string'）

1.LPAD與RPAD之間差異，在於補足字的左方還是右方.

2.n,代表字串字元總數,

3.string代表當欄位或字串字元總數不足n時,以string補足n值

4.當n>column/expression字數時,同substr只取該colmn前n個字元

TRIM（leading|trailing|both

trim_characterFROM

trim_source）

1.如果沒有其他參數,傳回前後空白去除之trim_source.

2.當傳帶入參數時,

SQL>SELECTTRIM（‘l’FROM‘lllHelloWorld!

!

llllll’）FROMDUAL;

SQL>-----------------

HelloWorld!

!

REPLACE（

text,

search_string,

replacement_string）

搜尋text字串中search_string,