sql大全_终身受用-时刻更新

1、Map类型的数据进行inset插入

有时候需要简单地把一个Map中所有的key和value获取出来，拼到sql语句中。MyBatis提供的一种方法是遍历Map中的entrySet，然后把key扔进index里面，value扔进item中。具体的一个使用的例子如下：

<insert id="operationName" parameterType="map">  
	    INSERT INTO table_name(hot_word, cnt)  
	    VALUES  
	    <foreach item="value" index="key" collection="mapData.entrySet()" open="(" separator="),(" close=")">  
	        #{key}, #{value}  
	    </foreach>  
	    ON DUPLICATE KEY UPDATE  
	    cnt=VALUES(cnt)  
</insert>

2、获取数据结果为list<map<String,Object>>

1、mapper

public interface HealerJeanMapper {

     List<Map<String,Object>> sqlMap();
}

2、mapper.xml

<select id="sqlMap" resultType="java.util.HashMap">
  SELECT h.id as id ,h.subject as subject FROM  healerjean  h;
</select>

3、解释：

1、返回类型必须是java.util.HashMap
2、map中的value 必须是Objecrt

4、controller测试

@RequestMapping("sqlMap")
@ResponseBody
public List<Map<String,Object>> sqlMap(){
    return healerJeanMapper.sqlMap();
}

[外链图片转存失败(img-NdRiKKK0-1566553583165)(https://raw.githubusercontent.com/HealerJean/HealerJean.github.io/master/blogImages/WEFASDFUMM.png)]

3、mybatis 自定义参数、返回类型map

1、参数

<select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE"resultType="java.util.HashMap
>
        CALL ges_user_count(?,?)
 </select>     

<parameterMap type="java.util.Map" id="getUserCountMap">
   <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
   <parameter property="userCount" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>

2、结果，返回字段一一匹配，想想之前@Results和@Result

  <resultMap id="BaseResultMap" type="com.taotao.pojo.TbUser" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="phone" property="phone" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />
    <result column="updated" property="updated" jdbcType="TIMESTAMP" />
  </resultMap>

3、解释
property和column分别指定实体类属性和数据表的列名。

4、If的使用

1、mapper接口

public interface CustomerMapper {

    List<Customer> findCustomerList( @Param("name") String name,
                                     @Param("status") Integer status,
                                     @Param("offset") Integer offset,
                                     @Param("limit") Integer limit);

}

2、mapper.xml 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="com.duodian.admore.dao.mybatis.customer.CustomerMapper">

    <select id="findCustomerList" resultType="com.duodian.admore.entity.db.customer.Customer">
        SELECT t.* from crm_customer t
        WHERE t.isVisiblisVisiblee = 1
        <if test="name != null and name != ''">
            and t.name like CONCAT('%','${name}','%' )
        </if>
        <if test="status != null">
            and t.status = #{status}
        </if>
        order by t.id DESC
        limit #{offset}, #{limit}
    </select>

    <select id="countCustomerList" resultType="java.lang.Long">
        select count(*) from crm_customer t
        WHERE t.isVisible = 1
        <if test="name != null and name != ''">
            and t.name like CONCAT('%','${name}','%' )
        </if>
        <if test="status != null">
            and t.status = #{status}
        </if>
    </select>

</mapper>

5、where include的使用（ 加入模糊查询筛选条件）自带去除第一个and

5.1、mapper

@Override
    public Page<SignetApplyRecord> findVerifySignetData(Long admId, Pageable pageable, SignetQuery query) {

        query.setStartDate(DateHelper.getDateFirstTime(query.getStartDate()));
        query.setEndDate(DateHelper.getDateLastTime(query.getEndDate()));
        query.setOffset(pageable.getOffset());
        query.setLimit(pageable.getPageSize());

              

        List<SignetApplyRecord> list = signetMapper.findVerifySignetList(query);
              

        Long count = signetMapper.countVerifySignet(query);

        return new PageImpl<>(list, pageable, count);
    }

5.2、xml

<select id="findVerifySignetList" resultType="com.duodian.admore.entity.db.signet.SignetApplyRecord">
    SELECT t.*,d.nickName userNickName,ifnull(e.realName,f.realName) userAuthName,b.name applyAdmName,ura.remittanceAccount
    FROM signet_apply_record t
    LEFT JOIN user d ON d.id = t.userId
    LEFT JOIN user_remittance_account ura ON ura.userId = t.userId
    <if test="currAdmId4Auth != null">
        LEFT JOIN sys_admin_user_customer h ON h.admId = #{currAdmId4Auth} AND h.userId = t.userId
        LEFT JOIN (SELECT wt.pid,count(*) cc FROM workflow_task_examine_details wte LEFT JOIN workflow_task wt ON wt.id = wte.taskId WHERE wt.taskType = 23 AND wte.admId = #{currAdmId4Auth} GROUP BY wt.pid) w ON w.pid = t.id
    </if>
    <where>
        <include refid="findVerifySignetWhere"></include>
    </where>
    order by t.id desc
    <if test="offset != null and limit != null">
        limit #{offset}, #{limit}
    </if>
</select>


<sql id="findVerifySignetWhere">
       <if test="taskTypeList != null and taskTypeList.size() > 0">
        and t.taskType IN
        <foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
            #{at}
        </foreach>
    </if>
    <if test="signetType != null">
        and t.type = #{signetType}
    </if>
    <if test="status != null">
        and t.status = #{status}
    </if>
    <if test="currAdmId4Auth != null ">
        <![CDATA[ and (h.admId = #{currAdmId4Auth} OR w.cc > 0) ]]>
    </if>
</sql>

5.3、自带去除第一个and

<select id="findUserAppsSpreadEffectList" resultType="com.duodian.admore.entity.db.apps.AppsSpreadEffectReport">
    SELECT t.*,b.trackName,b.smallIcon FROM apps_spread_effect_report t
    LEFT JOIN apps_user_app a ON a.trackId = t.trackId
    LEFT JOIN apps_app b ON b.trackId = t.trackId
    <where>
        <if test="userId != null">
            a.userId = #{userId}
        </if>
        <if test="startDate != null ">
            <![CDATA[ and t.spreadDate >=  #{startDate} ]]>
        </if>
        <if test="endDate != null ">
            <![CDATA[ and t.spreadDate <=  #{endDate} ]]>
        </if>
    </where>
    order by a.id desc
</select>



<!--自带取去除第一个and-->
SELECT * FROM OD_LINK_TYPE
   <where>
       LINK_URI != BASE_TYPE_URI
       <if test="linkUri != null">
          AND LINK_URI LIKE '%${linkUri}%'
       </if>
       <if test="linkLabel != null">
           AND LINK_LABEL LIKE '%${linkLabel}%'
       </if>
       <if test="baseTypeUri != null">
           AND BASE_TYPE_URI=#{baseTypeUri}
       </if>
   </where>

6、、foreach list结合作为参数在mapper中的查询（taskTypeList 为List）

 <if test="taskTypeList != null and taskTypeList.size() > 0"> and t.taskType IN
      <foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
            #{at}
       </foreach>
</if>

<sql id="findVerifySignetWhere">
    <if test="startDate != null">
        <![CDATA[ and t.cdate >= #{startDate} ]]>
    </if>
    <if test="endDate != null">
        <![CDATA[ and t.cdate <= #{endDate} ]]>
    </if>
    <if test="userParam != null and userParam != '' and userParam!= 'undefined'">
        AND (t.userId = #{userParam} OR d.nickName LIKE CONCAT('%',#{userParam},'%' ) OR e.realName LIKE
        CONCAT('%',#{userParam},'%' ) OR f.realName LIKE CONCAT('%',#{userParam},'%' ) OR g.email LIKE
        CONCAT('%',#{userParam},'%' ))
    </if>
    <if test="taskType != null">
        and t.taskType = #{taskType}
    </if>
    <if test="taskTypeList != null and taskTypeList.size() > 0"> and t.taskType IN
      <foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
            #{at}
       </foreach>
    </if>
    <if test="signetType != null">
        and t.type = #{signetType}
    </if>
    <if test="status != null">
        and t.status = #{status}
    </if>
    <if test="currAdmId4Auth != null ">
        <![CDATA[ and (h.admId = #{currAdmId4Auth} OR w.cc > 0) ]]>
    </if>
</sql>

7、choose when

<if test="flag != null">
    <choose>
        <when test="flag == 1">
            and t.status = 0
        </when>
        <when test="flag == 2">
            and t.status = 1
        </when>
        <when test="flag == 3">
            and t.expressStatus = 1
        </when>
        <when test="flag == 4">
            and t.status = -2
        </when>
    </choose>
</if>

8、ifnull （如果不是空返回第一个，否则返回第二个）

    ifnull(b.realName,c.realName) authName,

<select id="findRedStartSpread" parameterType="com.duodian.admore.dao.db.redstart.query.RedStartSpreadQuery" resultType="com.duodian.admore.dao.db.redstart.bean.RedStartHistoryBean">
    SELECT
    k.trackId,
    e.smallIcon,
    e.formattedPrice,
    e.price,
    e.fileSizeBytes,
    e.trackName,
    f.name admName,
    a.nickName userName,
    ifnull(b.realName,c.realName) authName,
    DATE_FORMAT(k.spreadDateStart, '%Y-%m-%d') AS ymd,
    k.userId
    FROM
    redstart_spread k

9。制作参数为map值进行传入（opt项目SkinsController）

1、controller接收参数

@RequestMapping("data")
@ResponseBody
public ResponseBean data(String name,
					  Integer type,
					  Integer status,@RequestParam(value = "page",defaultValue = "0") Integer page){
  
        int pageSize = 15;
        Pageable pageable = new PageRequest(page,pageSize);
        Page<SkinAppInfoData> dataPage = skinsService.findSkinAppInfoList(pageable,"name",name,"type",type,"status",status);
        return ResponseBean.buildSuccess(dataPage);

}

2、service 制作map参数（下面的功能是模糊查询?分页）

1、service接口

public Page<SkinAppInfoData> findSkinAppInfoList(Pageable pageable, Object... param) throws AppException;

2、service开始实现 （pageable 主要是利用里面的参数制作limit参数的）

@Override
public Page<SkinAppInfoData> findSkinAppInfoList(Pageable pageable, Object... param) throws AppException {
    Map data = MyBatisHelper.mergeParameterMap(pageable,param);

if(data.get("startDate") != null){
    Date startDate = (Date) data.get("startDate");
    data.put("startDate", com.duodian.admore.core.helper.DateHelper.getDateFirstTime(startDate));
}
if(data.get("endDate") != null){
    Date endDate = (Date) data.get("endDate");
    data.put("endDate", com.duodian.admore.core.helper.DateHelper.getDateLastTime(endDate));
}

    List<SkinAppInfoData> dataList = skinsMapper.findSkinList(data);

    for(SkinAppInfoData skinAppInfoData :dataList){
        List<ChannelJson> channelJsonList = new ArrayList<>();
        if(skinAppInfoData.getChannelJson()!=null&&!"".equals(skinAppInfoData.getChannelJson())) {
            JSONArray jsonArray = JSONArray.fromObject(skinAppInfoData.getChannelJson());
            for (int i = 0; i < jsonArray.size(); i++) {
                ObjectMapper objectMapper = new ObjectMapper();
                try {
                    ChannelJson channelJson = objectMapper.readValue(jsonArray.get(i).toString(), ChannelJson.class);
                    channelJsonList.add(channelJson);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        skinAppInfoData.setChannelJsonList(channelJsonList);

    }

    Long count = skinsMapper.countSkinList(data);
    return new PageImpl<SkinAppInfoData>(dataList,pageable,count);
}

3、MyBatisHelper工具栏

public class MyBatisHelper {
    public static final String PARAM_OFFSET = "offset";
    public static final String PARAM_LIMIT = "limit";

    public MyBatisHelper() {
    }

    public static Map<String, Object> mergeParameterMap(Object... parameter) {
        if (parameter.length % 2 != 0) {
            throw new IllegalArgumentException("parameter须为key-value对应参数");
        } else {
            Map<String, Object> map = new HashMap();

            for(int i = 0; i < parameter.length; i += 2) {
                map.put(parameter[i].toString(), parameter[i + 1]);
            }

            return map;
        }
    }

    public static Map<String, Object> mergeParameterMap(Pageable pageable, Object... parameter) {
        if (parameter.length % 2 != 0) {
            throw new IllegalArgumentException("parameter须为key-value对应参数");
        } else {
            Map<String, Object> map = new HashMap();
            map.put("offset", pageable.getOffset());
            map.put("limit", pageable.getPageSize());

            for(int i = 0; i < parameter.length; i += 2) {
                map.put(parameter[i].toString(), parameter[i + 1]);
            }

            return map;
        }
    }
}

4、mapper.java （下面这两个其实就是专门用来做制作page分页的）

public interface SkinsMapper {

    public List<SkinAppInfoData> findSkinList(Map param);

    public Long countSkinList(Map param);

}

<?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="com.duodian.admore.dao.db.skins.SkinsMapper">

    <select id="findSkinList" resultType="com.duodian.admore.data.skins.SkinAppInfoData">
        SELECT
            A1.`appid`,
            A1.`appSecret`,
            A1.`icon`,
            A1.`makerMemo`,
            A1.`haveBackstage`,
            A1.`channelJson`,
            A1.`filePath`
        FROM  `skin_app_info_check`  a1
        where A1.status not in (9)
        <if test="name != null and name != ''">
            AND (A1.trackId = #{name}
            OR A1.name LIKE CONCAT('%',#{name},'%' )
            OR A1.appid LIKE CONCAT('%',#{name},'%' )
            OR A1.appSecret LIKE CONCAT('%',#{name},'%' )
            )
        </if>
        <if test="type != null and type != '' ">
            and A1.type = #{type}
        </if>
        <if test="status != null and status != '' ">
            and  A1.status = #{status}
        </if>
        order by A1.cdate desc
        <if test="offset != null and limit != null">
            limit #{offset}, #{limit}
        </if>
    </select>

    <select id="countSkinList" resultType="java.lang.Long">
        SELECT
              count(*)
            FROM   `skin_app_info_check`      A1
            where A1.status != 9
            <if test="name != null and name != ''">
                AND (A1.trackId = #{trackId} OR A1.name LIKE CONCAT('%',#{name},'%' ))
            </if>
            <if test="type != null">
                and A1.type = #{type}
            </if>
            <if test="status != null">
                and A1.status = #{status}
            </if>
    </select>
</mapper>

5、有分组的

<select id="findCouponTaoKeDataByParam" resultType="com.duodian.youhui.entity.db.coupon.CouponTaoKeData">
  SELECT c.itemTitle,COUNT(c.itemId),sum(c.estimateAmount),c.adzoneName,c.adzonePid FROM  coupon_taoke_data c WHERE  1=1
    <where>
        <include refid="findCouponTaoKeDataByParamSQL"></include>
    </where>
    GROUP by c.itemId
    <if test="offset != null and limit != null">
        limit #{offset}, #{limit}
    </if>
</select>

<select id="countFindCouponTaoKeDataByParam">
    select count(*) from (
     SELECT c.itemTitle FROM  coupon_taoke_data c WHERE  1=1
    <where>
        <include refid="findCouponTaoKeDataByParamSQL"></include>
    </where>
    GROUP by c.itemId
    ) as a
</select>

<sql id="findCouponTaoKeDataByParamSQL">
    <if test="startDate != null ">
        <![CDATA[ and  c.createTime >=  #{endDate} ]]>
    </if>
    <if test="endDate != null ">
        <![CDATA[ and c.createTime <=  #{endDate} ]]>
    </if>
    <if test="adzonePid != null and adzonePid != ''">
        <![CDATA[ and c.adzonePid <=  #{adzonePid} ]]>
    </if>
</sql>

10、模糊查询，对象作为参数传入，类似于9中的map

1、query对象 (查询参数)

public class SysUserQuery implements Serializable {

    private static final long serialVersionUID = -4564423981924197001L;

    private Long id;
    private Integer offset;
    private Integer limit;
    private Date startDate;
    private Date endDate ;
    private String userid;
    private String userParam;
    private Integer status;


}

2、controller层

@RequestMapping("data")
@ResponseBody
public ResponseBean data(@RequestParam(defaultValue = "0")Integer page, @RequestParam(defaultValue = "15")Integer pageSize, SysUserQuery query){
    try {
        Pageable pageable = new PageRequest(page,pageSize);
        return ResponseBean.buildSuccess(sysDingUserService.getDingUserData(pageable,query));
    } catch (AppException e) {
        return ResponseBean.buildFailure(e.getMessage());
    } catch (Exception e) {
        logger.error(e.getMessage(),e);
        return ResponseBean.buildFailure(ErrorCodeEnum.系统错误);
    }
}

3、service层，将pageable分页对象放入

 @Override
    public Page<SysDingUser> getDingUserData(Pageable pageable, SysUserQuery query) {

        query.setOffset(pageable.getOffset());
        query.setLimit(pageable.getPageSize());

        List<SysDingUser> list = sysMapper.findSysDingUserList(query);

        Long count = sysMapper.countSysDingUser(query);
        return new PageImpl<>(list, pageable, count);
    }
}

4、mybatis查询语句

<select id="findSysDingUserList" resultType="com.duodian.admore.entity.db.admin.SysDingUser">
    SELECT t.*, a.admId,b.name admName FROM sys_ding_user t
    LEFT JOIN sys_admin_user_ding a ON a.userid = t.userid
    LEFT JOIN sys_admin_user b ON b.id = a.admId
    <where>
        <if test="userParam != null and userParam != ''">
            and (t.userid = #{userParam} OR t.name LIKE CONCAT('%',#{userParam},'%' ) OR t.email LIKE CONCAT('%',#{userParam},'%' )
            OR t.orgEmail LIKE CONCAT('%',#{userParam},'%' ) OR t.mobile LIKE CONCAT('%',#{userParam},'%' ) OR t.position LIKE CONCAT('%',#{userParam},'%' ))
        </if>
    </where>
    ORDER BY t.id ASC
    limit #{offset}, #{limit}
</select>

11、sql取出制作DTO对象

1、对于数据库字段匹配的，可以直接选择

2、对于不匹配的使用 as 转化

<select id="findRedStartSpread" parameterType="com.duodian.admore.dao.db.redstart.query.RedStartSpreadQuery" resultType="com.duodian.admore.dao.db.redstart.bean.RedStartHistoryBean">
    SELECT
    k.trackId,
    e.smallIcon,
    e.formattedPrice,
    e.price,
    e.fileSizeBytes,
    e.trackName,
    f.name admName,
    a.nickName userName,

    DATE_FORMAT(k.spreadDateStart, '%Y-%m-%d') AS ymd,
    k.userId
    FROM
    redstart_spread k

11.1、RedStartHistoryBean

public class RedStartHistoryBean implements Serializable {

    private Long userId;
    private String ymd;
    private String trackId;
    private String smallIcon;
    private String bundleId;
    private String formattedPrice;
    private BigDecimal price;
    private BigInteger fileSizeBytes;
    private String trackName;

    private String fileSizeDesc;   //文件大小描述
    private String authName;
    private String admName;
    private String userName;

11.2、RedStartSpreadQuery

public class RedStartSpreadQuery implements Serializable {

    private Long userId;
    private String trackId;
    private String trackName;
    private Date startDate;
    private Date endDate;
    private String name;
    private Integer status;

    private Integer offset;
    private Integer limit;
    private Date point;

    private String userParam;
    private String appParam;

12、只有一个参数传入的时候，不使用注解@Param,不能直接写参数名字了 而是使用下面的_parameter

1、mapepr.java

List<CustomerChance> getCustomerList(Long adminId);

2、mapper.xml

<select id="getCustomerList" resultType="com.duodian.admore.entity.db.customer.CustomerChance">
    SELECT *
    FROM `crm_customer_chance`  c
    where c.isVisible = 1
    <if test="_parameter != null">
        and c.adminId = #{_parameter}
    </if>
</select>

13、加入原生符号

t.cdate >= #{startDate}

<![CDATA[]]>和转义字符

被<![CDATA[]]>这个标记所包含的内容将表示为纯文本，比如<![CDATA[<]]>表示文本内容“<”。
　　此标记用于xml文档中，我们先来看看使用转义符的情况。我们知道，在xml中，”<”、”>”、”&”等字符是不能直接存入的，否则xml语法检查时会报错，如果想在xml中使用这些符号，必须将其转义为实体，如”<”、”>”、”&”，这样才能保存进xml文档。

13.1、举例说明

 <sql id="pageSuffix">
    ) a where rownum <![CDATA[<=]]> #{end,jdbcType=INTEGER} ) b where b.rn <![CDATA[>]]> #{start,jdbcType=INTEGER}
  </sql>

14、一个条件匹配多个字段

<if test="userParam != null and userParam != ''">
    AND (t.userId = #{userParam}
          OR a.nickName LIKE CONCAT('%',#{userParam},'%' )
          OR b.realName LIKE CONCAT('%',#{userParam},'%' )
          OR c.realName LIKE CONCAT('%',#{userParam},'%' )
          OR t.customerId LIKE CONCAT('%',#{userParam},'%' )
          OR t.customerName LIKE CONCAT('%',#{userParam},'%'))
</if>

15、count详解

1、这样输出结果只有一行，因为count（*）本来代表的就是一个数字，本身就是一行

SELECT  count(*) as "count",idfa from apps_click_record a; # WHERE  a.keywordId = '169995' GROUP  by idfa ORDER BY count(*) DESC ;

2、使用group分组 （下面二者是一样的） 其实这里就表示分组之后每组的个数

SELECT  count(*) as "count",idfa from apps_click_record a WHERE  a.keywordId = '169995' GROUP  by idfa ORDER BY count(*) DESC ;
    
SELECT  count(idfa) as "count",idfa from apps_click_record a WHERE  a.keywordId = '169995' GROUP  by idfa ORDER BY count(*) DESC ;

3、count（*） 和 * 的查询 是错误的

下面是错误的

SELECT  count(*) as "count",* from apps_click_record a; 

4、查找数group by分组后的个数，使用嵌套

select count(*) from 
(
    select sum(b.id) from B b group by b.type
) m

18、delete删除表中数据

1,delete from user as u where u.userid=6; 错误
2,delete from user u where u.userid=6; 错误
3,delete from user where userid=6;  正确
4,delete u.* from user u where u.userid=6; 正确
5,delete u from user u where u.userid=6; 正确 

19、请求分页参数

19.1、congtroller

@GetMapping("getOriginData")
public Wrapper<?> getOriginData(String phone, Integer bankId, PageQuery pageQuery){

19.2、DOTO

@Setter
@ApiModel("分页对象")
@Accessors(chain = true)
public class PageQuery {

    @ApiModelProperty(value = "开始页数，从1开始",example = "1", required = true,dataType = "java.lang.Integer")
    private Integer pageNum = 1;
    @ApiModelProperty(value = "每页数量",example = "20", required = true,dataType = "java.lang.Integer")
    private Integer pageSize = 20;
   

    public Integer getPageSize() {
        return pageSize == null ? 20 : pageSize;
    }

    public Integer getPageNum() {
        return pageNum == null ? 1 : pageNum;
    }
}

20、timestamp 多个日期，如果可能为空，则建议使用datetime

重点：2、@Temporal(TemporalType.DATE)插入数据库中的日期会自动变成 00.00.00，应该使用TIMESTAMP，sql中是datetime

普通字段不要设置为timestamp，timestamp列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。如果我们在save实体的时候，没有给相关timestamp设置值，那么他就会自动由mysql将当前时间设置进去， cdate和udate，都是在java基础上控制的

正确数据

createTime datetime default null ,
clickTime datetime default null,

@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(value = "创建时间")
private Date createTime;

@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(value = "点击时间")
private Date clickTime;

下面这个不可能为空，所以也是正确的
cdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
udate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,



@Temporal(TemporalType.TIMESTAMP)
@Column(columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP",insertable = true,updatable = false)
@ApiModelProperty(hidden = true)
private Date cdate;

@UpdateTimestamp
@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(hidden = true)
private Date udate;

21、多条件排序

举例：订单降序 1，订单升序 2 ，成交额降序 3，成交额升序 4，

<select id="findCouponTaoKeDataByParam" resultType="com.duodian.youhui.data.coupon.CouponTaoKeItemGoodSummaryData">
  SELECT c.itemTitle,COUNT(c.itemId) as orderSize,sum(c.estimateAmount) AS sumEstimateAmount ,c.adzoneName,c.adzonePid,c.createTime,c.itemId  FROM  coupon_taoke_data c
    <where>
        c.dataType = 1 and  c.status = 1
        <include refid="findCouponTaoKeDataByParamSQL"></include>
    </where>
    GROUP by c.itemId,c.adzonePid

    <if test="order != null">
        <choose>
            <when test="order == 1">
                order by    orderSize DESC
            </when>
            <when test="order == 2">
                order by    orderSize asc
            </when>
            <when test="order == 3">
                order by   sumEstimateAmount DESC
            </when>
            <when test="order == 4">
                order by   sumEstimateAmount asc
            </when>
        </choose>
    </if>

    <if test="offset != null and limit != ''">
        limit #{offset}, #{limit}
    </if>

</select>

2、给排序添加非空条件

使用order byorderid desc实现降序时

orderid 为null数据的会排在数据的最后面；

但是，order by orderid升序时

orderid 为null的数据则会排在最前面

如果想要将orderid为null的数据排在最后，就需要加上is null。

select * from b_programme u order by u.orderid is null, u.orderid

23、参数传入为0，判断null的时候

23.1、第一种解决方法，不建议

id传值为0时(前提是id对应的类型为long 或者 Integer，String型无此问题)，发现并没有执行if里的sql，因为在mybatis中会自动把0当成‘’空字符串，所以建议以后传入这种类型的数据，最好还是不要传入0，可以将0改变为其他的数字，比如5等

    <if test="status == 5">
        and c.connectStatus = 0 and c.createAdminId is not NULL
    </if>

List<CouponItemGood> dataByParam(@Param("status") Integer status,

<if test="status !=null and status !=''">
    <if test="status == 5">
        and c.connectStatus = 0 and c.createAdminId is not NULL
    </if>
    <if test="status == 1">
        and c.connectStatus = 1 and c.status = 1 and c.createAdminId is not NULL
    </if>
    <if test="status == 2">
        and c.connectStatus = 1 and c.status = 0 and c.createAdminId is not NULL
    </if>
    <if test="status == 3">
        and c.status = 2  and c.createAdminId is not NULL
    </if>
    <if test="status == 4">
        and c.createAdminId  is NULL
    </if>
    <if test="status == 6 ">
        and c.status  not in (0)  and c.zhiboStatus=1
    </if>
    <if test="status == 7 ">
        and c.scheduleDealStatus = 0 and c.createAdminId is not NULL
    </if>
</if>

23.2、第二种解决方法

使用时增加多一个or status == 0判断

<if test="status != null and status !=  '' or status == 0">

24、查询随机数优化

24.1 、性能比较差的一个

RAND() 函数返回的是一个小于1的随机数

BY RAND() LIMIT 1

<!--失效之后，从选品库中随便找一个 -->
<select id="wechatSuiJiItemGoodImageUrl" resultType="java.lang.String">
    <![CDATA[
        SELECT c.erWeiMaInfoUrl FROM coupon_item_good c WHERE
          TIMESTAMPDIFF(DAY ,cdate,now())  < 5 ORDER BY RAND() LIMIT 1
    ]]>
</select>

24.2、优化

SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name

参数	描述
column_name	必需。要舍入的字段
decimals	非必需，规定返回的小数位数，如果不给值，则自动四舍五入取整取整，select round(100.9) ; 101

随机选择一个id，然后选择一个大于他的数据，limit控制为1
随机选择一个推广位，具体条件就是下面and中连接的and t1.status 开始

>     <select id="findUserCouponAdzone" resultType="com.duodian.youhui.entity.db.coupon.CouponAdzone">
   <![CDATA[
        SELECT *
        FROM `coupon_adzone` AS t1
          JOIN (SELECT ROUND(RAND() * (SELECT MAX(id)
                                       FROM `coupon_adzone`)) AS id) AS t2
        WHERE t1.id >= t2.id 

and t1.status = 1 AND  t1.adzoneType = 3  and (TIMESTAMPDIFF(HOUR, t1.userUseTime, now()) > #{timeDiff} OR t1.userInfoId IS NULL )
        ORDER BY t1.id ASC
        LIMIT 1;
   ]]>
</select>

25、 GROUP_CONCAT -sql语句将某一列的值查询成，逗号分隔的字符串

select GROUP_CONCAT(c.id) from coupon_item_good;


返回结果

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22,23,24,25,26,27,28,29,30,31,32,33

26、find_in_set 查询字段为逗号隔开的字段属性

字段 pnum为逗号隔开的字符串

1,2,3,4,21,9

select * from test  t where find_in_set(2,t.pnum) ;

27、when，case、进行条件判断

**

 case cp.ssid when 'aa' then '0' else'1' end as flag, "+
      " case cp.ssid when 'aa' then '3001' else '0000' end  as retCode,"+

"SELECT cp.ssid AS ssid," +
      " cp.accname AS accname," +
      " cp.PAYSUMUNTAX AS PAYSUMUNTAX," +
      " cp.PAYTAX AS PAYTAX," +
      " cp.payaftersum AS payAmt," +
      " cp.benetype AS benetype," +
      " cp.memo AS memo," +
    " case cp.ssid when 'aa' then '0' else'1' end as flag, "+
      " case cp.ssid when 'aa' then '3001' else '0000' end  as retCode,"+
      " to_char(sysdate,'yyyyMMdd') as drawDate,"+
      " to_char(sysdate,'HH24mmss') as drawTime"+
  " FROM CSIP_OA_PAYLISTINFO CPI" +
 " JOIN CSIP_OA_TASKFILE CT" +
   " ON CPI.PK_PAYLISTINFO = CT.PK_PAYLISTINFO" +
 " JOIN CSIP_OA_PAYLIST CP" +
   " ON CP.PK_TASKFILE = CT.PK_TASKFILE" +
" WHERE CPI.INNER_LISTNO = '"+inworkflowno+"'" ;

28、#和$项目中使用的区别

28.1、#{变量名}可以进行预编译、类型匹配等操作，#{变量名}会转化为jdbc的类型,${变量名}不进行数据类型匹配，直接替换。

select * from tablename where id = #{id}


假设id的值为12，其中如果数据库字段id为字符型，那么#{id}表示的就是'12'，如果id为整型，那么id就是12，并且MyBatis会将上面SQL语句转化为jdbc的select * from tablename where id=?，把?参数设置为id的值。



select * from tablename where id = ${id}
如果字段id为整型，sql语句就不会出错，但是如果字段id为字符型， 那么sql语句应该写成select * from table where id = '${id}'。

28.2、方式能够很大程度防止sql注入。因为#会自动转换，而&为直接替换,所以$方式无法防止sql注入。

28.3、项目中的使用，尽量使用# ，少用& 臭小子，明白了吧

#适用于普通的参数传入

$方式一般用于传入数据库对象，例如传入表名。

order为 A ASC, A DESC ,B DESC ，B asc数据，这里直接使用#是错误的

 <when  test="order != null">
                     order by  ${order}
  </when>

29、mysql除法

余数可以为0，得到的结果为NUll

SELECT 1/0 from dual ;

30、mysql 取小数 convert ROUND cast

1、convert（四舍五入）

select convert(10000,decimal(10,2));

# 四舍五入，decimal(10,2)后面的代表最大长度10以及保留的小数位数2
select convert(10569.3645,decimal(10,2));    #10569.36
select convert(10569.3665555,decimal(10,2)); #10569.37


2、round（自动四舍五入）
# round 第二个表示保留几位，如果之前有小数，那么不足的补上0。肯定是够的。
#        第二个如果为负数 -1 代表个位数为0             ROUND(114.6,-1) 结果 110，
#                      -2 代表个位数和十分位 为0 例如 ROUND(114.6,-2) 结果  100
# 100.35  100 0.60  110
SELECT ROUND(100.3465,2),ROUND(100,2),ROUND(0.6,2),ROUND(114.6,-1) ;


3、cast，强制转换
select cast(10*1/4 as decimal(18,2)) from dual

31、子查询

31.1、子查询中的变量是不可以是哦用外面的变量的

比如下面的 #{userInfoId} 是不可以使用u的。只能是使用已知的参数

 select
               o.payAmount,
               o.estimateAmount,
               (o3.notValidOrderSize +o.orderSize) as orderSize
        from user_info u
                       left join  (select    IFNULL(count(o1.orderNo),0 )  as orderSize ,
                                             IFNULL(sum(o1.estimateAmount) ,0)  as  estimateAmount ,
                                             IFNULL(sum(o1.payAmount) ,0) as payAmount,
                                             o1.userInfoId as userInfoId
                                     from    user_order o1
                                     where   o1.userInfoId = #{userInfoId} and
                                             o1.orderStatus !='订单失效'
                                  )
               o on o.userInfoId = u.id
                       left join   (select IFNULL(count(o2.orderNo),0 ) as notValidOrderSize,
                                             o2.userInfoId as userInfoId
                                      from   user_order o2
                                      where o2.orderStatus ='订单失效'  and o2.userInfoId = #{userInfoId}
                                   )
              o3 on o3.userInfoId = u.id
        where u.id = #{userInfoId};
 u.id = 

32、sql查询结果加法

  select
               o.payAmount,
               o.estimateAmount,
               (o3.notValidOrderSize +o.orderSize) as orderSize
        from user_info u
        

33、某一个或者多个字段查找重复数据的sql语句

33.1、表中有id和name 两个字段，查询出name重复的所有数据

group by username   having count(*) > 1
                             

select * 
from healerjean a 
where (a.username) in (
                    select username 
                    from healerjean 
                    group by username 
                             having count(*) > 1
                    )

33.2、删除分组中重读的数据，只保留id最小的记录

delete from healerjean 
where username in (
                    select username 
                    from healerjean 
                    group by username 
                    having count(username) > 1
                    )
      and id not in (
                    select min(id)   #每组中最小的数据
                    from healerjean 
                    group by username 
                    having count(username)>1)

33.3、查找表中多余的重复记录（多个字段）

下面这个就保证了，肯定是二者同时存在才会出现count(*)>1 
如果没有having则，会出现先根据peopleId分组，然后组内，再根据seq 分组。

group by peopleId,seq having count(*) > 1
                            
select * 
from vitae a
where (a.peopleId,a.seq) in (
                            select peopleId,    
                                   seq      
                            from vitae 
                            group by peopleId,seq 
                            having count(*) > 1)

33.4、选择表中多余的重复记录（多个字段），只留有id最小的记录

select * 
from vitae a
where (a.peopleId,a.seq) in (
                            select peopleId,    
                                   seq      
                            from vitae 
                            group by peopleId,seq 
                            having count(*) > 1)
and id not in           ( select min(id)   
                            from vitae 
                            group by peopleId,seq 
                            having count(*) > 1)
 

感兴趣的，欢迎添加博主微信，

哈，博主很乐意和各路好友交流，如果满意，请打赏博主任意金额，感兴趣的在微信转账的时候，备注您的微信或者其他联系方式。添加博主微信哦。


请下方留言吧。可与博主自由讨论哦

支付包	微信	微信公众号