学习笔记6.15

最新推荐文章于 2025-11-23 02:29:31 发布

原创最新推荐文章于 2025-11-23 02:29:31 发布 · 339 阅读

0 ·

CC 4.0 BY-SA版权

数据库学习笔记专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了SQL中的不同类型的联结操作，包括内联结、外联结等，并通过具体示例展示了如何使用这些联结来从多个表中检索数据。

--给表名起别名
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;