转义字符
-- '_' 在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 '用户名';
多字段 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();