时间日期类型

mysql> create database test2;
Query OK, 1 row affected (0.06 sec)

mysql> truncate test2;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qq                 |
| test               |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> truncate test2;
ERROR 1046 (3D000): No database selected
mysql> drop database test2;
Query OK, 0 rows affected (0.29 sec)

mysql> create database dateTest;
Query OK, 1 row affected (0.03 sec)

mysql> use dateTest;
Database changed
mysql> drop databases dateTest;
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 'databases dateTest' at line 1
mysql> drop database dateTest;
Query OK, 0 rows affected (0.00 sec)

mysql> use database qq;
ERROR 1049 (42000): Unknown database 'database'
mysql> use qq;
Database changed
mysql> create table datetest{
    -> star varchar(20),
    -> birth date not null default '0000-00-00'
    -> )engine myisam charset utf8;
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 '{
star varchar(20),
birth date not null default '0000-00-00'
)engine myisam char' at line 1
mysql> #学习日期、时间类型 date型 年-月-日
mysql> create table datetest(
    -> star varchar(20) not null default '',
    -> birth date not null default '0000-00-00'
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into datetest
    -> values
    -> ('zhang','1961-01-22');
Query OK, 1 row affected (0.01 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_qq |
+--------------+
| class        |
| datetest     |
| stu          |
+--------------+
3 rows in set (0.02 sec)

mysql> select * from datetest;
+-------+------------+
| star  | birth      |
+-------+------------+
| zhang | 1961-01-22 |
+-------+------------+
1 row in set (0.00 sec)

mysql> #date的存储范围 1000-01-01  9999-12-31
mysql> #时间类型 10:37:20 论坛签到，记录签到的时间
mysql> alter table datetest add sign time not null default '00:00:00';
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into datetest
    -> (star,sign)
    -> values
    -> ('tiantian','08:06:23');
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetest;
+----------+------------+----------+
| star     | birth      | sign     |
+----------+------------+----------+
| zhang    | 1961-01-22 | 00:00:00 |
| tiantian | 0000-00-00 | 08:06:23 |
+----------+------------+----------+
2 rows in set (0.02 sec)

mysql> #日期时间类型输入格式  YYYY-MM-DD HH-mm-ss
mysql> create table datetime(
    -> sname varchar(20) not null default '',
    -> logintime datetime not null default '0000-00-00 00-00-00'
    -> >engine myisam charset utf8;
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 '>engine myisam charset utf8' at line 4
mysql> create table datetime(
    -> sname varchar(20) not null default '',
    -> logintime datetime not null default '0000-00-00 00-00-00'
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into datetime
    -> values
    -> ('张三','2015-05-06 20-12-22');
Query OK, 1 row affected (0.02 sec)

mysql> select * from datetime;
+-------+---------------------+
| sname | logintime           |
+-------+---------------------+
| 张三      | 2015-05-06 20:12:22 |
+-------+---------------------+
1 row in set (0.00 sec)

mysql> insert into datetime
    -> values
    -> ('zhangdan','2015-03-03 23:23:33';
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 3
mysql> insert into datetime
    -> values
    -> ('zhangdan','2015-03-03 20:20:56');
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetime;
+----------+---------------------+
| sname    | logintime           |
+----------+---------------------+
| 张三         | 2015-05-06 20:12:22 |
| zhangdan | 2015-03-03 20:20:56 |
+----------+---------------------+
2 rows in set (0.00 sec)

mysql> alter table datetime add shijian timestamp default current_timestamp;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into datetime
    -> (sname,logintime)
    -> values
    -> ('lixi','2015-06-16 21:15:29');
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetime;
+----------+---------------------+---------------------+
| sname    | logintime           | shijian             |
+----------+---------------------+---------------------+
| 张三         | 2015-05-06 20:12:22 | 0000-00-00 00:00:00 |
| zhangdan | 2015-03-03 20:20:56 | 0000-00-00 00:00:00 |
| lixi     | 2015-06-16 21:15:29 | 2015-07-19 11:40:59 |
+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> #year类型，存储范围为1911-2155，占用1字节