mysql> SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+mysql> SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+13章 分组数据
mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+排序
mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id ORDER BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+使用 ROLLUP
mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
| NULL | 14 |
+---------+-----------+13.3 过滤分组
mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2 ORDER BY num_prods;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1002 | 2 |
| 1005 | 2 |
| 1001 | 3 |
| 1003 | 7 |
+---------+-----------+mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2 ORDER BY num_prods;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1005 | 2 |
| 1003 | 4 |
+---------+-----------+13.4 分组和排序
mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+HAVING 可以使用AS后的描述
mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+使用ORDER BY 进行排序
mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+13.5 SELECT 子句顺序
14章 使用子查询
利用子查询进行过滤
1.
mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
同效果： mysql> SELECT order_num FROM orderitems WHERE prod_id LIKE 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+2.
mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+3.
mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+合并1.， 2. and 3.
mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+作为计算字段使用子查询
mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+使用了完全限定列名。 orders.cust_id = customers.cust_id代表让SQL比较orders表中的cust_id 与当前正从customers表中检索中的cust_id.若不使用完全限定列名：
mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 5 |
| E Fudd | IL | 5 |
| Mouse House | OH | 5 |
| Wascals | IN | 5 |
| Yosemite Place | AZ | 5 |
+----------------+------------+--------+此时，MySQL将假定对 orders表中的cust_id自身进行查询。而SELECT COUNT(*) FROM orders WHERE orders.cust_id = cust_id总是返回orders表中的订单总数。
必须注意限定是有歧义的列名。
相关子查询：涉及外部查询的子查询。