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

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

• • • ==时间转换；case，when，then==
    • ==DISTINCT==
    • ==mysql增加字段==
    • ==mysql，分组==

时间转换；case，when，then

SELECT
	tid,
	oid,
	CASE shop_id
WHEN 9 THEN
	'北京零售店'
WHEN 3 THEN
	'平台自营店'
END shop_name,
 user_id,
 bn,
 title,
 num,
 payment,
FROM_UNIXTIME( `pay_time`, '%Y-%m-%d %H:%i:%S' ) as pay_time
FROM
	systrade_order
WHERE
	tid IN (
		4025076720000851
	);

DISTINCT

SELECT
        COUNT(DISTINCT item_no) AS ctn
        FROM
        purchase_receipt_line

mysql增加字段

ALTER TABLE `np_crm_fund` ADD  `handle_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '处理状态';
ALTER TABLE `np_crm_fund` ADD  `handle_uid` int(10) NOT NULL DEFAULT '0' COMMENT '操作人';
ALTER TABLE `np_crm_fund` ADD  `handle_time` int(10) NOT NULL DEFAULT '0' COMMENT '处理时间';
ALTER TABLE `np_crm_fund` ADD  `handle_remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '处理备注';
ALTER TABLE `np_crm_fund` ADD  `handle_content_json` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '处理内容';

mysql，分组

#分组后，统计每组的个数

SELECT ppc_shop_id,count(*) AS counts from np_shop_recharge_log 
where source=0 and operate =1  GROUP BY ppc_shop_id ORDER BY counts desc LIMIT 5;