6.2 连接查询综合运用
语法:
内连接:
select 字段名 from 表1 {inner} join 表2
on 表1.字段1=表2.字段2
[where 子句]
自然连接:
select 字段名 from 表1 natural join 表2
[where 子句]
多表连接:
select 字段名 from 表1
join 表2 on 表1.字段1=表2.字段2-1
join 表3 on 表2.字段2-2=表3.字段3
[where 子句]
外连接:
select 字段名 from 表1
left / right / full [outer]
join 表2 on 表1.字段1=表2.字段2
两表连接语法:
select 字段名 from 表1, 表2
where 表1.字段1=表2.字段2
[and 其他条件]
三表连接语法:
select 字段名 from 表1 ,表2 ,表3
where 表1. 字段1=表2.字段2-1
and 表2.字段2-2=表3.字段3
[and 其他条件]
1.统计每一家公交公司所属线路的站点总数
SELECT company 公司, COUNT(*) 站点数
from line_station LS,line L
WHERE LS.lineNo=L.lineNo
GROUP BY company
ORDER BY count(*)
运行结果如下:
2.计算经过站点“解放大道古田四路”的公交线路的数量以及营运车辆数量
SELECT count(*) 线路数, sum(number) 运营车辆数
from line_station LS,line L
WHERE LS.lineNo=l.lineNo
AND station='解放大道古田四路'
运行结果如下:
3.计算每条公交线路的司机人数,并显示司机人数大于3的分组信息,按司机人数逆序显示
SELECT lineNo 线路号,count(*) 司机人数
FROM line l,vehicle v,driver d
WHERE d.driverID=v.driverID
AND v.lineID=l.lineID
GROUP BY lineNo
HAVING count(*)>3
ORDER BY 司机人数 DESC
运行结果如下:
4.统计每件商品的销售数量和销售金额,要求按照销售量和销售金额升序显示商品名,销售量和销售金额
SELECT g.goodsName 商品名, sum(od.quantity)销售量, sum(od.quantity*g.unitPrice) 销售金额
from goods g
LEFT JOIN ordersdetail od
ON g.goodsID=od.goodsID
GROUP BY g.goodsID
ORDER BY 销售量,销售金额
运行结果如下:
5.计算每个订单的金额,要求按照订单下单日期升序和订单金额降序显示订单ID,订单下单日期,订单金额和顾客姓名
SELECT od.ordersID 订单ID , o.ordersDate 订单下单日期 , sum(od.quantity*g.unitPrice) 订单金额 , c.cName 顾客姓名
FROM ordersdetail od, orders o,goods g, customer c
WHERE od.ordersID=o.ordersID
AND o.customerID=c.customerID
and od.goodsID=g.goodsID
GROUP BY o.ordersID
ORDER BY 订单下单日期,订单金额 DESC
运行结果如下:
注意:
(1)字符要确定是英文字符
(2)为表命名要以首字母命名,以确保精准查询