#######################CHAPTER9汇总查询得到的数据#################
--9.1.1 COUNT函数
--用于统计不包含NULL值得记录或字段值,包括重复行。默认为ALL,DISTINCT得另加
--语法
COUNT [(*)|(DISTINCT|ALL)] (COLUMN NAME)
mysql> SELECT * FROM EMPLOYEE_PAY_TBL;
+-----------+---------------+------------+----------+-----------------+--------+-------+
| EMP_ID | POSITION | DATE_HIRE | PAY_RATE | DATE_LAST_RAISE | SALARY | BONUS |
+-----------+---------------+------------+----------+-----------------+--------+-------+
| 213764555 | SALES MANAGER | 2004-08-14 | NULL | 2009-08-01 | 30000 | 2000 |
| 220984332 | SHIPPER | 2006-07-22 | 11 | 1999-07-01 | NULL | NULL |
| 311549902 | MARKETING | 1999-05-23 | NULL | 2009-05-01 | 40000 | NULL |
| 313782439 | SALESMAN | 2007-06-28 | NULL | NULL | 20000 | 1000 |
| 442346889 | TEAM LEADER | 2000-06-17 | 14.75 | 2009-06-01 | 30000 | NULL |
| 443679012 | SHIPPER | 2001-01-14 | 15 | 1999-01-01 | NULL | NULL |
+-----------+---------------+------------+----------+-----------------+--------+-------+
6 rows in set
--获取统计全部EMP_ID
mysql> SELECT COUNT(EMP_ID) FROM EMPLOYEE_PAY_TBL;
+---------------+
| COUNT(EMP_ID) |
+---------------+
| 6 |
+---------------+
1 row in set
--统计SALARY的全部行
mysql> SELECT COUNT(ALL SALARY) FROM EMPLOYEE_PAY_TBL;
+---------------+
| COUNT(SALARY) |
+---------------+
| 4 |
+---------------+
1 row in set
--只统计SALARY不相同的行
mysql> SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE_PAY_TBL;
+------------------------+
| COUNT(DISTINCT SALARY) |
+------------------------+
| 3 |
+------------------------+
1 row in set
--COUNT(*)来获得表EMPLOYEE_TBL全部记录数量(包括重复项和NULL)
mysql> SELECT COUNT(*)
FROM EMPLOYEE_TBL;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set
--
mysql> SELECT * FROM EMPLOYEE_TBL;
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+--------+
| EMP_ID | LAST_NAME | FIRST_NAME | MIDDLE_NAME | ADDRESS | CITY | STATE | ZIP | PHONE | PAGER |
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+--------+
| 213764555 | GLASS | BRANDON | SCOTT | 1710 MAIN ST | WHITELAND | IN | 47885 | 3178984321 | NULL |
| 220984332 | WALLACE | MARIAH | NULL | 7889 KEYSTONE AVE | INDIANAPOLIS | IN | 46741 | 3173325986 | NULL |
| 311549902 | STEPHENS | TINA | DAWN | RR 3 BOX 17A | GREENWOOD | IN | 47890 | 3178784465 | 123445 |
| 313782439 | GLASS | JACOB | NULL | 3789 WHITE RIVER BLVD | INDIANAPOLIS | IN | 45734 | 3175457676 | NULL |
| 442346889 | PLEW | LINDA | CAROL | 3301 BEACON | INDIANAPOLIS | IN | 46224 | 3172978990 | NULL |
| 443679012 | SPURGEON | TIFFANY | NULL | 5 GEORGE COURT | INDIANAPOLIS | IN | 46234 | 3175679007 | NULL |
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+--------+
6 rows in set
--EMPLOYEE_TBL中全部EMP_ID
mysql> SELECT COUNT(EMP_ID) FROM EMPLOYEE_TBL;
+---------------+
| COUNT(EMP_ID) |
+---------------+
| 6 |
+---------------+
1 row in set
--EMPLOYEE_TBL中全部PAGER,只有一个有传呼机
mysql> SELECT COUNT(PAGER
) FROM EMPLOYEE_TBL;
+--------------+
| COUNT(PAGER) |
+--------------+
| 1 |
+--------------+
1 row in set
mysql> SELECT * FROM ORDERS_TBL;
+---------+---------+---------+-----+------------+
| ORD_NUM | CUST_ID | PROD_ID | QTY | ORD_DATA |
+---------+---------+---------+-----+------------+
| 56A901 | 232 | 11235 | 1 | 2009-10-22 |
| 56A917 | 12 | 907 | 100 | 2009-09-30 |
| 32A132 | 43 | 222 | 25 | 2009-10-10 |
| 16C17 | 090 | 222 | 2 | 2009-10-17 |
| 18D778 | 287 | 90 | 10 | 2009-10-17 |
| 23E934 | 432 | 13 | 20 | 2009-10-15 |
| 90C461 | 560 | 1234 | 2 | NULL |
+---------+---------+---------+-----+------------+
7 rows in set
mysql> SELECT COUNT(DISTINCT PROD_ID)
-> FROM ORDERS_TBL;
+-------------------------+
| COUNT(DISTINCT PROD_ID) |
+-------------------------+
| 6 |
+-------------------------+
1 row in set
--9.1.2 SUM函数
--返回某一组记录中某一字段值的总和,此字段必须是数值型。
--语法
SUM ([ DISTINCT ] COLUMN NAME)
mysql> SELECT * FROM EMPLOYEE_PAY_TBL;
+-----------+---------------+------------+----------+-----------------+--------+-------+
| EMP_ID | POSITION | DATE_HIRE | PAY_RATE | DATE_LAST_RAISE | SALARY | BONUS |
+-----------+---------------+------------+----------+-----------------+--------+-------+
| 213764555 | SALES MANAGER | 2004-08-14 | NULL | 2009-08-01 | 30000 | 2000 |
| 220984332 | SHIPPER | 2006-07-22 | 11 | 1999-07-01 | NULL | NULL |
| 311549902 | MARKETING | 1999-05-23 | NULL | 2009-05-01 | 40000 | NULL |
| 313782439 | SALESMAN | 2007-06-28 | NULL | NULL | 20000 | 1000 |
| 442346889 | TEAM LEADER | 2000-06-17 | 14.75 | 2009-06-01 | 30000 | NULL |
| 443679012 | SHIPPER | 2001-01-14 | 15 | 1999-01-01 | NULL | NULL |
+-----------+---------------+------------+----------+-----------------+--------+-------+
6 rows in set
--计算薪水总和
mysql> SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| SUM(SALARY) |
+-------------+
| 120000.00 |
+-------------+
1 row in set
--DISTINCT计算不同薪水总和(一般不用)
mysql> SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE_PAY_TBL;
+----------------------+
| SUM(DISTINCT SALARY) |
+----------------------+
| 90000.00 |
+----------------------+
1 row in set
--9.1.3 AVG函数
--返回一组指定记录的平均值,必须是数值类型
mysql> SELECT AVG(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| AVG(SALARY) |
+-------------+
| 30000 |
+-------------+
1 row in set
--不同薪水的平均值
mysql> SELECT AVG(DISTINCT SALARY) FROM EMPLOYEE_PAY_TBL;
+----------------------+
| AVG(DISTINCT SALARY) |
+----------------------+
| 30000 |
+----------------------+
1 row in set
--9.1.4 MAX
--语法,DISTINCT没有意义
MAX (COLUMN NAME)
mysql> SELECT MAX(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| MAX(SALARY) |
+-------------+
| 40000 |
+-------------+
1 row in set
--9.1.5 MIN
--语法
MIN(COLUMN NAME)
mysql> SELECT MIN(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| MIN(SALARY) |
+-------------+
| 20000 |
+-------------+
1 row in set
--汇总函数和算数操作_构建字段别名 AVG_QTY
mysql>SELECT COUNT(ORD_NUM), SUM(QTY),
SUM(QTY) / COUNT(ORD_NUM) AVG_QTY
FROM ORDERS_TBL;
+----------------+----------+---------+
| COUNT(ORD_NUM) | SUM(QTY) | AVG_QTY |
+----------------+----------+---------+
| 7 | 160 | 22.8571 |
+----------------+----------+---------+
1 row in set
--9.4.2 练习
--9.4.2.1.A
mysql> SELECT AVG(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| AVG(SALARY) |
+-------------+
| 30000 |
+-------------+
1 row in set
--9.4.2.1.B
mysql> SELECT MAX(BONUS) FROM EMPLOYEE_PAY_TBL;
+------------+
| MAX(BONUS) |
+------------+
| 2000 |
+------------+
1 row in set
--9.4.2.1.C
mysql> SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL;
+-------------+
| SUM(SALARY) |
+-------------+
| 120000.00 |
+-------------+
1 row in set
--9.4.2.1.D
mysql> SELECT MIN(PAY_RATE) FROM EMPLOYEE_PAY_TBL;
+---------------+
| MIN(PAY_RATE) |
+---------------+
| 11 |
+---------------+
1 row in set
--9.4.2.1.E
mysql> SELECT COUNT(EMP_ID) FROM EMPLOYEE_PAY_TBL;
+---------------+
| COUNT(EMP_ID) |
+---------------+
| 6 |
+---------------+
1 row in set
--9.4.2.2
mysql> SELECT * FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE 'G%';
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+-------+
| EMP_ID | LAST_NAME | FIRST_NAME | MIDDLE_NAME | ADDRESS | CITY | STATE | ZIP | PHONE | PAGER |
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+-------+
| 213764555 | GLASS | BRANDON | SCOTT | 1710 MAIN ST | WHITELAND | IN | 47885 | 3178984321 | NULL |
| 313782439 | GLASS | JACOB | NULL | 3789 WHITE RIVER BLVD | INDIANAPOLIS | IN | 45734 | 3175457676 | NULL |
+-----------+-----------+------------+-------------+-----------------------+--------------+-------+-------+------------+-------+
2 rows in set
--9.4.2.3
mysql> SELECT SUM(QTY*10) FROM ORDERS_TBL;
+----------+
| SUM(QTY) |
+----------+
| 160 |
+----------+
1 row in set
--9.4.2.4
mysql> SELECT MAX(LAST_NAME) AS LAST_NAME FROM EMPLOYEE_TBL;
+-----------+
| LAST_NAME |
+-----------+
| WALLACE |
+-----------+
1 row in set
mysql> SELECT MIN(LAST_NAME) AS LAST_NAME FROM EMPLOYEE_TBL;
+-----------+
| LAST_NAME |
+-----------+
| GLASS |
+-----------+
1 row in set
--9.4.2.5
mysql> SELECT AVG(LAST_NAME) FROM EMPLOYEE_TBL;
+----------------+
| AVG(LAST_NAME) |
+----------------+
| 0 |
+----------------+
1 row in set