有用的sql

转义字符

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

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

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

插入查询结果集

-- 查询结果字段必须和插入表字段一致，可以指定部分字段
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

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

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();