1.转义字符
符号 | 小于 | 小于等于 | 大于 | 大于等于 | 不等于 | 和 |
原符号 | < | <= | > | >= | <> | & |
替换符号 | < | <= | > | >= | <> | & |
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}
O(∩_∩)O开心就好O(∩_∩)O