MYSQL中lower，CONCAT，ESCAPE，DISTINCT用法（模糊查询）

MySQL学习笔记

：lower，CONCAT，ESCAPE用法
lower(str)——根据当前字符集映射返回字符串str，并将所有字符更改为小写。默认值是latin1（西欧cp1252）。

CONCAT 函数用于将两个字符串合并为一个字符串
示例：

mysql> select concat(‘a’,‘b’,‘c’);
±--------------------+
| concat(‘a’,‘b’,‘c’) |
±--------------------+
| abc |
±--------------------+
1 row in set (0.00 sec)

ESCAPE用法:
ESCAPE 关键字的主要作用就是指定一个字符替代“\”的作用
ESCAPE用法：转自

<select id="queryInstructmentPages" resultMap="smddx">
    SELECT * FROM "BIZ_PLATFORM_OPREATION"."INSTRUCTMENT_MANAGEMENT" t1
    <where>
      <if test="identifier != null and identifier != ''">
        AND (LOWER(t1.IDENTIFIER) LIKE CONCAT('%',lower(#{identifier}),'%') ESCAPE '\' OR
      </if>
      <if test="instructmentName != null and instructmentName != ''">
        LOWER(t1.INSTRUCTMENT_NAME) LIKE CONCAT('%',lower(#{instructmentName}),'%') ESCAPE '\' )
      </if>
      <if test="unit != null and unit != ''">
        AND LOWER(t1.UNIT) LIKE CONCAT ('%',#{unit},'%')
      </if>
      <if test="status != null and status != ''">
        AND t1.STATUS = #{status}
      </if>
      <if test="instructmentType != null and instructmentType != ''">
        AND t1.INSTRUCTMENT_TYPE = #{instructmentType}
      </if>
      AND EXISTS (SELECT 1 FROM "BIZ_PLATFORM_OPREATION"."INSTRUCTMENT_MANAGEMENT" WHERE t1.UUID = UUID
      GROUP BY UUID HAVING t1.UPDATE_TIME = MAX(UPDATE_TIME))
    </where>
  </select>

DISTINCT用法：
distinct的意思是不重复，可以用在统计里

释：查询数据库中所有UNIT的数据且去除重复数据；功能类似于group by

 <select id="selectUList" resultType="java.lang.String">
    SELECT DISTINCT UNIT
    FROM "BIZ_PLATFORM_USER"."DEVICE_MANAGEMENT"
    WHERE UNIT IS NOT NULL
  </select>