【MYSQL】从零基础到快速入门(2)

一.MYSQL的数据类型

1.数据类型分类

2.数值类型

2.1 BIT的使用

bit[(M)]位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

create table p1( id int, tel bit(8));
insert into p1 values(10,10);
select * from p1;

该sql语句运行结果如下：

tel字段的值并没有显示，bit字段在显示时，是按照ASCII码对应的值显示。下图，当插入65时，对应的字符为A。

如果我们有这样的值，只存放0或1，只存在两种状态，这时可以定义bit(1)。这样可以节省空间。

create table p2(gender bit(1));
insert into p2 values(0);
insert into p2 values(1);
insert into p2 values(2); -- 不能插入2，因为只有1位，插入2会越界

2.2 整型类型的使用

对于上边的几个类型，我们应该注意的是它们所能使用的范围，注意越界问题。

crate table p3(id tinyint); -128-127
insert into p3 values(1);
insert into p3 values(128);-- 会报错，越界插入

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。 可以通过UNSIGNED来说明某个字段是无符号的。

create table p4(num tinyint unsigned); -- 0-255
insert into p4 values(-1);-- 越界插入
insert into p4 values(255);

其他的整型类型和TINYINT类型的使用完全一样，只要使用时注意范围即可。

2.3 UNSINGED的使用

int的表示范围是-2147483648 ~ 2147483647，int unsigned的范围是0 ~ 4294967295。所以我们在很多时候需要使用unsigned来表示非负数，比如年龄等。然而在实际使用中，unsigned可能会带来一些负面的影响。

create table p5(a int unsigned, b int unsigned);
insert into p5 values(1,2);
select a - b from p5; 

– 预料的应该是-1，但其实不然，-1减去2是正的最大值，-1(FFFFFFFF)和4294967295(FFFFFFFF)的补码相同，将这个数按照无符号数输出，结果是4294967295而不是-1

要获得正确的结果，我们想要设置参数对SQL_ MODE。

 set sql_mode='NO_UNSIGNED_SUBTRACTION';

这样结果才是我们想要的结果。

实际使用中，尽量不使用unsigned，因为可能带来一些意想不到的效果。另外，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

2.4 小数类型的使用

2.4.1 float的使用

float[(m, d)][unsigned]：m指定显示长度，d指定小数位数，占用空间4个字节。

float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

create table p6(id int, sal float(4,2));
insert into p6 values(1,-99.99);
insert into p6 values(1,-99.991); -- 先进行四舍五入，然后在插入
insert into p6 values(1,99.991);
insert into p6 values(1,99.999); -- 报警告，但依然会舍弃超出部分

运行结果：· show warnings· 可以打印出所有的警告信息。

如果定义的是float(4,2) unsigned这时，因为把它指定为无符号的数，范围是 0 ~ 99.99。

create table p7(id int, sal float(4,2) unsigned);
insert into p7 values(1,-0.1);-- 报警告
insert into p7 values(1,-0); -- yes
insert into p7 values(1,99.99); -- yes

打印出警告信息：

2.4.2 decimal的使用
decimal(m, d) [unsigned]：定点数m指定长度，d表示小数点的位数

decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0。 如果m被省略，默认 是10。

-- float和decimal很相似，但是他们有区别，表示的精度不同,float表示的精度大约是7位
decimal(5,2) 表示的范围是 -999.99 ~ 999.99 
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
create table p8(sal1 float(10,8),sal2 decimal(10,8))
insert into t8 values(12.12345678,11.12345678);

下图为插入记录的结果：由此结果可以看出decimal的精度要比float的精度高，如果要存储更高精度的数，推荐使用decimal。

3. 字符串

3.1 char和varchar

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，大长度值可以为255

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节
注：L是表示字符的个数，而不是字节的个数。

测试char
-- char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个。最多只能是255
create table p8(id int, name char(2));
insert into p8 values(1,'ab'); -- ab
insert into p8 values(1,'张三'); -- 张三
-- 超出最大长度，则会建表失败
create table tt10(id int ,name char(256)); 

-- 测试varchar
create table p9(id int, name varchar(6)) -- 表示可以存放6个字符
insert into p9 values(100, '我爱你，中国'); -- 插入成功

3.2 varchar的len
varchar的len到底是多长呢？varchar(L)，L代表的是字符的个数，但是总的最大字节数是65535。varchar的len其实是与 编码有关的。

• varchar长度可以指定为0到65535之间的，但是有1 - 3个字节用于记录数据大小，所以说有效字节数是65532。
• 当表的编码是utf8时，varchar(n)。n大值是65532/3=21844。(因为utf8中，一个汉字占用3个字节)
• 如果编码是gbk，varchar(n)。n大是65532/2=32766（因为gbk中，一个汉字占用2字节）。
  当我们以utf8编码创建一个varchar(21845)的表会失败，但是创建varchar(21844)的表就会创建成功。

注意：我们在创建表的时候并不是只创建有一个varchar字段，还可能存在其他字段，如果存在其他字段，那么varchar的最大长度并不是21844，而是要减去其他类型所占的长度，因为mysql规定，表中一行的记录不能超过65535。

上边的例子中，varchar的最大长度应该是21844 - 4(int) = 21840，所以第一次建表报错，第二次建表成功。

在一些使用场景下，应该如何选择char和varchar:

• 如果数据确定长度都一样，就使用定长，比如身份证，手机号，md5加密的密码。(定长的磁盘空间比较浪费，但是效率高)
• 如果数据长度有变化,就使用变长,比如名字，地址，但是你要保证长的能存的进去。(变长的磁盘空间比较节省，但是效率低)

3.2 日期和时间类型

MYSQL中常用的日期和时间类型有：

datatime：日期时间格式为yyyy-mm-dd HH:MM:SS，表示范围从1000-9999，占用8个字节
date：日期格式为yyyy-mm-dd，占用3个字节
timestamp：时间戳，，从1970年开始的 yyyy-mm-dd HH:MM:SS格式和datetime完全一致，占用4字节
create table birthday(t1 date, t2 datetime, t3 timestamp)
-- 添加数据时，时间戳自动补上当前时间 
-- 更新数据，时间戳会更新成当前时间 
insert into birthday(t1,t2) values('1997-3-6','1997-3-6 12:00:00')

当更新表中的数据时，时间戳会自动更新为当前的时间：

update birthday set t2 = '2008-3-6';

3.3 枚举和set类型

2.6.1 枚举类型
枚举，其实就是“单选”类型，对应界面或表单中的“单选项”的数据值。该设定只是提供了若干个选项的值，最终在一个单元格中，实际只存储了其中一个值；而且，处于效率考虑，这些值实际存储的是“数字”，因为这些选项，每个选项值，一次对应如下数字：1,2,3,…多65535个。枚举类型其实和C中的枚举类型是一个概念。

enum('选项1','选项2','选项3',...); 

当我们添加枚举值时，也可以直接添加编号。

2.6.2 set类型
set类型正好和enum相对。set就是“多选”类型，对应于界面或表单的“多选项的数据值。该设定只是提供了若干个选项的值，最终在一个单元格中，可存储了任何其中的多个值。基于效率考虑， 这些实际存储的是“数字”，因为这些选项，每个选项值，一次对应如下数字：1,2,4,8,16,32，....最多64个；[偶数]。

set('选项值1','选项值2','选项值3', ...); 

当我们添加set类型的值时，也可以直接添加编号。

2.6.3 set和enum例子
有一个调查表votes，需要调查人的喜好，比如登山，游泳，篮球，武术(多选)。 性别为男和女(单选)。

-- 建表
create table votes(
	name varchar(32),
	hobby set('登山','游泳','篮球','武术'),
	gender enum('男','女')
)charset=utf8;
-- 插入几条记录
insert into votes values('张三', '登山,武术', '男'); 
-- 枚举类型可以直接插入编号
insert into votes values('李四','登山,武术',2);
insert into votes values('王五','游泳',2);

select * from votes where hobby='登山'; 

以下为插入结果：

找出所有爱好有登山的人

-- 这样做查不出来
select * from votes where hobby='登山'; 
-- find_in_set(sub,str_list);  如果sub在str_list中，则返回下标，如果不在，返回0  str_list用逗号分隔的字符串。 

-- 这样做ok
select * from votes where find_in_set('登山', hobby); 

find_in_set的用法如下：

二.表的约束

真正约束表中字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是id，要求是唯一的。

1.空属性

1.1 空属性概念

空属性存在两个值：null(默认的)和not null(不为空)。 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

select null; -- null
select 1+null; -- 空值没法参加运算，运算结果依然为空

下边为运算结果：

1.2 空属性约束的应用

假设要创建一个班级表，该表包含班级名和班级所在的教室。如果班级没有名字，你知道你在哪个班级；如果教室名字可以为空，就不知道在哪上课。

create table class(
	class_name varchar(20) not null,
	class_room varchar(20) not null
)charset=utf8;

使用desc class查看表的结构：

当我们在插入数据，没有给出class_name或者class_room时，就会报错，因为我们已经设置约束该字段不能为空：

insert into class values('1603','203'); -- ok
insert into class values('1604'); -- 报错

下边为运行结果：

2.默认值

默认值是指某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。(可以理解为C++中的缺省参数)

-- 创建一个带有默认值约束的学生表
create table stu(
	name varchar(30) not null,
	age tinyint unsigned default 0,
	gender char(2) default '男'
)charset=utf8;

使用desc stu查看表的结构：

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值。

insert into stu values('张三',17,'女');
-- 年龄和性别字段使用默认值
insert into stu values('李四');

以下为运行结果：

注:set和enum类型字段不能设置默认值。

3.列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员来进行理解。可以理解为C语言中的注释信息

创建一个person表，给每一个字段加上列描述：

create table person(
	name varchar(20) not null comment '姓名',
	age tinyint unsigned default 0 comment '年龄',
	gender char(2) default '男' comment '性别'
)charset=utf8;

使用desc person查看表结构，可以发现看不到注释信息：

使用show create table person\G (加上\G可以以一种简单的形式打印信息)查看建表的信息，才可以查看到注释信息：

4.zerofill(0填充)

创建一个带有整型字段的表：

create table z1(
	a int(10) unsigned zerofill default NULL,
	b int unsigned default 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

整型是代表的4个字节，但是后边的10代表的是什么？其实int(10)如果没有设置zerofill约束这个值没有任何意义。但是如果设置zerofill约束，当你要插入的数位数没有达到10位，其他位数就会被0填充。

插入一条数据查看结果：

insert into z1 values(1,2);

下边为插入结果：

a的值由原来的1变成0000000001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是 10），自动填充0。要注意的是，这只是后显示的结果，在MySQL中实际存储的还是1。

使用内建函数hex()将a的值转为十六进制：

由结果可以看出数据库内部存储的还是1，0000000001只是设置了zerofill属性后的一种格式化输出而已。

5.主键约束

主键primary key:用来唯一的约束该字段里面的数据，不能重复、不能为空、主键所在的列是整数类型。一张表中多只能有一个主键，创建表的时候直接在字段上指定主键。

5.1 单个字段的主键

创建一个带有主键id的表

create table z2(
	id int unsigned primary key comment '学号', -- id一个字段加主键约束
	name varchar(20) not null comment '姓名'
)charset=utf8;

使用desc z2查看该表的结构

Key中的PRI代表的是主键。

5.2 多个字段的主键(复合主键)

创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

创建一张以多个字段为主键的表

create table z3(     
	 id int unsigned comment '学号',     
	 course char(10) comment '课程代码',     
	 score tinyint unsigned default 60 comment '成绩',     
	 primary key(id, course) -- id和course为复合主键，复合主键一定是在所有字段后声明的     
)charset=utf8;

使用desc z3查看该表的结构

可以看出id和course为复合主键。

当一个表建好后，也可以追加主键约束

-- 语法：
alter table 表名 add primary key(字段列表) 
-- 创建一个没有主键的表
create table z4(
	id int,
	name varchar(20)
);
-- 增加主键约束
alter table z4 add primary key(id);

使用desc查看表的结构，可以看到主键追加完成：

5.3 主键约束

主键对应的字段中不能重复，一旦重复，操作失败。

例如：在z4中插入两个记录：

insert into z4 values(1,'a'); -- 插入成功
insert into z4 values(1,'b'); -- 插入失败，主键不唯一

下边为插入结果：

5.4 删除主键

语法：alter table 表名 drop primary key;

例如：删除表z4中的主键：

alter table z4 drop primary key;

desc查看表结构，发现主键被删除：

6.自增长

6.1 自增长概念

自增长auto_increment:·当对应的字段，没有给值，会自动的被系统触发，系统会从当前字段中已经有的值进行+1操作， 得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

6.2 自增长的特点

任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表多只能有一个自增长
例如：创建一个带有自增长约束的表，并进行插入操作：

 create table z5(     
 	id int unsigned primary key auto_increment, -- 逻辑主键    
 	name varchar(10) not null default ''     
 ); 
 -- 插入两条记录
 insert into tt21(name) values('a'); 
 insert into tt21(name) values('b'); 

使用select * from z5查看插入结果：

7.唯一键(unique)

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键。唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

创建一个带有唯一性约束的表，并插入一些数据

create table z6(
	id int unique,
	name varchar(20) not null
);
-- 插入几条记录
insert into z6(id, name) values('01', 'a'); -- 插入成功
insert into z6(id, name) values('01', 'b'); -- id不唯一
insert into z6(id, name) values(null, 'c'); -- 可以插入null  

使用select * from z6查看插入结果

注：在MYSQL中的各类语句一般使用大写，在本篇博文中，由于大写写起来比较难看懂，所以就采用小写。