IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
select if(sva=1,"男","女") as ssva from taname where id = '111'
作为表达式的if也可以用CASE when来实现:
select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where id = '1'
在第一个方案的返回结果中, value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为 NULL。
IFNULL(expr1,expr2)
假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。
UNIX_TIMESTAMP()函数用法
UNIX_TIMESTAMP(date)
其中date可以是一个DATE字符串,一个DATETIME字符串,一个TIMESTAMP或者一个当地时间的YYMMDD或YYYMMDD格式的数字
用这个函数可以帮助我们在时间戳中筛选出某些天的数据。
1、unix_timestamp() 得到当前时间戳
2、如果参数date满足yyyy-MM-dd HH:mm:ss形式,则可以直接unix_timestamp(string date) 得到参数对应的时间戳
3、如果参数date满足yyyy-MM-dd HH:mm:ss形式,则我们需要指定date的形式,在进行转换
unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’)=1237532400
比如说:
SELECT
username,
FROM_UNIXTIME(create_time, "%Y-%m-%d") AS dat
FROM
`wp_user`
WHERE
create_time >=UNIX_TIMESTAMP(''2017-11-29')
AND
create_time
GROUP BY
dat
二、FROM_UNIXTIME()函数用法
FROM_UNIXTIME(unix_timestamp,format)参数unix_timestamp 时间戳 可以用数据库里的存储时间数据的字段
参数format 要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30
可以有的形式:
%M 月名字(January~December)
%W 星期名字(Sunday~Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun~Sat)
%d 月份中的天数, 数字(00~31)
%e 月份中的天数, 数字(0~31)
%m 月, 数字(01~12)
%c 月, 数字(1~12)
%b 缩写的月份名字(Jan~Dec)
%j 一年中的天数(001~366)
%H 小时(00~23)
%k 小时(0~23)
%h 小时(01~12)
%I 小时(01~12)
%l 小时(1~12)
%i 分钟, 数字(00~59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00~59)
%s 秒(00~59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ~6=Saturday )
%U 星期(0~52), 这里星期天是星期的第一天
%u 星期(0~52), 这里星期一是星期的第一天
%% 一个文字%
语法:from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值,即1970-1-1至今的秒,而13位的所谓毫秒的是不可以的。
对于13位时间戳,需要截取,然后转换成bigint类型,因为from_unixtime类第一个参数只接受bigint类型。例如:
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data where …
参数unix_timestamp 时间戳 可以用数据库里的存储时间数据的字段
参数format 要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30
使用举例:
SELECT
username,
FROM_UNIXTIME(create_time, "%Y-%m-%d") AS dat
FROM
`wp_user`
GROUP BY
dat
这样就能查出每天有哪些用户注册了。按天分组,你可以将数据导出后进行其他操作。
CAST函数语法规则是:
Cast(字段名 as 转换的类型 ),其中类型可以为:
CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
表table1
date:2015-11-03 15:31:26
select cast(date as signed) as date from table1;
结果如下:
date:20151103153126
select cast(date as char) as date from table1;
结果如下:
date:2015-11-03 15:31:26
实例2:
select cast(date as datetime) as date from table1;
结果如下:
date:2015-11-03 15:31:26
实例3:
select cast(date as date) as date from table1;
结果如下:
date:2015-11-03
实例4:
select cast(date as time) as date from table1;
结果如下:
date:15:31:26
这里date对应日期,time对应时间
实例5:
表table2
num:20
select cast(num as decimal(10, 2)) as num from table2
结果如下:
num:20.00
解释:decimal 数据类型最多可存储 38 个数字,所有数字都能够放到小数点的右边.decimal 数据类型存储了一个准确(精确)的数字表达法;不存储值的近似值.其中10是小数点左边和右边的数字个数之和(不包括小数点),2代表小数点右边的小数位数或数字个数.decimal(10,2)可以存储8位整数2位小数的数字.
MySQL字符串函数substring:字符串截取
MySQL 字符串截取函数:left(), right(), substring(), substring_index()。还有 mid(), substr()。其中,mid(), substr() 等价于 substring() 函数,substring() 的功能非常强大和灵活。
1. 字符串截取:left(str, length)
mysql> select left('example.com', 3);
+-------------------------+
| left('example.com', 3) |
+-------------------------+
| exa |
+-------------------------+
2. 字符串截取:right(str, length)
mysql> select right('example.com', 3);
+--------------------------+
| right('example.com', 3) |
+--------------------------+
| com |
+--------------------------+
实例:
#查询某个字段后两位字符
select right(last3, 2) as last2 from historydata limit 10;
#从应该字段取后两位字符更新到另外一个字段
update `historydata` set `last2`=right(last3, 2);
3. 字符串截取:substring(str, pos); substring(str, pos, len)
3.1 从字符串的第 4 个字符位置开始取,直到结束。
mysql> select substring('example.com', 4);
+------------------------------+
| substring('example.com', 4) |
+------------------------------+
| ple.com |
+------------------------------+
3.2 从字符串的第 4 个字符位置开始取,只取 2 个字符。
mysql> select substring('example.com', 4, 2);
+---------------------------------+
| substring('example.com', 4, 2) |
+---------------------------------+
| pl |
+---------------------------------+
3.3 从字符串的第 4 个字符位置(倒数)开始取,直到结束。
mysql> select substring('example.com', -4);
+-------------------------------+
| substring('example.com', -4) |
+-------------------------------+
| .com |
+-------------------------------+
3.4 从字符串的第 4 个字符位置(倒数)开始取,只取 2 个字符。
mysql> select substring('example.com', -4, 2);
+----------------------------------+
| substring('example.com', -4, 2) |
+----------------------------------+
| .c |
+----------------------------------+
我们注意到在函数 substring(str,pos, len)中, pos 可以是负值,但 len 不能取负值。
4. 字符串截取:substring_index(str,delim,count)
4.1 截取第二个 '.' 之前的所有字符。
mysql> select substring_index('www.example.com', '.', 2);
+------------------------------------------------+
| substring_index('www.example.com', '.', 2) |
+------------------------------------------------+
| www |
+------------------------------------------------+
4.2 截取第二个 '.' (倒数)之后的所有字符。
mysql> select substring_index('www.example.com', '.', -2);
+-------------------------------------------------+
| substring_index('www.example.com', '.', -2) |
+-------------------------------------------------+
| com.cn |
+-------------------------------------------------+
4.3 如果在字符串中找不到 delim 参数指定的值,就返回整个字符串
mysql> select substring_index('www.example.com', '.coc', 1);
+---------------------------------------------------+
| substring_index('www.example.com', '.coc', 1) |
+---------------------------------------------------+
| www.example.com |
+---------------------------------------------------+