mysql 常用函数范例详解

菜鸟教程：

MySQL 函数

 

IN  、NOT IN  、  EXISTS   、 NOT  EXISTS   

子关联查询数据

实际应用：过滤出在指定范围内的数据

mysql 子关联查询之 EXISTS 和 NOT EXISTS（一）

mysql 子关联查询之 EXISTS 和 NOT EXISTS（二） 

 

CAST (expression AS data_type)

转换数据类型

实际应用：需要排序的字段为varchar,需要转换数据类型

                 时间转化，范围搜索，也可用DATE_FORMAT(d,f)函数处理

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

• 二进制，同带binary前缀的效果 : BINARY    
• 字符型，可带参数 : CHAR()     
• 日期 : DATE     
• 时间: TIME     
• 日期时间型 : DATETIME     
• 浮点数 : DECIMAL      
• 整数 : SIGNED
• 无符号整数 : UNSIGNED 

cast 与convert的区别

 

范例：

#获取当前日期 SELECT  CAST(NOW() AS DATE)； ->2019-02-23   #将值转换为DATETIME类型 select CAST('2019-03-06 13:23:29' AS DATETIME)； ->2019-03-06 13:23:29   #将值转换为TIME类型 SELECT CAST('08:30:30' AS TIME); ->08:30:30   #varchar 转 SIGNED SELECT CAST('6.66' AS SIGNED ); -> 6   #varchar 转 DECIMAL SELECT CAST('9.12345' AS DECIMAL ); ->9   #varchar 转 DECIMAL  # 精度与小数位数分别为5与2；精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数 SELECT CAST('9.12345' AS DECIMAL(5,2) ); ->9.12

 

 CAST  与  DATE_FORMAT 时间处理上的区别

 CAST比较方便一些，（可用   DATETIME  OR  DATE）

 CAST  :

SELECT
	login_code,
	last_login_date
FROM
	user
WHERE
	last_login_date <=  CAST('2019-02-01 14:38:39' AS DATETIME);

DATE_FORMAT  :

SELECT
	login_code,
	last_login_date
FROM
	user
WHERE
	last_login_date <=  DATE_FORMAT('2019-02-01 04:38:39','%y-%m-%d %H:%i:%S');

结果相同 ：

login_code	last_login_date
YT_admin	2019-01-31 14:38:39
lampoy02	2019-01-31 11:42:47

 

 

BETWEEN value1  AND  value2

SQL BETWEEN Condition将返回表达式在value1和value2（包括）范围内的记录

实际应用：数值区间、日期区间范围搜索

对于SQL Server，PostgreSQL和SQLite：

SELECT *
FROM orders
WHERE order_date BETWEEN '2016/04/19' AND '2016/05/01';

对于Oracle（使用TO_DATE函数）：

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2016/04/19', 'yyyy/mm/dd')
AND TO_DATE ('2016/05/01', 'yyyy/mm/dd');

对于MySQL和MariaDB（使用CAST函数）：

SELECT *
FROM orders
WHERE order_date BETWEEN CAST('2016/04/19' AS DATE) AND CAST('2016/05/01' AS DATE);

 

 

IF(expr,v1,v2)

如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

实际应用：树的子节点判断，true  or  false

SELECT IF(1 > 0,'正确','错误')    
->正确

 

IFNULL(v1,v2)

如果 v1 的值为 NULL，则返回 v2，否则返回 v1。

SELECT IFNULL(null,'Hello Word')
->Hello Word

SELECT IFNULL(1,'Hello Word')
->1

SELECT IFNULL(1/0,'Hello Word')
->Hello Word

 

ISNULL(expression)

判断表达式是否为 NULL

实际应用：可与  IF(expr,v1,v2)  函数结合使用

SELECT ISNULL(NULL);
->1

SELECT ISNULL(' ');
->0

 

 

CASE

 CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

MySQL 的CASE WHEN 语句使用说明

Mysql查询时case when语句的使用

1. 第一种用法：case后面跟列名，when后面跟对应值

   CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
   END

   这种用法正好解决我们刚刚提出的问题，当sex值为0时当前列显示“男”，否则显示“女”，sql写法如下：

   SELECT
   	(
   	    CASE sex
   	    WHEN 0 THEN
   		    '男'
   	    ELSE
   		    '女'
   	    END
   	) AS 性别,
   	avg(score) AS 平均分
   FROM
   	grade
   GROUP BY
   	sex;
   +--------+-----------+
   | 性别   | 平均分    |
   +--------+-----------+
   | 男     |   67.2500 |
   | 女     |   89.5000 |
   +--------+-----------+
   2 rows in set (0.00 sec)

2. 第二种用法：case后面空白，when后面跟着判断条件

   CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
   END

   针对于这种写法，我们考虑这样一种需求，学生成绩是有评分的，大于等于90分的学生是A，小于90分大于等于60分的学生是B， 其余的学生是C，现在要查询评分为A、B、C的学生成绩的平均分分别是多少，因为成绩评分并不是单独的一列，所以不能简单的 使用 group by 来分组实现了，但是可以利用 case when 语句实现，写起来也很简单，看看下面的sql语句就知道了！

   SELECT
   	(
   		CASE
   		WHEN score >= 90 THEN
   			'A'
   		WHEN score < 60 THEN
   			'C'
   		ELSE
   			'B'
   		END
   	) AS 等级,
   	avg(score) AS 平均分
   FROM
   	grade
   GROUP BY
   	等级;
   +--------+-----------+
   | 等级   | 平均分    |
   +--------+-----------+
   | A      |   95.0000 |
   | B      |   74.8000 |
   | C      |   47.3333 |
   +--------+-----------+
   3 rows in set (0.00 sec)

 

 

CONCAT(s1,s2...sn)

字符串 s1,s2 等多个字符串合并为一个字符

实际应用：SQL中地址的拼接（省，市，乡）

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

 

 

 REPLACE(s,s1,s2)

将字符串 s2 替代字符串 s 中的字符串 s1

将字符串 abc 中的字符 a 替换为字符 x：

SELECT REPLACE('abc','a','x') --xbc

 

 

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

能将相同的行组合起来

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

SELECT student_name,
         GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

SELECT student_name,
         GROUP_CONCAT(DISTINCT test_score
                      ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;

在MySQL中，您可以获得表达式组合的连接值。要消除重复值，请使用该 DISTINCT子句。要对结果中的值进行排序，请使用该ORDER BY子句。要按相反顺序排序，请将DESC （descending）关键字添加到要在ORDER BY子句中排序的列的名称中。默认为升序; 这可以使用ASC关键字明确指定。组中值之间的默认分隔符是逗号（,）。要明确指定分隔符，请使用SEPARATOR后跟应在组值之间插入的字符串文字值。要完全消除分隔符，请指定 SEPARATOR ''。

group_concat函数详解

GROUP_CONCAT(expr)

 

DATEDIFF(d1,d2)

计算日期 d1->d2 之间相隔的天数

实际应用：计算日期差值

SELECT DATEDIFF('2019-02-24','2019-02-20')
-> 4

 

DATE_FORMAT(d,f)

按表达式 f的要求显示日期 d

实际用途：时间格式化     OR     时间区间搜索

SELECT DATE_FORMAT('2019-02-26 22:30:45','%Y-%m-%d');
->2019-02-26

SELECT DATE_FORMAT('2019-02-26 22:30:45','%Y-%m-%d %H:%i:%S');
->2019-02-26 22:30:45