目录
6.2.3 CEIL(X) && CEILING(X) && FLOOR(X)
6.2.5 ROUND(x) && ROUND(X,Y) && TRUNCATE(X,Y)
6.2.7 POW(X,Y) && POWER(X,Y) && EXP(x)
6.2.9 RADIANS(X) && DEGREES(X)
6.2.10 SIN(X) && ASIN(X) && COS(X) && ACOS(X) && TAN(X) && ATAN(X) && COT(X)
6.3.1 CHAR_LENGTH(STR) && LENGTH(STR)
6.3.2 CONCAT(S1,S2,...) && CONCAT_WS(X, S1,S2,...)
6.3.4 LOWER(STR) && LCASE(STR) && UPPER(STR) && UCASE(STR)
6.3.6 LPAD(S1,LEN,S2) && RPAD(S1,LEN,S2)
6.3.7 LTRIM(S) && RTRIM(S) && TRIM(S)
6.3.10 SPACE(N) && REPLACE(S,S1,S2)
6.3.12 SUBSTRING(S,N,LEN) && MIN(S,N,LEN)
6.3.13 LOCATE(STR1,STR) && POSITION(STR1 IN STR) && INSTR(STR, STR1)
6.4.1 CURDATE && CURRENT_DATE && CURTIME && CURRENT_TIME
6.4.2 CURRENT_TIMESTAMP && LOCALTIME && NOW && SYSDATE
6.2 数学函数
6.2.1 ABS(X) && PI()
SELECT ABS(2), ABS(-3.3), ABS(-33);
SELECT PI();6.2.2 SQRT(X) && MOD(X,Y)
SELECT SQRT(9), SQRT(40), SQRT(-49);
SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6);6.2.3 CEIL(X) && CEILING(X) && FLOOR(X)
SELECT CEIL(-3.35),CEILING(3.35);
SELECT FLOOR(-3.35), FLOOR(3.35);6.2.4 RAND && RAND(X)
SELECT RAND(),RAND(),RAND();
SELECT RAND(10),RAND(10),RAND(11);6.2.5 ROUND(x) && ROUND(X,Y) && TRUNCATE(X,Y)
-- ROUND(X) 返回最接近与参数X的整数，对X进行四舍五入
-- ROUND(X,Y) 返回最接近于X的数，其值保留到小数点后的Y位，若Y为负值，则将保留X值到小数点左边的Y位
-- TRUNCATE(X,Y) 返回被社区至小数点后Y位的数字X。若Y的值位0，则结果不带有小数点或不带有小数部分
-- 若Y设置为负数，则截去X小数点左起第Y位开始后面所有低位的值
SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);
SELECT ROUND(1.38, 1), ROUND(1.38, 0), ROUND(232.38, -1), ROUND(232.38,-2);
SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0), TRUNCATE(19.99,-1);6.2.6 SIGN(X)
-- 返回参数的符号，X位负、零、正时，分别返回-1、0、1
SELECT SIGN(-21),SIGN(0), SIGN(21);6.2.7 POW(X,Y) && POWER(X,Y) && EXP(x)
SELECT POW(2,2), POWER(2,2),POW(2,-2), POWER(2,-2);
SELECT EXP(3),EXP(-3),EXP(0);6.2.8 LOG(X) && LOG10(X)
SELECT LOG(3), LOG(-3);
SELECT LOG10(2), LOG10(100), LOG10(-100);6.2.9 RADIANS(X) && DEGREES(X)
SELECT RADIANS(90),RADIANS(180);
SELECT DEGREES(PI()), DEGREES(PI() / 2);6.2.10 SIN(X) && ASIN(X) && COS(X) && ACOS(X) && TAN(X) && ATAN(X) && COT(X)
SELECT SIN(1), ROUND(SIN(PI()));
SELECT ASIN(0.8414709848078965), ASIN(3);
SELECT COS(0),COS(PI()),COS(1);
SELECT ACOS(1),ACOS(0), ROUND(ACOS(0.5403023058681398));
SELECT TAN(0.3), ROUND(TAN(PI()/4));
SELECT ATAN(0.30933624960962325), ATAN(1);
SELECT COT(0.3), 1/TAN(0.3),COT(PI() / 4);
6.3 字符串函数
6.3.1 CHAR_LENGTH(STR) && LENGTH(STR)
-- CHAR_LENGTH 返回字符长度，一个多字节字符算作一个单字符
-- LENGTH 返回字节长度
SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');
SELECT LENGTH('date'), LENGTH('egg');6.3.2 CONCAT(S1,S2,...) && CONCAT_WS(X, S1,S2,...)
CONCAT 返回连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数位NULL，则结果返回NULL。如果所有参数都是非二进制字符串，则结果位非二进制字符串。如果自变量中含有任意一个二进制字符串，则结果返回一个二进制字符串
CONCAT_WS 使用X参数作为分隔符，连接字符串
SELECT CONCAT('My SQL', '5.6'),CONCAT('My',NULL, 'SQL');
SELECT CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');6.3.3 INSERT(S1, X, LEN, S2)
返回字符串S1，其子字符串起始于X位置和被字符串取代的LEN字符。如果X出熬过字符串的长度，则返回原始字符串。接入LEN的长度大于其他字符串的长度。则从位置X开始转换。若任何一个参数位NULL，则返回NULL
SELECT INSERT('Quest', 2, 4, 'What') AS col1,
INSERT('Quest', -1, 4, 'What') AS col2,
INSERT('Quest', 3, 100, 'Wh') AS col3;6.3.4 LOWER(STR) && LCASE(STR) && UPPER(STR) && UCASE(STR)
SELECT LOWER('BEAUTIFUL'), LCASE('Well');
SELECT UPPER('black'), UCASE('BLacK');6.3.5 LEFT(S,N) && RIGHT(S,N)
SELECT LEFT('football', 5);
SELECT RIGHT('football', 4);6.3.6 LPAD(S1,LEN,S2) && RPAD(S1,LEN,S2)
将字符串S1通过S2补全或截断到LEN长度
SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');6.3.7 LTRIM(S) && RTRIM(S) && TRIM(S)
SELECT '( book )', CONCAT('(', LTRIM (' book '),')');
SELECT '( book )', CONCAT('(', RTRIM (' book '),')');
SELECT '( book )', CONCAT('(', TRIM (' book '),')');6.3.8 TRIM(S1 FROM S)
删除字符串S中两端所有的子字符串S1。S1为可选项，在未指定情况下，删除空格
SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;6.3.9 REPECAT(S,N)
SELECT REPEAT('MySQL', 3);6.3.10 SPACE(N) && REPLACE(S,S1,S2)
SELECT CONCAT('(', SPACE(6), ')' );
SELECT REPLACE('xxx.mysql.com', 'x', 'w');
6.3.11 STRCMP(S1,S2)
SELECT STRCMP('txt', 'txt2'),STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');6.3.12 SUBSTRING(S,N,LEN) && MIN(S,N,LEN)
SELECT SUBSTRING('breakfast', 5 ) AS col1,
SUBSTRING('breakfast', 5, 3) AS col2,
SUBSTRING('lunch', -3 ) AS col3,
SUBSTRING('lunch', -5, 3) AS col4;
SELECT MID('breakfast', 5 ) as col1,
MID('breakfast', 5, 3) as col2,
MID('lunch', -3 ) as col3,
MID('lunch', -5, 3) as col4;6.3.13 LOCATE(STR1,STR) && POSITION(STR1 IN STR) && INSTR(STR, STR1)
SELECT LOCATE('ball','football'),
POSITION('ball'IN 'football'),
INSTR ('football', 'ball');6.3.14 REVERSE(S)
SELECT REVERSE('abc');6.3.15 ELT
ELT(N,STR1,STR2,STR3,...)根据N返回参数字符串
SELECT ELT(3,'1st','2nd','3rd'), ELT(3,'net','os');6.3.16 FIELD
FIELD(S,S1,S2,...)返回S在参数中的位置
SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as col1,
FIELD('Hi', 'Hey', 'Lo', 'Hilo', 'foo') as col2;6.3.17 FIND_IN_SET
返回子字符串在字符串列表中的位置
SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');6.3.18 MAKE_SET
根据二进制位选取指定字符串
SELECT MAKE_SET(1,'a','b','c') as col1,
MAKE_SET(1 | 4,'hello','nice','world') as col2,
MAKE_SET(1 | 4,'hello','nice',NULL,'world') as col3,
MAKE_SET(0,'a','b','c') as col4;6.4 日期和时间函数
6.4.1 CURDATE && CURRENT_DATE && CURTIME && CURRENT_TIME
SELECT CURDATE(),CURRENT_DATE(), CURDATE() + 0;
SELECT CURTIME(),CURRENT_TIME(), CURTIME() + 0;
-- CURDATE() + 0 将当前日期值转换为数值型
6.4.2 CURRENT_TIMESTAMP && LOCALTIME && NOW && SYSDATE
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();6.4.3 UNIX_TIMESTAMP
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
SELECT FROM_UNIXTIME('1364098609');6.4.4 UTC_DATE && UTC_TIME
SELECT UTC_DATE(), UTC_DATE() + 0;
SELECT UTC_TIME(), UTC_TIME() + 0;6.4.5
SELECT MONTH('2013-02-13');
SELECT MONTHNAME('2013-02-13');
SELECT DAYNAME('2013-02-13');
SELECT DAYOFWEEK('2011-02-13');
SELECT WEEKDAY('2011-02-13 22:23:00'), WEEKDAY('2011-07-01');
SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0), WEEK('2011-02-20',1);
SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');
SELECT DAYOFYEAR('2011-02-20');
SELECT DAYOFMONTH('2011-02-20');
SELECT YEAR('11-02-03'),YEAR('96-02-03');
SELECT QUARTER('11-04-01');
SELECT MINUTE('11-02-03 10:10:03');
SELECT SECOND('10:05:03');
SELECT EXTRACT(YEAR FROM '2011-07-02') AS col1,
EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03') AS col2,
EXTRACT(DAY_MINUTE FROM '2011-07-12 01:02:03') AS col3;
SELECT TIME_TO_SEC('23:23:00');
SELECT SEC_TO_TIME(2345),SEC_TO_TIME(2345)+0,
TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1,
ADDDATE ('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2,
DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1,
SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,
DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME('02:02:02', '02:00:00');
SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'), SUBTIME('02:02:02','02:00:00');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;
SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
SELECT GET_FORMAT (DATE,'EUR'), GET_FORMAT(DATE,'USA');
SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') );
6.5 条件判断
IF(EXPR, V1, V2)，如果EXPR是true，则IF返回V1，否则返回V2。类似于 三元操作符 exp ? v1 : v2
IFNULL(V1,V2)， 如果V1不为NULL返回V1，否则返回V2
CASE EXPR WHEN V1 THEN R1
WHEN V2 THEN R2
END
SELECT IF(12,2,3),
IF(1<2,'yes ','no'),
IF(STRCMP('test','test1'),'no','yes');
SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(1/0, 'wrong');
SELECT CASE 2 WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'more'
END;
SELECT CASE WHEN 1<0 THEN 'true'
ELSE 'false'
END;6.6 系统信息函数
--查看当前MySQL版本号
SELECT VERSION();
--查看当前用户的连接数
SELECT CONNECTION_ID();
--输出当前用户的连接信息
SHOW PROCESSLIST;
--查看当前使用的数据库
SELECT DATABASE(),SCHEMA();
--获取当前登录用户名称
SELECT USER(), CURRENT_USER(), SYSTEM_USER();
--返回字符串使用的字符集
SELECT CHARSET('abc'),
CHARSET(CONVERT('abc' USING latin1)),
CHARSET(VERSION());
--返回字符串排列方式
SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8));
-- 查看最后一个自动生成的列值
-- 一次插入一条记录
-- 首先创建表worker，其Id字段带有AUTO_INCREMENT约束
CREATE TABLE worker (Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,Name VARCHAR(30));
INSERT INTO worker VALUES(NULL, 'jimy');
INSERT INTO worker VALUES(NULL, 'Tom');
SELECT * FROM worker;
SELECT LAST_INSERT_ID();
-- 一次同时插入多条记录
INSERT INTO worker VALUES(NULL, 'Kevin'),(NULL,'Michal'),(NULL,'Nick');
SELECT * FROM worker;
SELECT LAST_INSERT_ID();
6.7 加密/解密
-- 加密密码
SELECT PASSWORD('newpwd');
SELECT MD5 ('mypwd');
SELECT ENCODE('secret','cry'), LENGTH(ENCODE('secret','cry'));
-- 解密密码
SELECT DECODE(ENCODE('secret','cry'),'cry');6.8 其他函数
-- 格式化数字，保留小数点位数为指定值
SELECT FORMAT(12332.123456, 4), FORMAT(12332.1,4), FORMAT(12332.2,0);
-- 不同进制数值之间转换
SELECT CONV('a',16,2),CONV(15,10,2),CONV(15,10,8),CONV(15,10,16);
-- 字符串网络点地址转换为数值网络地址
SELECT INET_ATON('209.207.224.40');
-- 数值网络地址转换为字符串网络点地址
SELECT INET_NTOA(3520061480);
-- 使用加锁、解锁函数
SELECT GET_LOCK('lock1',10) AS GetLock,
IS_USED_LOCK('lock1') AS ISUsedLock,
IS_FREE_LOCK('lock1') AS ISFreeLock,
RELEASE_LOCK('lock1') AS ReleaseLock;
-- 重复执行指定函数。
SELECT PASSWORD ( 'newpwd' );
SELECT BENCHMARK( 500000, PASSWORD ('newpwd') );
-- 改变字符串的默认字符集
SELECT CHARSET('string'), CHARSET(CONVERT('string' USING latin1));
-- 数据类型的转换
SELECT CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12',TIME);
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
