mysql--常用基本语法

常用mysql用到语法积累

mybatis--mysql常用语法
1、包含指定sql，需要外部定义
使用
<include refid="Base_Column_List" /> 
定义：
<sql id="Base_Column_List">
	id, book_id,name,parent_id
</sql>

2、like用法
name like concat('%',concat(#{name},'%'))

3、判断字符串不为空，添加sql
<if test="bookId != null and bookId != ''">
	and book_id =#{bookId}
</if>

4、多条件，执行其中一个（如：parentId为空，执行when中的sql，否则，执行otherwise的sql）
<choose>
	<when test="parentId != null and parentId != '' ">
		and parent_id = #{parentId}  and id != '0'
	</when>
	<otherwise>
		and parent_id is null or parent_id = ''
	</otherwise>
</choose>

5、等于指定的值执行
<if test='period == "1"'>
	and grade &lt;= '6'
</if>

6、小于<，小于等于，大于，大于等于，&，单引号，双引号
&lt; &lt;= &gt; &gt;= &amp; &apos; &quot;
也可以
大于等于   <![CDATA[ >= ]]>
小于等于   <![CDATA[ <= ]]>

7、字符串转数字
CAST(t.CLASS_NO AS signed)

8、传入字符串的in语法
（1）、List<String> 用法，主要集合注意为空的情况，可在外面嵌套一层空判断（<if test="ids != null and ids.size() > 0">）
and t.period in 
<foreach collection="ids" index="index" item="id" open="(" separator="," close=")">
	#{id}
</foreach>
（2）List<Object>
<if test="orgSet != null and orgSet.size()>0">
	and t.belong_unit in
	<foreach collection="orgSet" index="index" item="org" open="(" separator="," close=")">
		#{org.id}
	</foreach>
</if>


9、获取最大排序值
MAX(cast(ec.CLASS_NO as SIGNED)) as classNo 

10、更新写法
<update id="backClass">
	update t_class t 
		set t.del_flag = #{delFlag}, 
		t.is_end = #{isEnd},
		t.is_his = #{isHis},
		t.MODIFY_TIME = #{modifyTime} 
	WHERE t.ID = #{id} 
</update>

11、删除写法
<delete id="deleteSubjectIsNullDataByTeacherId">
	delete from t_class_teacher_subject where teacher_id = #{teacherId} and (subject_id is null  or subject_id = '')
</delete>

12、时间类型或者时间类型字符串比较大小（between包含边界值）
（1）、直接日期比较
 and art.CREATE_TIME_ BETWEEN #{leaveMain.applyStartTimeForSearch} and #{leaveMain.applyEndTimeForSearch}
（2）、将日期转指定格式字符串比较
 AND DATE_FORMAT(t.begin_date, '%Y-%m-%d') &lt;= #{leaveDay}
AND DATE_FORMAT(t.end_date, '%Y-%m-%d') &gt;= #{leaveDay}

13、获取时间类型的年，月，比较
and YEAR(y.change_date) = #{year}

14、按照分组连接，之后必须要和GROUP BY 连用
GROUP_CONCAT(eta.award_name  separator',') awardName,
。。。
GROUP BY t.id

15、判断为空，返回指定数据
IF(MAX(t.sn) IS NULL,1, MAX(t.sn)+1) 

16、返回查询数量
<select id="getMaxSnForInspectionItem" resultType="java.lang.Integer">

17、返回指定的内容（如果r.period为1，返回小学部，否则返回中学部给periodTxt）
(
	CASE r.period
	WHEN 1 THEN
			'小学部'
	ELSE
			'中学部'
	END
) periodTxt,

18、常用日期方法
-- 获取当前日期（yyyy-MM-dd HH:mm:ss），当前日期（yyyy-MM-dd），当前时间（HH:mm:ss）
select NOW(),CURDATE(),CURTIME(),DATE(NOW()),EXTRACT(YEAR FROM NOW());
-- 获取日期时间值输出日期
select DATE(NOW());
-- 获取日期的年，月，周，日（MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH）
select EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(WEEK FROM NOW()),EXTRACT(DAY FROM NOW());
-- 获取两天后的日期
select DATE_ADD(now(),INTERVAL 2 DAY);
-- 获取两天前的日志
select DATE_SUB(now(),INTERVAL 2 DAY); 
-- 获取两个日期之间的天数。
SELECT DATEDIFF(NOW(),'2008-08-08') AS 北京奥运会已经过去多少天;

19、date_format方法（第一个参数可以是日期，也可以是字符串）
select date_format('2018-06-26','%w')       获取日期是周几（0是星期日）
format如下：%a：缩写星期名
%b：缩写月名
%c：月，数值
%D：带有英文前缀的月中的天
%d：月的天，数值(00-31)
%e：月的天，数值(0-31)
%f：微秒
%H：小时 (00-23)
%h：小时 (01-12)
%I：小时 (01-12)
%i：分钟，数值(00-59)
%j：年的天 (001-366)
%k：小时 (0-23)
%l：小时 (1-12)
%M：月名
%m：月，数值(00-12)
%p：AM 或 PM
%r：时间，12-小时（hh:mm:ss AM 或 PM）
%S：秒(00-59)
%s：秒(00-59)
%T：时间, 24-小时 (hh:mm:ss)
%U：周 (00-53) 星期日是一周的第一天
%u：周 (00-53) 星期一是一周的第一天
%V：周 (01-53) 星期日是一周的第一天，与 %X 使用
%v：周 (01-53) 星期一是一周的第一天，与 %x 使用
%W：星期名
%w：周的天 （0=星期日, 6=星期六）
%X：年，其中的星期日是周的第一天，4 位，与 %V 使用
%x：年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y：年，4 位
%y：年，2 位


20、在字符串中查找指定的字符串（前为字符串，后为目标字符串，查不到返回0）
SELECT INSTR("abcd",'b');

21、常用字符串方法
-- 转小写字符串
select lower('SQL Course');
-- 转大写字符串
select upper('Use MYsql');
-- 字符串连接
select concat('My','S','QL');
-- 字符串连接特殊情况（包含null，则结果为null 和 包含数字，数字转字符串数字）
select  concat('My',null,'QL');
-- 用特定中间符号，连接字符串（用；连接字符串）
select concat_ws(';','First name','Second name','Last name');
-- 特定中间符号连接字符串特殊情况（包含null，去除null，继续连接）
select concat_ws(',','id',null,'name');
-- 字符串截取（正，从左截取；负，从右截取）  substring和substr一样
select substring('hello world',-5);
-- 字符串截取指定长度
select substr('hello world',5,3);
-- 返回字符串长度（字符占一个，中文字符，数据库utf8是3，gbk是2，本例结果4，6）
select length('text'),length('你好');
-- 返回字符串字符长度（中英文都只占一个，本例结果4，2）
select char_length('text'),char_length('你好');
-- 返回目标字符串在指定字符串中第一次出现的位置（没有返回0）
select instr('foobarbar','bar')
-- 源字符串的左边填充给定的字符??到指定的长度len，返回填充后的字符串
select lpad('hi',5,'??');
-- 在源字符串的右边填充给定的字符??到指定的长度len，返回填充后的字符串
select rpad('hi',6,'??');
-- 指定格式字符串（默认删除左右的空格）
select trim('  bar  ');
-- 删除左空格，删除右空格
SELECT  ltrim('   barbar   ') rs1, rtrim('   barbar   ') rs2;
-- 删除两边的x
select trim(both 'x' from 'xxxbarxxx');
-- 删除左边的x
select trim(leading 'x' from 'xxxbarxxx');
-- 删除右边的x
select trim(trailing 'x' from 'barxxyzxxx');
-- 替换自定字符串（以为将用Ww,替换字符串中的w）
select replace('www.mysql.com','w','Ww');
-- 将字符串重复三次输出（此例返回：MySQLMySQLMySQL）
select repeat('MySQL',3);
-- 字符串反转
select reverse('abcdef');
-- 数字输出指定的小数位
SELECT format(12332.123456, 4),format(12332.2,0);
-- 输出三个空字符，如‘   ’
select space(3);
-- 从左，返回5个字符
select left('chinaitsoft',5);
-- 从右，返回5个字符
select right('chinaitsoft',5);
-- 字符串比较大小  相等返回0，第一个大返回1，第一个小返回-1
SELECT strcmp('text', 'text2'),strcmp('text2', 'text');


22、自定义排序 （FIELD参数，指定name列，按照后面的先后顺序进行数据排序）
SELECT  *  FROM A  ORDER  BY  FIELD(name,  '王五',   '张三',   '李四')   ASC

23、返回数据带有集合字段的对象（定义map最为关键，本例createUser为List<String>字段，flowList为List<Object>字段）
定义Map如下：
<resultMap id="MatterListResultMap"
	type="com.dto.cloud.statistics.MatterPageResponseDto">
	<id column="THIRD_ORDER_NO" property="matterId" jdbcType="VARCHAR" />
	<result column="BUSINESS_SCENE" property="matterName" jdbcType="VARCHAR" />
	<result column="APP_NAME" property="appName" jdbcType="CHAR" />
	<result column="AREA_NAME" property="areaName" jdbcType="VARCHAR" />
	<result column="DEPT_NAME" property="deptName" jdbcType="VARCHAR" />
	<result column="SIGN_PROVIDER_CODE" property="signProviderCode" jdbcType="VARCHAR" />

	<!-- property表示集合类型属性名称，ofType表示集合中的对象是什么类型 -->
	<collection property="createUser" ofType="java.lang.String">
		<constructor>
			<arg column="createUser"/>
		</constructor>	
	</collection>
	
	<!-- property表示集合类型属性名称，ofType表示集合中的对象是什么类型 -->
	<collection property="flowList"
		ofType="com.MatterPageFlowResponseDto">
		<id column="F_ID" property="id" jdbcType="VARCHAR" />
		<result column="FLOW_ID" property="flowId" jdbcType="VARCHAR" />
		<result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
	</collection>
</resultMap>

定义查询列：
<sql id="matterPageCloumn">
	f.THIRD_ORDER_NO THIRD_ORDER_NO,
	f.BUSINESS_SCENE BUSINESS_SCENE,
	a.APP_NAME APP_NAME,
	a.AREA_NAME AREA_NAME,
	a.DEPT_NAME DEPT_NAME,
	f.SIGN_PROVIDER_CODE SIGN_PROVIDER_CODE,
	t.SIGN_USER_NAME createUser,
	f.ID F_ID,
	f.FLOW_ID FLOW_ID,
	f.CREATE_TIME CREATE_TIME
</sql>

定义sql
<!-- 根据分页的办件id获取办件统计列表数据 -->
<select id="getPageMatterList" resultMap="MatterListResultMap" > 
	select <include refid="matterPageCloumn" /> from t_flow f 
		left join t_app a on f.APP_ID = a.APP_ID 
		LEFT JOIN t_task t on f.FLOW_ID = t.FLOW_ID 
	where f.IS_DELETE = '0'  
	<if test="appId != null and appId != ''">
		and a.APP_ID = #{appId} 
	</if>
	<if test='cloudType == "1"'>
		and f.SIGN_PROVIDER_CODE = 'esign' 
	</if>
	<if test="endTime != null and endTime != ''">
		and t.CREATE_TIME &lt;= #{endTime} 
	</if>
	<foreach collection="list" item="item" index="id"
		open="and f.THIRD_ORDER_NO in (" close=")" separator=",">
		#{item.matterId}
	</foreach>
	order by f.CREATE_TIME desc 
</select>

学海无涯苦作舟！

本文地址：https://blog.csdn.net/qq_34207422/article/details/107322443