mysql常用的读取、新增、修改、删除语句整理

目录

一：读取语句

二：新增语句

三：修改语句

四：删除语句

五：数据表结构

---

一：读取语句

需求场景：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`;