MYSQL完全手册学习笔记（第七章）
MYSQL函数
mysql>selectround(10.33),round(64.50001),round(10.98547);
+--------------+-----------------+-----------------+
|round(10.33)|round(64.50001)|round(10.98547)|
+--------------+-----------------+-----------------+
|10|65|11|
+--------------+-----------------+-----------------+
1rowinset(0.00sec)
Round函数四舍五入
mysql>selecttruncate(1.78502356,3),truncate(-6235.3256984,10);
+------------------------+----------------------------+
|truncate(1.78502356,3)|truncate(-6235.3256984,10)|
+------------------------+----------------------------+
|1.785|-6235.3256984000|
+------------------------+----------------------------+
1rowinset(0.00sec)
mysql>selecttruncate(1789.09854,-2),truncate(-629.6953245,-1);
+-------------------------+---------------------------+
|truncate(1789.09854,-2)|truncate(-629.6953245,-1)|
+-------------------------+---------------------------+
|1700|-620|
+-------------------------+---------------------------+
1rowinset(0.00sec)
Truncate把一个数字截短成为一个指定小数个数的数字，注意取负数的时候哦
mysql>selectabs(-789);
+------------+
|abs(-789)|
+------------+
|789|
+------------+
1rowinset(0.00sec)
Abs绝对值
mysql>select*fromtoys;
+----+-----------------+-------+-------+-------+----------+
|id|item|ctm|ctr|ctc|quantity|
+----+-----------------+-------+-------+-------+----------+
|0|ww|3.00|3.00|3.00|200|
|1|huggybear|5.00|7.40|9.90|300|
|2|coloringbook|3.00|5.00|7.99|2000|
|3|boardgame|12.00|20.00|39.90|1430|
|4|activitycd-rom|2.00|6.00|17.49|3780|
|6|ww|6.00|6.00|6.00|100|
+----+-----------------+-------+-------+-------+----------+
6rowsinset(0.00sec)
mysql>select*fromtoysgroupbyitem;
+----+-----------------+-------+-------+-------+----------+
|id|item|ctm|ctr|ctc|quantity|
+----+-----------------+-------+-------+-------+----------+
|4|activitycd-rom|2.00|6.00|17.49|3780|
|3|boardgame|12.00|20.00|39.90|1430|
|2|coloringbook|3.00|5.00|7.99|2000|
|1|huggybear|5.00|7.40|9.90|300|
|0|ww|3.00|3.00|3.00|200|
+----+-----------------+-------+-------+-------+----------+
5rowsinset(0.01sec)
Groupby去掉重复的纪录
字符串函数
mysql>selectlength('123456');
+------------------+
|length('123456')|
+------------------+
|6|
+------------------+
1rowinset(0.00sec)
mysql>selectlength('fsfsdsesdsfdfsd');
+---------------------------+
|length('fsfsdsesdsfdfsd')|
+---------------------------+
|15|
+---------------------------+
1rowinset(0.00sec)
Length返回字符串的长度
mysql>selectrtrim("righthand"),ltrim("lefteye");
+--------------------------+----------------------+
|rtrim("righthand")|ltrim("lefteye")|
+--------------------------+----------------------+
|righthand|lefteye|
+--------------------------+----------------------+
1rowinset(0.00sec)
Ltrim（）去掉首部的空格
Rtrim（）去掉尾部的空格
mysql>selecttrim('ressere');
+-----------------------+
|trim('ressere')|
+-----------------------+
|ressere|
+-----------------------+
Trim去掉首尾的空格
mysql>selectleft('hello',4),right('welcometoshenyang',5);
+-----------------+--------------------------------+
|left('hello',4)|right('welcometoshenyang',5)|
+-----------------+--------------------------------+
|hell|nyang|
+-----------------+--------------------------------+
1rowinset(0.00sec)
Left和right从左边或者右边返回字符
mysql>selectleft(right('market',5),3);
+---------------------------+
|left(right('market',5),3)|
+---------------------------+
|ark|
+---------------------------+
1rowinset(0.00sec)
取得中间的部分
mysql>selectsubstring('market',2,3);
+-------------------------+
|substring('market',2,3)|
+-------------------------+
|ark|
+-------------------------+
1rowinset(0.00sec)
Substring也是可以的
mysql>selectconcat('sds','dsee');
+----------------------+
|concat('sds','dsee')|
+----------------------+
|sdsdsee|
+----------------------+
1rowinset(0.00sec)
mysql>selectconcat('sds',"ee22");
+----------------------+
|concat('sds',"ee22")|
+----------------------+
|sdsee22|
+----------------------+
1rowinset(0.00sec)
mysql>selectconcat('sds',23);
+------------------+
|concat('sds',23)|
+------------------+
|sds23|
+------------------+
1rowinset(0.00sec)
Concat连接字符串
mysql>selectinsert('21010419801010171*',18,1,'x');
+---------------------------------------+
|insert('21010419801010171*',18,1,'x')|
+---------------------------------------+
|21010419801010171x|
+---------------------------------------+
1rowinset(0.00sec)
mysql>selectinsert('IamJoeCool',10,4,'camel');
+--------------------------------------+
|insert('IamJoeCool',10,4,'camel')|
+--------------------------------------+
|IamJoecamel|
+--------------------------------------+
1rowinset(0.00sec)
Insert函数可以把一个字符串的指定部分（定义了起始位置和长度）替换为一个新值
mysql>selectrepeat('ha',5);
+----------------+
|repeat('ha',5)|
+----------------+
|hahahahaha|
+----------------+
1rowinset(0.05sec)
Repeat函数用于重复字符串
mysql>selectreverse(repeat('ha',3));
+-------------------------+
|reverse(repeat('ha',3))|
+-------------------------+
|ahahah|
+-------------------------+
1rowinset(0.00sec)
Reverse函数颠倒字符串
日期和时间函数
mysql>selectnow();
+---------------------+
|now()|
+---------------------+
|2008-10-1516:44:00|
+---------------------+
1rowinset(0.03sec)
mysql>selectcurtime(),curdate();
+-----------+------------+
|curtime()|curdate()|
+-----------+------------+
|16:44:29|2008-10-15|
+-----------+------------+
1rowinset(0.02sec)
mysql>selectdate_add('2004-01-0100:00:00',interval6month);
+--------------------------------------------------+
|date_add('2004-01-0100:00:00',interval6month)|
+--------------------------------------------------+
|2004-07-0100:00:00|
+--------------------------------------------------+
1rowinset(0.00sec)
第一个例子在初始时间加上6个月并返回结果
mysql>selectdate_add('2004-03-1412:20:00',interval'1203:45'day_minute);
+----------------------------------------------------------------+
|date_add('2004-03-1412:20:00',interval'1203:45'day_minute)|
+----------------------------------------------------------------+
|2004-03-2616:05:00|
+----------------------------------------------------------------+
1rowinset(0.00sec)
第二个例子向初始时间增加12天3小时45分钟，并显示结果
mysql>selectdate_sub('2004-01-0107:00:00',interval8hour);
+-------------------------------------------------+
|date_sub('2004-01-0107:00:00',interval8hour)|
+-------------------------------------------------+
|2003-12-3123:00:00|
+-------------------------------------------------+
1rowinset(0.00sec)
从一个日期值减去8个小时
mysql>selectdate_sub('2004-01-0107:20:00',interval'13-4',year_month);
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'year_
month)'atline1
日期值减去13年4个月没有效果！！！！
P135
mysql>selectextract(yearfrom'1999-07-02');
+--------------------------------+
|extract(yearfrom'1999-07-02')|
+--------------------------------+
|1999|
+--------------------------------+
1rowinset(0.00sec)
mysql>selectextract(year_monthfrom'1999-07-02');
+--------------------------------------+
|extract(year_monthfrom'1999-07-02')|
+--------------------------------------+
|199907|
+--------------------------------------+
1rowinset(0.00sec)
Extract（）函数获得一个日期的指定部分
mysql>selectperiod_diff(200302,199802);
+----------------------------+
|period_diff(200302,199802)|
+----------------------------+
|60|
+----------------------------+
1rowinset(0.02sec)
period_diff函数返回两个日期之间的差值（月数）
mysql>selectname,dob,round(period_diff(date_format(now(),'%y%m'),date_format(d
ob,'%y%m'))/12,1)asagefrombirthday;
+-------+---------------------+-------+
|name|dob|age|
+-------+---------------------+-------+
|raoul|1978-06-0400:00:00|30.3|
|luis|1965-11-1700:00:00|-57.1|
|larry|1971-08-1900:00:00|37.2|
|moe|1992-01-2300:00:00|16.8|
+-------+---------------------+-------+
4rowsinset(0.02sec)
为什么出来负数？？？
mysql>selectname,dob,(to_days(now())-to_days(dob))/365asagefrombirthday;
+-------+---------------------+---------+
|name|dob|age|
+-------+---------------------+---------+
|raoul|1978-06-0400:00:00|30.3890|
|luis|1965-11-1700:00:00|42.9425|
|larry|1971-08-1900:00:00|37.1863|
|moe|1992-01-2300:00:00|16.7425|
+-------+---------------------+---------+
4rowsinset(0.00sec)
mysql>selectmember,to_days(check_in_date)-to_days(check_out_date)asout_perio
dfromlibrary;
+---------+------------+
|member|out_period|
+---------+------------+
|joe|3|
|john|6|
|johann|4|
|mark|30|
|jthomas|8|
+---------+------------+
5rowsinset(0.00sec)
查询出来每个电影借出多长时间
mysql>selectmemberfromlibrarywhereto_days(check_in_date)-to_days
->(check_out_date)>4;
+---------+
|member|
+---------+
|john|
|mark|
|jthomas|
+---------+
3rowsinset(0.00sec)
假设每部电影可以借出的最长时间是4天，找到哪个成员借出电影的时间过期了
加密函数（略）
控制流函数
mysql>selectifnull(1,2),ifnull(null,10),ifnull(4*null,'false');
+-------------+-----------------+--------------------------+
|ifnull(1,2)|ifnull(null,10)|ifnull(4*null,'false')|
+-------------+-----------------+--------------------------+
|1|10|false|
+-------------+-----------------+--------------------------+
1rowinset(0.00sec)
Ifnull函数，它有2个参数，并且对第一个参数进行判断。如果第一个参数不是null，函数就会向调用者返回第一个参数；如果是null，将返回第二个参数
mysql>selectnullif(1,1),nullif('a','b'),nullif(2+3,4+1);
+-------------+-----------------+-----------------+
|nullif(1,1)|nullif('a','b')|nullif(2+3,4+1)|
+-------------+-----------------+-----------------+
|NULL|a|NULL|
+-------------+-----------------+-----------------+
1rowinset(0.02sec)
Nullif函数将会检验提供的两个参数是否相等，如果相等，则返回null，如果不等就返回第一个参数
mysql>selectif(1<10,2,3),if(56>100,'ture','fasle');
+--------------+---------------------------+
|if(1<10,2,3)|if(56>100,'ture','fasle')|
+--------------+---------------------------+
|2|fasle|
+--------------+---------------------------+
1rowinset(0.00sec)
If函数可以建立一个简单的条件测试。它有三个参数。第一个是要被判断的表达式，如果表达式为真，if将会返回第二个参数如果为假if将会返回第三个参数
mysql>selectcase'green'
->when'red'then'stop'
->when'green'then'go'
->end;
+----------------------------------------------------------------+
|case'green'
when'red'then'stop'
when'green'then'go'
end|
+----------------------------------------------------------------+
|go|
+----------------------------------------------------------------+
1rowinset(0.00sec)
mysql>selectcase9
->when1then'a'
->when2then'b'
->else'n/a'
->end;
+-------------------------------------------------------+
|case9
when1then'a'
when2then'b'
else'n/a'
end|
+-------------------------------------------------------+
|n/a|
+-------------------------------------------------------+
1rowinset(0.00sec)
mysql>selectcase2
->when1then'a'
->when2then'b'
->else'n/a'
->end;
+-------------------------------------------------------+
|case2
when1then'a'
when2then'b'
else'n/a'
end|
+-------------------------------------------------------+
|b|
+-------------------------------------------------------+
1rowinset(0.00sec)
mysql>selectcase
->when(2+2)=4then'ok'
->when(2+2)<>4then'nook'
->endasstatus;
+--------+
|status|
+--------+
|ok|
+--------+
1rowinset(0.00sec)
关于case函数的用法
格式化函数
mysql>selectformat(99999999.69639,2),format(-4512,4);
+--------------------------+-----------------+
|format(99999999.69639,2)|format(-4512,4)|
+--------------------------+-----------------+
|99,999,999.70|-4,512.0000|
+--------------------------+-----------------+
1rowinset(0.00sec)
mysql>selectdate_format(now(),'%w,%d,%m,%y,%r');
+-------------------------------------+
|date_format(now(),'%w,%d,%m,%y,%r')|
+-------------------------------------+
|4,16,10,08,10:40:24AM|
+-------------------------------------+
1rowinset(0.03sec)
mysql>selectdate_format(19980317,'%d/%m/%y');
+----------------------------------+
|date_format(19980317,'%d/%m/%y')|
+----------------------------------+
|17/03/98|
+----------------------------------+
1rowinset(0.00sec)
类型转化函数
mysql>selectcast(now()assignedinteger),curdate()+0;
+-------------------------------+-------------+
|cast(now()assignedinteger)|curdate()+0|
+-------------------------------+-------------+
|20081016105615|20081016|
+-------------------------------+-------------+
1rowinset(0.03sec)
有关cast函数还需要找一些例子，在实际中比较有用的