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的正则表达式