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 函数还需要找一些例子，在实际中比较有用的