1、标签分类

在我看来标签大致可以分为crud类和动态标签类。下面讲讲我对这两类标签的理解。

2、crud类

crud类标签总共四个，对应着增查改删。下面依次讲讲这四个。

2.1、create(增)

在sql中使用<insert>标签实现数据库的数据增加，通常是不设置返回值，偶尔会设置一个布尔值来判断是否成功插入以及设置返回自增的主键值以便进行后续操作。

下面是一个范例

<insert id="testinsert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        <selectKey keyProperty="id" order="AFTER" resultType="integer">
            select LAST_INSERT_ID()
        </selectKey>
INSERT XXXX....
</insert>

其中id是必备的，这个绑定了这条sql和mapper层的方法。当想要获取自增主键的值得时候，需要用到useGeneratedKeys="true"来开启这个功能，同时使用column来指定数据库这个字段是谁和用property来指定这个值赋给谁。

此外insert标签还支持和其他标签混合使用，能一起使用的标签如下：

<!ELEMENT insert (#PCDATA | selectKey | include | trim | where | set | foreach | choose | if | bind)*>
<!ATTLIST insert
id CDATA #REQUIRED
parameterMap CDATA #IMPLIED
parameterType CDATA #IMPLIED
timeout CDATA #IMPLIED
flushCache (true|false) #IMPLIED
statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
keyProperty CDATA #IMPLIED
useGeneratedKeys (true|false) #IMPLIED
keyColumn CDATA #IMPLIED
databaseId CDATA #IMPLIED
lang CDATA #IMPLIED
>

selectKey 会将 SELECT LAST_INSERT_ID()的结果放入到传入的model的主键里面，keyProperty 对应的model中的主键的属性名，因为它跟数据库的主键对应order AFTER 表示 SELECT LAST_INSERT_ID() 在insert执行之后执行，BEFORE表示SELECT LAST_INSERT_ID() 在insert执行之前执行。

2.2、read(查)

使用<select>标签从数据库中获取数据，返回值各式各样，可以是实体对象可以是数字可以是布尔值等等。

下面是一个查询范例：

<select id="portGetWharfClean" resultMap="cleanMapper">
        select <include refid="mapper"/>
        from user
        where id=#{id}
</select>

其中标签中必要的属性是id，当返回值结构较为复杂的时候，需要用到resultmap，多数时候还是使用的resulttype。

使用时需要注意映射字段和查询字段的对应关系，否则会出现数据库字段不为空，但最后响应的结果集存在空数据。

其中where多用标签，使用标签有个好处是会自动舍弃不合适的条件

2.3、update(更新)

使用update标签以更新数据库中的数据，返回值为更新的数目。

下面是一个更新范例：

<update id="portGetWharfClean">
        update xxx set a=xx,b=xxx...
        where xxxx
</update>

依据条件更新满足条件的数据

通常set也会用标签来替代，在set标签中，使用if标签更新符合条件的内容。对于mybatisplus则没这么复杂，通过配置即可忽略null值，只更新有值的部分。

2.4、delete(删除)

使用delete标签来指定删除数据库的数据。返回值是删除的数量。

下面是一个更新范例：

<delete id="portGetWharfClean">
   delete from xxx where xxxx
</delete>

删除分为逻辑删除和物理删除。逻辑删除即从逻辑上删除，但实际上还是存在的，具体方法是在数据库中新增一个字段，来标记这条数据是否被删除，逻辑删除执行的实质上是更新数据。

物理删除即是，直接从数据库中删除数据，数据库中存放的都是有效数据。

3、其他类

3.1、sql(复用代码)

sql标签能够创建一个可供其他地方调用的代码块，提高了代码复用率，降低了代码的重复度。

下面是个sql标签第的范例：

<sql id="aa" >        
	user.id,user.name,user.address
</sql>

sql标签封装了共同的查询体，使用include标签进行引用，引用时通过id和refid进行关联。sql片段可以不只是查询属性，也可以包含crud的开头

3.2、include(复用代码)

include标签能够引用创建好的sql片段。

下面是一个include标签的范例：

select <include refid="aa" /> from user where id=#{id}

icnlude标签和sql标签都能够传递参数，但是实际上使用不多，这里简单介绍一些。

include可以通过porperty标签动态传递参数，传参同样分为$和#传参，#会将参数处理成字符串再进行传递，即多了个预编译的过程，$则不会进行预编译，参数是什么传递进去的就是什么。

下面是一个简单的范例：

select
  <include refid="aa">
    <property name="sqlval" value="1"/>
  </include>
from user
where id=#{id}

<sql id="aa">
  user.id,user.name
  <if test="${testval}==1"> ,user.address</if>
</sql>

当传入的参数为1时，才会查询address这个属性，反之则不会查询。

一般很少会这么用，但是也需要了解能这么用。

3.3、foreach(循环)

foreach的使用场景大多是批量插入或者用于构建in (x,x,x,x)这种语句的查询。通过foreach标签构建values后面的插入数据或者构建in后面的条件集合。

下面是一个范例：

insert into user (id,name,age) values 
<foreach collection="list" item="item" separator="," index="index">
(id,name,age)        
</foreach>

select * from user where 
<foreach collection="list" item="item" separator="," open="(" close=")" index="index">
#{id}        
</foreach>

colletion：表示需要遍历的集合，可以是个集合可以是个数组。

item：遍历集合中的单个对象。当参数是map时，表示map的value

separator：分隔符，每次遍历之后中间加入的分隔符。

open：循环开始时，在左侧加入的元素

close：循环结束时，在右侧加入的元素

index：索引，当前循环次数。当入参是一个map的时候，index也是map的key

insert into equip
<foreach collection="map" separator="," open="(" close=")" index="index">
  <trim prefixOverrides="-- | ;" suffixOverrides="-- | ;">${index}</trim>
</foreach>
values
<foreach collection="list" separator="," item="item">
  <foreach collection="item" open="(" close=")" separator="," item="value">
    <choose>
      <when test="@com.SelfQueryServiceImpl@judge(value) ==1 ">1</when>
      <when test="@com.SelfQueryServiceImpl@judge(value) ==0 ">0</when>
      <otherwise>#{value}</otherwise>
    </choose>
  </foreach>
</foreach>

这个地方有些混乱，入参是两个，一个是map，另一个是list<map>，遍历map时，通过获取他的index（key），来拼接表的列名，再通过遍历list'<map>来拼接value的值，在遍历list时，第一个循环的item是map对象，第二个循环时循环map内部，item是他的value，index是他的key。两个test是写着玩的，后面会用到。

trim的作用是去除掉字符串中的--和;防止sql注入，这个地方是使用的$所以需要更加注意，这个后面详细讲。

3.4、if(逻辑判断)

if通常用于where条件中，使条件在合适的机会显示。if标签中必须要有的属性是test用于判断条件，相同的的在when中也是有test这一个属性的，两者的使用是一样的：

1、简单的逻辑判断：例如test="1==1"，恒成立

2、判断参数：例如test="user.id==1"，获取入参条件，进行判断

3、引用自定义的方法：例如上面的sql，自定义一个方法，返回值是任意的，但最后在sql标签中需要进行一个比较，最终使得test的表达式返回一个布尔值。

下面是几个简单的示例：

##入参是一个user对象

<if test="1==1">user_id=#{user.id} </if>

<if test="user.id!=null and user.id != ''">user_id=#{user.id}</if>

##这条暂且存疑，设想是返回一个布尔值，没尝试过，上面的返回数字是经过测试的
<if test="com.test@judegUser(user.id)">user_id=#{user.id}</if>

3.5、where(条件判断)

当where标签中第一个语句是and时，会去掉and，当where标签中有内容时，会添加上where关键字。

当涉及到条件判断时，一般推荐使用where标签而非关键字。

下面是简单的范例：

select * from user where 
<if test="xxx">id=#{id}</if>
<if test="aaa">and name=#{name}</if>

select * from user
<where>
<if test="xxx">id=#{id}</if>
<if test="aaa">and name=#{name}</if>
</where>

上面两个语句，第一个当id所在的语句不满足条件但name满足条件时，会变成where and name=#{name}，以及当两个都不满足条件时会变成，selecture* from user where。戛然而止，两个语句都是会报错的。

而下一个则不会有这种问题，当id不满足条件，会自动去掉and name=#{name}中的and，编程where name=#{name}，以及当均不满足时，会变成select * from user。

3.6、set(sql修改)

set标签通常用在update语句中，作用和where类似，都能够去除多余的东西，通常时去除最后一个修改对象后面的","。

下面是两个范例：

update user set id=#{id},name=#{name}...

update user set 
<if test="xx">id=#{id},</if>
<if test="xx">name=#{name},</if>
<if test="xx">age=#{age}</if>

update user
<set>
  <if test="xx">id=#{id},</if>
  <if test="xx">name=#{name},</if>
  <if test="xx">age=#{age}</if>
</set>

三个语句都没有错误，但是第二个在某些特定情况下发生意料之外的错误。

当age不满足条件时，语句结尾是“,”，会抛出异常。如果通过set标签的话，当语句结尾是“,”标签会将其去除。

3.7、trim(截断、修改)

trim标签有四个属性，四个属性是可以同时使用的。

prefix：在前面添加内容，和foreach的open类似

suffix：在后面添加内容，和foreach的close类似

prefixOverrides：去掉前面内容

suffixOverrides：去掉后面内容

trim的适用场景大多数时候是类似3.3中的对入参进行规范化，例如去除前后空格、去除可能引起sql注入的参数，但发散一下思维，trim还能这么用：

update user
<trim prefix="set" suffixOverrides=",">
  <if test="id!=null and id!=''">id=#{id},</if>
  <if test="id!=null and id!=''">name=#{name},</if>
  <if test="id!=null and id!=''">age=#{age},</if>
</trim>

利用标签能够在最开始和结尾添加和删除指定字符的特性，能够通过trim构建一个update的语句，使得trim起到一个set标签的做用。

3.8、bind(计算赋值)

bind这个标签作用有适配不同数据库的兼容问题，以及改变参数值的作用。

下面是一个范例：

select * from user where
<bind name="name" value="'%'+name+'%'" />
name  like #{name} 

在mysql和oracle中都可以使用concat关键字进行字符串的拼接，但是语法上存在差异。所以当发生数据库迁移之后，旧的sql就不适用了，使用bind的话则没这个问题。

3.9、choose-when-otherwise(选择)

类似java中的switch循环，当满足when和otherwise中的任意一个，其他条件将不再判断。

下面是一个范例：

select * from user where
<choose>
  <when test="id==1"> id = #{id} </when>
  <when test="id!=1"> name like "z%" </when>
  <otherwise> age > 15 </otherwise>
</choose>

没什么逻辑可言，只是一个关于如何使用这个标签的sql。when相当于switch的case，otherwise相当于default。如果存在多个满足的条件，只有第一个条件会生效，其他的则会被忽略。

3.10、组合范例

场景，需要对数据库进行批量更新数据。

方法一：通过foreach构建多个update语句

<foreach collection="list" item="item" index="index" open="" close="" separator=";">
  update course
  <set>
    name=${item.name}
  </set>
  where id = ${item.id}
</foreach>

方法二：代码里面循环更新数据。

//伪代码
list.foreach(o->{
updateOne(o);
})
循环遍历集合，每次只更新一条

方法三：批量 插入或更新

//伪代码,前提是有一个唯一键，依据唯一键进行插入或者更新，有相同唯一键则更新，无则插入
insert into user (id,name,age) values
<foreach ....>
  (#{id},#{name},#{age})
</foreach>
on duplicate key update
id=values(id),name=values(name).....

方法四：利用标签组合sql

update user
<trim prefix="set" suffixOverrides=",">
  <trim prefix="name =case" suffix="end,">
    <foreach collection="list" item="item" >
      <if test="item.name!=null">
         when id=#{item.id} then #{item.name}
      </if>
    </foreach>
  </trim>
  <trim prefix="age=case" suffix="end,">
    <foreach collection="list" item="item" index="index">
      <if test="item.age!=null">
        when id=#{item.id} then #{item.age}
      </if>
    </foreach>
  </trim>
</trim>
where id in
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
##理论上是可以的。写了还没试过，最后构建出来的是update user set name= case when then end,... where id in (1,2,3,4,5...)

4、mysql常用函数

4.1、聚合函数

count（）：计数，select count（*） from user

sum（）：求和，select sum(age) from user

avg（）：平均值

max（）：最大值

min（）：最小值

4.2、数值型函数

abs（）：获取绝对值

rand（）：返回0到1 的随机数

round（x，y）：四舍五入的小数，y为保留位数

4.3、字符串型函数

concat（x,y,z...）：字符串拼接

length（）：计算字符串长度

trim()：删除字符串两边的空格

lower（）：转小写

upper（）：转大写

substring（s,n,len）：截取字串

replace(s,s1,s2)：用s2替换s中的s1

left(s，len)：截取左边len个字符串

right(s,len)：截取右边len个字符串

4.4、日期类函数

DAY（）：返回指定日期位于当月第几天

DAYOFYEAR()：返回指定日期位于当年第几天

MONTH（）：返回指定日期为第几月

YEAR（）：返回指定日期的年份

DATEFORMAT(date,format)：按照format的格式返回日期格式

NOW()：返回当前时间

ADDDATE(date,INTERVAL expr unit )：在date的基础上增减expr 单位时间，expr正数负数都行，unit 单位，DAY\MONTH等，INTERVAL关键字，必须有的；等效函数DATE_ADD(date,INTERVAL expr unit)，用法一样的。