1. 简单更新
(1)将gid为1000的物品的价格改为100
update goods set price = 100 where gid = 1000;
(2)同时更新多个字段
update state set value = 0, time = unix_timestap() where state = 1;
(3)更新id从50到100的所有记录
update goods set prices = 100 where gid between 50 and 100;
(4)将所有用户ID大于1000的用户的坐标为100到103的所有建筑ID改为9
sys_building(cid, bid,xy...)
sys_city(cid, uid...)
update sys_building b left join sys_city c on b.id = c.id set b.id = 9 where c.uid > 1000 and b.xy in(100,101,102,103);
(5)将普通城池(type=0)的等级为1的官府(bid = 1)的黄金最大值设置为10,0000
city_res(id, cid,gold_max....)
sys_building(cid,bid,level...)
sys_city(cid,uid,type...)
update city_res m left join sys_city c on m.cid = c.cid left join sys_building b on c.cid = b.cid set m.gold_max = 100000 where c.type = 0 and b.bid = 1 and b.level = 1;
(6)给表增加一个字段
alter table sys_city add column ownerid integer default -1;
(7)简单删除,删除价格小于100的所有物品
delete from goods where price > 100;
2. 插入
(1)批量插入,给满足某一条件的用户插入一条任务记录信息
sys_user_task(uid,tid,state)
insert into sys_user_task (uid,tid,state) (select uid, $tid, 0 from sys_user_task where tid = 1000 and state = 1) on duplicate key update state = 0;
(2)简单插入
insert into task_group(`id`,`name`,`type`) values('1001','attack', 1);
insert into task_group values('1001','attack',1);
(3)替换表中的某一条记录
replace into sys_building(`cid`,`bid`,`level`) values('$cid',6,1);
3. 查询
(1) 查询当前时间戳
select unix_timestamp();
(2)统计每一个省的城市个数
select count(*) from city group by province order by province;
4. 工作中遇到的一个需求,更新每个城市的黄金容量,黄金容量跟城市官府的等级有关系,城市分好几等级,普通城市,县城,州城等,黄金容量存储在mem_city_resource表中中
城市表sys_city, 城市建筑表 sys_building, 黄金容量表cfg_city_gold_max
update mem_city_resource aa, sys_building bb,sys_city cc,cfg_city_gold_max dd set aa.gold_max=dd.value where bb.bid=6 and aa.cid=cc.cid and aa.cid=bb.cid and dd.type=cc.type and dd.level=bb.level;