数据库运维
记录DBA学习成长历程
当前位置：MySQL学习笔记 > MySQL > 正文

11.SQL数据类型及属性约束

2023-04-03 分类：MySQL 阅读(693)
文章目录

1. SQL介绍

结构化查询语言(Structured Query Language)简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；

SQL语句就是对数据库进行操作的一种语言。

另外，为了使各类RDBMS数据库的SQL能够兼容，大部分的数据库的SQL都符合ANSI的SQL标准。

ANSI标准逐年：

1986： SQL-87最初由ANSI于1986年正式确定。

1989年：美国国家标准协会（ANSI）发布了第一套数据库查询语言标准，称为SQL-89或FIPS 127-1。

1992年： ANSI发布了修订后的标准ANSI / ISO SQL-92或SQL2，它们比SQLI更严格，增加了一些新功能。这些标准引入了合规水平，表明方言符合ANSI标准的程度。

1999： ANSI发布SQL3或ANSI / ISO SQL：1999，具有新功能，如对对象的支持。取代了核心规范的合规水平，以及另外9个封装的附加规格。

2003： ANSI发布SQL：2003，引入标准化序列，XML相关功能和标识列。第一个RDBMS的创建者EFCodd博士于同年4月18日去世。

2006： ANSI发布SQL：2006，定义如何将SQL与XML结合使用，并使应用程序能够将XQuery集成到现有的SQL代码中。

2008： ANSI发布SQL：2008，引入INSTEAD OF触发器以及TRUNCATE语句。

2011： ANSI发布SQL：2011或ISO / IEC 9075：2011，ISO（1987）的第七个修订版和SQL数据库查询语言的ANSI（1986）标准。

MySQL对于ANSI SQL的支持如下：

https://dev.mysql.com/doc/refman/8.0/en/compatibility.html

2. MySQL中命令常用种类

—2.1 mysql客户端内置命令

help 查看全部命令
  \G 宽表行显示
  \d 存储过程相关
  \q 退出
  tee/notee 如执行tee /tmp/tee.log 下面操作会记录在这个日志文件中
  pager/nopager 调用linux命令分页显示等功能（如输入pager less后，下面的操作会分页）
  source 导入一个库（如source /opt/t100w.sql，和mysql -uroot -p123</opt/world.sql功能一致）
  system 在mysql里执行linux命令(与\!功能一直)
  status 查看当前数据库的工作状态

—2.2 MySQL服务器端内置命令

help contents 查看全部命令
Account Management（用户、权限管理）
Administration（系统管理类语句）
Components（组件应用）
Compound Statements（过程函数复合语句）
Contents（帮助目录）
Data Definition（数据定义） 
Data Manipulation（数据操作） 
Data Types（数据类型）
Functions（内置函数）
Geographic Features（地理位置）
Help Metadata（帮助信息元数据）
Language Structure
Plugins（插件管理）
Storage Engines（）
Table Maintenance（表维护）
Transactions（事务）
User-Defined Functions（自定义函数）
Utility（实用工具）
如help Account Management 查看用户、权限管理相关命令

3 SQL_MODE详解

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
mysql> select @@sql_mode;
mysql> show variables like '%sql_mode%';
mysql> set global sql_mode='';把sql_mode修改为空

4. 库表属性

—4.1 MySQL 逻辑结构

库：库名，属性（字符集，校对规则，表空间加密）

表：表名，表属性（存储引擎，字符集，校对，表空间加密），列(字段)，（列名，列属性），数据行

—4.2 字符集

show charset;

UTF8 : 字符最大长度3个字节(比如中文在UTF8中占3字节,在gbk编码中占2字节)

UTF8mb4 ：字符最大长度4个字节(比如emoji 表情字符为4字节) 开发统一要求。

英文字母，数字为1字节。

存储图片视频使用blob数据类型二进制存储，但是不建议这样存储，建议使用文件链接附件存储图片。

—4.3 校对(排序规则)规则 

show collation;
影响排序，排序会转换成数字排序，大小写是否敏感会影响到排序。
mysql> select ascii('a');
|   97   |
mysql> select ascii('A');
|   65   |
默认为utf8mb4_0900_ai_ci，大小写不敏感，会统一使用小写排序。
utf8mb4_0900_as_cs 大小写敏感 

—4.4 存储引擎

show engines;
90%以上的表都是innoDB引擎

—4.5 加密表空间

ENCRYPTION='N'/'Y'
表空间加密就是对表的ibd物理文件加密。
临时生效：
INSTALL PLUGIN keyring_file soname 'keyring_file.so';
mkdir -p /data/3306/mysql-keyring/
chown -R mysql.mysql /data/3306/mysql-keyring/
chmod 750 /data/3306/mysql-keyring/

永久生效
在my.cnf的[mysqld]段，加这两行
early-plugin-load=keyring_file.so
keyring_file_data=/data/3306/mysql-keyring/keyring

5. 列的数据类型

定义：约束数据行的规则，让数据变的有意义。

—5.1 数字类型-整型

1 Bytes(字节) = 8 BITS(长度)=11111111（二进制数）=255（十进制数）

选择数据类型的关键点：
1、合适的。
2、简短的。
3、完整的。

—5.2 数字类型-浮点型与定点数

FLoat
Float：表示不指定小数位的浮点数
Float(M,D)：表示一共存储M个有效数字，其中小数部分占D位，比如：Float(10,2)：整数部分为8位，
小数部分为2位

Double
Double又称之为双精度：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右。

Decimal：
Decimal系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的。
定点数：能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入），整数部分一定精确

Decimal(M,D)：
M表示总长度，最大值不能超过65，D代表小数部分长度，最长不能超过30。

—5.3 字符串类型

——5.3.1 Char（L）

char(64)
定长字符：指定长度之后，系统一定会分配指定的空间用于存储数据
基本语法：char(L)，L代表字符数（中文与英文字母一样），L长度为0到255

——5.3.2 Varchar（L）

varchar(64)
变长字符：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度）
基本语法：Varchar(L)，L代表字符数，L的长度理论值位0到65535

因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销，
是用来保存数据所占用的空间长度。（长度在0-255为1个字节额外开销，255-65535为2个字节额外开销）
如果数据本身小于255个字符：额外开销一个字节；如果大于255个，就开销两个字节

——5.3.3 Char和varchar的区别

1、 char一定会使用指定的空间，varchar是根据数据来定空间
2、 char的插入数据效率理论上比varchar高：varchar是需要通过后面的记录数来计算使用哪一种类型？
如果确定数据一定是占指定长度，那么使用char类型，例如身份证号,手机号等。
如果不确定数据到底有多少，那么使用varchar类型；
如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar
(65535-2-1)/3=存中文的个数
varchar(L)行格式如果是compact(5.6版本),如果字符集是utf8时，索引长度为751~765字节，varchar的L值不能超过250长度(765/3） 
,如果字符集是utf8mb4时，索引长度为761~765字节，varchar的L值不能超过190长度(750/4）
varchar(L)行格式如果是dymanic，索引长度不能超过3072字节，字符集是utf8时，varchar的L值不能超过1024(3072/3)，如果字符集
是utf8mb4时，varchar的L值不能超过768(3072/4)

——5.3.4 Text/longtext

文本类型：本质上mysql提供了两种文本类型

Text：存储普通的字符文本
Blob：存储二进制文本（图片，文件），一般都不会使用blob来存储文件本身，
      通常是使用一个链接来指向对应的文件本身。
Text：系统中提供的四种text
Tinytext：系统使用一个字节来保存，实际能够存储的数据为：2 ^ 8 + 1
Text：使用两个字节保存，实际存储为：2 ^ 16 + 2
Mediumtext：使用三个字节保存，实际存储为：2 ^ 24 + 3
Longtext：使用四个字节保存，实际存储为：2 ^ 32 + 4

注意：
1、在选择对应的存储文本的时候，不用刻意去选择text类型，系统会自动根据存储的数据长度来
   选择合适的文本类型。
2、 在选择字符存储的时候，如果数据超过255个字符，那么一定选择text存储

——5.3.5 Enum

枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果。
如果确定某个字段的数据只有那么几个值：如性别，男、女、保密，系统就可以在设定字段的时候规定当前
字段只能存放固定的几个值：使用枚举

基本语法：enum(数据值1,数据值2…)
系统提供了1到2个字节来存储枚举数据：通过计算enum列举的具体值来选择实际的存储空间：如果数据值列表在255个以内，那么
一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。

——5.3.6 Set

集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制：
1表示该选项被选中，0表示该选项没有被选中。

基本语法：set(‘值1’,’值2’,’值3’…)
系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元
1个字节：set只能有8个选项
2个字节：set只能有16个选项
3个字节：set只能表示24个选项
8个字节：set可以表示64个选项

Set和enum一样，最终存储到数据字段中的依然是数字而不是真实的字符串
数据在存储的时候，如果被选中，那么对应的位的值就为1，否则为0
系统在进行存储的时候会自动将得到的最终的二进制颠倒过来，然后再进行转换成十进制存储

Set集合的意义：
1、 规范数据
2、 节省存储空间

Enum：单选框(只能插入一个)
Set：复选框(可以插入多个)

—5.4 时间类型

—5.5 二进制类型

6. 列约束及属性

primary key ：主建约束，同时保证唯一性和非空.每个表只能有一个PK，我们建议业务无关列（数字列）。
foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值。改外键关联的其中一张表会导致锁，
大型项目中一般不使用外键，通过代码逻辑实现多表更新。
not null ：非空约束，保证字段的值不能为空
default：默认约束，保证字段总会有值，即使没有插入值，都会有默认值
unique：唯一，保证唯一性但是可以为空，比如手机号。
auto_increment：自增长列
unsigned： 无符号
comment： 注释
赞(1)
MySQL学习笔记 » 11.SQL数据类型及属性约束

近期文章

友情链接