日期函数
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 setmysql> select right(curdate(),5);+--------------------+| right(curdate(),5) |+--------------------+| 07-06 |+--------------------+1 row in setmysql> 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 setmysql> 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 setmysql> ?以上详细可以看下mysql的正则表达式