学习笔记6.15

--给表名起别名
select cust_name,cust_contact
from customers  c,orders o,orderitems  oi
where c.cust_id=o.cust_id
and oi.order_num=o.order_num
and prod_id='RGAN01';
--oracle无as关键字

--自然联结
select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
from customers c,orders o,orderitems oi
where c.cust_id=o.cust_id
and oi.order_num=o.order_num
and prod_id='RGAN01';

--内联结
select customers.cust_id,orders.order_num
from customers inner join orders
on customers.cust_id=orders.cust_id;


--外联结
select customers.cust_id,orders.order_num
from customers left outer join orders
on customers.cust_id=orders.cust_id;

select customers.cust_id,orders .order_num
from orders full outer join customers
on Orders.cust_id=customers.cust_id;

/*left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行*/

select customers.cust_id,orders .order_num
from orders right outer join customers
on Orders.cust_id=customers.cust_id;

select customers.cust_id,count(orders.order_num) num_ord
from customers inner join orders
on customers.cust_id=orders.cust_id
group by customers.cust_id;

select customers.cust_id,
count(orders.order_num) num_ord
from customers left outer join orders
on customers.cust_id=orders.cust_id
group by customers.cust_id;