跟燕十八学习PHP-第二十三天-mysql日期时间列详解date-time-year-timestamp

最新推荐文章于 2022-05-15 22:26:56 发布

weixin_30532759

最新推荐文章于 2022-05-15 22:26:56 发布

阅读量59

点赞数

文章标签：数据库 php

原文链接：http://www.cnblogs.com/phperlang/archive/2012/10/24/2737865.html

版权

/**
燕十八公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/

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> 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> 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> 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> # 增加生日列
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> # 发现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> # 如果想新建一个列,且在表的最前面,用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> # 修改列. 到了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> 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

燕十八老师太幽默了, 昨天的视频如下:

转载于:https://www.cnblogs.com/phperlang/archive/2012/10/24/2737865.html

weixin_30532759

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
跟燕十八学习PHP-第二十三天-mysql日期时间列详解date-time-year-timestamp

/** 燕十八公益PHP培训课堂地址:YY频道88354001 学习社区:www.zixue.it **/mysql> #学习日期/时间类型mysql> #date型,存储年-月-日mysql> show tables;ERROR 1046 (3D000): No database selectedmysql> use testDatabase change...
复制链接

扫一扫