Java應(yīng)用開發(fā)教程-第18章 用SQL訪問Oracle DB XE

《Java應(yīng)用開發(fā)教程——基于

JDK

9、OracleJDeveloper與Oracle

DB

XE實(shí)現(xiàn)》第18

章

用SQL訪問Oracle

DB

XE本章概要：Oracle

DB

XE的對象瀏覽器提供了對SQL命令進(jìn)行可視化操作的環(huán)境，而SQL命令器則提供了直接使用SQL命令、PL/SQL、存儲過程/函數(shù)等的命令行執(zhí)行環(huán)境。本章首先介紹SQL函數(shù)和操作符，然后介紹如何使用Oracle

DB

XE提供的SQL命令器工具實(shí)現(xiàn)SQL的數(shù)據(jù)操作功能，即對存儲在數(shù)據(jù)庫中數(shù)據(jù)的查詢、輸入、更新、刪除等操作。18.1

SQL函數(shù)Oracle數(shù)據(jù)庫的SQL主要包括以下一些函數(shù)：數(shù)值型函數(shù)字符型函數(shù)日期型函數(shù)轉(zhuǎn)換函數(shù)和聚集函數(shù)。18.1.1

數(shù)值函數(shù)數(shù)字型函數(shù)接受的是number類型的參數(shù)，而返回的是number類型的數(shù)值。Oracle數(shù)據(jù)庫中常用的SQL數(shù)值型函數(shù)，如表18.1所示。數(shù)值型函數(shù)的實(shí)例如下：ceil(16.3)——返回大于16.3的整數(shù)，結(jié)果為17。round(15.6,-1)——y為-1，從整數(shù)的個(gè)位向左算起使1位為0，即個(gè)位為0，數(shù)值四舍五入，個(gè)位6進(jìn)位，結(jié)果為20。trunc(15.6,-1)——y為-1，從整數(shù)的個(gè)位向左算起使1位為0，即個(gè)位為0，數(shù)值截尾到個(gè)位，個(gè)位5被截尾，結(jié)果為10。18.1.2

字符函數(shù)

字符型函數(shù)接受的是字符型參數(shù)，返回的是字符值。出去幾個(gè)個(gè)別函數(shù)以外，這些函數(shù)大都返回VARCGAR2類型的

值。Oracle數(shù)據(jù)庫中常用的SQL字符處理函數(shù)，如表8-2所示。例如，如下的實(shí)例：concat("您好，","北京")——結(jié)果為："您好，北京！"。substr("[I

am

a

stydent",3,2)——結(jié)果為："am"。18.1.3

日期函數(shù)日期型函數(shù)接受的是DATE類型的參數(shù)，除了Months-Between函數(shù)返回的是number類型的數(shù)值外，其他的日期函數(shù)都返回DATE類型的值。如表18-3所示。例如，如下的實(shí)例：Last_Day("6-6月-06")——結(jié)果為30-6月-06。Next_Day("10-3月-06,"星期一"）——表示06年3月10日后的期一。結(jié)果為13-3月-06。18.1.4

轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)用于在數(shù)據(jù)類型之間進(jìn)行轉(zhuǎn)換。數(shù)值型轉(zhuǎn)換為字符類型函數(shù)名稱：To_Char(num[,format])。

函數(shù)功能：將number類型的數(shù)據(jù)轉(zhuǎn)換為一個(gè)varchar2類型的數(shù)據(jù)，format為格式參數(shù)，如果沒有指定format，那么結(jié)果字符串包含和num中有效位的個(gè)數(shù)相同的字符。如果為負(fù)數(shù)，則在前面加一個(gè)減號。函數(shù)實(shí)例：To_Char(9.6)結(jié)果為"9.6"。2.日期型轉(zhuǎn)換為字符串類型函數(shù)名稱：To_Char(d[,format])。函數(shù)功能：將日期類型的數(shù)據(jù)轉(zhuǎn)換為一個(gè)varchar2類型的數(shù)據(jù)，如果沒有指定format格式串，則使用默認(rèn)的日期格式。SQL提供許多的日期格式，用戶可以用它們的組合來表示最終的輸出格式。SQL的日期格式如表18-4所示。函數(shù)實(shí)例：設(shè)當(dāng)前系統(tǒng)日期為2003年3月10日，

To_Char(SYSDATE,"YYYY"年"MM"月"DD"日，第"W"個(gè)星期，

"HH24"時(shí)")的結(jié)果為"2003年3月10日，第2個(gè)星期，08時(shí)"。3.字符串類型轉(zhuǎn)換為日期型●函數(shù)名稱：To_Date(string,format)。●函數(shù)功能：將char或varchar2類型的數(shù)據(jù)轉(zhuǎn)換為一個(gè)date類型的數(shù)據(jù)，日期的格式詳見表18-4。●函數(shù)實(shí)例：To_Date("2003-3-10","YYYY-MM-DD")的結(jié)果為10--03月03。18.1.5聚集函數(shù)

聚集函數(shù)也稱分組函數(shù)，是從一組記錄中返回的匯總信息。

Oracle數(shù)據(jù)庫中常用的SQL聚集函數(shù)如表10-5所示。下面給出聚集函數(shù)的實(shí)例：avg(s_score)——結(jié)果為s_score列的平均值。sum(s_score)——結(jié)果為s_score列值的總和。注意，在Oracle數(shù)據(jù)庫中，函數(shù)以及語句是不分大小寫的。18.2

SQL操作符SQL中涉及到的操作符主要分為4類：算術(shù)運(yùn)算符、比較操作符、謂詞操作符以及邏輯操作符。1.算數(shù)運(yùn)算符在SQL中，常用的算術(shù)運(yùn)算符由：+、-、*、/、()。2.比較操作符在SQL中，常用的比較操作符有：=、！=、<>、<、>、<=、>=。3.

謂詞操作符在S

Q

L中，常用的謂詞操作符有：I

N——屬于集合的任一成員。N

O

T

I

N——不屬于集合的任一成員。B

E

T

W

E

E

N

a

A

N

D

b——在a和b之間，包括a和b。N

O

T

B

E

T

W

E

E

N

a

A

N

D

b——不在a和b之間，也不包括a和b。E

X

I

S

T

S——總存在一個(gè)值滿足條件。L

I

K

E"[_%]s

t

r

i

n

g[_%]"——包括在指定子串內(nèi)，%將匹配零個(gè)或多個(gè)任意字符，下劃線將匹配一個(gè)任意字符。例如：L

I

K

E"s

t

u

d%"——表示如果一個(gè)字符串的前4個(gè)字符為s

t

u

d，后面為0個(gè)或任意多個(gè)字符，都滿足集合條件。L

I

K

E"s

t

u

d

_

t"——表示如果一個(gè)字符串的前4個(gè)字符為s

t

u

d，第6個(gè)字符為t，第5個(gè)字符為任意字符。都滿足集合條件。B

E

T

W

E

E

N

2

0

A

N

D

3

0——表示數(shù)值在2

0到3

0之間。4.邏輯操作符在SQL中，常用的邏輯操作符有：AND、OR、NOT。NOT可以與比較運(yùn)算符連用，表示非。例如，NOT

age>=20，表示age小于20。18.3

用SQL查詢數(shù)據(jù)用SQL查詢數(shù)據(jù)可以使用SELECT命令實(shí)現(xiàn)，該命令可以查詢數(shù)據(jù)庫中的數(shù)據(jù)，并能夠?qū)⒉樵兘Y(jié)果排序、分組以及統(tǒng)計(jì)等。它的語法格式如下：SELECT[ALL|DISTINCT]<顯示列表項(xiàng)>|

*

FROM<數(shù)據(jù)來源項(xiàng)>[WHERE<條件表達(dá)式>[GROUP

BY<分組選項(xiàng)>[HAVING<組條件表達(dá)式>]][ORDER

BY<排序選項(xiàng)>[ASC|DESC];命令中各參數(shù)的含義如下：A

L

L

|

D

I

S

T

I

N

C

T——表示兩者任選其一。其中，A

L

L表示查詢出表中所有滿足條件的記錄，是默認(rèn)選項(xiàng)，可以省略不寫。D

I

S

TI

N

CT表示去除掉輸出結(jié)果中的重復(fù)記錄。顯示列表項(xiàng)——指定查詢結(jié)果中現(xiàn)實(shí)的項(xiàng)。這個(gè)項(xiàng)可以是表中的字段、字段表達(dá)式，也可以是S

Q

L常量，各項(xiàng)之間用逗號分隔。字段表達(dá)式既可以是S

Q

L函數(shù)表達(dá)式，也可以是S

Q

L操作符連接的表達(dá)式。如果要顯示列表項(xiàng)中包含表中所有的字段，可以用“*”來代替。數(shù)據(jù)來源項(xiàng)——指定顯示列表中顯示項(xiàng)的來源，它可以是一個(gè)或多個(gè)表，各項(xiàng)之間用逗號分隔。S

E

L

E

C

T語句的基本語義是，根據(jù)W

H

E

R

E子句的條件表達(dá)式，從F

O

R

M子句指定的表或視圖中找出滿足條件的記錄，再將顯示列表項(xiàng)中顯示項(xiàng)的值列出來。在這種固定模式中，可以不要W

H

E

R

E，但必須有S

E

L

E

C

T和F

R

O

M。W

H

E

R

E<條件表達(dá)式>——指定查詢條件。查詢條件中涉及到的S

Q

L函數(shù)和S

Q

L操作符。G

R

O

U

P

B

Y<分組選項(xiàng)>——表示查詢時(shí)，可以按照某個(gè)或某些字段分組匯總，各分組選項(xiàng)之間用逗號分隔。H

A

V

I

N

G<組條件表達(dá)式>表示分組匯總時(shí)，可以根據(jù)組條件表達(dá)式選出滿足條件的組記錄。G

R

O

U

P

B

Y<排序選項(xiàng)>——表示顯示結(jié)果時(shí)，可以按照指定字段排序，各選項(xiàng)之間用逗號分隔。A

S

C表示升序，D

E

S

C表示降序，默認(rèn)值為升序。1.

單表查詢單表查詢即從一個(gè)表中查詢數(shù)據(jù)。此時(shí)，SELECT命令中FORM子句中只有一個(gè)表，而且語句中涉及的字段可以省略表名。【例18-1】查詢deparment表中的全部信息。（

1

）在Windows

OS中，在“開始”菜單中啟動(dòng)Oracle

DB

XE。再調(diào)用

Oracle

DB

11

g

XE→“入門”命令。如圖18-1所示。（5）創(chuàng)建表（

3

）輸入命令：S

E

L

E

C

T

*

F

R

O

M

D

E

P

A

R

T

M

E

N

T；，然后單擊“運(yùn)行”按鈕，則在其下面顯示查詢結(jié)果，如圖1

8-3所示?！纠?

8-2】查詢e

m

p

l

o

y

e

e表中所有雇員的I

D號、姓名、年齡、職位、薪水等信息。S

E

L

E

C

T

E

M

P

_

I

D,E

M

P

_

N

A

M

E,E

M

P

_

A

G

E,E

M

P

_

J

O

B,E

M

P

_

S

A

L

A

R

Y

f

r

o

me

m

p

l

o

y

e

e

;【例18-3】查詢employee表中雇員所屬部門的信息。select

dept_id

from

employee;DEPT_ID10202046105020303040【例1

8-4】查詢e

m

p

lo

y

e

e表中部門編號為5

0的雇員的信息?！纠?8-5】查詢employee表中部門編號為50，且年齡在

24歲以上的雇員的信息。2.

多表查詢【例18-10】查詢所有雇員的雇員號、姓名、薪水和部門名信息。SELECT

EMP_ID,

EMP_NAME,

EMP_SALARY,

DEPT_NAME

FROMEMPLOYEE,

DEPARTMENT

WHERE

EMPLOYEE.

DEPT_ID=DEPARTMENT.

DEPT_ID;3.

嵌套查詢在S

E

L

E

C

T查詢語句中嵌入S

E

L

E

C

T查詢語句，稱為嵌套查詢。嵌入的S

E

C

E

L

T查詢語句稱為子查詢。子查詢要加括號，并且與S

E

L

E

C

T語句的形式類似，也有F

R

O

M子句，以及可選擇的W

H

E

R

E、G

R

O

U

P

BY和H

A

V

IN

G子句等。子查詢是一種把查詢結(jié)果作為參數(shù)返回給另一個(gè)查詢的一種查詢。子查詢中的子句格式與S

E

L

E

C

T語句中的子句相同。用于子查詢時(shí)，它們執(zhí)行正常的功能。子查詢和S

E

L

E

C

T語句之間的區(qū)別如下：子查詢必須生成單字段數(shù)據(jù)作為其查詢結(jié)果，即必須是一個(gè)確定的項(xiàng)。如果結(jié)果為一個(gè)集合，則需要使用謂詞操作符；O

R

D

E

R

B

Y子句不能用于子查詢。子查詢的結(jié)果只是在主查詢內(nèi)部使用，對用戶是不可見的，所以對它們的任何排序都是無意義的。一般地，嵌套查詢一般的求解方法是有里向外處理，即先執(zhí)行子查詢，將子查詢的結(jié)果作為父查詢的查詢條件?！纠?8-11】查詢所薪水金額高于平均值的雇員的信息，并將顯示結(jié)果按部門編號、雇員號排列。18.4用SQL輸入數(shù)據(jù)一般地，SQL提供了3種輸入數(shù)據(jù)的方法：單表——用INSERT命令向表中輸入一行新紀(jì)錄。多行——用INSERT命令從數(shù)據(jù)庫的其他對象中選取多行數(shù)據(jù)，并將這些數(shù)據(jù)添加到表中。表間數(shù)據(jù)復(fù)制——從一個(gè)表中選擇需要的數(shù)據(jù)輸入到新表中。這種方法用于初始裝載數(shù)據(jù)庫或從其它系統(tǒng)下載來的或從許多節(jié)點(diǎn)收集來的數(shù)據(jù)。1.

單行輸入命令I(lǐng)N

S

E

R命令可以向數(shù)據(jù)庫中輸入一行數(shù)據(jù)，其命令格式如下：IN

S

E

R

T

IN

T

O<表名>[(<字段清單>)]V

A

L

U

E

S(<數(shù)值清單>);I

N

S

E

R

T為輸入關(guān)鍵字，I

N

T

O子句指定接受新數(shù)據(jù)的表及字段，V

A

L

U

E

S子句指定其數(shù)據(jù)值。字段清單和數(shù)值清單指定哪些數(shù)據(jù)進(jìn)入哪些字段，并且數(shù)值清單應(yīng)與字段清單一一對應(yīng)，清單各項(xiàng)之間用“，”分隔。如果向表中所有字段輸入數(shù)據(jù)，字段清單可以省略；如果向表中部分字段輸入數(shù)據(jù)，字段清單不可以省略。如果不知道某字段的值，可以使用N

U

L

L關(guān)鍵字將其值設(shè)為空，兩個(gè)連續(xù)的逗號也可以表示空值。但是，如果表結(jié)構(gòu)中該字段已經(jīng)設(shè)定為N

O

T

N

U

L

L，則不能使用空值輸入。對于數(shù)值型字段可以直接寫值，字符型字段其值要加英文單引號，日期型字段其值也要加英文單引號，其輸入順序?yàn)槿?月-年。2.

多行輸入命令多行輸入命令的語法格式如下：INSERT

INTO<表名>[(<字段清單>)]SELECT子句;該命令將多行數(shù)據(jù)輸入其目標(biāo)表中。在該命令中，新行的數(shù)據(jù)源是SELECT子句的查詢結(jié)果。這是從一個(gè)表向另一個(gè)表復(fù)制多行記錄的典型方法?！纠?8-12】將emp_bak表中數(shù)據(jù)輸入到employee表中。emp_bak表的結(jié)構(gòu)與表employee相同，表中存儲了部分雇員的記錄，而這些雇員信息在employee表中尚未輸入。insert

into

employee

select

*

from

emp_bak;3.

表間數(shù)據(jù)復(fù)制輸入數(shù)據(jù)庫中的數(shù)據(jù)通常來自其他的計(jì)算機(jī)系統(tǒng)，或是從其他的網(wǎng)站收集的，或是存儲在數(shù)據(jù)文件中的數(shù)據(jù)。為了將數(shù)據(jù)裝載到表中，可以先創(chuàng)建該表，并將SELECT命令的結(jié)果復(fù)制到新表中。實(shí)現(xiàn)上述功能的命令如下：c

re

a

te

ta

b

le<表名>a

s

s

e

le

c

t子句;【例1

8-1

3】創(chuàng)建一個(gè)新表e

m

p

_n

e

w，將e

m

p

lo

y

e

e表中部分字段的值復(fù)制到信標(biāo)

e

m

p

_n

e

w中。c

re

a

te

ta

b

le

e

m

p

_n

e

w

a

s

s

e

le

c

t

s

e

p

_id

,

e

m

p

_n

a

m

e

,

e

m

p

_s

a

la

ry

,

d

e

p

t_idfro

m

e

m

p

lo

y

e

e

;在本例中，利用表間數(shù)據(jù)復(fù)制的方法創(chuàng)建新表e

m

p

_n

e

w，結(jié)果顯示將e

m

p

lo

y

e

e表中指定字段的所有記錄復(fù)制到新表中。18.5

用SQL更新數(shù)據(jù)一般地，SQL提供了以下2種方法更新數(shù)據(jù)的方法。直接復(fù)制更新——UPDATE命令直接將表中的數(shù)據(jù)更新為確定值。嵌套更新——UPDATE命令將表中的數(shù)據(jù)更新為數(shù)據(jù)庫的其他對象中選取的數(shù)據(jù)。1.

直接賦值更新U

P

D

A

T

E命令修改單個(gè)表所選行的一個(gè)或多個(gè)字段的值。其語法格式如下：U

P

D

A

T

E<表名>S

E

T<字段名>=<表達(dá)式>[,<字段名>=<表達(dá)式>......]

[W

H

E

R

E<條件>];U

P

D

A

T

E為更新關(guān)鍵字。表名為被更新的目標(biāo)表。W

H

E

R

E子句指定被修改表的行，S

E

T子句指定更新的字段并賦予它們新的值，字段之間用逗號分隔?！纠?

8-1

3】將e

m

p

lo

y

e

e表中宋理民的雇員編號改為5

0

2。u

p

d

a

te

e

m

p

lo

y

e

e

e

m

p

_

id="5

0

2"w

h

e

re

e

m

p

_

n

a

m

e="宋理民";s

e

le

c

t

e

m

p

_

id,e

m

p

_

n

a

m

e

fro

m

e

m

p

lo

y

e

e

w

h

e

re

e

m

p

_

n

a

m

e="宋理民";E

M

P

_

ID

E

M

P

_

N

A

M

E--------------------------------------------------5

0

2

宋理民2.嵌套更新與INSERT一樣，UPDATE也可以使用SELECT子句查詢的結(jié)果進(jìn)行修改。【例18-14】將employee表中宋理民的部門編號改為高義民的部門編號。update

employee

dept_id=(select

dept_id

from

employee

whereemp_name="宋立民";select

smp_id,emp_name,dept_id,from

employee

whereemp_name="宋立民";EMP_ID

EMP_NAME

DEPT_I