mysql 常见函数2

最新推荐文章于 2022-10-19 10:46:09 发布
最新推荐文章于 2022-10-19 10:46:09 发布
阅读量365 收藏
点赞数
分类专栏： 数据库 文章标签： 函数 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012412427/article/details/48226065
版权 
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(242.38,-2);
+------------------+
| round(242.38,-2) |
+------------------+
|              200 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(842.38,-2);
+------------------+
| round(842.38,-2) |
+------------------+
|              800 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.31,1);
+------------------+
| truncate(1.31,1) |
+------------------+
|              1.3 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.35,1);
+------------------+
| truncate(1.35,1) |
+------------------+
|              1.3 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.9999,1);
+--------------------+
| truncate(1.9999,1) |
+--------------------+
|                1.9 |
+--------------------+
1 row in set (0.00 sec)

mysql> select pow(2,2),power(2,2),pow(2,-2),power(2,-2);
+----------+------------+-----------+-------------+
| pow(2,2) | power(2,2) | pow(2,-2) | power(2,-2) |
+----------+------------+-----------+-------------+
|        4 |          4 |      0.25 |        0.25 |
+----------+------------+-----------+-------------+
1 row in set (0.02 sec)

mysql> select exp(2);
+------------------+
| exp(2)           |
+------------------+
| 7.38905609893065 |
+------------------+
1 row in set (0.00 sec)

mysql> select exp(0);
+--------+
| exp(0) |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select exp(-3)
    -> ;
+----------------------+
| exp(-3)              |
+----------------------+
| 0.049787068367863944 |
+----------------------+
1 row in set (0.00 sec)

mysql> select log(3);
+--------------------+
| log(3)             |
+--------------------+
| 1.0986122886681098 |
+--------------------+
1 row in set (0.00 sec)

mysql> select log10(10);
+-----------+
| log10(10) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select log10(100);
+------------+
| log10(100) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> select log10(-100);
+-------------+
| log10(-100) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select char_length('date');
+---------------------+
| char_length('date') |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.00 sec)

mysql> select char_length('egg');
+--------------------+
| char_length('egg') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

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

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

mysql> select concat('My SQL','5.6');
+------------------------+
| concat('My SQL','5.6') |
+------------------------+
| My SQL5.6              |
+------------------------+
1 row in set (0.02 sec)

mysql> select concat('Mysql','5.6');
+-----------------------+
| concat('Mysql','5.6') |
+-----------------------+
| Mysql5.6              |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat('my',null,'sql');
+-------------------------+
| concat('my',null,'sql') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> select
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->     -> Ctrl-C -- exit!

    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    -> ;
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 '' at
line 1
mysql> select concat_ws('-','1st','2nd','3rd');
+----------------------------------+
| concat_ws('-','1st','2nd','3rd') |
+----------------------------------+
| 1st-2nd-3rd                      |
+----------------------------------+
1 row in set (0.02 sec)

mysql> select concat_ws('*','1st',NULL,'3rd');
+---------------------------------+
| concat_ws('*','1st',NULL,'3rd') |
+---------------------------------+
| 1st*3rd                         |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select insert('Quest',2,4,'What') AS coll,
    -> insert('Quest',-1,4,'What') as col2,
    -> insert('Quest',3,100,'Wh') as col3;
+-------+-------+------+
| coll  | col2  | col3 |
+-------+-------+------+
| QWhat | Quest | QuWh |
+-------+-------+------+
1 row in set (0.00 sec)

mysql> select lower('LISHENG');
+------------------+
| lower('LISHENG') |
+------------------+
| lisheng          |
+------------------+
1 row in set (0.00 sec)

mysql> select lcase(
    -> 'Well');
+----------------+
| lcase(
'Well') |
+----------------+
| well           |
+----------------+
1 row in set (0.00 sec)

mysql> select upper('lisheng');
+------------------+
| upper('lisheng') |
+------------------+
| LISHENG          |
+------------------+
1 row in set (0.00 sec)

mysql> select left('football',5);
+--------------------+
| left('football',5) |
+--------------------+
| footb              |
+--------------------+
1 row in set (0.00 sec)

mysql> select left('lisheng',2);
+-------------------+
| left('lisheng',2) |
+-------------------+
| li                |
+-------------------+
1 row in set (0.00 sec)

mysql> select right('lisheng',5);
+--------------------+
| right('lisheng',5) |
+--------------------+
| sheng              |
+--------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',4,'??');
+----------------------+
| lpad('hello',4,'??') |
+----------------------+
| hell                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',10,'>>');
+-----------------------+
| lpad('hello',10,'>>') |
+-----------------------+
| >>>>>hello            |
+-----------------------+
1 row in set (0.00 sec)

mysql> select rpad('hello',4,"?");
+---------------------+
| rpad('hello',4,"?") |
+---------------------+
| hell                |
+---------------------+
1 row in set (0.00 sec)

mysql> select RPAD('hello',10,"?");
+----------------------+
| RPAD('hello',10,"?") |
+----------------------+
| hello?????           |
+----------------------+
1 row in set (0.00 sec)

mysql>

| concat('My SQL','5.6') |
+------------------------+
| My SQL5.6              |
+------------------------+
1 row in set (0.02 sec)

mysql> select concat('Mysql','5.6');
+-----------------------+
| concat('Mysql','5.6') |
+-----------------------+
| Mysql5.6              |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat('my',null,'sql');
+-------------------------+
| concat('my',null,'sql') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> select
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->     -> Ctrl-C -- exit!

    ->
    ->
    ->
    ->
    ->
    ->
    ->
    ->
    -> ;
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 '' at
line 1
mysql> select concat_ws('-','1st','2nd','3rd');
+----------------------------------+
| concat_ws('-','1st','2nd','3rd') |
+----------------------------------+
| 1st-2nd-3rd                      |
+----------------------------------+
1 row in set (0.02 sec)

mysql> select concat_ws('*','1st',NULL,'3rd');
+---------------------------------+
| concat_ws('*','1st',NULL,'3rd') |
+---------------------------------+
| 1st*3rd                         |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select insert('Quest',2,4,'What') AS coll,
    -> insert('Quest',-1,4,'What') as col2,
    -> insert('Quest',3,100,'Wh') as col3;
+-------+-------+------+
| coll  | col2  | col3 |
+-------+-------+------+
| QWhat | Quest | QuWh |
+-------+-------+------+
1 row in set (0.00 sec)

mysql> select lower('LISHENG');
+------------------+
| lower('LISHENG') |
+------------------+
| lisheng          |
+------------------+
1 row in set (0.00 sec)

mysql> select lcase(
    -> 'Well');
+----------------+
| lcase(
'Well') |
+----------------+
| well           |
+----------------+
1 row in set (0.00 sec)

mysql> select upper('lisheng');
+------------------+
| upper('lisheng') |
+------------------+
| LISHENG          |
+------------------+
1 row in set (0.00 sec)

mysql> select left('football',5);
+--------------------+
| left('football',5) |
+--------------------+
| footb              |
+--------------------+
1 row in set (0.00 sec)

mysql> select left('lisheng',2);
+-------------------+
| left('lisheng',2) |
+-------------------+
| li                |
+-------------------+
1 row in set (0.00 sec)

mysql> select right('lisheng',5);
+--------------------+
| right('lisheng',5) |
+--------------------+
| sheng              |
+--------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',4,'??');
+----------------------+
| lpad('hello',4,'??') |
+----------------------+
| hell                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',10,'>>');
+-----------------------+
| lpad('hello',10,'>>') |
+-----------------------+
| >>>>>hello            |
+-----------------------+
1 row in set (0.00 sec)

mysql> select rpad('hello',4,"?");
+---------------------+
| rpad('hello',4,"?") |
+---------------------+
| hell                |
+---------------------+
1 row in set (0.00 sec)

mysql> select RPAD('hello',10,"?");
+----------------------+
| RPAD('hello',10,"?") |
+----------------------+
| hello?????           |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> select '( book  )',concat('(',ltrim('  book  '),')');
+-----------+-----------------------------------+
| ( book  ) | concat('(',ltrim('  book  '),')') |
+-----------+-----------------------------------+
| ( book  ) | (book  )                          |
+-----------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select concat('(',rtrim('  book '),')');
+----------------------------------+
| concat('(',rtrim('  book '),')') |
+----------------------------------+
| (  book)                         |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select concat('(',trim(' book ')',')');
    '> ;
    '> '
    -> ;
ERROR 1583 (42000): Incorrect parameters in the call to native function 'concat'

mysql> select concat(
    ->     -> Ctrl-C -- exit!

    -> ;
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 '' at
line 1
mysql> select concat('(',trim('  book   '),')');
+-----------------------------------+
| concat('(',trim('  book   '),')') |
+-----------------------------------+
| (book)                            |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select trim('xy' from 'xyboxxyokxxyxy');
+----------------------------------+
| trim('xy' from 'xyboxxyokxxyxy') |
+----------------------------------+
| boxxyokx                         |
+----------------------------------+
1 row in set (0.00 sec)

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

mysql> select repeat('lisheng is a good man',4);
+-------------------------------------------------------------------------------
-------+
| repeat('lisheng is a good man',4)
       |
+-------------------------------------------------------------------------------
-------+
| lisheng is a good manlisheng is a good manlisheng is a good manlisheng is a go
od man |
+-------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)

mysql> select concat('(',space(6),')');
+--------------------------+
| concat('(',space(6),')') |
+--------------------------+
| (      )                 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select replace('xxx.mysql.com','x','w');
+----------------------------------+
| replace('xxx.mysql.com','x','w') |
+----------------------------------+
| www.mysql.com                    |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('txt','txt2'),strcmp('txt2','txt'),strcmp('txt','txt');
+----------------------+----------------------+---------------------+
| strcmp('txt','txt2') | strcmp('txt2','txt') | strcmp('txt','txt') |
+----------------------+----------------------+---------------------+
|                   -1 |                    1 |                   0 |
+----------------------+----------------------+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select substring('breakfast',5) as coll,
    -> substring('breakfast',5,3) as col2,
    -> substring('lunch',-3) as col3,
    -> substring('lunch',-5,3) as col4;
+-------+------+------+------+
| coll  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select locate('ball','football');
+---------------------------+
| locate('ball','football') |
+---------------------------+
|                         5 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-09-05 |
+------------+
1 row in set (0.03 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2015-09-05     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_date()+0;
+------------------+
| current_date()+0 |
+------------------+
|         20150905 |
+------------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:15:04       |
+----------------+
1 row in set (0.01 sec)

mysql> select current_time()+0;
+------------------+
| current_time()+0 |
+------------------+
|    151513.000000 |
+------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-09-05 15:15:52 |
+---------------------+
1 row in set (0.00 sec)

mysql>mysql> select substring('breakfast',5) as coll,
    -> substring('breakfast',5,3) as col2,
    -> substring('lunch',-3) as col3,
    -> substring('lunch',-5,3) as col4;
+-------+------+------+------+
| coll  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select locate('ball','football');
+---------------------------+
| locate('ball','football') |
+---------------------------+
|                         5 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-09-05 |
+------------+
1 row in set (0.03 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2015-09-05     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_date()+0;
+------------------+
| current_date()+0 |
+------------------+
|         20150905 |
+------------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:15:04       |
+----------------+
1 row in set (0.01 sec)

mysql> select current_time()+0;
+------------------+
| current_time()+0 |
+------------------+
|    151513.000000 |
+------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-09-05 15:15:52 |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select substring('breakfast',5) as coll,
    -> substring('breakfast',5,3) as col2,
    -> substring('lunch',-3) as col3,
    -> substring('lunch',-5,3) as col4;
+-------+------+------+------+
| coll  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select locate('ball','football');
+---------------------------+
| locate('ball','football') |
+---------------------------+
|                         5 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-09-05 |
+------------+
1 row in set (0.03 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2015-09-05     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_date()+0;
+------------------+
| current_date()+0 |
+------------------+
|         20150905 |
+------------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:15:04       |
+----------------+
1 row in set (0.01 sec)

mysql> select current_time()+0;
+------------------+
| current_time()+0 |
+------------------+
|    151513.000000 |
+------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-09-05 15:15:52 |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-09-05 |
+------------+
1 row in set (0.03 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2015-09-05     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_date()+0;
+------------------+
| current_date()+0 |
+------------------+
|         20150905 |
+------------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:15:04       |
+----------------+
1 row in set (0.01 sec)

mysql> select current_time()+0;
+------------------+
| current_time()+0 |
+------------------+
|    151513.000000 |
+------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-09-05 15:15:52 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1441437497 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1441437511 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1441437518 |
+------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('1441437511');
+-----------------------------+
| from_unixtime('1441437511') |
+-----------------------------+
| 2015-09-05 15:18:31         |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2015-09-05 |
+------------+
1 row in set (0.00 sec)

mysql> select MONTH(now());
+--------------+
| MONTH(now()) |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)

mysql> select MONTHNAME(now());
+------------------+
| MONTHNAME(now()) |
+------------------+
| September        |
+------------------+
1 row in set (0.01 sec)

mysql> select dayname(now()):
    -> ;
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 ':' at
 line 1
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Saturday       |
+----------------+
1 row in set (0.02 sec)

mysql> select day(now()):
    -> ;
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 ':' at
 line 1
mysql> select day(now()):
    -> ;
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 ':' at
 line 1
mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

mysql> select weekday(now()):
    -> ;
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 ':' at
 line 1
mysql> select weekday(now());
+----------------+
| weekday(now()) |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)

mysql> select week(now()):
    -> ;
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 ':' at
 line 1
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          35 |
+-------------+
1 row in set (0.00 sec)

mysql> select week(now(),1);
+---------------+
| week(now(),1) |
+---------------+
|            36 |
+---------------+
1 row in set (0.00 sec)

mysql> select week(now(),3);
+---------------+
| week(now(),3) |
+---------------+
|            36 |
+---------------+
1 row in set (0.00 sec)

mysql> select weekofyear(now());
+-------------------+
| weekofyear(now()) |
+-------------------+
|                36 |
+-------------------+
1 row in set (0.00 sec)

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              248 |
+------------------+
1 row in set (0.00 sec)

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.00 sec)

mysql>


mysql> select extract(year from now()) as coll;
+------+
| coll |
+------+
| 2015 |
+------+
1 row in set (0.00 sec)

mysql> select time();
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 ')' at line 1
mysql> select date();
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 ')' at line 1
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:42:28       |
+----------------+
1 row in set (0.00 sec)

mysql> select time_to_sec(current_time());
+-----------------------------+
| time_to_sec(current_time()) |
+-----------------------------+
|                       56586 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select sec_to_time(56586);
+--------------------+
| sec_to_time(56586) |
+--------------------+
| 15:43:06           |
+--------------------+
1 row in set (0.00 sec)

mysql> select date_add('2010-12-31 23:59:59',interval 1 second) as coll;
+---------------------+
| coll                |
+---------------------+
| 2011-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date_add(
    -> '2010-12-31 23:59:59',interval 1 second) as coll;
+---------------------+
| coll                |
+---------------------+
| 2011-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select data_add('2010-12-31 23:59:59',interval '1:1' minute_second);
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 ')' at line 1
mysql> select date_add('2010-12-31 23:59:59',interval '1:1' minute_second) ;
+--------------------------------------------------------------+
| date_add('2010-12-31 23:59:59',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2011-01-01 00:01:00                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub('2011-01-02',interval 31 day);
+----------------------------------------+
| date_sub('2011-01-02',interval 31 day) |
+----------------------------------------+
| 2010-12-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select addtime('2000-12-31 23:59:59','1:1:1'),addtime('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| addtime('2000-12-31 23:59:59','1:1:1') | addtime('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2001-01-01 01:01:00                    | 04:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> select subtime('2000-12-31 23:59:59','1:1:1'),subtime('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| subtime('2000-12-31 23:59:59','1:1:1') | subtime('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2000-12-31 22:58:58                    | 00:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2010-12-31 23:59:59','2010-12-30');
+----------------------------------------------+
| datediff('2010-12-31 23:59:59','2010-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2010-12-31 23:59:59','2015-12-30');
+----------------------------------------------+
| datediff('2010-12-31 23:59:59','2015-12-30') |
+----------------------------------------------+
|                                        -1825 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2030-12-31 23:59:59','2015-12-30');
+----------------------------------------------+
| datediff('2030-12-31 23:59:59','2015-12-30') |
+----------------------------------------------+
|                                         5480 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2020-12-31 23:59:59','2015-12-30');
+----------------------------------------------+
| datediff('2020-12-31 23:59:59','2015-12-30') |
+----------------------------------------------+
|                                         1828 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select time_stamp();
ERROR 1305 (42000): FUNCTION time_stamp does not exist
mysql> select date_format('1997-10-04 22:23:00','%W %M %Y');
+-----------------------------------------------+
| date_format('1997-10-04 22:23:00','%W %M %Y') |
+-----------------------------------------------+
| Saturday October 1997                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format('1997-10-04 22:23:00','%D %y %a %d %m %b %j');
+-----------------------------------------------------------+
| date_format('1997-10-04 22:23:00','%D %y %a %d %m %b %j') |
+-----------------------------------------------------------+
| 4th 97 Sat 04 10 Oct 277                                  |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%H:%i:%s');
+-------------------------------+
| date_format(now(),'%H:%i:%s') |
+-------------------------------+
| 16:18:10                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format('2000-10-05 22:23:00',GET_FORMAT(DATE,'USA'));
+-----------------------------------------------------------+
| date_format('2000-10-05 22:23:00',GET_FORMAT(DATE,'USA')) |
+-----------------------------------------------------------+
| 10.05.2000                                                |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),GET_FORMAT(DATE,'USA'));
+-------------------------------------------+
| date_format(now(),GET_FORMAT(DATE,'USA')) |
+-------------------------------------------+
| 09.05.2015                                |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>
2015_thinker
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 常用函数总结
09-08
主要介绍了一些MySQL 常用函数的总结，文中讲解非常细致，帮助大家更好的学习mysql，感兴趣的朋友可以了解下
mysql 常用函数
04-20
mysql 函数整理，方便使用。。。。。。。。。。。。。。。。。。。。。
mysql 0函数_MySQL数据库函数(一)
weixin_28693447的博客
01-18 261
一、数学函数1、绝对值函数ABS(x) ：返回 x 的绝对值mysql> select ABS(2),ABS(-2.3),ABS(-22);返回的结果如下：数学学得好的大佬应该知道(本人是个数学渣。。。。)，负数的绝对值是正数，正数的绝对值还是正数本身2、 返回圆周率的函数PI() ：返回圆周率值mysql> select PI();返回结果如下：3、平方根函数SQRT(x) ：可以返...
MySQL函数大全 MySQL常用函数汇总
最新发布
梁辰兴的博客
10-19 1043
MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。MySQL 包含了大量并且丰富的函数，这套 MySQL 函数大全只收集了几十个常用的，剩下的比较罕见的函数我就不再整理了，有需要的读者可以到「MySQL 官网」查询。可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。
MySql常用函数大全
热门推荐
qq_43842093的博客
10-24 1万+
MySql常用函数大全 MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。在这一讲中将讲解的内容包括： 数学函数 字符串函数 日期和时间函数 条件判断函数 系统信息函数 加密函数 格式化函数 ​ MySQL函数是MySQL数据库提供的内部函数。这些内部函数可以帮助用户更加方便的处理表中的数据。本小节中将简单介绍MySQ
MySql常用函数汇总
木仕的博客
04-29 1435
MYSQL常用函数汇总 授人以渔1 授人以渔2 1、CONCAT() SELECT CONCAT(A,B) FROM TABLE 用于合并两个字段 2、AVG() SELECT AVG(A) FROM TABLE 用于求字段的平均值，会忽略null行 3、COUNT() SELECT COUNT(1) FROM TABLE 用于返回某列的行数，1和*的时候返回全表行数 4、MAX() SELECT MAX() FROM TABLE 用于返回某列中最大的值 5、MIN() SELECT MI
MYSQL常用函数总结
LuoMin2523的博客
03-26 8581
第一部分 MYSQL中常用的函数 (PS:总结的有点潦草，适合有一定基础的总结性学习) 一、排序函数 RANK()：1，1，3 在计算排序时，若存在相同的位次，会跳过之后的位次 DENSE_RANK()：1，1，2 在计算排序时，若存在相同的位次，不会跳过之后的位次 ROW_NUMBER()：1，2，3 这个函数赋予唯一的连续位次 二、日期函数 DATEDIFF(data1,date2)：date1-date2 DATE_FORMAT(date,format):日期格式转换 TI
mysql常用函数汇总.chm
08-14
Mysql的常用函数整体, 从网上收集的一些常用函数, 进行汇总整体成文档 MySQL控制流函数： CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default CASE [test] WHEN...
MySQL 常用函数的使用
12-14
MySQL 常用函数1. 字符串函数a. 常用函数b. 导入数据c. 示例题目2. 日期函数a. 常用函数b. 示例题目3. 数字函数a. 常用函数b. 示例题目4. 高级函数a. CASE 表达式i. 语法ii. 示例题目b. IF 表达式i. 语法ii. 示例...
MySQL常用函数
11-20
包含了MySQL常用的所有函数及详解。IFNULL(expr1,expr2)，FROM_UNIXTIME。
mysql的算术运算符和比较运算符
Just for our dream
09-05 2779
mysql 的算术部分mysql> create table tmpl4(num int); Query OK, 0 rows affected (0.14 sec)mysql> insert into tmpl4 values(64); Query OK, 1 row affected (0.08 sec)mysql> select num,num+10,num-3+5,num+5-3,num+3
mysql 存储引擎,数据类型和运算符
Just for our dream
11-04 394
mysql 存储引擎1.存储引擎其实就是如何存储数据 2.如何为存储的数据建立索引 3.如何更新，查询数据的等技术的实现方法注意: 在Oracle 和 sqlserve 等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的， 而mysql数据库提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写存储引擎查询mysql中支持的存储引擎:命令:show e
条件判断函数
Just for our dream
09-05 374
ysql> select if(1>2,2,3); +-------------+ | if(1>2,2,3) | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec)mysql> select if(1<2,'yes','no'); +--------------------+ | if(1<2,'yes',
项目中常用的mysql函数
Just for our dream
11-04 355
项目中经常的查询:join GROUP BY经常的流程控制:case when '' then '' else '' end经常用的函数:IFNULL LEFT SUM replaceCURDATE() - INTERVAL 15 DAYROUNDDATE_FORMATTO_DAYSROUNDUNIX_TIMESTAMP 最实用的编程语句(mysql 基本操作)学习本章目的 1.掌握mysql数据库
mysql 数据表的基本操作
Just for our dream
08-22 352
掌握创建表，添加各类约束，查看表结构，以及删除也修改操作.create database Market; Query OK, 1 row affected (0.00 sec)mysql> use Market; Database changed mysql> mysql> show tables; Empty set (0.00 sec)mysql> mysql> create table c
存储过程&函数&触发器@变量&权限
Just for our dream
11-06 343
mysql 数据查询:mysql> select name,subject,group_concat(id) from student group by subject; +---------+---------+------------------+ | name | subject | group_concat(id) | +---------+---------+------------
简单的触发器和存储过程
Just for our dream
11-05 341
触发器:before create procedure p4()begin declare age int default 18; if age >=18 then select ''; else select ''; end if; endcall p4(); ---------------------[in/out/inout] 参数名 参数类型cr
综合案例:运算符的使用
Just for our dream
09-05 311
---+----------+ 1 row in set (0.00 sec)mysql> select 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3) <=> (2+1),NULL<=>NU LL,1<=>NULL\G *************************** 1. row ***************************

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值