mysql汇总函数

2022-12-24 21:48:59

汇总函数

-- 查询 products中最高价格，最低价格是多少？
SELECT max(prod_price), min(prod_price) FROM products;

-- 查询products表中 vendid =1001 相关商品的数量
select vend_id, count(*) from products
WHERE vend_id=1001;

-- 按照 vend_id 进行分组，统计每一个 vend_id 对应的商品数量。
SELECT vend_id,COUNT(*) FROM products
GROUP BY vend_id

-- 按照 vend_id 进行分组，统计每一个 vend_id 对应的商品最大价格，最小价格。
SELECT vend_id, max(prod_price), min(prod_price), COUNT(*),sum(prod_price),AVG(prod_price) FROM products
GROUP BY vend_id;

-- 统计订单表orders中 每个 cust_id 对应的订单数
select cust_id,count(*) FROM orders
GROUP BY cust_id;


-- 统计订单表orders中 2005-09月份的订单总数
select "2005-09" as 月份, count(*) as 订单总量 from orders
WHERE order_date LIKE "2005-09%"

时间函数

-- 返回当前的日期
SELECT CurDate()
-- （1+1）*3 的数学运算
select (1+1)*3
-- 当前时间
SELECT curTime()
-- 年，月
SELECT YEAR(order_date), MONTH(order_date) from orders

-- 合并字符串
SELECT CONCAT("2005","-","09")

-- 合并
SELECT CONCAT(YEAR(order_date),'-',MONTH(order_date)) from orders;

Date_Format() 函数使用

使用方式

select date_format(时间,格式)

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

更多关于 dateformat的使用可以参考： https://www.w3school.com.cn/sql/func_date_format.asp

select NOW();

SELECT DATE_FORMAT(NOW(),'%Y')
SELECT DATE_FORMAT(NOW(),'%y')

-- 2021-08

SELECT DATE_FORMAT(NOW(),'%Y %M')

SELECT DATE_FORMAT(NOW(),'%Y-%m') # 2021-08

练习

-- orders表中每个月的订单数
-- 2005-09 3
-- 2005-10 2

-- 日期 改为 年-月 这样的格式
SELECT DATE_FORMAT(order_date,'%Y-%m') as 日期,COUNT(*) as 订单数 from orders
GROUP BY 日期

-- checkin 表中 每个月考勤人次
-- 2021-07 x 
-- 2021-08 y 

select DATE_FORMAT(checkin_time,'%Y-%m') as 日期, count(*) from checkin
GROUP BY 日期

-- 统计3天之内的所有考勤信息
-- 查询最近3天的考勤信息
SELECT * from checkin
WHERE DATEDIFF(NOW(),checkin_time) <= 3;

-- 计算所有订单的总价值
select sum(quantity*item_price) as "总价值"  FROM orderitems;

-- 计算每个订单总额 分组
-- 20005  x
-- 20006  y
-- ... 

SELECT order_num, sum(quantity*item_price) from orderitems
GROUP BY order_num;

子查询

-- 1. orderitems找到 购买tnt2的订单号
SELECT order_num FROM orderitems
WHERE prod_id = "tnt2"

-- 2. orders 订单表中 根据订单号再订单表中找到对应的 客户id
SELECT cust_id from orders
where order_num in 
	(SELECT order_num FROM orderitems   -- 使用子查询 第一步获取到的结果
		WHERE prod_id = "tnt2")

-- 3 customers 表中根据 cust_id 找到对应的相关人员信息
select * from customers
WHERE cust_id in (
	SELECT cust_id from orders
	where order_num in 
		(SELECT order_num FROM orderitems   -- 使用子查询 第一步获取到的结果
			WHERE prod_id = "tnt2")
)

作业

请在heros 表中进行相关查询；

查询出2016年上架(birthdate)的所有英雄（日期函数）

select * from heros where Year(birthdate) = 2016

role_main 字段进行分组，统计出每组中的英雄数量

select role_main, count(*) FROM heros
GROUP BY role_main

查询主要定位(role_main)或者次要定位(role_assist)是法师或是射手的英雄，同时英雄的上线时间不在 2016-01-01 到 2017-01-01 之间(不包含Null时间)。

select * from heros
WHERE (role_main in("法师","射手") OR role_assist in ("法师","射手"))
AND
(birthdate not BETWEEN "2016-01-01" and "2017-01-01")
AND
birthdate is not null;

上架日期(birthdate)为空的英雄个数；

select COUNT(*) from heros
WHERE birthdate is null;

根据主要定位进行分组(role_main), 分别统计每组英雄中血量值max(hp_max)最高的英雄名字查询结果为

主要角色(role_main)	英雄名字(name)	最大血量(hp_max)

-- 每组中血量的最大值

SELECT role_main,MAX(hp_max) from heros
GROUP BY role_main


-- 将 查询的结果作为临时表

SELECT name, hp_max, role_main from heros,
(SELECT role_main as 主要定位,MAX(hp_max) as 最大血量 from heros GROUP BY role_main) as tmp
WHERE hp_max = 最大血量 AND role_main = 主要定位

使用inner join 方式内连接查询

SELECT name,heros.role_main,hp_max from heros
INNER JOIN (select role_main, max(hp_max) as 最大血量 FROM heros GROUP BY role_main) as hm
ON heros.role_main = hm.role_main and heros.hp_max = 最大血量

根据下面4个表进行操作

customers, orderitems,products,vendors

orderitems 表中单个订单购买商品数量(quantity)超过10个的订单.

SELECT order_num, sum(quantity) from orderitems
GROUP BY order_num
HAVING sum(quantity)> 10

显示customers 表中每个客户的订单总数（书中找到答案）

SELECT cust_name, (select count(cust_id) FROM orders WHERE customers.cust_id = orders.cust_id) from customers

左联方式

SELECT cust_name, 订单数 from customers
LEFT JOIN (SELECT cust_id, COUNT(*) as 订单数 FROM orders  GROUP BY cust_id) as tmp
ON customers.cust_id = tmp.cust_id

客户名称 cust_name	订单总数

查询prod_id为TNT2的供应商名字(vend_name),供应商地址(vend_adrress) （子查询参考https://www.yuque.com/imhelloworld/bypiud/bw2nt4#zK5J7）

select * FROM products
INNER JOIN vendors
ON products.vend_id = vendors.vend_id
WHERE prod_id="tnt2"

统计orderitems表中每个商品的销售金额

商品id （prod_id）	销售数量	销售金额
ANV01
ANV02
....

select prod_id, sum(quantity),sum(quantity*item_price) as total_price FROM orderitems
GROUP BY prod_id

所有已经销售出去的商品当中，销售数量最高的商品名字(prod_name)，商品单价（prod_price）,供应商id(vend_id)

-- 最大销售量

SELECT max(销售量) from (select prod_id, sum(quantity) as 销售量 FROM orderitems GROUP BY prod_id) as t1

-- 根据最大销售量找产品


select prod_id, sum(quantity) as 销售量 FROM orderitems GROUP BY prod_id
HAVING 销售量 = ( SELECT max(销售量) from (select prod_id, sum(quantity) as 销售量 FROM orderitems GROUP BY prod_id) as t1 )





SELECT prod_name, prod_price,销售量 FROM products
INNER JOIN  ( select prod_id, sum(quantity) as 销售量 FROM orderitems GROUP BY prod_id
							HAVING 销售量 = ( SELECT max(销售量) from (select prod_id, sum(quantity) as 销售量 FROM orderitems GROUP BY prod_id) as t1 )) as tmp
ON tmp.prod_id = products.prod_id

码农公寓

汇总函数

时间函数

Date_Format() 函数使用

练习

子查询

作业

相关文章