MySQL常用sql,10进制转16进制,数据拆分,数据合并,数据去重,数据比较判断

将table表里面code相同的数据删除,保留id最小的一条数据

DELETE
FROM table
WHERE CODE IN (SELECT * FROM (
SELECT CODE FROM table GROUP BY CODE HAVING count(CODE) > 1 ) b
) and id NOT IN (
SELECT * FROM (SELECT min(id) FROM table GROUP BY CODE HAVING count(CODE) > 1) c
);

转换为小写：

UPDATE 表名 SET 列名= lower( 列名 );

如果转换为大写：

UPDATE 表名 SET 列名= UCASE( 列名 );

将10进制的数据转换成16进制保存

UPDATE table SET CODE = HEX(CONVERT(CODE,UNSIGNED ))

查询数据将10进制的数据转换成16进制

SELECT
HEX(CONVERT(CODE ,UNSIGNED)) AS CODE
FROM `tabel’

当主键重复的时候更新,不重复的时候插入

1. REPLACE INTO stuInfo (name,birthday,is_deleted) VALUES (‘yoona’,‘1990-01-15’,0),(‘yoona’,‘1990-02-16’,0),(‘aa’,‘1990-01-13’,0);
   使用replace当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入。如果使用的是主键自增id,id的值没有设定,就会发生改变
2. INSERT IGNORE INTO
   插入时检索主键列表，如存在相同主键记录，不更改原纪录，只插入新的记录。
3. INSERT INTO … ON DUPLICATE KEY UPDATE
   插入时如果发现主键已存在，则执行UPDATE更新操作
   insert into device values (1,‘readonly’,‘yang’) ON DUPLICATE KEY UPDATE status =‘drain’;
   翻译过来就是：插入一条数据（1,‘readonly’,‘yang’），当有重复的主键KEY，那就更新一下status吧。

多列数据合并成一列

group_concat和concat的用法

1. group_concat
   select name ,group_concat(type,‘分数为:’,score Separator '; ') as score from stu group by name
   
2. concat
   select name ,concat(name ,‘的分数为[’,group_concat(type,‘分数为:’,score Separator ‘; ‘),’]’) as score from stu group by
   

拆分数据

1. 水平拆分
   
   SELECT id, house_no as ‘总称’,
   SUBSTR(house_no, 1, 4) AS ‘小区名称’,
   SUBSTR(house_no, -10, 3) AS ‘楼幢’,
   SUBSTR(house_no, 5, 3) AS ‘楼幢2’,
   SUBSTR(house_no, -7,3) AS ‘单元’,
   SUBSTRING(house_no,-4) AS ‘房间号’
   FROM aa;
   
   2. 垂直拆分
   
   select a.house_no as ‘房子’,substring_index(substring_index(a.name,’,’,b.help_topic_id+1),’,’,-1) as ‘拥有者’
   from aa a
   join mysql.help_topic b on b.help_topic_id < (length(a.name) - length(replace(a.name,’,’,’’))+1);
   

数据比较判断

CASE p.VALID_FLAG WHEN ‘ENABLE’ THEN ‘0’ ELSE ‘1’ END AS ‘别名’
当p.VALID_FLAG = ‘ENABLE’ 替换成0代表的值否则替换成1代表的值
当不添加ELSE时,填充的值为null

SELECT
case -------------如果
when sex=‘1’ then ‘男’ -------------sex=‘1’，则返回值’男’
when sex=‘2’ then ‘女’ -------------sex=‘2’，则返回值’女’
else 0 -------------其他的返回’其他’
end -------------结束
from sys_user --------整体理解： 在sys_user表中如果sex=‘1’，则返回值’男’如果sex=‘2’，则返回值’女’ 否则返回’其他’
或者
SELECT STATE
CASE WHEN ‘1’ THEN ‘成功’
WHEN ‘2’ THEN ‘失败’
ELSE ‘其他’ END
FROM SYS_SCHEDULER

欢迎各位大佬给出意见