mysql> #学习日期/时间类型
mysql> #date型,存储 年-月-日
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account |
| class |
| salary |
| stu |
| test |
| test2 |
+----------------+
6 rows in set (0.25 sec)
mysql> create table test3 (
-> star varchar(20) not null default '',
-> birth date not null default '0000-00-00'
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.17 sec)
mysql> desc test3;
+-------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+------------+-------+
| star | varchar(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+-------+-------------+------+-----+------------+-------+
2 rows in set (0.08 sec)
mysql> insert into test3
-> values
-> ('张国荣','1961-03-12');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xB9\xFA\xC8\xD9' for column 'star' at row 1
mysql> # 这个错误,什么引起的?
mysql> # 这是因为客户端没有声明字符集
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test3
-> values
-> ('张国荣','1961-03-12');
Query OK, 1 row affected (0.13 sec)
mysql> select * from test3;
+--------+------------+
| star | birth |
+--------+------------+
| 张国荣 | 1961-03-12 |
+--------+------------+
1 row in set (0.00 sec)
mysql> #date类型 能存储哪年到哪年?
mysql> # date能存1000-01-01 9999-12-31
mysql> # 时间类型 20:20:20
mysql> # 论坛每天来签到,记录签到时间
mysql> alter table test3 add sign time not null default 00:00:00;
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 ':00:00' at line 1
mysql> alter table test3 add sign time not null default '00:00:00';
Query OK, 1 row affected (0.39 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc test3;
+-------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+------------+-------+
| star | varchar(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
| sign | time | NO | | 00:00:00 | |
+-------+-------------+------+-----+------------+-------+
3 rows in set (0.05 sec)
mysql> insert into test3
-> (star,sign)
-> values
-> ('tlijian1989','19:10:45');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test3;
+-------------+------------+----------+
| star | birth | sign |
+-------------+------------+----------+
| 张国荣 | 1961-03-12 | 00:00:00 |
| tlijian1989 | 0000-00-00 | 19:10:45 |
+-------------+------------+----------+
2 rows in set (0.00 sec)
mysql> # datetime类型,日期时间类型
mysql> # 日期时间类型输入格式 YYYY-mm-dd HH:ii:ss
mysql> create table test4 (
-> 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.14 sec)
mysql> insert into test4
-> values
-> ('张三','2009-10-13 15:34:45');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test4;
+-------+---------------------+
| sname | logintime |
+-------+---------------------+
| 张三 | 2009-10-13 15:34:45 |
+-------+---------------------+
1 row in set (0.00 sec)
mysql> #一个比较有意思的列 timestamp
mysql> create table test5 (
-> ts timestamp default CURRENT_TIMESTAMP
-> ,
-> id int
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.17 sec)
mysql> insert into test5
-> (id)
-> values
-> (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test5;
+---------------------+------+
| ts | id |
+---------------------+------+
| 2012-10-23 20:36:26 | 1 |
| 2012-10-23 20:36:26 | 2 |
| 2012-10-23 20:36:26 | 3 |
+---------------------+------+
3 rows in set (0.00 sec)
mysql> insert into test5
-> (id)
-> values
-> (4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test5;
+---------------------+------+
| ts | id |
+---------------------+------+
| 2012-10-23 20:36:26 | 1 |
| 2012-10-23 20:36:26 | 2 |
| 2012-10-23 20:36:26 | 3 |
| 2012-10-23 20:36:57 | 4 |
+---------------------+------+
4 rows in set (0.00 sec)
mysql> #year类型
mysql> create table test6 (
-> thing varchar(20) not null default '',
-> ya year not null default '0000'
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.19 sec)
mysql> insert into test6
-> values
-> ('淝水之战','383');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql> # 超出范围了,year类型只占1字节,最多能存256种变化.
mysql> insert into test6
-> values
-> ('辛亥革命','1911');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test6;
+----------+------+
| thing | ya |
+----------+------+
| 辛亥革命 | 1911 |
+----------+------+
1 row in set (0.00 sec)
mysql> insert into test6
-> values
-> ('殖民火星','2156');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql> insert into test6
-> values
-> ('清帝逊位','1900');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql> insert into test6
-> values
-> ('清帝逊位','0000');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test6;
+----------+------+
| thing | ya |
+----------+------+
| 辛亥革命 | 1911 |
| 清帝逊位 | 0000 |
+----------+------+
2 rows in set (0.00 sec)
mysql> #year类型,还可以简写成2位
mysql> create table test7 (
-> ya year(2)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.22 sec)
mysql> insert into test7 values ('95','12');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into test7 values ('95'),('12');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test7;
+------+
| ya |
+------+
| 95 |
| 12 |
+------+
2 rows in set (0.00 sec)
mysql> #建member表
mysql> create table member (
-> id int unsigned auto_increment primary key,
-> username char(20) not null default '',
-> gender char(1) not null default '',
-> weight tinyint unsigned not null default 0,
-> birth date not null default '0000-00-00',
-> salary decimal(8,2) not null default 0.00,
-> lastlogin int unsigned not null default 0
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.17 sec)
mysql> create table m1 (
-> id int unsigned auto_increment primary key
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.11 sec)
mysql> desc m1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.13 sec)
mysql> alter table m1 add username char(20) not null default '';
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)
mysql> # 增加生日列
mysql> alter table m1 add birth date not null default '0000-00-00';
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
3 rows in set (0.23 sec)
mysql> # 发现gender性别列忘加了.想加上,而且要加在username后
mysql> alter table m1 add gender char(1) not null default '' after username;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| gender | char(1) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.19 sec)
mysql> # 如果想新建一个列,且在表的最前面,用first
mysql> alter table m1 add pid int not null default 0 first;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| pid | int(11) | NO | | 0 | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| gender | char(1) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
5 rows in set (0.09 sec)
mysql> #删除pid列
mysql> alter table m1 drop pid;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| gender | char(1) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.03 sec)
mysql> # 修改列. 到了X世纪,性别有男/女/雌雄同体/伪娘
mysql> # 这时我们想把char(1) 改为char(4)
mysql> alter table m1 modify gender char(4) not null default '';
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| gender | char(4) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.22 sec)
mysql> alter table m1 change id uid int unsigned;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc m1;
+----------+------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+-------+
| uid | int(10) unsigned | NO | PRI | 0 | |
| username | char(20) | NO | | | |
| gender | char(4) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+-------+
4 rows in set (0.03 sec)
mysql> exit
