MySQL学习笔记03-创建表_数据类型_修改表结构

1,570次阅读
一条评论

前言时刻:今天的学习了 MySQL 的数据类型以及创建表,不得不说里面的坑真多,还好老师讲的细致,都搞明白了。

写作环境:

MacOS、MySQL8.0

写SQL工具:Navicat15.0和电脑自带终端

写作软件:Typora

作者:西园公子:www.zwjjiaozhu.top

1、创建表

创建表的同时也是需要指定表的编码方式排序规则存储引擎。如果不指定这几项则默认是和数据库的这几项值一样,例如,我们创建了一个数据库 a 并指定 utf8 编码和 utf8_bin 的排序规则,则创建的表 t 若不指定则默认是 utf8 和 utf8_bin 。关于引擎这一块,后面再补。

CREATE TABLE t1 (`name` CHAR(10),age INT, hobby VARCHAR(10)
) CHARACTER SET UTF8 COLLATE utf8_bin ENGINE InnoDB;

# 插入数据
INSERT INTO t1 VALUES('zhang', 18, '看电影');
SELECT * FROM t1;

2、数据类型

MySQL 的数据类型超级重要,不然以后遇到插数据的问题,就会犹豫不定:我要插入多大的数据呀?数据库能放下吗?等等各种问题,但若是你搞懂了就有一种胸有成竹的感觉,随意增删改查。

2.1 字符串类型:

类型 存储范围 单位 特点
char(num) 存储范围:0~255个字符固定长度,注意字符是什么?大致上就是明面上一个字,比如:一个汉字,一个字母是一个字符。num最大255。 字符 存取速度快,取值时会处理去掉尾部空格
varchar(num) 存储范围:0~$2^{16}-1=65535$​​​个字节可变长度,字节需要转成字符,至于 num 最大是多少个字符,具体可看表下方的解释。 字符 可变长,取值时不去掉尾部空格
text 文本类型,存储范围:0~$2^{16}-1=65535$​​个字节(8KB),无需指定存储长度 字节 和varchar类似,
mediumtext 存储范围:0~$2^{24}-1=16777215$​​​个字节(约2MB),无需指定存储长度。 字节 text的升级版
longtext 存储范围:0~$2^{32}-1=4294967295$​​​​个字节(约511MB),无需指定存储长度。 字节 mediumtext的升级版

注:指定长度的类型,如果插入的数据超过设置大小,则字符串会被截断,取前半部分。

1)可变长度和固定长度是什么意思?

可变长度,字面意思就是用多少存多少。举个例子,你家的网的带宽大小,如果最高 10MB/s,那么你的速度可以是1MB/s、2MB/s……只要不超过10MB/s就可以。

固定长度,假设规定存储范围是 4 个字节,即使只存进去一个字母(1个字节),数据库也是按照4个字节存放该字母,不足的补空格。举个例子,假设这条带宽你是按月交钱,即使你每天都不用或者上网速度都是1~2MB/s的情况,那你到月底交钱的时候还是按照10MB 规格的宽带交钱,不会因为你平常不用就少交些钱的。

看完下面这个表,你就明白了。

CHAR(4) 需要存储 VARCHAR(4) 需要存储
'' ' ' 4字节 '' 1个字节
'ab' 'ab ' 4字节 'ab' 3个字节
'abcd' 'abcd' 4字节 'abcd' 5个字节
'abcdefgh' 'abcd' 4字节 'abcd' 5个字节

注:VARCHAR(4)中存放'abcd'为什么需要 5 个字节,abcd 占用 4 个字节 + 存储字符数 1 个字节 = 5

2)varchar 类型最大可以存放多少个字符?

MySQL 规定的 VARCHAR 的单位是字符,所以需要将字节数转成字符数,另外默认需留出1-3个字节用于存放字符数。我们知道表有很多的编码方式如:utf8、gbk等,其中 utf8 是可变长的采用 1 到 6 个字节存放一个字符,我们的汉字是一个汉字对应 3 个字节,如果不懂编码可看我之前写的文章搞懂编码问题

假设表的编码方式是utf8 ,MySQL按照一个字符对应最大三个字节来算,减去存放字符数的最大值(三个字节),最终的字符数等于$num = (65535-3)/3=21844$​​​​​。所以你在设置 VARCHAR(num) 的时候,num 最大是21844。如果是gbk方式,则是按最大两个字节对应一个字符的规则,得出$num = (65535-3)/2=32766$​​​​​​。

# 2.2、数据类型,varchar的范围是0-65535个字节,对应0~21844个字符

# 表的字符集是 utf8 编码
CREATE TABLE t2 (`name` VARCHAR(21844)) CHARACTER SET utf8; # ✅
CREATE TABLE t2 (`name` VARCHAR(21845)) CHARACTER SET utf8;  # ❌

# 表的字符集是 gbk 编码
CREATE TABLE t3 (`name` VARCHAR(32766)) CHARACTER SET gbk; # ✅
CREATE TABLE t3 (`name` VARCHAR(32767)) CHARACTER SET gbk; # ❌

3)关于 CHAR 类型的补充:

CHAR类型的范围默认就是以字符为单位,0~255个字符范围,所以超过255就会报错。

# 2.1 char类型,0到255个字符
create table t2 (`name` CHAR(255)) CHARACTER SET utf8;  # ✅
create table t2 (`name` CHAR(256)) CHARACTER SET utf8; # ❌

2.2 整数类型:

所有的整数类型都默认是有符号的,即有负数和正数,如果需要无符号,则在该关键字后面跟着unsigned即可。

类型 存储范围 单位
tinyint 占用 1 个字节($2^8-1=255$​​),存储范围:-1281~27,无符号范围:0-255
smallint 占用 2 个字节($2^{16}-1=65535$​​),存储范围:-32783~277,无符号范围:0-65535
mediumint 占用 3 个字节($2^{8*3}-1=16777215$​​),存储范围:-83886088~388607,无符号范围:0~16777215
int 占用 4 个字节($2^{8*4}=4294967296$​),存储范围:-21474836482~147483647,无符号范围:0~4294967296
bigint 占用 8 个字节($2^{8*8}=4294967296$​),存储范围:-21474836482~147483647,无符号范围:0~4294967296
CREATE TABLE t5 (`name` VARCHAR(10), age INT, job VARCHAR(20)) CHARACTER SET utf8 COLLATE utf8_bin;

# 测试1
# 修改 age的属性为tinyint类型(1个字节)
ALTER TABLE t5 MODIFY age TINYINT;
# 插入数据
INSERT INTO t5 (age, job) VALUES(127, '写文章'); # ✅
INSERT INTO t5 (age, job) VALUES(128, '写文章'); # ❌

# 无符号 int 类型
CREATE TABLE t6 ( age INT UNSIGNED) CHARACTER SET utf8 COLLATE utf8_bin;

其他就不做演示了,都是一样的原理。

2.3 浮点数类型

类型 存储范围 单位
float 单精度,占用 4 个字节,存储范围:有符号:-3.402823466E+38~-1.175494351E-38,无符号范围:0 和 -1.175494351E-38~-3.402823466E+38
double 双精度,占用 8 个字节,存储范围:-1.7976931348623157E+308~-2.2250738585072014E-308,无符号范围:0 和 -2.2250738585072014E-308~-1.7976931348623157E+308
decimal(m, n) 占用 m+2 个字节,m是指定长度,n是精度,就是小数点后有 n 位。

注:不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。

2.4 浮点数类型

类型 存储范围 单位
blob 存储范围:0~$2^{16}-1=65535$​字节 字节
longblob 存储范围:0~$2^{32}-1=4294967295$​字节 字节

2.5 日期类型

类型 范围 格式
date 占用 3 个字节,1000-01-01/9999-12-31 YYYY-MM-DD
time 占用 3 个字节, '-838:59:59'/'838:59:59' HH:MM:SS
year 占用 1 个字节,1901/2155 YYYY
datetime 占用 8 个字节,支持存储 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
timestamp 占用 4 个字节,支持存储 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
# 创建一个表 t10
CREATE TABLE t10 (`name` VARCHAR(12), m_date DATE,m_time TIME, m_datetime DATETIME, m_stamp TIMESTAMP);
# 插入两条数据
INSERT INTO t10 VALUES('Z', '2020-11-22', '16:15:32', '2020-11-22 16:15:32', NOW());
INSERT INTO t10 VALUES('W', CURRENT_DATE, CURRENT_TIME, NOW(), CURRENT_TIMESTAMP());

# 查询表数据
mysql> SELECT * FROM t10;
+------+------------+----------+---------------------+---------------------+
| name | m_date     | m_time   | m_datetime          | m_stamp             |
+------+------------+----------+---------------------+---------------------+
| Z    | 2020-11-22 | 16:15:32 | 2020-11-22 16:15:32 | 2021-08-16 15:43:24 |
| W    | 2021-08-16 | 16:26:59 | 2021-08-16 16:26:59 | 2021-08-16 16:26:59 |
+------+------------+----------+---------------------+---------------------+

3、 修改表结构

使用DESC table_name;命令,可展示出表的内部结构,后面修改完表结构后可以用此命令查看表结构。

DESC `table_name`;   # 使用desc展示表的结构
# 结果
mysql> DESC t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| hobby | varchar(30) | YES  |     | 干饭    |       |
+-------+-------------+------+-----+---------+-------+

1)修改表名:

rename table t1 to t2;

RENAME TABLE `table_name` TO table_new_name;

2)追加列:

使用 add 命令

# 2.1 追加列
ALTER TABLE `table_name` ADD (hobby VARCHAR(30) DEFAULT '干饭');

3)修改列属性:

使用 modify 命令

ALTER TABLE `table_name` MODIFY job VARCHAR(15);

4)删除列:

使用 drop 命令

# 2.3 删除列
ALTER TABLE `table_name` DROP hobby;

可以一次性删除多个列,用逗号分隔,组合不同的命令。同理也可以一次性添加多个列等,

ALTER TABLE `table_name` DROP hobby, DROP job;

5)修改列名:

使用 change 命令,替换后的列名同时也可设定新的的参数。

ALTER TABLE t5 CHANGE `column_name` `column_name2`;
ALTER TABLE t5 CHANGE `column_name` `column_name2` VARCHAR(30);

以上命令均为本地测试成功后才写的,不是瞎写的。

总结:

内容很多,多练几遍就好,重点是varcharchar的存储范围问题。好了今天就到这了,下一篇将写插入数据相关的操作,觉的有帮助的话,别忘了一键三连哈。

2
西园公子
版权声明:本站原创文章,由西园公子2021-08-02发表,共计5494字。
转载提示:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(一条评论)
载入中...
西园公子 博主
2021-08-17 19:48:18 回复

:mrgreen: