SQL语句总结2018-11-7

基本语句

增加一条数据

insert into table (列字段1,列字段2)values(列1值,列2值)

批量增加

insert into table (列字段1,列字段2)values(列1值,列2值),(列1值,列2值)...

<insert id="batchInsertOrderItem" parameterType="java.util.List">
        insert into biz_order_item (
        id,
        order_id,
        product_id,
        price,
        count,
        amount
        )
        values
        <foreach collection="list" item="orderItem" index="index" separator=",">
            (
            #{orderItem.id},#{orderItem.order.id},#{orderItem.product.id},
            #{orderItem.price},#{orderItem.count},#{orderItem.amount}
            )
        </foreach>
    </insert>

删除一条数据

delete from table where 列名1=值1

批量删除

delete from table where 列名1 in (值1,值2...)

    <update id="batchRemoveByOrderIds" parameterType="List">
        update biz_order_item set deleted = 1 where
        <foreach collection="array" item="id" open="order_id in (" separator="," close=")">
            #{id}
        </foreach>
    </update>

修改一条数据

update table set 列名1=值1,列名2=值2 where 列名3=值3 

    <update id="update">
        update biz_company_address
        <set>
            deleted = 0,
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="addrName != null">addr_name = #{addrName},</if>
            <if test="partOne != null">part_one = #{partOne},</if>
            <if test="partTwo != null">part_two = #{partTwo},</if>
            <if test="postalCodeCity !=null">postal_code_city= #{postalCodeCity},</if>
            <if test="country !=null">country= #{country},</if>
            <if test="addrType !=null">addr_type= #{addrType},</if>
            <if test="vatNr !=null">vat_nr= #{vatNr},</if>
        </set>
        where id = #{id}
    </update>

跨表修改数据

    <update id="updateConfirm">
        update biz_order
        left join biz_order_item on biz_order.id = biz_order_item.order_id
        left join biz_product on biz_product.id = biz_order_item.product_id
        set
        <if test="orderType != null and orderType.length() > 0">
            biz_order.order_type = #{orderType},
        </if>
        <if test="paymentMethod != null and paymentMethod.length() > 0">
            biz_order.payment_method = #{paymentMethod},
        </if>
        <if test="deliveryMethod != null and deliveryMethod.length() > 0">
            biz_order.delivery_method = #{deliveryMethod},
        </if>
        <if test="deliveryConditions != null and deliveryConditions.length() > 0">
            biz_order.delivery_conditions = #{deliveryConditions},
        </if>
        <if test="goodsMarking != null and goodsMarking.length() > 0">
            biz_order.goods_marking = #{goodsMarking},
        </if>
        <if test="reference != null and reference.length() > 0">
            biz_order.reference = #{reference},
        </if>
        <if test="billTo != null and billTo.length() > 0">
            biz_order.bill_to = #{billTo},
        </if>
        <if test="referenceOrderNo != null and referenceOrderNo.length() > 0">
            biz_order.reference_order_no = #{referenceOrderNo},
        </if>
        biz_order.confirmed = 1,
        biz_order.confirm_date = now(),
        biz_order_item.price = biz_product.price,
        biz_order_item.amount = biz_product.price*biz_order_item.count,

        biz_product.po= biz_product.po + biz_order_item.count,
        biz_product.in_order= biz_product.in_order - biz_order_item.count
        where biz_order.deleted = 0 and biz_order.id= #{id}
    </update>

联合查询onion

 返回的结果类型为 List<Map>

    <select id="getBalanceOfCompany" resultType="Map">
        SELECT IFNULL(sum(amount_due),0) 'a', 'noOverFifteen' type  FROM biz_invoice  where  company_id = #{companyId} and  deleted = 0 and DATEDIFF(now(),invoice_date) &lt;=15
        UNION
        SELECT IFNULL(sum(amount_due),0) 'b', 'noOverDue' type FROM biz_invoice  where  company_id = #{companyId} and  deleted = 0 and DATEDIFF(now(),invoice_date) > 15 and DATEDIFF(now(),du_date) &lt;= 0
        UNION
        SELECT IFNULL(sum(amount_due),0) 'c', 'overInFifteen' type FROM biz_invoice  where  company_id = #{companyId} and  deleted = 0 and DATEDIFF(now(),du_date) &lt;= 15 and DATEDIFF(now(),du_date) &gt; 0
        UNION
        SELECT IFNULL(sum(amount_due),0) 'd', 'overFifteen' type FROM biz_invoice  where  company_id = #{companyId} and  deleted = 0 and DATEDIFF(now(),du_date) &gt; 15
    </select>

模糊搜索查询

    <select id="findAll" resultType="Order">
        select
        sum(i.amount) "total",
        o.delivered,o.delivery_method,
        o.last_shipment_date,o.order_no,
        o.id,o.create_date,o.update_date,
        o.bill_to,o.confirmed,o.confirm_date,
        o.delivery_conditions,o.goods_marking,
        o.payment_method,o.reference,o.reference_order_no,
        o.pdf_id,o.pdf_id_en
        from biz_order o left join biz_order_item i on o.id = i.order_id
        where o.deleted = 0 and i.deleted = 0
        <if test="keywords != null and keywords.length() > 0">
            and (
            o.company_id = #{keywords}
            or o.order_no like concat('%',#{keywords},'%')
            or o.order_type like concat('%',#{keywords},'%')
            or o.bill_to like concat('%',#{keywords},'%')
            or o.payment_method like concat('%',#{keywords},'%')
            or o.delivery_method like concat('%',#{keywords},'%')
            or o.delivery_conditions like concat('%',#{keywords},'%')
            or o.goods_marking like concat('%',#{keywords},'%')
            or o.reference like concat('%',#{keywords},'%')
            or o.reference_order_no like concat('%',#{keywords},'%'))
        </if>
        group by i.order_id
        order by o.update_date desc
    </select>

插入不重复数据

    <insert id="insert">
     insert into biz_company_address (
     id, company_id, addr_name, part_one, part_two,postal_code_city,country,addr_type, vat_nr)
        select
            #{id},
            #{companyId},
            #{addrName},
            #{partOne},
            #{partTwo},
            #{postalCodeCity},
            #{country},
            #{addrType},
            #{vatNr}
        from dual
        where not exists (select bca.id
                              from biz_company_address  bca
                              where bca.deleted = 0
                              and bca.company_id = #{companyId}
                              and bca.addr_type = #{addrType}
                          )

    </insert>

循环or条件查询

    <select id="findByNames" resultMap="DictionaryMap" parameterType="List">
        select d.*, di.id "item_id", di.dictionary_id, di.item_code, di.item_value, di.item_value_en, di.deleted "item_deleted", di.create_date "item_create_date", di.update_date "item_update_date"
        from base_dictionary d
        left join base_dictionary_item di on d.id = di.dictionary_id
        where d.deleted = 0
        <foreach collection="array" item="name" open="and (" separator=" or " close=")">
            d.dictionary_name = #{name}
        </foreach>
    </select>

条件插入

    <insert id="insert" >
        insert into biz_product_catalog (
            id,
            <if test="parentId != null and '' != parentId">parent_id,</if>
            catalog_name,
            catalog_name_en,
            description
        )
        values (
            #{id},
            <if test="parentId != null and '' != parentId">#{parentId},</if>
            #{catalogName},
            #{catalogNameEn},
            #{description}
        )
    </insert>

返回行数的查询

    <select id="getTopTenProductsMonth" resultType="TopProduct">


    select
        @rownum:=@rownum+1 as rownum,
        pro.productName "productName",
        pro.productNameEn "productNameEn",
        pro.productNo "productNo",
        pro.count "count" from
    (
    　　SELECT bp.product_name 'productName',
   　　 bp.product_name_en 'productNameEn',
   　　 bp.product_no 'productNo',
   　　 sum(boi.count) 'count'
  　　  FROM biz_product bp
  　　  LEFT JOIN biz_order_item boi ON boi.product_id = bp.id
  　　  WHERE boi.order_id in
  　　    (
  　　    SELECT bo.id FROM biz_order bo
 　　       where bo.confirmed = 1
 　　       and quarter(update_date)=quarter(date_sub(now(),interval 1 quarter))
　　      )
　　    GROUP BY bp.id ORDER BY count desc limit 10
    ) 
　　 pro,(select @rownum:=0) t
    </select>

查询一条数据

select 字段1,字段2 from table 

去重

distinct   字段名

求和

sum(字段名)

求最大值、最小值

max(字段名)，min(字段名)

求平均数

avg(字段名)

按字段排序-升序asc（降序desc）

升序：order by 列字段 asc

降序：order by 列字段 desc

分组

group by 列字段

返回列行数

count(列字段)，count(*)

条件关键字

查询字段的值在A到B范围的数据：where 列字段 between A and B

等值条件查询： where 列字段 in(值1,值2,值3...)