第5章数据库查询-02.ppt

北京物资学院信息学院 【例5-29】查询进货的北京客户所进零件的编号和名称。 SELECT 库存零件表.零件编号,零件名称 FROM 库存零件表 JOIN 进货单表 JOIN 客户表 ON 客户表.客户编号=进货单表.客户编号 ON 库存零件表.零件编号=进货单表.零件编号 AND 城市=北京 GO 【例5-30】查询学生选修的课程的情况，输出学号和课程名，如果有课程没有学生选修，也输出。 SELECT 学号,课程名 FROM 课程表 LEFT JOIN 成绩表 ON 课程表.课程号=成绩表.课程号 【例5-31】查询学生选修的课程的情况，输出姓名和课程号，如果有学生没有选修课程，也输出。 SELECT 姓名,课程号 FROM 成绩表 RIGHT JOIN 学生表 ON 学生表.学号=成绩表.学号 【例5-32】查询所有学生选修所有课程的情况。 SELECT 姓名,课程名 FROM 学生表 CROSS JOIN 课程表 GO 【例5-34】统计选修了1号课程的学生的人数。 SELECT count(*),count(分数) FROM 成绩表 WHERE 课程号=1 【例5-35】统计红色零件的总数。 SELECT COUNT(颜色) FROM 库存零件表 WHERE 颜色=红 空值能使聚合函数产生意想不到的结果 对包含空值的字段使用聚合函数时，应注意 SQL Server 的聚合函数（COUNT(*) 除外）将忽略字段中的空值 COUNT（*）将计算所有的行，即使每个字段都含有空值 除了使用聚集函数对结果进行汇总计算外，T-SQL还提供了COMPUTE关键字对结果进行汇总计算。 COMPUTE生成合计作为附加的汇总列，出现在结果集的最后。当与BY一起使用时， COMPUTE字句在结果集内生成控制中断和小计。可在同一查询内指定COMPUTE BY和COMPUTE。其语法格式如下： [COMPUTE {{AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM}(expression)}[,…n] [BY expression [,…n] ] 说明： COMPUTE对指定要执行的聚集函数计算。如果是用COMPUTE子句指定的聚集函数，则不允许它们使用DISTINCT关键字。 【例5-36】查询外语学院的学生信息，并统计人数。 SELECT * FROM 学生表 WHERE 院系名称=‘外语学院’ COMPUTE COUNT(学号) GO 【例5-37】查询选修了5号课程的学生，并按分数从高到低排序输出学生的学号和分数。 SELECT 学号,分数 FROM 成绩表 WHERE 课程号=5 ORDER BY 分数 DESC 【例5-35】查询零件信息，并按重量从轻到重输出。 SELECT * FROM 库存零件表 ORDER BY 重量 使用 GROUP BY 子句 使用 GROUP BY 子句（续） 【例5-39】统计每门课程的总分和平均分，并按平均分从高到低排序输出。 SELECT 课程号,总分=SUM(分数),平均分=AVG(分数) FROM 成绩表 GROUP BY 课程号 ORDER BY AVG(分数) DESC GO 【例5-40】统计每个学院学生的男女生人数。 SELECT 院系名称,性别,COUNT(*) FROM 学生表 GROUP BY 院系名称,性别 GO 统计每个学院学生的男女生人数。 SELECT 院系名称,性别,COUNT(*) FROM 学生表 GROUP BY 性别,院系名称 GO 【例5-41】统计每种颜色零件的总数。 SELECT 颜色,COUNT(颜色) FROM 库存零件表 GROUP BY 颜色 GO USE northwind SELECT productid, orderid,quantity FROM [order details] GO USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM [order details] GROUP BY productid HAVING SUM(quantity)=1200 GO 在结果集中生成汇总值 使用带有 ROLLUP 运算符的 GROUP BY 子句 使用带有 CUBE 运算符的 GROUP BY 子句 使用 GROUPING 函数 Use northwind Select orderid,productid,sum(quantity) as total_quantity From [order details] Group by orderid,productid Order by orderid,product