SQL碎片

删除字段中的最后几位字符

update exam_info set title=substr(title,1,char_length(title)-6)

length与char_length

两个都是用来获取字符串长度的函数

其中length在utf-8编码下，一个汉字是算三个字符,一个数字或字母算一个字符，在 gbk下一个汉字是两个字符，一数字或字母算一个字符

而char_length 不管在什么编码格式下，一个汉字或字母、数字都算一个字符。

substr(str,pos,len)函数——在str中由pos的位置开始的len个字符

pos为正数，在字符串指定的位置开始，第一个字符串为1

pos为负数，在字符串结束的位置开始，最后一个字符串为-1

pos或len为0，返回结果为空

len缺省时，默认截取到字符串最后

在字段最后添加字符

update exam_info set title=concat(title,'No.',id)

删除没有题目的选项

delete from exam_option where info_id in (select INFO_ID from (select a.id,b.INFO_ID from exam_info as a right join exam_option as b on a.id=b.info_id ) as c where c.id is null)

删除没有答案的题目

select * from exam_info where id in (select info_id from (select sum(IS_ANSWER) as c,b.INFO_ID from exam_info as a ,exam_option as b where  a.id=b.INFO_ID  group by INFO_ID) as d where d.c=0)

select sum(case when IS_ANSWER=1 then 1 else 0 end) total,b.INFO_ID from exam_info as a ,exam_option as b where  a.id=b.INFO_ID group by INFO_ID order by info_id

case when then else end

select sum(popu) ,(case country when 'china' then 'Asia'

when 'America' then 'North America'

when 'Canada' then 'Asia'

else 'qita' end )continent from test  GROUP BY continent