DB2 730学习笔记6
使用联结从多个表中检索数据
?
联结join是一种将来自两个或更多表中的数据组合起来的查询。
?
查询标识出哪些是经理的职员
select * from org
?
?DEPTNUMB DEPTNAME?????? MANAGER DIVISION?? LOCATION????
-------- -------------- ------- ---------- -------------
????? 10 Head Office??????? 160 Corporate? New York????
????? 15 New England???????? 50 Eastern??? Boston??????
????? 20 Mid Atlantic??????? 10 Eastern??? Washington??
????? 38 South Atlantic????? 30 Eastern??? Atlanta?????
????? 42 Great Lakes??????? 100 Midwest??? Chicago?????
????? 51 Plains???????????? 140 Midwest??? Dallas??????
????? 66 Pacific??????????? 270 Western??? San Francisco
????? 84 Mountain?????????? 290 Western??? Denver??????
? 8 record(s) selected.
?
select * from staff
?
ID???? NAME????? DEPT?? JOB?? YEARS? SALARY??? COMM????
------ --------- ------ ----- ------ --------- ---------
??? 10 Sanders?????? 20 Mgr??????? 7? 98357.50???????? -
??? 20 Pernal??????? 20 Sales????? 8? 78171.25??? 612.45
??? 30 Marenghi????? 38 Mgr??????? 5? 77506.75???????? -
??? 40 O'Brien?????? 38 Sales????? 6? 78006.00??? 846.55
??? 50 Hanes???????? 15 Mgr?????? 10? 80659.80???????? -
??? 60 Quigley?????? 38 Sales????? -? 66808.30??? 650.25
??? 70 Rothman?????? 15 Sales????? 7? 76502.83?? 1152.00
??? 80 James???????? 20 Clerk????? -? 43504.60??? 128.20
??? 90 Koonitz?????? 42 Sales????? 6? 38001.75?? 1386.70
?? 100 Plotz???????? 42 Mgr??????? 7? 78352.80???????? -
?? 110 Ngan????????? 15 Clerk????? 5? 42508.20??? 206.60
?? 120 Naughton????? 38 Clerk????? -? 42954.75??? 180.00
?? 130 Yamaguchi???? 42 Clerk????? 6? 40505.90???? 75.60
?? 140 Fraye???????? 51 Mgr??????? 6? 91150.00???????? -
?? 150 Williams????? 51 Sales????? 6? 79456.50??? 637.65
?? 160 Molinare????? 10 Mgr??????? 7? 82959.20???????? -
?? 170 Kermisch????? 15 Clerk????? 4? 42258.50??? 110.10
?? 180 Abrahams????? 38 Clerk????? 3? 37009.75??? 236.50
?? 190 Sneider?????? 20 Clerk????? 8? 34252.75??? 126.50
?? 200 Scoutten????? 42 Clerk????? -? 41508.60???? 84.20
?? 210 Lu??????????? 10 Mgr?????? 10? 90010.00???????? -
?? 220 Smith???????? 51 Sales????? 7? 87654.50??? 992.80
?? 230 Lundquist???? 51 Clerk????? 3? 83369.80??? 189.65
?? 240 Daniels?????? 10 Mgr??????? 5? 79260.25???????? -
?? 250 Wheeler?????? 51 Clerk????? 6? 74460.00??? 513.30
?? 260 Jones???????? 10 Mgr?????? 12? 81234.00???????? -
?? 270 Lea?????????? 66 Mgr??????? 9? 88555.50???????? -
?? 280 Wilson??????? 66 Sales????? 9? 78674.50??? 811.50
?? 290 Quill???????? 84 Mgr?????? 10? 89818.00???????? -
?? 300 Davis???????? 84 Sales????? 5? 65454.50??? 806.10
?? 310 Graham??????? 66 Sales???? 13? 71000.00??? 200.30
?? 320 Gonzales????? 66 Sales????? 4? 76858.20??? 844.00
?? 330 Burke???????? 66 Clerk????? 1? 49988.00???? 55.50
?? 340 Edwards?????? 84 Sales????? 7? 67844.00?? 1285.00
?? 350 Gafney??????? 84 Clerk????? 5? 43030.50??? 188.00
? 35 record(s) selected.
?
联结查询（内联结查询，返回差集中满足联结条件的行）?
SELECT deptnumb, deptname, id AS manager_id, name AS manager
????? FROM org, staff? WHERE manager = id? ORDER BY deptnumb
?
?
也可以写为:
SELECT?deptnumb,deptname,id?AS manager_id,name?AS manager
??????FROM org INNER JOIN staff ON manager = id?ORDER?BY deptnumb
?
DEPTNUMB DEPTNAME?????? MANAGER_ID MANAGER?
-------- -------------- ---------- ---------
????? 10 Head Office?????????? 160 Molinare
????? 15 New England??????????? 50 Hanes???
????? 20 Mid Atlantic?????????? 10 Sanders?
????? 38 South Atlantic???????? 30 Marenghi
????? 42 Great Lakes?????????? 100 Plotz???
????? 51 Plains??????????????? 140 Fraye???
????? 66 Pacific?????????????? 270 Lea?????
????? 84 Mountain????????????? 290 Quill???
? 8 record(s) selected.
?
?
外部联结Outer?join返回内部联结操作产生的行，加上内部联结操作不会返回的行。
有三种类型的外部联结：
左外部联结Left outer join或Left join包括内部联结，加上左表中内部联结操作不会返回的行。
?
SELECT?deptnumb,deptname,id?AS manager_id,name?AS manager? FROM org LEFT JOIN staff ON manager = id?ORDER?BY deptnumb
?
DEPTNUMB DEPTNAME?????? MANAGER_ID MANAGER?
-------- -------------- ---------- ---------
????? 10 Head Office?????????? 160 Molinare
????? 15 New England??????????? 50 Hanes???
????? 20 Mid Atlantic?????????? 10 Sanders?
????? 38 South Atlantic???????? 30 Marenghi
????? 42 Great Lakes?????????? 100 Plotz???
????? 51 Plains??????????????? 140 Fraye???
????? 66 Pacific?????????????? 270 Lea?????
????? 84 Mountain????????????? 290 Quill???
? 8 record(s) selected.?
?
右外部联结Right outer join或Rigth join包括内部联结，加上右表中内部联结操作不会返回的行。
SELECT?deptnumb,deptname,id?AS manager_id,name?AS manager? FROM org RIGHT JOIN staff ON manager = id?ORDER?BY deptnumb
?
DEPTNUMB DEPTNAME?????? MANAGER_ID MANAGER?
-------- -------------- ---------- ---------
????? 10 Head Office?????????? 160 Molinare
????? 15 New England??????????? 50 Hanes???
????? 20 Mid Atlantic?????????? 10 Sanders?
????? 38 South Atlantic???????? 30 Marenghi
????? 42 Great Lakes?????????? 100 Plotz???
????? 51 Plains??????????????? 140 Fraye???
????? 66 Pacific?????????????? 270 Lea?????
????? 84 Mountain????????????? 290 Quill???
?????? - -????????????????????? 20 Pernal??
?????? - -????????????????????? 40 O'Brien?
?????? - -????????????????????? 60 Quigley?
?????? - -????????????????????? 70 Rothman?
?????? - -????????????????????? 80 James???
?????? - -????????????????????? 90 Koonitz?
?????? - -???????????????????? 110 Ngan????
?????? - -???????????????????? 120 Naughton
?????? - -???????????????????? 130 Yamaguchi
?????? - -???????????????????? 150 Williams
?????? - -???????????????????? 170 Kermisch
?????? - -???????????????????? 180 Abrahams
?????? - -???????????????????? 190 Sneider?
?????? - -???????????????????? 200 Scoutten
?????? - -???????????????????? 210 Lu??????
?????? - -???????????????????? 220 Smith???
?????? - -???????????????????? 230 Lundquist
?????? - -???????????????????? 240 Daniels?
?????? - -???????????????????? 250 Wheeler?
?????? - -???????????????????? 260 Jones???
?????? - -???????????????????? 280 Wilson??
?????? - -???????????????????? 300 Davis???
?????? - -???????????????????? 310 Graham??
?????? - -???????????????????? 320 Gonzales
?????? - -???????????????????? 330 Burke???
?????? - -???????????????????? 340 Edwards?
?????? - -???????????????????? 350 Gafney??
? 35 record(s) selected.
?
完全外部联结Full outer join或Full join包括内部联结，加上左表和右表中联结操作不会返回的行。?
?
使用UNION集合操作符将两个或更多的查询组合成一个查询。
?
SELECT sales_person FROM sales WHERE region = 'Ontario-South'
?
SALES_PERSON??
---------------
LUCCHESSI?????
LEE???????????
LUCCHESSI?????
LEE???????????
GOUNOT????????
LUCCHESSI?????
LEE???????????
GOUNOT????????
LEE???????????
GOUNOT????????
LUCCHESSI?????
LEE???????????
GOUNOT????????
? 13 record(s) selected.
?
SELECT sales_person FROM sales WHERE sales > 3
?
SALES_PERSON??
---------------
LEE???????????
GOUNOT????????
LEE???????????
LEE???????????
LEE???????????
GOUNOT????????
LEE???????????
LEE???????????
LEE???????????
LEE???????????
LEE???????????
GOUNOT????????
? 12 record(s) selected.
?
SELECT sales_person FROM sales
? WHERE region = 'Ontario-South'
UNION
SELECT sales_person FROM sales
? WHERE sales > 3
?
SALES_PERSON??
---------------
GOUNOT????????
LEE???????????
LUCCHESSI?????
? 3 record(s) selected.
?
?
EXCEPT集合操作符生成的结果表中包含第一个查询返回的所有行，但是去掉了第二个任何后续查询返回的行。
?
SELECT sales_person FROM sales
? WHERE region = 'Ontario-South'
EXCEPT
SELECT sales_person FROM sales
? WHERE sales > 3
?
SALES_PERSON??
---------------
LUCCHESSI?????
? 1 record(s) selected.
?
INTERSECT集合操作符生成的结果表只包含所有查询都返回的行。
?
SELECT sales_person FROM sales
? WHERE region = 'Ontario-South'
INTERSECT
SELECT sales_person FROM sales
? WHERE sales > 3
?
SALES_PERSON??
---------------
GOUNOT????????
LEE???????????
? 2 record(s) selected.
?
?
使用GROUP BY 子句对结果进行总结
使用GROUP BY 自己对结果集中的行进行组织。每个组在结果集中由一行表示。
?
SELECT sales_date,MAX(sales) AS max_sales FROM sales GROUP BY sales_dateSALES_DATE
?
SALES_DATE MAX_SALES?
---------- -----------
03/29/1996?????????? 2
12/31/2005?????????? 3
03/29/2006?????????? 7
03/30/2006????????? 18
03/31/2006????????? 14
04/01/2006?????????? 9
?
GROUPING SETS子句，分组集可以用来在一遍分析不同聚合层次上的数据。
YEAR函数返回日期值的年份；
SUM函数用来返回每个分组的销售区间的总量；
分组集列表指定如何对数据进行分组；
在分组集列表中添加一对空的圆括号，可以获得结果集中的总量。
?
SELECT YEAR(sales_date) AS year,region,SUM(sales) AS total_sale FROM sales GROUP BY GROUPING SETS (YEAR(sales_date),region,())
?
YEAR??????? REGION????????? TOTAL_SALE
----------- --------------- -----------
????????? - -????????????????????????????? 155
????????? - Manitoba???????????????? 41
????????? - Ontario-North???????????? 9
????????? - Ontario-South??????????? 52
????????? - Quebec??????????????????? 53
?????? 1996 -?????????????????????????? 2
?????? 2005 -?????????????????????????? 8
?????? 2006 -?????????????????????????? 145
? 8 record(s) selected.
?
HAVING子句与GROUP BY 子句一起使用，检索出满足特定条件的组的结果。
返回销售总量超过25的销售人员。
?
?SELECT sales_person, SUM(sales) AS total_sales FROM sales
? GROUP BY sales_person
? HAVING SUM(sales) > 25
?
SALES_PERSON??? TOTAL_SALES
--------------- -----------
GOUNOT?????????????????? 50
LEE???????????????????????????91
? 2 record(s) selected.
?
?
?
?
?
?
?????
????? ?
?
?
