连接查询综合应用
综合使用连接查询、聚合函数和分组查询可以实现很多复杂的查询需求。
一、连接查询
1.连接查询分为:内连接和外连接。
1)内连接:内连接是应用程序中非常常见的连接操作,它一般都是默认的连接类型;
2)外连接:在内连接查询中,只有满足连接条件的记录才能出现在查询结果中。但在实际应用中,如果希望不满足连接条件的记录也在查询结果中出现,这时就需要使用外连接。
二、聚合函数
1.定义:聚合函数能对集合中的一组数据进行计算,并返回单个计算结果。
2.种类:聚合函数共五种分别为:sum函数、max函数、min函数、avg函数、count函数。
1)sum函数:sum函数返回选取的某列值的总和;
2)max/min函数:MAX函数返回选取的某列的最大值;MIN函数返回选取的某列的最小值;
3)AVG函数:AVG函数返回选取的某列的平均值;
4)COUNT函数:COUNT函数返回选取的某列或记录的行数;COUNT(*)获取表行数;COUNT(column)获取表中column列不为空的行数。
三、分组查询
1.使用GROUP BY分组:GROUP BY意为“根据(by)一定的规则进行分组(Group)”;其作用是通过一定的规则将一个数据集划分为若干个小的区域,然后针对若干个小区域进行统计汇总。一般用于对查询结果分组统计,常与聚合函数联合使用。
2.多字段段分组和HAVING子句
1)多字段分组规则:使用GROUP BY可以根据多个字段进行分组;分组层次从左至右,即先按第一个字段分组,然后在第一个字段值相同的记录中,再根据第二个字段的值进行分组……依次类推。
2)使用HAVING对分组结果进行过滤
HAVING的含义和作用:HAVING子句的作用是筛选满足条件的组,即在分组之后过滤数据;使用 HAVING子句中的条件用于显示特定的组,条件中经常包含聚合函数;HAVING子句在GROUP BY子句之后。
- 连接查询综合应用示例
计算每条公交线路的司机人数,按司机人数降序
连接驾驶员表、车辆表和线路表,SQL语句如下:
SELECT
lineNo 线路号,
count(*) 司机数
FROM
driver D,
vehicle v,
line L
WHERE
D.driverID = V.driverID
AND V.lineID = L.lineID
GROUP BY
lineNo
ORDER BY
司机数 DESC;
运行结果图如下:
学生实践作业:
1.统计每件商品的销售数量和销售金额,要求按照销售量和销售金额升序显示商品名、销售量和销售金额
实现SQL语句如下:
在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
销售量,销售金额;
运行图如下:
2.计算每个订单的金额,要求按照订单下单日期升序和订单金额降序显示订单 ID、订单下单日期、订单金额和顾客姓名
实现SQL语句如下:
SELECT
o.ordersID 订单,
o.ordersDate 下单日期,
sum(od.quantity * g.unitPrice) 订单金额,
c.cName 客户名
FROM
orders o,
ordersdetail od,
goods g,
customer c
WHERE
o.ordersID = od.ordersID
AND od.goodsID = g.goodsID
AND o.customerID = c.customerID
GROUP BY
o.ordersID
ORDER BY
下单日期,
订单金额 DESC;
运行图如下:
*多表连接查询
简单多表连接查询
如果在 FROM 子句中,直接列出所有要连接的表,然后在 WHERE 子句中指定连接条件,此为简单多表查询, 它与内连接功能相同。
使用两表连接查询语法格式如下:
SELECT fieldlist FROM tablel,table2
WHERE tablet.columnt=table2.column2 【and 其他条件】
使用三表连接查询语法格式如下:
SELECT fieldlist FROM table1.table2,table3
where tablel.column1=table2.column2_1 and
table2.column2_2=table3.column3 【and 其他条件】
- 简单多表连接示例
车牌号和型号来源于车辆表;司机姓名来源于司机表;线路号、起点战和终点战来源于线路表,因而需要连接三张表:车辆表、司机表和线路表,实现SQL语句如下
SELECT
NAME 司机姓名,
plateNo 车牌号,
model 型号,
lineNo 线路号,
from_station 起点战,
end_station 终点站
FROM
vehicle V,
driver D,
line L
WHERE
V.driverID = D.driverID
AND V.lineID = L.lineID
AND type = '非空调车';
运行图如下
总结
- 连接查询分为:内连接和外连接。
- 常用的聚合函数包括SUM、MAX、MIN、COUNT和AVG。
- GROUP BY子句可以实现分组查询
- having子句和where子句均用于过滤数据,两者的区别在于:where子句对结果集进行过滤筛选,而having子句则对分组的结果进行筛选。
- 可以使用多列分组。
- 简单多表连接比多表连接要更加简单明了一些。