目录
一:读取语句
需求场景:wherw条件IN范围查询;SUM求和查询;Join联表查询;时间戳转为日期时间查询;GROUP BY查询等等
(1)查询数据表invest,字段status为1或4,以及其他条件的数据
SELECT `id`,`status` FROM `invest` WHERE `cateid` = 7 AND `status` IN (1,4) ORDER BY id desc;
(2)统计某字段总数,前提是搜索出的数据不会产生重复
SELECT SUM(money) FROM `invest` WHERE `cateid` = 7 AND `order_status` = 1 AND `virtual_status` = 0 AND `status` = 4
AND `crtime` < 1519574400 LIMIT 1;
(3)Join三表联查,where条件三表均有
select p.* from `erp_performance` p left join `erp_performance_user_tpl` u on p.tplId=u.id left join `erp_performance_role_tpl` r on r.id=p.tplId where p.type>0 and p.status>0 and (u.userId=6 or r.roleId=52) order by p.addTime desc limit 10;
(4)时间戳date_format转为日期时间查询
select id,userid,money,date_format(FROM_UNIXTIME(addtime),'%Y-%m-%d %H:%i:%s') as time from sy_pay_account WHERE `status`='succ' and `money`>0.1 ORDER BY id desc;
(5)GROUP BY应用,用户消费从高到低记录列表
select SUM(money) as summoney,username from sy_pay_account WHERE `status`='succ' GROUP BY username ORDER BY summoney DESC;
二:新增语句
需求场景:普通插入、多条插入、等
//插入一条数据,可部分字段
INSERT INTO menu(title,controller,action,position,sort_id,status) VALUES('push短信群发','Chairman','messpush','left','0','1');
三:修改语句
需求场景:单个修改,批量修改,条件批量修改等
//修改记录,where条件字段进行数学运算操作
UPDATE `claims_release` SET `status` = 4 WHERE( `status` = 8 AND `full_time`+3600*24*367 > 1520352000 AND `expire` =12);
UPDATE `goodsall_01` SET `tuijian_type`=251 , `fenxiao_type`=257 WHERE(`id`> 0);
四:删除语句
需求场景:where条件范围下的删除,变量方式下的SQl语句删除
(1)基本删除
DELETE FROM `php8_goods_all` WHERE (`id`>50000);
DELETE FROM `php8_goodsalllist_02` WHERE`goods_code` NOT IN (100024166665,100024339511,100024184857,100024112215);
(2)时间范围下的删除
DELETE FROM trans_list WHERE ctime>='1515918232' and ctime<='1515918255';
(3)通过设置变量,变量方式-关联删除
set @uid = 102;
DELETE FROM `n_device_info` WHERE (`uid`=@uid);
DELETE FROM `n_user` WHERE (`uid`=@uid);
五:数据表结构
需求场景:数据表加字段、修改字段、删除字段等
//数据表加字段
ALTER TABLE `yrnet_goodsall_01`
ADD COLUMN `tuijian_type` smallint(4) UNSIGNED NULL DEFAULT 0 COMMENT '推荐类型' AFTER `archives_state`,
ADD COLUMN `fenxiao_type` smallint(4) UNSIGNED NULL DEFAULT 0 COMMENT '分销类型' AFTER `tuijian_type`;