mysql 基础(2)_mysql

日期函数

mysql> select year(curdate()) ; +-----------------+ | year(curdate()) | +-----------------+ |            2015 | +-----------------+ 1 row in set

 YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符

  Month() 提取日期的月份

mysql> select year(curdate()) ; +-----------------+ | year(curdate()) | +-----------------+ |            2015 | +-----------------+ 1 row in set mysql> select right(curdate(),5); +--------------------+ | right(curdate(),5) | +--------------------+ | 07-06              | +--------------------+ 1 row in set mysql> select month(curdate()); +------------------+ | month(curdate()) | +------------------+ |                7 | +------------------+ 1 row in set

DATE_ADD( )允许在一个给定的日期上加上时间间隔

mysql> select MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)); +---------------------------------------------+ | MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)) | +---------------------------------------------+ |                                           8 | +---------------------------------------------+

   使用IS NULL和IS NOT NULL操作符：

mysql> select mod(9 ,8); +----------+ | mod(9,8) | +----------+ |        1 | +----------+ 1 row in set

 使用IS NULL和IS NOT NULL操作符：请注意在MySQL中，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。

在GROUP BY中，两个NULL值视为相同。

执行ORDER BY时，如果运行 ORDER BY … ASC，则NULL值出现在最前面，若运行ORDER BY … DESC，则NULL值出现在最后面。

mysql> select 1 is null,1 is not null; +-----------+---------------+ | 1 is null | 1 is not null | +-----------+---------------+ |         0 |             1 | +-----------+---------------+ 1 row in set 

模式匹配

 查找已F开头的记录

mysql> select * from pet where name like 'F%'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set

 查找以s结尾的记录

mysql> select * from pet where name like '%s'; +-------+-------+---------+-----+------------+------------+ | name  | owner | species | sex | birth      | death      | +-------+-------+---------+-----+------------+------------+ | Claws | Gwen  | cat     | m   | 1994-03-17 | 1994-03-17 |

mysql> SELECT * FROM PET where name REGEXP 'y$'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set

  +-------+-------+---------+-----+------------+------------+ 1 row in set

 查找name中有luf的记录

mysql> select * from pet where name like '%luf%'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set

 还可以用正则表达式查询匹配的行

   查找首个字母是F的行

mysql> SELECT * FROM PET where name REGEXP '^F'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set

   查找结尾是y的行

mysql> SELECT * FROM PET where name REGEXP 'y$ '; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set

   查找包含l的行

mysql> select * from pet where name REGEXP 'l'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | | Claws  | Gwen   | cat     | m   | 1994-03-17 | 1994-03-17 | +--------+--------+---------+-----+------------+------------+ 2 rows in set

 找出包含正好5个字符的名字，

mysql> select * from pet  where name REGEXP '^.....$'; +-------+-------+---------+-----+------------+------------+ | name  | owner | species | sex | birth      | death      | +-------+-------+---------+-----+------------+------------+ | Claws | Gwen  | cat     | m   | 1994-03-17 | 1994-03-17 | +-------+-------+---------+-----+------------+------------+ 1 row in set

 也可以使用“{n}”“重复n次”操作符重写前面的查询：

mysql> select * from pet  where name REGEXP '^.{5}$'; +-------+-------+---------+-----+------------+------------+ | name  | owner | species | sex | birth      | death      | +-------+-------+---------+-----+------------+------------+ | Claws | Gwen  | cat     | m   | 1994-03-17 | 1994-03-17 | +-------+-------+---------+-----+------------+------------+ 1 row in set mysql> select * from pet  where name REGEXP '^.{6}$'; +--------+--------+---------+-----+------------+------------+ | name   | owner  | species | sex | birth      | death      | +--------+--------+---------+-----+------------+------------+ | Fluffy | Harold | cat     | f   | 1993-02-04 | 0000-00-00 | +--------+--------+---------+-----+------------+------------+ 1 row in set mysql>  

 以上详细可以看下mysql的正则表达式