常用到的一些简单SQL语句

 

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;