汇总函数

-- 查询 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(时间,格式)

更多关于 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 表中进行相关查询；

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

select * from heros where Year(birthdate) = 2016

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

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

3. 查询主要定位(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;

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

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

5. 根据主要定位进行分组(role_main), 分别统计每组英雄中血量值max(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

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

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

2. 显示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

3. 查询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"

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

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

5. 所有已经销售出去的商品当中，销售数量最高的商品名字(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