SQL常用函数整理(完善中)

一、表操作

1、CREATE TABLE 建表

CREATE TABLE 表名(
    字段名 字段类型,
    ……
    字段名 字段类型
);

COMMENT ON TABLE 表名 is ''; --添加表描述

COMMENT ON COLUMN 表名.列名 is ''; --添加列描述

2、ALTER TABLE 修改表

ALTER TABLE 表名 CHANGE 原列名 新列名 类型; --修改表的列属性名

ALTER TABLE 表名 MODIFY 列名 类型 ; --修改表的类类型

ALTER TABLE 表名 DROP 列名; --删除表的某一列

ALTER TABLE 表名 ADD 列名 类型; --添加某一列

ALTER TABLE 表名 RENAME 新表名; --修改表名

3、INSERT INTO 插入数据

INSERT INTO 表名 VALUES (value1, value2, ...);--全字段插入

INSERT INTO 表名(column1, column2, ...) VALUES (value1, value2, ...);--限定字段插入

INSERT INTO 表名(column1, column2, ...) VALUES (value1_1, value1_2, ...),
(value2_1, value2_2, ...), ...; --多条插入

INSERT INTO 表名1 SELECT * FROM 表名2 [WHERE key=value]; --从另一个表导入

4、UPDATE 修改/更新数据

UPDATE <表名> SET 字段1 = 值1 [,字段2 = 值2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

5、DELETE 删除

DELETE FROM 表名 WHERE 条件;--物理删除

ALTER TABLE 表名 ADD 字段名 BIT DEFAULT 0;-- 给表添加一个字段 bit类型 默认值为0
UPDATE 表名 SET 字段名 = 1 WHERE 条件;--逻辑删除

 6、其他

--若存在则把原纪录删除再插入，其他字段会改变
replace into
--insert若主键重复则update
insert into ...on duplicate key update


insert into 表1
  select * from 表2 
on duplicate key update 
字段值=values(字段值)

二、查询操作

1、SELECT  查询

SELECT * FROM 表名 [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句];

2、LIKE / NOT LIKE 模糊查询  

SELECT * FROM 表名 WHERE 字段 LIKE '${}';
SELECT * FROM 表名 WHERE 字段 LIKE '${}%';
SELECT * FROM 表名 WHERE 字段 LIKE '%${}';
SELECT * FROM 表名 WHERE 字段 LIKE '%${}%';
SELECT * FROM 表名 WHERE 字段 NOT LIKE '${}';

1、“%”：匹配0个或多个字符。
2、 “_”：只能匹配一个字符。 

3、子查询

在一个完整的查询语句中，嵌套不同功能的小查询，从而完成复杂查询的一种编写形式

SELECT * FROM table WHERE salary>(SELECT avg(salary) FROM table);

非关联子查询：在SELECT子句、GROUP BY子句、HAVING子句、ORDER BY子句中均可使用子查询语句，较常用的是WHERE子句、HAVING子句和FROM子句。 

执行逻辑：在非关联子查询中，子查询语句可以独立执行，查询结果是一个数据，一条数据，或者一张临时数据表，主查询可使用子查询结果进行操作。

4、连接查询

1.内部 inner join  ，返回连接表中符合连接条件和查询条件的数据行

2.交叉连接 cross join ，返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

3.左联表 left join ，返回符合连接条件和查询条件（即：内连接）的数据行，返回左表中不符合连接条件单符合查询条件的数据行

4.右联表 right join ，返回符合连接条件和查询条件（即：内连接）的数据行，返回右表中不符合连接条件单符合查询条件的数据行

5.全部联表查询 full join ，返回符合连接条件和查询条件（即：内连接）的数据行，返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

5、SQL查询的基本原理

两种情况介绍。

第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。

第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。

6、ON后面的条件（ON条件）和WHERE条件的区别

ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。

在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。

 7、总结

--多个表查询的时候，这些不同的连接类型可以写到一块。例如：
SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
       INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
       INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
       LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;

连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：
1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、 求差操作的时候用联合查询。

三、表达式 

1、DISTINCT 去重  

SELECT DISTINCT 字段A FROM 表A; --对A表的A字段去重然后显示
--distinct作用于多列的时候只在开头加上即可，并不用每个字段都加上。
SELECT DISTINCT 字段A,字段B from 表A;   --对A表的A字段和B字段去重然后显示

DISTINCT【查询字段】，必须放在要查询字段的开头，即放在第一个参数；
只能在SELECT 语句中使用，不能在 INSERT, DELETE, UPDATE 中使用；
DISTINCT 表示对后面的所有参数的拼接取不重复的记录，即查出的参数拼接每行记录都是唯一的
不能与all同时使用，默认情况下，查询时返回的就是所有的结果

DISTINCT 支持单列、多列的去重方式。

--单列去重，即相同值只保留1个

--多列去重，根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。

2、LIMIT 限制条数（ MYSQL特有）

提取前几条或者中间某几行数据，限制查询结果返回的数量。

SELECT * FROM 表名 WHERE 条件 LIMIT m,n;

3、GROUP BY 分组

SELECT * FROM 表名 ORDER BY 子句;

GROUP BY：根据一个或多个列对结果集进行分组。

在select指定的字段必须包含在group by 语句的后面，作为分组的依据；非分组依据的字段不能直接查询，只能用聚合函数查其聚合属性。

group by 查询结果的每条记录都是“数据组”的概念，聚集函数表示这个组的count,sum等一些属性，分组查询只能查询分组依据中的属性，因为分组后同一组中这些属性值是相同的，是属于“组”的。

4、WHERE  / HAVING 条件 

SELECT * FROM 表名 WHERE 条件 ORDER BY 子句 HAVING 字段;

 where和 having 区别

 where:
        where是一个约束声明,使用where来约束来自数据库的数据;
        where是在结果返回之前起作用的;
        where中不能使用聚合函数。

having:
        having是一个过滤声明;
        在查询返回结果集以后，对查询结果进行的过滤操作;
        在having中可以使用聚合函数

where早于 group by 早于 having

where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选

5、ORDER BY 排序 （ASC--升序 / DESC--降序）

SELECT * FROM 表名 ORDER BY 字段;--ASC
SELECT * FROM 表名 ORDER BY 字段 DESC;

用于根据指定的列对结果集进行排序。

默认按照升序对记录进行排序。

如果存在 where 子句，那么 order by 必须放到 where 询句后面。

7、UNION ALL / UNION 用于合并两个或多个SELECT语句的结果集

SELECT 列名 FROM 表1
UNION [ALL]
SELECT 列名 FROM 表2

union和 union all的区别

1. 查询的select语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
2. 每条select语句中列的顺序必须相同
3. union合并的结果集，不允许重复值，如果允许有重复值的话，使用union all
4. 结果集中的列名等于第一个 select语句中的列名

8、 CASE WHEN THEN  ELAE END 查找型表达式

CASE
    WHEN C1 THEN E1
    WHEN C2 THEN E2
    ……
    WHEN CN THEN EN
[ELSE END]

9、执行顺序

from ： 将硬盘上的表文件加载到内存
where： 将符合条件的数据行摘取出来。生成一张新的临时表
group by ：根据列中的数据种类，将当前临时表划分成若干个新的临时表
having ： 可以过滤掉group by生成的不符合条件的临时表
select : 对当前临时表迚行整列读取
order by : 对select生成的临时表，迚行重新排序，生成新的临时表
limit : 对最终生成的临时表的数据行，近行截取

四、运算符

1、NOT & AND & OR

SELECT * FROM 表名 WHERE NOT 条件;
SELECT * FROM 表名 WHERE 条件1 AND 条件2;
SELECT * FROM 表名 WHERE 条件1 OR 条件2;

条件不成立，则 NOT(!)运算符显示一条记录

如果第一个条件和第二个条件都成立，则 AND(&&) 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR(||) 运算符显示一条记

2、IS NOT NULL /IS NULL 空值  

SELECT * FROM 表名 WHERE 字段 IS NULL;
SELECT * FROM 表名 WHERE 字段 IS NOT NULL;

3、IN / NOT IN 属于 

SELECT * FROM 表名 WHERE 字段 IN (A,B);
SELECT * FROM 表名 WHERE 字段 NOT IN (A,B);

4、BETWEEN AND 

SELECT * FROM 表名 WHERE 字段 BETWEEN ? AND ?;

between 条件下界1 and 条件上界2

查询条件1和条件2范围内的数据，包含边界，其中条件下界1一般小于条件上界2.

5、位运算符

        按位与(&)

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0

        按位或(|)

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的
二进制位的数值有一个或两个为1时，则该位返回1，否则返回0 

按位与(按位或) 与 逻辑与(逻辑或) 的区别

①代表含义不同

按位与运用二进制进行计算，逻辑与比较符号两边的真假输出逻辑值。

②运算法则不同

按位与对所有的表达式都要判断，逻辑与运算符第一个表达式不成立的话，后面的表达式不运算，直接返回。

③输出不同

按位与&输出运算结果为不同的数值，逻辑与 && 输出逻辑值true或者 false。

按位与运算符“&”是双目运算符。其功能是参与运算的两数各对应的二进位相与。只有对应的两个二进位都为1时，结果位才为1。参与运算的两个数均以补码出现

逻辑与，符号为“&&”，只有两个操作数都是真，结果才是真。 逻辑与操作属于短路操作，既如果第一个操作数能够决定结果，那么就不会对第二个操作数求值。对于逻辑与操作而言，如果第一个操作数是假，则无论第二个操作数是什么值，结果都不可能是真，相当于短路了右边

五、日期函数

 1、日期转换 

日期与时间戳之间的转换

## 日期转化为时间戳 ##
SELECT UNIX_TIMESTAMP('2023-02-14 10:23:59');--得到 1676341439
SELECT UNIX_TIMESTAMP('20230214 10:23:59','yyyyMMdd HH:mm:ss');-- 得到 1676341439
SELECT UNIX_TIMESTAMP('20230214','yyyyMMdd');--得到 1676341439

## 时间戳转化为日期 ## 
SELECT FROM_UNIXTIME (1676341439);-- 得到 2023-02-14 10:23:59
SELECT FROM_UNIXTIME(1676341439,'yyyyMMdd');-- 得到 20230214
SELECT FROM_UNIXTIME(1676341439,'yyyy-MM-dd');--得到 2023-02-14

2、日期加减

天--维度计算

--date_sub(string startdate, int days)
## 使用date_sub (string startdate, int days)得到开始日期startdate减少days天后的日期## 
select date_sub('2012-12-08', 10) 得到 2012-11-28

--date_add(string start date, int days)
## 使用date_add(string startdate, int days)得到开始日期startdate增加days天后的日期 ##
select date_add('2012-12-08', 10) 得到 2012-12-18

--datediff(string enddate, string startdate)
## 使用datediff(string enddate, string startdate)得到 结束日期减去开始日期的天数 ## 
select datediff('2012-12-08','2012-05-09') 得到 213

月--维度计算 

--add_months(d,n); 
--在某一个日期d上,加上指定的月数n,返回计算后的新日期。
--d表示日期,n表示要加的月数（n可以为负值）
select add_months(sys date,1) from student;

 时间差函数timestampdiff--综合维度计算

--timestampdiff( interval, datetime_1, datetime_2)
interval:
--毫秒：frac_second
--秒   ：second
--分钟：minuter
--小时：hour
--天   ：day
--星期：week
--月   ：month
--季度：quarter
--年   ：year

--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2018-03-22 00:00:00');
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差176400秒
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

3、日期提取

日期（2020-03-21 17:13:39）怎么转换为想要的格式（2020-03-21）

--方法：可以直接使用to_date函数，也可以使用字符串提取函数。
select to_date('2020-03-21 17:13:39') 得到 2020-03-21 
select substr('2020-03-21 17:13:39',1,10) 得到 2020-03-21

 获取日期年份/月份/几号/当前日期

year()    --获取日期年份
month() --获取日期月份
day()     --获取日期几号
now()    --获取当前日期

 last_day(datetime)

--返回指定日期当前月的最后一天；
select last_day(create_time) from student;

extract(unit from date)

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,--2008
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,--11
EXTRACT(DAY FROM OrderDate) AS OrderDay--11
FROM Orders；

--date 参数是合法的日期表达式。unit 参数可以是下列的值：
--Unit 值:
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

也可以通过日期命令格式date_format()提取

4、日期命令格式

date_format(string datetime, interval)

---interval:
%y：表示年(两位数)，例如: 17 年。
%Y：表示4位数中的年，例如: 2017年
%m：表示月(1-12)
%d: 表示月中的天
%H: 小时(0-23)
%i: 分钟 (0-59)
%s: 秒 (0-59)

--常用格式：'%Y-%m-%d %H:%i:%s'

NOW()        --返回当前的日期和时间

CURDATE()        --返回当前的日期

CURTIME()        --返回当前的时间

DATE()        --提取日期或日期/时间表达式的日期部分

EXTRACT()         --返回日期/时间的单独部分

注：

DATENAME 和 DATEPART 的区别，返回的值类型不同，一个是VARCHAR一个是INT，另外就是星期会用本地语言来表示

当显示日期列的内容时如果只显示年月日部分,可以使用CONVERT转换函数对日期列进行转换

CONVERT(VARCHAR(10),日期字段名,120) --120 为日期格式YYYY-MM-DD

SELECT CONVERT(VARCHAR(10),盘点日期,120) AS 盘点日期 FROM 原材料盘点日期明细表

SQL中日期的表示方法及有效范围,如下:

日期部分 缩写 值 日期部分 缩写 值
年 yy 1753-9999 周 wk 1-53
季度 qq 1-4 小时 hh 0-23
月 mm 1-12 分钟 mi 0-59
一年中的天 dy 1-366 秒 ss 0-59
一月中的天 dd 1-31 毫秒 ms 0-999
一周中的天 dw 1-7

六、聚合函数

返回汇总值 （它对其应用的每个行集返回一个值）

1、AVG(表达式) 返回表达式中所有的平均值。

仅用于数字列并自动忽略NULL值。

2、COUNT(表达式) 返回表达式中非NULL值的数量。

可用于数字和字符列。

3、COUNT(*) / COUNT(1) 返回表中的行数(包括有NULL值的列)。

count(1)、count(*)和count(字段名)的区别

执行效果：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。且在表中有索引的情况下会扫描索引
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL 。且在表中有索引的情况下会扫描索引
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。是否扫描索引与该列是否添加了索引有关

执行效率：

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优。

效率问题底层理解

性能对比：

count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)

在sql调优功能上，count(1)和count(*)基本上是没有差别

 count(*) 对 innodb 而言，它需要把数据从磁盘中读取出来然后累计计数；而 MyISAM 引擎把一个表的总行数存在了磁盘上，所以执行 count(*) 会直接返回这个数，如果有 where 条件则和 innodb一样。那么如何优化 count(*) ？一个思路是使用缓存，但是需要注意双写一致的问题（双写一致性后文缓存章节会做介绍）。还可以专门设计一张表用以存储 count(*)。

对于 count(主键 id )来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1” 进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id)快。因为从引擎 返回 id 会涉及到解析数据行，以及拷贝字段值的操作。对于 count(字段)来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再 判断一下，不是 null 才累加。而对于 count(*) 来说，并不会把全部字段取出来，而是专门做了优化，不取值，按行累加。

4、MAX(表达式) 返回表达式中的最大值,忽略NULL值。

可用于数字、字符和日期时间列。

5、MIN(表达式) 返回表达式中的最小值,忽略NULL值。

可用于数字、字符和日期时间列。

6、SUM(表达式) 返回表达式中所有的总和,忽略NULL值。

仅用于数字列

七、转换函数

将一种数据类型转换为另外一种（有CONVERT和CAST两种）

1、CONVERT(data_type[(length)], expression [, style])

Select convert(varchar(10) ,stuno) as stuno,stuname from student

2、CAST( expression AS data_type )

Select cast(stuno as varchar(10)) as stuno,stuname from student

Cast和Convert的区别
Cast 和Convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式

八、数学函数

abs(num_expr) 返回数值表达式的绝对值。

ACOS(float_expr) 返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。

ASIN(float_expr) 返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。

ATAN(float_expr) 返回角(以弧度表示),它的正切值近似于指定的浮点表达式。

ATN2(float_expr1, float_expr2) 返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。

CEILING(num_expr) 返回大于或等于数值表达式的最小整数。

COS(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。

COT(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。

DEGREES(num_expr)返回数值表达式表示的弧度值对应的度值。

EXP(float_expr) 根据指定的近似浮点表达式,返回指数值。

floor(num_expr) 返回小于或等于数值表达式的最大整数

LOG(float_expr) 根据指定的近似浮点表达式,返回自然对数值。

LOG10(float_expr) 根据指定的近似浮点表达式,返回以为底的对数。

pi() 返回常量值3.141592653589793，默认值为小数后六位

POWER(num_expr,y) 返回幂为y的数值表达式的值。

RADIANS(num_expr) 返回数值表达式表示的度值对应的弧度值。

RAND([seed]) 随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。

round(num_expr,length) 对数值表达式截取指定的整数长度,返回四舍五入后的值。

SIGN(num_expr) 对正数执行+1操作,对负数和零执行-1操作。

SIN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。

SQUARE(float_expr) 返回浮点表达式的平均值。

SQRT(float_expr) 返回指定的近似浮点表达式的平方根。

TAN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。

九、字符串函数

1、字符串提取

substr/substring函数

--使用substr/substring (string A, int start)返回字符串A从start位置到结尾的字符串##
select substring('abcde', 3) 得到 cde 

--使用substring(string A, int start, int len)返回字符串A从start位置开始，长度为len的字符串
select substring('abcde', 3,2) 得到 cd

 2、字符串拼接

concat/concat_ws函数/group_concat函数

--使用concat(string A, string B) 返回字符串AB的拼接结果,可以多个字符串进行拼接
select concat('abc', 'def','gh') 得到abcdefgh

--使用concat_ws(string X, stringA, string B) 返回字符串A和B由X拼接的结果
select concat_ws(',', 'abc', 'def', 'gh') 得到 abc,def,gh

--group_concat用于将GROUP BY产生的同一个分组中的值连接起来，返回一个字符串结果

GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

GROUP_CONCAT函数首先根据GROUP BY指定的列进行分组，将同一组的列显示出来，并且用分隔符分隔，由函数参数(字段名)决定要返回的列
说明:

(1) 使用DISTINCT可以排除重复值

(2) 如果需要对结果中的值进行排序，可以使用ORDER BY子句

(3) SEPARATOR '分隔符'是一个字符串值，默认为逗号

3、字符串常见处理函数

length/trim/lower/upper

--使用length(string A)返回字符串A的长度
select length('abcedfg') 得到 7 

--使用trim(string A) 去除字符串两边的空格
select trim(' abc ') 得到 'abc'

--使用lower(string A)/ lcase(string A)返回字符串的小写形式，常用于不确定原始字段是否统一为大小写
select lower('abSEd') 得到 absed

--使用upper(string A)/ ucase(string A)返回字符串的大写形式，常用于不确定原始字段是否统一为大小写
select upper('abSEd') 得到 ABSED

 4、不同格式数据的转换

cast

--bigint转换为字符串
select cast(A as string) as A

 5、正则表达式

regexp_extract 提取 / regexp_replace 替换

--regexp_extract(string subject, string pattern, int index)
--将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) 得到 the

--regexp_replace(string A, string B, string C)
--将字符串A中的符合java正则表达式B的部分替换为C
select regexp_replace('foobar', 'oo|ar', '') 得到 fb

6、字符串解析 

get_json_object

--get_json_object(string json_string, string path)
--解析json的字符串json_string,返回path指定的内容

select get_json_object(
                  	{"from_remain_count":420,"reason":"collect","to_remain_count":0},
                    '$.from_remain_count'
                ) 得到 420

十、系统函数

1、coalesce函数

用途：
（1）：将空值替换成其他值；
（2）：返回第一个非空值
表达式：
COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

--当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。
select coalesce(success_cnt, 1) from tableA；

--当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），
--当success_cnt为null，而period不为null的时候，返回period的真实值。
--只有当success_cnt和period均为null的时候，将返回1。
select coalesce(success_cnt,period,1) from tableA；

COL_LENGTH('table_name','column_name') 返回列的长度。

COL_NAME(table_id,column_id) 返回指定的表中的列名。

DATALENGTH('expr') 返回任何数据类型的实际长度。

DB_ID([‘database_name']) 返回数据库的标识号。

DB_NAME([database_id]) 返回数据库的名称。

GETANSINULL([‘database_name']) 返回数据库的默认空性(Nullability)。

HOST_ID() 返回工作站的标识号。

HOST_NAME() 返回工作站的名称。

IDENT_INCR('table_or_view') 有新的记录添加入到表中时计数加。

IDENT_SEED('table_or_view') 返回标识列的起始编号。

INDEX_COL('table_name',index_id,key_id) 返回索引的列名。

ISNULL(expr,value) 使用指定的值替换的NULL表达式。

NULLIF(expr1,expr2) Expr1与Expr2相等时,返回Null。

OBJECT_ID('obj_name') 返回数据库对象标识号。

OBJECT_NAME('object_id') 返回数据库对象名。

STATS_DATE(table_id,index_id) 返回上次更新指定索引的统计的日期。

SUSER_SID([‘login_name']) 返回用户的登录标识号。

SUSER_ID([‘login_name']) 返回用户的登录标识号。这个函数类似于SUSER_SID()函数,并且保留了向后的兼容性。

SUSER_SNAME([server_user_id]) 返回用户的登录标识号。

SUSER_NAME([server_user_id]) 返回用户的登录标识号。这个函数类似于SUSER_SNAME()函数,并且保留了向后的兼容性。

USER_ID('user_name') 返回用户的数据库标识号。

USER_NAME(['user_id']) 返回用户的数据库名称。