mysql-6 数据检索(4)
2024-10-11 07:50:58
汇总数据
函数 |
说明 |
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 = ;
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 = ;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = ;
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(*)>=;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= GROUP BY vend_id HAVING COUNT(*)>=;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=;
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)>=;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>= ORDER BY ordertotal;
使用子查询
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (,);
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 = ;
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 = ;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = ;
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(*)>=;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= GROUP BY vend_id HAVING COUNT(*)>=;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>= ORDER BY ordertotal;
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (,);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
最新文章
- 关于手机的内置SD卡与外置SD卡
- VS2010中,无法嵌入互操作类型“……”,请改用适用的接口的解决方法(转自网络)
- Deep learning:四十三(用Hessian Free方法训练Deep Network)
- 我的bootstrap使用的历程
- [Unity菜鸟] Final IK
- easyui-treegrid移除树节点出错
- 遍历json创建树状表(首先的前提条件是要引入jquery的jquery treeTable插件)
- http服务详解(1)——一次完整的http服务请求处理过程
- HTML5图片上传本地预览
- Install Centrifugo and quick start
- codeforces——961B. Lecture Sleep
- Netty 客户端断线重连
- Java 后台验证的工具类
- 电脑时间显示秒 win10电脑显示农历
- XMPP使用简介--登录
- 《高性能MySQL》——第五章创建高性能索引
- Redis之数据备份与恢复
- 执行Go程序的三种方式及Go语言关键字
- 复选框的全选+全不选+ajax传递复选框的value值+后台接受复选框默认值
- ZOJ - 2402 DP方案数
热门文章
- [iOS] Delphi for iOS 版本需求
- Oracle 表分组 group by和模糊查询like
- MySQL日期处理(笔记)
- 软件代码生成之Codesmith模板.netTiers
- Fiddler (五) Mac下使用Fiddler
- GJM : 数据结构 - 轻松看懂机器学习十大常用算法 [转载]
- CMS
- linux TCP: time wait bucket table overflow
- ENVI软件操作【数据显示操作——Overlay菜单操作】
- 带给你灵感:30个超棒的 SVG 动画展示【上篇】