MYSQL完全手册学习笔记（第七章）

MYSQL函数

mysql> select round(10.33),round(64.50001),round(10.98547);
+--------------+-----------------+-----------------+
| round(10.33) | round(64.50001) | round(10.98547) |
+--------------+-----------------+-----------------+
| 10 | 65 | 11 |
+--------------+-----------------+-----------------+
1 row in set (0.00 sec)

Round 函数四舍五入


mysql> select truncate(1.78502356,3),truncate(-6235.3256984,10);
+------------------------+----------------------------+
| truncate(1.78502356,3) | truncate(-6235.3256984,10) |
+------------------------+----------------------------+
| 1.785 | -6235.3256984000 |
+------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> select truncate(1789.09854,-2),truncate(-629.6953245,-1);
+-------------------------+---------------------------+
| truncate(1789.09854,-2) | truncate(-629.6953245,-1) |
+-------------------------+---------------------------+
| 1700 | -620 |
+-------------------------+---------------------------+
1 row in set (0.00 sec)


Truncate 把一个数字截短成为一个指定小数个数的数字，注意取负数的时候哦

mysql> select abs (-789);
+------------+
| abs (-789) |
+------------+
| 789 |
+------------+
1 row in set (0.00 sec)

Abs绝对值


mysql> select * from toys;
+----+-----------------+-------+-------+-------+----------+
| id | item | ctm | ctr | ctc | quantity |
+----+-----------------+-------+-------+-------+----------+
| 0 | ww | 3.00 | 3.00 | 3.00 | 200 |
| 1 | huggy bear | 5.00 | 7.40 | 9.90 | 300 |
| 2 | coloring book | 3.00 | 5.00 | 7.99 | 2000 |
| 3 | board game | 12.00 | 20.00 | 39.90 | 1430 |
| 4 | activity cd-rom | 2.00 | 6.00 | 17.49 | 3780 |
| 6 | ww | 6.00 | 6.00 | 6.00 | 100 |
+----+-----------------+-------+-------+-------+----------+
6 rows in set (0.00 sec)

mysql> select * from toys group by item;
+----+-----------------+-------+-------+-------+----------+
| id | item | ctm | ctr | ctc | quantity |
+----+-----------------+-------+-------+-------+----------+
| 4 | activity cd-rom | 2.00 | 6.00 | 17.49 | 3780 |
| 3 | board game | 12.00 | 20.00 | 39.90 | 1430 |
| 2 | coloring book | 3.00 | 5.00 | 7.99 | 2000 |
| 1 | huggy bear | 5.00 | 7.40 | 9.90 | 300 |
| 0 | ww | 3.00 | 3.00 | 3.00 | 200 |
+----+-----------------+-------+-------+-------+----------+
5 rows in set (0.01 sec)

Group by 去掉重复的纪录

字符串函数

mysql> select length('123456');
+------------------+
| length('123456') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)

mysql> select length('fsfsdsesdsfdfsd');
+---------------------------+
| length('fsfsdsesdsfdfsd') |
+---------------------------+
| 15 |
+---------------------------+
1 row in set (0.00 sec)
Length 返回字符串的长度


mysql> select rtrim("right hand "), ltrim(" left eye");
+--------------------------+----------------------+
| rtrim("right hand ") | ltrim(" left eye") |
+--------------------------+----------------------+
| right hand | left eye |
+--------------------------+----------------------+
1 row in set (0.00 sec)

Ltrim（）去掉首部的空格
Rtrim（）去掉尾部的空格

mysql> select trim(' res sere ');
+-----------------------+
| trim(' res sere ') |
+-----------------------+
| res sere |
+-----------------------+


Trim 去掉首尾的空格


mysql> select left('hello',4),right('welcome to shenyang',5);
+-----------------+--------------------------------+
| left('hello',4) | right('welcome to shenyang',5) |
+-----------------+--------------------------------+
| hell | nyang |
+-----------------+--------------------------------+
1 row in set (0.00 sec)

Left 和right 从左边或者右边返回字符


mysql> select left(right('market',5),3);
+---------------------------+
| left(right('market',5),3) |
+---------------------------+
| ark |
+---------------------------+
1 row in set (0.00 sec)

取得中间的部分


mysql> select substring('market',2,3);
+-------------------------+
| substring('market',2,3) |
+-------------------------+
| ark |
+-------------------------+
1 row in set (0.00 sec)


Substring 也是可以的


mysql> select concat('sds','dsee');
+----------------------+
| concat('sds','dsee') |
+----------------------+
| sdsdsee |
+----------------------+
1 row in set (0.00 sec)

mysql> select concat('sds',"ee22");
+----------------------+
| concat('sds',"ee22") |
+----------------------+
| sdsee22 |
+----------------------+
1 row in set (0.00 sec)

mysql> select concat('sds',23);
+------------------+
| concat('sds',23) |
+------------------+
| sds23 |
+------------------+
1 row in set (0.00 sec)

Concat 连接字符串

mysql> select insert('21010419801010171*',18,1,'x');
+---------------------------------------+
| insert('21010419801010171*',18,1,'x') |
+---------------------------------------+
| 21010419801010171x |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select insert('I am Joe Cool',10,4,'camel');
+--------------------------------------+
| insert('I am Joe Cool',10,4,'camel') |
+--------------------------------------+
| I am Joe camel |
+--------------------------------------+
1 row in set (0.00 sec)

Insert 函数可以把一个字符串的指定部分（定义了起始位置和长度）替换为一个新值


mysql> select repeat('ha',5);
+----------------+
| repeat('ha',5) |
+----------------+
| hahahahaha |
+----------------+
1 row in set (0.05 sec)

Repeat函数用于重复字符串


mysql> select reverse(repeat('ha',3));
+-------------------------+
| reverse(repeat('ha',3)) |
+-------------------------+
| ahahah |
+-------------------------+
1 row in set (0.00 sec)


Reverse 函数颠倒字符串


日期和时间函数


mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2008-10-15 16:44:00 |
+---------------------+
1 row in set (0.03 sec)

mysql> select curtime(),curdate();
+-----------+------------+
| curtime() | curdate() |
+-----------+------------+
| 16:44:29 | 2008-10-15 |
+-----------+------------+
1 row in set (0.02 sec)


mysql> select date_add('2004-01-01 00:00:00',interval 6 month);
+--------------------------------------------------+
| date_add('2004-01-01 00:00:00',interval 6 month) |
+--------------------------------------------------+
| 2004-07-01 00:00:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)

第一个例子在初始时间加上6个月并返回结果


mysql> select date_add('2004-03-14 12:20:00',interval '12 03:45' day_minute);
+----------------------------------------------------------------+
| date_add('2004-03-14 12:20:00',interval '12 03:45' day_minute) |
+----------------------------------------------------------------+
| 2004-03-26 16:05:00 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
第二个例子向初始时间增加12天3小时45分钟，并显示结果


mysql> select date_sub('2004-01-01 07:00:00',interval 8 hour);
+-------------------------------------------------+
| date_sub('2004-01-01 07:00:00',interval 8 hour) |
+-------------------------------------------------+
| 2003-12-31 23:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)

从一个日期值减去8个小时


mysql> select date_sub('2004-01-01 07:20:00',interval '13-4',year_month);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'year_
month)' at line 1


日期值减去13年4个月 没有效果！！！！


P135


mysql> select extract(year from'1999-07-02');
+--------------------------------+
| extract(year from'1999-07-02') |
+--------------------------------+
| 1999 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select extract(year_month from'1999-07-02');
+--------------------------------------+
| extract(year_month from'1999-07-02') |
+--------------------------------------+
| 199907 |
+--------------------------------------+
1 row in set (0.00 sec)

Extract（）函数获得一个日期的指定部分


mysql> select period_diff(200302,199802);
+----------------------------+
| period_diff(200302,199802) |
+----------------------------+
| 60 |
+----------------------------+
1 row in set (0.02 sec)

period_diff 函数返回两个日期之间的差值（月数）


mysql> select name,dob,round(period_diff(date_format(now(),'%y%m'),date_format(d
ob,'%y%m'))/12,1) as age from birthday;
+-------+---------------------+-------+
| name | dob | age |
+-------+---------------------+-------+
| raoul | 1978-06-04 00:00:00 | 30.3 |
| luis | 1965-11-17 00:00:00 | -57.1 |
| larry | 1971-08-19 00:00:00 | 37.2 |
| moe | 1992-01-23 00:00:00 | 16.8 |
+-------+---------------------+-------+
4 rows in set (0.02 sec)

为什么出来负数？？？


mysql> select name,dob,(to_days(now())-to_days(dob))/365 as age from birthday;
+-------+---------------------+---------+
| name | dob | age |
+-------+---------------------+---------+
| raoul | 1978-06-04 00:00:00 | 30.3890 |
| luis | 1965-11-17 00:00:00 | 42.9425 |
| larry | 1971-08-19 00:00:00 | 37.1863 |
| moe | 1992-01-23 00:00:00 | 16.7425 |
+-------+---------------------+---------+
4 rows in set (0.00 sec)


mysql> select member,to_days(check_in_date)-to_days(check_out_date) as out_perio
d from library;
+---------+------------+
| member | out_period |
+---------+------------+
| joe | 3 |
| john | 6 |
| johann | 4 |
| mark | 30 |
| jthomas | 8 |
+---------+------------+
5 rows in set (0.00 sec)

查询出来每个电影借出多长时间


mysql> select member from library where to_days(check_in_date)-to_days
-> (check_out_date)>4;
+---------+
| member |
+---------+
| john |
| mark |
| jthomas |
+---------+
3 rows in set (0.00 sec)

假设每部电影可以借出的最长时间是4天，找到哪个成员借出电影的时间过期了


加密函数（略）

控制流函数


mysql> select ifnull(1,2),ifnull(null,10),ifnull(4 * null,'false');
+-------------+-----------------+--------------------------+
| ifnull(1,2) | ifnull(null,10) | ifnull(4 * null,'false') |
+-------------+-----------------+--------------------------+
| 1 | 10 | false |
+-------------+-----------------+--------------------------+
1 row in set (0.00 sec)

Ifnull 函数，它有2个参数，并且对第一个参数进行判断。如果第一个参数不是null，函数就会向调用者返回第一个参数；如果是null，将返回第二个参数


mysql> select nullif(1,1),nullif('a','b'),nullif(2+3,4+1);
+-------------+-----------------+-----------------+
| nullif(1,1) | nullif('a','b') | nullif(2+3,4+1) |
+-------------+-----------------+-----------------+
| NULL | a | NULL |
+-------------+-----------------+-----------------+
1 row in set (0.02 sec)

Nullif 函数将会检验提供的两个参数是否相等，如果相等，则返回null，如果不等就返回第一个参数


mysql> select if(1<10,2,3),if(56>100,'ture','fasle');
+--------------+---------------------------+
| if(1<10,2,3) | if(56>100,'ture','fasle') |
+--------------+---------------------------+
| 2 | fasle |
+--------------+---------------------------+
1 row in set (0.00 sec)

If 函数可以建立一个简单的条件测试。它有三个参数。第一个是要被判断的表达式，如果表达式为真，if将会返回第二个参数 如果为假if 将会返回第三个参数


mysql> select case 'green'
-> when 'red' then 'stop'
-> when 'green' then 'go'
-> end;
+----------------------------------------------------------------+
| case 'green'
when 'red' then 'stop'
when 'green' then 'go'
end |
+----------------------------------------------------------------+
| go |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case 9
-> when 1 then 'a'
-> when 2 then 'b'
-> else 'n/a'
-> end;
+-------------------------------------------------------+
| case 9
when 1 then 'a'
when 2 then 'b'
else 'n/a'
end |
+-------------------------------------------------------+
| n/a |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case 2
-> when 1 then 'a'
-> when 2 then 'b'
-> else 'n/a'
-> end;
+-------------------------------------------------------+
| case 2
when 1 then 'a'
when 2 then 'b'
else 'n/a'
end |
+-------------------------------------------------------+
| b |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case
-> when (2+2)=4 then 'ok'
-> when (2+2)<>4 then 'nook'
-> end as status;
+--------+
| status |
+--------+
| ok |
+--------+
1 row in set (0.00 sec)

关于case 函数的用法

格式化函数


mysql> select format(99999999.69639,2), format(-4512,4);
+--------------------------+-----------------+
| format(99999999.69639,2) | format(-4512,4) |
+--------------------------+-----------------+
| 99,999,999.70 | -4,512.0000 |
+--------------------------+-----------------+
1 row in set (0.00 sec)


mysql> select date_format(now(),'%w,%d,%m,%y,%r');
+-------------------------------------+
| date_format(now(),'%w,%d,%m,%y,%r') |
+-------------------------------------+
| 4,16,10,08,10:40:24 AM |
+-------------------------------------+
1 row in set (0.03 sec)


mysql> select date_format(19980317,'%d/%m/%y');
+----------------------------------+
| date_format(19980317,'%d/%m/%y') |
+----------------------------------+
| 17/03/98 |
+----------------------------------+
1 row in set (0.00 sec)

类型转化函数

mysql> select cast(now() as signed integer),curdate()+0;
+-------------------------------+-------------+
| cast(now() as signed integer) | curdate()+0 |
+-------------------------------+-------------+
| 20081016105615 | 20081016 |
+-------------------------------+-------------+
1 row in set (0.03 sec)

有关cast 函数还需要找一些例子，在实际中比较有用的