Mysql遇到的问题

1.如何查询空字段

SELECT	* FROM USER WHERE id =''   x

SELECT	* FROM USER WHERE id =NULL   x

正解

SELECT	* FROM USER WHERE	ISNULL(id)

2.如何使用关键字(必要情况下)

SELECT * from `condition`= 1

mybatis-plus指定必需加上``

  @TableField("`condition`")
    private String condition;

查询某一年12个月里各个月份的数据 以2022年为例

SELECT
	SUM( CASE MONTH ( o.create_time ) WHEN '1' THEN order_amount ELSE 0 END ) AS 1月,
	SUM( CASE MONTH ( o.create_time ) WHEN '2' THEN order_amount ELSE 0 END ) AS 2月,
	SUM( CASE MONTH ( o.create_time ) WHEN '3' THEN order_amount ELSE 0 END ) AS 3月,
	SUM( CASE MONTH ( o.create_time ) WHEN '4' THEN order_amount ELSE 0 END ) AS 4月,
	SUM( CASE MONTH ( o.create_time ) WHEN '5' THEN order_amount ELSE 0 END ) AS 5月,
	SUM( CASE MONTH ( o.create_time ) WHEN '6' THEN order_amount ELSE 0 END ) AS 6月,
	SUM( CASE MONTH ( o.create_time ) WHEN '7' THEN order_amount ELSE 0 END ) AS 7月,
	SUM( CASE MONTH ( o.create_time ) WHEN '8' THEN order_amount ELSE 0 END ) AS 8月,
	SUM( CASE MONTH ( o.create_time ) WHEN '9' THEN order_amount ELSE 0 END ) AS 9月,
	SUM( CASE MONTH ( o.create_time ) WHEN '10' THEN order_amount ELSE 0 END ) AS 10月,
	SUM( CASE MONTH ( o.create_time ) WHEN '11' THEN order_amount ELSE 0 END ) AS 11月,
	SUM( CASE MONTH ( o.create_time ) WHEN '12' THEN order_amount ELSE 0 END ) AS 12月 
FROM
	orders o 
WHERE
	YEAR ( o.create_time ) = 2022

结果

查询某一年12个月里各个月份的数据总数 以2022年为例

SELECT
	sum( CASE MONTH ( o.create_time ) WHEN '1' THEN 1 ELSE 0 END ) AS '1月',
	sum( CASE MONTH ( o.create_time ) WHEN '2' THEN 1 ELSE 0 END ) AS '2月',
	sum( CASE MONTH ( o.create_time ) WHEN '3' THEN 1 ELSE 0 END ) AS '3月',
	sum( CASE MONTH ( o.create_time ) WHEN '4' THEN 1 ELSE 0 END ) AS '4月',
	sum( CASE MONTH ( o.create_time ) WHEN '5' THEN 1 ELSE 0 END ) AS '5月',
	sum( CASE MONTH ( o.create_time ) WHEN '6' THEN 1 ELSE 0 END ) AS '6月',
	sum( CASE MONTH ( o.create_time ) WHEN '7' THEN 1 ELSE 0 END ) AS '7月',
	sum( CASE MONTH ( o.create_time ) WHEN '8' THEN 1 ELSE 0 END ) AS '8月',
	sum( CASE MONTH ( o.create_time ) WHEN '9' THEN 1 ELSE 0 END ) AS '9月',
	sum( CASE MONTH ( o.create_time ) WHEN '10' THEN 1 ELSE 0 END ) AS '10月',
	sum( CASE MONTH ( o.create_time ) WHEN '11' THEN 1 ELSE 0 END ) AS '11月',
	sum( CASE MONTH ( o.create_time ) WHEN '12' THEN 1 ELSE 0 END ) AS '12月' 
FROM
	orders o 
WHERE
	YEAR ( o.create_time )= '2022'

结果

数据表

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `uid` bigint DEFAULT NULL COMMENT '用户id',
  `goods_id` bigint DEFAULT NULL COMMENT '商品id',
  `goods_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
  `goods_thumb` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片地址',
  `discount_price` decimal(10,2) DEFAULT NULL COMMENT '团购价格',
  `goods_price` decimal(10,2) DEFAULT NULL COMMENT '商品价格',
  `discount` decimal(10,2) DEFAULT '0.00' COMMENT '优惠额',
  `buyer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '购买者',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `store_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '店铺名称',
  `order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  `goods_receiving_information` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货信息',
  `order_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '2' COMMENT '订单状态 1:待付款 2:待备货 3:备货中4:配送中 5:待提货 6.已提货(交易完成) 7.退货 8.退款 0:已关闭',
  `audit_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '审核状态 1:待审核  2:审核中 3:通过 4:不予通过',
  `reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '原因',
  `reason_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片说明',
  `apply_time` datetime DEFAULT NULL COMMENT '申请时间',
  `order_remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单备注',
  `purchasing_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '下单时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `return_time` datetime DEFAULT NULL COMMENT '退还时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='订单表';