• 0
  • 0

MYSQL 常用SQL语句

2020-03-23 898 0 admin 所属分类:Mysql

拼接字段返回字符串

SELECT GROUP_CONCAT(field_id) FROM your_table

NOT EXISTS 子查询筛选目标记录集 

SELECT * FROM `main_table` a WHERE NOT EXISTS(SELECT id FROM `sub_table` WHERE id=a.tid and `day`='20200517') LIMIT 50

find_in_set()函数使用

select * from article where FIND_IN_SET('4',type)

使用时间戳赚日期函数 方便分组  from_unixtime 第二个参数是转换格式

"SELECTdateline,from_unixtime(dateline,'%Y%m') as `date`,count(*) as num FROM threads WHERE  dateline between {$start} and {$end} GROUP BY `date` "

将数据库字段数值转换为绝对值 

SELECT abs(price) as abs_price,price,count(*) as num FROM threads WHERE dateline between {$start} and {$end}  GROUP BY `abs_price` HAVING abs_price between  {$start_reward} and {$end_reward}"

由于 where 执行条件在前 因此 通过 as 别名生成的临时字段无法在where中使用  如果真的需要使用该字段做筛查 可以如下

1. 使用子查询  

2.分组后结合 having 查询 


查看 表结构

desc table_xxx
show create table table_xxx

列举符合条件的数据表

show TABLES  like 'table_%'


返回顶部