價(jià)值百萬it技術(shù)知識(shí)-sql基礎(chǔ)oracle數(shù)據(jù)庫使用

的基本對(duì)象數(shù)據(jù)(Data)是數(shù)據(jù)庫中數(shù)據(jù)的定義數(shù)據(jù)的種類數(shù)據(jù)的特點(diǎn)學(xué)生 中的學(xué)生記錄數(shù)據(jù)的形式不能完全表達(dá)其內(nèi)容數(shù)據(jù)的解釋請(qǐng)給出另一個(gè)解釋和語義人們收集并抽取出一個(gè)應(yīng)用所需要的大量數(shù)據(jù)之后，應(yīng)將其保存起來以供進(jìn)一步加工處理，進(jìn)一步抽取有用信息。數(shù)據(jù)庫的定義長(zhǎng)期組織共享

大量?DBMS數(shù)據(jù)管理DBMS的用途?數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng)的構(gòu)成數(shù)據(jù)庫應(yīng)用系統(tǒng)應(yīng)用開發(fā)工具操作系統(tǒng)數(shù)據(jù)庫管理系統(tǒng)數(shù)據(jù)庫管理員用戶用戶用戶數(shù)據(jù)庫系統(tǒng)應(yīng)用

平臺(tái)基礎(chǔ)構(gòu)架平臺(tái)基礎(chǔ)

平臺(tái)硬件平臺(tái)產(chǎn)品協(xié)同辦公數(shù)據(jù)庫系統(tǒng)操作系統(tǒng)中間件應(yīng)用服務(wù)器置SQL

SELECT語句通過本章學(xué)習(xí)，您將可以:列舉SQL

SELECT語句的功能。執(zhí)行簡(jiǎn)單的選擇語句。SQL

語言和SQL*Plus

命令的不同。SELECT

標(biāo)識(shí)選擇哪些列。FROM

標(biāo)識(shí)從哪個(gè)表中選擇。SELECT *|{[DISTINCT]

column|expression

[alias],...}FROM

table;SELECT

*FROM

departments;department_id,

location_idSELECTFROM

departments;SQL

語言大小寫不敏感。SQL

可以寫在一行或者多行關(guān)鍵字不能被縮寫也不能分行各子句一般要分行寫。使用縮進(jìn)提高語句的可讀性。SQL*Plus:數(shù)字和日期使用的數(shù)學(xué)表達(dá)式。操作符描述+加-減*乘/除SELECT

last_name,

salary,

salary

+

300FROM

employees;乘除的優(yōu)先級(jí)高于加減。同一優(yōu)先級(jí)運(yùn)算符從左向右執(zhí)行。括號(hào)內(nèi)的運(yùn)算先執(zhí)行。*

/

+_優(yōu)先SELECT

last_name,

salary,

12*salary+100FROM

employees;號(hào)SELECT

last_name,

salary,

12*(salary+100)FROM

employees;空值是無效的，未指定的，未知的或不可預(yù)知的值?？罩挡皇强崭窕蛘?。SELECT

last_name,

job_id,

salary,

commission_pctFROM

employees;SELECT

last_name,

12*salary*commission_pctFROM

employees;包含空值的數(shù)學(xué)表達(dá)式的值都為空值列的別名:重命名一個(gè)列。便于計(jì)算。緊跟列名，也可以在列名和別名之間加入關(guān)鍵字‘AS’，以便在別名中包含空格或特殊的字符并區(qū)分大小寫。用S別ELEC名T

last_name

AS

name,commission_pct

commFROM

employees;SELECT

last_name

"Name",

salary*12

"Annual

Salary"FROM

employees;…連接符:把列與列，列與字符連接在一起。用‘||’表示?？梢?’列。SELECTFROMlast_name||job_id

AS

"Employees"employees;…字符串可以是SELECT

列表中的一個(gè)字符,數(shù)字,日期。日期和字符只能在單引號(hào)中出現(xiàn)。每當(dāng)返回一行時(shí)，字符串被輸出一次。SELECT

last_name ||'

is

a

'||jobidAS

"Employee

Details"FROM

employees;默認(rèn)情況下，查詢會(huì)返回全部行，包括重復(fù)行。SELECT

department_idFROM

employees;SELECT

DISTINCT

department_idFROM

employees;Oracle服務(wù)器SQL*Plus瀏覽器SQLstatements?????????SQL*Pluscommands使用SQL*Plus可以:描述表結(jié)構(gòu)。編輯SQL

語句。執(zhí)行SQL語句。將SQL

保存在文件中并將SQL語句執(zhí)行結(jié)果保存在文件中。在保存的文件中執(zhí)行語句。將文本文件裝入SQL*Plus編輯窗口。使用DESCRIBE

命令，表示表結(jié)構(gòu)DESC[RIBE]

tablenameDESCRIBE

employeesSELECT *|{[DISTINCT]

column|expression

[alias],...}FROM

table;?–––?和排序數(shù)據(jù)?通過本章學(xué)習(xí)，您將可以:在查詢中過濾行。在查詢中對(duì)行進(jìn)行排序。返回在90號(hào)部門工作的所有員工的信息使用WHERE

子句，將不滿足條件的行過濾掉。SELECT*|{[DISTINCT]

column|expression

[alias],...}FROM

table[WHERE

condition(s)];WHERE

子句緊隨FROM

子句。WHERE department_id

=

90SELECT

employee_id,

last_name,

job_id,

department_idFROM

employees;字符和日期要包含在單引號(hào)中。字符大小寫敏感，日期格式敏感。默認(rèn)的日期格式是DD-MON-RR。SELECT

last_name,

job_id,

department_idFROM

employeesWHERE last_name

=

'Whalen';操作符含義=等于>大于>=大于、等于<小于<=小于、等于<>不等于SELECT

last_name,

salaryFROM

employeesWHERE

salary<=

3000;操作符含義BETWEEN...AND...在兩個(gè)值之間(包含邊界)IN(set)等于值列表中的一個(gè)LIKE模糊查詢IS

NULL空值使用BETWEEN

運(yùn)算來顯示在一個(gè)區(qū)間內(nèi)的值。SELECT

last_name,

salaryFROM

employeesWHERE

salaryBETWEEN

2500

AND

3500;SELECT

employee_id,

last_name,

salary,

manager_idFROM

employeesWHERE manager_id

IN

(100,

101,

201);使用IN運(yùn)算顯示列表中的值。使用LIKE

運(yùn)算選擇類似的值選擇條件可以包含字符或數(shù)字:SELECTFROMWHERE_nameemployees_name

LIKE

'S%';‘%’和‘-’可以同時(shí)使用。可以使用ESC標(biāo)識(shí)符選擇‘%’和‘_’符號(hào)。SELECT

last_nameFROM

employeesWHERE

last_nameLIKE

'_o%';使用NULL

判斷空值。SELECT

last_name,

manager_idFROM

employeesWHEREmanager_id

IS

NULL;操作符含義AND邏輯并OR邏輯或NOT邏輯否SELECT

employee_id,

last_name,

job_id,

salaryFROM

employeesWHEREANDsalary

>=10000job_id

LIKE

'%MAN%';AND

要求和的關(guān)系為真。SELECT

employee_id,

last_name,

job_id,

salaryFROM

employeesWHEREORsalary

>=

10000job_id

LIKE

'%MAN%';OR

要求或關(guān)系為真。SELECT

last_name,

job_idFROM

employeesWHEREjob_idNOT

IN

('IT_PROG',

'ST_CLERK',

'SA_REP');優(yōu)先級(jí)算術(shù)運(yùn)算符連接符比較符IS

[NOT]

NULL,

LIKE,

[NOT]

IN[NOT]

BETWEENNOTANDORSELECT

last_name,

job_id,

salaryFROM

employeesWHERE job_id

=

'SA_REP'OR job_id

=

'AD_PRES'AND salary

>

15000;SELECT

last_name,

job_id,

salaryFROM

employeesWHERE (job_id

=

'SA_REP'OR job_id

=

'AD_PRES')AND salary

>

15000;使用括號(hào)控制執(zhí)行順序。SELECTFROMORDER

BY

hire

datelast_name,

job_id,

department_id,

hire_dateemployees;使用ORDER

BY

子句排序ORDER

BY

子句在SELECT語句的結(jié)尾。排序SELECT last_name,

job_id,

department_id,

hire_dateFROM

employeesORDER

BY

hire_dateDESC;別名排序SELECT

employee_id,

last_name,

salary*12

annsalFROM

employeesORDER

BY

annsal;按照ORDER

BY

列表的順序排序?？梢允褂貌辉赟ELECT

列表中的列排序SELECT

last_name,

department_id,

salaryFROM

employeesORDER

BY

department_id,

salary

DESC;SELECT *|{[DISTINCT]

column|expression

[alias],...}FROM

table[WHERE

condition(s)][ORDER

BY {column,

expr,

alias}

[ASC|DESC]];?–––?SELECT

LAST_NAME,SALARYFROMemployeesWHERESALARY

>=

12000;select

last_name,

DEPARTMENT_ID

from

employeeswhere

employee_id=176;SELECT

LAST_NAME,JOB_IDFROM

EMPLOYEESWHERE

HIRE_DATE

BETWEEN

'20-FEB-98'

AND

'01-MAY-98'ORDER

BY

HIRE_DATE;select

last_name,department_IDfrom

employeeswhere

department_ID

IN(20,50)order

bylast_name;Practice

2

(continued)5. Display

the

last

name

and

hire

date

ofevery

employee

who

was

hired

in

1994.select

last_name,

hire_datefrom

employeeswhere

hire_da ike

'%94';6. Display

the

last

name

and

job

title

of

allemployees

who

do

nothave

a

manager.select

last_name,job_title

from

employees,jobs

where

employees.job_id=jobs.job_idand

manager_id

is

NULL;7. Display

the

last

name,

salary,

and

commission

for

all

employees

who

earn

commissions.

Sort

datain

descending

order

of

salary

and

commissions.Select

lamission_pct

from

employees

where

commission_pct

is

not

null

orderbymission_pct

desc;If

you

want

anextra

challenge,

complete

the

followingexercises:8. Display

the

last

name,

job,

and

salary

for

allemployees

whose

job

is

salesrepresentative

orstock

clerk

and

whose

salary

is

not

equal

to

$2,500,

$3,500,

or

$7,000.display

the

last

name,

salary,

and

commission

for

all

employees

whose

commission

amount

is

20%.Udi:SELECT

last_name,JOB_ID,salaryFROM

employeesWHERE

(JOB_ID='SA_REP'

or

JOB_ID='ST_CLERK')and

salary

not

in

(2500,3500,7000);Joy:select

last_name,job_title,salaryfrom

employees,jobswhere

employees.job_id=jobs.job_idand

job_title

in

('Sales

Representative','Stock

Clerk')and

salary

not

in

(1500,3500,7000);SELECT

last_name,jo

MISSION_PCTFROM

employees

jobs函數(shù)通過本章學(xué)習(xí)，您將可以:SQL中不同類型的函數(shù)。在SELECT

語句中使用字符，數(shù)字和日期函數(shù)。描述轉(zhuǎn)換型函數(shù)的用途。函數(shù)參數(shù)1參數(shù)2參數(shù)n結(jié)果函數(shù)單行函數(shù)多行函數(shù)單行函數(shù):操作數(shù)句對(duì)象接受函數(shù)返回一個(gè)結(jié)果只對(duì)一行進(jìn)行變換每行返回一個(gè)結(jié)果可以轉(zhuǎn)換數(shù)據(jù)類型可以嵌套參數(shù)可以是一列或一個(gè)值function_name

[(arg1,

arg2,...)]轉(zhuǎn)換字符數(shù)值日期通用單行函數(shù)字符函數(shù)大小寫控制函數(shù)字符控制函數(shù)函數(shù)結(jié)果LOWER('SQL

Course')UPPER('SQL

Course')INITCAP('SQL

Course')sql

courseSQL

COURSESql

Course這類函數(shù)改變字符的大小寫。顯示員工Higgins的信息:SELECT

employee_id,

last_name,

department_idFROM

employeesWHERE last_name

=

'higgins';no

rows

selectedSELECT

employee_id,

last_name,

department_idFROM

employeesWHERELOWER(last_name)

=

'higgins';函數(shù)結(jié)果CONCAT(' o',

'World')oWorldSUBSTR('

oWorld',1,5)oLENGTH('

oWorld')10INSTR(' oWorld',

'W')6LPAD(salary,10,'*')*****24000RPAD(salary,

10,

'*')24000*****TRIM('H'

FROM

'

oWorld')elloWorld這類函數(shù)控制字符:SELECT

employee

id,CONCAT( _name,

last_name)

NAME,job_id,

LENGTH

(last

name),INSTR(last_name,

'a')

"Contains

'a'?"FROMWHEREemployeesSUBSTR(job_id,

4)

='REP';123312ROUND:四舍五入ROUND(45.926,

2)45.93TRUNC:

截?cái)郥RUNC(45.926,

2)MOD:求余MOD(1600,

300)45.92100SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;123312SELECT312TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM

DUAL;123SELECT

last_name,

salary,

MOD(salary,

5000)FROM

employeesWHERE job_id

=

'SA_REP';Oracle

數(shù)字 日期:

世紀(jì),年,月,日,小時(shí),分鐘,秒。默認(rèn)的日期格式是DD-MON-RR.SELECT

last_name,

hire_dateFROM

employeesWHERE last_name

like

'G%';函數(shù)SYSDATE

返回:日期時(shí)間在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。兩個(gè)日期相減返回日期之間相差的天數(shù)。可以用數(shù)字除24來向日期中加上或減去小時(shí)。SELECT

last_name,

(SYSDATE-hire_date)/7

AS

WEEKSFROM

employeesWHERE department_id

=

90;函數(shù)函數(shù)描述MONTHS_BETWEEN兩個(gè)日期相差的月數(shù)ADD_MONTHS向指定日期中加上若干月數(shù)NEXT_DAY指定日期的下一個(gè)日期LAST_DAY本月的最后一天ROUND日期四舍五入TRUNC日期截?cái)郙ONTHS_BETWEEN

('01-SEP-95','11-JAN-94')19.6774194ADD_MONTHS

('11-JAN-94',6)

'11-JUL-94'NEXT_DAY

('01-SEP-95','FRIDAY')'08-SEP-95'LAST_DAY('01-FEB-95')'28-FEB-95'01-AUG-9501-JAN-9601-JUL-9501-JAN-95Assume

SYSDATE

=

'25-JUL-95':ROUND(SYSDATE,'MONTH')ROUND(SYSDATE

,'YEAR')TRUNC(SYSDATE

,'MONTH')TRUNC(SYSDATE

,'YEAR')隱性顯性數(shù)據(jù)類型轉(zhuǎn)換Oracle

自動(dòng)完成下列轉(zhuǎn)換：源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型VARCHAR2

or

CHARNUMBERVARCHAR2

or

CHARDATENUMBERVARCHAR2DATEVARCHAR2表達(dá)式計(jì)算中,Oracle

自動(dòng)完成下列轉(zhuǎn)換:源數(shù)據(jù)類型目標(biāo)數(shù)據(jù)類型VARCHAR2

or

CHARNUMBERVARCHAR2

or

CHARDATENUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE單行函數(shù)可以嵌套。嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。F3(F2(F1(col,arg1),arg2),arg3)2

=

23

=

3NVL(TO_CHAR(manager_id),

'No

Manager')SELECT

last_name,FROM

employeesWHERE manager_id

IS

NULL;這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL

(expr1,

expr2)NVL2

(expr1,

expr2,

expr3)NULLIF

(expr1,

expr2)COALESCE

(expr1,

expr2,...,

exprn)將空值轉(zhuǎn)換成一個(gè)已知的值：可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。函數(shù)的一般形式:SELECT

last

name,

salary,

NVL(commissio t,

0),(salary*12)

+

(salary*12*NVL(commission_pct,

0))

AN_SALFROM

employees;1212在SQL

語句中使用IF-THEN-ELSE

邏輯。使用兩種方法:在需要使用IF-THEN-ELSE

邏輯時(shí):CASE

expr

WHEN

comparison_expr1

THEN

return_expr1[WHEN

comparison_expr2

THEN

return_expr2WHEN

comparison_exprn

THEN

return_exprnELSE

else_expr]ENDSELECT

last

name,

job

id,salary,CASE

job_id

WHEN

'IT_PROG'

THENWHEN

'ST_CLERK'

THENWHEN

'SA_REP'

THEN1.10*salary1.15*salary1.20*salary"REVISED_SALARY"FROMELSE salary

ENDemployees;下面是使用case表達(dá)式的一個(gè)例子：在需要使用IF-THEN-ELSE

邏輯時(shí):DECODE(col|expression,

search1,

result1[,

search2,

result2,...,][,

default])SELECT

last

name,job

id,

salary,DECODE(job_id,

'IT_PROG',

1.10*salary,'ST_CLERK',

1.15*salary,'SA_REP',

1.20*salary,salary)REVISED_SALARYFROMemployees;使用decode函數(shù)的一個(gè)例子：SELECT

last_name,

salary,DECODE

(TRUNC(salary/2000,

0),0,

0.00,1,

0.09,2,

0.20,3,

0.30,4,

0.40,5,

0.42,6,

0.44,0.45)

TAX_RATEFROM

employeesWHERE department_id

=

80;通過本章學(xué)習(xí)，您應(yīng)該學(xué)會(huì):使用函數(shù)對(duì)數(shù)據(jù)進(jìn)行計(jì)算使用函數(shù)修改數(shù)據(jù)使用函數(shù)控制一組數(shù)據(jù)的輸出格式使用函數(shù)改變?nèi)掌诘娘@示格式使用函數(shù)改變數(shù)據(jù)類型使用NVL

函數(shù)使用IF-THEN-ELSE

邏輯查詢通過本章學(xué)習(xí)，您將可以:使用等值和不等值連接在SELECT

語句中查詢多個(gè)表中的數(shù)據(jù)。使用外連接查詢不滿足連接條件的數(shù)據(jù)。使用自連接。?在下面條件下產(chǎn)生:為了避免集，可以在WHERE

加入有效的連接條件。集:20x8=160行Cross

joinsNatural

joinsUsing

clauseFull

ortwo

sided

outer

joinsArbitrary

join

conditions

forouter

joinsEquijoinNon-equijoinOuter

joinSelf

join使用連接在多個(gè)表中查詢數(shù)據(jù)。SELECT

table1.column,

table2.columnFROM

table1,

table2WHERE

table1.column1

=

table2.column2;在WHERE

字句中寫入連接條件。在表中有相同列時(shí)，在列名之前加上表名前綴。外鍵主鍵SELECT

employees.employee_id,

employees.last_name,employees.department_id,

departments.department_id,departments.location_idFROM employees,

departmentsWHEREemployees.department

id

=

departments.department

id;使用表名前綴在多個(gè)表中區(qū)分相同的列。使用表名可以提高效率。在不同表中具有相同列名的列可以用別名加以區(qū)分。SELECT

e.employee_id,

e.last_name,

e.department_id,d.department_id,

d.location_idFROM employees

e

,

departments

dWHERE

e.department_id

=

d.department_id;使用別名可以簡(jiǎn)化查詢。使用表名前綴可以提高執(zhí)行效率。連接n個(gè)表,至少需要n-1個(gè)連接條件。例如：連接三個(gè)表，至少需要兩個(gè)連接條件。EMPLOYEES表中的列工資應(yīng)在JOB_GRADES表中的最高工資與最低工資之間SELEC ast_name,

e.salary,

j.grade_levelFROM employees

e,

job_gradesjWHERE

e.salaryBETWEEN

j.lowest_sal

AND

j.highest_sal;190號(hào)部門沒有員工使用外連接可以查詢不滿足連接條件的數(shù)據(jù)。外連接的符號(hào)是(+)。SELECT

table1.column,

table2.columnFROM

table1,

table2WHERE

table1.column(+)

=

table2.column;SELECT

table1.column,

table2.columnFROM

table1,

table2WHERE

table1.column

=

table2.column(+);e.department

id(+)

=

d.department

idSELEC ast_name,

e.department_id,

d.department_nameFROM employees

e,

departments

dWHERE

;EMPLOYEES

(WORKER)EMPLOYEES

(MANAGER)WORKER

表中的MANAGER_ID

和MANAGER

表中的MANAGER_ID相等SELECT

worker.last_name

||

'

works

for

'||

manager.last_nameFROM employees

worker,

employees

managerWHEREworker.manager_id

=

manager.employee_id

;使用連接從多個(gè)表中查詢數(shù)據(jù)：SELECT

table1.column,

table2.columnFROM

table1[CROSS

JOIN

table2]

|[NATURAL

JOIN

table2]

|[JOIN

table2

USING

(column_name)]

|[JOIN

table2ON(table1.column_name

=

table2.column_name)]

|[LEFT|RIGHT|FULL

OUTER

JOIN

table2ON

(table1.column_name

=

table2.column_name)];使用CROSS

JOIN

子句使連接的表產(chǎn)生叉集。叉集和 集是相同的。SELECT

last_name,

department_nameFROM

employeesCROSS

JOINdepartments;NATURAL

JOIN

子句，會(huì)以兩個(gè)表中具有相同名字的列為條件創(chuàng)建等值連接。在表中查詢滿足等值條件的數(shù)據(jù)。如果只是列名相同而數(shù)據(jù)類型不同，則會(huì)產(chǎn)生錯(cuò)誤。SELECT

department_id,

department_name,location_id,

cityFROM

departmentsNATURAL

JOIN

locations;在NATURAL

JOIN

子句創(chuàng)建等值連接時(shí)，可以使用USING

子句指定等值連接中需要用到的列。使用USING

可以在有多個(gè)列滿足條件時(shí)進(jìn)行選擇。不要給選中的列中加上表名前綴或別名。NATURAL

JOIN

和USING

子句經(jīng)常同時(shí)使用。SELECT

e.employee_id,

e.last_name,

d.location_idFROM employees

e

JOIN

departments

dUSING

(department

id);自然連接中是以具有相同名字的列為連接條件的?？梢允褂?/p>

ON

子句指 外的連接條件。這個(gè)連接條件是與其它條件分開的。ON

子句使語句具有更高的易讀性。SELECT

e.employee_id,

e.last_name,

e.department_id,d.department_id,

d.location_idFROM employees

e

JOIN

departments

dON (e.department

id

=

d.department

id);SELECT

employee_id,

city,

department_nameFROM employees

eJOIN departments

dON d.department_id

=

e.department_idJOIN locations

lON d.location

id=

l.location

id;在SQL:1999中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行，這種連接稱為左（或右）外聯(lián)接。兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回兩個(gè)表中不滿足條件的行，這種連接稱為滿外聯(lián)接。LEFT

OUTER

JOIN

departments

dON (e.department

id=

d.departmentid)SELEC ast_name,

e.department_id,

d.department_nameFROM employees

e;RIGHT

OUTER

JOIN

departments

dON (e.department_id

=

d.department_id)SELEC ast_name,

e.department_id,

d.department_nameFROM employees

e;SELEC ast_name,

e.department_id,

d.department_nameFROM employees

eFULL

OUTER

JOIN

departments

dON (e.department

id=

d.department

id);SELECT

e.employee_id,

e.last_name,

e.department_id,d.department_id,

d.location_idFROM employees

e

JOIN

departments

dON (e.department_id

=

d.department_id)AND e.manager

id

=

149;4.

Display

the

employee

last

name

and

department

name

for

allemployees

who

have

an

a(lowercase)

in

their

last

names.

Place

yourSQLstatement

in

a

text

file

named

lab4_4.sql.7. Modify

lab4_6.sql

to

display

all

employees

including

King,

who

has

no

manager.Order

the

results

by

the

employee

number.Place

your

SQL

statement

in

a

text

file

named

lab4_7.sql.

Run

the

query

inlab4_7.sql.If

you

have

time,

complete

the

following

exercises:8. Create

a

query

that

displays

employee

last

names,

department

numbers,

and

all

theemployees

who

work

in

the

same

department

as

a

givenemployee.

Giveeach

columnanappropria

abel.9.job,Show

the

structure

of

the

JOB_GRADES

table.

Create

a

query

that

displays

the

name,department

name,

salary,

and

grade

for

all

employees.If

you

want

an

extra

challenge,

complete

the

following

exercises:10. Create

a

query

to

display

the

name

and

hire

date

ofany

employee

hired

afteremployee

Davies.11.

Display

the

names

and

hire

dates

for

all

employees

who

were

hired

before

theirmanagers,

alongwith

their

manager’s

names

and

hiredates.

Label

the

columns

Employee,EmpHired,

Manager,

and

Mgr

Hired,

respectively.函數(shù)?通過本章學(xué)習(xí)，您將可以:了解組函數(shù)。描述組函數(shù)的用途。使用GROUP

BY

字句數(shù)據(jù)分組。使用HAVING

字句過濾分組結(jié)果集。分組函數(shù)作用于一組數(shù)據(jù)，并對(duì)一組數(shù)據(jù)返回一個(gè)值。表EMPLOYEES中的工資最大值A(chǔ)VGCOUNTMAXMINSTDDEVSUMSELECTFROM[WHERE[column,]

group_function(column),

...tablecondition][GROUP

BY

column][ORDER

BY

column];SELECTAVG(salary),

MAX(salary),MIN(salary),

SUM(salary)FROM

employeesWHERE job_id

LIKE

'%REP%';可以對(duì)數(shù)值型數(shù)據(jù)使用AVG

和SUM

函數(shù)。SELECTMIN(hire_date),

MAX(hire_date)FROM

employees;可以對(duì)任意數(shù)據(jù)類型的數(shù)據(jù)使用MIN

和MAX

函數(shù)。SELECTCOUNT(*)FROM

employeesWHERE

department_id

=

50;COUNT(*)返回表中記錄總數(shù)。SELECTCOUNT(commissio

t)FROM

employeesWHERE department_id

=

80;COUNT(expr)返回expr不為空的記錄總數(shù)。SELECTCOUNT(DISTINCT

department

id)FROM

employees;COUNT(DISTINCT

expr)返回expr非空且不重復(fù)的記錄總數(shù)AVG(commissio

t)SELECTFROM

employees;組函數(shù)忽略空值。SELECTAVG(NVL(commissio t,

0))FROM

employees;NVL函數(shù)使分組函數(shù)無法忽略空值。44009500求出EMPLOYEES3500

表中各部門的平均工資640010033SELECTFROM[WHEREcolumn,

group_function(column)tablecondition][GROUP

BY

group_by_expression][ORDER

BY

column];可以使用GROUP

BY

子句將表中的數(shù)據(jù)分成若干組GROUP

BY

department_id;在SELECT

列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在GROUP

BY

子句中。SELECT

department_id,

AVG(salary)FROM

employeesSELECT

AVG(salary)FROM

employeesGROUP

BY

department_id;包含在GROUP

BY

子句中的列不必包含在SELECT

列表中。使用多個(gè)列進(jìn)行分組SELECT

department_id

dept_id,

job_id,

SUM(salary)FROM

employeesGROUP

BY

department_id,

job_id

;所用包含于SELECT

列表中，而未包含于組函數(shù)中的列都必須包含于GROUP

BY

子句中。SELECT

department_id,

COUNT(last_name)FROM

employees;SELECT

department_id,

COUNT(last_name)*ERROR

at

line

1:ORA-00937:

not

a

single-group

group

function不能在WHERE

子句中使用組函數(shù)。可以在HAVING

子句中使用組函數(shù)。SELECTFROMWHEREdepartment_id,

AVG(salary)employeesAVG(salary)

>

8000GROUP

BY

department_id;WHERE AVG(salary)

>

8000*ERROR

at

line

3:ORA-00934:

group

function

is

not

allowed

hereThe

umsalaryper

departmentwhen

it

isgreater

than$10,000EMPLOYEESSELECTFROM[WHEREcolumn,

group_functiontablecondition][HAVING

group_condition][GROUP

BY

group_by_expression][ORDER

BY

column];使用HAVING

過濾分組:1.2.3.行已經(jīng)被分組。使用了組函數(shù)。滿足HAVING

子句中條件的分組將被顯示。SELECT department_id,

MAX(salary)FROM

employeesGROUP

BY

department_idHAVING

MAX(salary)>10000

;SELECTFROMWHEREjob_id,

SUM(salary)

PAYROLLemployeesjob_id

NOT

LIKE

'%REP%'HAVING SUM(salary)

>

13000GROUP

BY

job

idORDER

BY

SUM(salary);顯示平均工資的最大值MAX(AVG(salary))SELECTFROMemployeesGROUP

BY

department_id;SELECTFROM[WHEREcolumn,

group_function(column)tablecondition][GROUP

BY

group_by_expression][HAVING

group_condition][ORDER

BY

column];通過本章學(xué)習(xí)，您已經(jīng)學(xué)會(huì):使用組函數(shù)。在查詢中使用GROUP

BY

子句。在查詢中使用HAVING

子句。6.Write

a

query

to

display

the

number

of

people

with

the

same

job.7. Determine

the

number

of

managers

without

listing

them.

Label

the

column

Number

ofManagers.

Hint:

Use

the

MANAGER_ID

column

to

determine

thenumber

of

managers.8.

Write

a

query

that

displays

the

difference

between

the

highest

and

lowest

salaries.Label

the

column

DIFFERENCE.10.

Write

a

query

to

display

each

department’s

name,

location,number

ofemployees,

and

theaverage

salary

for

all

employees

in

that

department.

Label

the

columns

Name,

Location,Number

of

People,

and

Salary,respectively.

Round

the

average

salary

to

twodecimal

places.If

you

want

anextra

challenge,

complete

the

following

exercises:11.

Create

a

query

that

will

display

the

total

number

of

employees

and,

of

that

total,

the

numberof

employees

hired

in

1995,

1996,

1997,

and

1998.

Create

appropriate

column

headings.12. Create

a

matrix

query

to

display

the

job,

the

salary

for

that

job

based

on

department

number,and

the

total

salary

for

that

job,

for

departments

20,

50,

80,

and

90,

giving

each

column

anappropriate

heading.詢?通過本章學(xué)習(xí)，您將可以:描述子查詢可以解決的問題定義子查詢。列句子查詢的類型。書寫單行子查詢和多行字查詢。誰的工資比Abel

高?MainQuery:誰的工資比Abel

高?Subquery?Abel的工資是多少?詢語法SELECT

select_listFROM

tableWHERE

expr

operator子查詢(內(nèi)查詢)在主查詢之前一次執(zhí)行完成。子查詢的結(jié)果被主查詢使用(外查詢)。(SELECTFROMselect_listtable);SELECT

last_nameFROMWHEREemployeessalary

>(SELECT

salaryFROMWHEREemployeeslast_name

=

'Abel');詢11000子查詢要包含在括號(hào)內(nèi)。將子查詢放在比較條件的右側(cè)。除非進(jìn)行Top-N

分析，否則不要在子查詢中使用ORDER

BY子句。單行操作符對(duì)應(yīng)單行子查詢，多行操作符對(duì)應(yīng)多行子查詢。子查詢返回單行子查詢主查詢ST_CLERKST_CLERKSA_MAN多行子查詢主查詢子查詢返回只返回一行。使用單行比較操作符。操作符含義=Equal

to>Greater

than>=Greater

than

or

equalto<Lessthan<=Less

than

or

equal

to<>Not

equal

toSELECT

last_name,

job_id,

salaryFROM

employeesWHERE job_id

=

ST_CLERK(SELECT

job_idFROM

employeesWHERE employee

id

=

141)AND salary

>

2600(SELECT

salaryFROM

employeesWHERE employee_id

=

143);SELECT

last_name,

job_id,

salaryFROM

employeesWHERE salary

=(SELECT

MIN(salary)2500首先執(zhí)行子查詢。向主查詢中的HAVING

子句返回結(jié)果。SELECTFROMdepartment_id,

MIN(salary)employeesGROUP

BY

department_idHAVING

MIN(salary)>FROM

employeesWHERE department_id

=

50);2500(SELECT

MIN(salary)SELECT

employee_id,

last_nameFROMWHEREsalaryemployees=(SELECTFROMMIN(salary)employeesGROUP

BY

department

id);ERROR

at

line

4:ORA-01427:

single-row

subquery

returns

more

thanone

rowno

rows

selectedSELECT

last_name,

job_idFROM

employeesWHERE job_id

=(SELECT

job_idFROM

employeesWHERE last_name

=

'Haas');返回多行。使用多行比較操作符。操作符含義IN等于列表中的任何一個(gè)ANY和子查詢返回的任意一個(gè)值比較ALL和子查詢返回的所有值比較9000,

6000,4200SELECT

employee_id,

last_name,

job_id,

salaryFROMWHEREemployeessalary

<

ANY(SELECT

salaryFROM

employeesWHERE job_id

=

'IT_PROG')AND job_id

<>

'IT_PROG';SELECT

employee_id,

last_name,

job_id,

salaryFROMWHEREemployeessalary

<

ALL(SELECT

salaryFROM

employeesWHERE job_id

=

'IT_PROG')AND job_id

<>

'IT_PROG';9000,

6000,

4200SELECT

emp.last_nameFROMWHEREemployees

empemp.employee_id

NOT

IN(SELECT

mgr.manager_idFROM employees

mgr);no

rows

selected通過本章學(xué)習(xí)，您已經(jīng)學(xué)會(huì):在什么時(shí)候遇到什么問題應(yīng)該使用子查詢。在查詢是基于未知的值時(shí)應(yīng)使用子查詢。SELECT

select_listFROM

tableWHEREexpr

operator(SELECT

select_listFROM

table);1.

Write

a

query

to

display

the

last

name

and

hire

date

of

any

employee

in

the

samedepartment

as

Zlotkey.

Exclude

Zlotkey.2.

Create

a

query

to

display

the

employee

numbers

and

last

names

ofall

employees

whoearn

more

than

the

average

salary.

Sort

the

results

in

ascending

order

ofsalary.3.

Write

a

query

that

displays

the

employee

numbers

and

last

names

of

all

employeeswho

work

in

a

department

with

any

employee

whose

last

name

contains

a

u.

Place

your

SQLstatement

in

a

text

file

named

lab6_3.sql.

Run

your

query.4.Display

the

last

name,

department

number,

and

job

ID

ofall

employees

whosedepartment

location

ID

is

1700.5.Display

thelast

name

and

salary

of

every

employee

who

reports

to

King.數(shù)據(jù)通過本章學(xué)習(xí)，您將可以:使用DML

語句向表中 數(shù)據(jù)更新表中數(shù)據(jù)從表中刪除數(shù)據(jù)將表中數(shù)據(jù)和并控制事務(wù)DML

可以在下列條件下執(zhí)行:事務(wù)是由完成若干項(xiàng)工作的DML語句組成的。使用

INSERT

語句向表中 數(shù)據(jù)。使用這種語法一次只能向表中 一條數(shù)據(jù)。INSERT

INTOVALUEStable

[(column

[,

column...])](value

[,

value...]);為每一列添加一個(gè)新值。按列的默認(rèn)順序列出各個(gè)列的值。在INSERT

子句中隨意列出列名和他們的值。字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。INSERT

INTO

departments(department_id,

department_name,manager_id,

location_id)VALUES (70,

'Public

Relations',

100,

1700);1

row

created.INSERT

INTOVALUESdepartments(100,

'Finance',

NULL,

NULL);1

row

created.INSERT

INTOVALUESdepartments

(department_id,department_name(30,

'Purchasing');1

row

created.隱式方式:在列名表中省略該列的值。INSERT

INTO

employees

(employee_id,_name,

last_name,,

phone_number,hire

date,

job_id,

salary,commission_pct,

manager_id,department_id)VALUES

(113,'Louis',

'Popp','LPOPP',

'515.124.4567',SYSDATE,

'AC_ACCOUNT',

6900,NULL,

205,

100);1

row

created.SYSDATE

記錄當(dāng)前系統(tǒng)的日期和時(shí)間。VALUES(114,'Den',

'Raphealy','DRAPHEAL',

'515.127.4561',TO_DATE('FEB

3,

1999',

'MON

DD,

YYYY'),加入新員工INSERT

INTO

employees'AC_ACCOUNT',

11000,

NULL,

100,

30);1

row

created.檢查 的數(shù)據(jù)在INSERT

語句中加入子查詢。不必書寫VALUES

子句。子查詢中的值列表應(yīng)于INSERT

子句中的列名對(duì)應(yīng)。SELECT

employee_id,

last_name,

salary,

commission_pctFROM

employeesWHERE job_id

LIKE

'%REP%';INSERT

INTO

sales_reps(id,

name,

salary,

commission_pct)4

rows

created.更新EMPLOYEES

表使用UPDATE

語句更新數(shù)據(jù)。?可以一次更新多條數(shù)據(jù)。UPDATESET[WHEREtablecolumn

=

value

[,

column

=

value,

...]condition];WHERE employee_id

=

113;UPDATE

employeesSET department

id

=

701

row

updated.使用WHERE

子句指定需要更新的數(shù)據(jù)。如果省略WHERE子句，則表中的所有數(shù)據(jù)都將被更新。UPDATESETcopy_empdepartment_id

=

110;22

rows

updated.UPDATESETemployeesjob_id

=salary

=SELECT

job_idFROM

employeesWHERE employee_id

=

205),SELECT

salaryFROM

employeesWHERE employee_id

=

205)WHERE

employee_id

=

114;1

row

updated.更新114號(hào)員工的工作和工資使其與205號(hào)員工相同。copy_empUPDATESET

department_id

=WHEREjob_id(SELECT

department_idFROM

employeesWHERE

employee_id

=

100)= (SELECT

job_idFROM

employeesWHERE

employee_id

=

200);1

row

updated.在UPDATE

中使用子查詢，使更新基于另一個(gè)表中的數(shù)據(jù)。UPDATE

employees*ERROR

at

line

1:ORA-0229