SELECT 多表查询语法

最新推荐文章于 2023-03-27 15:03:13 发布
最新推荐文章于 2023-03-27 15:03:13 发布
阅读量346 收藏
点赞数


[@more@]一. 两个表间的联接查询
1. 笛卡尔联接：两个表中的每一行都要互相配对。又称交叉联接。得到的都是m*n.
比较适合执行某些统计过程以分析数据时。
实现方法：
1）传统方法：不在 WHERE 子句中包含联接条件
例子： SELECT title, NAME
FROM books, publisher;
2）使用 CROSS JOIN 关键字，不使用逗号
例子： SELECT title, NAME
FROM books CROSS JOIN publisher;

2-4是内部联接：
内部联接：只有当联接的每个表都包括一个对应的记录时才会返回一行。
默认为 [ INNER ] join

2. 相等联接：对在一个公共列中存储了相同数据的多个表创建的联接。
1）传统方法：使用 WHERE 子句
例子： SELECT title, NAME
FROM books, publisher
WHERE books.pubid = publisher.pubid;
2） JOIN 方法：
方法一：
... NATURAL JOIN ...：暗示两个表中包含同等命名和定义的字段，
不允许对建立联接的列加列修饰符。
/* 注意点：不需要使用条件来建立联接，但是必须存在一个公共列。
当没有公共列时，会产生笛卡尔积的结果。*/
例子：
SQL> select title, pubid, name
2 from publisher natural join books;

TITLE PUBID NAME
------------------------------ ----- -----------------------
BODYBUILD IN 10 MINUTES A DAY 4 READING MATERIALS INC.
REVENGE OF MICKEY 1 PRINTING IS US
BUILDING A CAR WITH TOOTHPICKS 2 PUBLISH OUR WAY
DATABASE IMPLEMENTATION 3 AMERICAN PUBLISHING
COOKING WITH MUSHROOMS 4 READING MATERIALS INC.
HOLY GRAIL OF ORACLE 3 AMERICAN PUBLISHING
HANDCRANKED COMPUTERS 3 AMERICAN PUBLISHING
E-BUSINESS THE EASY WAY 2 PUBLISH OUR WAY
PAINLESS CHILD-REARING 5 REED-N-RITE
THE WOK WAY TO COOK 4 READING MATERIALS INC.
BIG BEAR AND LITTLE DOVE 5 REED-N-RITE
HOW TO GET FASTER PIZZA 4 READING MATERIALS INC.
SHORTEST POEMS 5 REED-N-RITE
HOW TO MANAGE THE MANAGER 1 PRINTING IS US

14 rows selected

SQL> SELECT title,gift
2 FROM books NATURAL JOIN promotion;

TITLE GIFT
------------------------------ ---------------
BODYBUILD IN 10 MINUTES A DAY BOOKMARKER
REVENGE OF MICKEY BOOKMARKER
BUILDING A CAR WITH TOOTHPICKS BOOKMARKER
DATABASE IMPLEMENTATION BOOKMARKER
COOKING WITH MUSHROOMS BOOKMARKER
HOLY GRAIL OF ORACLE BOOKMARKER
HANDCRANKED COMPUTERS BOOKMARKER
E-BUSINESS THE EASY WAY BOOKMARKER
PAINLESS CHILD-REARING BOOKMARKER
THE WOK WAY TO COOK BOOKMARKER
BIG BEAR AND LITTLE DOVE BOOKMARKER
HOW TO GET FASTER PIZZA BOOKMARKER
SHORTEST POEMS BOOKMARKER
HOW TO MANAGE THE MANAGER BOOKMARKER
BODYBUILD IN 10 MINUTES A DAY BOOK LABELS
REVENGE OF MICKEY BOOK LABELS
BUILDING A CAR WITH TOOTHPICKS BOOK LABELS
DATABASE IMPLEMENTATION BOOK LABELS
COOKING WITH MUSHROOMS BOOK LABELS
HOLY GRAIL OF ORACLE BOOK LABELS
HANDCRANKED COMPUTERS BOOK LABELS
E-BUSINESS THE EASY WAY BOOK LABELS
PAINLESS CHILD-REARING BOOK LABELS
THE WOK WAY TO COOK BOOK LABELS
BIG BEAR AND LITTLE DOVE BOOK LABELS
HOW TO GET FASTER PIZZA BOOK LABELS
SHORTEST POEMS BOOK LABELS
HOW TO MANAGE THE MANAGER BOOK LABELS
BODYBUILD IN 10 MINUTES A DAY BOOK COVER
REVENGE OF MICKEY BOOK COVER
BUILDING A CAR WITH TOOTHPICKS BOOK COVER
DATABASE IMPLEMENTATION BOOK COVER
COOKING WITH MUSHROOMS BOOK COVER
HOLY GRAIL OF ORACLE BOOK COVER
HANDCRANKED COMPUTERS BOOK COVER
E-BUSINESS THE EASY WAY BOOK COVER
PAINLESS CHILD-REARING BOOK COVER
THE WOK WAY TO COOK BOOK COVER
BIG BEAR AND LITTLE DOVE BOOK COVER
HOW TO GET FASTER PIZZA BOOK COVER
SHORTEST POEMS BOOK COVER
HOW TO MANAGE THE MANAGER BOOK COVER
BODYBUILD IN 10 MINUTES A DAY FREE SHIPPING
REVENGE OF MICKEY FREE SHIPPING
BUILDING A CAR WITH TOOTHPICKS FREE SHIPPING
DATABASE IMPLEMENTATION FREE SHIPPING
COOKING WITH MUSHROOMS FREE SHIPPING
HOLY GRAIL OF ORACLE FREE SHIPPING
HANDCRANKED COMPUTERS FREE SHIPPING
E-BUSINESS THE EASY WAY FREE SHIPPING
PAINLESS CHILD-REARING FREE SHIPPING
THE WOK WAY TO COOK FREE SHIPPING
BIG BEAR AND LITTLE DOVE FREE SHIPPING
HOW TO GET FASTER PIZZA FREE SHIPPING
SHORTEST POEMS FREE SHIPPING
HOW TO MANAGE THE MANAGER FREE SHIPPING

56 rows selected

相等联接 ... NATURAL ... JOIN ...
/*注意点： 只需要指出公共列,不能使用别名，不能使用列限定词。
连续使用相等连接时
要注意每个相等连接前的关键字都必须是from或者natural join。*/

例子：
SQL> select title, gift
2 from books natural join promotion
3 where retail between minretail and maxretail;

TITLE GIFT
------------------------------ ---------------
BIG BEAR AND LITTLE DOVE BOOKMARKER
REVENGE OF MICKEY BOOK LABELS
COOKING WITH MUSHROOMS BOOK LABELS
HANDCRANKED COMPUTERS BOOK LABELS
BODYBUILD IN 10 MINUTES A DAY BOOK COVER
DATABASE IMPLEMENTATION BOOK COVER
E-BUSINESS THE EASY WAY BOOK COVER
THE WOK WAY TO COOK BOOK COVER
HOW TO GET FASTER PIZZA BOOK COVER
SHORTEST POEMS BOOK COVER
HOW TO MANAGE THE MANAGER BOOK COVER
BUILDING A CAR WITH TOOTHPICKS FREE SHIPPING
HOLY GRAIL OF ORACLE FREE SHIPPING
PAINLESS CHILD-REARING FREE SHIPPING

14 rows selected

SQL> SELECT NAME, title
2 FROM books NATURAL JOIN publisher
3 WHERE CATEGORY = 'FITNESS' ;

NAME TITLE
----------------------- ------------------------------
READING MATERIALS INC. BODYBUILD IN 10 MINUTES A DAY


SQL> SELECT NAME, title
2 FROM books NATURAL JOIN publisher
3 WHERE CATEGORY = 'FITNESS' OR books.pubid = 4;

SELECT NAME, title
FROM books NATURAL JOIN publisher
WHERE CATEGORY = 'FITNESS' OR books.pubid = 4

ORA-25155: NATURAL 联接中使用的列不能有限定词


SQL>
SELECT NAME, title
FROM books NATURAL JOIN publisher
WHERE CATEGORY = 'FITNESS' AND books.pubid = 4

ORA-25155: NATURAL 联接中使用的列不能有限定词

SQL>
SELECT NAME, title
FROM books NATURAL JOIN publisher
WHERE books.pubid < 4

ORA-25155: NATURAL 联接中使用的列不能有限定词

SQL>
SELECT NAME, title
FROM books NATURAL JOIN publisher
WHERE books.pubid = 4

ORA-25155: NATURAL 联接中使用的列不能有限定词


SQL>
SELECT NAME, title
FROM books NATURAL JOIN publisher
WHERE books.pubid = '4'

ORA-25155: NATURAL 联接中使用的列不能有限定词


SQL> SELECT NAME, title
2 FROM books NATURAL JOIN publisher
3 WHERE pubid > 4;

NAME TITLE
----------------------- ------------------------------
REED-N-RITE PAINLESS CHILD-REARING
REED-N-RITE BIG BEAR AND LITTLE DOVE
REED-N-RITE SHORTEST POEMS


SQL> SELECT lastname || firstname customername
2 FROM books NATURAL JOIN orderitems
3 WHERE CATEGORY = 'COMPUTER'
4 NATURAL JOIN orders
5 NATURAL JOIN customers
6 WHERE state = 'FL';

SELECT lastname || firstname customername
FROM books NATURAL JOIN orderitems
WHERE CATEGORY = 'COMPUTER'
NATURAL JOIN orders
NATURAL JOIN customers
WHERE state = 'FL'

ORA-00933: SQL 命令未正确结束


SQL> SELECT lastname || firstname customername
2 FROM (SELECT * FROM books NATURAL JOIN orderitems
3 WHERE CATEGORY = 'COMPUTER')
4 NATURAL JOIN orders
5 NATURAL JOIN customers
6 WHERE state = 'FL';

CUSTOMERNAME
--------------------
MORALESBONITA
SMITHLEILA
MORALESBONITA



方法二：
使用 ... JOIN ... USING ：使用在两个表具有相同列名称和内容的列创建联接。
不能对该列使用列修饰符，列必须在括号内出现。
例子：
SQL> select title, pubid, name
2 from books join publisher
3 using (pubid);

TITLE PUBID NAME
------------------------------ ----- -----------------------
BODYBUILD IN 10 MINUTES A DAY 4 READING MATERIALS INC.
REVENGE OF MICKEY 1 PRINTING IS US
BUILDING A CAR WITH TOOTHPICKS 2 PUBLISH OUR WAY
DATABASE IMPLEMENTATION 3 AMERICAN PUBLISHING
COOKING WITH MUSHROOMS 4 READING MATERIALS INC.
HOLY GRAIL OF ORACLE 3 AMERICAN PUBLISHING
HANDCRANKED COMPUTERS 3 AMERICAN PUBLISHING
E-BUSINESS THE EASY WAY 2 PUBLISH OUR WAY
PAINLESS CHILD-REARING 5 REED-N-RITE
THE WOK WAY TO COOK 4 READING MATERIALS INC.
BIG BEAR AND LITTLE DOVE 5 REED-N-RITE
HOW TO GET FASTER PIZZA 4 READING MATERIALS INC.
SHORTEST POEMS 5 REED-N-RITE
HOW TO MANAGE THE MANAGER 1 PRINTING IS US

14 rows selected

方法三：
使用 ... JOIN ... ON : 当相同内容的列在两个表中名称不同时用来创建联接，
要使用列修饰符。
例子：
SQL> SELECT title, b.pubid, NAME
2 FROM books b JOIN publisher p
3 ON b.pubid = p.pubid;

TITLE PUBID NAME
------------------------------ ----- -----------------------
BODYBUILD IN 10 MINUTES A DAY 4 READING MATERIALS INC.
REVENGE OF MICKEY 1 PRINTING IS US
BUILDING A CAR WITH TOOTHPICKS 2 PUBLISH OUR WAY
DATABASE IMPLEMENTATION 3 AMERICAN PUBLISHING
COOKING WITH MUSHROOMS 4 READING MATERIALS INC.
HOLY GRAIL OF ORACLE 3 AMERICAN PUBLISHING
HANDCRANKED COMPUTERS 3 AMERICAN PUBLISHING
E-BUSINESS THE EASY WAY 2 PUBLISH OUR WAY
PAINLESS CHILD-REARING 5 REED-N-RITE
THE WOK WAY TO COOK 4 READING MATERIALS INC.
BIG BEAR AND LITTLE DOVE 5 REED-N-RITE
HOW TO GET FASTER PIZZA 4 READING MATERIALS INC.
SHORTEST POEMS 5 REED-N-RITE
HOW TO MANAGE THE MANAGER 1 PRINTING IS US

14 rows selected


3. 不等联接：表中不存在可以联接的相同行时使用来创建联接。
比如说发现某个匹配范围对应的数据。
/*必须确定范围没有重叠的值，否则会返回两个结果。*/

1）传统方法：使用 WHERE 子句
SQL> select title, gift
2 from books, promotion
3 where retail between minretail and maxretail;

TITLE GIFT
------------------------------ ---------------
BIG BEAR AND LITTLE DOVE BOOKMARKER
REVENGE OF MICKEY BOOK LABELS
COOKING WITH MUSHROOMS BOOK LABELS
HANDCRANKED COMPUTERS BOOK LABELS
BODYBUILD IN 10 MINUTES A DAY BOOK COVER
DATABASE IMPLEMENTATION BOOK COVER
E-BUSINESS THE EASY WAY BOOK COVER
THE WOK WAY TO COOK BOOK COVER
HOW TO GET FASTER PIZZA BOOK COVER
SHORTEST POEMS BOOK COVER
HOW TO MANAGE THE MANAGER BOOK COVER
BUILDING A CAR WITH TOOTHPICKS FREE SHIPPING
HOLY GRAIL OF ORACLE FREE SHIPPING
PAINLESS CHILD-REARING FREE SHIPPING

14 rows selected

2）使用 ... JOIN ... ON
例子：
SQL> select title, gift
2 from books join promotion
3 on retail between minretail and maxretail;

TITLE GIFT
------------------------------ ---------------
BIG BEAR AND LITTLE DOVE BOOKMARKER
REVENGE OF MICKEY BOOK LABELS
COOKING WITH MUSHROOMS BOOK LABELS
HANDCRANKED COMPUTERS BOOK LABELS
BODYBUILD IN 10 MINUTES A DAY BOOK COVER
DATABASE IMPLEMENTATION BOOK COVER
E-BUSINESS THE EASY WAY BOOK COVER
THE WOK WAY TO COOK BOOK COVER
HOW TO GET FASTER PIZZA BOOK COVER
SHORTEST POEMS BOOK COVER
HOW TO MANAGE THE MANAGER BOOK COVER
BUILDING A CAR WITH TOOTHPICKS FREE SHIPPING
HOLY GRAIL OF ORACLE FREE SHIPPING
PAINLESS CHILD-REARING FREE SHIPPING

14 rows selected


4. 自我联接：引用同一个表的数据作关联时，通过使用表别名使之看起来像是联接两个表。
1）传统方法：使用 WHERE 子句。
例子：
select c.firstname ||' '|| c.lastname customers,
2 s.firstname ||' '|| s.lastname referred
3 from customers c, customers s
4 where c.customer# = s.referred;

2）使用 ... JOIN ... ON ...
例子：
select c.firstname ||' '|| c.lastname customers,
2 s.firstname ||' '|| s.lastname referred
3 from customers c JOIN customers s
4 ON c.customer# = s.referred;


5. 外部联接：查找存在于一个表中，但在对应表没有对应行的记录时使用。
使用 LEFT/RIGHT/FULL [OUTER] JOIN 或者 外部联接运算符 '(+)' 。

1）传统方法：使用 带有 外部联接运算符 '(+)' 的 WHERE 子句.
(+)的意义是在缺少对应行的表中创建一个null行，以便与其它表中的行联接。
通常放在缺少对应行的表的列名称后面。
注意点：（+）只能用在联接的一个表上。
一个查询中一个表最多外部连接一个表
包括（+）的条件不能使用 OR 或 IN，
因为这表明如果一行与其它表中的一行匹配或满足其它条件，就将显示在结果内。

例子：
/*返回所有客户的列表，对有订单的客户列出他的订单号。
orders表是缺少对应行的表*/
SQL> select lastname, firstname, ORDER#
2 from customers c, orders o
3 where c.customer# = o.customer#(+)
4 order by c.customer#
5 ;

LASTNAME FIRSTNAME ORDER#
---------- ---------- ------
MORALES BONITA 1003
MORALES BONITA 1018
THOMPSON RYAN
SMITH LEILA 1006
SMITH LEILA 1016
PIERSON THOMAS 1008
GIRARD CINDY 1000
GIRARD CINDY 1009
CRUZ MESHIA
GIANA TAMMY 1007
GIANA TAMMY 1014
JONES KENNETH 1020
PEREZ JORGE
LUCAS JAKE 1001
LUCAS JAKE 1011
MCGOVERN REESE 1002
MCKENZIE WILLIAM
NGUYEN NICHOLAS
LEE JASMINE 1013
SCHELL STEVE 1017
DAUM MICHELL
NELSON BECCA 1012
MONTIASA GREG 1005
MONTIASA GREG 1019
SMITH JENNIFER 1010
FALAH KENNETH 1004
FALAH KENNETH 1015

27 rows selected


/*显示BOOKS中所有图书的列表。如果某位客户订了一本书，那么列出订单号和客户所在州*/
SQL> SELECT DISTINCT b.title, o.ORDER#, c.state
2 FROM books b, Orderitems oi, orders o, customers c
3 WHERE b.isbn = oi.isbn(+)
4 AND oi.ORDER# = o.Order#(+)
5 AND o.customer# = c.customer#(+);

TITLE ORDER# STATE
------------------------------ ------ -----
BIG BEAR AND LITTLE DOVE 1007 TX
BIG BEAR AND LITTLE DOVE 1012 MI
BIG BEAR AND LITTLE DOVE 1017 FL
BODYBUILD IN 10 MINUTES A DAY 1003 FL
BUILDING A CAR WITH TOOTHPICKS
COOKING WITH MUSHROOMS 1000 WA
COOKING WITH MUSHROOMS 1003 FL
COOKING WITH MUSHROOMS 1008 ID
COOKING WITH MUSHROOMS 1009 WA
COOKING WITH MUSHROOMS 1015 NJ
COOKING WITH MUSHROOMS 1018 FL
COOKING WITH MUSHROOMS 1020 WY
DATABASE IMPLEMENTATION 1002 IL
DATABASE IMPLEMENTATION 1003 FL
DATABASE IMPLEMENTATION 1007 TX
DATABASE IMPLEMENTATION 1010 NJ
DATABASE IMPLEMENTATION 1013 WY
DATABASE IMPLEMENTATION 1018 FL
E-BUSINESS THE EASY WAY 1006 FL
E-BUSINESS THE EASY WAY 1007 TX
HANDCRANKED COMPUTERS 1012 MI
HOLY GRAIL OF ORACLE 1007 TX
HOW TO GET FASTER PIZZA
HOW TO MANAGE THE MANAGER 1001 GA
PAINLESS CHILD-REARING 1001 GA
PAINLESS CHILD-REARING 1004 NJ
PAINLESS CHILD-REARING 1011 GA
PAINLESS CHILD-REARING 1012 MI
PAINLESS CHILD-REARING 1016 FL
REVENGE OF MICKEY 1009 WA
REVENGE OF MICKEY 1012 MI
REVENGE OF MICKEY 1014 TX
REVENGE OF MICKEY 1019 GA
SHORTEST POEMS 1005 GA
THE WOK WAY TO COOK

35 rows selected

SQL> SELECT DISTINCT b.title, o.ORDER#, c.state
2 FROM books b, Orderitems oi, orders o, customers c
3 WHERE b.isbn = oi.isbn(+)
4 AND oi.ORDER#(+) = o.Order#
5 AND o.customer#(+) = c.customer#;

SELECT DISTINCT b.title, o.ORDER#, c.state
FROM books b, Orderitems oi, orders o, customers c
WHERE b.isbn = oi.isbn(+)
AND oi.ORDER#(+) = o.Order#
AND o.customer#(+) = c.customer#

ORA-01417: 表可以外部连接到至多一个其它的表

SQL>
SQL> SELECT DISTINCT b.title, o.ORDER#, c.state
2 FROM books b, Orderitems oi, orders o, customers c
3 WHERE b.isbn = oi.isbn(+)
4 AND oi.ORDER# = o.Order#(+)
5 AND o.customer#(+) = c.customer#;

SELECT DISTINCT b.title, o.ORDER#, c.state
FROM books b, Orderitems oi, orders o, customers c
WHERE b.isbn = oi.isbn(+)
AND oi.ORDER# = o.Order#(+)
AND o.customer#(+) = c.customer#

ORA-01417: 表可以外部连接到至多一个其它的表


2）使用 ... LEFT/RIGHT/FULL [OUTER] JOIN ... ON ...
LEFT 联接：右边表中创建null行，即左表有右表没有
RIGHT 联接：左边表中创建null行，即右表有左表没有
FULL 联接：返回这两个表中的在另一个表中不包括匹配记录的记录。

例子：
/*为了让查询结果有差异，对customers表复制后改变数据作为customers2表作为查询基础*/
SQL> select lastname, firstname, ORDER#
2 from customers2 c LEFT JOIN orders o
3 ON c.customer# = o.customer#
4 order by c.customer#;

LASTNAME FIRSTNAME ORDER#
---------- ---------- ------
THOMPSON RYAN
SMITH LEILA 1006
SMITH LEILA 1016
GIRARD CINDY 1000
GIRARD CINDY 1009
CRUZ MESHIA
GIANA TAMMY 1007
GIANA TAMMY 1014
JONES KENNETH 1020
PEREZ JORGE
LUCAS JAKE 1001
LUCAS JAKE 1011
MCGOVERN REESE 1002
MCKENZIE WILLIAM
NGUYEN NICHOLAS
LEE JASMINE 1013
SCHELL STEVE 1017
DAUM MICHELL
MONTIASA GREG 1005
MONTIASA GREG 1019
SMITH JENNIFER 1010
FALAH KENNETH 1004
FALAH KENNETH 1015

23 rows selected


SQL> select lastname, firstname, ORDER#
2 from customers2 c RIGHT JOIN orders o
3 ON c.customer# = o.customer#
4 order by c.customer#;

LASTNAME FIRSTNAME ORDER#
---------- ---------- ------
SMITH LEILA 1016
SMITH LEILA 1006
GIRARD CINDY 1009
GIRARD CINDY 1000
GIANA TAMMY 1014
GIANA TAMMY 1007
JONES KENNETH 1020
LUCAS JAKE 1011
LUCAS JAKE 1001
MCGOVERN REESE 1002
LEE JASMINE 1013
SCHELL STEVE 1017
MONTIASA GREG 1019
MONTIASA GREG 1005
SMITH JENNIFER 1010
FALAH KENNETH 1015
FALAH KENNETH 1004
1018
1003
1008
1012

21 rows selected


SQL> select lastname, firstname, ORDER#
2 from customers2 c FULL JOIN orders o
3 ON c.customer# = o.customer#
4 order by c.customer#;

LASTNAME FIRSTNAME ORDER#
---------- ---------- ----------
THOMPSON RYAN
SMITH LEILA 1006
SMITH LEILA 1016
GIRARD CINDY 1000
GIRARD CINDY 1009
CRUZ MESHIA
GIANA TAMMY 1007
GIANA TAMMY 1014
JONES KENNETH 1020
PEREZ JORGE
LUCAS JAKE 1001
LUCAS JAKE 1011
MCGOVERN REESE 1002
MCKENZIE WILLIAM
NGUYEN NICHOLAS
LEE JASMINE 1013
SCHELL STEVE 1017
DAUM MICHELL
MONTIASA GREG 1005
MONTIASA GREG 1019
SMITH JENNIFER 1010
FALAH KENNETH 1004
FALAH KENNETH 1015
1003
1008
1012
1018

27 rows selected


SQL> SELECT b.title, o.ORDER#, c.state
2 FROM books b FULL JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 LEFT JOIN orders o
5 ON oi.ORDER# = o.Order#
6 RIGHT JOIN customers c
7 ON o.customer# = c.customer#;

TITLE ORDER# STATE
------------------------------ ------ -----
COOKING WITH MUSHROOMS 1000 WA
PAINLESS CHILD-REARING 1001 GA
HOW TO MANAGE THE MANAGER 1001 GA
DATABASE IMPLEMENTATION 1002 IL
COOKING WITH MUSHROOMS 1003 FL
BODYBUILD IN 10 MINUTES A DAY 1003 FL
DATABASE IMPLEMENTATION 1003 FL
PAINLESS CHILD-REARING 1004 NJ
SHORTEST POEMS 1005 GA
E-BUSINESS THE EASY WAY 1006 FL
DATABASE IMPLEMENTATION 1007 TX
BIG BEAR AND LITTLE DOVE 1007 TX
E-BUSINESS THE EASY WAY 1007 TX
HOLY GRAIL OF ORACLE 1007 TX
COOKING WITH MUSHROOMS 1008 ID
REVENGE OF MICKEY 1009 WA
COOKING WITH MUSHROOMS 1009 WA
DATABASE IMPLEMENTATION 1010 NJ
PAINLESS CHILD-REARING 1011 GA
REVENGE OF MICKEY 1012 MI
PAINLESS CHILD-REARING 1012 MI
HANDCRANKED COMPUTERS 1012 MI
BIG BEAR AND LITTLE DOVE 1012 MI
DATABASE IMPLEMENTATION 1013 WY
REVENGE OF MICKEY 1014 TX
COOKING WITH MUSHROOMS 1015 NJ
PAINLESS CHILD-REARING 1016 FL
BIG BEAR AND LITTLE DOVE 1017 FL
DATABASE IMPLEMENTATION 1018 FL
COOKING WITH MUSHROOMS 1018 FL
REVENGE OF MICKEY 1019 GA
COOKING WITH MUSHROOMS 1020 WY
FL
CA
CA
NY
CA
MA

38 rows selected


SQL> SELECT b.title, o.ORDER#, c.state
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 LEFT JOIN orders o
5 ON oi.ORDER# = o.Order#
6 RIGHT JOIN customers c
7 ON o.customer# = c.customer#;

TITLE ORDER# STATE
------------------------------ ------ -----
COOKING WITH MUSHROOMS 1000 WA
PAINLESS CHILD-REARING 1001 GA
HOW TO MANAGE THE MANAGER 1001 GA
DATABASE IMPLEMENTATION 1002 IL
COOKING WITH MUSHROOMS 1003 FL
BODYBUILD IN 10 MINUTES A DAY 1003 FL
DATABASE IMPLEMENTATION 1003 FL
PAINLESS CHILD-REARING 1004 NJ
SHORTEST POEMS 1005 GA
E-BUSINESS THE EASY WAY 1006 FL
DATABASE IMPLEMENTATION 1007 TX
BIG BEAR AND LITTLE DOVE 1007 TX
E-BUSINESS THE EASY WAY 1007 TX
HOLY GRAIL OF ORACLE 1007 TX
COOKING WITH MUSHROOMS 1008 ID
REVENGE OF MICKEY 1009 WA
COOKING WITH MUSHROOMS 1009 WA
DATABASE IMPLEMENTATION 1010 NJ
PAINLESS CHILD-REARING 1011 GA
REVENGE OF MICKEY 1012 MI
PAINLESS CHILD-REARING 1012 MI
HANDCRANKED COMPUTERS 1012 MI
BIG BEAR AND LITTLE DOVE 1012 MI
DATABASE IMPLEMENTATION 1013 WY
REVENGE OF MICKEY 1014 TX
COOKING WITH MUSHROOMS 1015 NJ
PAINLESS CHILD-REARING 1016 FL
BIG BEAR AND LITTLE DOVE 1017 FL
DATABASE IMPLEMENTATION 1018 FL
COOKING WITH MUSHROOMS 1018 FL
REVENGE OF MICKEY 1019 GA
COOKING WITH MUSHROOMS 1020 WY
FL
CA
CA
NY
CA
MA

38 rows selected

/*连用多个 LEFT/RIGHT/FULL/ JOIN 的不同结果演示*/
SQL> SELECT b.title ,oi.isbn
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn ;

TITLE ISBN
------------------------------ ----------
COOKING WITH MUSHROOMS 3437212490
HOW TO MANAGE THE MANAGER 9247381001
PAINLESS CHILD-REARING 2491748320
DATABASE IMPLEMENTATION 8843172113
DATABASE IMPLEMENTATION 8843172113
BODYBUILD IN 10 MINUTES A DAY 1059831198
COOKING WITH MUSHROOMS 3437212490
PAINLESS CHILD-REARING 2491748320
SHORTEST POEMS 2147428890
E-BUSINESS THE EASY WAY 9959789321
HOLY GRAIL OF ORACLE 3957136468
E-BUSINESS THE EASY WAY 9959789321
BIG BEAR AND LITTLE DOVE 8117949391
DATABASE IMPLEMENTATION 8843172113
COOKING WITH MUSHROOMS 3437212490
COOKING WITH MUSHROOMS 3437212490
REVENGE OF MICKEY 0401140733
DATABASE IMPLEMENTATION 8843172113
PAINLESS CHILD-REARING 2491748320
BIG BEAR AND LITTLE DOVE 8117949391
HANDCRANKED COMPUTERS 1915762492
PAINLESS CHILD-REARING 2491748320
REVENGE OF MICKEY 0401140733
DATABASE IMPLEMENTATION 8843172113
REVENGE OF MICKEY 0401140733
COOKING WITH MUSHROOMS 3437212490
PAINLESS CHILD-REARING 2491748320
BIG BEAR AND LITTLE DOVE 8117949391
COOKING WITH MUSHROOMS 3437212490
DATABASE IMPLEMENTATION 8843172113
REVENGE OF MICKEY 0401140733
COOKING WITH MUSHROOMS 3437212490
BUILDING A CAR WITH TOOTHPICKS
HOW TO GET FASTER PIZZA
THE WOK WAY TO COOK

35 rows selected

SQL>
SQL> SELECT b.title ,oi.isbn,o.Order#
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 LEFT JOIN orders o
5 ON oi.ORDER# = o.Order#;

TITLE ISBN ORDER#
------------------------------ ---------- ------
COOKING WITH MUSHROOMS 3437212490 1000
HOW TO MANAGE THE MANAGER 9247381001 1001
PAINLESS CHILD-REARING 2491748320 1001
DATABASE IMPLEMENTATION 8843172113 1002
DATABASE IMPLEMENTATION 8843172113 1003
BODYBUILD IN 10 MINUTES A DAY 1059831198 1003
COOKING WITH MUSHROOMS 3437212490 1003
PAINLESS CHILD-REARING 2491748320 1004
SHORTEST POEMS 2147428890 1005
E-BUSINESS THE EASY WAY 9959789321 1006
HOLY GRAIL OF ORACLE 3957136468 1007
E-BUSINESS THE EASY WAY 9959789321 1007
BIG BEAR AND LITTLE DOVE 8117949391 1007
DATABASE IMPLEMENTATION 8843172113 1007
COOKING WITH MUSHROOMS 3437212490 1008
COOKING WITH MUSHROOMS 3437212490 1009
REVENGE OF MICKEY 0401140733 1009
DATABASE IMPLEMENTATION 8843172113 1010
PAINLESS CHILD-REARING 2491748320 1011
BIG BEAR AND LITTLE DOVE 8117949391 1012
HANDCRANKED COMPUTERS 1915762492 1012
PAINLESS CHILD-REARING 2491748320 1012
REVENGE OF MICKEY 0401140733 1012
DATABASE IMPLEMENTATION 8843172113 1013
REVENGE OF MICKEY 0401140733 1014
COOKING WITH MUSHROOMS 3437212490 1015
PAINLESS CHILD-REARING 2491748320 1016
BIG BEAR AND LITTLE DOVE 8117949391 1017
COOKING WITH MUSHROOMS 3437212490 1018
DATABASE IMPLEMENTATION 8843172113 1018
REVENGE OF MICKEY 0401140733 1019
COOKING WITH MUSHROOMS 3437212490 1020
BUILDING A CAR WITH TOOTHPICKS
HOW TO GET FASTER PIZZA
THE WOK WAY TO COOK

35 rows selected

SQL>
SQL> SELECT b.title ,oi.isbn,o.Order#
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 RIGHT JOIN orders o
5 ON oi.ORDER# = o.Order#;

TITLE ISBN ORDER#
------------------------------ ---------- ------
COOKING WITH MUSHROOMS 3437212490 1000
HOW TO MANAGE THE MANAGER 9247381001 1001
PAINLESS CHILD-REARING 2491748320 1001
DATABASE IMPLEMENTATION 8843172113 1002
DATABASE IMPLEMENTATION 8843172113 1003
BODYBUILD IN 10 MINUTES A DAY 1059831198 1003
COOKING WITH MUSHROOMS 3437212490 1003
PAINLESS CHILD-REARING 2491748320 1004
SHORTEST POEMS 2147428890 1005
E-BUSINESS THE EASY WAY 9959789321 1006
HOLY GRAIL OF ORACLE 3957136468 1007
E-BUSINESS THE EASY WAY 9959789321 1007
BIG BEAR AND LITTLE DOVE 8117949391 1007
DATABASE IMPLEMENTATION 8843172113 1007
COOKING WITH MUSHROOMS 3437212490 1008
COOKING WITH MUSHROOMS 3437212490 1009
REVENGE OF MICKEY 0401140733 1009
DATABASE IMPLEMENTATION 8843172113 1010
PAINLESS CHILD-REARING 2491748320 1011
BIG BEAR AND LITTLE DOVE 8117949391 1012
HANDCRANKED COMPUTERS 1915762492 1012
PAINLESS CHILD-REARING 2491748320 1012
REVENGE OF MICKEY 0401140733 1012
DATABASE IMPLEMENTATION 8843172113 1013
REVENGE OF MICKEY 0401140733 1014
COOKING WITH MUSHROOMS 3437212490 1015
PAINLESS CHILD-REARING 2491748320 1016
BIG BEAR AND LITTLE DOVE 8117949391 1017
COOKING WITH MUSHROOMS 3437212490 1018
DATABASE IMPLEMENTATION 8843172113 1018
REVENGE OF MICKEY 0401140733 1019
COOKING WITH MUSHROOMS 3437212490 1020

32 rows selected

SQL>
SQL> SELECT b.title ,oi.isbn,o.Order#, c.customer#
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 RIGHT JOIN orders o
5 ON oi.ORDER# = o.Order#
6 RIGHT JOIN customers c
7 ON o.customer# = c.customer#;

TITLE ISBN ORDER# CUSTOMER#
------------------------------ ---------- ------ ---------
COOKING WITH MUSHROOMS 3437212490 1000 1005
HOW TO MANAGE THE MANAGER 9247381001 1001 1010
PAINLESS CHILD-REARING 2491748320 1001 1010
DATABASE IMPLEMENTATION 8843172113 1002 1011
DATABASE IMPLEMENTATION 8843172113 1003 1001
BODYBUILD IN 10 MINUTES A DAY 1059831198 1003 1001
COOKING WITH MUSHROOMS 3437212490 1003 1001
PAINLESS CHILD-REARING 2491748320 1004 1020
SHORTEST POEMS 2147428890 1005 1018
E-BUSINESS THE EASY WAY 9959789321 1006 1003
HOLY GRAIL OF ORACLE 3957136468 1007 1007
E-BUSINESS THE EASY WAY 9959789321 1007 1007
BIG BEAR AND LITTLE DOVE 8117949391 1007 1007
DATABASE IMPLEMENTATION 8843172113 1007 1007
COOKING WITH MUSHROOMS 3437212490 1008 1004
COOKING WITH MUSHROOMS 3437212490 1009 1005
REVENGE OF MICKEY 0401140733 1009 1005
DATABASE IMPLEMENTATION 8843172113 1010 1019
PAINLESS CHILD-REARING 2491748320 1011 1010
BIG BEAR AND LITTLE DOVE 8117949391 1012 1017
HANDCRANKED COMPUTERS 1915762492 1012 1017
PAINLESS CHILD-REARING 2491748320 1012 1017
REVENGE OF MICKEY 0401140733 1012 1017
DATABASE IMPLEMENTATION 8843172113 1013 1014
REVENGE OF MICKEY 0401140733 1014 1007
COOKING WITH MUSHROOMS 3437212490 1015 1020
PAINLESS CHILD-REARING 2491748320 1016 1003
BIG BEAR AND LITTLE DOVE 8117949391 1017 1015
COOKING WITH MUSHROOMS 3437212490 1018 1001
DATABASE IMPLEMENTATION 8843172113 1018 1001
REVENGE OF MICKEY 0401140733 1019 1018
COOKING WITH MUSHROOMS 3437212490 1020 1008
1013
1009
1016
1006
1002
1012

38 rows selected

SQL>
SQL> SELECT b.title ,oi.isbn,o.Order#, c.customer#
2 FROM books b left JOIN Orderitems oi
3 ON b.isbn = oi.isbn
4 RIGHT JOIN orders o
5 ON oi.ORDER# = o.Order#
6 LEFT JOIN customers c
7 ON o.customer# = c.customer#;

TITLE ISBN ORDER# CUSTOMER#
------------------------------ ---------- ------ ---------
COOKING WITH MUSHROOMS 3437212490 1000 1005
HOW TO MANAGE THE MANAGER 9247381001 1001 1010
PAINLESS CHILD-REARING 2491748320 1001 1010
DATABASE IMPLEMENTATION 8843172113 1002 1011
DATABASE IMPLEMENTATION 8843172113 1003 1001
BODYBUILD IN 10 MINUTES A DAY 1059831198 1003 1001
COOKING WITH MUSHROOMS 3437212490 1003 1001
PAINLESS CHILD-REARING 2491748320 1004 1020
SHORTEST POEMS 2147428890 1005 1018
E-BUSINESS THE EASY WAY 9959789321 1006 1003
HOLY GRAIL OF ORACLE 3957136468 1007 1007
E-BUSINESS THE EASY WAY 9959789321 1007 1007
BIG BEAR AND LITTLE DOVE 8117949391 1007 1007
DATABASE IMPLEMENTATION 8843172113 1007 1007
COOKING WITH MUSHROOMS 3437212490 1008 1004
COOKING WITH MUSHROOMS 3437212490 1009 1005
REVENGE OF MICKEY 0401140733 1009 1005
DATABASE IMPLEMENTATION 8843172113 1010 1019
PAINLESS CHILD-REARING 2491748320 1011 1010
BIG BEAR AND LITTLE DOVE 8117949391 1012 1017
HANDCRANKED COMPUTERS 1915762492 1012 1017
PAINLESS CHILD-REARING 2491748320 1012 1017
REVENGE OF MICKEY 0401140733 1012 1017
DATABASE IMPLEMENTATION 8843172113 1013 1014
REVENGE OF MICKEY 0401140733 1014 1007
COOKING WITH MUSHROOMS 3437212490 1015 1020
PAINLESS CHILD-REARING 2491748320 1016 1003
BIG BEAR AND LITTLE DOVE 8117949391 1017 1015
COOKING WITH MUSHROOMS 3437212490 1018 1001
DATABASE IMPLEMENTATION 8843172113 1018 1001
REVENGE OF MICKEY 0401140733 1019 1018
COOKING WITH MUSHROOMS 3437212490 1020 1008

32 rows selected

二. 集合运算符：用来结合多个 SELECT 语句的结果。
1. UNION 返回结合的 SELECT 语句的结果，删除重复的记录。
UNION ALL 返回结合的 SELECT 语句的结果，不删除重复的记录。

例子：
/*查询块具有不正确的结果列数*/
SQL> select pubid from books
2 union
3 select cost, pubdate from books;

select pubid from books
union
select cost, pubdate from books

ORA-01789: 查询块具有不正确的结果列数

/*表达式必须具有与对应表达式相同的数据类型*/
SQL> select pubid,ISBN from books
2 union
3 select cost, pubdate from books;

select pubid,ISBN from books
union
select cost, pubdate from books

ORA-01790: 表达式必须具有与对应表达式相同的数据类型

SQL> (select category from books)
2 union
3 (select zip from customers);

CATEGORY
------------
02110
07962
08607
12211
30314
31206
32306
32328
33111
34711
49006
60606
78710
82003
82414
83707
90404
91508
91510
98115

CATEGORY
------------
BUSINESS
CHILDREN
COMPUTER
COOKING
FAMILY LIFE
FITNESS
LITERATURE
SELF HELP

28 rows selected

SQL> select customer# from customers
2 union
3 select customer# from orders;

CUSTOMER#
----------
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020

20 rows selected


SQL> (select customer# from customers
2 union all
3 select customer# from orders)
4 ORDER BY customer#;

CUSTOMER#
----------
1001
1001
1001
1002
1003
1003
1003
1004
1004
1005
1005
1005
1006
1007
1007
1007
1008
1008
1009
1010
1010
1010
1011
1011
1012
1013
1014
1014
1015
1015
1016
1017
1017
1018
1018
1018
1019
1019
1020
1020
1020

41 rows selected

2. INTERSECT :只返回两个 SELECT 语句的结果中都包括的行。
例子：
SQL> select customer# from customers
2 intersect
3 select customer# from orders;

CUSTOMER#
----------
1001
1003
1004
1005
1007
1008
1010
1011
1014
1015
1017
1018
1019
1020

14 rows selected


3. MINUS ：只显示有第一个查询返回的行，并删除第一个查询中的第二个查询也存在的结果。
例子：
SQL> select customer# from customers
2 minus
3 select customer# from orders;

CUSTOMER#
----------
1002
1006
1009
1012
1013
1016

6 rows selected

三. 联接多个表.
规则：需要的联接数量比将要联接的表数量少1。

1. 传统方法：用 WHERE 子句来指定
例子：
SQL> SELECT c.customer#, b.title
2 FROM customers c, orders o, orderitems oi, books b
3 WHERE c.customer# = o.customer#
4 AND o.ORDER# = oi.order#
5 AND oi.isbn = b.isbn
6ORDER BY title;

CUSTOMER# TITLE
--------- ------------------------------
1007 big bear and little dove
1017 big bear and little dove
1015 big bear and little dove
1001 bodybuild in 10 minutes a day
1005 cooking with mushrooms
1001 cooking with mushrooms
1001 cooking with mushrooms
1008 cooking with mushrooms
1020 cooking with mushrooms
1005 cooking with mushrooms
1004 cooking with mushrooms
1011 database implementation
1001 database implementation
1007 database implementation
1001 database implementation
1014 database implementation
1019 database implementation
1003 e-business the easy way
1007 e-business the easy way
1017 handcranked computers
1007 holy grail of oracle
1010 how to manage the manager
1010 painless child-rearing
1003 painless child-rearing
1020 painless child-rearing
1010 painless child-rearing
1017 painless child-rearing
1005 revenge of mickey
1017 revenge of mickey
1007 revenge of mickey
1018 revenge of mickey
1018 shortest poems

32 rows selected

2. 使用 JOIN ... ON ...
关键是：除了表1，2的联接，其他的每个联接和前一个联接的结果作联接。
注意：适当的情况下，其他的 JOIN 也可以应用在多表联接中。

例子：/*查询每个客户订的书*/

/*使用 join on */
SQL> SELECT title, firstname, lastname
2 FROM customers c JOIN orders o
3 ON c.customer# = o.customer#
4 JOIN Orderitems oi
5 ON o.order# = oi.ORDER
6 JOIN books b
7 ON oi.isbn = b.isbn
8 ORDER BY title;

TITLE FIRSTNAME LASTNAME
------------------------------ ---------- ----------
BIG BEAR AND LITTLE DOVE TAMMY GIANA
BIG BEAR AND LITTLE DOVE BECCA NELSON
BIG BEAR AND LITTLE DOVE STEVE SCHELL
BODYBUILD IN 10 MINUTES A DAY BONITA MORALES
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS KENNETH JONES
COOKING WITH MUSHROOMS KENNETH FALAH
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS THOMAS PIERSON
DATABASE IMPLEMENTATION REESE MCGOVERN
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION TAMMY GIANA
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION JASMINE LEE
DATABASE IMPLEMENTATION JENNIFER SMITH
E-BUSINESS THE EASY WAY LEILA SMITH
E-BUSINESS THE EASY WAY TAMMY GIANA
HANDCRANKED COMPUTERS BECCA NELSON
HOLY GRAIL OF ORACLE TAMMY GIANA
HOW TO MANAGE THE MANAGER JAKE LUCAS
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING LEILA SMITH
PAINLESS CHILD-REARING KENNETH FALAH
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING BECCA NELSON
REVENGE OF MICKEY CINDY GIRARD
REVENGE OF MICKEY BECCA NELSON
REVENGE OF MICKEY TAMMY GIANA
REVENGE OF MICKEY GREG MONTIASA
SHORTEST POEMS GREG MONTIASA

32 rows selected

/*使用 join...using (...)*/
SQL> SELECT title, firstname, lastname
2 FROM customers c JOIN orders o
3 USING (customer#)
4 JOIN Orderitems oi
5 USING (ORDER#)
6 JOIN books b
7 USING(isbn)
8 ORDER BY title;

TITLE FIRSTNAME LASTNAME
------------------------------ ---------- ----------
BIG BEAR AND LITTLE DOVE TAMMY GIANA
BIG BEAR AND LITTLE DOVE BECCA NELSON
BIG BEAR AND LITTLE DOVE STEVE SCHELL
BODYBUILD IN 10 MINUTES A DAY BONITA MORALES
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS KENNETH JONES
COOKING WITH MUSHROOMS KENNETH FALAH
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS THOMAS PIERSON
DATABASE IMPLEMENTATION REESE MCGOVERN
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION TAMMY GIANA
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION JASMINE LEE
DATABASE IMPLEMENTATION JENNIFER SMITH
E-BUSINESS THE EASY WAY LEILA SMITH
E-BUSINESS THE EASY WAY TAMMY GIANA
HANDCRANKED COMPUTERS BECCA NELSON
HOLY GRAIL OF ORACLE TAMMY GIANA
HOW TO MANAGE THE MANAGER JAKE LUCAS
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING LEILA SMITH
PAINLESS CHILD-REARING KENNETH FALAH
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING BECCA NELSON
REVENGE OF MICKEY CINDY GIRARD
REVENGE OF MICKEY BECCA NELSON
REVENGE OF MICKEY TAMMY GIANA
REVENGE OF MICKEY GREG MONTIASA
SHORTEST POEMS GREG MONTIASA

32 rows selected

/*使用 natural join ,因为这个查询每个联接都有相同列名的列*/
SQL> SELECT title, firstname, lastname
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN Orderitems
4 NATURAL JOIN books b
5 ORDER BY title;

TITLE FIRSTNAME LASTNAME
------------------------------ ---------- ----------
BIG BEAR AND LITTLE DOVE TAMMY GIANA
BIG BEAR AND LITTLE DOVE BECCA NELSON
BIG BEAR AND LITTLE DOVE STEVE SCHELL
BODYBUILD IN 10 MINUTES A DAY BONITA MORALES
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS BONITA MORALES
COOKING WITH MUSHROOMS KENNETH JONES
COOKING WITH MUSHROOMS KENNETH FALAH
COOKING WITH MUSHROOMS CINDY GIRARD
COOKING WITH MUSHROOMS THOMAS PIERSON
DATABASE IMPLEMENTATION REESE MCGOVERN
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION TAMMY GIANA
DATABASE IMPLEMENTATION BONITA MORALES
DATABASE IMPLEMENTATION JASMINE LEE
DATABASE IMPLEMENTATION JENNIFER SMITH
E-BUSINESS THE EASY WAY LEILA SMITH
E-BUSINESS THE EASY WAY TAMMY GIANA
HANDCRANKED COMPUTERS BECCA NELSON
HOLY GRAIL OF ORACLE TAMMY GIANA
HOW TO MANAGE THE MANAGER JAKE LUCAS
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING LEILA SMITH
PAINLESS CHILD-REARING KENNETH FALAH
PAINLESS CHILD-REARING JAKE LUCAS
PAINLESS CHILD-REARING BECCA NELSON
REVENGE OF MICKEY CINDY GIRARD
REVENGE OF MICKEY BECCA NELSON
REVENGE OF MICKEY TAMMY GIANA
REVENGE OF MICKEY GREG MONTIASA
SHORTEST POEMS GREG MONTIASA

32 rows selected


注意点：
1）如果在 FROM 子句中指明了一个表别名，
那么只要在这个 SQL 语句中引用这个表，就必须使用这个别名。
否则会报错。
例子：
select title, name
from books b, publisher p
where books.pubid = p.pubid
ORA-00904: "BOOKS"."PUBID": 无效的标识符

2） JOIN 的形式：
... JOIN ... ON ...需要指定联接条件
... JOIN ... USING ...只需要指出公共列
相等联接 ... NATURAL ... JOIN ...只需要指出公共列
不等联接 ... JOIN ... ON ...
自身联接 ... 必须创建列别名
外部联接 ... 1.使用外部联接符（+）
... 2.使用 LEFT / RIGHT / FULL [OUTER] JOIN ...
原理：为缺少对应行的表创建null行

3） 集合运算符：
UNION 返回结合的 SELECT 语句的结果，删除重复的记录。
UNION ALL 返回结合的 SELECT 语句的结果，不删除重复的记录。
INTERSECT :只返回两个 SELECT 语句的结果中都包括的行。
MINUS ：只显示有第一个查询返回的行，并删除第一个查询中的第二个查询也存在的结果。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/198459/viewspace-908758/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/198459/viewspace-908758/

cuiang9442
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Select语句 多表查询 聚合函数
Lzzzzzzk的博客
03-06 1255
Select语句 多表查询 聚合函数
SELECT 查询的基础语法
Megetood的博客
09-23 512
查询列 SELECT name FROM heros; (单列) SELECT name, hp_max, mp_max FROM heros; （多列） SELECT * FROM heros; (所有列) 起别名 SELECT name AS n FROM heros; (AS 可以省略) 查询常数 字符串都用单引号 SELECT '王者荣耀' as platform, name FROM heros 去除重复行 SELECT DISTINCT attack_range FROM heros;
select 多表查询 组合查询语句
等夏天
09-13 1万+
很多时候需要查询两张表及以上的表来获取需要的信息   一张表的时候查询语句：              select  列1,列2 from  表 where 查询条件       例如：一张表                                  信息表 username age sex 张三 24 男 李四 23 女
sql的select查询语句大全（单表查询、多表连接查询）
chensay的博客
11-10 1万+
FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段1=表2.字段2;FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段1=表2.字段2;FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段1=表2.字段2;FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段1=表2.字段2;, 表2名.表列,表2名.表列,…, 表2名.表列,表2名.表列,…, 表2名.表列,表2名.表列,…on（where） 表1名.表列=表2名.表列;
T-SQL查询语句(三)：多表查询
zxs820329的专栏
02-14 2631
***************************************SQL查询语句《三》：多表查询 (也叫连接查询，此处为基于两个表的连接查询)如果一个查询需要对多个表进行操作就称为连接查询,连接查询的结果集或结果称为表之间的连接.连接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征.select 表1.字段名1，表2.字段名2，... from
mysql多表查询
qq_60275529的博客
01-31 2826
三、通过外连接left join,left outer join,right join,right outer jion,union进行查询。right outer join(相当于right join+[where 表1.字段 is null])left outer join(相当于left join+[where表2.字段 is null])准备两张表：一张users用户表，一张user_addr用户地址表，它们有共同的user_id。二、通过内连接inner join进行查询。
SQL语法中的SELECT
Alittlevegetable的博客
11-09 366
回顾总结SQL知识点——SELECT语法 （个人总结，如有遗漏和错误，希望发现的大神给与指正！ 下面列举几项比较常见的查询以及实现结果。） 注：建的数据表内容和查询语句1的结果是完全相同的 ————————————————————————————— SELECT 语句一般用于从数据库或者数据表中挑选数据。 ————————————————————————————— 1查询到表中所有数据（语法为：S...
dual虚表 select语法规则
05-08
"DUAL虚表SELECT语法规则" DUAL虚表是Oracle系统中一个特殊的表，它总是只有一条记录。这个表的存在使得我们可以使用SELECT语句来执行一些有趣的事情，如获取当前用户、系统时间、主机名、 locale、随机数、序列号...
SQL 多表连接查询实现语句
09-11
在SQL中，多表连接查询是数据库操作的重要组成部分，它允许我们从多个相关的表中获取数据，以满足复杂的查询需求。本文将详细讲解SQL中三种主要的连接查询方式：INNER JOIN、LEFT JOIN和RIGHT JOIN。 1. INNER JOIN...
mysql多表联合查询操作实例分析
09-09
首先，让我们了解多表联合查询的基本语法。在MySQL中，最常用的联合查询类型包括`JOIN`、`UNION`和`UNION ALL`。`JOIN`操作用于合并两个或更多表的数据，根据指定的关联条件进行匹配。例如： ```sql SELECT * FROM ...
SAP HANA SELECT语法（带参数）
04-25
SAP HANA SELECT语法（带参数） SAP HANA SELECT语法（带参数）
MySQL查询语法汇总
09-08
`JOIN`用于合并两个或更多表的数据，基于这些表之间的关联。例如： ```sql SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno; ``` 这将返回员工名字与其所在部门名称的组合。 6. **...
【MySQL基础篇】
xuxuxux123的博客
03-27 3010
Mysql基础
Select之多表查询
honeyJ
06-03 1万+
前面我们讲解的mysql表的查询都是对一张表进行查询，链接为这里写链接内容 今天来学习多表查询。 我们用一个简单的公司管理系统，有三张表EMP ,DEPT,SALGRADE来演示如何进行多表查询。 表内容如下： 创建部门表 drop table if exists dept; create table if not exists dept ( deptno INT(2) ...
多表联合查询
m0_62274923的博客
04-09 3451
隐式内连接（SOL92标准）：select * from A,B where 条件; 显示内连接（SOL99标准）：select * from A [inner] join B on 条件；
select语句多表联合查询（三）上
T-T
12-12 3259
（所有表都在select语句多表联合查询（一）中） （NOT） in子查询 我们从以下几个问题来展开对In的理解与研究： 1. 如何理解In操作？ 2.何时用In操作？ 3.如何理解NOT IN 以及何时用NOT IN？ 来，我们先来看个简单示例： select * from Student where Sname in ('张三','王三'); 通过执行这条select语句我们会...
select语句--多表查询
暗鹰的天空
05-17 1826
聚合函数        sum,avg   求和/求平均数，只支持数字        max,min   求最大/最小数字，最早/最晚日期，最长/最短字符串，支持数字、datetime、字符串                         count     统计所有记录行的数量         可使用distinct，区分相同的值                se
sql语句之多表查询
热门推荐
jiey0407的博客
07-31 2万+
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。左边主表，左所有数据都显示，右边显示相关联，没有数据显示null。右边主表，右所有数据都显示，左边显示相关联，没有数据显示null。...
基于Springboot和Vue的图书借还管理系统源码 图书借还管理系统代码（高分毕设）
最新发布
07-28
图书借还管理系统源码（高分毕设），个人经导师指导并认可通过的98分毕业设计项目，主要针对计算机相关专业的正在做毕设的学生和需要项目实战练习的学习者。也可作为课程设计、期末大作业。包含全部项目源码[代码]、该项目可以直接作为毕设使用。项目技术栈：前端是vue，后端是springboot，项目代码都经过严格调试，代码没有任何bug！ 系统源码（高分毕设），个人经导师指导并认可通过的98分毕业设计项目，主要针对计算机相关专业的正在做毕设的学生和需要项目实战练习的学习者。也可作为课程设计、期末大作业。包含全部项目源码[代码]、该项目可以直接作为毕设使用。项目技术栈：前端是vue，后端是springboot，项目代码都经过严格调试，代码没有任何bug！ 系统源码（高分毕设），个人经导师指导并认可通过的98分毕业设计项目，主要针对计算机相关专业的正在做毕设的学生和需要项目实战练习的学习者。也可作为课程设计、期末大作业。包含全部项目源码[代码]、该项目可以直接作为毕设使用。项目技术栈：前端是vue，后端是springboot，项目代码都经过严格调试，代码没有任何bug！
多表查询sql语句语法
05-15
多表查询是指同时查询多张表中的数据，常用的语法是使用 JOIN 关键字。常用的多表查询语句有以下几种： 1. 内连接查询 ``` SELECT <字段列表> FROM <表1> JOIN <表2> ON <连接条件> ``` 其中，连接条件可以是两个...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值