常见sql语句在mybatis里面的编写

1.转义字符

符号	小于	小于等于	大于	大于等于	不等于	和
原符号	<	<=	>	>=	<>	&
替换符号	&lt;	&lt;=	&gt;	&gt;=	&lt;&gt;	&amp;

2.常用函数

函数名称	数据库	对应函数
时间格式化		DATE_FORMAT( time, '%Y-%m-%d' )
字符串查找		FIND_IN_SET( '李逍遥', users )
字符串连接		CONCAT( price, '元/', unit )
模糊查询	MYSQL	LIKE CONCAT('%', #{name}, '%' )
模糊查询	ORACLE	LIKE '%'||#{name}||'%'

 3.查询

SELECT id, name, age FROM student

SELECT
    id,
    name,
    age
FROM
    student
<where>
    id = #{id}
    <if test="name != null and name != ''">
        and name = #{name}
    </if>
    <if test="age != null and age !=0">
        and age = #{age}
    </if>
</where>

4.添加

INSERT INTO
    student ( name, phone, age )
VALUES
    ( #{name}, #{phone}, #{age} )
ON DUPLICATE KEY UPDATE
    update_time = now()

<insert id="exportUser" parameterType="com.xxxx.entity.Student" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        name,
        <if test="phone != null and phone != ''">
            phone,
        </if>
        <if test="age != null and age != 0">
            age,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        #{userName},
        <if test="phone != null and phone != ''">
            #{phone},
        </if>
        <if test="age != null and age != 0">
            #{age},
        </if>
    </trim>
    ON DUPLICATE KEY UPDATE
        name = #{name}, update_time = now()
        <if test="phone != null and phone != ''">
            ,phone = #{phone}
        </if>
        <if test="age != null and age != 0">
            ,age = #{age}
        </if>
</insert>

5.修改

UPDATE
    student
SET
    name = #{name}, phone = #{phone}, update_time = now()
WHERE
    id = #{id}

不建议使用,如果要修改的数据是空值，就会出现问题，可以直接再修改实体类上把对应字段转换为空字符串
UPDATE student
<trim prefix="set" suffixOverrides=",">
    name = #{name}, update_time = now(),
    <if test="phone != null and phone != ''">
        phone = #{phone},
    </if>
    <if test="age != null and age != 0">
        age = #{age},
    </if>
</trim>
WHERE id = #{id}

<update id="updateStudent" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        UPDATE student SET name = #{item} WHERE id = 1
    </foreach>
</update>

6.删除

DELETE FROM student WHERE id = #{id}