欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql--常用基本语法

程序员文章站 2022-03-02 16:34:13
常用mysql用到语法积累mybatis--mysql常用语法1、包含指定sql,需要外部定义使用 定义:id, book_id,name,parent_id2、like用法name like concat('%',concat(#{name},'%'))3、判断字符串不为空,添加sql

常用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