mysql常用

mysql -uiapplo12ad -pNRqimmXYdw7nxfdsY1sBHQ -h192.168.13.141 -P3307
mysqldump -utest3307 -ptest_ia2pp  -h192.168.11.124 -P3307 -d iapppay > abc.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p abc > abc.sql
show databases;
use
http://www.cnblogs.com/jiunadianshi/archive/2011/04/20/2022334.html

<update id="tryLockToCheck" parameterType="java.util.Map">
update `retry_${tableSuffix}` set
locktoken = IF(lockstatus = 0 or locktoken = #{locktoken} or locktimeout &lt; #{nowtime, jdbcType=BIGINT}, #{locktoken}, locktoken),
locktime = IF(lockstatus = 0 or locktoken = #{locktoken} or locktimeout &lt; #{nowtime, jdbcType=BIGINT}, #{locktime, jdbcType=BIGINT}, locktime),
locktimeout = IF(lockstatus = 0 or locktoken = #{locktoken} or locktimeout &lt; #{nowtime, jdbcType=BIGINT}, #{locktimeout, jdbcType=BIGINT}, locktimeout),
lockstatus = IF(lockstatus = 0 or locktoken = #{locktoken} or locktimeout &lt; #{nowtime, jdbcType=BIGINT}, 1, lockstatus)
where id = #{id, jdbcType=BIGINT}  and execFinish = 1 and checkFinish = 0
</update>
select中的IF函数是if（A=B,0,other）

<insert id="trylock" parameterType="java.util.Map">
insert into `type_lock`
(lockkey, token, locktime, locktimeout, status)
values
(
#{key,
jdbcType=CHAR},
#{token, jdbcType=CHAR},
#{locktime,jdbcType=BIGINT},
#{locktimeout, jdbcType=BIGINT},
1
)
on duplicate key update
token = IF(status = 0 or token = #{token} or locktimeout &lt; #{nowtime,
jdbcType=BIGINT}, #{token}, token),
locktime = IF(status = 0 or token =
#{token} or locktimeout &lt; #{nowtime, jdbcType=BIGINT}, #{locktime,
jdbcType=BIGINT}, locktime),
locktimeout = IF(status = 0 or token =
#{token} or locktimeout &lt; #{nowtime, jdbcType=BIGINT},
#{locktimeout, jdbcType=BIGINT}, locktimeout),
status = IF(status = 0
or token = #{token} or locktimeout &lt; #{nowtime, jdbcType=BIGINT},
1, status)
</insert>

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

create table en(id int,cr_name enum('广州', '北京', '新疆'))default character set utf8
select id,cr_name From en  where id =2
1.可以根据原名称查，可以根据索引查

  <update id="up">
  update `game_detail` set `status`=1,uptime = NOW() where gid in
        <foreach collection="gids" item="gid" open="(" close=")" separator=",">
            #{gid}
        </foreach>
</update>


//第一步锁表查询
<select id="getForUpdate" resultMap="TransOrderMap">
select
<include refid="template" />
from `trans_order` where trans_id = #{transId} for update
</select>

<update id="update" parameterType="com.zyzx.mall.charge.model.TransOrder">
update `trans_order` set
<if test="payPrice > 0">
pay_price = #{payPrice},
</if>
<if test="payType > 0">
pay_type = #{payType},
</if>
<if test="payTransId != null and payTransId != ''">
pay_trans_id = #{payTransId},
</if>
<if test="payResultInt >= 0">
pay_result = #{payResultInt},
</if>
<if test="payFinishTime != null">
pay_finish_time = #{payFinishTime},
</if>
<if test="rechrTransId != null and rechrTransId != ''">
rechr_trans_id = #{rechrTransId},
</if>
<if test="supplierId > 0">
supplier_id = #{supplierId},
</if>
<if test="supplyPrice > 0">
supply_price = #{supplyPrice},
</if>
<if test="rechrResultInt >= 0">
rechr_result = #{rechrResultInt},
</if>
<if test="rechrFinishTime != null">
rechr_finish_time = #{rechrFinishTime},
</if>
<if test="refundTransId !=null and refundTransId != ''">
refund_trans_id = #{refundTransId},
</if>
<if test="refundResultInt >= 0">
refund_result = #{refundResultInt},
</if>
<if test="refundFinishTime != null">
refund_finish_time = #{refundFinishTime},
</if>
<if test="rechrThirdNO != null">
rechr_third_no = #{rechrThirdNO},
</if>
<if test="rechrErrMsg != null">
rechr_errmsg = #{rechrErrMsg},
</if>
<if test="refundErrMsg != null">
refund_errmsg = #{refundErrMsg},
</if>
update_time = #{updateTime} where trans_id = #{transId}
</update>

//更新
information_schema
select table_name,table_rows from tables where table_schema='iapp_v3.4'
select table_name,concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data_length_GB from information_schema.tables