常用的一些sql基础语句汇总

（1）IN查询

SELECT  cat_id,cat_name from syscategory_cat where 1=1 and disabled = 0 and cat_id in ({$ids}) order by field(cat_id,{$ids});

SELECT goods_id,goods_name,goods_price,goods_image,store_id from 33hao_goods where goods_id in (100591,100592,100153,100173,100142,100178,100174);

SELECT goods_id,goods_name,goods_price,goods_image,store_id FROM 33hao_goods WHERE goods_id IN (100591,100592,100153,100173,100142,100178,100174) ORDER BY FIND_IN_SET(goods_id,'100591,100592,100153,100173,100142,100178,100174');//按顺序查询

贴图：

SELECT id,HASH,path,STATUS FROM pic_list WHERE id IN (729,729,724) ORDER BY find_in_set(id, '729,729,724')//去重

（2）联合查询

SELECT card.id,card.card_sn FROM card_baseinfo AS card LEFT JOIN seller_list AS sell ON card.seller_id = sell.id WHERE card.request_id = 14 AND sell.`status` = 0;//左查询

（3）时间范围

SELECT * FROM card_create_request WHERE 1 = 1 AND cdate >= '2017/01/10 00:00:00 ' AND cdate <= '2017/01/10 23:59:59 ' ORDER BY id DESC LIMIT 0,10;

SELECT op_date FROM member_qiandao_log WHERE member_id = 21 AND op_date >= 20160117 AND op_date <= 20160117

（4）动态更新数据case,when,then,end

UPDATE content_publish
SET content = CASE id
WHEN 21 THEN
'{"supplier_id":0,"shipping_express_id":"0","shipping_code":"","shipping_time":"0"} '
WHEN 22 THEN
'value2'
WHEN 23 THEN
'value3'
END
WHERE
id IN (21, 22, 23);//

UPDATE 33hao_order
SET mdate = '2017-03-10 15:26:03',
shipping_info = CASE order_id
WHEN 324 THEN
'[{"supplier_id":0,"shipping_express_id":"32","shipping_code":"550432091945","shipping_message":"","goods":["100077"],"shipping_time":"1464252505"}]'
WHEN 352 THEN
'[{"supplier_id":0,"shipping_express_id":"32","shipping_code":"7667767676","shipping_message":"","goods":["100363"],"shipping_time":"1478150574"}]'
WHEN 388 THEN
'[{"supplier_id":0,"shipping_express_id":"39","shipping_code":"11231232132132","shipping_message":"","goods":["100380","100381"],"shipping_time":"1478484967"}]'
END
WHERE
order_id IN (324, 352, 388);//

SELECT
sex,
count(*) AS zj
FROM
employee
WHERE
1 = 1
AND `status` = 0
GROUP BY
sex;//查询男女人数

SELECT
sum(CASE WHEN sex = 0 THEN 1 ELSE 0 END) 男,
sum(CASE WHEN sex = 1 THEN 1 ELSE 0 END) 女,
count(*) AS 总数
FROM
employee
WHERE
`status` = 0;//查询男女人数，总人数

（5）排序

SELECT * FROM 33hao_article_class ORDER BY ac_id asc;//正序
SELECT * FROM 33hao_article_class ORDER BY ac_id desc;//倒叙

（6）limit

SELECT * FROM content_publish WHERE type_id = 5 ORDER BY id DESC LIMIT 1;//倒数第一条

SELECT * FROM withdraw_list WHERE 1 = 1 AND cdate >= '2017-02-21 00:00:00' AND cdate <= '2017-02-21 23:59:59' ORDER BY id DESC LIMIT 0,10;//分页

SELECT op_date,STATUS,continued_num FROM member_qiandao_log WHERE member_id = $member_id ORDER BY id DESC LIMIT 31;//近31天的签到日志

SELECT * FROM 33hao_goods WHERE is_deleted = 0 AND goods_state = 1 AND goods_verify = 1 AND goods_commend = 1 ORDER BY rand() LIMIT 8;//随机取8个商品

（7）查询库表个数

SELECT COUNT(*) TABLES,table_schema FROM information_schema. TABLES WHERE table_schema = 'water_station' GROUP BY table_schema;

 （8）转换时间格式、时间戳

SELECT tid,`shop_id` ,`status` ,`user_id` ,cancel_reason,`payment` ,`receiver_name` , FROM_UNIXTIME( `created_time`, '%Y-%m-%d %H:%i:%S' ) as cdate FROM systrade_trade where `shop_id` = 3

AND `created_time` >= 1501516800 AND `created_time` <= 1504195199

（9）阿里云导出表格导致订单号乱

SELECT concat(refund_bn,"\t") AS '退款申请编号' ,user_id as '用户id',`shop_id` as '店铺id', concat(`tid`,"\t") as `订单id`,refunds_reason as '退款原因',`status` as '状态' ,order_price as '订单金额',total_price as '总金额'
,refund_fee as '退款金额' ,FROM_UNIXTIME( `created_time`, '%Y-%m-%d %H:%i:%S' ) as '创建时间' FROM sysaftersales_refunds
where `shop_id` = 3
AND `created_time` >= 1501516800 AND `created_time` <= 1504195199;

（10）关机键字查询

SELECT * from base_setting where `key` like '%b2c.wss.enable%'

（11）业务场景：促销商品是否存在其他促销活动中。

SELECT
     FROM_UNIXTIME(
        `start_time`,
        '%Y-%m-%d %H:%i:%S'
    ) AS start_time,
    FROM_UNIXTIME(
        `end_time`,
        '%Y-%m-%d %H:%i:%S'
    ) AS end_time,promotion_id,promotion_name
FROM
    syspromotion_promotions
WHERE
    (`end_time` > 1511157868)
AND (
    (
        start_time > 1507600800
        AND start_time < 1510279200
    )
    OR (
        start_time < 1507600800
        AND end_time > 1510279200
    )
    OR (
        end_time > 1507600800
        AND end_time < 1510279200
    )
);


SELECT it.`bn`  FROM sysitem_item_promotion as pro INNER JOIN `sysitem_item` as it ON pro.`item_id` = it.`item_id`  where pro.`promotion_id` in(158,157,103) and it.bn IN  (3333,4);