SQL入门经典—CHAPTER9汇总查询得到的数据

#######################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