- 论坛徽章:
- 0
|
这是我以前学习mysql的学习笔记,学习教程《mysql完全手册》。
第五章 数据类型
1,数值类型
tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节
bigint 8个字节
float 4个字节
double 8个字节
decimal 依赖于M和D的值
numeric 依赖于M和D的值
数值类型的实验:
mysql> create table test(tid tinyint,sid smallint,mid mediumint,bid bigint);
Query OK, 0 rows affected (0.00 sec)
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| tid | tinyint(4) | YES | | NULL | |
| sid | smallint(6) | YES | | NULL | |
| mid | mediumint(9) | YES | | NULL | |
| bid | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into test values (123456789,123456789,123456789,123456789);
Query OK, 1 row affected, 3 warnings (0.02 sec)
mysql> select * from test;
+------+-------+---------+-----------+
| tid | sid | mid | bid |
+------+-------+---------+-----------+
| 127 | 32767 | 8388607 | 123456789 |
+------+-------+---------+-----------+
1 row in set (0.00 sec)
mysql> insert into test values (-123456789,-123456789,-123456789,-123456789);
Query OK, 1 row affected, 3 warnings (0.01 sec)
mysql> select * from test;
+------+--------+----------+------------+
| tid | sid | mid | bid |
+------+--------+----------+------------+
| 127 | 32767 | 8388607 | 123456789 |
| -128 | -32768 | -8388608 | -123456789 |
+------+--------+----------+------------+
2 rows in set (0.00 sec)
mysql>alter table test add uid tinyint unsigned;###添加一列uid类型为unsigned
mysql>insert into test values (123456789,123456789,123456789,123456789,123456789);
mysql> select * from test;
+------+--------+----------+------------+------+
| tid | sid | mid | bid | uid |
+------+--------+----------+------------+------+
| 127 | 32767 | 8388607 | 123456789 | NULL |
| -128 | -32768 | -8388608 | -123456789 | NULL |
| 127 | 32767 | 8388607 | 123456789 | 255 |
+------+--------+----------+------------+------+
3 rows in set (0.00 sec)
mysql> alter table test add fid float(5,2);##增加一列fid类型为float
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| tid | tinyint(4) | YES | | NULL | |
| sid | smallint(6) | YES | | NULL | |
| mid | mediumint(9) | YES | | NULL | |
| bid | bigint(20) | YES | | NULL | |
| uid | tinyint(3) unsigned | YES | | NULL | |
| fid | float(5,2) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> insert into test(fid) values(123.456);
Query OK, 1 row affected (0.03 sec)
mysql> select fid from test;
+--------+
| fid |
+--------+
| 123.46 |
+--------+
4 rows in set (0.00 sec)
2,字符串类型
char 2^8字节
varchar 2^8字节
tinyblob 2^8字节
tinytext 2^8字节
blob 2^16字节
text 2^16字节
mediumblob 2^24字节
mediumtext 2^24字节
longblob 2^32字节
longtext 2^32字节
字符串类型的实验:
3,时间日期类型:
date 3字节
time 3字节
datetime 8字节
year 1字节
datestamp 8字节
4,复合类型
enum
mysql> create table enums(sex enum("M","F"));
Query OK, 0 rows affected (0.83 sec)
mysql> desc enums;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sex | enum('M','F') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.31 sec)
mysql> insert into enums values("M");
Query OK, 1 row affected (0.04 sec)
mysql> insert into enums values("F");
Query OK, 1 row affected (0.00 sec)
mysql> insert into enums values("F");
Query OK, 1 row affected (0.00 sec)
mysql> select * from enums;
+------+
| sex |
+------+
| M |
| F |
| F |
+------+
3 rows in set (0.03 sec)
SET
mysql> create table color(color set("white","red","gteen","black"));
Query OK, 0 rows affected (0.02 sec)
mysql> desc color;
+-------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+---------+-------+
| color | set('white','red','gteen','black') | YES | | NULL | |
+-------+------------------------------------+------+-----+---------+-------+
1 row in set (0.03 sec)
mysql> insert into colot values("red");
ERROR 1146 (42S02): Table 'school.colot' doesn't exist
mysql> insert into color values("red");
Query OK, 1 row affected (0.00 sec)
mysql> insert into color values("red,white");
Query OK, 1 row affected (0.00 sec)
mysql> insert into color values("red,white,hhhh");
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into color values("jjjjred,white,hhhh");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from color;
+-----------+
| color |
+-----------+
| red |
| white,red |
| white,red |
| white |
+-----------+
4 rows in set (0.00 sec)
***注意,enum和set一样,对于任何不是预定义中的字符都将放弃。。。
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u/29452/showart_270217.html |
|