ITPub博客

首页 > 数据库 > MySQL > Mysql 基础数据类型(无时间相关类型)

Mysql 基础数据类型(无时间相关类型)

原创 MySQL 作者:yewushang 时间:2015-03-13 00:30:31 0 删除 编辑


数据类型无时间相关类型

--zerofill不满足位数的用0填充

mysql> use yws
Database changed
mysql> create table t1(id1 int,id2 int(5));
Query OK, 0 rows affected (0.11 sec)


mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.06 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.32 sec)
Records: 1  Duplicates: 0  Warnings: 0




mysql> alter table t1 modify id2 int zerofill;
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> select * from t1;
+------------+------------+
| id1        | id2        |
+------------+------------+
| 0000000001 | 0000000001 |
+------------+------------+
1 row in set (0.00 sec)

increment类型
mysql> create table a1(id int auto_increment not null primary key);
Query OK, 0 rows affected (0.11 sec)



mysql> alter table a1 add column name varchar(20);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into a1 (name) values ('yws');
Query OK, 1 row affected (0.07 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from a1;
+----+------+
| id | name |
+----+------+
|  1 | yws  |
+----+------+
1 row in set (0.00 sec)


mysql> insert into a1 (name) values ('yws');
Query OK, 1 row affected (0.05 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from a1;
+----+------+
| id | name |
+----+------+
|  1 | yws  |
|  2 | yws  |
+----+------+
2 rows in set (0.00 sec)

float double decimal类型

mysql> create table a2(id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.09 sec)



mysql> insert into a2 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.07 sec)


mysql> select * from a2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into a2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.03 sec)




mysql> select * from a2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)


mysql> alter table a2 modify column id1 float;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table a2 modify column id2 double;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table a2 modify column id3 decimal;
Query OK, 2 rows affected, 2 warnings (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 2


mysql> insert into a2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.06 sec)


mysql> select * from a2;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
|  1.23 |  1.23 |    1 |
|  1.23 |  1.23 |    1 |
| 1.234 | 1.234 |    1 |
+-------+-------+------+
3 rows in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

bit数据类型

mysql> create table a3 (id bit);
Query OK, 0 rows affected (0.09 sec)


mysql> desc a3;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)


mysql> insert into a3 values(11);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into a3 values(1);
Query OK, 1 row affected (0.05 sec)


mysql> select * from a3;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)


mysql> select bin(id),hex(id) from a3;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.04 sec)


mysql>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1457886/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-09

  • 博文量
    53
  • 访问量
    277278