Oracle-Join

1、创建用户并授权

创建用户：create user test identified by hw
用户授权：grant connect, resource to hw

2、建表语句

CREATE TABLE webSites(
       id        INT PRIMARY KEY,
       name      VARCHAR2(30),
       url       VARCHAR2(100),
       alexa     INT,
       country   VARCHAR2(30)
);
INSERT INTO webSites(id,name,url,alexa,country) VALUES (1,'Google','http://www.google.com/',1,'USA');
INSERT INTO webSites(id,name,url,alexa,country) VALUES (2,'淘宝','http://www.taobao.com/',13,'CN');
INSERT INTO webSites(id,name,url,alexa,country) VALUES (3,'天猫','http://www.tianmao.com/',16,'CN');
INSERT INTO webSites(id,name,url,alexa,country) VALUES (4,'微博','http://www.weibo.com/',20,'CN');
INSERT INTO webSites(id,name,url,alexa,country) VALUES (5,'Facebook','http://www.facebook.com/',3,'USA');
INSERT INTO webSites(id,name,url,alexa,country) VALUES (7,'stackoverflow','http://www.stackoverflow.com/',0,'IND');
CREATE TABLE accessLog(
       aid      INT PRIMARY KEY,
       siteId  INT,
       count    INT,
       dateTime date
);
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (1,1,45,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (2,3,100,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (3,1,230,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (4,2,10,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (5,5,205,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (6,4,13,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (7,3,220,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (8,5,545,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (9,3,201,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO accessLog(aid,siteId,count,dateTime) VALUES (10,8,201,to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
COMMIT;
SELECT * FROM webSites;
SELECT * FROM accessLog;

3、SQL 连接(JOIN) 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

（1） INNER JOIN从多个表中返回满足 JOIN 条件的所有行，关键字在表中存在至少一个匹配时返回行，INNER JOIN 与 JOIN 是相同的。

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
INNER JOIN accessLog a
ON w.id = a.siteId
ORDER BY a.count

注释：INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Websites" 表中的行在 "access_log" 中没有匹配，则不会列出这些行。

不同的 SQL JOIN

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

（2）LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT 
  w.id,
  w.name,
  w.url,
  w.alexa,
  w.country,
  a.aid,
  a.site_id,
  a.count,
  a.datetime
FROM Websites w
LEFT JOIN access_log a
ON w.id = a.site_id
ORDER BY a.count

注释：LEFT JOIN 关键字从左表（webSites）返回所有的行，即使右表（accessLog）中没有匹配。

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

（3）RIGHT JOIN 关键字从右表（table2或accessLog）返回所有的行，即使左表（table1或webSites）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
RIGHT JOIN accessLog a
ON w.id = a.siteId
ORDER BY a.count

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

（4）FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
FULL OUTER JOIN accessLog a
ON w.id = a.siteId
ORDER BY a.count DESC

注释：FULL OUTER JOIN 关键字返回左表（WebSites）和右表（accessLog）中所有的行。如果 "Websites" 表中的行在 "accessLog" 中没有匹配或者 "accessLog" 表中的行在 "WebSites" 表中没有匹配，也会列出这些行。

注解：本次测试数据库是Oracle，MySQL中不支持 FULL OUTER JOIN。

（5）

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
LEFT JOIN accessLog a
ON w.id = a.siteId
WHERE a.siteId IS NULL

（6）

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
RIGHT JOIN accessLog a
ON w.id = a.siteId
WHERE w.id IS NULL

（7）FULL OUTER JOIN------- IS NULL返回关键字返回左表（WebSites）和右表（accessLog）中空行。

SELECT 
     w.id,
     w.name,
     w.url,
     w.alexa,
     w.country,
     a.aid,
     a.siteId,
     a.count,
     a.datetime
FROM Websites w
FULL OUTER JOIN accessLog a
ON w.id = a.siteId
WHERE w.id IS NULL OR a.siteId IS NULL

posted @ 2019-05-04 13:08 清-华-园阅读(594) 评论(0) 编辑收藏举报

刷新页面返回顶部

清-华-园

Oracle-Join

公告