Sql函数记录

韩_师兄
426 阅读10分钟

记录日常中常用的函数,想记录成为函数百科.持续更新ing

0 数据库基础知识

0 Sql执行顺序

关键字顺序
FROM1
ON2
JOIN3
WHERE4
GROUP BY5
WITH6
HAVING7
SELECT8
DISTINCT9
ORDER BY10
LIMIT11
(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

1 查询数据库版本

select VERSION()

在这里插入图片描述

1 group_concat

功能: 将group by产生的同一个分组中的值连接起来，返回一个字符串结果

语法: group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

tips: []内为可选, 分隔符不默认是逗号,

应用场景: 常见一对多表结构关系,将根据一表方分组,查询对应多表某列数据,以规定的格式拼接成字符串

eg:

# 查询用户表所有信息
SELECT * FROM `userinfo`

image-20210221145356847

# 根据brand分组, 查询每个brand的title,并以逗号,拼接
select brand ,GROUP_CONCAT(title) from `userinfo` GROUP BY brand;

image-20210221145437843

# 根据brand分组, 查询每个brand的title,并以美元符号$拼接
select brand ,GROUP_CONCAT(title separator '$') from `userinfo` GROUP BY brand;

image-20210221145541134

2 @i:=@i+1

Oracle中有一个伪列==rownum==,在查询结果时生成一组递增的序列号,可以用于分页截断等作用.

Mysql中没有该伪列,可通过上述模拟生成一列自增序号.

功能: 在查询结果时生成一组递增的序列号

语法: SELECT (@i:=@i+1) as rownum , 查询字段 FROM 查询表名 ,(select @i:=0) as a

tips:

  • (select @i:=0) 定义初始序号为0(可更换为其他如10,20,...),且必须起别名,别名无规定,不与查询表名相同即可.
  • (@i:=@i+1) 定义递增规则,每次递增为1(可更换为5,10,...)
# 查询用户表所有信息
SELECT * FROM `userinfo`

image-20210221145356847

# 添加自增序列号 初始为0,每次递增1
SELECT (@i:=@i+1) as rownum , u.* FROM `userinfo` u ,(select @i:=0) a

image-20210221151516913

# 添加自增序列号 初始为10,每次递增5
SELECT (@i:=@i+5) as rownum , u.* FROM `userinfo` u ,(select @i:=10)  a

image-20210221152437821

在多表联查,在有分组和排序中,若想order by某字段之后再添加序号列,必须把原sql用括号套起来,放到from后面 和, ( select @i := 0 ) init

错误示范:

SELECT
	( @i := @i + 1 ) as rownum,
	A.* 
FROM
	userinfo A
	LEFT JOIN `user` B ON A.id = B.id,
	( select @i := 0 ) init 
GROUP BY
	A.id 
ORDER BY
	A.price DESC

在这里插入图片描述正确示范:

SELECT
	( @i := @i + 1 ) as rownum,
	c.* 
FROM
	(
	SELECT
		A.* 
	FROM
		userinfo A
		LEFT JOIN `user` B ON A.id = B.id 
	GROUP BY
		A.id 
	ORDER BY
		A.price DESC 
	) c,
	( select @i := 0 ) init

在这里插入图片描述

3 order by 排序问题

功能: 将查询结果,按照指定的规则排序 语法: order by colum [asc | desc] 常见排序,升序(ASC,查询默认为ASC),降序(DESC)

tips:遇到需要和Null一起排序:

# null默认被放在最前  oracle相反
order by colum asc   

# null被强制放在最前，不为null的按声明顺序[asc|desc]进行排序
ORDER BY IF(ISNULL(update_date),0,1)

4 DATE_FORMAT

功能: 用于以不同的格式显示日期/时间数据 语法: DATE_FORMAT(date,format) date 参数是合法的日期,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)
%pAM 或 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 位
eg:
# DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
# DATE_FORMAT(NOW(),'%m-%d-%Y')
# DATE_FORMAT(NOW(),'%d %b %y')
# DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

select
	DATE_FORMAT( NOW(), '%b %d %Y %h:%i %p' ) UNION ALL
select
	DATE_FORMAT( NOW(), '%m-%d-%Y' ) UNION ALL
select
	DATE_FORMAT( NOW(), '%d %b %y' ) UNION ALL
select
	DATE_FORMAT( NOW(), '%d %b %Y %T:%f' )

在这里插入图片描述

5 CONCAT

功能: 多个字符合并为一个字符串 语法: CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串

CONCAT_WS(x, s1,s2...sn)

功能: 将多个字符合并为一个字符串,.每个字符用x分隔 语法: CONCAT_WS(x, s1,s2...sn) 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符.

e.g:

select
	CONCAT('Hello','World')
union all
select
	CONCAT_WS('$$','Welcome','BeiJing')

在这里插入图片描述

6 EXCEPT、INTERSECT、UNION

  • EXCEPT 返回两个结果集的差
  • INTERSECT 返回 两个结果集的交集
  • UNION返回两个结果集的并集 语法:
{ （<ＳＱＬ－查询语句１>） }
{ EXCEPT | INTERSECT| UNION}
{ （<ＳＱＬ－查询语句２> ）}

问题:

# 有些版本sql不支持EXCEPT函数 可按照下列查询

select name from tableA a where name not in (select name from tableB b )
union
select name from tableB b where name not in (select name from tableA a )

7 Sql中关于批量更新操作

方法一: 遍历更新,发送多条更新sql,虽然共用一个事务,但是依然性能较低.

	<update id="updateBatch">
        <foreach separator=";" collection="list" item="c" index="index">
            update t_user
            <set>
                status = 1
            </set>
            where id = #{c}
        </foreach>
    </update>

方法二: 使用case when,把所有情况列举,一个sql完成.

<update id="updateBatch" parameterType="list">
         update t_user
            <trim prefix="set" suffixOverrides=",">
                 <trim prefix="path =case" suffix="end,">
                     <foreach collection="list" item="i" index="index">
                             <if test="i.id!=null">
                                  when id=#{i.id} then #{i.path}
                             </if>
                     </foreach>
                     
                  </trim>
 <trim prefix="address =case" suffix="end,">
                     <foreach collection="list" item="i" index="index">
                             <if test="i.address!=null">
                                  when id=#{i.address} then #{i.address}
                             </if>
                     </foreach>
                  </trim>
             </trim>
             
            where id in 
            <foreach collection="list"  item="i" index="index" open="(" separator="," close=")">
              #{i.id}
          </foreach>
    </update>

解释: 使用case when 将更新的内容列举出来,进行批量更新.

UPDATE t_user
SET path =
CASE  id
      WHEN '10800537' THEN    '/api/ids/10800537'
      WHEN '10800118' THEN    '/api/ids/10800118'
END ,   
address=
CASE  id
      WHEN '10800537' THEN    '武汉'
      WHEN '10800118' THEN    '上海'
END   
WHERE   id IN ('10800537','10800118')

8 流程控制函数case/when

语法: if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1:否则表达式2

实例数据: 在这里插入图片描述 ==情况一:==

case 变量或表达式或字段
when 常量1	then 值1
when 常量2	then 值2
...
else 值n
end

在这里插入图片描述

==情况二:==

case 
​when 条件1	then 值1
when 条件2	then 值2
...
else 值n
end

在这里插入图片描述

9 保留两位小数

Mysql之保留两位小数:

方法说明
Round(number,2)param2表示保留两位有效数字,不负责截断,后面如有数据用0补充
Convert(decimal(10,2),number)param2表示保留两位有效数字,会截断数据
cast(number as decimal(10,2))param2表示保留两位有效数字,会截断数据
SELECT ROUND(123.9994, 3)   --123.9990
SELECT ROUND(151.75, 0,0)   --152.00 舍入
SELECT ROUND(151.75, 0,1)   --151.00 截断

SELECT CONVERT(DECIMAL(13,2),13.123) --13.12
SELECT CAST(13.123 as DECIMAL(13,2)) --13.12

总结: 推荐使用第三种方法,使用CAST函数保留两位小数.

Oracle之保留两位小数

  • ROUND(A/B,2) 函数是会将计算结果进行四舍五入的, 参数一是计算表达式, 参数二是保留的小数位.
  • TRUNC(A/B,2) 不会进行四舍五入, 参数一是计算表达式, 参数二是保留的小数位.
  • TO_CHAR(A/B,‘FM99990.99’) 格式化函数，参数一是计算表达式，参数二是指定格式化的格式. (如果保留两位小数则小数点后写两个99,表示占位符)
-- 特殊情况下,可使用下列表示
-- 如 1/10  上面的结果为0.1 而下面的结果是0.10
select to_char(a/b,'FM9990.00') AS result  from dual;

10 IF函数条件判断

Mysql: 语法: IF(expr1,expr2,expr3) 说明: expr1是判断条件,expr2和expr3是符合expr1条件的返回结果. 使用场景: 将数据库查询的结果进行自定义替换 在这里插入图片描述 tips: 判断一个字段为null,使用is null,而不是= null ; 在这里插入图片描述 Oracle: oracle中没有if函数,但是有想使用条件三元表达式怎么办? 可以使用sign和decode函数组合.

sign函数: 语法: sign(n) 说明: ==(n可以是表达式)==

  • n>0, 返回1
  • n=0, 返回0
  • n<0, 返回-1

** decode函数:** 语法: decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 说明: 根据条件的值,返回不同的值. e.g:

-- 值1-值2 如果结果小于0,则结果0,否则结果为值1-值2 
SELECT
	DECODE(sign(值1-值2),1,(值1-值2),0)
FROM
	DUAL

以上,是oracle中使用sign和decode函数组合,达到IF(expr1,expr2,expr3)函数的效果.

11 with...as函数

语法: with ... as ... 用法: 与子查询相识,生成临时表. 使用场景: 常在报表统计时使用,提高多次重用效率和可读性. 案例:

WITH ids AS
select
	id
from
user

select
	id
from
user
where id IN (select *from ids)

上面sql和下面表达式效果相同:

select
	id 
from
user 
where
	id IN (select id from user)

==tips:==

  • mysql5版本不支持,mysql8版本支持

12 IFNULL(VALUE,0)函数/COALESCE(VALUE,0)

语法: IFNULL(字段,0) 用法: 字段为null,则设为0.(也可设为其他值) 使用场景: 常在计算时转换,因为做加减乘除时,左右数据都不能为null.(计算左右如有为null,计算结果为null)

IFNULL(VALUE,0)仅在Mysql中使用.

在Oracle中使用COALESCE(VALUE,0)函数,效果相同.

13 SQL中大于和等于写法

写法一: 大于等于:

<![CDATA[ >= ]]>

小于等于:

<![CDATA[ <= ]]>

写法二: 大于等于:

&gt;=

小于等于:

&lt;=

14 SQL中时间比较相关问题

比较大小问题

Oracle: 表中数据 日期一: 2021-04-10 12:30:01 日期二: 2021-04-11 00:00:00 要根据年月日比较大小, 要使用to_char函数. 格式: to_char(date,'format') 说明:

  • 1 参数date,可以是任意有效的日期格式
  • 2 参数format,必须包含单引号且大小写敏感.(常见 'yyyy-mm-dd hh24:mi:ss')

==案列== Oracle中查询当前系统时间:

SELECT 
	SYSDATE 
FROM 
	dual

在这里插入图片描述 大于等于比较:

SELECT 
	SYSDATE  ,
	CASE 
	WHEN to_char(SYSDATE,'yyyy-mm-dd') >='2021-04-10' THEN 'YES'
	ELSE 'NO'
	END
FROM 
	dual

在这里插入图片描述 大于等于比较: (验证说明)

SELECT 
	SYSDATE  ,
	CASE 
	WHEN to_char(SYSDATE,'yyyy-mm-dd') >='2021-04-11' THEN 'YES'
	ELSE 'NO'
	END
FROM 
	dual

在这里插入图片描述

计算时间相差的天数

Oracle: 在Oracle中计算两个时间相差天数,直接相减.但如果,两个日期的时分秒不同,计算结果出现小数. 在计算中要使用to_date()函数. 格式:to_date(string,‘format’) 说明:用来将字符串转换成date类型的数据.

SELECT 
	to_date('2021-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') - to_date('2021-04-10 12:30:01','yyyy-mm-dd hh24:mi:ss')
FROM 
	dual

在这里插入图片描述 解决方法一:

1  先用to_char,把两个日期变成字符串-年月日    即    2021-04-10  和  2021-04-11
2  再用to_date,把两个字符串变成日期-年月日   即    2021-04-10 00:00:00   和  2021-04-11 00:00:00
3  再把两个日期进行相减   日期二减日期一   等于 1天

因为是模拟日期,所以伪代码多了一层转换:

SELECT
        to_date(to_char(to_date( '2021-04-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),'yyyy-mm-dd'),'YYYY-MM-DD HH24:MI:SS') - to_date(to_char(to_date( '2021-04-10 12:30:01', 'YYYY-MM-DD HH24:MI:SS' ) ,'yyyy-mm-dd'),'YYYY-MM-DD HH24:MI:SS')
FROM
        dual;

在这里插入图片描述 解决方法二: 使用trunc()函数. 格式

SELECT
	to_date( '2021-04-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
	to_date( '2021-04-10 12:30:01', 'YYYY-MM-DD HH24:MI:SS' ),
        trunc(to_date( '2021-04-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) - trunc(to_date( '2021-04-10 12:30:01', 'YYYY-MM-DD HH24:MI:SS' ))
FROM
        dual;

在这里插入图片描述 trunc()函数. 作用: 用于截取时间或者数值，返回指定的值.

日期处理： TRUNC(date,[format]) date 为必要参数，是输入的一个date日期值. format参数可忽略，指定日期格式，缺省时表示指定日期的0点.

数值处理： TRUNC(number,[decimals]) number 为必要参数，是输入的一个number数值. decimals 参数可忽略，指定截取的位数，缺省时表示截掉小数点后边的值.

15 SQL中在SELECT查询列表中使用if条件查询

select
   <if test='tag="0"'>
      id
   </if>
   <if test='tag="1"'>
      name
   </if>
from
user

说明: 当传入的tag='0'时,查询学生的id,当传入的tag='1'时,查询学生的姓名,因为二者其他查询条件一样,所以,可以采用该方法.

16 SQL中if条件查询时等于和不等于格式

1 不等于

<if test="tag!= null and tag != ''">

2 等于

<if test='tag!= null and tag == ""'>

==总结:== 在不等于判断时,最外层使用双引号,里面使用单引号; 在等于判断时,最外层使用单引号,里面使用双引号.

Update information

Update TimeUpdate instructions
2021-03-12 22:21:279 保留两位小数
2021-03-21 10:58:5910/11
2021-04-09 22:58:5912/13
2021-04-10 17:58:5914
2021-04-29 07:58:5915
2021-05-19 07:58:5916
avatar
文章
阅读
粉丝