有用的sql

转义字符

-- '_' 在mysql中是表示任一字符，下面查的是名字等于 *Aaaa 的
SELECT * FROM USER WHERE NAME = '_Aaaa';

-- 如果就要查名字是 _Aaaa 的人需要使用转移字符，把_当成普通字符
SELECT * FROM USER WHERE NAME = '\_Aaaa';

-- 或者自定义字符
SELECT * FROM USER WHERE NAME = '$_Aaaa' ESCAPE '$';

字段调整

-- 增加字段
ALTER TABLE 表名 ADD 字段名 varchar(20) [COMMENT '注释内容'];

-- eg1 增加一个字段，默认为空
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL;
-- eg2 增加一个字段，默认不能为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL; 
-- eg3 字段已经存在了，只是增加注释，类型不能省略
alter table user modify name varchar(20) comment '用户名';

-- 修改字段长度
ALTER TABLE table MODIFY your_column VARCHAR(100);

多字段 IN

SELECT id, toc_order_no, toc_order_line_no 
FROM dm_demand_order_line_toc 
WHERE dr = 0 
	AND ( toc_order_no, toc_order_line_no ) IN (
		                                        ( 'N2404000012', '1371748181548054539' ),
		                                        ( 'N2404000012', '1371748181548054540' )
	)

插入查询结果集

-- 查询结果字段必须和插入表字段一致，可以指定部分字段
INSERT INTO emp2
    SELECT *
    FROM employees

-- 指定字段
INSERT INTO emp2(id, code, create_time)
    SELECT id, code, create_time
    FROM employees
    WHERE department_id = 90;

时间查询

-- 获取当前系统年月是时分秒
SELECT 
    YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), 
    HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) 
FROM DUAL

-- 查询当前是一年中第几天，是一年中第几周，月份名称等等
SELECT 
    MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY(now()), 
    QUARTER(CURDATE()),WEEK(CURDATE()),
    DAYOFYEAR(NOW()), DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) 
FROM DUAL;

联表更新

-- test1 的 name 设置为 test2 的 name（两表 code 相等，单字段）
update test1 
set name = (select name from test2 where test2.code=test1.code)

-- test1 的 addr 和 num 设置为 test2 的 addr 和 num（两表 name 和 age 相等，多字段）
update test1 a 
inner join (select * from test2) b on a.name = b.name and a.age = b.age 
set a.addr = b.addr, a.num = b.num

case when

SELECT
	record_no 流水号,
	order_no 订单编号,
	sap_no sap 单据号,
CASE 
    WHEN business_type > 16 THEN '不可能' 
    WHEN business_type = 2 THEN '订单取消' 
    WHEN business_type = 5 THEN '支付翻新费'
    WHEN business_type = 16 THEN '财务调整' 
    WHEN business_type = 1 THEN '订单支付'
    WHEN business_type = 4 THEN '取消发货' 
    WHEN business_type = 3 THEN '支付运费' 
    ELSE business_type
END '类型',
    quota 金额,
    dealer_code 经销商编码,
    create_time 创建时间,
    update_time 更新时间
FROM
    c_credit_ext_record 
WHERE
    org_code IS NULL 
    AND dealer_code IN ( '13869', '13844' ) 
    AND dr = 0;

存储过程-遍历查询结果集并处理

DELIMITER $$
-- 存储过程名称，不带参数
CREATE PROCEDURE repairCurrentDayStatistics()
BEGIN
    -- 声明变量
    DECLARE rst int DEFAULT 0;
    DECLARE shopCode varchar(255);
    DECLARE stockId bigint;
    DECLARE countDate datetime;
    
    -- 声明游标：游标存储的内容为 select 结果集
    DECLARE record CURSOR FOR select shop_code, stock_id, count_date from it_stock_statistics_tob;
    -- 循环结束时，更改变量 rst 设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET rst = 1;
    -- 打开游标
    OPEN record;
    -- 游标内容赋值给变量
    FETCH record INTO shopCode, stockId, countDate;
    -- 循环开始，当 rst 不等于 1 才进入循环体
    WHILE rst <> 1 DO
      -- 具体业务操作
      update it_stock_statistics_tob a 
      inner join (
                  select 
                         r.shop_code, r.stock_id, r.countDate, 
                         r.sp, r.spw, r.ss, r.ssw 
                  from (select shop_code, stock_id, countDate, sum(purchase_size) sp, sum(purchase_weight) spw, sum(sale_size) ss, sum(sale_weight) ssw 
                        from it_stock_statistics_tob 
                        where shop_code = shopCode and stock_id = stockId and count_date <= countDate
                       ) r
                  ) b 
      on a.shop_code = b.shop_code and a.stock_id = b.stock_id and a.count_date = b.countDate 
      set a.total_purchase_size = b.sp, a.total_purchase_weight = b.spw, a.total_sale_size = b.ss, a.total_sale_weight = b.ssw;
      -- 业务操作结束，再把一个游标内容赋值给变量
      FETCH record INTO shopCode, stockId, countDate;
    -- 循环结束
    END WHILE;
    -- 关闭游标
    CLOSE record;
END$$
DELIMITER ;

-- 调用存储过程
CALL repairCurrentDayStatistics();