CASE WHEN 小结

在进行sql脚本编写时,已跟踪的在跟踪表内有记录,没有跟踪的对应的跟踪表的跟踪记录为null,尝试编写sql脚本:当将track.is_tracking写在case 与 when 之间时,当track.is_tracking为null时候不生效,在这个case when 中还用了and

11. 数据集合的 并集、差集、交集 !

UNION / EXCEPT / INTERSECT  [ALL]  
        (CASE
        WHEN  track.is_tracking is null and node1.created_date>orr.KPI_arrive_time THEN '超时未跟踪'
        WHEN track.is_tracking is not null and node1.created_date>orr.KPI_arrive_time THEN '超时已跟踪'    
        else '未超时' 
        END)isTracking,

2. 删除存储过程

drop procedure [if exists] spname  

2、 case when 在一整个表为空强行让其显示出一个值,在其后加上一个count(1),强行让其显示一行,不然一整个表为空时,case when 做判断不生效

三、 函数

修改,去掉case when 之间的track.is_tracking,null生效

14. 对于非必须提供的参数,而且又要进行精确匹配的,可以 使用 like 来处理

if (argtitle is null || argtitle='') then
   set argtitle='%';
else 
  -- 首先对参数要进行转义处理
  set argtitle=replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_');
  -- 如果是自己拼接字符串,还需要对 ' 进行转义
  -- set argtitle=replace(replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_'),''','\'');
  -- 然后就不需要在加 %% 了,因为要精确匹配
  -- set argtitle=concat('%',argtitle,'%')
end if;

函数示例

(1) 转成精确搜索字符串

CREATE DEFINER=`sa`@`%` FUNCTION `accurateSearchStr` (
  param varchar(4096)
)
RETURNS varchar(4096) CHARSET utf8
DETERMINISTIC
BEGIN
  DECLARE retVal varchar(4096) DEFAULT null;
 if (param  is null || param ='') then
   set retVal ='%';
else 
  -- 首先对参数要进行转义处理
  set retVal =replace(replace(replace(param,'\','\\'),'%','%'),'_','_');
end if;
RETURN retVal;
END

(2) 转成精确搜索字符串ForStmt

CREATE DEFINER=`sa`@`%` FUNCTION `accurateSearchStrForStmt` (
  param varchar(4096)
)
RETURNS varchar(4096) CHARSET utf8
DETERMINISTIC
BEGIN
  DECLARE retVal varchar(4096) DEFAULT null;
 if (param  is null || param ='') then
   set retVal ='%';
else 
  -- 如果是自己拼接字符串,还需要对 ' 进行转义
  set retVal =replace(replace(replace(replace(param,'\','\\'),'%','%'),'_','_'),''','\'');
end if;
RETURN retVal;
END

(3) 转成模糊搜索字符串

CREATE DEFINER=`sa`@`%` FUNCTION `fuzzySearchStr` (
  param varchar(4096)
)
RETURNS varchar(4096) CHARSET utf8
DETERMINISTIC
BEGIN
  DECLARE retVal varchar(4096) DEFAULT null;
 if (param  is null || param ='') then
   set retVal ='%';
else 
  -- 首先对参数要进行转义处理
  set retVal =replace(replace(replace(param,'\','\\'),'%','%'),'_','_');
  set retVal =concat('%',retVal ,'%');
end if;
RETURN retVal;
END

(4) 转成模糊搜索字符串ForStmt

CREATE DEFINER=`sa`@`%` FUNCTION `fuzzySearchStrForStmt` (
  param varchar(4096)
)
RETURNS varchar(4096) CHARSET utf8
DETERMINISTIC
BEGIN
  DECLARE retVal varchar(4096) DEFAULT null;
 if (param  is null || param ='') then
   set retVal ='%';
else 
  -- 如果是自己拼接字符串,还需要对 ' 进行转义
  set retVal =replace(replace(replace(replace(param,'\','\\'),'%','%'),'_','_'),''','\'');
  set retVal =concat('%',retVal ,'%');
end if;
RETURN retVal;
END

超时状态

计算日期之间的间隔
TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 12:00:00');

这里不得不说 TIMESTAMPDIFF 的一个 坑 ,

计算两个日期之间相差的月数时,使用函数 TIMESTAMPDIFF( month,@date1,@date2)
经过验证,这个函数首先会选出一个较小的日期作为 比较基数,这里记为 @datediff

  • 首先要注意 即使 @date1 > @date2 , 但是如果它们之间相差的天数 小于 @datediff 所在月的天数, 其结果仍然为 0
  • 其次因为计算相差月数时 它是以 @datediff 作为 比较基数,即结果是根据 判断 两个日期之间间隔的天数是否大于 @datediff 所在月的天数 来计算的,所以
-- 因为1月31号到2月28号 显然不够31天 ,所以是 false
select timestampdiff(month,'2017-1-31','2017-2-28')=0 
-- 因为1月1号到2月1号 是31天 ,所以是 true
select timestampdiff(month,'2017-1-1','2017-2-1')=1 

所以要计算两个日期之间相隔的月时, 使用第1天进行比较! 血的教训!

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

3. 日期格式化

DATE_FORMAT(NOW(),'%Y/%m/%d %T') 

 图片 1

4. 时间和时间戳之间的转换

unix_timestamp(NOW()) || from_unixtime('141556720.243') 

 图片 2

6. 给变量赋值

set var_name=expr;   select col1,col2 into var1,var2;  

1、简单的一个case when 例子:

3. 显示所有的存储过程

show procedure status  

3、在 做一个订单时效,订单时效需要用KPI时间与实际到达时间进行比较,且有三个状态:

编写SQL语句相关

     (CASE track.is_tracking
        WHEN  track.is_tracking is null and node1.created_date>orr.KPI_arrive_time THEN '超时未跟踪'
        WHEN track.is_tracking is not null and node1.created_date>orr.KPI_arrive_time THEN '超时已跟踪'    
        else '未超时' 
        END)isTracking,

2. 坑

  • 2.1 return的类型为varchar(128)时,要注意实际返回的内容的长度是否可能会超过这个值
  • 2.2 函数运行时如果出错好像不会报错! 除非使用的是 select into !!
  • 2.3 不论是存储过程还是函数,使用子查询返回单个结果进行赋值时,一定要注意看是否需要使用 distinct 、 limit 0,1 进行修饰,避免返回多个结果!

 

12. 对于int类型的参数,不要使用 arg1='' 进行判断!!

对于int型的参数:

  • (1) 如果传入的参数是非整数值,存储过程会直接报参数类型错误,所以你不用判断 arg1=''
  • (2) 如果传入的参数是null,存储过程不报错,所以你需要使用 arg1 is null 进行判断

那为什么不可以使用 arg1='' 进行判断呢? 因为 arg1=0时, arg1=''为true!!即你可以试试 select 0='' 返回1

SELECT CASE WHEN log_minute_30 IS NULL THEN 0 ELSE (log_minute_30) END, count(1) FROM call_count cc WHERE cc.log_date='2016-09-17' AND cc.log_hour='19' AND cc.cname='A'

5. 连接字符串

  • concat中的参数如果有null,那么返回结果就是null,所以必要时需要使用ifnull、if(isnull()) 等函数来规避风险
  • 聚合函数group_concat会忽略null值,这点和concat不同
  • 另外 concat_ws('separator','a',null,'b') 也会忽略null值
* 是否大于KPI所需要的到达时间
* 1、大于(未跟踪)
* 2、大于(已跟踪)
* 3、小于

4. loop

loop1:LOOP
 set i=i+1;
 if i>30 then
   leave loop1;
 end if;
end LOOP;

17. UUID

select uuid()
select replace(uuid(),'-','')

13. 模糊搜索处理:

if (argtitle is null || argtitle='') then
   set argtitle='%';
else 
  -- 首先对参数要进行转义处理
  set argtitle=replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_');
  -- 如果是自己拼接字符串,还需要对 ' 进行转义
  -- set argtitle=replace(replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_'),''','\'');
  -- 然后如果是模糊搜索,需要前后补上 %
  set argtitle=concat('%',argtitle,'%');
end if;

当然还要注意如果字段内容可能为 null , 那么使用like进行匹配时还要注意下这种情况,可以使用 ifnull(field,'') like argtitle
还要注意: like 不区分大小写!!
其实不是like不区分大小写! 你使用 'a' = 'A' 一样返回true
这个行为是由 column的 Collation 决定的,
*_bin 表示 binary case sensitive collation,也就是说是区分大小写的
*_cs 表示 case sensitive collation,区分大小写
*_ci 表示 case insensitive collation,不区分大小写
collate默认应该是 utf_general_ci , 所以 like 才不区分大小写
要想 like 区分大小写,请使用 binary 修饰符, 例如
columnA like binary 'value' 或者 binary columnA like 'value'

*_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的

*_cs: case sensitive collation,区分大小写

*_ci: case insensitive collation,不区分大小写

4.1. 关于日期和时间的函数
-- 获取当前日期
select curdate(); 

-- 获取本月第一天
select DATE_ADD(curdate(),interval -day(curdate())+1 day) 

-- 获取当月最后一天
select last_day(curdate()); 

-- 获取下个月的第一天
select date_add(curdate()-day(curdate())+1,interval 1 month ) 

3. repeat

repeat 
  ...
  set i=i+1;
until i>=20 END REPEAT

5. 声明变量,其他类型 FLOAT、DOUBLE、VARCHAR、DATE等

declare a INT [default 100]  

21. 子查询中的join

子查询中的join中的on条件不能有上级表中的字段,必须把那些条件放到where中, 例如

select *
from A
where exists (
  select * 
  from B 
  join C on B.xx=C.xx and B.yy=A.zz
)

以上语句不合法,会报错误: 找不到 A.yy , 应该是 mysql 认为 join中的on中的条件 代表参与jion的表格,而A显然不是参与join的表格
修改为如下就没有问题: 在where中的条件中,A.zz代表一个值,可以和B.yy进行比较

select *
from A
where exists (
  select * 
  from B 
  join C on B.xx=C.xx 
  where  B.yy=A.zz
)

2. 显示某个数据库中的所有表

show tables from someDatabase;

20. join时条件放在 where 还是 on 里

例如

select * 
from A
join B on A.xx=B.xx
where A.yy='xxx'
-- 还是下面的写法?
select * 
from A
join B on A.xx=B.xx and A.yy='xxx'

有些文章说第二种写法好一些,因为mysql会做查询优化 ( 这应该不是优化不优化的问题,而是显然的放在on里更好一些,第一种是先join后筛选,第二种是先筛选后join,如果mysql没有做优化的话,那么显然第二种效率更高. )
但是实际使用过程中,还是要看其他一些情况来决定如何写
如果你习惯了使用第二种写法(在on中写筛选条件),一定要注意 使用left join的时候 可能产生的误区
例如

select * 
from A
left join B on A.xx=B.xx and A.yy='xxx'

这样写的效果是 A表中所有的记录都会在左侧显示出来,而不是筛选 A.yy='xxx' 之后的结果

1. 显示所有数据库

有的系统数据库只能通过这个命令显示出来

show databases; 

15. 使用旧表创建新表

create table tab_new like tab_old 
create table tab_new as select c1,c2 from tab_old where 1<>1
create table tab_new as select c1,c2 from tab_old

11. 存储过程中有使用了 ONLY_FULL_GROUP_BY 不允许的语法时别忘了

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

15. 自己拼接字符串时防注入处理

例如你的sql语句可能是如下所示,需要对 argtitle 进行精确或模糊搜索

set @sql=concat('select * from xx where title like '',argtitle,''');

那么需要对 argtitle 先进行安全字符替换

if (argtitle is null || argtitle='') then
   set argtitle='%';
else 
  -- 首先对参数要进行转义处理
  set argtitle=replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_');
  -- 如果是自己拼接字符串,还需要对 ' 进行转义
  -- set argtitle=replace(replace(replace(replace(argtitle,'\','\\'),'%','%'),'_','_'),''','\'');
  -- 然后如果是模糊搜索,需要前后补上 %
  set argtitle=concat('%',argtitle,'%');
end if;

9. 异常处理

  • (1) 为一个error声明一个别名,将这个别名与错误连接起来,相当于给错误代码起一个友好的别名供异常处理使用。
declare conditon_name condition for {SQLSTATE sqlstate_code | MYSQL_ERROR_CODE};

系统有内置的错误别名,如 SQLEXCEPTION,SQLWARNING和NOT FOUND
例子:

DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
  -- body of handler
END;
  • (2) 声明handler
DECLARE {continue|exit|undo} HANDLER FOR 
{error-number | SQLSTATE error-string | condition},...

  statement

发生错误的条件可以是:

  • MYSQL错误代码
  • ANSI-standard SQLSTATE code
  • 命名条件。可使用系统内置的 SQLEXCEPTION,SQLWARNING和NOT FOUND

例子:

-- 当错误代码为1062时将duplicate_key的值设为1,并继续执行当前任务
declare continue handler for 1062 set duplicate_key=1;
-- 跟上面一样,只是使用的条件为ANSI标准错误代码
declare continue handler for sqlstate '23000' set duplicate_key=1;
-- 当发生SQLEXCEPTION时,将L_error设为1,并继续
declare continue handler for SQLEXCEPTION set L_error=1;

优先级:当同时使用MYSQL错误码,标准SQLSTATE错误码,命名条件(SQLEXCEPTION)来定义错误处理时,
其捕获顺序是(只捕获一条错误):MYSQL码->SQLSTATE->命名条件

作用域:声明handler的begin...end块及其子块内

2. while

set i = 1;
while i<11 do
  ...
  set i=i+1;
end while;

本文由金沙官网线上发布于数据库,转载请注明出处:CASE WHEN 小结

您可能还会对下面的文章感兴趣: