基本语句
增加一条数据
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) <=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) <= 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) <= 15 and DATEDIFF(now(),du_date) > 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) > 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...)