數據庫中表的復雜查詢
數據庫中表的復雜查詢
1、連接查詢
1.0連接的基本語法格式:
from TABLE1 join_type TABLE2 [on (join_condition)][where (query_condition)]
TABLE1:左表
TABLE2:右表
join_type:連接的類型。交叉、內連接、左外連接、右外連接
on:設置連接條件
where:對連接查詢的結果進步一的篩選
1.1交叉連接
select * from CUSTOMER cross join ORDERS;
或者
select * from CUSTOMER,ORDERS;
select c.name,o.order_number from CUSTOMER c,ORDERS o;
1.2內連接:
隱式內連接:(不使用on關鍵字,使用where)
select * from CUSTOMER c,ORDERS o where c.id=o.customer_id;
顯式內連接:(使用on關鍵字)
select * from CUSTOMER c inner join ORDERS o on c.id=o.customer_id;
1.3外連接:
左外連接:(返回符合連接條件的所有記錄,同時還返回左表中其余的所有記錄)
select * from CUSTOMER c left outer join ORDERS o on c.id=o.customer_id;
右外連接:(返回符合連接條件的所有記錄,同時還返回右表中其余的所有記錄)
select * from CUSTOMER c right outer join ORDERS o on c.id=o.customer_id;
2、子查詢(嵌套查詢)
子查詢: select * from orders where customer_id=(select id from customer where name='張三');
3、聯合查詢
SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;
取兩條語句的並集,並去除重復的記錄。