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);
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();