常用sql语句

#添加

"insert into user(username, password, create_time) values ('%s', '', '%s')" % (userName, now)

 

#更新

 "update item set propext = '%s', lefttimes = %d ,strengthen_level = %d where uid = %d and itemid = %d" % (propExt,item.lefttimes,item.strengthenLevel, player.uid, item.uid)

 

#删除

"delete from password_lock where uid = %d" % (uid)

 

#查询

'select uid,name,job, gold, level from player order by gold desc,uid limit 100'

'select uid,name,job,fightpower, level from player where job=3 order by fightpower desc,uid   limit 100'

 "select b.uid, b.name, b.job, a.total_send_num, b.level from flower as a, player as b where b.uid = a.ownerid order by a.total_send_num desc,uid limit 100"  # 联合查询

sql = 'select group_concat(b.uid) as  bro_uid, group_concat(b.name) as  bro_names, group_concat(b.job) as bro_job, group_concat(b.level) as bro_level, group_concat(b.fightpower) as bro_fightpower, sum(b.fightpower) as total_fight, a.bid from brother as a, player as b where b.uid in (a.uid1, a.uid2, a.uid3) group by a.bid order by total_fight desc,a.bid limit 100' （如果需要单独显示每个玩家的信息可通过python split分割符进行处理）

"select auctionid,cfgid,sellPrice,sellTime,lefttimes,createtime,strengthen_level,propext, item_uid, name, level, uid from auction where uid <> %d and name like '%%%s%%' order by sellTime desc" % (player.uid, name)             # 模糊查找

#添加替换(在没有的时候添加，在已存在的情况下替换)

sql  = "replace into enemy (uid, eid, typeId, add_time) values ('%d', '%d', '%d', '%d') " % (uid, eid, typeId, add_time)