SQL2009.4.15

//author 满晨晨
//time 2009 4 15上午
内链接 结果集仅包含满足条件的行
等值连接
使用=号 包含重复列
 不等链接 使用除等号以外的所有+-><<=>=
 自然链接 两个表中寻找字段和数据类型相同的自动链接 不需要写出 natural join
链接条件

select distinct p.prod_name,v.vend_namefrom vendors v natural join products p
外链接 结果集即包含哪些满足条件的行，还包含那些其中某个表的全部行
交叉链接 结果集包含两个表的所有行的组合 笛卡尔积

交叉链接 交叉链接不带where 笛卡尔积
select vnd_name,prod_name,prod_price from vendors,products
select count(*) from vendors 6
select count(*) from product 12
select count(*) from product,vendors 72

 

两个表的内链接
select vend_name,prod_name,prod_price from vendors ，products where vender.vend_id=product.vend_id
==select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id

三个表之间的内链接
select distinct c.cust_id,c.cust_name,o.order_num
from customers c,order o,orderitems i
where c.cust_id=o.cust_id and o.order_num=i.order_num
或者
select
distinct c.cust_id,c.cust_name,o.order_num
from
customer c inner join orders o on c.cust_id=o.cust_id inner join orderitems i on o.
查询订单编号是20007的产品名称和产品数量
select oi.quantity,p.prod_name,v.vend_name
from order o inner join orderitems oi on o.order_num=oi.order_num
join products p on oi.prod_id=p.prod_id
join vendors v on p.vend_id=v.vend_id
where o.order_num=20007

查询订购货物编号为RGNAO1的顾客姓名和联系方式
内链接
select c.cust_name,c.cust_contact
from customers c inner join orders o
on c.cust_id=o.cust_id
inner join orderitems oi
on oi.order_num=o.order_num
where oi.prod_id='RGAN01'

子查询
select cust_name,cust_contact
from customers
where cust_id in(select  cust_id
from orders where
order_num in(select order_num from orderitems
where prod_id='RGAN01'

)

)

 

自我链接 联系方式为jim jones 的用户编号，姓名，联系方式
select cust_id,cust_name,cust_contact
from customers
where cust_name=
(select cust_name from customers where cust_contact=
'jim jones')

select c1.customer,c1.cust_name,c1.cust_contact from
customer c1,customer c2
where c2.cust_contact='jim jones' and c1.cust_name=c2.cust_name

自然链接 好用：：：：
哪个顾客订购货物编号为RGAN01的货物 在什么时间订购 订购数量 此货物单价

select c.cust_name,o.order_date,oi.quantity ,oi.item_price
from customers c natural join orders o natural join orderitems oi
where oi.prod_id='RGAN01'

外连接
查询下订单的顾客名称和其订单编号
select c.cust_id,c.cust_name,o.order_num
from customers c inner join order o on c.cust_id=o.cust_id
所有顾客下订单的状况 (顾客编号，订单编号）显示时包含没有下订单的客户
select c.cust_id,c.cust_name,o.order_num
from customers c right outer join order o on c.cust_id=o.cust_id
等于
select c.cust_id,c.cust_name,o.order_num
from order o,customers c
where o.cust_id(+)=c.cust_id
外联 outer join 符合条件的显示出来 此外还要显示出其他条件的值
left join 左边的表为核心表都要显示出来  右边的那个表符合条件的显示出来 其他的NULL  
right join
full join 左右为核心一定要显示出来

外连接必须加上left right 不能单独outer join

统计订货的顾客下的订单的数量
select count(o.order_num) ,c.cust_id from customers c,orders o
where c.cust_id=o.cust_id
group by c.cust_id
order by count(order_num) asc

统计每个顾客订购了多少个订单
select c.cust_id ,count(o.order_num)
from custmoers c left ounter join orders on c.cust_id=o.cust_id
group by c.cust_id
order by count(order_num) asc

select count(o.order_num) ,c.cust_id from customers c,orders o
where c.cust_id=o.cust_id(+)
group by c.cust_id
order by count(order_num) asc

统计订单数量大于2的顾客

select c.cust_id ,count(o.order_num)
from customers c left outer join orders o on c.cust_id=o.cust_id
 
group by c.cust_id
having count(o.order_num)>=2
order by count(order_num) asc

 

统计订单表中所有顾客各自订单的数量，并同时显示该顾客的信息

select c.cust_id,count(order_num)
from customers c,orders o
where c.cust_id=o.cust_id(+)
group bu c.cust_id

 

select cust_name,cust_state ,
(select count(*) from orders where orders.cust_id=customers.cust_id)as orders
from customers
order by cust_name

 

多个用户 可能每个用户都有多个订单 那么 如果想统计显示各个用户各自有多少个订单
用count统计 如果只有一个用户的话 那么直接count就可以
如果多个订单属于不同的用户 那么只用count就没办法进行统计
因为count统计符合条件的一个列 当用户不同的时候count统计不知道每个用户有多少个订单了
必须用group by 分开客户名字 那么这样客户名字