MYSQL - SubStr() concat()等函数运用

目录

常用的函数集合

SubString()函数

 concat函数

COALESCE函数

数值处理函数 

 soundex函数

 DATEDIFF()等时间处理函数

① DATEDIFF()函数

② DATEPART函数

③ EXTRACT函数

④ TIMESTAMPDIFF 函数

---

常用的函数集合

功能	函数	说明
截取字符	RIGHT()	返回字符串右边的字符（或者使用子字符串函数）
	LEFT()	返回字符串左边的字符（或使用子字符串函数）
	LENGTH()	返回字符串的长度，可与str()结合使用，截取特定长度
大小写转换	LOWER()	将字符串转换为小写
	UPPER()	将字符串转换为大写
除去空格	LTRIM()	去字符串的左边空格
	TRIM()	去字符串两边的空格
	RTRIM()	去字符串右边的空格
字符替换	Stuff()	删除指定长度的字符，并在指定的起点处插入另一组字符。
		STUFF(原字符,开始位置,删除长度,插入字符)
	REPLACE()	REPLACE(String,from_str,to_str)
		将String中所有出现的from_str替换为to_str
查找位置	Charindex()	返回字符或字符串在另一个字符的起始位置
		CHARINDEX(查找字符，被查字符，开始位置)
类型替换	CONVERT()	CONVERT ( data_type[ ( length ) ] , expression[ , style] )
		用于时间类型与字符串类型的相互转换
返回非空	coalesce（）	返回所有参数中的第一个非null值
字段长度	length（）	计算字段长度：一个汉字=3字符,一个数字or字母=1字符
合并分组 （行显示）	group_concat()	将括号内的字段，合并成一行显示，并去重
时间戳	timestampdiff	timestampdiff（unit，begin，end）
截取（按索引）	SubString_index()	substring_index(要处理的字符串,分隔用的索引,计数 正的左到右)

SubString()函数

 形式1：

SUBSTRING（字符串，开始位置，截取长度）

e.g. 把blog_url字段中url字符后的字符串提取出，单独为一个新字段。示例：user_submit表如下

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/ur/bisdgboy777

语句如下，截取长度为（总字符长度-开头需跳过的字符长度）

select 
substr(blog_url,11,length(blog_url)-10)
as user_name
from user_submit;

形式2： SUBSTRING（字符串，开始位置）

 直接截取到开始位置→最后一个字符

e.g.  使名字只有第一个字符是大写的，其余都是小写的。

SELECT user_id, 
       CONCAT(
              UPPER(LEFT(name, 1)), 
              LOWER(SUBSTRING(name, 2))) 
       AS name
FROM Users;

 concat函数

和excel的concat函数用法一样，用来联结多个字符串，中间用逗号隔开

e.g. 数分项目-求复购率

SELECT
CONCAT(sum(case when 购买>1 then 1 else 0 end)*100/ 
       sum(case when 购买>0 then 1 else 0 end,'%') 
as 复购率
from 用户行为数据;

COALESCE函数

COALESCE ( expression,value1,value2……,valuen) 

• expression为待检测的表达式，其后的参数不定。
• 返回包括expression在内的所有参数中，的第一个非空表达式
• 如expression不为空值则返回expression；以此类推，如果所有的表达式都为空值，则返回NULL。 

举例如下，结果输出为： fat，pig

数值处理函数 

常用的数值处理函数

函数	返回的内容说明
ABS ( )	一个数的绝对值
COS ( )	一个角度的余弦
EXP ( )	一个数的指数值
PI ( )	圆周率π的值
SIN ( )	一个角度的正弦
SQRT ( )	一个数的平方根
TAN ( )	一个角度的正切

 soundex函数

• 将文本串转换为描述其语音表示的字母数字模式的算法
• soundex即sound的index，可理解为 用英语发音的语音 作为模糊查找的通配符

e.g. customers表中，有个顾客为Kids Place,联系名为Michelle Green。但正确联系名为Michael Green。

可使用SOUNDEX进行发音匹配搜索，代码如下：

SELECT cust_name,cust_contact 
       FROM customers
         WHERE SOUNDEX(cust_contact) = SOUNDEX ('Michael Green') ;

• 也写成 SUBSTR（）
• 用来截取字符串中的一部分字符
• 可与LENGTH( )结合，做截取字段

 DATEDIFF()等时间处理函数

程序一般不用时间＆日期的存储格式， ∴ 日期和时间函数多用来读取、统计和处理时间值

① DATEDIFF()函数

• 返回两个日期的天数差，即date1 - date2的计算结果
• DATEDIFF(date1,date2)

e.g1.查找与之前（昨天的）日期相比温度更高的所有日期的 id 。id 是这个表的主键,该表包含特定日期的温度信息。表： Weather     

+---------------+------------+
| Column Name | Type |
+---------------+------------+
| id                     | int     |
| recordDate      | date  |
| temperature    | int     |
+---------------+------------+
语句如下

select a.id
from Weather a
JOIN
Weather b
on DATEDIFF(a.recordDate,b.recordDate)=1
   and a.temperature > b.temperature;

e.g2.从Activity表中查询出activity_date字段记录日期的截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条user_id活动记录，即为活跃用户）。

select activity_date, 
count(distinct user_id) as active_users
from activity
where datediff('2019-07-27', activity_date) >= 0 
AND datediff('2019-07-27', activity_date) <30
group by activity_date

② DATEPART函数

• 返回日期的某一部分
• DATEPART（返回的成分，从中返回成分的日期）

③ EXTRACT函数

• 用来提取日期的成分，将字符串转换为日期
• EXTRACT （需要提取的部分 FORM 提取列）

范例运用 从orders表中包含的订单日期里，检索出2020年的所有订单，需要按订单日期去找。但只用具体到年份，不用按照完整的日期查找。

e.g1. 用DATEPART函数完成，语句如下：

SELECT order_num 
       FROM orders
           WHERE DATEPART(yy,order_date) = 2020;

e.g2. 用EXTRACT函数完成，语句如下：

SELECT order_num
      FROM orders
           WHERE EXTRACT( year FROM order_date ) = 2020;

 e.g3. to_date函数 还可以和BETWEEN操作符结合完成，语句如下：

SELECT order_num
      FROM orders
           WHERE order_daet 
            BETWEEN to_date('2020-01-01','yyyy-mm-dd')
            AND to_date('2020-12-31','yyyy-mm-dd');

④ TIMESTAMPDIFF 函数

求时间戳差异

 timestampdiff（unit，begin，end）

       begin和end可为DATE或DATETIME，参数可为混合类型

参数类型

单位	MySQL(UNIT)
秒	second
分钟（返回秒数差除以60的整数部分）	minute
小时（返回秒数差除以3600的整数部分）	hour
天（返回秒数差除以3600*24的整数部分）	day
周	week
月	month
季	quarter
年	year

e.g. 删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；

作答记录表exam_record部分字段：start_time，submit_time，score

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) <5
and score<60;