首页 > 代码库 > mysql-6 数据检索(4)
mysql-6 数据检索(4)
汇总数据
函数 |
说明 |
AVG() |
返回某列的平均数 |
COUNT() |
返回某列的行数 |
MAX() |
返回某列的最大值 |
MIN() |
返回某列的最小值 |
SUM() |
返回某列值的和 |
1、AVG函数
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
2、count()函数
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
这条select语句使用count(cust_email)对cust_email列中有值的进行计数,在此例子中,cust_email的计算式3次,表示5个客户中只有三个客户有电子邮件
3、max()函数
SELECT MAX(prod_price) AS max_price FROM products;
4、min()函数
SELECT MIN(prod_price) AS min_price FROM products;
5、sum()函数
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;
分组数据 group by
SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;
order by 1、排序产生的输出 2、任意列都可以使用 3、不一定需要
group by 1、分组行,但输出可能不是分组的顺序 2、只可能使用选择列或表达式列,而且必须使用每个选择列表达式 3、如果与聚集函数一起使用列,则必须使用
where过滤行,having过滤组
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;
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 order_num FROM orderitems WHERE prod_id = ‘TNT2‘; SELECT cust_id FROM orders WHERE order_num IN (20005,20007); SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘);
SELECT AVG(prod_price) *AS avg_price FROM products; SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003; SELECT COUNT(*) AS num_cust FROM customers; SELECT COUNT(cust_email) AS num_cust FROM customers; SELECT MAX(prod_price) AS max_price FROM products; SELECT MIN(prod_price) AS min_price FROM products; SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005; SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005; SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products; SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id; SELECT cust_id FROM orders ; SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2; SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2; SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2; SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50; 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 order_num FROM orderitems WHERE prod_id = ‘TNT2‘; SELECT cust_id FROM orders WHERE order_num IN (20005,20007); SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘);
mysql-6 数据检索(4)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。