汇总函数

-- 查询 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)最高的英雄名字 查询结果为 | 主要角色(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

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

   | 客户名称 cust_name | 订单总数 | | —- | —- | | | | | | |

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表中 每个商品的销售金额 | 商品id （prod_id） | 销售数量 | 销售金额 | | —- | —- | —- | | ANV01 | | | | ANV02 | | | | …. | | |

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

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

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 ```