常用sql

以下时间均以unix时间戳存储的，为数字
-----------------------------------------------------

//今天所有学生注册记录
SELECT * FROM `student` WHERE regTime > UNIX_TIMESTAMP( curdate() )

 

//2011-04-26以后的订单
SELECT `order_id` FROM gd_order_info WHERE `add_time` > UNIX_TIMESTAMP('2011-04-26');

 

//订单提交时间
SELECT FROM_UNIXTIME(add_time) AS `time`  FROM gd_order_info where id=1

 

//今日注册的用户，显示注册时间为标准格式
SELECT FROM_UNIXTIME(reg_time) AS '注册时间' FROM gd_users WHERE reg_time > UNIX_TIMESTAMP( CURDATE())

 

 ### 如果字段warehouse_id为空，则值等于16，否则在原值基础上再串一个字符串",16"
### mysql下连接字符串的函数CONCAT()
UPDATE `db_fojiaoyongpin`.`ecs_admin_user`
SET warehouse_id = CASE WHEN warehouse_id = '' THEN '16' ELSE CONCAT(warehouse_id, ',16')END
WHERE user_id IN('12','40')


#判断id=16是否在字符串中（该字符串是以英文半角逗号分隔的多个ID）
SELECT user_id, user_name, CASE WHEN (warehouse_id = '16' OR warehouse_id LIKE '16,%' OR warehouse_id LIKE '%,16,%' OR warehouse_id LIKE '%,16') THEN 'this' WHEN warehouse_id = '' THEN 'free' ELSE 'other' END AS TYPE FROM `db_fojiaoyongpin`.`ecs_admin_user` WHERE action_list <> 'all'

-----------------------------------------------------