6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

2019年12月3日08:45:59 评论 78 次浏览

6.1.数据类型(列类型)总览

6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

可见,mysql中的数据类型,总体分3大类:
数字型:
时间型:
字符型:
其中,在sql语句中,数字型数据不需要单引号引起来,而时间型和字符型数据需要用单引号引起来。

6.2.整型

整型数据类型包括:

tinyint     :微整型
smallint        :小整型
mediumint   :中整型
int         :整型
bigint      :大整型

这些不同大小范围的整型信息如下表所示:
类型 所占空间(字节) 带符号 无符号 最小值 较大值 最小值 较大值
tinyint 1 -128 127 0 255
smallint 2 -32768 32767 0 65535
mediumint 3 -8388608 8388607 0 16777215
int 4 -2147483648 2147483647 0 4294967295
bigint 8 -9223372036854775808 9223372036854775807 0 18446744073709551615

默认整数类型是带符号的,即可以有正负值,比如:

create  table  zhengxing1(num1  int, num2  tinyint);

此时,num1和num2中都可以存储负数(但都不能超出范围)
不带符号的整数类型设置形式如下:

create  table  zhengxing2(num1  int  unsigned, num2  tinyint  unsigned);
---创建一个表,具有int和tinyint类型(默认带符号),并使用数据测试:
create  table  zhengxing1(num1  int, num2  tinyint);
insert into zhengxing1(num1, num2) values(10, 10);//正确的数据
select * from zhengxing;
insert into zhengxing1(num1, num2) values(1000,1000);//错误的数据
        //错误的原因是:后一个1000超出了数据类型tinyint的范围
另一个演示:unsigned :
create  table  zhengxing2(num1  int unsigned, num2  tinyint unsigned);
insert into zhengxing2(num1, num2) values(10, 10);//正确的数据
insert into zhengxing2(num1, num2) values(-10, -10);//错误的数据
        //错误的原因:两个字段都设定了unsigned,只能是非负数

6.3.小数型

小数类型分为浮点小数和定点小数。

浮点小数

浮点小数是“不精确的小数”,包括float和double。

float:
占用4字节存储空间,可称为“单精度浮点数”,约7位有效数字。
double:
占用8字节存储空间,可称为“双精度浮点数”,约17位有效数字。

6.3.2.定点小数

定点小数是“精确的小数”——它通过内部技巧,突破了“有些小数无法用二进制精确表示”的局限。

其设定方式通常是这样的: decimal(M, D);

其中M表示该小数的总的有效位数(较大65),D表示该小数的小数点后的位数。

浮点小数演示:

定义三个字段分别为float、double和decimal类型,并都插入数字“123456789.123456789123456789”,显示结果。

定义三个字段分别为float、double和decimal类型,
并都插入数字“123456789.123456789123456789”,显示结果。
create table xiaoshu1(x1 float, x2 double, x3 decimal(23, 14) );
insert into xiaoshu1(x1, x2, x3)values(123456789.123456789123456789, 123456789.123456789123456789,123456789.123456789123456789);
select * from xiaoshu1;

6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

数据类型选择示例

要设置一个字段为年龄:tinnyint(-128~127) unsigned (无符号 较大255)
要设置一个人的工资:double(17为数字) unsigned或是decimal unsigned。工资没有负数;不能用float 只有7位数字 较高到百万
要全国人民的人口编号:int unsigned 较大到4294967295 40亿

6.4.日期时间型

日期时间类型包括如下几种:

date类型:
    表示日期,格式类似这样:'0000-00-00'

time类型:
    表示时间,格式类似这样:'00:00:00'

datetime类型:
    表示日期时间,格式类似这样:'0000-00-00 00:00:00'

timestamp类型:
    表示“时间戳”,其实就是一个整数数字,该数字是从是“时间起点”到现在为止的“秒数”。
    “时间起点”是:1970-1-1 0:0:0
    timestamp类型的字段,无需插入数据,而是会自动取得当前的日期时间(表示当前时刻)。
    而且,此类型字段会在数据被更新时,也同样自动取得当前的日期时间(表示修改的时刻)。
    特别总结:它在insert或update某行数据的时候,能够自动获得当前时间。

year类型:
    表示年份,格式为:'0000'

注意:
时间类型的字面值,通常使用单引号引起来

时间类型示例:

创建一个表,设定5个字段分别为上述类型,并插入相应的数据值后查看结果。

创建一个表,设定5个字段分别为上述类型,并插入相应的数据值后查看结果。
create table shijian1(
    t_time time,
    t_date date,
    t_datetime datetime,
    t_timestamp timestamp,
    t_year year
);
insert into shijian1(t_time, t_date, t_datetime, t_year)values
            ('10:57:50','2018-7-29', '2008-8-8 20:0:0', '2008');
select * from shijian1;


insert into shijian1(t_time, t_date, t_datetime, t_year)values
            ('10:57:50','2018-7-29', now() , '2008');

6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

now()函数的使用:
6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set
更新数据,以对比datetime类型的数据和timestamp类型的数据的区别:
6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

小细节:timestamp类型在一个表中只能用于一个字段!

6.5.字符串型

字符串类型常用的包括:char, varchar, text, enum, set,分述如下:

6.5.1.定长字符char和变长字符varchar

定长字符类型char:

适用于存储的字符长度为固定长度的字符,比如中国邮政编码,中国身份证号码,手机号码等。

字段名称 char(字符个数)

1,存储的字符长度固定,最长可设定为255个字符。
2,如果实际写入的字符不足设定长度,内部会自动用空格填充到设定的长度。
3,相对varchar类型,其存取速度更快。

变长字符类型varchar:

适用于存储字符长度经常不确定的字符,比如姓名,用户名,标题,内容,等大多数场合的字符。

字段名称 varchar(字符个数)

1,存储的字符长度是写入的实际长度,但不超过设定的长度。最长可设定为65532(字节)。
(1)注:由于其最长的限制是字节数,因此存储中文和英文的实际字符个数是不同的;
(2)英文:一个字符占一个字节;
(3)中文(gbk编码):一个字符占2个字节;
(4)中文(utf8编码):一个字符占3个字节;
2,如果实际写入的字符不足设定的长度,就按实际的长度存储。
相对于char字符串,其存取速度相对更慢。

字符串示例:

定义一个表,演示char和varchar的使用和区别:

---定义一个表,演示char和varchar的使用和区别:
create table char_varchar(
    id int auto_increment primary key,
    postcode char(6),
    user_name varchar(6)
);
insert into char_varchar( id, postcode, user_name) values
                        (null, '100110', 'lisi1');

insert into char_varchar( postcode, user_name) values
                        ('120130', 'lisi2');
insert into char_varchar( postcode, user_name) values
                        ('120150', '罗马里奥');
---下面给出非常规(非法)的数据情形:
insert into char_varchar( postcode, user_name) values
                        ('120130150', 'lisi3333');
insert into char_varchar( postcode, user_name) values
                        ('120140', 'lisi3333');
    ---以上两行都报错!
    演示不足个数的情况:
insert into char_varchar( id, postcode, user_name) values
                        (null, '110', 'ls4');
    //上一行也可以执行成功!但:
        //postcode字段的值仍然占6个字符
        //而user_name字段只真3个字符

一个表中有一个字段为c1,其类型为char(10),另有一个字段为c2,类型为varchar,问:
c2最多可以设置多长?

1,一个表中的行也有一个“较大字节长度的限制”,一行最多存储65532字节。
2,则此时,c2最多可以设置65532-10 = 65522(长度设定)
3,如果c2中存储的全是英文字符,就可以存储65522个
4,如果存储中文:
gbk:最多65522/2 = 32761个
utf8:最多65522/3 = 21840个

6.5.2.text长文本类型

适用于存储“较长的文本内容”,比如文章内容。最长可存储65535个字符。
如果还需要存储更长的文本,可以使用mediumtext(1600万左右)或longtext(40亿左右)。

字段名称   text

text类型的字段不能设置默认值。
text类型虽然是字符类型,但不能设置长度!!!
text类型的数据不存在行中。

//演示有关text类型和其他相关类型
create table article(   -- 文章表
    id int auto_increment primary key,
    title varchar(100), -- 标题
    author varchar(20), -- 作者
    content text,
    pub_time datetime, -- 发布时间
    edit_time timestamp -- 更新时间
);
insert into article (title, author, content, pub_time) values
    ('文章标题1','张三','文章内容1','2018-8-8 12:15:30');

insert into article (title, author, content, pub_time) values
    ('文章标题2','张4','文章内容2', now() );

6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

6.5.3.enum和set类型

enum类型和set类型都是用于存储“有给定值的可选字符”,比如类似表单中的单选,多选,下拉列表。
enum类型(单选类型/枚举类型):
enum类型通常用于存储表单中的“单选项”的值。

设定形式:
enum(‘选项值1’, ‘选项值2’, ‘选项值3’, ....)
这些选项值都对应了相应的“索引值”,类似索引数组的下标,但是从1开始的。
即这些选项的索引值分别为:1, 2, 3, 4, .....
enum类型最多可设定65535个选项。
示例:
create  table  tab1 (id  int,  edu(‘大学’, ‘中学’, ‘小学’, ‘其他’ )  );
insert  into  tab1 (id, edu) values (1, ‘大学’);
或:
insert  into  tab1 (id, edu) values (1, 2);     //表示中学

set类型(多选类型):
set类型通常用于存储表单中的“多选项”的值。

设定形式:
set(‘选项值1’, ‘选项值2’, ‘选项值3’, ....)
这些选项值都对应了相应的“索引值”,其索引值从1开始,并“依次翻倍”。
即这些选项的索引值分别为:1, 2, 4, 8, 16, .....  (其实就是2的n次方)
enum类型最多可设定64个选项值。
示例:
create  table  tab2 (aihao(‘篮球’, ‘排球’, ‘足球’, ‘中国足球’ )  ); #对应索引值为1,2,4,8
insert  into  tab2( aihao ) values ( ‘篮球’ );
或:
insert  into  tab2 ( aihao ) values ( ‘篮球,排球’ );
或:
insert  into  tab2 ( aihao ) values ( ‘篮球,足球,排球’ );
或:
insert  into  tab2 ( aihao ) values ( 2 );  //表示排球(2)
或:
insert  into  tab2 ( aihao ) values ( 3);       //表示“篮球,排球”(1+2)
或:
insert  into  tab2 ( aihao ) values ( 7);       //表示“篮球,排球,足球”(1+2+4)

enum set 示例

enum和set类型:
create table user3(
    id int auto_increment primary key,
    user_name varchar(20),
    user_pass char(32),
    edu enum('小学', '中学','大学'),  -- 单选项数据
    aihao set('篮球','排球','足球','中国足球') -- 多选选数据
);
insert into user3 (user_name, user_pass, edu, aihao)
            values('user1','123','小学','篮球');
insert into user3 (user_name, user_pass, edu, aihao)
            values('user2','123','中学','篮球,排球');

select * from user3;

使用索引值来插入数据:

使用索引号来插入数据:
insert into user3 (user_name, user_pass, edu, aihao)
            values('user3','123', 3 , 4 );

insert into user3 (user_name, user_pass, edu, aihao)
            values('user4','123', 3 , 5 );
            //5 代表1+4,也就是篮球和足球
insert into user3 (user_name, user_pass, edu, aihao)
            values('user5','123', 2 , 7 );
            //7 代表1+2+4,也就是篮球和足球
update user3 set aihao = 5 where id = 5;

6.MySQL数据类型 整型 小数型 日期时间 字符串 enum set

继续阅读
weinxin
加我微信
微信扫一扫,加我微信好友,共同交流,共同进步! (备注:焦国强博客)
mysql 最后更新:2019-12-7
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: