Mysql 的基本SQL语句

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Request">

    //添加
    <insert id="addRecordService" parameterType="map" >
        insert into servicelog(
            typeid,state,recorder
        ) values (
           #{typeid},#{state},now()
        )
    </insert>
    
    //删除
    <delete id="delMenuById" parameterType="String">
        delete 
            from 
        t_sys_menu 
            where 
        MENU_ID=#{MENU_ID} 
    </delete>
    
    //修改
    <update id="updateService"  parameterType="map">
        update contract set contract_id=#{contract_id}
        <if test="contract_state !=null and contract_state!=''">
            ,contract_state=#{contract_state}
        </if>
        <if test="servicePerson!=null and servicePerson!=''">
            ,servicePerson=#{servicePerson}
        </if>
        <if test="contract_userDate !=null and contract_userDate !=''">
            ,contract_userDate=now()
        </if>
            where contract_id=#{contract_id}
    </update>
  
    //查询数量
    <select id="getRequestAllCount"  parameterType="map" resultType="int" >        
        select count(contract_id) from contract where 1=1         
        <if test="contract_id !=null and contract_id !=''"> 
            and contract_id=#{contract_id}
        </if>
    </select>
    
    //查询 没有就赋值0 判断空
    <select id="getApplicationMaxId" parameterType="map" resultType="int">
        SELECT ifnull(max(application_id),0) FROM application where 1=1
    </select>
    
    //多表联查
    <select id="findRequestAll"  parameterType="map" resultType="map" >
        SELECT
            //查询所有
            co.*
            //条件查询
            co.contract_nameId,co.contract_number,a.customer_name
        FROM
            contract AS co
            LEFT JOIN customer AS a ON a.customer_id = co.contract_nameId
        WHERE 
        //不包含查询
        co.application_serviceState NOT IN(6)
        1=1
        //普通查询
        <if test="contract_id !=null and contract_id !=''"> 
            and contract_id=#{contract_id}
        </if>
        //简单模糊查询
        <if test="USER_NO!=null and USER_NO !=''"> 
            and USER_NO like CONCAT('%','${USER_NO}','%' ) 
        </if>
        //复杂模糊查询
        <if test="servicecodeId !=null and servicecodeId!=''"> 
            and servicecodeId IN(SELECT servicecodeId FROM servicelog  WHERE servicecodeId LIKE CONCAT('%','${servicecodeId}','%' ))
        </if>
        //单for
        <if test="contract_states !=null and contract_states !=''"> 
            and  contract_state in
            <foreach item="item" index="index" collection="contract_states"  open="(" separator="," close=")">  
                #{item}  
            </foreach>  
        </if>
        //连环for
        <if test="ROLE_ID !=null and ROLE_ID !=''"> 
            and contract_userId in(select u.PK_ID from t_sys_user as u where u.ROLE_ID in
            <foreach item="item" index="index" collection="ROLES"  open="(" separator="," close=")">  
                #{item}  
             </foreach> 
            )
        </if>
        //根据id 排序
        ORDER BY ROLE_ID
    </select>

    //查询时间显示时间格式
      <select id="getContractTerm" parameterType="map" resultType="String">
            SELECT DATE_FORMAT(contract_term, '%Y-%m-%d %H:%i:%s') as contract_term FROM contract WHERE contract_nameId=#{customer_id} ORDER BY contract_term DESC LIMIT 0,1 
      </select>     
</mapper>