时间：2021-07-01 10:21:17 帮助过：7人阅读
-> from Vendors
-> order by vend_name;
+--------------------------------+
| vend_title |
+--------------------------------+
| Bear Emporium(USA) |
| Bears R Us(USA) |
| Doll House Inc.(USA) |
| Fun and Games(England) |
| Furball Inc.(USA) |
| Jouets et ours(France) |
+--------------------------------+
6 rows in set (0.00 sec)
-> from OrderItems
-> where order_num = 20008;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01 | 5 | 4.99 | 24.95 |
| BR03 | 5 | 11.99 | 59.95 |
| BNBG01 | 10 | 3.49 | 34.90 |
| BNBG02 | 10 | 3.49 | 34.90 |
| BNBG03 | 10 | 3.49 | 34.90 |
+---------+----------+------------+----------------+
5 rows in set (0.02 sec)
| 函数 | 说明 |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
-> FROM Products;
+------------+
| prod_price |
+------------+
| 5.99 |
| 8.99 |
| 11.99 |
| 3.49 |
| 3.49 |
| 3.49 |
| 4.99 |
| 9.49 |
| 9.49 |
+------------+
9 rows in set (0.00 sec)
mysql> select avg(prod_price) as avg_price
-> from Products;
+-----------+
| avg_price |
+-----------+
| 6.823333 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT AVG(prod_price) AS avg_price
-> from Products
-> where vend_id = ‘DLL01‘;
+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*)
-> FROM Products;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
注：
1）使用COUNT(*)对表中行的数目进行计数，不管列表中包含的是空值还是非空值。
2）使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
mysql> SELECT COUNT(cust_email) AS num_cus
-> FROM Customers;
+---------+
| num_cus |
+---------+
| 3 |
+---------+
1 row in set (0.02 sec)
mysql> SELECT MAX(prod_price) AS max_price
-> FROM Products;
+-----------+
| max_price |
+-----------+
| 11.99 |
+-----------+
1 row in set (0.00 sec)
注：可以观察发现，聚集函数通过对所有行或者所有非空行或者符合where条件的所有（非空）行进行操作，结果集为只有一行数据。如果跟结果集为多行的查询一起，则会很怪异。如下：mysql> SELECT prod_name, MAX(prod_price) AS price
-> FROM Products;
+-------------------+-------+
| prod_name | price |
+-------------------+-------+
| 8 inch teddy bear | 11.99 |
+-------------------+-------+
1 row in set (0.00 sec)
（注：上面的是错误的用法，具体对比看下面的全表，就能发现错误）
mysql> SELECT prod_name, prod_price
-> FROM Products;
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| 8 inch teddy bear | 5.99 |
| 12 inch teddy bear | 8.99 |
| 18 inch teddy bear | 11.99 |
| Fish bean bag toy | 3.49 |
| Bird bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| Raggedy Ann | 4.99 |
| King doll | 9.49 |
| Queen doll | 9.49 |
+---------------------+------------+
9 rows in set (0.01 sec)
又一例子
mysql> select vend_id, count(*) as num_prods
mysql> SELECT prod_name, prod_price
-> FROM Products
-> WHERE prod_price = MAX(prod_price);
ERROR 1111 (HY000): Invalid use of group function
mysql> SELECT prod_name, prod_price
-> FROM Products
-> WHERE MAX(prod_price);
ERROR 1111 (HY000): Invalid use of group function
WHY？
分组：
mysql> SELECT cust_id, COUNT(cust_name) AS COUNT -> FROM Customers -> Where cust_name = ‘Fun4All‘; +------------+-------+ | cust_id | COUNT | +------------+-------+ | 1000000003 | 2 | +------------+-------+ 1 row in set (0.01 sec) mysql> SELECT cust_id, COUNT(cust_name) AS count -> FROM Customers -> WHERE cust_name = ‘Fun4All‘ -> GROUP BY cust_id; +------------+-------+ | cust_id | count | +------------+-------+ | 1000000003 | 1 | | 1000000004 | 1 | +------------+-------+ 2 rows in set (0.01 sec) (注：GROUP BY对COUNT函数起到很重要的作用，COUNT根据GOUP BY的分组来计算）
| 子句 | 说明 | 是否必须使用 |
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
《sql必知必会》
标签：