学习笔记6.15

--给表名起别名select cust_name,cust_contactfrom customers  c,orders o,orderitems  oiwhere c.cust_id=o.cust_idand oi.order_num=o.order_numand prod_id='RGAN01';--oracle无as关键字--自然联结select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_pricefrom customers c,orders o,orderitems oiwhere c.cust_id=o.cust_idand oi.order_num=o.order_numand prod_id='RGAN01';--内联结select customers.cust_id,orders.order_numfrom customers inner join orderson customers.cust_id=orders.cust_id;--外联结select customers.cust_id,orders.order_numfrom customers left outer join orderson customers.cust_id=orders.cust_id;select customers.cust_id,orders .order_numfrom orders full outer join customerson Orders.cust_id=customers.cust_id;/*left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录inner join(等值连接) 只返回两个表中联结字段相等的行*/select customers.cust_id,orders .order_numfrom orders right outer join customerson Orders.cust_id=customers.cust_id;select customers.cust_id,count(orders.order_num) num_ordfrom customers inner join orderson customers.cust_id=orders.cust_idgroup by customers.cust_id;select customers.cust_id,count(orders.order_num) num_ordfrom customers left outer join orderson customers.cust_id=orders.cust_idgroup by customers.cust_id;