MySQL 3 数据处理函数、汇总数据、分组数据、子查询

AVG()　　返回某列的平均值

COUNT()　　返回某列的行数

MAX()　　返回某列的最大值

MIN()　　返回某列的最小值

SUM()　　返回某列之和

AVG()函数：

对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可用来返回特定列或行的平均值。

　　SELECT AVG(prod_price) AS avg_price

　　FROM products;

只列出别名avg_price和它代表的列的平均值。

AVG()含糊忽略列值为NULL的行。

COUNT()函数：

COUNT()函数进行计数。可用来确定表中行的数目或符合特定条件的行的数目。

COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值。

COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

　　SELECT COUNT(*) AS num_cust

　　FROM customers;

MAX()函数：

返回指定列中的最大值。MAX()要求指定列名。

　　SELECT MAX(prod_price) AS max_price

　　FROM products;

这里返回了products表中最贵的物品的价格。

MAX()可用于非数值数据，按字典序排序的最大值，即A和Z两个时选择Z。

MAX()忽略列值为NULL的行。

MIN()函数：

返回指定列的最小值。也要求指定列名。

　　SELECT MIN(prod_price) AS min_price

　　FROM products;

MIN()也可用于非数值数据。忽略NULL值。

SUM()函数：

用来返回指定列值的和（总计）。

　　SELECT SUM(quantity) AS items_ordered

　　FROM orderitems

　　WHERE order_num = 20005;

聚集函数的参数可用于算术运算的聚集，如：SUM(A*B) AS cjj。。统计列A*B的最大值。

聚集不同的值：

以上5个聚集函数都可以使用：对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；只包含不同的值，指定DISTINCT参数。

ALL是默认的。

　　SELECT AVG(DISTINCT prod_price) AS avg_price

　　FROM products

　　WHERE vend_id = 1003;

DISTINCT不能用于COUNT。DISTINCT用于MAX和MIN没有意义。

组合聚集函数：

聚集函数可以组合使用。

　　SELECT COUNT(*) AS num_items,

　　　　　　MIN(prod_price) AS price_min,

　　　　　　MAX(prod_price) AS price_max,

　　　　　　ACG(prod_price) AS price_avg

　　FROM products;

3. 分组数据

涉及新的SELECT语句子句，分别是GROUP BY 子句和HAVING子句。

创建分组：

分组是在SELECT语句的GROUP BY子句中建立的。

　　SELECT vend_id, COUNT(*) AS num_prods

　　FROM products

　　GROUP BY vend_id;

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。

除了聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组：

WHERE针对的是行记录，HAVING子句过滤分组。

　　SELECT cust_id, COUNT(*) ASorders

　　FROM orders

　　GROUP BY cust_id

　　HAVING COUNT(*) >= 2;

HAVINF针对的是该过程中的分组。其后跟着的条件是满足的。

HAVING和WHERE可以一起使用：

　　SELECT vend_id, COUNT(*) AS num_prods

　　FROM products

　　WHERE prod_price >= 10

　　GROUP BY vend_id

　　HAVING COUNT(*) >= 2;

上述语句先在满足WHERE的条件下，再进行分组，并对分组进行过滤。

分组和排序：

ORDER BY排序产生的输出，任意列都可以使用。

GROUP BY分组行，但输出可能不是分组的顺序。只可能使用选择列或表达式列，而且必须使用每个选择列表达式。通常与聚集函数一起使用。

　　SELECT order_num, SUM(quantity*item_price) AS ordertotal

　　FROM orderitems

　　GROUP BY order_num

　　HAVING SUM(quantity*item_price) >= 50

　　ORDER BY ordertotal;

SELECT子句的顺序：

SELECT　　要返回的列或表达式　　必须使用

FROM　　从中检索数据的表　　仅在从表选择数据时使用

WHERE　　行级过滤　　不是必须使用

GROUP BY　　分组说明　　仅在按组计算聚集时使用

HAVING　　分组过滤　　不是必须使用

ORDER BY　　输出排序顺序　　不是必须使用

LIMIT　　要检索的行数　　不是必须使用

4. 使用子查询

查询：任何SQL语句都是查询。但此术语一般指SELECT语句。

SQL允许创建子查询，即嵌套在其它查询中的查询。

　　SELECT cust_id

　　FROM orders

　　WHERE order_num IN (SELECT order_num

　　　　　　　　　　　　　FROM orderitems

　　　　　　　　　　　　　WHERE prod_id = ‘TNT2‘);

在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。

子查询可以有两级至多级。由于性能的限制，不能嵌套太多的子查询。

列必须匹配：在WHERE子句中使用子查询，应该保证SELECT语句具有WHERE子句中相同数目的列。

子查询一般与IN操作符结合使用，但也可用于测试等于（=）、不等于（<>）等。

作为计算字段使用子查询：

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。从customers表中检索客户列表，对于检索出的每个客户，统计其在orders表中的订单数目。

　　SELECT cust_name,

　　　　　　cust_state,

　　　　　　(SELECT COUNT(*)

　　　　　　　FROM orders

　　　　　　　WHERE orders.cust_id = customers.cust_id) AS orders

　　FROM customers

　　ORDER BY cust_name;

外层SELECT每次选择一行，对于每一行的cust_id进行一个内存查询，找出在另一个表中为该id的COUNT行数。

相关子查询：涉及外部查询的子查询。

任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

MySQL 3 数据处理函数、汇总数据、分组数据、子查询

标签：圆周率   左右   排序   customers   sub   mon   汇总   price   trim