一、字段类型
1.int类型
有符号的范围:-2147483648~2147483647(2的31次放减1)
无符号的范围:0~4294967295
2.unsigned修饰符规定字段只保存正的值,它可以增大这个字段支持的值的范围
3.zerofill修饰符规定0(不是空格)可以用于填补输出值,可以防止MySql存储负值
4.举例
(1)创建表:create table data(a int,b int unsigned,c int zerofill,d int unsigned zerofill);
(2)插入数据:
insert into data values(10,10,10,10),(-10,-10,-10,-10),(2147483647,2147483647,2147483647,2147483647),(2147483648,2147483648,2147483648,2147483648);
(3)显示数据:
a
| b
| c
| d
|
+------------+------------+------------+------------+
|
10 |
10 | 0000000010 | 0000000010 |
|
-10 |
0 | 0000000000 | 0000000000 |
| 2147483647 | 2147483647 | 2147483647 | 2147483647 |
| 2147483647 | 2147483648 | 2147483648 | 2147483648 |
5.float,double,decimal类型:小数部分超过指定长度会四舍五入
6.字符串类型char、varchar
7.日期和时间类型 datetime、timestamp
(1)创建表:create table data2(time datetime,stamp timestamp);
(2)插入数据:insert into data2 values(now(),now());
或insert into data2 values(sysdate(),sysdate());
8.复合类型
(1)enum类型举例:
1)建表:create table data3(sex enum('M','F'));
2)插入数据:insert into data3 values('M'),('m'),('f'),('xx'),(NULL);
3)查询所有:select * from data3;
+------+
| sex
|
+------+
| M
|
| M
|
| F
|
|
|
| NULL |
4)查询为0的:select * from data3 where sex=0;
| sex |
+------+
|
|
5)查询为1的:select * from data3 where sex=1;
| sex
|
+------+
| M
|
| M
|
(2)set类型举例:
1)建表:create table data4(sex set('m','f'));
2)插入数据:insert into data4 values('m,f');
3)查询:select * from data4;
| sex
|
+------+
| m,f
|
+------+
4)特殊插入:insert into data4 values('a,m,b,m,c,f,d');
5)查询:结果与3相同