连接查询综合应用

连接查询综合应用

综合使用连接查询、聚合函数和分组查询可以实现很多复杂的查询需求。

一、连接查询
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 = '非空调车';

运行图如下

总结

1. 连接查询分为：内连接和外连接。
2. 常用的聚合函数包括SUM、MAX、MIN、COUNT和AVG。
3. GROUP BY子句可以实现分组查询
4. having子句和where子句均用于过滤数据，两者的区别在于：where子句对结果集进行过滤筛选，而having子句则对分组的结果进行筛选。
5. 可以使用多列分组。
6. 简单多表连接比多表连接要更加简单明了一些。