【SQL用法】Mybatis框架中的xml文件中经常使用的sql语句

        insert into mainsite_product_message
        
            
                customer_name,
            
            
                customer_phone,
            
            
                customer_email,
            
            
                create_time,
            
            
                customer_status,
            
        
        
            
                #{customerName},
            
            
                #{customerPhone},
            
            
                #{customerEmail},
            
            
                #{createTime},
            
            
                #{customerStatus},
            
        
    

    
    

    

    
    
        update mainsite_product_message set customer_status = 1 where id in
        
            #{id}
        
    

    
    
        delete from mainsite_product_message where id = #{id}
    

    
    
        select
        id as id,
        site_id as siteId,
        site_name as siteName,
        customer_name as customerName,
        customer_phone as customerPhone,
        customer_email as customerEmail,
        product_want as productWant,
        create_time as createTime,
        customer_status as customerStatus,
        search_keyword as searchKeyword
        from mainsite_product_message
        
            
                customer_name like CONCAT('%', #{customerName}, '%')
            
            
                and customer_phone = #{customerPhone}
            
            
                and create_time = #{createTime}
            
            
                and customer_email = #{customerEmail}
            
            
                and product_want = #{productWant}
            
            
                and site_id = #{siteId}
            
            
                and site_name = #{siteName}
            
            
                and customer_status = #{customerStatus}
            
            
                and search_keyword = #{searchKeyword}
            
        
        ORDER BY create_time DESC
    

        SELECT
        c.*,
        m.*,
        u.avatar avatar,
        u.username username
        FROM
        site_model_content c LEFT JOIN  site_model_${tableName}  m
        ON
        c.content_id = m.content_id
        LEFT JOIN
        t_sys_user u ON u.user_id = c.user_id
        
            c.status=1
            
               AND  c.site_id = #{siteId}
            
            
               AND
                c.category_id in
                 
                   #{item}
                 
            
            
                AND
                c.recommend = 1
            
            
                AND  m.${whereParam} = #{valueParam}
            
            
                AND
                c.thumb!=''
            
            
                AND
                c.thumb=''
            
            
                AND
                c.site_type like CONCAT('%', #{siteType}, '%')
            

        

        
            
                order by c.content_id
            
            
                order by c.inputdate DESC
            
            
                order by c.inputdate
            
            
                order by c.updatedate DESC
            
            
                order by c.updatedate
            
            
                order by c.view_num DESC
            
            
                order by c.content_id DESC
            
        
    

    
        INSERT INTO
            t_cms_site(
            site_name,
            `domain`,
            template,
            is_mobile,
            mobile_tpl,
            status
            )
            values
            
                (#{item.siteName},#{item.domain},#{item.template},
                #{item.isMobile},#{item.mobileTpl},#{item.status})
            
    

SELECT
        count(*)
        FROM
        crm_contract
         isDel = 0
            
                and departmentId LIKE CONCAT(#{departmentId},'%')
            
            
                and directorAccount = #{directorAccount}
            
            
                and executeState = #{executeState}
            
            
                and auditState = #{auditState}
            
            
                and contractType = #{contractType}
            
            
                and categoryId = #{categoryId}
            
            
                and customerName like CONCAT("%",#{customerName},"%")
            
            
                and director like CONCAT("%",#{director},"%")
            
            
                and signUser like CONCAT("%",#{signUser},"%")
            
            
                = DATE_FORMAT(#{startDate},"%Y-%m-%d") ]]>
            
            
                
            
        

select
        c.*
        from crm_contract c left join crm_customer_info i on c.customerId =
        i.customerId
        
            c.isDel = 0
            
                and c.endDate >= #{startDate}
            
            
                and c.endDate 
            
                and c.customerName like CONCAT(#{customerName},"%")
            
            
                and c.director like CONCAT(#{director},"%")
            
            
                and c.auditState = #{auditState}
            
        
        limit #{start},#{end}

        SELECT
        log_id AS logId,
        page_name AS pageName,
        template_name AS templateName,
        publish_status AS publishStatus,
        consuming_time AS consumingTime,
        C.description,
        create_time AS createTime,
        C.site_id AS siteId,
        C.category_id AS categoryId,
        C.site_name as siteName,
        D.category_name as categoryName
        FROM
            (
                SELECT
                    log_id,
                    page_name,
                    template_name,
                    publish_status,
                    consuming_time,
                    A.description,
                    create_time,
                    A.site_id,
                    category_id,
                    site_name
                FROM
                    mainsite_publish_log A
                LEFT JOIN t_cms_site B ON A.site_id = B.site_id
            ) C
        LEFT JOIN t_cms_category D ON (C.category_id = D.category_id)
        
            
                = DATE_FORMAT(#{startTime},"%Y-%m-%d %H:%i:%s") ]]>
            
            
                
            
         order by log_id desc