常用的一些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)业务场景:促销商品是否存在其他促销活动中。
1 SELECT 2 FROM_UNIXTIME( 3 `start_time`, 4 '%Y-%m-%d %H:%i:%S' 5 ) AS start_time, 6 FROM_UNIXTIME( 7 `end_time`, 8 '%Y-%m-%d %H:%i:%S' 9 ) AS end_time,promotion_id,promotion_name 10 FROM 11 syspromotion_promotions 12 WHERE 13 (`end_time` > 1511157868) 14 AND ( 15 ( 16 start_time > 1507600800 17 AND start_time < 1510279200 18 ) 19 OR ( 20 start_time < 1507600800 21 AND end_time > 1510279200 22 ) 23 OR ( 24 end_time > 1507600800 25 AND end_time < 1510279200 26 ) 27 ); 28 29 30 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);