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 FNameWHEN 'Tom' THEN 'GoodBoy'WHEN 'Lily' THEN 'GoodGirl'WHEN 'Sam' THEN 'BadBoy'WHEN 'Kerry' THEN 'BadGirl'ELSE 'Normal'END) as isgoodFROM T_Person;SELECTFName,FWeight,(CASEWHEN FWeight<40 THEN 'thin'WHEN FWeight>50 THEN 'fat'ELSE 'ok'END) as isnormalFROM T_Person;SELECTFName,FWeight,IF(FWeight>50,'太胖','正常') AS ISTooFatFROM T_Person;SELECT FWeight,Round(FWeight),BIN(Round(FWeight)) as b,OCT(Round(FWeight)) as o,HEX(Round(FWeight)) as hFROM 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/