MYSQL-DDL建表语句及数据类型

存储同样的数据,不同的列类型,所占据的空间和效率是不一样的

 

 

列类型学习

mysql三大列类型

数值型 

整型

  Tinyint/ smallint/ mediumint/int/ bigint(M) unsigned zerofill

 

整型系列所占字节与存储范围的关系.

定性: 占字节越多,存储范围越大.

下图: 是具体的数字分析

 

Tinyint 1个字节 8个位  0 - 2^8-1 ,  0-255

                -2^7 ----> +2^7-1

 

 

分析:

Smallint 2个字节 , 16位  0----2^16-1 = 65535

                -2^15 ---> +2^15-1, -32768 -> 32767

 

一般而言,设某类型 N字节

N字节 , 8N位.

0 ----> 2^8N-1

 

-2^(8N-1)  ---> +2^(8N-1) -1;

 

对于int型 : 占的字节越多,存储的范围也越大.

整型系统的可选参数 : XXint(M)  unsigned zerofill

例: age tinyint(4) unsigned ,或者 stunum smallint(6) zerofill;

Unsigned: 代表此列为无符号类型, 会影响到列的存储范围. (范围从0开始)

(不加unsinged, 则该列默认是有符号类型,范围从负数开始)

 

 

Zerofill: 代表0填充, 即: 如果该数字不足参数M位, 则自动补0, 补够M位.

1: 如果没有zerofill属性, 单独的参数M,没有任何意义.

2:如果设置某列为zerofill,则该列已经默认为 unsigned,无符号类型.

 

小数型

Float(M,D),decimal(M,D)

 M叫"精度" ---->代表"总位数",而D是"标度",代表小数位.(小数右边的位数)

 

浮点数占多大的空间呢

答：　float　能存10＾３８　，10^-38

如果M<=24, 点4个字节,否则占8字节

 

用来表示数据中的小数,除了float---浮点.

还有一种叫定点decimal,定点是把整数部分, 和小数部分,分开存储的.

比float精确,他的长度是变化的.

 

空间上的区别:

Float(M,D), M<=24, 4个字节, 24 <M <=53, 8个字节

Decimal () ,变长字节.

 

区别: decimal比float精度更高, 适合存储货币等要求精确的数字,

见下例:

字符串型

Char(M)

Varchar(M)

Text 文本类型

 

日期时间类型

Date 日期

Time 时间

Datetime 时间时间类型

Year 年类型

 

 

Mysql 字符串类型

Char 定长类型

Char(M) , M 代表宽度, 0<=M<=255之间

例:Char(10) ,则能输入10个字符.

 

Varchar 变长类型

Varchar(M), M代表宽度, 0<=M<=65535(以ascii字符为例,utf822000左右)

 

类型

类型

宽度

可存字符

实存字符(i<=M)

实占空间

利用率

Char

M

M

i

M

i/M <= 100%

Varchar

M

M

i

i字符+(1-2)字节

i/(i+1-2) < 100%

 

0000000000

00\0\0\0\0\0 (char型,如果不够M个字符,内部用空格补齐,取出时再把右侧空格删掉) 

注:这意味着,如果右侧本身有空格,将会丢失.

 

Varchar(10)

[2]张三

[3]二麻子

[4]司马相如

 

Char(8)

00000000

'Hello  '

'hello '

Char(M)如何占据M个字符宽度?

答: 如果实际存储内容不足M个,则后面加空格补齐.

取出来的时候, 再把后面的空格去掉.(所以,如果内容最后有空格,将会被清除).

 

 

速度上: 定长速度快些

 

注意: char(M),varchar(M)限制的是字符,不是字节.

即 char(2) charset utf8, 能存2个utf8字符. 比如'中国'char与varchar型的选择原则:

1:空间利用效率, 四字成语表, char(4),

个人简介,微博140字, varchar(140)

2:速度

用户名: char

Char 与 varchar相关实验

 

Text : 文本类型,可以存比较大的文本段,搜索速度稍慢.

因此,如果不是特别大的内容,建议用char,varchar来代替.

Text 不用加默认值 (加了也没用).

 

Blob,是二进制类型,用来存储图像,音频等二进制信息.

意义: 2进制,0-255都有可能出现.

Blob在于防止因为字符集的问题,导致信息丢失.

比如:一张图片中有0xFF字节, 这个在ascii字符集认为非法,在入库的时候,被过滤了.

日期时间类型

Year 年(1字节)   95/1995,  [1901-2155],

在insert时,可以简写年的后2位,但是不推荐这样.

[00-69] +2000

[70-99] + 1900,  

即: 填2位,表示 1970 - 2069

 

Date 日期 1998-12-31

范围: 1000/01/01 ,9999/12/31

 

Time 时间 13:56:23

范围: -838:59:59 -->838:59:59

datetime 时期时间  1998-12-31 13:56:23

范围: 1000/01//01 00:00:00 ---> 9999:12:31 23:59:59

 

 

时间戳:

是1970-01-01 00:00:00 到当前的秒数.

一般存注册时间,商品发布时间等,并不是用datetime存储,而是用时间戳.

因为datetime虽然直观,但计算不便.

 

 

建表案例,某高端白领私密社交网站

 

主键id

用户名

性别

体重(KG)

生日

工资

上次登陆

个人简介

Int

Username

gender

weight

birth

salary

Lastlogin

intro

 

列名称

列类型

默认值

是否主键

Id

Int unsigned

 

PRI

Username

Varchar(20)

''

 

gender

Char(1) /tinyint

 

 

weight

Tinyint unsigned

 

 

Birth

Date

 

 

Salary

Decimal(8,2)

 

 

lastlogin

Datetime

 

 

intro

Varchar(1500)

 

 

 

这张表不够好,可以优化

分析:这张表除了username/intro列之外,每一列都是定长的.

我们不妨让其所有列,都定长,可以极大提高查询速度.

列名称

列类型

默认值

是否主键

Id

Int unsigned

 

PRI

Username

char(20)

''

 

gender

Char(1) /tinyint

 

 

weight

Tinyint unsigned

 

 

Birth

Date

 

 

Salary

Decimal(8,2)

 

 

lastlogin

Int unsigned

 

 

 

Username char(20) 是会造成空间的浪费,但是提高的速度,值.

Intro char(1500) 却浪费的太多了,另一方面,人的简介,一旦注册完,改的频率也并不高.

我们可以把 intro列单独拿出来,另放一张表里.

 

列名称

列类型

默认值

是否主键

Id

Int unsigned

 

PRI

Username

char(20)

''

 

intro

Varchar(1500)

 

 

 

在开发中,会员的信息优化往往是 把频繁用到的信息,优先考虑效率,存储到一张表中.

不常用的信息和比较占据空间的信息,优先考虑空间占用,存储到辅表中.
建表语法

所谓建表就是一个声明列的过程.

create table表名 (

列名1 列类型1 列1参数,

列名2 列类型2 列2参数,

....

...

列名n 列类型n 列n参数

)engine myisam/innodb/bdb charset utf8/gbk/latin1...

 

 

修改表的语法

一张表,创建完毕,有了N列.

之后还有可能要增加或删除或修改列

 

Alter table 表名 add列名称 列类型 列参数;[加的列在表的最后]

例:alter table m1 add birth date not null default '0000-00-00';

Alter table 表名 add列名称 列类型 列参数 after 某列[把新列加在某列后]

例:alter table m1 add gender char(1) not null default '' after username;

 

Alter table 表名 add列名称 列类型 列参数 first[把新列加在最前面]

例: alter table m1 add pid int not null default 0 first;

 

 

删除列:

Alter table 表名 drop 列名

 

 

修改列类型:

Alter table表名 modify列名新类型  新参数

例:alter table m1 modify gender char(4) not null default '';

 

修改列名及列类型

Alter table 表名 change旧列名 新列名 新类型 新参数

例:alter table m1 change id uid int unsigned;

 

作业: 让我们建一个电子商城, 如何来设计商品表.

再把商城表的字段,一个个删掉,再一个个加上.

并穿插改列操作.

 

??如果列类型改变了,导致数据存不下怎么办?

比如,int 改成smallint列.  如果不匹配,数据将会丢失,或者在mysql的strict_mode下,修改不了.

为什么建表时,加not null default '' / default 0

答:不想让表中出现null值.

 

为什么不想要的null的值

答:

不好比较,null是一种类型,比较时,只能用专门的is null 和 is not null来比较.

碰到运算符,一律返回null

效率不高,影响提高索引效果.

 

因此,我们往往,在建表时 not null default ''/0