东软-oracle新补充内容

本章学习目标Oracle自带客户端工具介绍连接种类补充使用子查询注意事项TopN查询数据分页问题使用子查询创建表表间数据拷贝merge语句清空表数据方法Oracle自带客户端工具SqlplusSql

plus

worksheetisqlplusSql

plusSql

plusworksheetisqlplus前提：启动服务isqlplus连接种类补充交叉连接(Cross

join)◻

Cross

join产生了一个集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;

举例：select

empno,ename,

sal,

emp.deptno,

dnamefrom

emp

cross

join

dept;连接种类补充自然连接(Natural

join)

Natural

join基于两个表中的全部同名列建立连接从两个表中选出同名列的值均对应相等的所有行如果两个表中同名列的数据类型不同，则出错不允许在参照列上使用表名或者别名作为前缀

举例：select

empno,

ename,

sal,

deptno,

dnamefrom

emp

natural

join

dept;Using子句

如果不希望参照被连接表的所有同名列进行等值连接，自然连接将

要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。举例：select

empno,

ename,

sal,

deptno,

dnamefrom

empjoin

dept

using

(deptno);不允许在参照列上使用表名或者别名作为前缀使用子查询注意事项

在查询是基于未知值时应考虑使用子查询

子查询必须包含在括号内

建议将子查询放在比较运算符的右侧，以增强可读性。

除非进行Top-N

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

子句。

对单行子查询使用单行运算符

对多行子查询使用多行运算符子查询空值/多值问题

如果子查询未返回任何行，则主查询也不会返回任何结果select

*

from

empwhere

sal

>

(select

sal

from

emp

where

empno

=

7369);

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符select

*

from

empwhere

sal

>

(select

sal

from

emp

where

empno

=

7566);

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符select

*

from

empwhere

sal

>

(select

avg(sal)

from

emp

group

by

deptno);

//TopN查询 在ORACLE中通常采用子查询的方式来实现TOP

N查询 语法格式：select

字段列表from(select

字段列表from

table

order

by

排序字段)where

rownum

<=

n;select

*from

(select

*

from

emp

order

by

sal

desc)where

rownum

<=

5;5-10条记录select

*

from(select

rownum

mynum

,

a.*

from(select

*

from

emp

order

by

sal

desc)a)where

mynum>=5and

mynum<=10补充问题数据分页每页显示M条记录，第N页查询的结果的范围数据分页每页显示n条记录，第p页查询的结果的范围例：每页显示5条1到5条6到10条1页2页.

……100页

496到500101页

501到505p

(p-1)*n+1到p*n使用子查询创建表

在创建表的同时，可以将子查询的结果直接其中：新建表与子查询结果的字段列表必须匹配新建表的字段列表可以缺省语法格式create

tabletablename

(column

[,...])as

subquery;例：create

tablemyemp(

, ,年薪)as

selectempno,

ename,sal*12from

emp;create

tablemyemp2as

selectempno,

ename,sal*12

annsal

from

emp;表间数据拷贝可以在insert

语句中使用子查询，实现表间数据拷贝：create

table

dept1(id

number(2),name

varchar2(14))insert

into

dept1(id,

name)

select

deptno,

dname

from

dept;

说明：此时不必再给出values子句子查询中的值列表应与insert子句中的字段列表相对应merge语句merge语句用于进行数据合并——根据条件在表中执行数据的修改或操作，如果要

的记录在目标表中已经存在，则执行更新操作、否则执行 操作。 语法：merge

into

table

[alias]using

(table

|

view

|

sub_query)

[alias]on

(join_condition)when

matched

thenupdate

set

col1

=

col1_val,

col2

=

col2_valwhen

not

matched

theninsert

(column_list)

values

(column_values);merge语句用法举例：create

table

test1(id

number(10)

primary

key,

name

varchar2(20));');');insert

into

test1

values

(1001,

'insert

into

test1

values

(1002,

'select

*

from

test1;create

table

test2(id

number(10)

primary

key,name

varchar2(20))');insert

into

test2

values

(1002,

'select

*

from

test2;merge

into

test2

using

test1

on(test1.id

=

test2.id)when

matched

thenupdate

set

name

=when

not

matched

theninsert

(id,

name)

values(test1.id,

);select

*

from

test2;清空表数据

truncate

table语句用于清空表中数据：表的 空间