mysql学习笔记003

(17)使用日期函数

mysql> update sales_rep set date_joined='2015-03-16',birthday="1988-05-04" where employee_number=1;

（18）指定日期格式

标准的日期格式是：YYYY-MM-DD，指定日期格式的函数date_format()

%m:01-12,%d:01-31,%y:四位数的年份

mysql> select date_format(date_joined,'%m/%d/%y') from sales_rep where employee_number=1;
+-------------------------------------+
| date_format(date_joined,'%m/%d/%y') |
+-------------------------------------+
| 03/16/15                            |
+-------------------------------------+

%W:返回星期日期，%M:返回月份的名字 ,%e:显示日期01-31，

%a:星期日期的缩写，%D:带后缀的日期，%b:缩写的月份的名字，%y:四位数的年份

mysql> select date_format(date_joined,'%W %M %e %Y') from sales_rep where employee_number=1;
+----------------------------------------+
| date_format(date_joined,'%W %M %e %Y') |
+----------------------------------------+
| Monday March 16 2015                   |
+----------------------------------------+

mysql> select date_format(date_joined,'%a %D %b %Y') from sales_rep where employee_number=1;
+----------------------------------------+
| date_format(date_joined,'%a %D %b %Y') |
+----------------------------------------+
| Mon 16th Mar 2015                      |
+----------------------------------------+

(19)返回当前日期和时间

mysql> select now(),current_date();
+---------------------+----------------+
| now()               | current_date() |
+---------------------+----------------+
| 2015-03-16 22:46:46 | 2015-03-16     |
+---------------------+----------------+

mysql> select year(birthday) from sales_rep;
+----------------+
| year(birthday) |
+----------------+
|           1988 |
|           1980 |
|           1989 |
|           1989 |
|           1984 |
|           1984 |
|           1984 |
|           1984 |
+----------------+

mysql> select month(birthday),dayofmonth(birthday)  from sales_rep;
+-----------------+----------------------+
| month(birthday) | dayofmonth(birthday) |
+-----------------+----------------------+
|               5 |                    4 |
|               5 |                    4 |
|               5 |                    4 |
|               5 |                    4 |
|               5 |                    4 |
|               6 |                    4 |
|               6 |                    5 |
|               2 |                    5 |
+-----------------+----------------------+