2012.6.14 星期四

教学内容：数据分组（五）

教学目的：使用数据分组，让数据处理更加智能

教学重点：group by 短语的实战

教学难点：多记录下的查询、分组统计

教学方法：实例法、程序代码调试

一、作业评析、复习巩固、课程导入

截止2012.6.13日，下午14:08，没有上交作业。情况不明。

二、本节内容

1.安装xx3.sql

2.完成以下应用（统计查询出数字，并写出相应指令）

1）查看books数据库中有多少张表；

2）表格books中有多少条记录、customers表格中有多少条记录；

3）customers中涉及到的城市有多少个（相同的算一个）；

4）books中有多少个作者（相同的算一个）；

5）books中一个作者写3本及3 本以上的书的人有多少个；

6）统计出books中作者中不同姓氏的数量；并统计出写书数最多的前三名的姓氏；

7）.统计出customers表中姓‘白’的，家住在‘安肃镇’的有哪些人？

8）统计出customers表中姓‘白’、‘李’、‘张’的各有多少人？

三、练习

将在线测试的所有题目认真练习。

四、总结

内容调试的结果

五、作业

把上次作业中的题目重写一遍，不要在原题上修改。下次课准备测试，题目就是10道题。

2012.6.7 星期四

教学内容：数据分组（四）

教学目的：使用数据分组，让数据处理更加智能

教学重点：group by 短语的实战

教学难点：多记录下的查询、分组统计

教学方法：实例法、程序代码调试

一、作业评析、复习巩固、新课导入

1.作业评析

上交情况：应该上交15人，实际上交15人。本次优秀人员：田露露、刘艳新、黄李晴。

存在问题：

1）分组依据中丢落year(date)，但本题目中因为只有2007年的定单，所以‘结果’没错，但实际情况如果有其他的年份，且分组依据中不涉及年份，所以每个年份的相同月份就会统计到一起，结果就会错误，不切合题意。

所以应该用 group by year(date),month(date)；做为分组依据

2）部分同学的输出项目中的‘逗号’丢落，在实际操作中就会出现问题。如：select month(date),sum(amount) from orders.....

3）部分同学的语句末尾丢落‘分号’，这样命令就不会被执行。如：select ......from .....group by ......；

4）部分同学的书写很不规范、了草，应该认真书写；

2.复习巩固

这几次课我们学习了分组统计智能查询。

1）分组短语，group by+分组依据；【http://192.168.8.40/ch01/cxly.asp#timu164】

2）使用数据分组，让数据处理更加智能；【http://192.168.8.40/ch01/cxly.asp#timu165】

3）group by 短语的应用，时间问题的处理；【http://192.168.8.40/ch01/cxly.asp#timu166】

3.课程导入

我们的books数据库的组织结构

五张表：customers（顾客表）、orders（定单表）、books（书目表）、order_items（定单项目表）、book_reviews（书目反馈表）。

记录若干：customers（3条）、orders（4条）、books（4条）、order_items（5条）、book_reviews（1条）

问题提出：

本次推出xx3.sql文件，可以使用source xx3.sql；导入建立相应的数据库、表环境；

主要指标：books-------增加了记录；customers--------增加了记录； 

二、多记录下的group by 实战

1.安装xx3.sql

2.完成以下应用（统计查询出数字，并写出相应指令）

1）查看books数据库中有多少张表；

2）表格books中有多少条记录、customers表格中有多少条记录；

3）customers中涉及到的城市有多少个（相同的算一个）；

4）books中有多少个作者（相同的算一个）；

5）books中一个作者写3本及3 本以上的书的人有多少个；

6）统计出books中作者中不同姓氏的数量；并统计出写书数最多的前三名的姓氏；

三、总结

group by 的基本用途；

group by分组后，常用到的统计函数；

注意事项。 

四、作业

1.统计出customers表中姓‘白’的，家住在‘安肃镇’的有哪些人？

2.统计出customers表中姓‘白’、‘李’、‘张’的各有多少人？

2012.5.31 星期四

教学内容：数据分组（三）

教学目的：使用数据分组，让数据处理更加智能

教学重点：group by 短语的应用，时间问题的处理

教学难点：分组方式

教学方法：实例法、程序代码调试

一、作业评析、复习巩固（重点）、新课导入

1.作业评析

上交情况：本次应该上交15本，实际上交15本，上交率100%；

认真情况：商丹丹、黄李睛。（书写认真）

作业问题：（本次没有优秀人员）

（1）输出项逗号丢失：select orderid avg(quantity) from order_items group by orderid;

应该是：select orderid ,avg(quantity) from order_items group by orderid;

（2）句末的分号丢失：select orderid , avg(quantity) from order_items group by orderid

应该是：select orderid , avg(quantity) from order_items group by orderid;

（3）统计定单的个数，很多同学用了 select orderid,sum(orderid) from order_items group by orderid;

比如3号顾客有三个定单，分别是（3、8、10），很显然应该统计结果是3个定单，而不是把（3+8+10）等于21个。

所以，应该是用count()函数统计定单的个数：select orderid,count(orderid) from order_items group by orderid;

（4）一个统计函数，对一组一次只能统计出一个结果，有的同学用了sum(orderid,amount)，想一次出两个结果，是不行的；

本题应该使用：count(orderid),sum(amount)，即使用同一个函数，也应该分开来写：sum(orderid),sum(amount)。

2.巩固复习

题目：统计同一顾客所有定单中书目的数量

同一顾客所有定单中的书目数量，重点是同一顾客的区分上，显然需要把顾客号（orderid）一样的分成一组，然后把这一组中的需求（书目的数量累加），然后再返回到输出结果即可。

应该注意三点：

一是分组依据：顾客号，orderid

二是统计函数：累计，求各（sum）

三是统计内容：累计什么，书目数量（quantity）

对号入座：

分组依据，写入group by短语，即group by orderid

统计函数，内容，sum(quantity)

合成即可：

select orderid,sum(quantity) from order_items group by orderid;

3.新课导入

问题：时间问题的处理

（1）在表格orders中，按年份统计出定单的数量

（2）在表格orders中，按月份统计出定单的数量

分析：显然需要对orders表格中的date字段进行显示，在分组统计中，需要把时间进行分割。这节课我们就学习在分组智能查询中的相关的日期时间处理。

二、新授

1.获得当前的日期和时间：now()、currentdate、currenttime；

 2.获得当前日期（now()）的年份、月份、日子、季度；

含义：返回四列内容，分别是2012‘年’、5‘月’、30‘日’，第2‘季度’

3.也可以返回指定日期的年、月、日、季度，如：2013.3.25；

含义：返回变量@dt（前边已经赋值为2013.3.25），年份、月份、日子、季度

4.可以返回数据库中指定的数据表中的内容的年、月、日、季度

含义：指定表格orders中的date字段内容分解成年、月、日、季度

5.指定表头处理

6.联系题目，把表格ordres中，按月份统计出定单数量；（这节课的重点）

分析：将月份一致的归到一组，得事先知道每个定单的月份。可用month(date)得到每个定单的具体月份。

分组：分组依据就是month(date)

函数：统计每组的个数,应该用count()，因为只统计个数，可能使用count(*),也可以使用count(orderid)、count(customerid)、count(amount)、count(date)都可以

 含义：4月份共有3个定单，5月份有1个定单。

7.扩展（含有合计功能的统计）

含义：这说明这次查询合计共有4个定单。这和orders表格中的定单数（记录数）是一致的，共四条。

三、练习

按年份统计出表格orders中的定单数量，得出结论，哪个年份的定单数量多。

四、作业

统计出每年的每个月份的定单数量、定单金额。

【解释：根据统计结果能分析中，那年的哪个月份定单多（解释顾客多），哪个月份的定单金额多（解释买得多）】。

例：（只供参考）

2012.5.24 星期四

教学内容：数据分组（二）

教学目的：使用数据分组，让数据处理更加智能

教学重点：group by 短语的应用

教学难点：分组方式

教学方法：实例法、程序代码调试

一、作业评析、复习巩固（重点）、新课导入

1.作业评析：

作业情况：

就该上交15人，实际上交15人，本次作业没有A等，B等有4个。

存在问题：

分组依据不正确：有的人使用group by quantity；（如：select orderid,sum(isbn) from order_items group byquantity; ）显然是对group by 短语的格式及分组依据不能正确分析得到；

统计内容不正确：有的人用sum(orderid)，问的是书目的数量，却去统计顾客号的累加；

统计函数应用不正确：有的人用count(*)，显然是统计分组后的记录条数，和题意相差甚远；

有的同学select后边的字段中间逗号没写、丢失了句末的分号；

有的同学把group by +分组依据的格式颠倒位置，如：orderid by group，by group orderid等等，这是一种规范的短语格式，不能随意颠倒。

2.复习巩固（重点）

例如：题目１中的是“统计同一顾客所有定单中书目的数量”

分析：

（1）首先看看order_items表格中的内容，结构

表格名：order_items

字段名：（共有四个字段，分别是orderid,isbn,quantity，保存的内容分别是顾客号、书号、数量）

表格内容分析：

这张表格存放的内容是，某一个顾客（拥有一个唯一的顾客号orderid，像人的身份证一样）对某一个一本书（每本相同的书拥有同一书号isbn，就可以定位到该书）的数量，即“某某顾客想买某某本书多少多少本”。

具体分析：

一个顾客可以买不同的书，这样就会看到表格中，3号顾客（orderid为3的）购买了两种书（一种是书号isbn为 0-672-31769-9的quantity为1的书，一种是书号isbn为 0-672-31769-9的quantity为1的书）；

同一本书会被不同的顾客购买，这样就会看到表格中，书号为 0-672-31769-9的书被两个顾客选购了（一个是2号顾客买了一本，一个是3号顾客买了一本）；

与作业问题结合：

题目1分析：统计同一顾客所有定单中书目的数量

同一顾客所有定单中的书目数量，重点是同一顾客的区分上，显然需要把顾客号（orderid）一样的分成一组，然后把这一组中的需求（书目的数量累加），然后再返回到输出结果即可。

应该注意三点：

一是分组依据：顾客号，orderid

二是统计函数：累计，求各（sum）

三是统计内容：累计什么，书目数量（quantity）

对号入座：

分组依据，写入group by短语，即group by orderid

统计函数，内容，sum(quantity)

合成即可：

select orderid,sum(quantity) from order_items group by orderid;

执行结果：

同理分析：

题目2中是同一本目的所有书目的数量，即同一本书，被不同的顾客下了定单，现在忽略是谁定的，只想知道同一本书共被定了多少本。

分析：

同一本书分成一组（也就是isbn一样的就是同一本书），应该做为分组依据；

统计书目数量的累加（应该用sum统计函数）对（quantity）进行统计；

结果：select isbn,sum(quantity) from order_items group by isbn;

3.新课导入

如果我们只对分组结果中的部分内容感兴趣，应该怎么办？

如：我们要把每本书（上题目2）的数量2及2本以上的结果查询出来，即上图需要去掉第一条记录，只要后边的3条，应该如何办，这节课我们把结果筛选加上，即 having短语。

二、新授

1.having+条件短语

2.应用

select isbn,sum(quantity) from order_items group by isbn having sum(quantity)>1;

3.结果：

3.having与where短语的区别：

• 作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。
• WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。
• 相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）

三、练习

将作业题目中的两题，安装环境，下载建立数据库及表、记录，启动Mysql服务器，完成题目要求！

四、作业

1.对order_items表格中的数据，查询得到每个定单数目的平均定书量。

结果要求：能看出每个定单对应自己的平均定书量，如：2号定单（定了2种书，1种10本，2种30本），那么2号定单共定40本，结果中会出现（2  20），表明2号定单，平均定了20本。

2.对orders表格中，查询得到每位顾客下的定单数量及定单金额。

结果要求：能看出每位不同顾客共下了多少定单，以及这些定单的总金额。结果中有（2   4  156），表示2号顾客，下了4个定单，共需要付款156元。
 

上课时间：2012.5.17（星期四）

教学内容：数据分组

教学目的：使用数据分组，让数据处理更加智能

教学重点：group by 短语的应用

教学难点：分组方式

教学方法：实例法、程序代码调试

一、作业评析、复习巩固、课程导入

1.作业评析：

全班15人，应上交13人（张婷婷、龙静因为期中考试成绩满分，没有作业），实际上交10人，郝倩、韩艳伟、黄李晴未交。本次作业完成优秀人员：商丹丹、王娟。

2.复习巩固：

上节课，我们主要是期中考试试题重点分析（错误比较多的题型），回答了Mysql的6类大的知道提问（提问内容已经写入了在线考试题库），并对Mysql、PHP的环境安装、调试、数据库的连接、表格内容的显示进行了复习。

3.课程导入：

这节课，我们继续深入学习Mysql的数据管理中的select查询功能中的分组功能，因为有分组，我们才对每组中的数据行进行必要的统计、合计，从而得到我们需要的数据。

二、新授（书上的内容为：P【192】页，10.3.4分组与合计数据）

我们经常需要知道某些特征一样的记录进行分组，然后对这些不同的分组结果中的记录行进行进一步的统计、汇总、平均等。

1.例如：在定单表中的平均金额。Mysql中专门有一组合计函数用来实现这种功能。【P192.表10-3】

select avg(amount) from orders;

分析：这个命令来源于表orders,其中有一个‘金额’的字段列amount，通过合计函数avg（），把整个amount列的值加在一起（所有记录中的amount加在一起）求平均值。结果如下：

avg(amount) 

 54.985002 

可以说，这个结果（54.985002）在定单表（orders）中没有存在，而是通过合计函数avg（）统计出来得到的结果。

2.要获得更加详细的信息，可以使用group by 子句。（这使我们可以按分组浏览订单总量的平均值）

例如：按照顾客（customerid相同的为同一名顾客，因为在定单表里允许同一位顾客下了不同的单子，每个单子里不同的书目不同的金额）分组，得到哪些顾客的订单金额大。

select customerid,avg(amount) from orders group by customerid;

结果：

customerid  avg(amount)

1　　　　　49.99002

2　　　　　74.98003

3　　　　　47.485002

分析：在定单表（orders）中，共有4条记录，其中1号顾客1条记录，2号顾客1条，3号顾客2条。如果按题目要求按顾客号分组，必然分成三组，即1条1号顾客分成一组，1条2号顾客分成一组，2条3号顾客分成一组。再把第一组中的1个amount值求平均，显然不变，因为只有1条；把第二组中的1个amount值求平均，显然也不变，因为只有1条；再把第三组中的2个amount值求平均得到（69.98+24.99）/2=47.48的值。并且因为分成了三组，所以得到了结果有三行。

结论：按什么分组（此例按customerid），就把特征一样的（customerid一样的）所有记录（可能是一组一条，一组两条，一组若干条，根据需要）分别进行avg(amount)的函数合计处理，因为分成了三组，就会产生三个分组结果。

 当然，还可以对不同的分组进行不同的操作，且可以对不同的分组同时进行不同的统计、汇总。【P192表10-3】

3.注意：

在使用分组和合计函数的时候，需要注意的是：

如果使用了group by 子句，在select子句中必须是合计函数和group by子句中出现的列；

同样，如果希望在一个group by子句中使用一列，该列必须在select子句中出现；

即：在使用group by子句的时候，出现的列应该和select子句中出现的列匹配。

4.除了分组与合计数据，实际操作的时候，需要对分组的结果进行筛选，可以使用having子句。它可以直接放在group by子句后边，有些类似于where子句。（如果同时有where子句和having子句时，where子句决定哪些记录用于分组，having子句决定分组合计后的哪些行出现在结果里）

三、练习：

分析如下语句：

select customerid，avg(amount) from orders group by customerid having avg(amount)>50;

结果：

customerid avg(amount)

2　　　　　74.980003

分析：

可以看到对比例子，只是多了having avg(amount)>50这个筛选条件，本来定单表orders中按customerid分成了三组，结果中应该有三行数据呀，但结果中只有一个，原因，其余的那两条，被这个条件过滤掉了，因为只有一条合适。

试想：

如何才能把那两条记录找出来，不要这个顾客号为2的结果呢，聪明的你想想如何更改吧。

select customerid，avg(amount) from orders group by customerid having avg(amount)<50;

对了，只需要把‘大于’号更改成‘小于’号即可，你一定知道为什么了是不是。

根据下边参考资料，想想P192页的表10-3中的Mysql中的合计函数的应用

四、参考资料：

（一）group by 用法解析：

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

（二）实例：

某个员工信息表结构和数据如下：
  　id  name  dept  salary  edlevel  hiredate
      1 张三 开发部 2000 3 2009-10-11
      2 李四 开发部 2500 3 2009-10-01
      3 王五 设计部 2600 5 2010-10-02
      4 王六 设计部 2300 4 2010-10-03
      5 马七 设计部 2100 4 2010-10-06
      6 赵八 销售部 3000 5 2010-10-05
      7 钱九 销售部 3100 7 2010-10-07
      8 孙十 销售部 3500 7 2010-10-06

例如，我想列出每个部门最高薪水的结果，sql语句如下：

SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT
查询结果如下：
      DEPT  MAXIMUM
      开发部 2500
      设计部 2600
      销售部 3500

（三）分析、解释一下这个结果：

1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY DEPT中包含的列DEPT。
2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。

注意：计算的是每个部门（由 GROUP BY 子句定义的组）而不是整个公司的 MAX(SALARY)。

（四）例如，查询每个部门的总的薪水数

SELECT DEPT, sum( SALARY ) AS total FROM STAFF GROUP BY DEPT

查询结果如下：

DEPT  total
开发部 4500
设计部 7000
销售部 9600

（五）将 WHERE 子句与 GROUP BY 子句一起使用

分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。

例如，查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM FROM staff WHERE HIREDATE > '2010-01-01' GROUP BY DEPT, EDLEVELORDER BY DEPT, EDLEVEL

查询结果如下：
  DEPT  EDLEVEL  MAXIMUM
      设计部 4 2300
      设计部 5 2600
      销售部 5 3000
      销售部 7 3500

注意：在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对DEPT和EDLEVEL的每个唯一组合各返回一行。
在GROUP BY子句之后使用HAVING子句

（六）可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。为此，在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。每个谓词将组特性（如AVG(SALARY)）与下列之一进行比较：
 

例如：寻找雇员数超过2个的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM FROM staff GROUP BY DEPT HAVING COUNT( * ) >2 ORDER BY DEPT

查询结果如下：

  DEPT  MAXIMUM  MINIMUM
      设计部 2600 2100
      销售部 3500 3000

例如：寻找雇员平均工资大于3000的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM  FROM staff GROUP BY DEPT HAVING AVG( SALARY ) >3000 ORDER BY DEPT

查询结果如下：
  DEPT  MAXIMUM  MINIMUM
      销售部 3500 3000

五、作业（书面作业：写出下面的红色部分题目要求的SQL语句，课堂作业：熟练掌握在线考试中的【51】道题目）

1.查询表order_items中，同一顾客所有定单的书目的数量。

（本题能够解决看看2号顾客共订了多少本书，当然包括相同的书，还包括不同的书）

2.查询表order_items中，同一本书（书号一致）的所有定单的书目数量。

（本题能够解决，同一本书，共被不同的顾客订了多少本）