1.字符串函数
函数 | 功能 |
concat(s1,s2,…,sn) | 连接字符串 |
insert(str,x,y,instr) | 将字符串str从第x位置开始,y个字符串替换为字符串instr |
lower(str) | 将字符串变成小写。类似函数为upper |
left(str,x) | 返回字符串最左边的x个字符。类似函数为right |
Lpad(str,n,pad) | 用字符串Pad 对str最左边填充,直到长度为 n个字符长度。类似函数为rpad |
Ltrim(str) | 去掉字符串str左侧的空格。类似函数 rtrim,trim |
Repeat(str,x) | 返回字符串Str 重复x次的结果 |
Replace(str,a,b) | 用字符串b替换字符串 str 中所有出现的字符串 a |
Strcmp(s1,s2) | 比较字符串s1 s2 的ASCII码值的大小 |
Substring(str,x,y) | 返回从字符串str x 位置起y个字符长度的内容 |
连接字符串
SELECT
CONCAT('hello','world','!'),
CONCAT('world','null');
+-----------------------------+------------------------+
| CONCAT('hello','world','!')
| CONCAT('world','null') |
+-----------------------------+------------------------+
| helloworld!
| worldnull
|
+-----------------------------+------------------------+
1 ROW IN SET (0.00 sec)
从x位置替换y个字符
SELECT INSERT('china greatwall',7,9,'baiyun');
+----------------------------------------+
| insert('china greatwall',7,9,'baiyun')
|
+----------------------------------------+
| china baiyun
|
+----------------------------------------+
1 row in set (0.00 sec)
大小写转换
SELECT
LOWER('greatWaLL'),
UPPER('greatWaLL');
+--------------------+--------------------+
| LOWER('greatWaLL') | UPPER('greatWaLL') |
+--------------------+--------------------+
| greatwall
| GREATWALL
|
+--------------------+--------------------+
1 row in set (0.00 sec)
LEFT(STR,X)和
RIGHT(STR,X)函数:分别返回字符串最左边的x个字符和最右边的x个字符。
如果第二个参数是null,那么将不反回任何字符串。
SELECT LEFT('greatwall',7),
LEFT('greatwall',
NULL),
RIGHT('greatwall',4);
+---------------------+------------------------+----------------------+
| LEFT('greatwall',7)
| LEFT('greatwall',NULL) | RIGHT('greatwall',4) |
+---------------------+------------------------+----------------------+
| greatwa
| NULL
| wall
|
+---------------------+------------------------+----------------------+
1 row in set (0.00 sec)
LPAD(STR,n,pad)和
RPAD(str,n,pad)函数:用字符串pad对str最左边和最右边进行填充,知道长度为n个字符长度
SELECT LPAD('great',20,'wall'),
RPAD('great',20,'wall');
+-------------------------+-------------------------+
| LPAD('great',20,'wall')
| RPAD('great',20,'wall')
|
+-------------------------+-------------------------+
| wallwallwallwalgreat
| greatwallwallwallwal
|
+-------------------------+-------------------------+
1 row in set (0.01 sec)
LIRIM(STR)和
RTRIM(STR)函数:去掉字符串str左侧和右侧的空格
SELECT
LTRIM('
|great wall|
'),
RTRIM('
|greate wall|
');
+---------------------------+----------------------------+
| LTRIM('
|great wall|
')
| RTRIM('
|greate wall|
')
|
+---------------------------+----------------------------+
| |great wall|
|
|greate wall|
|
+---------------------------+----------------------------+
1 row in set (0.00 sec)
repeat(str,x)函数:返回str重复X次结果
SELECT
REPEAT('mysql ',3);
+--------------------+
| REPEAT('mysql ',3) |
+--------------------+
| mysql mysql mysql |
+--------------------+
1 row in set (0.00 sec)
REPLACE(STR,a,b)函数:用常用字符串b替换字符串str中所有出现的字符串a
SELECT
REPLACE('beijing_2008','_2008','2008');
+----------------------------------------+
| REPLACE('beijing_2008','_2008','2008') |
+----------------------------------------+
| beijing2008
|
+----------------------------------------+
1 row in set (0.00 sec)
STRCMP(s1,s2)函数:比较字符串S1和S2的ASCII码值的大小
SELECT
STRCMP('a','b'),
STRCMP('b','b'),
STRCMP('c','b');
+-----------------+-----------------+-----------------+
| STRCMP('a','b')
| STRCMP('b','b') | STRCMP('c','b') |
+-----------------+-----------------+-----------------+
|
-1
|
0
|
1
|
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
trim(str)函数:去掉目标字符串开头和即为的空格
SELECT
TRIM('
|beijing2008|
');
+-------------------------------+
| TRIM('
|beijing2008|
')
|
+-------------------------------+
| |beijing2008|
|
+-------------------------------+
1 row in set (0.01 sec)
substring(str,x,y)函数:返回从字符串str中的第x位置起y个字符长度的字符串
SELECT
SUBSTRING('beijign2008',8,4);
+------------------------------+
| SUBSTRING('beijign2008',8,4) |
+------------------------------+
| 2008
|
+------------------------------+
1 row in set (0.00 sec)
2.数值函数
函数 | 功能 |
ABS(X) | 返回X的绝对值 |
CEIL(X) | 返回大于X的最小整数值 |
FLOOR(X) | 返回小于X的最大整数值 |
MOD(X,Y) | 返回X/Y的模 |
RAND() | 返回0~1内的随机值 |
ROUND(X,Y) | 返回参数X的四舍五入的有Y位小数的值 |
TRUNCATE(X,Y) | 返回数字X截断为Y位小数的结果 |
ABS(X)函数,返回绝对值
SELECT
ABS(1),
ABS(-1);
+--------+---------+
| ABS(1)
| ABS(-1)
|
+--------+---------+
|
1
|
1
|
+--------+---------+
1 row in set (0.00 sec)
CEIL(x)函数:返回大于x的最小整数值
SELECT
CEIL(-0.8),
CEIL(0.8);
+------------+-----------+
| CEIL(-0.8)
| CEIL(0.8)
|
+------------+-----------+
|
0
|
1
|
+------------+-----------+
1 row in set (0.00 sec)
FLOOR(X)函数:返回小鱼X的最大整数,和CEIL的用法想反
SELECT
FLOOR(-0.8),
FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
|
-1
|
0
|
+-------------+------------+
1 row in set (0.01 sec)
MOD(x,y)函数:返回X/Y的模
模数和被模数的任何一个味NULL结果都为NULL,例如:
SELECT
MOD(15,10),
MOD(1,11),
MOD(
NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
|
5
|
1
|
NULL
|
+------------+-----------+--------------+
1 row in set (0.01 sec)
RAND()函数:返回0~1内的随机数
SELECT
RAND(),
RAND();
+------------------+--------------------+
| RAND()
| RAND()
|
+------------------+--------------------+
| 0.73975877532713 | 0.5370678152302911 |
+------------------+--------------------+
1 row in set (0.00 sec)
SELECT
RAND()*100,
RAND()*100;
+-------------------+-------------------+
| RAND()*100
| RAND()*100
|
+-------------------+-------------------+
| 46.60627809037108 | 71.91104895613253 |
+-------------------+-------------------+
1 row in set (0.00 sec)
1~100的随即整数
SELECT
CEIL(
RAND()*100),
CEIL(
RAND()*100);
+------------------+------------------+
| CEIL(RAND()*100) | CEIL(RAND()*100) |
+------------------+------------------+
|
20
|
83
|
+------------------+------------------+
1 row in set (0.00 sec)
ROUNAD(x,y)函数:返回参数X的四舍五入的有y为小数的值
SELECT
ROUND(1.5),
ROUND(1.55,1),
ROUND(111,2),
ROUND(1.1,2);
+------------+---------------+--------------+--------------+
| ROUND(1.5) | ROUND(1.55,1) | ROUND(111,2) | ROUND(1.1,2) |
+------------+---------------+--------------+--------------+
|
2
|
1.6
|
111
|
1.10
|
+------------+---------------+--------------+--------------+
1 row in set (0.00 sec)
TRUNCATE(X,Y)函数:返回数字X截断为Y位小数的结果(非四舍五入)
SELECT
ROUND(1.235,2),
TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
|
1.24
|
1.23
|
+----------------+-------------------+
1 row in set (0.00 sec)
3.日期和时间函数
函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前时间和日期 |
UNIX_TIMESTAMP(date) | 返回日期date的UNIX时间戳 |
FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date 为一年中的第几周 |
YEAR(date) | 返回date的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time分钟值 |
MONTHNAME(date) | 返回date的月份名 |
DATE_FORMAT(date,fmt) | 返回字符串fmt格式化日期 date值 |
DATE_ADD(date,interval expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr1,expr2) | 返回起始时间expr1 和结束时间 expr2之间的天数 |
CURDATE()函数:返回当前日期,只包含年月日,同
CURRENT_DATE()
SELECT
CURDATE(),
CURRENT_DATE();
+------------+----------------+
| CURDATE()
| CURRENT_DATE() |
+------------+----------------+
| 2012-12-04 | 2012-12-04
|
+------------+----------------+
1 row in set (0.00 sec)
URTIME()函数:返回当前时间,只包含时分秒,同
CURRENT_TIME()
SELECT
CURTIME(),
CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 16:10:48
| 16:10:48
|
+-----------+----------------+
1 row in set (0.00 sec)
NOW()函数:返回当前的日期和时间
SELECT
NOW();
+---------------------+
| NOW()
|
+---------------------+
| 2012-12-04 16:11:43 |
+---------------------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP(date)函数:返回日期date的UNIX时间戳。
SELECT
UNIX_TIMESTAMP(
NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|
1354608756
|
+-----------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME(unixtime)函数:返回UNIXTIME时间戳的日期值,和
UNIX_TIMESTAMP(date)互为逆操作
SELECT
FROM_UNIXTIME(1354608756);
+---------------------------+
| FROM_UNIXTIME(1354608756) |
+---------------------------+
| 2012-12-04 16:12:36
|
+---------------------------+
1 row in set (0.00 sec)
week(date)、year(date)、month(date)、day(date)、hour(date)、minute(date)和
second(date)函数:
SELECT WEEK(
NOW()),
YEAR(
NOW()),
MONTH(
NOW()),
DAY(
NOW()),
HOUR(
NOW()),
MINUTE(
NOW()),
SECOND(
NOW()) ;
+-------------+-------------+--------------+------------+-------------+---------------+---------------+
| WEEK(NOW()) | YEAR(NOW()) | MONTH(NOW()) | DAY(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------+-------------+--------------+------------+-------------+---------------+---------------+
|
49
|
2012
|
12
|
4
|
16
|
15
|
15
|
+-------------+-------------+--------------+------------+-------------+---------------+---------------+
1 row in set (0.00 sec)
date_format(date,fmt)函数:按字符串fmt 格式化日期date值,例如:
将下面的例子按照月,日,年的格式显示:
SELECT
DATE_FORMAT(
NOW(),‘%M,%D,%Y’);
+-------------------------------+
| DATE_FORMAT(NOW(),'%M,%D,%Y') |
+-------------------------------+
| December,4th,2012
|
+-------------------------------+
1 row in set (0.00 sec)
DATE_ADD(date,interval expr type)函数:返回与所给日期date 相差interval时间段的日期。
其中interval是间隔类型关键字,expr 是一个表达式,这个表达式对应后面的类型,type 是间隔类型, 有如下类型:HOUR、MINUTE、SECOND、YEAR、MONTH、DAY、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND和MINUTE_SCOND
下面第一列是当前日期,第二列是31天以后的日期,第三列是一年二个月后的日期
SELECT
NOW(),
DATE_ADD(
NOW(),
INTERVAL
31
DAY) after31days,
DATE_ADD(
NOW(),
INTERVAL
'1_2'
YEAR_MONTH) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| NOW()
| after31days
| after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2012-12-04 16:22:30 | 2013-01-04 16:22:30 | 2014-02-04 16:22:30
|
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
同样也可以用负数让它返回之前的某个日期时间,如第1列返回了当前日期时间,第2列返回距离当前日期31天前的日期时间,第3列返回距离当前日期一年两个月前的日期时间。
SELECT
NOW(),
DATE_ADD(
NOW(),
INTERVAL
-31
DAY) after31days,
DATE_ADD(
NOW(),
INTERVAL
'-1_-2'
YEAR_MONTH) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| NOW()
| after31days
| after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2012-12-04 16:24:19 | 2012-11-03 16:24:19 | 2011-10-04 16:24:19
|
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
DATEDIFF(date1,date2)函数:计算两个日期之间相关的天数。
下面的例子计算出2012/1/1距今天有多少天
SELECT
DATEDIFF(
NOW(),'2012-01-01');
+------------------------------+
| DATEDIFF(NOW(),'2012-01-01') |
+------------------------------+
|
338
|
+------------------------------+
1 row in set (0.01 sec)
4.流程函数
函数 | 功能 |
if(value,t ,f) | 如果value 是真,返回t,否则返回f |
ifnull(value1,vale2) | 如果value1不为空返回 value1,否则返回 value2 |
case when [value1] then [result1]…else [default] end | 如果value1是真,返回 result1 ,否则返回default |
case [expr] when [value1] then [result1]…else [default] end | 如果expr等于value1,返回 result1 ,否则返回default ,这个主要用于等值比较,不适合范围比较 |
创建练习表
CREATE TABLE
salary(userid
INT,salary
DECIMAL(9,2));
Query OK, 0 rows affected (0.04 sec)
INSERT
INTO
salary
VALUES(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(6,NULL);
Query OK, 6 rows affected (0.01 sec)
Records: 6
Duplicates: 0
Warnings: 0
SELECT
*
FROM
salary;
+--------+---------+
| userid | salary
|
+--------+---------+
|
1 | 1000.00 |
|
2 | 2000.00 |
|
3 | 3000.00 |
|
4 | 4000.00 |
|
5 | 5000.00 |
|
6 |
NULL |
+--------+---------+
6 rows in set (0.00 sec)
if(vale,t ,f)
函数:这里认为月薪在2000元以上的职员属于高薪,用“high”表示;而2000以下属于低薪,用“low”表示
SELECT
userid,salary,
IF(salary>2000,'
high','
low')
FROM
salary;
+--------+---------+------------------------------+
| userid
| salary
| IF(salary>2000,'high','low') |
+--------+---------+------------------------------+
|
1
| 1000.00 | low
|
|
2
| 2000.00 | low
|
|
3
| 3000.00 | high
|
|
4
| 4000.00 | high
|
|
5
| 5000.00 | high
|
|
6
|
NULL | low
|
+--------+---------+------------------------------+
6 rows in set (0.00 sec)
ifnull(value1,value2)函数:用来替换NULL值
SELECT
IFNULL(salary,0 )
FROM
salary;
+-------------------+
| IFNULL(salary,0 ) |
+-------------------+
|
1000.00 |
|
2000.00 |
|
3000.00 |
|
4000.00 |
|
5000.00 |
|
0.00 |
+-------------------+
6 rows in set (0.00 sec)
CASE WHEN [value1] THEN [result1]…ELSE [DEFAULT] end 当薪水低于2000时,显示LOW,低于3000时,为MID,其于为HIGH
INSERT INTO
salary
VALUES(7,500),(8,1500),(9,2500);
SELECT
userid,salary,
CASE WHEN
salary<=2000
THEN
'low'
WHEN
salary<=3000
THEN
'mid'
ELSE
'high'
END FROM
salary
WHERE
salary
IS NOT NULL;
+--------+---------+--------------------------------------------------------------------------------+
| userid | salary
| CASE WHEN salary<=2000 THEN 'low' WHEN salary<=3000 THEN 'mid' ELSE 'high' END |
+--------+---------+--------------------------------------------------------------------------------+
|
1 | 1000.00 | low
|
|
2 | 2000.00 | low
|
|
3 | 3000.00 | mid
|
|
4 | 4000.00 | high
|
|
5 | 5000.00 | high
|
|
7 |
500.00
| low
|
|
8 | 1500.00 | low
|
|
9 | 2500.00 | mid
|
+--------+---------+--------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
CASE [expr] WHEN [value1] THEN [result1]…ELSE [DEFAULT] end
当值等于values1,返回result1,值为values2,返回result2…
下面的例子中,userid =3 ,显示pen , userid =5 ,显示pencil ,其余的显示book
SELECT
userid,salary,
CASE
userid
WHEN
3
THEN
'pen'
WHEN
5
THEN
'pencil'
ELSE
'book'
END FROM
salary
+--------+---------+--------------------------------------------------------------------+
| userid | salary
| CASE userid WHEN 3 THEN 'pen' WHEN 5 THEN 'pencil' ELSE 'book' END |
+--------+---------+--------------------------------------------------------------------+
|
1 | 1000.00 | book
|
|
2 | 2000.00 | book
|
|
3 | 3000.00 | pen
|
|
4 | 4000.00 | book
|
|
5 | 5000.00 | pencil
|
|
6 |
NULL | book
|
|
7 |
500.00
| book
|
|
8 | 1500.00 | book
|
|
9 | 2500.00 | book
|
+--------+---------+--------------------------------------------------------------------+
9 rows in set (0.00 sec)
5.常用函数
函数 | 功能 |
database() | 返回当前数据库名 |
version() | 返回当前数据库版本 |
user() | 返回当前用户登陆名 |
USE
test;
SELECT DATABASE();
SELECT
VERSION();
SELECT USER();