mysql 函数使用

CREATE TABLE T_Person (FIdNumber VARCHAR(20),
FName VARCHAR(20),FBirthDay DATETIME,
FRegDay DATETIME,FWeight DECIMAL(10,2));

INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) 
VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32);

-- 数学
SELECT RAND(),FWeight,ROUND(FWeight),ROUND(FWeight,1),FLOOR(FWeight),CEIL(FWeight),CEILING(FWeight*-1),POWER(FWeight, 2),SQRT(FWeight) FROM T_Person;
SELECT SIN(FWeight),COS(FWeight),ASIN(1/FWeight),ACOS(1/FWeight),TAN(FWeight),ATAN(FWeight),ATAN(FWeight, 2),COT(FWeight),PI(),DEGREES(FWeight),RADIANS(FWeight) FROM T_Person;
SELECT SIGN(FWeight-48.68),MOD(FWeight, 5),LOG(FWeight),LOG(10, FWeight),LOG10(FWeight) FROM T_Person;


-- 字符串
SELECT FName,LENGTH(FName),LOWER(FName),UPPER(FName),LTRIM(' abc '),RTRIM(' abc '),TRIM(' abc ') FROM T_Person;
SELECT FName,REPLACE(FName,'i', '111'),LEFT(FName,1),RIGHT(FName, 1),SUBSTRING('abcdef111',2,3),LOCATE('bcd','abcdef111'),ASCII('a'),CHAR(97),CONCAT('a','b','c') FROM T_Person;

-- 日期
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME();
SELECT FBirthDay,DATE_ADD(FBirthDay,INTERVAL 1 WEEK) as w1,DATE_ADD(FBirthDay,INTERVAL 2 MONTH) as m2,DATE_ADD(FBirthDay,INTERVAL 5 QUARTER) as q5 FROM T_Person;
SELECT NOW(),ADDDATE(NOW(),INTERVAL 1 DAY),DATE_ADD(NOW(),INTERVAL -1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY),DATEDIFF(NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY));
SELECT NOW(),DAYNAME(NOW()),DATE_FORMAT(NOW(),'%Y年%m月%e日 %T');

-- 其他函数
-- 类型转换
-- 可选值缩写说明 
-- BINARY字符串
-- CHAR 字符串类型
-- DATE 日期类型
-- DATETIME 时间日期类型
-- SIGNED INTEGER 有符号整数
-- TIME 时间类型
-- UNSIGNED INTEGER 无符号整数
SELECT CAST('-30' AS SIGNED) as sig,CONVERT ('36', UNSIGNED INTEGER) as usig,CAST('2008-08-08' AS DATE) as d,CONVERT ('08:09:10', TIME) as t;
SELECT CONV('26',10,2), CONV(26,10,2),CONV('7D',16,8);
UPDATE T_Person SET FBirthDay=null,FRegDay=null WHERE FName='Smith';
SELECT FName,FBirthDay,FRegDay,COALESCE(FBirthDay,FRegDay,'2008-08-08'),IFNULL(FBirthDay,'123,456'),NULLIF(FBirthDay,FRegDay) AS ImportDay FROM T_Person;


-- 流程控制
SELECT FName,(CASE FName
WHEN 'Tom' THEN 'GoodBoy'
WHEN 'Lily' THEN 'GoodGirl'
WHEN 'Sam' THEN 'BadBoy'
WHEN 'Kerry' THEN 'BadGirl'
ELSE 'Normal'
END) as isgood
FROM T_Person;

SELECT
FName,
FWeight,
(CASE
WHEN FWeight<40 THEN 'thin'
WHEN FWeight>50 THEN 'fat'
ELSE 'ok'
END) as isnormal
FROM T_Person;

SELECT
FName,
FWeight,
IF(FWeight>50,'太胖','正常') AS ISTooFat
FROM T_Person;

SELECT FWeight,Round(FWeight),
BIN(Round(FWeight)) as b,
OCT(Round(FWeight)) as o,
HEX(Round(FWeight)) as h
FROM T_Person;

-- 填充函数
SELECT FName,LPAD(FName,5,'*'),RPAD(FName,5,'*') FROM T_Person;

-- REPEAT()函数用来得到一个子字符串重复了若干次所组成的字符
SELECT REPEAT('*',5), REPEAT('OK',3), SPACE(5);

-- 字符串颠倒
SELECT FName, REVERSE(FName) FROM T_Person;

-- 字符串的集合操作
FIELD()函数用于计算字符串在一个字符串集合中的位置
SELECT ELT(2, 'ej', 'Heja', 'hej', 'foo'),ELT(4, 'ej', 'Heja', 'hej', 'foo');
SELECT FIELD('vip','normal','member','vip') as f1,FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo') as f2;
SELECT FIND_IN_SET('b','a,b,c,d') as f1,FIND_IN_SET('d','a,b,c,d') as f2,FIND_IN_SET('w','a,b,c,d') as f3;

-- 计算集合中的最大最小值  GREATEST()函数和LEAST()函数用于计算一个集合中的最大和最小值
SELECT GREATEST(2,7,1,8,30,4,3,99,2,222,12),LEAST(2,7,1,8,30,4,3,99,2,222,12);

-- 辅助功能函数
SELECT CURRENT_USER,USER(),SYSTEM_USER(),SESSION_USER(),DATABASE(),VERSION();
-- ENCODE(str,pass_str)函数使用pass_str 做为密钥加密str，函数的返回结果是一个与string 一样长的二进制字符,需要使用BLOB列类型。
-- DECODE()函数使用pass_str 作为密钥解密经ENCODE加密后的字符串crypt_str
-- SELECT FName,MD5(FName),SHA1(FName),ENCODE(FName,'aha'),Length(ENCODE(FName,'aha')),DECODE(ENCODE(FName,'aha'),'aha')FROM T_Person
-- 全局唯一字符串
SELECT UUID(),UUID_SHORT();

mysql-sql高级应用:  http://www.cnblogs.com/Aiapple/p/5683030.html

mysql日常运维与参数调优: http://www.cnblogs.com/Aiapple/p/5698157.html

mysql 管理 ： http://www.cnblogs.com/Aiapple/tag/mysql%E7%AE%A1%E7%90%86/