价值百万it技术知识-sql基础oracle数据库使用

的基本对象数据(Data)是数据库中数据的定义数据的种类数据的特点学生 中的学生记录数据的形式不能完全表达其内容数据的解释请给出另一个解释和语义人们收集并抽取出一个应用所需要的大量数据之后，应将其保存起来以供进一步加工处理，进一步抽取有用信息。数据库的定义长期组织共享

大量•DBMS数据管理DBMS的用途•数据库系统数据库系统的构成数据库应用系统应用开发工具操作系统数据库管理系统数据库管理员用户用户用户数据库系统应用

平台基础构架平台基础

平台硬件平台产品协同办公数据库系统操作系统中间件应用服务器置SQL

SELECT语句通过本章学习，您将可以:列举SQL

SELECT语句的功能。执行简单的选择语句。SQL

语言和SQL*Plus

命令的不同。SELECT

标识选择哪些列。FROM

标识从哪个表中选择。SELECT *|{[DISTINCT]

column|expression

[alias],...}FROM

table;SELECT

*FROM

departments;department_id,

location_idSELECTFROM

departments;SQL

语言大小写不敏感。SQL

可以写在一行或者多行关键字不能被缩写也不能分行各子句一般要分行写。使用缩进提高语句的可读性。SQL*Plus:数字和日期使用的数学表达式。操作符描述+加-减*乘/除SELECT

last_name,

salary,

salary

+

300FROM

employees;乘除的优先级高于加减。同一优先级运算符从左向右执行。括号内的运算先执行。*

/

+_优先SELECT

last_name,

salary,

12*salary+100FROM

employees;号SELECT

last_name,

salary,

12*(salary+100)FROM

employees;空值是无效的，未指定的，未知的或不可预知的值。空值不是空格或者0。SELECT

last_name,

job_id,

salary,

commission_pctFROM

employees;SELECT

last_name,

12*salary*commission_pctFROM

employees;包含空值的数学表达式的值都为空值列的别名:重命名一个列。便于计算。紧跟列名，也可以在列名和别名之间加入关键字‘AS’，以便在别名中包含空格或特殊的字符并区分大小写。用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

列表中的一个字符,数字,日期。日期和字符只能在单引号中出现。每当返回一行时，字符串被输出一次。SELECT

last_name ||'

is

a

'||jobidAS

"Employee

Details"FROM

employees;默认情况下，查询会返回全部行，包括重复行。SELECT

department_idFROM

employees;SELECT

DISTINCT

department_idFROM

employees;Oracle服务器SQL*Plus浏览器SQLstatements•••••••••SQL*Pluscommands使用SQL*Plus可以:描述表结构。编辑SQL

语句。执行SQL语句。将SQL

保存在文件中并将SQL语句执行结果保存在文件中。在保存的文件中执行语句。将文本文件装入SQL*Plus编辑窗口。使用DESCRIBE

命令，表示表结构DESC[RIBE]

tablenameDESCRIBE

employeesSELECT *|{[DISTINCT]

column|expression

[alias],...}FROM

table;•–––•和排序数据•通过本章学习，您将可以:在查询中过滤行。在查询中对行进行排序。返回在90号部门工作的所有员工的信息使用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;字符和日期要包含在单引号中。字符大小写敏感，日期格式敏感。默认的日期格式是DD-MON-RR。SELECT

last_name,

job_id,

department_idFROM

employeesWHERE last_name

=

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

last_name,

salaryFROM

employeesWHERE

salary<=

3000;操作符含义BETWEEN...AND...在两个值之间(包含边界)IN(set)等于值列表中的一个LIKE模糊查询IS

NULL空值使用BETWEEN

运算来显示在一个区间内的值。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运算显示列表中的值。使用LIKE

运算选择类似的值选择条件可以包含字符或数字:SELECTFROMWHERE_nameemployees_name

LIKE

'S%';‘%’和‘-’可以同时使用。可以使用ESC标识符选择‘%’和‘_’符号。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

要求和的关系为真。SELECT

employee_id,

last_name,

job_id,

salaryFROM

employeesWHEREORsalary

>=

10000job_id

LIKE

'%MAN%';OR

要求或关系为真。SELECT

last_name,

job_idFROM

employeesWHEREjob_idNOT

IN

('IT_PROG',

'ST_CLERK',

'SA_REP');优先级算术运算符连接符比较符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;使用括号控制执行顺序。SELECTFROMORDER

BY

hire

datelast_name,

job_id,

department_id,

hire_dateemployees;使用ORDER

BY

子句排序ORDER

BY

子句在SELECT语句的结尾。排序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

列表的顺序排序。可以使用不在SELECT

列表中的列排序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函数通过本章学习，您将可以:SQL中不同类型的函数。在SELECT

语句中使用字符，数字和日期函数。描述转换型函数的用途。函数参数1参数2参数n结果函数单行函数多行函数单行函数:操作数句对象接受函数返回一个结果只对一行进行变换每行返回一个结果可以转换数据类型可以嵌套参数可以是一列或一个值function_name

[(arg1,

arg2,...)]转换字符数值日期通用单行函数字符函数大小写控制函数字符控制函数函数结果LOWER('SQL

Course')UPPER('SQL

Course')INITCAP('SQL

Course')sql

courseSQL

COURSESql

Course这类函数改变字符的大小写。显示员工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';函数结果CONCAT(' o',

'World')oWorldSUBSTR('

oWorld',1,5)oLENGTH('

oWorld')10INSTR(' oWorld',

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

10,

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

FROM

'

oWorld')elloWorld这类函数控制字符: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:

截断TRUNC(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

数字 日期:

世纪,年,月,日,小时,分钟,秒。默认的日期格式是DD-MON-RR.SELECT

last_name,

hire_dateFROM

employeesWHERE last_name

like

'G%';函数SYSDATE

返回:日期时间在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时。SELECT

last_name,

(SYSDATE-hire_date)/7

AS

WEEKSFROM

employeesWHERE department_id

=

90;函数函数描述MONTHS_BETWEEN两个日期相差的月数ADD_MONTHS向指定日期中加上若干月数NEXT_DAY指定日期的下一个日期LAST_DAY本月的最后一天ROUND日期四舍五入TRUNC日期截断MONTHS_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')隐性显性数据类型转换Oracle

自动完成下列转换：源数据类型目标数据类型VARCHAR2

or

CHARNUMBERVARCHAR2

or

CHARDATENUMBERVARCHAR2DATEVARCHAR2表达式计算中,Oracle

自动完成下列转换:源数据类型目标数据类型VARCHAR2

or

CHARNUMBERVARCHAR2

or

CHARDATENUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE单行函数可以嵌套。嵌套函数的执行顺序是由内到外。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;这些函数适用于任何数据类型，同时也适用于空值：NVL

(expr1,

expr2)NVL2

(expr1,

expr2,

expr3)NULLIF

(expr1,

expr2)COALESCE

(expr1,

expr2,...,

exprn)将空值转换成一个已知的值：可以使用的数据类型有日期、字符、数字。函数的一般形式: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

逻辑时: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表达式的一个例子：在需要使用IF-THEN-ELSE

逻辑时: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函数的一个例子：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;通过本章学习，您应该学会:使用函数对数据进行计算使用函数修改数据使用函数控制一组数据的输出格式使用函数改变日期的显示格式使用函数改变数据类型使用NVL

函数使用IF-THEN-ELSE

逻辑查询通过本章学习，您将可以:使用等值和不等值连接在SELECT

语句中查询多个表中的数据。使用外连接查询不满足连接条件的数据。使用自连接。•在下面条件下产生:为了避免集，可以在WHERE

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

joinsNatural

joinsUsing

clauseFull

ortwo

sided

outer

joinsArbitrary

join

conditions

forouter

joinsEquijoinNon-equijoinOuter

joinSelf

join使用连接在多个表中查询数据。SELECT

table1.column,

table2.columnFROM

table1,

table2WHERE

table1.column1

=

table2.column2;在WHERE

字句中写入连接条件。在表中有相同列时，在列名之前加上表名前缀。外键主键SELECT

employees.employee_id,

employees.last_name,employees.department_id,

departments.department_id,departments.location_idFROM employees,

departmentsWHEREemployees.department

id

=

departments.department

id;使用表名前缀在多个表中区分相同的列。使用表名可以提高效率。在不同表中具有相同列名的列可以用别名加以区分。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;使用别名可以简化查询。使用表名前缀可以提高执行效率。连接n个表,至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。EMPLOYEES表中的列工资应在JOB_GRADES表中的最高工资与最低工资之间SELEC ast_name,

e.salary,

j.grade_levelFROM employees

e,

job_gradesjWHERE

e.salaryBETWEEN

j.lowest_sal

AND

j.highest_sal;190号部门没有员工使用外连接可以查询不满足连接条件的数据。外连接的符号是(+)。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

;使用连接从多个表中查询数据：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

子句使连接的表产生叉集。叉集和 集是相同的。SELECT

last_name,

department_nameFROM

employeesCROSS

JOINdepartments;NATURAL

JOIN

子句，会以两个表中具有相同名字的列为条件创建等值连接。在表中查询满足等值条件的数据。如果只是列名相同而数据类型不同，则会产生错误。SELECT

department_id,

department_name,location_id,

cityFROM

departmentsNATURAL

JOIN

locations;在NATURAL

JOIN

子句创建等值连接时，可以使用USING

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

可以在有多个列满足条件时进行选择。不要给选中的列中加上表名前缀或别名。NATURAL

JOIN

和USING

子句经常同时使用。SELECT

e.employee_id,

e.last_name,

d.location_idFROM employees

e

JOIN

departments

dUSING

(department

id);自然连接中是以具有相同名字的列为连接条件的。可以使用

ON

子句指 外的连接条件。这个连接条件是与其它条件分开的。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中，内连接只返回满足连接条件的数据。两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外联接。两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外联接。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.函数•通过本章学习，您将可以:了解组函数。描述组函数的用途。使用GROUP

BY

字句数据分组。使用HAVING

字句过滤分组结果集。分组函数作用于一组数据，并对一组数据返回一个值。表EMPLOYEES中的工资最大值AVGCOUNTMAXMINSTDDEVSUMSELECTFROM[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%';可以对数值型数据使用AVG

和SUM

函数。SELECTMIN(hire_date),

MAX(hire_date)FROM

employees;可以对任意数据类型的数据使用MIN

和MAX

函数。SELECTCOUNT(*)FROM

employeesWHERE

department_id

=

50;COUNT(*)返回表中记录总数。SELECTCOUNT(commissio

t)FROM

employeesWHERE department_id

=

80;COUNT(expr)返回expr不为空的记录总数。SELECTCOUNT(DISTINCT

department

id)FROM

employees;COUNT(DISTINCT

expr)返回expr非空且不重复的记录总数AVG(commissio

t)SELECTFROM

employees;组函数忽略空值。SELECTAVG(NVL(commissio t,

0))FROM

employees;NVL函数使分组函数无法忽略空值。44009500求出EMPLOYEES3500

表中各部门的平均工资640010033SELECTFROM[WHEREcolumn,

group_function(column)tablecondition][GROUP

BY

group_by_expression][ORDER

BY

column];可以使用GROUP

BY

子句将表中的数据分成若干组GROUP

BY

department_id;在SELECT

列表中所有未包含在组函数中的列都应该包含在GROUP

BY

子句中。SELECT

department_id,

AVG(salary)FROM

employeesSELECT

AVG(salary)FROM

employeesGROUP

BY

department_id;包含在GROUP

BY

子句中的列不必包含在SELECT

列表中。使用多个列进行分组SELECT

department_id

dept_id,

job_id,

SUM(salary)FROM

employeesGROUP

BY

department_id,

job_id

;所用包含于SELECT

列表中，而未包含于组函数中的列都必须包含于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

子句中使用组函数。可以在HAVING

子句中使用组函数。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.行已经被分组。使用了组函数。满足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];通过本章学习，您已经学会:使用组函数。在查询中使用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.询•通过本章学习，您将可以:描述子查询可以解决的问题定义子查询。列句子查询的类型。书写单行子查询和多行字查询。谁的工资比Abel

高?MainQuery:谁的工资比Abel

高?Subquery?Abel的工资是多少?询语法SELECT

select_listFROM

tableWHERE

expr

operator子查询(内查询)在主查询之前一次执行完成。子查询的结果被主查询使用(外查询)。(SELECTFROMselect_listtable);SELECT

last_nameFROMWHEREemployeessalary

>(SELECT

salaryFROMWHEREemployeeslast_name

=

'Abel');询11000子查询要包含在括号内。将子查询放在比较条件的右侧。除非进行Top-N

分析，否则不要在子查询中使用ORDER

BY子句。单行操作符对应单行子查询，多行操作符对应多行子查询。子查询返回单行子查询主查询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首先执行子查询。向主查询中的HAVING

子句返回结果。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等于列表中的任何一个ANY和子查询返回的任意一个值比较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通过本章学习，您已经学会:在什么时候遇到什么问题应该使用子查询。在查询是基于未知的值时应使用子查询。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.数据通过本章学习，您将可以:使用DML

语句向表中 数据更新表中数据从表中删除数据将表中数据和并控制事务DML

可以在下列条件下执行:事务是由完成若干项工作的DML语句组成的。使用

INSERT

语句向表中 数据。使用这种语法一次只能向表中 一条数据。INSERT

INTOVALUEStable

[(column

[,

column...])](value

[,

value...]);为每一列添加一个新值。按列的默认顺序列出各个列的值。在INSERT

子句中随意列出列名和他们的值。字符和日期型数据应包含在单引号中。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

记录当前系统的日期和时间。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.检查 的数据在INSERT

语句中加入子查询。不必书写VALUES

子句。子查询中的值列表应于INSERT

子句中的列名对应。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

语句更新数据。•可以一次更新多条数据。UPDATESET[WHEREtablecolumn

=

value

[,

column

=

value,

...]condition];WHERE employee_id

=

113;UPDATE

employeesSET department

id

=

701

row

updated.使用WHERE

子句指定需要更新的数据。如果省略WHERE子句，则表中的所有数据都将被更新。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号员工的工作和工资使其与205号员工相同。copy_empUPDATESET

department_id

=WHEREjob_id(SELECT

department_idFROM

employeesWHERE

employee_id

=

100)= (SELECT

job_idFROM

employeesWHERE

employee_id

=

200);1

row

updated.在UPDATE

中使用子查询，使更新基于另一个表中的数据。UPDATE

employees*ERROR

at

line

1:ORA-0229