mysql基本操作

cooolr 于 2022-09-17 发布
// cast(value as type)

// 数字转字符
select cast(10 as char)

// 字符转数字
select cast("10" as SIGNED)
select concat("10", "/", "20")
Date        Value  Count
2021-01-28  1      1000
2021-01-28  2      2000
2021-01-28  1      3000
2021-01-28  2      4000
select 
    date,
    max(case Value when "1" then Count else 0 end),
    max(case Value when "2" then Count else 0 end)
    from table
group by date
// 从左到右
select left("hello world", 5)

// 从右到左
select right("hello world", 5)

// 从第几位开始截取几位
select substring("hello world", 6, 5)

// 按关键字截取几位
select substring_index("hello world", " ", 1)
if(member_id,"已登陆","未登录")

// 字段判断
IF(is_hot = b'1', 1,0) AS is_hot

天数语句

create_time > DATE_SUB(now(),INTERVAL 5 day)

分钟间隔

where create_time>(current_timestamp - INTERVAL 60 MINUTE);

update_time自动使用当前时间

alter table game_ranking
	add update_time timestamp default CURRENT_TIMESTAMP not null comment '更新时间';

插入并更新

insert into correlation(news_id, news_type, update_time) 
    values({news_id}, {news_type}, '{ctime}') 
on duplicate key update news_id=values(news_id),news_type=values(news_type),update_time=values(update_time)

查询重复记录

# 查询重复记录
select * from dapp 
where dapp_name in (select dapp_name from dapp group by dapp_name having count(*)>1)

# 保留一条
delete dapp where chain_name not in (select max())

临时表

断开连接自动删除临时表

CREATE TEMPORARY TABLE 临时表名 AS
(
    SELECT *  FROM 旧的表名
    LIMIT 0,10000
);