MYSQL综合查询练习(7/24)

数据:

1.周几赚的最多(dayofweek)

!!!注意: 1=星期日,2=星期一,3=星期二,4=星期三,5=星期四,6=星期五,7=星期六。

DAYOFWEEK() 函数返回给定日期的工作日索引(从 1 到 7 的数字)。

DAYOFWEEK(date)

select dayofweek('2019-06-25');
+----------------------------------+
| dayofweek('2019-06-25 00:00:00') |
+----------------------------------+
|                                3 |
+----------------------------------+

select dayofweek(cast(20190625 as date));
+-----------------------------------+
| dayofweek(CAST(20190625 AS DATE)) |
+-----------------------------------+
|                                 3 |
+-----------------------------------+

CAST()函数,把一个字段转成另一个字段,主要转化的是字段的类型

其语法为:cast(字段名 as 转换的类型 )

            转换的类型共有:            CHAR            字符型

                                      DATE         日期型

                                      DATETIME  日期和时间型

                                      DECIMAL    float型

                                      SIGNED        int型

                                     TIME             时间型

数值:2015-11-03 15:31:26

select cast(date as signed) as date from  table1;
-- 结果:
20151103153126
 
select cast(date as char) as date from  table1;
-- 结果:
2015-11-03 15:31:26
 
select cast(date as datetime) as date from  table1;
-- 结果
2015-11-03 15:31:26
 
select cast(date as date) as date from  table1;
-- 结果
2015-11-03
 
select cast(date as time) as date from  table1;
-- 结果
15:31:26 

select dayofweek(cart.create_time) as 周几, max(cart.num*goods.price)
 as 总价
    ->  from cart,account,goods,category
    ->  where goods.good_no =cart.goods_no and account.id= cart.account_id and category.no = goods.category_no
    ->  group by cart.create_time;

image-20230724212702876

2.购物车哪个货物利润最高(group by 与聚合函数用法)

select goods.good_name,(sum((price-cost)*num))/cost as 利润率

    -> from account,cart,goods,category

    -> where goods.good_no = cart.goods_no

    -> and category.no = goods.category_no

    -> and account.id = cart.account_id

    -> group by goods.good_name,cost;

image-20230724214226467

3.每个用户购物车总额()

select account.name, sum(num*price) as 总额
    -> from account,cart,goods,category
    -> where goods.good_no = cart.goods_no
    -> and category.no = goods.category_no
    -> and account.id = cart.account_id
    -> group by account.name;

image-20230724213329499

4.求2023年3月12日前一周销售的商品(date_add与date_sum)

DATE_ADD(date,INTERVAL expr type)
DATE_SUM(date,INTERVAL expr type)

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

select goods.good_name from cart,goods
    -> where cart.goods_no = goods.good_no
    -> and cart.create_time between date_sub('2023-03-12',interval 1 week) and '2023-03-12';

image-20230724210943253

5.张三的购物时间在什么时候

select create_time
    -> from account,cart,goods,category
    -> where goods.good_no = cart.goods_no
    -> and category.no = goods.category_no
    -> and account.id = cart.account_id
    -> and account.name = '张三' and cart.num > 0;

6一周内哪一天的商品总价最高

SELECT 子句中,你只能选择被 GROUP BY 子句列出的列或使用聚合函数进行计算的列

7.每个商品剩余(group by 与聚合函数)

select goods.good_name,count - sum(num) as 库
存
    -> from account,cart,goods,category
    -> where goods.good_no = cart.goods_no
    -> and category.no = goods.category_no
    -> and account.id = cart.account_id
    -> group by goods.good_name,goods.count;

image-20230724205101692

8.求用户购买完东西后还有多少余额(group by 与聚合函数)

select goods.good_name,count - sum(num) as 库存
    -> from account,cart,goods,category
    -> where goods.good_no = cart.goods_no
    -> and category.no = goods.category_no
    -> and account.id = cart.account_id
    -> group by goods.good_name;

image-20230724205230079