dba經(jīng)典輔助學(xué)習(xí)lesorder

取出和排序數(shù)據(jù)

周炯

數(shù)據(jù)實(shí)驗(yàn)室1

教學(xué)目標(biāo)

周炯

數(shù)據(jù)實(shí)驗(yàn)室2

課程安排

限定行:

WHERE字句

比較條件式使用=,<=,BETWEEN,IN,LIKE,和

NULL條件式

合乎邏輯的條件式使用AND，OR,而NOT操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室3

使用選擇限定行

周炯

數(shù)據(jù)實(shí)驗(yàn)室4

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

FROM table

[WHEREcondition(s)];

SELECTemployee_id,last_name,job_id,department_idFROM employees

WHEREdepartmentid=90

;

SELECTlast_nameFROM employees

WHEREhire_date=

'17-FEB-96'

;

Operator

Meaning

=

Equalto

>

Greaterthan

>=

Greaterthanorequalto

<

Lessthan

<=

Lessthanorequalto

<>

Notequalto

BETWEEN

...AND...

Betweentwovalues(inclusive)

IN(set)

Matchanyofalistofvalues

LIKE

Matchacharacterpattern

ISNULL

Isanullvalue

使用比較操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室9

SELECTlast_name,salary

FROM employees

WHEREsalary

BETWEEN2500AND3500

;

成員表達(dá)式 使用IN操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室11

類型匹配—使用LIKE操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室12

SELECTlast_name

FROM employees

WHERElast_name

LIKE'_o%'

;

SELECTlast_name,manager_id

FROM employees

WHERE

manager_idISNULL

;

操作符

含義

AND

ReturnsTRUEifbothcomponentconditionsaretrue

OR

ReturnsTRUEifeithercomponentconditionistrue

NOT

ReturnsTRUEiftheconditionisfalse

使用AND操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室16

使用OR操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室17

SELECTlast_name,job_idFROM employees

WHERE

job_id

NOTIN('IT_PROG','ST_CLERK','SA_REP')

;

課程安排

一個(gè)表達(dá)式操作符的優(yōu)先規(guī)則

周炯

數(shù)據(jù)實(shí)驗(yàn)室19

操作符

含義

1

Arithmeticoperators

2

Concatenationoperator

3

Comparisonconditions

4

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

5

[NOT]BETWEEN

6

Notequalto

7

NOTlogicalcondition

8

ANDlogicalcondition

9

ORlogicalcondition

優(yōu)先規(guī)則

周炯

數(shù)據(jù)實(shí)驗(yàn)室21

課程安排

排序行使用ORDERBY字句

周炯

數(shù)據(jù)實(shí)驗(yàn)室22

SELECT last_name,job_id,department_id,hire_date

FROM employees 1

ORDERBYhiredate

DESC

;

SELECT last_name,job_id,department_id,hire_dateFROM employees

ORDERBYhiredate

;

SELECT

employee_id,last_name,salary*12

annsal

FROM

employees

2

ORDERBY

annsal

;

SELECT last_name,job_id,department_id,hire_date

FROM employees 3

ORDERBY

3;

課程安排

替代變量

周炯

數(shù)據(jù)實(shí)驗(yàn)室26

替代變量

different

周炯

數(shù)據(jù)實(shí)驗(yàn)室27

替代變量

周炯

數(shù)據(jù)實(shí)驗(yàn)室28

SELECTemployee_id,last_name,salary,department_id

FROM employees

WHEREemployee_id=

&employeenum

;

使用單一&符號替代變量

周炯

數(shù)據(jù)實(shí)驗(yàn)室30

SELECTlast_name,department_id,salary*12FROM employees

WHEREjob_id=

'&jobtitle'

;

SELECTemployee_id,last_name,job_id,

FROM employeesWHERE

ORDERBY&order_column;

&condition

columnname

SELECT

employee_id,last_name,job_id,

&&column_name

FROM

employees

ORDERBY

&columnname

;

課程安排

DEFINE和VERIFY命令

周炯

數(shù)據(jù)實(shí)驗(yàn)室34

使用DEFINE命令

周炯

數(shù)據(jù)實(shí)驗(yàn)室35

SETVERIFYON

SELECTemployee_id,last_name,salaryFROM employees

WHEREemployee_id=&employee_num;

總結(jié)

使用比較條件式

使用BETWEEN,IN,LIKE,和NULL操作符

應(yīng)用邏輯AND,OR,和NOT操作符

周炯

數(shù)據(jù)實(shí)驗(yàn)室37

練習(xí)2