MySQL (一)数据类型与SQL语法

2024 年 4 月 18 日 星期四(已编辑)
/
59
1
这篇文章上次修改于 2024 年 7 月 18 日 星期四,可能部分内容已经不适用,如有疑问可询问作者。

MySQL (一)数据类型与SQL语法

MySQL 数据类型

VARCHAR

VARCHAR(max_length)  //5.0版本以上,varchar(20),指的是20字符

VARCHAR 允许的最大长度为 65535 个字节,这也是 MySQL 中的行大小的限制

因此如果使用UTF8的最大长度为(65535-1-2)/3

减1是因为实际存储从第2个字节开始 减2则因为要在列表长度存储实际字符长度 除3是因为utf8编码限制

例:

create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
N的最大值:(65535-1-2-4-30*3)/3

VARCHAR 截断内容

当插入的内容超过 VARCHAR 列定义的长度时,MySQL 会采用如下策略:

  • 如果超过的部分只包含空格,则多余的空格会被截断。
  • 如果超过的部分不只是空格,则给出错误提示。

CHAR

CHAR(length) //默认1 最大255  性能要比 VARCHAR 更好

写入 CHAR 列中的字符串的长度小于指定的字符长度,MySQL 会在源字符串后填充空格一直到指定的长度。当您读取 CHAR 列中的值时,MySQL 会删除后面的空格

后缀空格问题:即使原来存的是'Tom ' 取出也会变成 'Tom'

插入超过长度的值会直接返回错误

INT

类型字节数
TINYINT1
SMALLINT2
MEDIUMINT3
INT4
BIGINT8

INTINTEGER 是同义词。

  • 自增整型类型做主键,务必使用类型 BIGINT,而非 INT,后期表结构调整代价巨大;
  • MySQL 8.0 版本前,自增整型会有回溯问题,做业务开发的你一定要了解这个问题;

在删除自增为 3 的这条记录后,下一个自增值依然为 4(AUTO_INCREMENT=4),这里并没有错误,自增并不会进行回溯。但若这时数据库发生重启,那数据库启动后,表 t 的自增起始值将再次变为 3,即自增值发生回溯

  • 当达到自增整型类型的上限值时,再次自增插入,MySQL 数据库会报重复错误;
  • 不要再使用浮点类型 Float、Double,MySQL 后续版本将不再支持上述两种类型;
  • 账户余额字段,设计是用整型类型(BIGINT),而不是 DECIMAL 类型,这样性能更好,存储更紧凑

DATE

MySQL DATE 使用 yyyy-mm-dd 格式来存储日期值。如果您想以其他的日期格式显示,比如 mm-dd-yyyy,您可以使用 DATE_FORMAT 函数将日期格式化为您需要的格式。

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today      |
+------------+
| 03/25/2022 |
+------------+

DATETIME 和 TIMESTAMP

TIMESTAMP 需要 4 个字节,而 DATETIME 需要 8 个字节。TIMESTAMPDATETIME 二者都需要额外字节存储小数秒。

TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC2038-01-19 03:14:07 UTC 。如果要存储超过 2038 年的时间值,则应使用 DATETIME 代替 TIMESTAMP

MySQL TIMESTAMP 以 UTC 值存储(会从当前时区转换到UTC)。但是,MySQL 将 DATETIME 值按原样存储,没有时区。

YEAR

YEAR数据类型占用 1 个字节,YEAR值的范围为从19012155, 还有0000

定义为 YEAR 数据类型的列可以接受多种输入格式,包括:

  • 4 位数字的年份值,从 1901 to 2155
  • 4 位数字的年份值的字符串形式,从 '1901''2155'
  • 2 位数字的年份值,从 099,并按如下规则转换为 4 位数年份:

    • 169 转换为 20012069
    • 7099 转换为 19701999
    • 0 转换为 0000
  • 2 位数字的年份值的字符串形式,从 '0''99',并按如下规则转换为 4 位数年份:

    • '0''69' 转换为 20002069

    • '70''99' 转换为 19701999

问题自测

char 和 varchar 的区别是什么?

  1. char:固定长度,分配固定的存储空间,存储的数据长度小于指定的长度,剩余空间会用空格来填充(取出时会删除后面空格,所以原来字符串有空格也会被删)。char类型在存储固定长度的数据时比较高效。
  2. varchar:可变长度的字符数据类型。varchar类型存储的数据长度是可变的,数据库会根据实际存储的数据长度来分配存储空间。相比于char类型,varchar类型在存储可变长度的数据时比较节省空间。(不会进行空格填充。比如如果定义一个varchar(10)类型的字段,并存储了一个长度为5的字符串,那么它只会占用5个字节的存储空间,并且还会额外用1-2字节存储「可变长字符串长度」的空间。
  3. 理论上CHAR比VARCHAR快的根本原因是站在CPU的角度来说的(物理储存连续),但性能是综合各种因素后的最终结果,当Innodb buffer pool小于表大小时,磁盘读写成为了性能的关键因素,而VARCHAR更短,因此性能反而比CHAR高。但是当Innodb buffer pool足够大时,CHAR和VARCHAR性能没有太大的差别。

varchar(100)和 varchar(10)的区别是什么?

存储的最大长度为多少个字符,实际占用的存储空间取决于存储的数据长度

假如说一个字段是varchar(10),但它其实只有6个字节,那他在内存中占的存储空间是多少?在文件中占的存储空间是多少?

保存到文件的时候,只会存储实际使用的字符串大小。但是内存是会按varchar最大值来固定分配大小

因此:内存会占用10字节,文件存储空间会占用6字节,并且还会额外用1-2字节存储「可变长字符串长度」的空间。

decimal 和 float/double 的区别是什么?存储⾦钱应该⽤哪⼀种?

  • decimal 类型的值以十进制方式存储,这意味着它们可以精确地表示小数,不会出现浮点数计算中的舍入误差。
  • floatdouble 类型的值以二进制方式存储,它们在存储和计算时可能会引入舍入误差,特别是在执行多次计算后会逐渐积累误差。
  • float使用4个字节存储,double使用8个字节,
  • decimal类型的数据存储形式是,将每9位十进制数存储为4个字节,decimal(M,D),其中, M 的范围是1~65, D 的范围是0~30, 而且D不能大于M。字段decimal(5,2),5-2=3,其中小数部分为2,整数部分为3

为什么不推荐使⽤ text 和 blob?

BLOB(Binary Large Object)用于存储二进制数据,而TEXT用于存储字符数据

​ 1.不适合建索引

​ 2.数据处理和转换的复杂性增加

​ 3.降低数据的可读性和维护性

count主键和count非主键结果会不同吗

count()函数是返回表中某个列的非NULL值的数量。

  • 由于主键的列不能存NULL值,所以count(主键)返回的结果,可以表示数据库表中所有行数据的数量。
  • 由于非主键的列可以存NULL值,那么count(非主键)返回表中非主键列的非NULL值的数量。

count(1)和 count(*)区别,哪个性能好

count(1)、 count(*)、 count(主键字段)在执行的时候,如果表里存在二级索引,优化器就会选择二级索引进行扫描,没有就读聚簇索引。

count(1)表示不为NULL,等价于count(0)也就是 count(*),相比于count(主键字段) 不会读取记录中的任何字段的值,因此会快一点

使用 count(字段) 来统计记录个数,它的效率是最差的,会采用全表扫描的方式来统计。如果非要统计表中该字段不为 NULL 的记录个数,建议给这个字段建立一个二级索引。

MySQL内连接、外连接有什么区别

内连接和外连接都是用于连表查询。内连接是只返回两个表匹配的数据行,外连接可以返回两个表匹配和不匹配的数据行,外连接主要分为左连接和右连接。

  • 左连接返回左表中的所有行和右表中匹配的行。如果右表中没有匹配的行,则用NULL值填充
  • 右连接返回右表中的所有行和左表中匹配的行,如果左表中没有匹配的行,则用NULL值填充

外连接时on和where过滤条件区别

内连接on与where等价

在外连接中,使用on和where过滤条件的区别在于:

  • on用于指定连接两个表的条件,通常用于指定两个表之间的关联条件,即连接条件,在连接时进行过滤。
  • where用于指定过滤条件,对连接后的结果集进行进一步筛选。

having与where的区别

在GROUP BY分组查询过程中,

Where是工作在GROUP BY之前,Where是对分组之前的数据进行筛选,无法使用聚合函数,

Having是工作在GROUP BY之后,Having主要对分组之后的数据进行筛选,可以使用聚合函数。

delete、drop、truncate有什么区别

delete是删除表中的数据,我们可以选择删除部分数据或者全部数据,delete删除的数据是可以回滚的,delete操作并不是真的把数据删除掉了,而是给数据打上删除标记,目的是为了空间复用,所以delete删除表数据,磁盘文件的大小是不会缩减的。

drop是删除表结构和表中所有的数据,truncate是只删除表中所有的记录,表结构并不会被删除,drop和truncate删除的数据都是不可以回滚的,并且删除表会立刻释放磁盘空间。

从删除表的性能来看,drop>truncate>delete。

联合查询中union和union all的区别是什么

  • UNION:在合并结果集后会自动剔除重复的行。
  • UNION ALL:则会保留所有的重复行,不会进行去重操作

数据库三大范式是什么

第一范式(1NF)

原子性:保证数据不可再分

tags
java,python        //一列一个数据

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情,就是主键对应着所有信息

学生表
学号id 姓名    课程  //课程不应该属于学生表

第三范式(3NF)

前提:满足第一和第二范式

第三范式需要保证表中的数据和主键直接相关,而不是间接相关

存款    花费    余额
100      10    90
  • 一范式要求所有属性都是不可分的基本数据项;
  • 二范式目的是解决部分依赖;
  • 三范式目的是解决传递依赖。

在实际的工程实践上没有必要严格遵循三范式要求,比如说可以通过字段冗余的设计,避免联表查询。

使用社交账号登录

  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Loading...