Day02_MySQLLearning
12_日期时间列date-time-year-timestamp
-
各自的特点:
-
注意:
- date类型 可以设置0000-00-00为默认值
- year类型 还可以存0000年,通常用来表示填写错误的年份。
- 在insert时,可以简写年的后两位,但是不推荐这样。其中[00-69]+2000,即范围为[2000-2069];[70-99]+1900;即:填2位,表示1970-2069
-
代码:
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> use worker;
Database changed
mysql> show tables;
+
| Tables_in_worker |
+
| account |
| class |
| salary |
| test |
| test2 |
+
5 rows in set (0.06 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.53 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.06 sec)
mysql> insert into test3 values ('张国荣' , 1961-03-12);
ERROR 1292 (22007): Incorrect date value: '1946' for column 'birth' at row 1
mysql> insert into test3 values ('张国荣' , '1961-03-12');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: worker
Query OK, 1 row affected (1.83 sec)
mysql> select * from test3;
+
| star | birth |
+
| 张国荣 | 1961-03-12 |
+
1 row in set (0.10 sec)
mysql>
mysql>
mysql>
mysql>
mysql> alter table test3 add sign time not null default '00:00:00';
Query OK, 1 row affected (0.41 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.14 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>
mysql>
mysql> create table test4
-> (sname varchar(20) not null default '',
-> logintime datetime not null default '0000-00-00 0000:00:00')
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into test4
-> values
-> ('张三' , '2009-10-13 15:34:45');
Query OK, 1 row affected (0.03 sec)
mysql> select * from test4;
+
| sname | logintime |
+
| 张三 | 2009-10-13 15:34:45 |
+
1 row in set (0.01 sec)
mysql>
mysql> create table test5
-> (ts timestamp default CURRENT_TIMESTAMP,
-> id int)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test5
-> (id)
-> values
-> (1) , (2) , (3);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test5;
+
| ts | id |
+
| 2019-08-26 11:36:26 | 1 |
| 2019-08-26 11:36:26 | 2 |
| 2019-08-26 11:36:26 | 3 |
+
3 rows in set (0.05 sec)
mysql> insert into test5
-> (id)
-> values
-> (4) , (5) , (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test5;
+
| ts | id |
+
| 2019-08-26 11:36:26 | 1 |
| 2019-08-26 11:36:26 | 2 |
| 2019-08-26 11:36:26 | 3 |
| 2019-08-26 11:37:16 | 4 |
| 2019-08-26 11:37:16 | 5 |
| 2019-08-26 11:37:16 | 6 |
+
6 rows in set (0.00 sec)
mysql>
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.11 sec)
mysql> insert into test6
-> values
-> ('淝水之战' , '383');
ERROR 1264 (22003): Out of range value for column 'ya' at row 1
mysql>
mysql> year 类型只占一个字节 , 最多能存256种变化
-> \c
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');
'> '
-> \c
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类型,还可以简写成两位
mysql> create table test7
-> (ya year(2))engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)
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)
13_网站建表实战及优化意识
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> use worker;
Database changed
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.08 sec)
14_表修改语法之列的增删改
-
增加列
- alter table 表名 add 列名称 列类型 列参数 【加的列在表的最后】
例:alert table n1 add birth date not null default ‘0000-00-00’; - alter table 表名 add 列名称 列类型 列参数 after 列名称 【把新列加在某列后】
例:alter table n1 add gender char(1) not null default ‘’ after username; - alter table 表名 add 列名称 列类型 列参数 first 【把新列加在最前面】
例:alter table n1 add pid int not null default 0 first;
-
删除列
-
修改列
- 修改列类型
alter tablee 表名 modify 列名 char(4) not null default ''
例:alter table n1 modify gender char(4) not null default ‘’; - 修改列名及列类型
alter table 表名 change 旧列名 新列名 新类型 新参数
例:alter table n1 change id uid int unsigned;
-
代码:
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> use worker;
Database changed
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.08 sec)
mysql> create table n1
-> (id int unsigned auto_increment primary key)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> desc n1;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+
1 row in set (0.14 sec)
mysql> alter table n1 add username char(20) not null default '';
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
+
2 rows in set (0.06 sec)
mysql>
mysql> alter table n1 add birth date not null default '0000-00-00';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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.03 sec)
mysql>
mysql> alter table n1 add gender char(1) not null default '';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table n1 drop column gender;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table n1 add gender char(1) not null default '' after username;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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>
mysql> alter table n1 add pid int not null default 0 first;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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.03 sec)
mysql>
mysql> alter table n1 drop pid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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.06 sec)
mysql>
mysql>
mysql> alter table n1 modify gender char(4) not null default '';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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.03 sec)
mysql> alter table n1 change id uid int unsigned;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc n1;
+
| 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.16 sec)
mysql> exit