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

最新推荐文章于 2024-09-22 18:03:42 发布

码农印象

最新推荐文章于 2024-09-22 18:03:42 发布

阅读量254

点赞数

分类专栏：数据库文章标签： mysql java python 数据分析数据库

本文链接：https://blog.csdn.net/qq_41035588/article/details/107872508

版权

数据库专栏收录该内容

10 篇文章 1 订阅

订阅专栏

一.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的使用

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

ﬂoat(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· 可以打印出所有的警告信息。
在这里插入图片描述

如果定义的是ﬂoat(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';
1在这里插入图片描述

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，这就是zeroﬁll属性的作用，如果宽度小于设定的宽度（这里设置的是 10），自动填充0。要注意的是，这只是后显示的结果，在MySQL中实际存储的还是1。

使用内建函数hex()将a的值转为十六进制：
在这里插入图片描述

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

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