Oracle数据库关键字用法,MERGE、START WITH 、OVER(PARTITION BY) 、MINUS

1.MERGE 判断table1和table2是否满足ON中条件，如果满足则用table2表去更新table1表，如果不满足，则将table2表数据插入table1表但是有很多可选项,表关联更新

MERGE  INTO table1 a

    USING(

                 select b.id bid,b.name bname from table2 b left join table1 c on c.id = b.id

)bb on(a.id = bb.bid)

WHEN MATCHED THEN

UPDATE SET

        a.name = bb.bname

WHEN NOT MATCHED THEN

INSERT(

a.id,a.name

)VALUES(

bb.bid,bb.bname

)

2.START WITH... CONNECT BY PRIOR...的作用，简单来说，就是将一个树状结构存储在一张表里，比如菜单表

select * from table_menu start with menu_id='210' connect by prior menu_id=menu_parent_id

查询菜单id为210下所有子菜单包括id为210的菜单。

3.OVER(PARTITION BY)根据表中字段分割后在排序取第一条

select * from (select r.*, row_number() over(PARTITION BY 字段1，字段2 order by 字段3 desc) rn

from table1 r

) where rn = 1

4.MINUS 表之间比较

select  id,name from table1

minus

select id,name from table2

table1中有table2中无或者table1中id,name与table2中不同

数据库实例名称为orcl的话， 方法一： windows系统可以查看windows服务，如OracleServiceORCL 方法二： 查看安装目录下相应目录名称，如：D：\app\oracle\admin\orcl 方法三： 使用oracle net manager工具查看。

一般保留关键字不能用做对象名

ACCESS

ADD

ALL

ALTER

AND

ANY

AS

ASC

AUDIT

BETWEEN

BY

CHAR

CHECK

CLUSTER

COLUMN

COMMENT

COMPRESS

CONNECT

CREATE

CURRENT

DATE

DECIMAL

DEFAULT

DELETE

DESC

DISTINCT

DROP

ELSE

EXCLUSIVE

EXISTS

FILE

FLOAT

FOR

FROM

GRANT

GROUP

HAVING

IDENTIFIED

IMMEDIATE

IN

INCREMENT

INDEX

INITIAL

INSERT

INTEGER

INTERSECT

INTO

IS

LEVEL

LIKE

LOCK

LONG

MAXEXTENTS

MINUS

MLSLABEL

MODE

MODIFY

NOAUDIT

NOCOMPRESS

NOT

NOWAIT

NULL

NUMBER

OF

OFFLINE

ON

ONLINE

OPTION

OR

ORDER

P

CTFREE

PRIOR

PRIVILEGES

PUBLIC

RAW

RENAME

RESOURCE

REVOKE

ROW

ROWID

ROWNUM

ROWS

SELECT

SESSION

SET

SHARE

SIZE

SMALLINT

START

SUCCESSFUL

SYNONYM

SYSDATE

TABLE

THEN

TO

TRIGGER

UID

UNION

UNIQUE

UPDATE

USER

VALIDATE

VALUES

VARCHAR

VARCHAR2

VIEW

WHENEVER

WHERE

WITH

详细信息可以查看v$reserved_words视图

---