格式化时间日期

函数列表

日期和时间处理函数

mysql查询今天、昨天、本周、本月、上一月 、今年数据

时间戳转格式化日期方法：

select from_unixtime(1576736151, '%Y-%m-%d %H:%i:%S');

格式化日期转时间戳方法：

select unix_timestamp('2019-12-19 14:15:51');

时间/日期转格式化日期方法：

select date_format(now(), '%Y-%m-%d %H:%i:%S');
select date_format('2019-12-19', '%Y-%m-%d %H:%i:%S');

格式化日期格式如下：

逻辑判断

COALLESCE

COALESCE(expression, value1, value2, valuen)

返回按顺序取第一个不为NULL的值，都为NULL则返回NULL, 因为不判断空字符串，所以如果有空字符串则需要先处理，例如：

COALESCE(NULLIF(text1, ''), NULLIF(text2, ''))

其他替代

--MYSQL: 
  IFNULL(expression,value) 
--MSSQLServer: 
  ISNULL(expression,value) 
--Oracle: 
  NVL(expression,value)

IF

Mysql的if既可以作为表达式用，也可在存储过程中作为流程控制语句使用
IF表达式

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 0 and expr1 NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

作为表达式的if也可以用CASE when来实现：

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

例如：

SELECT CASE 1 WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'more' END
              as testCol

将输出one

IFNULL(expr1,expr2)

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

mysql> SELECT IFNULL(1,0);
        -> 1

mysql> SELECT IFNULL(NULL,10);
        -> 10

mysql> SELECT IFNULL(1/0,10);
        -> 10

mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

IFNULL(expr1,expr2) 的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、 REAL或 INTEGER。

IF ELSE 做为流程控制语句使用
if实现条件判断，满足不同条件执行不同的操作，这个我们只要学编程的都知道if的作用了，下面我们来看看mysql 存储过程中的if是如何使用的吧。

IF search_condition THEN
    statement_list
[ELSEIF search_condition THEN]
    statement_list ...
[ELSE
    statement_list]
END IF

当IF中条件search_condition成立时，执行THEN后的statement_list语句，否则判断ELSEIF中的条件，成立则执行其后的statement_list语句，否则继续判断其他分支。当所有分支的条件均不成立时，执行ELSE分支。search_condition是一个条件表达式，可以由“=、、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。

例如，建立一个存储过程，该存储过程通过学生学号（student_no）和课程编号（course_no）查询其成绩（grade），返回成绩和成绩的等级，成绩大于90分的为A级，小于90分大于等于80分的为B级，小于80分大于等于70分的为C级，依次到E级。那么，创建存储过程的代码如下：

create procedure dbname.proc_getGrade
(stu_no varchar(20),cour_no varchar(10))
BEGIN
declare stu_grade float;
select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;
if stu_grade>=90 then
    select stu_grade,'A';
elseif stu_grade=80 then
    select stu_grade,'B';
elseif stu_grade=70 then
    select stu_grade,'C';
elseif stu_grade70 and stu_grade>=60 then
    select stu_grade,'D';
else
    select stu_grade,'E';
end if;
END

注意：IF作为一条语句，在END IF后需要加上分号“;”以表示语句结束，其他语句如CASE、LOOP等也是相同的。

文本处理函数

SELECT * 
FROM party_course_study
WHERE LOCATE(findCode, '00001') > 0

表中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

substr(str, pos, len)

• str为列名/字符串
• pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么表示就从倒数第几个开始截取
• len为截取字符个数/长度

count(1), count(字段), count(*) 区别

• COUNT有几种用法？
• COUNT(字段名)和COUNT(*)的查询结果有什么不同？
• COUNT(1)和COUNT(*)之间有什么不同？
• COUNT(1)和COUNT(*)之间的效率哪个更高？
• 为什么《阿里巴巴Java开发手册》建议使用COUNT(*)
• MySQL的MyISAM引擎对COUNT(*)做了哪些优化？
• MySQL的InnoDB引擎对COUNT(*)做了哪些优化？
• 上面提到的MySQL对COUNT(*)做的优化，有一个关键的前提是什么？
• SELECT COUNT(*) 的时候，加不加where条件有差别吗？
• COUNT(*)、COUNT(1)和COUNT(字段名)的执行过程是怎样的？

count(expr) 返回expr值不为null的数量，结果是bigint，但是count(*) 会包含null，count(*)是SQL92标准语法，所以mysql做了很多优化。myisam中将表的总数单独记录，所以没有where条件时候count(*) 会直接返回总数。myisam是表级锁，不会有并发行操作，所以结果是准确的。innodb支持行级锁，行可能会被修改，所以通过低成本的索引（通常是最小索引）进行扫表，不关注表的具体内容，InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。MySQL会优先选择最小的非聚簇索引来扫表。优化的前提是查询语句中不包含where条件和group by条件。count(1)和count(*) 的优化是一样的，count(字段) 判断指定字段不为null，效率低。

select count(distinct cid) from notes;

聚合函数可以聚合多个列的计算值，例如

select sum(price * stock) from products;

concat系列函数

concat

mysql

select concat(id, name) as id_and_name from users;

返回两个字段拼接的值，如果有一个字段的值为null，则返回null

oracle, psql

可以使用||来分割，例如

select id || name as id_and_name from users;

好像是上面这么写的，好久没用了。

concat_ws

concat with separator, 使用分隔符分割

select concat_ws(',', id, name) as id_and_name from users;

返回结果例如，分隔符如果为null则返回null

id = 1, name = 2
id_and_name 1,2

group_concat

• 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
• 语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])

在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中

例如，查询相同姓名中年龄最小的用户

select name, min(age) from users group by name;

如果要查出所有

select name, age from users order by age;

则name可能重复，可以使用group_concat

select name, group_concat(age) from users group by name; 

拼接（concatenate）字段或者字符串

多数数据库采用||或者+ 来拼接，而Mysql使用concat() 函数拼接。

删除多余空格

rtrim() ltrim() trim()

AS 别名，可以用来创建一个计算字段，当列名不符合规定的字符例如包含空格的时候可以使用别名代替，在原名易混淆时候扩充它。别名也称作导出列（derivid column）

any_value

extract

EXTRACT()("提取"的意思) 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

就是返回出来具体的年，月，日

2008-12-29 16:25:46.635

1 SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
2 EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
3 EXTRACT(DAY FROM OrderDate) AS OrderDay
4 FROM Orders