mysql数据库列类型_mysql数据库的基本操作(列类型、类属性)

目录

1.列类型--整型

2.如何存储布尔类型数据

3.列类型--浮点型

4.列类型--定点型decimal

5.列类型--时间日期类型

6.列类型--字符串char和varchar

7.列类型--文本字符串

8.列类型--枚举字符串enum(单选)

9.列类型--集合字符串set(多选)

10.列属性--空属性

11.列属性--列描述

12.列属性--默认值

1.列类型--整型   

* 数据类型，也称为列类型

* SQL中将数据类型分成了三大类：数值类型、字符串类型、日期类型

* 整数型

tinyint:迷你整形，使用1个子节存储(常用)

smallint:小整形，2个字节

mediumint:中整型，3个字节

int:标准整型，4个字节(常用)

bigint:大整型，8个字节

* SQL中的数值默认是有符号的，要想限定类型是无符号的：int unsigned

例子：

create table tb_stu(

age int unsigned

)charset=utf8;

* 通过desc tb_stu;查看表结构时，int类型是int(11),tinyint类型是tinyint(4)

(11)和(4)是【显示宽度】，数据最终显示的位数，-123---显示宽度为4位，123--显示宽度是3位

- 可以在创建表时指定显示宽度：tinyint(2)[unsigned] zerofill，这样，数据类型默认会加上unsigned,

数据就是无符号的。当数据小于2位时，前面加0。

- 例子：alter table tb_stu add age tinyint(2) [unsigned] zerofill;

当有zerofill属性时，[unsigned]不写也是默认存在的。

zerofill：表示当数据小于2位时，前面加0

2.如何存储布尔类型数据   

drop table if exists stu1;

create table stu1 (

username varchar(20),

`password` varchar(32),

state tinyint

)charset utf8;

insert into stu1 values('张三','123',true),('李四','456',false),('王五','789',2);

<====>insert into stu1 values('张三','123',1),('李四','456',0),('王五','789',2);

select * from stu1 where state = false  <==等价==>  select * from stu1 where state = 0

select * from stu1 where state = true  <==等价==>  select * from stu1 where state = 1

3.列类型--浮点型   

* SQL将小数分为：浮点型和定点型

浮点型：小数点浮点，精度有限，而且会丢失精度

定点型：小数点固定，精度固定，不会丢失精度

* 浮点类型：

float：单精度，占用4个字节存储数据，精度范围大概为7位左右

- 直接float表示没有小数部分

- float(M,D):总共M位，小数D位

double：双精度，占用8个字节存储数据，精度范围大概为15位左右

* 浮点数据：整形部分是不能超出长度的，小数部分可以超出长度(系统自动四舍五入)

* 例子：

create table tb_stu(

f1 float,      -- 小数可以取两位

f2 float(10,2),

f3 float(6,2)

)charset utf8;

insert into tb_stu values(1000.10,1000.10,1000.10);

insert into tb_stu values(3e38,99999999.99,9999.99);-- 后面两个数字是对应的最大值

存储结果 3e+038  100000000.00  9999.99

因为float精度大概为7位，99999999.99会进位变成100000000.00

insert into tb_stu values(1000.155111,20.104,10.205);

存储结果 1000.16  20.10   10.21   -- 小数部分可以超出长度(系统自动四舍五入)

4.列类型--定点型decimal   

* 浮点数如果进位导致长度溢出没有问题，定点型数据不允许

drop table tb_stu;

create table tb_stu(

f float(10,2),

d decimal(10,2)

)charset utf8;

insert into tb_stu values(123456.12,12345678.12);

insert into tb_stu values(1234567.12,12345678.12);

insert into tb_stu values(12345678.12,12345678.12);

insert into tb_stu values(99999999.99,99999999.99);

insert into tb_stu values(99999999.999,99999999.999);

select * from tb_stu;

f                                   d

123456.12    精度没丢失          12345678.12

1234567.13    丢失精度            12345678.12

12345678.00    丢失精度            12345678.12

100000000.00    丢失精度        99999999.99

100000000.00    这里有警告        99999999.99  这里有警告

执行insert into tb_stu values(99999999.999,99999999.999);时有警告，

查看警告 show warnings;    ==> Out of range value for column 'd' at row 1

5.列类型--时间日期类型   

* datetime:时间日期，格式yyyy-MM-dd HH:mm:ss,表示的范围从1000到9999年；有0值，0000-00-00 00:00:00

* date：日期，就是datatime中的日期部分

* time：时间(段)，指定的某个区间之间；

可以是负数 -11:11:11   ,  -2 11:11:11==>-59:11:11

* timestamp:时间戳，从1970年开始的yyyy-MM-dd HH:mm:ss；

特点：

1)不能为null

2)只要当前所在的记录被更新，该时间戳一定会自动更新

* year:年份,两种格式 year(2) year(4),直接写year默认是year(4)

* timestamp:只要当前所在的记录被更新，该时间戳一定会自动更新

注意：不能设置default null

drop table tb_stu;

create table tb_stu(

state tinyint,

d1 datetime,

d2 timestamp

)charset utf8;

insert into tb_stu values(false,'2018-9-15 14:37:34','2018-9-15 14:37:50');

update tb_stu set state = true where state = false;   时间戳变成2018-09-15 14:40:05

6.列类型--字符串char和varchar   

* SQL中字符串有6类：char,varchar,text,blob,enum,set

* 定长字符串  char(L):L最大为255；磁盘定义结构的时候就已经确定了最终数据的存储长度

例子：

drop table tb_stu;

create table tb_stu(

id char(4)

)charset utf8;

insert into tb_stu values('1');

insert into tb_stu values('12');

insert into tb_stu values('123');

insert into tb_stu values('1234');

insert into tb_stu values('12345');    警告：Data truncated for column 'id' at row 1

select * from tb_stu;

结果：

id

1

12

123

1234

1234

* 变长字符串 varchar(L)，理论L最大为65536；在分配空间的时候，按照最大的空间分配，但是实际上最终用了多少，

是根据具体存储的数据来确定；要用1或2个字节来存储实际长度;

实际上，如果字符长度超过255，既不用char，也不用varchar，而是使用text;

varchar(10):存了10个汉字，utf8环境下，10*3+1=31(bytes)

存了3个汉字，utf8环境下，3*3+1=10(bytes)

* 如何选择定长或者变长字符串呢？

- 定长的磁盘空间比较浪费，但是效率高；如果数据基本上长度都一样，就使用定长，如

身份证号码，手机号码

- 变长的磁盘空间比较节省，但是效率低；如果数据不能确定长度，就使用变长，如姓名，地址等。

7.列类型--文本字符串   

* 如果数据量非常大，通常所超出255个字符就会使用文本字符串

* text：存储文字                                                      clob:character large object

* blob：存储二进制数据(通常也不用，二进制数据实际上都是存储路径)      blob:binary large object

8.列类型--枚举字符串enum(单选)   

* 枚举enum：事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个

* 例子：

create table tb_stu(

gender enum('男','女','保密')    -- 实际存储的是1,2,3

)charset utf8;

insert into tb_stu values('男'),(null);

insert into tb_stu values(3);===>等价于insert into tb_stu values('保密');

9.列类型--集合字符串set(多选)   

* set与enum类似，实际存储的是数值，而不是字符串

* 例子：

create table my_set(

hobby set('篮球','足球','羽毛球','乒乓球')

)charset utf8;

insert into my_set values('篮球,足球,乒乓球');  -- 存储的实际值为0B1011=11

select hobby+0,hobby from my_set;

insert into my_set values(5);  -- 5的二进制位 0B0101

==>等价于insert into my_set values('篮球,羽毛球');

10.列属性--空属性   

* 列属性：null/not null, default, comment ,primary key, unique key, auto_increment

* 例子：

create table tb_stu(

name varchar(20) not null,  -- 指定该字段列属性not null

age int [default] null -- 不写null，默认就有null这个列属性

)charset utf8;

11.列属性--列描述comment   

* 列描述：comment，没有实际含义，是专门用来描述字段，会根据表创建语句保存；

用来给程序员(数据库管理员)来了解的。

* 例子：

create table teacher(

name varchar(20) not null comment '姓名',

money decimal(10,2) not null comment '工资'

)charset utf8;

show create table teacher;

12.列属性--默认值   

* 关键字default

* 例子：

create table tb_default(

name varchar(20) not null,

age tinyint unsigned default 0,

gender enum('男','女','保密') default '男'

)charset utf8;

desc tb_default; -- 查看表结构

insert into tb_default(name) values('张三');

insert into tb_default values('李四',default,default);

select * from tb_default;

---