- 类型转换
// 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判断
IF(expr1,expr2,expr3) -
如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。
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
);