测试数据类型（整型，浮点）

创建表格

1. MYISAM 引擎下
   – 表结构存储在frm中
   – 表数据存储在MYD中
   – 表索引存在MYI 中
   

查看数据库下的表

1. SHOW TABLE

mysql> show tables
    -> ;
+---------------+
| Tables_in_db4 |
+---------------+
| cms_cate      |
| cms_news      |
+---------------+

查看表结构

1. DESC tbl_name

mysql> DESC cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubTime  | int(11)     | YES  |     | NULL    |       |
| clicknum | int(11)     | YES  |     | NULL    |       |
| isTOP    | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2. DESCRIBE tbl_name
3. SHOW COLUMNS FROM tbl_name

测试数据类型

1. 测试整型
   – 创建一个含有不同整型的表格

mysql> CREATE TABLE IF NOT EXISTS test1(
    -> num1 TINYINT,
    -> num2 SMALLINT,
    -> num3 MEDIUMINT,
    -> num4 INT,
    -> num5 BIGINT
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

– 向表中插入记录INSERT table_name() VALUE

mysql> INSERT test1 VALUES(-128, -32768, -8388608,-2147483648,-9223372036854775808);

– 查询表中所有记录SELECT*FROM table_name()

mysql> SELECT*FROM test1;
+------+--------+----------+-------------+----------------------+
| num1 | num2   | num3     | num4        | num5                 |
+------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+
1 row in set (0.00 sec)

*只要不在range中就无法插入比如说

mysql> INSERT test1 VALUES(-128, -32768, -8388608,-2147483648,-9223372036854775808);
Query OK, 1 row affected (0.22 sec)

mysql> INSERT test1 VALUES(-129, -32768, -8388608,-2147483648,-9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1

2. 无符号测试
   – 这里注意要是我们标注了UNSIGNED 给整型，那么将不能填充负数，相应的，整数范围将会扩大一倍。

mysql> CREATE TABLE IF NOT EXISTS test2(
    -> num1 TINYINT UNSIGNED,
    -> num2 TINYINT
    -> );
Query OK, 0 rows affected (0.74 sec)

mysql> INSERT test2 VALUES(0,-12);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT*FROM test2;
+------+------+
| num1 | num2 |
+------+------+
|    0 |  -12 |
+------+------+
1 row in set (0.00 sec)

– 0填充 ZEROFILL：
当值达不到的显示长度的时候会以0填充

mysql> DESC test2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| num1  | tinyint(3) unsigned | YES  |     | NULL    |       |
| num2  | tinyint(4)          | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+

这里我们好TINYINT（3）表示显示值为长度为3，如果我们在这里用ZEROFILL， 自动这里就没有符号了，比如

mysql> CREATE TABLE IF NOT EXISTS test3(
    -> num1 TINYINT ZEROFILL,
    -> num2 SMALLINT ZEROFILL,
    -> num3 MEDIUMINT ZEROFILL,
    -> num4 INT ZEROFILL,
    -> num5 BIGINT ZEROFILL
    -> );
Query OK, 0 rows affected (0.86 sec)

mysql> DESC test3;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| num1  | tinyint(3) unsigned zerofill   | YES  |     | NULL    |       |
| num2  | smallint(5) unsigned zerofill  | YES  |     | NULL    |       |
| num3  | mediumint(8) unsigned zerofill | YES  |     | NULL    |       |
| num4  | int(10) unsigned zerofill      | YES  |     | NULL    |       |
| num5  | bigint(20) unsigned zerofill   | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> INSERT test3 VALUES(1,1,1,1,1);
Query OK, 1 row affected (0.47 sec)

mysql> SELECT*FROM test3;
+------+-------+----------+------------+----------------------+
| num1 | num2  | num3     | num4       | num5                 |
+------+-------+----------+------------+----------------------+
|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
1 row in set (0.00 sec)

这里1 变成了001， 00001等，如果达到了显示长度，就不会补0了。

3. 测试浮点类型
   – FLOAT, DOUBLE, DECIMAL

mysql> CREATE TABLE IF NOT EXISTS test4(
    -> num1 FLOAT(6,2),
    -> num2 DOUBLE(6,2),
    -> num3 DECIMAL(6,2)
    -> );
Query OK, 0 rows affected (0.80 sec)

mysql> INSERT test4 VALUE(3.1415,3.141592,3.1415926);
Query OK, 1 row affected, 1 warning (0.49 sec)

mysql> SELECT*FROM test4;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)

• 精度是保留小数点后两位，但是我们依旧插入成功了，对数字进行了四舍五入（是四舍五入了而不是截断了）

• 定点数是以字符串的形式存储的，精度高。浮点数不可比较的因为涉及四舍五入，而定点数可以