sql基础

mysql版本说明

  1. Alpha版本: 是内部测试版，一般不向外发布，会有很多Bug 一般只有测试人员使用
  2. Beta版本: 功能开发完和所有测试之后的产品，不会存在较大的功能和性能Bug
  3. RC版本：生产环境之前的一个小版本，根据Beta版本测试结果打补丁后的版本。
  4. GA版本: 是正式发布的版本

数据类型

数字数据类型

整形类型

数字类型是MySQL内置的数据类型之一，数字类型被细分为精确数值型(简称"整形")和近似数值型(简称"浮点型")。

  1. 微整型(tinyint): 只占用一个字节，对应十进制(decimal)取值范围是-128 ～ 127(有符号位)或者0 ～ 255(无符号位)。
  2. 小整型(smallint): 只占用两个字节，对应十进制(decimal)取值范围是-32768 ～ 32767(有符号位)或者0 ～ 65535(无符号位)。
  3. 中等整型(MEDIUMINT)：只占用三个字节，对应十进制(decimal)取值范围-8388608 ～ 8388607(有符号位)或者0 ～ 16777215(无符号位)
  4. 整型(int): 只占用四个字节的int，对应十进制(decimal)取值范围-2147483648 ～ 2147483647(有符号位)或者0 ～ 4294967295(无符号位)。
  5. 大整形(BIGINT): 只占用八个字节的，对应十进制(decimal)取值范围是-9223372036854775808 ～ 9223372036854775807(有符号位)或者0 ～ 18446744073709551615(无符号位)。　　　　　　
  6. 定点类型(DECIMAL，NUMERIC): DECIMAL和NUMERIC类型存储精确的数字数据值，这些类型用于对于保持精确性很重要，例如货币数据。在MySQL中，NUMERIC是基于DECIMAL类型实现的，因此下面关于DECIMAL的注释同样适用于NUMERIC。以"salary DECIMAL(5,2)"为例,要求DECIMAL(5,2)能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列范围内的值从-999.99到 999.99。遵循四舍五入法则

浮点类型

近似数值型又分为单精度浮点型(float)和双精度浮点型(double)和自定义十进制型(DECIMAL)。

  1. 单精度浮点型(float): 只占用4字节，对应十进制位有符号位值为: "(-3.402823466E+38，-1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38)"; 对应十进制位无符号位值为: "0，(1.175494351E-38，3.402823466E+38)"
  2. 双精度浮点型(double): 只占用8字节，对应十进制位有符号位值为: "(-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.225 0738585072014E-308，1.7976931348623157E+308)" 对应十进制位无符号位值为: 0，(2.2250738585072014E-308，1.7976931348623157E+308)
  3. 位数据类型(BIT): 位数据类型用于存储位值，其语法为: BIT(M)，这里的M指的的取值范围只能在1-64，即仅能存储8个字节大小。要指定位值，可以使用b'value'表示法。比如:b"111"和b"10000000"分别表示7和128。

字符串数据类型

CHAR是固定的长度大小，而VARCHAR是可变的长度大小且会多出1-2个字节来存储这个长度大小。

  1. CHAR： 定长字符类型，最大可存储255个字符。举例: CHAR(4)，存储真实数据"AB"，实际在数据库存储的样子是"AB "，即实际长度不足4字节，则使用空格填充。
  2. VARCHAR: 不定长字符类型，最大可存储65535个字符。VARCHAR的优点是实际存储的字符长度随着写入数据的实际字符长度变化而变化，因此需要维护一个定义字符长度。举例: VARCHAR(4)，存储真实数据"AB"，实际在数据库存储的样子就是"AB"，但会占用1个字节来维护实际数据的长度为2，则实际存储占用的空间应该是3字节。

温馨提示:
(1)VARCHAR内部维护了存储每条数据的真实长度的变量，它们只能占用1-2个字节，这是由于VARCHAR最多只能存储65535个字符，因此仅需2个字节就能表示65535种状态;

(2)在MySQL Community 5.6版本中，如果我们定了某个字段的数据类型为CHAR(5)或VARCHAR(5)，则意味着该字段仅能存储5个字符，若超出5个字符，则多余的部分将被截断;但是在MySQL Community 5.7或者MySQL Community 8.0版本中不允许插入成功;这主要是SQL_MODE在背后捣鬼，很明显MySQL Community 5.7以上不采用MySQL Community 5.6那种截断数据的方式，目的是让我们明白，数据存在丢失的风险！

(3)对于英文，符号和数字，每个字符均占用1个字节长度，但对于特殊字符，比如中文，日文，韩文等就得考虑字符集的因素，我们以中文为例，若指定GBK字符编码，则常用的汉字通常占用2个字节，但对于utf8mb4字符编码而言，则常用的汉字通常占用3个字节，而对于emoji表情字符底层对应4个字节去存储。

ENUM枚举类型

枚举(ENUM)是一个字符串对象，其值从表创建时列规范中显式枚举的允许值列表中选择。比如性别，省份，城市基本上都是固定的，很少发生变动的数据，我们就可以将其设定为枚举型。

mysql> create table shirts (name VARCHAR(40), size ENUM('x-small','small','medium','large','x-large'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert shirts (name, size) VALUES  ('dress-shirt','large'), ('t-shirt','medium'),('polo-shirt','small');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 只能插入ENUM字段所定义的字符串

BINARY与VARBINARY

BINARY和VARBINARY类型与CHAR和VARCHAR类似，只是它们存储二进制字符串而不是非二进制字符串。

也就是说，它们存储字节字符串而不是字符串。这意味着它们具有二进制字符集和排序规则，比较和排序基于值中字节的数值。

允许的最大长度对于BINARY和VARBINARY与CHAR和VARCHAR相同，只是BINARY和VARBINARY的长度是以字节而不是字符来度量的。

BLOB与TEXT

BLOB是一个二进制大对象，它可以保存可变数量的数据。这四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们仅在所能容纳的值的最大长度上不同。

TEXT是一个文本大对象，这四种文本类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型，具有相同的最大长度和存储要求。

SET

集合(SET)是可以有零个或多个值的字符串对象，每个值必须从创建表时指定的允许值列表中选择。值得注意的是，创建集合的各元素的值不应该重复，这是集合的特点。

由多个集合成员组成的集合列值由逗号(",")分隔的成员指定。这样做的结果是集合成员值本身不应包含逗号。一个集合列最多可以有64个不同的成员。

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO myset (col) VALUES ('b,c'), ('1,2');
ERROR 1265 (01000): Data truncated for column 'col' at row 2
mysql>

## 数据插入失败，因为'1','2'并不在定义的集合'a','b','c','d'中

日期和时间数据类型

用于表示时间值的日期和时间数据类型是DATE，TIME，DATETIME，TIMESTAMP，和YEAR。

  1. DATE:(MySQL 5.6.4之前占用3字节)
    支持的范围是“1000-01-01”到“9999-12-31”。MySQL以'YYYY-MM-DD'格式显示日期值，但允许使用字符串或数字为日期列赋值。

  2. TIME:(MySQL 5.6.4之前占用3字节)
    范围是“-838:59:59.000000”到“838:59:59.000000”。MySQL以“hh:mm:ss[.fraction]”格式显示时间值，但允许使用字符串或数字为时间列赋值。可以给出0到6范围内的可选fsp值，以指定小数秒精度。值为0表示没有小数部分。如果省略，则默认精度为0。

  3. DATETIME:(MySQL 5.6.4之前占用8字节)
    日期(DATE)和时间(TIME)的组合。支持的范围是“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。

    MySQL以“YYYY-MM-DD hh:MM:ss[.fraction]”格式显示DATETIME值，但允许使用字符串或数字为DATETIME列赋值。

  4. TIMESTAMP:(MySQL 5.6.4之前占用4字节)
    时间戳值存储为自epoch('1970-01-01 00:00:00'UTC)以来的秒数。其范围是“1970-01-01 00:00:01.000000”UTC到“2038-01-19 03:14:07.999999”UTC(格林尼治时间)。

    存储时，MySQL将TIMESTAMP值从当前时区转换为UTC时间进行存储，查询时，将数据从UTC转换为检索的当前时区。因此TIMESTAMP会受到时区的影响，而DATETIME不会发生这种情况。

    时间戳不能表示值“1970-01-01 00:00:00”，因为这相当于从纪元开始的0秒，而值0保留用于表示“0000-00-00 00:00:00”时间戳值“零”。

  5. YEAR:(MySQL 5.6.4之前占用1字节)
    用4个数字来表示一个年份。MySQL以YYYY格式显示年份值，但允许使用字符串或数字为年份列赋值。值显示为1901-2155或0000。

    SUM()和AVG()聚合函数不能处理时态值。(它们将值转换为数字，丢失第一个非数字字符之后的所有内容。)要解决此问题，请转换为数字单位，执行聚合操作，然后转换回时间值。

常用的字段约束及字段属性

常用的字段约束(Constraints)相关的关键字:

  1. UNIQUE KEY Constraints: 唯一约束，顾名思义，作用是保证该列字段的必须是不重复的值。
  2. PRIMARY KEY Constraints: 主键约束，作用是保证该列字段唯一且非空，每张表只能有一个主键，称为聚簇索引。
  3. FOREIGN KEY Constraints:外键约束，作用是允许跨表交叉引用相关数据，外键约束有助于保持数据的一致性。

常用的字段属性:

  1. UNSIGNED : 无符号约束，即取消字段的符号位，通常作用在数字数据类型字段上。换句话说，就是让该字段非负数。作用是保证该列字段无符号，主要针对的是数字列，即可以保证非负数，比如人的年龄。
  2. NOT NULL: 非空约束，作用是保证该列字段必须非空，通常建议大家将每个列都设置为非空。若不指定非空约束则默认该字段允许为空(NULL)！
  3. DEFAULT: 默认值，若插入一条数据后，用户未提交某列的值，则该列将使用默认值。
  4. COMMENT: 注释
  5. AUTO_INCREMENT: 用于自动增长的字段，该属性不能单独使用。通常和PRIMARY KEY Constraints配合使用。比如你为某个"id PRIMARY KEY"字段指定了AUTO_INCREMENT属性，若插入数据时未给该字段赋值，则该字段会根据上一条的记录自动增长。

字符集(Charset)

什么是字符集

字符集只是一个规则集合的名字，对应到真实生活中，字符集就是对某种语言的称呼。例如：英语，汉语，日语。

字符集的组成

对于一个字符集来说要正确编码转码一个字符需要三个关键元素：字库表(character repertoire)、编码字符集(coded character set)和字符编码(character encoding form)。

字库表: 是一个相当于所有可读或者可显示字符的数据库，字库表决定了整个字符集能够展现表示的所有字符的范围。

编码字符集:即用一个编码值(code point)来表示一个字符在字库中的位置。

字符编码: 将编码字符集和实际存储数值之间的转换关系。一般来说都会直接将(code point)的值作为编码后的值直接存储。例如在ASCII中A在表中排第65位，而编码后A的数值是0100 0001也即十进制的65的二进制转换结果。

查看字符集

SHOW CHARSET;

校对规则(Collation)

校对规则概述

每种字符集，有多种校对规则，也可以称之为"排序规则"。

如果我们对字符集设置了不同的校对规则，那么它们将会影响到排序的操作。

查看校对规则

SHOW COLLATION;

# 我们通常只关心创建库所对应的字符集的校对规则
SHOW COLLATION WHERE Charset='utf8mb4';
posted @ 2025-08-31 00:04  阿峰博客站  阅读(2)  评论(0)    收藏  举报