第2章 数据类型与操作数据表

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

下面的表显示了需要的每个数值类型的存储和范围：

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

1. mysql> INSERT tb1 VALUES(‘Tom‘,25,7863.25); //插入记录；
2. Query OK,1 row affected (0.08 sec)
4. mysql> INSERT tb1 VALUES(‘Tom‘,25);
5. ERROR 1136(21S01):Column count doesn‘t match value count at row 1
6. mysql> INSERT tb1(username,salary) VALUES(‘John‘,4500.69); //插入部分记录
7. Query OK, 1 row affected (0.06 sec)
9. mysql> SELECT * FROM tb1; //查看数据表中的所有字段；
10. +----------+------+---------+
11. | username | age | salary |
12. +----------+------+---------+
13. | Tom | 25 | 7863.25 |
14. | John | NULL | 4500.69 |
15. +----------+------+---------+
16. 2 rows in set (0.02 sec)

1. mysql> CREATE TABLE tb2(
2. -> username VARCHAR(20) NOT NULL, //表示该字段今后需要写入值得时候必须要赋值，否则将会产生错误；
3. -> age TINYINT UNSIGNED NULL //可以不赋值，默认为NULL；
4. ->);
5. Query OK,0 rows affected (0.14 sec)
7. mysql> SHOW COLUMNS FROM tb2;
8. +----------+---------------------+------+-----+---------+-------+
9. |Field|Type|Null|Key|Default|Extra|
10. +----------+---------------------+------+-----+---------+-------+
11. | username | varchar(20)| NO || NULL ||
12. | age | tinyint(3) unsigned | YES || NULL ||
13. +----------+---------------------+------+-----+---------+-------+
14. 2 rows inset(0.02 sec)
16. mysql>

1. mysql> CREATE TABLE tb3(
2. -> id SMALLINT UNSIGNED AUTO_INCREMENT, //自动编号必须与主键组合使用；
3. -> username VARCHAR(30) NOT NULL
4. ->);
5. ERROR 1075(42000):Incorrect table definition; there can be only one auto column and it must be defined as a key
6. mysql> CREATE TABLE tb3(
7. -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, //自动编号必须与主键组合使用；
8. -> username VARCHAR(30) NOT NULL
9. ->);
10. Query OK,0 rows affected (0.08 sec)
11. 
    
12. mysql> SHOW COLUMNS FROM tb3;
13. +----------+----------------------+------+-----+---------+----------------+
14. |Field|Type|Null|Key|Default|Extra|
15. +----------+----------------------+------+-----+---------+----------------+
16. | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | //主键默认为NOT NULL
17. | username | varchar(30)| NO || NULL ||
18. +----------+----------------------+------+-----+---------+----------------+
19. 2 rows inset(0.01 sec)
21. mysql> INSERT tb3(username) VALUES(‘Tom‘);
22. Query OK,1 row affected (0.05 sec)
24. mysql> INSERT tb3(username) VALUES(‘John‘);
25. Query OK,1 row affected (0.03 sec)
27. mysql> INSERT tb3(username) VALUES(‘Rose‘);
28. Query OK,1 row affected (0.04 sec)
30. mysql> INSERT tb3(username) VALUES(‘Dimitan‘);
31. Query OK,1 row affected (0.05 sec)
33. mysql> SELECT * FROM tb3;
34. +----+----------+
35. | id | username |
36. +----+----------+
37. |1|Tom| //自动编号，每次自增量为1；
38. |2|John|
39. |3|Rose|
40. |4|Dimitan|
41. +----+----------+
42. 4 rows inset(0.00 sec)
44. mysql>

1. mysql> CREATE TABLE tb4(
2. -> id SMALLINT UNSIGNED PRIMARY KEY, //没有自动编号
3. -> username VARCHAR(20) NOT NULL
4. ->);
5. Query OK,0 rows affected (0.14 sec)
7. mysql> SHOW COLUMNS FROM tb4;
8. +----------+----------------------+------+-----+---------+-------+
9. |Field|Type|Null|Key|Default|Extra|
10. +----------+----------------------+------+-----+---------+-------+
11. | id | smallint(5) unsigned | NO | PRI | NULL ||
12. | username | varchar(20)| NO || NULL ||
13. +----------+----------------------+------+-----+---------+-------+
14. 2 rows inset(0.00 sec)
16. mysql> INSERT tb4 VALUES(4,‘Tom‘);
17. Query OK,1 row affected (0.05 sec)
19. mysql> INSERT tb4 VALUES(22,‘John‘);
20. Query OK,1 row affected (0.05 sec)
22. mysql> SELECT * FROM tb4;
23. +----+----------+
24. | id | username |
25. +----+----------+
26. |4|Tom|
27. |22|John|
28. +----+----------+
29. 2 rows inset(0.00 sec)
31. mysql>
32. mysql> INSERT tb4 VALUES(22,‘Rose‘); //不能插入相同的主键；

33. ERROR 1062 (23000): Duplicate entry ‘22‘ for key ‘PRIMARY‘

34.  

1. mysql> CREATE TABLE tb5(
2. -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, //无符号、自动编号、主键约束；
3. -> username VARCHAR(20) NOT NULL UNIQUE KEY, //唯一约束；
4. -> age tinyint UNSIGNED
5. ->);
6. Query OK,0 rows affected (0.08 sec)
8. mysql> SHOW COLUMNS FROM tb5;
9. +----------+----------------------+------+-----+---------+----------------+
10. |Field|Type|Null|Key|Default|Extra|
11. +----------+----------------------+------+-----+---------+----------------+
12. | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
13. | username | varchar(20)| NO | UNI | NULL ||
14. | age | tinyint(3) unsigned | YES || NULL ||
15. +----------+----------------------+------+-----+---------+----------------+
16. 3 rows inset(0.01 sec)
18. mysql> INSERT tb5(username,age) VALUES(‘Tom‘,22);
19. Query OK,1 row affected (0.03 sec)
21. mysql> INSERT tb5(username,age) VALUES(‘Tom‘,22);
22. ERROR 1062(23000):Duplicate entry ‘Tom‘for key ‘username‘
23. mysql>

1. mysql> CREATE TABLE tb6(
2. -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3. -> username VARCHAR(20) NOT NULL UNIQUE KEY,
4. -> sex ENUM(‘1‘,‘2‘,‘3‘) DEFAULT ‘3‘
5. ->);
6. Query OK,0 rows affected (0.08 sec)
8. mysql> SHOW COLUMNS FROM tb6; //查看数据表结构；
9. +----------+----------------------+------+-----+---------+----------------+
10. |Field|Type|Null|Key|Default|Extra|
11. +----------+----------------------+------+-----+---------+----------------+
12. | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
13. | username | varchar(20)| NO | UNI | NULL ||
14. | sex | enum(‘1‘,‘2‘,‘3‘)| YES ||3|| //默认值3；
15. +----------+----------------------+------+-----+---------+----------------+
16. 3 rows inset(0.03 sec)
18. mysql> INSERT tb6(username) VALUES(‘Tom‘);
19. Query OK,1 row affected (0.05 sec)
21. mysql> SELECT * FROM tb6;
22. +----+----------+------+
23. | id | username | sex |
24. +----+----------+------+
25. |1|Tom|3|
26. +----+----------+------+
27. 1 row inset(0.00 sec)
29. mysql>