Oracle 连接

Oracle 连接


1. 连接

-- 查询当前用户
select * from user_users;

-- 查询用户对象
select * from user_objects order by object_type;

-- 查询表结构
desc user_objects;

-- 创建连接测试表
create table t1 (c1 integer);
create table t2 (c1 integer, c2 varchar2(1));
create table t3 (c1 integer);
create table t4 (c1 integer, c2 varchar2(1));

insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(NULL);

insert into t2 values(1, 'A');
insert into t2 values(2, 'B');
insert into t2 values(NULL,NULL);

insert into t3 values(1);
insert into t3 values(2);
insert into t3 values(3);
insert into t3 values(4);


insert into t4 values(1, 'A');
insert into t4 values(2, 'B');

commit;

-- 等连接(默认是内连接)
select * from t1;
select * from t2;

select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 = b.c1;

-- 等连接 + 左外连接:先进行等连接,再进行左外连接
select * from t1;
select * from t2;

select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 = b.c1 (+);

select a.c1, b.c1, b.c2 from t3 a, t4 b where a.c1 = b.c1 (+);

-- 等连接 + 右外连接:先进行等连接,再进行右外连接
select * from t1;
select * from t2;

select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 (+) = b.c1;

select a.c1, b.c1, b.c2 from t3 a, t4 b where a.c1 (+) = b.c1;

select 1 from dual where NULL = NULL;

-- 不等连接(默认是内连接)
select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 > b.c1;

-- 不等连接 + 左外连接:先进行不等连接,再进行左外连接
select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 > b.c1 (+);

-- 不等连接 + 左外连接:先进行不等连接,再进行右外连接
select a.c1, b.c1, b.c2 from t1 a, t2 b where a.c1 (+) > b.c1;

-- 自等连接
select a.c1, b.c1 from t1 a, t1 b where a.c1 = b.c1;


-- 自等连接 + 外连接
select a.c1, b.c1 from t1 a, t1 b where a.c1 = b.c1 (+);
select a.c1, b.c1 from t1 a, t1 b where a.c1 (+) = b.c1;

-- 自不等连接
select a.c1, b.c1 from t1 a, t1 b where a.c1 > b.c1;

-- 全外连接
select c1, b.c2 from t1 a full outer join t2 b using (c1);
select a.c1, b.c1, b.c2 from t1 a full outer join t2 b on a.c1 = b.c1;

-- 交叉连接
select a.c1, b.c1, b.c2 from t1 a, t2 b;
select a.c1, b.c1, b.c2 from t1 a cross join t2 b;

-- 高效删除重复行
DELETE FROM t1 a WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM t1 b WHERE b.c1 = a.c1);

-- 查询低效的SQL
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
         ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
         ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
         SQL_TEXT 
FROM    V$SQLAREA 
WHERE   EXECUTIONS > 0 
AND      BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC; 

-- Decode / CASE函数的使用
select * from t2;

select c1, c2,
       decode(c2, 'A', 'Best', c2) lv
from t2;

select c1, c2, 
decode(c2, 
'A', '甲',
'B', '乙',
'无') sex
from t2;

2. PPT 附件