数据库学习笔记（二）

目录

数据类型

数值型数据（整数型、小数型）

浮点型

定点型（decimal）

时间日期类型

字符串类型

MySQL记录长度

列属性

主键

索引

---

数据类型

数据类型就是对数据进行统一分类，从系统的角度出发就是为了能够使用统一的方式进行管理，能够更好的利用有限的空间

SQL中将数据类型分为三大类：数值类型、字符串类型、时间日期类型

数值型数据（整数型、小数型）

整数型

在SQL中，为了更好的利用存储空间，系统又将整形细分为五种子类型，其中tinyint和int最常用

名称	所占字节	存储范围
tinyint	1	-2^7~2^8
smallint	2	-2^15~2^16
mediumint	3	-2^23~2^24
int	4	-2^31~2^32
bigint	8	-2^63~2^64

SQL中的数据默认是有符号的，如果需要使用无符号的数据，需要使用unsigned将其指定为无符号数据

字段的数据类型后面的数值表示数据的显示宽度：数据最终的显示位数（-123的位数为4，符号也是一位数）

显示宽度并不能改变数据的大小，也无法限制超出显示宽度的数据插入表格（255可以插入数据类型为tinyint(1)的字段）

显示宽度的意义在于当数据的宽度不够显示宽度的时候，系统通过在数据前添加0的方法将数据的宽度补为显示宽度，这种操作方法被称为zerofill，其意义在于保证数据格式，使用zerofill之后数据自动变为unsigned

小数型

浮点型：带有小数点的数据以及范围超出整数型的数据

由于浮点型数据会出现精度丢失的情况，因此在SQL中，系统又将小数型数据分为浮点型数据和定点型数据

定点型数据不会出现精度丢失的情况

浮点型

浮点型数据是一种精度型数据，因为在超出指定范围后，会丢失精度（自动四舍五入）

浮点型分为两种：float和double

float：采用4个字节存储数据

double：采用8个字节存储数据

在创建浮点数字段时，直接使用float表示没有小数部分，而使用float(M，D)；    #M代表总长度，D代表小数部分长度

在插入数据时，可以使用科学计数法

在插入浮点型数据时，整数部分不能超出长度的，但是小数部分可以超出范围，但是系统会自动四舍五入进行存储

如果是因为系统四舍五入使得数据进位导致整数部分超出精度，系统也是允许的

定点型（decimal）

绝对保证数据的整数部分不会被四舍五入，保证数据的精度，但是数据的小数部分可能丢失精度

定点型数据在插入数据时，整数部分一定不能超出长度，小数部分可以超出

在浮点型数据中，四舍五入导致的数据溢出则不被支持

时间日期类型

类型	显示格式	存储空间	零值
DATETIME	YYYY-MM-DD HH:MM:SS	8	0000-00-00 00:00:00
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4	0000-00-00 00:00:00
DATE	YYYY-MM-DD	3	0000-00-00
TIME	HH:MM:SS	3	00:00:00
YEAR	YYYY	1	0000

time可以为负数，year可以使用两位存储也可以使用四位存储

timestamp：只要当前记录被更新，timestamp一定会被自动更新为当前时间

字符串类型

在SQL中，将字符串分为六种：char、varchar、text、blob、enum、set

定长字符串（char）：在定义数据结构时就指定了字符串的长度

char（L）：L为length，即字符串的长度，单位为字符，最大长度为255

变长字符串（varchar）：在定义数据的时候提供最长字符串的长度，具体使用的长度是系统根据实际的使用情况确定的

varchar（L）：L为length，即字符串的长度，单位为字符，最大长度65536

定长字符串比较浪费磁盘空间，但效率较高；变长字符串虽然节省磁盘空间，但是效率较低

文本字符串

在数据长度超过255个字符时就会使用文本字符串来存储数据

文本字符串被分为两类：text、blob

text用来存储文字，blob用来存储二进制（不常用），因为在存储二进制文件时，通常存储其文件路径而非文件本身

枚举字符串

枚举enum：事先将所有可能出现的结果列举出来，最后存储的信息必须是这些类型中的一个

例如：enum sex('男','女')；

枚举的作用：一、规范数据格式，将数据限定在指定数据中的一个；

二、节省存储空间，因为枚举中实际存储的是数值，而非字符串，按字符串顺序，从1开始编号

枚举的原理：在数据库进行数据定义的时候，系统会自动创建一个数字与枚举元素的对应关系，然后在数据插入的时候将字符串自动转化为数字进行存储，而在提取字符串时，将存储的数字转化为字符串进行显示

集合字符串

与枚举字符串相似，集合字符串存储的数据也是数字，不同的是集合字符串是多选而非单选

例如：set(元素列表);

在集合中，每个元素代表一个二进制位，被选中则该位位1，否则为0，最后将数据顺序反转（100=>001）

集合可以规范数据以及节省空间

MySQL记录长度

MySQL规定一条记录的最长长度为65535个字节，varchar永远不能达到理论最大值65535

MySQL记录中，如果有任意一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL，若向将占用的一个字节释放，则需要将所有的字段指定为不允许为空

MySQL的记录中，如果是变长的数据类型，需要额外的空间来存储记录的长度，例如varchar占用1~2个，text占用10个

列属性

在数据表中，约束字段的是数据类型，但是数据类型对字段的约束是比较单一的，需要一些额外的约束，来保证数据的合法性

列属性包括NULL/NOT NULL、default、primary key、unique key、comment......

空属性

NULL和NOT NULL

虽然数据库中的基本字段都为空，但是在实际开发时，尽可能的将所有的数据都不为空；因为空数据本身是没有意义的，其次其不能参加运算（1+NULL == NULL）

列描述

comment：没有实际意义，其作用是用来描述字段，会根据表的创建语句保存

create table 表名（
name varchar(10) not null comment '姓名',
money int not null comment '工资'
）；

可以使用show create table 表名可以查看comment

默认值

使用方法
 

create table teacher(
name varchar(10) default '张三',
sex varchar(2) default '男'
);

在插入数据时，不给设置了默认值的字段赋值即可使得默认值生效

可以使用default关键字指代被插入的数据值

insert into 表名 values('字段1',default);

主键

唯一键和和自增长，设置为主键属性的字段默认不能为空

增加主键

增加主键的方式分为三种，如下所示：

方案一：在创建数据表时在字段后添加一个primary key关键字将字段指定为主键来创建主键

优点：非常直接    缺点：只能有一个字段作为主键

方案二：在创建数据表时在所有字段后使用primary key（关键字列表）来创建主键（如果有多个字段作为主键，可以是复合主键）

方案三：在数据表创建完成之后， 通过修改列属性和直接追加的方式额外追加主键

例如：alter table 表名 add primary key(字段/字段列表)；

追加主键的前提是字段中的数据是不重复的

主键约束

主键对应的字段中的数据是不允许重复的，如果重复，数据操作失败（增加、修改）

复合主键中的单个字段数据是可以重复的

主键更新&删除主键

因为主键是没有办法修改的，因此需要先将主键删除，之后才能进行主键的更新操作

alter table 表名 drop pirmary key；        #删除主键

主键的分类

主键分为逻辑主键、业务主键、复合主键

逻辑主键：在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键，又称为“代理主键”

业务主键：在数据库表中把具有业务逻辑含义的字段作为主键，称为“自然主键(Natural Key)”

复合主键：通过两个或者多个字段的组合作为主键

在实际的操作中，很少使用真实的业务数据作为主键，即很少使用业务主键，更多的是使用逻辑主键

使用逻辑主键的原因：

一、业务主键一旦改变则系统中关联该主键的部分的修改将会是不可避免的，并且引用越多改动越大。而使用逻辑主键则只需要修改相应的业务主键相关的业务逻辑即可，减少了因为业务主键相关改变对系统的影响范围

二、业务主键过大，不利于传输、处理和存储

自增长

通常跟主键搭配，具有以下特点：

一、任何一个字段被赋予自增长属性必须是一个索引

二、自增长属性字段数据类型必须为整形数字

三、数据表中只能有一个自增长字段

自增长的起始值为1，且步长为1；当自增长字段被指定了数据时，自增长失效但是下次自增长会选择当前数据中的最大值作为自增长的起始值

可以通过查看数据表的创建语句查看下次自增长的值

修改自增长

自增长如果是涉及到字段的更改，需要先将自增长字段删除再进行设置

如果想要设置自增长的值，只能修改比当前最大值大，不能修改为比当前最大值小（无错误提示但不生效）

alter table 表名 auto_ increment = x；    #x必须大于当前自增长字段中的最大值

可以对数据库的自增长起始值和步长进行修改，但是一旦修改，整个数据库内生效，且修改为会话级

会话级：修改只对当次连接生效

删除自增长

由于自增长是字段的一个属性，想要将自增长属性删除只需要在其字段属性更改时不在赋予其自增长属性即可

alter table 表名 modify 字段名 相关属性;

唯一键

数据表中往往有多个字段需要设置唯一性，但是主键只能有一个，因此，为了满足多个字段的唯一性要求，系统引入了唯一键（unique key），以满足字段的唯一性约束

唯一键允许字段为空，因为空字段不参与唯一性比较

新增唯一键有三种方式：

一、在创建表时在字段后添加unique/uinque key关键字

二、在创建表时在所有字段后添加unique key（字段列表）

三、在创建表之后添加唯一键

alter table 表名 add uinque key（字段名）；

唯一键约束

唯一键和主键类似，唯一的区别就是其允许字段为空，且允许多个为空（NULL不参与唯一性比较）

删除唯一键

alter table 表名 drop index 索引名；        #索引名默认为字段名

索引

几乎所有的索引都是建立在字段之上

索引：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

建立索引的同时会产生索引文件，会增加磁盘空间的消耗

MySQL中的索引分类：

• 主键索引：primary key
• 唯一键索引：uinque key
• 全文索引：fulltext index（针对文章的关键字进行索引）
• 普通索引：index

全文索引索引的最大问题在于关键字的确认（英文较简单----有空格，但是中文很难）

create index index_name on table_name (column_list)