当前位置: 代码迷 >> MySQL >> mysql 学习记要(三)-字段
  详细解决方案

mysql 学习记要(三)-字段

热度:180   发布时间:2016-05-05 16:39:53.0
mysql 学习记录(三)--字段
1.填充字段mysql> create database test1;Query OK, 1 row affected (0.02 sec)mysql> use test1;Database changedmysql> create table t1(id1 int,id2 int(5));Query OK, 0 rows affected (0.06 sec)mysql> insert into t1 values(1,1);Query OK, 1 row affected (0.02 sec)mysql> select * from t1;+------+------+| id1  | id2  |+------+------+|    1 |    1 |+------+------+1 row in set (0.00 sec)mysql> alter table t1 modify id1 int zerofill;Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> alter table t1 modify id2 int(5) zerofill;Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from t1;+------------+-------+| id1        | id2   |+------------+-------+| 0000000001 | 00001 |+------------+-------+1 row in set (0.00 sec)mysql> insert into t1 values(1,1111111);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------------+---------+| id1        | id2     |+------------+---------+| 0000000001 |   00001 || 0000000001 | 1111111 |+------------+---------+2 rows in set (0.00 sec)2.自增长字段的建立mysql> create table ai1(id int auto_increment not null primary key);Query OK, 0 rows affected (0.03 sec)mysql> create table ai2(id int auto_increment not null,primary key(id));Query OK, 0 rows affected (0.03 sec)mysql> create table ai3(id int auto_increment not null,unique(id));Query OK, 0 rows affected (0.03 sec)3.带小数的字段设置mysql> alter table t1 rename t1_test;Query OK, 0 rows affected (0.02 sec)mysql> desc t1_test;+-------+---------------------------+------+-----+---------+-------+| Field | Type                      | Null | Key | Default | Extra |+-------+---------------------------+------+-----+---------+-------+| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       || id2   | int(5) unsigned zerofill  | YES  |     | NULL    |       |+-------+---------------------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> create table t1( id1 float(5,2) default null, id2 double(5,2) default null, id3 decimal(5,2) default null);Query OK, 0 rows affected (0.03 sec)mysql> insert into t1 values(1.23,1.23,1.23);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 |+------+------+------+1 row in set (0.00 sec)mysql> insert into t1 values(1.234,1.234,1.23);Query OK, 1 row affected (0.02 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 |+------+------+------+2 rows in set (0.00 sec)mysql> insert into t1 values(1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 |+------+------+------+3 rows in set (0.00 sec)mysql> show warnings;mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 |+------+------+------+3 rows in set (0.00 sec)mysql> alter table t1 modify id1 float;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 modify id2 double;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 modify id3 decimal;Query OK, 3 rows affected, 3 warnings (0.04 sec)Records: 3  Duplicates: 0  Warnings: 3mysql> insert into t1 values(1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message                                  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+-------+-------+------+| id1   | id2   | id3  |+-------+-------+------+|  1.23 |  1.23 |    1 ||  1.23 |  1.23 |    1 ||  1.23 |  1.23 |    1 || 1.234 | 1.234 |    1 |+-------+-------+------+4 rows in set (0.00 sec)mysql> create table t2(id bit(1));Query OK, 0 rows affected (0.02 sec)mysql> desc t2;+-------+--------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id    | bit(1) | YES  |     | NULL    |       |+-------+--------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into t2 values(1);Query OK, 1 row affected (0.01 sec)mysql> select bin(id),hex(id) from t2;+---------+---------+| bin(id) | hex(id) |+---------+---------+| 1       | 1       |+---------+---------+1 row in set (0.00 sec)mysql> insert into t2 values(2);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+---------------------------------------------+| Level   | Code | Message                                     |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column 'id' at row 1 |+---------+------+---------------------------------------------+1 row in set (0.00 sec)mysql> alter table t2 modify id bit(2);Query OK, 2 rows affected (0.04 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> insert into t2 values(2);Query OK, 1 row affected (0.01 sec)mysql> select bin(id),hex(id) from t2;+---------+---------+| bin(id) | hex(id) |+---------+---------+| 1       | 1       || 1       | 1       || 10      | 2       |+---------+---------+3 rows in set (0.00 sec)mysql> create table t(d date,t time ,dt datetime);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| d     | date     | YES  |     | NULL    |       || t     | time     | YES  |     | NULL    |       || dt    | datetime | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)4.与时间有关的字段设置mysql> insert into t values(now(),now(),now());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+------------+----------+---------------------+| d          | t        | dt                  |+------------+----------+---------------------+| 2015-10-02 | 06:04:42 | 2015-10-02 06:04:42 |+------------+----------+---------------------+1 row in set (0.00 sec)mysql> alter table t rename t_test;Query OK, 0 rows affected (0.01 sec)mysql> create table t(id1 timestamp);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type      | Null | Key | Default           | Extra                       |+-------+-----------+------+-----+-------------------+-----------------------------+| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+-------+-----------+------+-----+-------------------+-----------------------------+1 row in set (0.00 sec)mysql> insert into t values(null);Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------------------+| id1                 |+---------------------+| 2015-10-02 06:05:20 |+---------------------+1 row in set (0.00 sec)mysql> alter table t add id2 timestamp;Query OK, 1 row affected (0.04 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> show create table t \G;*************************** 1. row ***************************       Table: tCreate Table: CREATE TABLE `t` (  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00') ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> create table t8( id1 timestamp not null default current_timestamp, id2 datetime default null );Query OK, 0 rows affected (0.03 sec)mysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value  |+---------------+--------+| time_zone     | SYSTEM |+---------------+--------+1 row in set (0.00 sec)mysql> select * from t8 ;Empty set (0.00 sec)mysql> insert into t8 values(now(),now());Query OK, 1 row affected (0.00 sec)mysql> select * from t8;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 06:07:16 | 2015-10-02 06:07:16 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> set time_zone='+9:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from t8;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:07:16 | 2015-10-02 06:07:16 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> desc t8;+-------+-----------+------+-----+-------------------+-------+| Field | Type      | Null | Key | Default           | Extra |+-------+-----------+------+-----+-------------------+-------+| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP |       || id2   | datetime  | YES  |     | NULL              |       |+-------+-----------+------+-----+-------------------+-------+2 rows in set (0.00 sec)mysql> desc t;+-------+-----------+------+-----+---------------------+-----------------------------+| Field | Type      | Null | Key | Default             | Extra                       |+-------+-----------+------+-----+---------------------+-----------------------------+| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP || id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |+-------+-----------+------+-----+---------------------+-----------------------------+2 rows in set (0.00 sec)mysql> insert into t(id1) values('19700101080001');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |+---------------------+---------------------+2 rows in set (0.00 sec)mysql> insert into t(id1) values(19700101080001);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |+---------------------+---------------------+3 rows in set (0.00 sec)mysql> insert into t(id1) values(19700101080000);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |+---------------------+---------------------+4 rows in set (0.00 sec)mysql> show warnings;Empty set (0.00 sec)mysql> desc t1;+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id1   | float         | YES  |     | NULL    |       || id2   | double        | YES  |     | NULL    |       || id3   | decimal(10,0) | YES  |     | NULL    |       |+-------+---------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> desc t;+-------+-----------+------+-----+---------------------+-----------------------------+| Field | Type      | Null | Key | Default             | Extra                       |+-------+-----------+------+-----+---------------------+-----------------------------+| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP || id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |+-------+-----------+------+-----+---------------------+-----------------------------+2 rows in set (0.00 sec)mysql> insert into t(id1) value('2038-01-19 11:14:07');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 2038-01-19 11:14:07 | 0000-00-00 00:00:00 |+---------------------+---------------------+5 rows in set (0.00 sec)mysql> insert into t(id1) value('2038-01-19 11:14:08');Query OK, 1 row affected (0.02 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 2038-01-19 11:14:07 | 0000-00-00 00:00:00 || 2038-01-19 11:14:08 | 0000-00-00 00:00:00 |+---------------------+---------------------+6 rows in set (0.00 sec)mysql> insert into t(id1) value('2097-01-19 11:14:08');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2015-10-02 22:05:20 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 || 2038-01-19 11:14:07 | 0000-00-00 00:00:00 || 2038-01-19 11:14:08 | 0000-00-00 00:00:00 || 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |+---------------------+---------------------+7 rows in set (0.00 sec)mysql> alter table t rename t_date;Query OK, 0 rows affected (0.02 sec)mysql> create table t(y year);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| y     | year(4) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into t values(2100);Query OK, 1 row affected (0.02 sec)mysql> select * from t;+------+| y    |+------+| 2100 |+------+1 row in set (0.00 sec)mysql> create table t6(dt datetime);Query OK, 0 rows affected (0.02 sec)mysql> insert into t6 values('2007-9-3 12:10:10');Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values('2007/9/3 12+10+10');Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values('20070903121010');Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(20070903121010);Query OK, 1 row affected (0.01 sec)mysql> select * from t6;+---------------------+| dt                  |+---------------------+| 2007-09-03 12:10:10 || 2007-09-03 12:10:10 || 2007-09-03 12:10:10 || 2007-09-03 12:10:10 |+---------------------+4 rows in set (0.01 sec)5.varchar与char字段的长度mysql> create table vc(v varchar(4),c char(4));Query OK, 0 rows affected (0.02 sec)mysql> insert into vc values('ab ','ab     ');Query OK, 1 row affected (0.01 sec)mysql> select length(v),length(c) from vc;+-----------+-----------+| length(v) | length(c) |+-----------+-----------+|         3 |         2 |+-----------+-----------+1 row in set (0.00 sec)mysql> select concat(v,'+'),concat(c,'+') from vc;+---------------+---------------+| concat(v,'+') | concat(c,'+') |+---------------+---------------+| ab +          | ab+           |+---------------+---------------+1 row in set (0.00 sec)mysql> drop table t;Query OK, 0 rows affected (0.02 sec)mysql> create table t (c binary(3));Query OK, 0 rows affected (0.02 sec)mysql> insert into t set c='a';Query OK, 1 row affected (0.01 sec)mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;+------+--------+-------+---------+-----------+| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |+------+--------+-------+---------+-----------+| a    | 610000 |     0 |       0 |         1 |+------+--------+-------+---------+-----------+1 row in set (0.00 sec)mysql> alter table t rename t_binary;Query OK, 0 rows affected (0.02 sec)6.枚举型字段mysql> create table t(gender enum('M','F'));Query OK, 0 rows affected (0.02 sec)mysql> insert into t values('m'),('1'),('f'),(null);Query OK, 4 rows affected (0.01 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t;+--------+| gender |+--------+| M      || M      || F      || NULL   |+--------+4 rows in set (0.01 sec)mysql> alter table t rename t_enum;Query OK, 0 rows affected (0.01 sec)7.set类型字段设置mysql> create table t(col set ('a','b','c','d' ));Query OK, 0 rows affected (0.03 sec)mysql> insert into t value('a,b'),('a,d,a'),('a,b'),('a,c'),('a');Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from t;+------+| col  |+------+| a,b  || a,d  || a,b  || a,c  || a    |+------+5 rows in set (0.00 sec)


版权声明:本博客原创文章欢迎转载,请转载的朋友最好注明出处,谢谢大家。

  相关解决方案