一 SQL基础知识
1.1 数据库概述
1.1.1 数据库
- 数据库（DataBase，简称DB）是存放数据的仓库，按照某种数据结构对数据进行组织部、存储和管理
数据库中的数据通常以表格的形式呈现，表格由行和列组成，每一行表示一个记录，而每一列表示一种属性。数据库的设计和管理旨在实现数据的高效存储、检索和更新。
1.1.2 数据库系统
- 数据库系统（Database System，简称DBS）是采用了数据库技术的计算机系统。数据库系统不仅是对一组数据进行管理的软件，还是存储介质、处理对象和管理系统的集合体，由数据库、硬件、软件和数据库管理员组成
它提供了一种结构化和有组织的方式来存储和管理大量数据，以便用户和应用程序可以方便地访问和操作数据。
1.1.3 数据库管理系统
- 数据库管理系统（Database Management System，简称DBMS）是介于操作系统和用户之间的一个数据管理软件，用于创建、管理和维护数据库。
DBMS提供了一套接口和工具，使用户能够轻松地存储、检索、更新和管理数据库中的数据。它负责处理数据库的各种操作，包括数据的增加、删除、修改和查询，同时还提供了数据安全性、一致性和完整性的控制机制。
1.2 数据模型
1.2.1 数据结构
- 是对系统静态特征的描述，是一种组织和存储数据的方式，使得数据可以高效地被访问和操作
在数据库中，数据结构通常指数据库内部组织数据的方式，如表、索引、视图等。关系数据库中使用表格（关系）来存储数据，这些表格包含行和列，形成了一种表格状的数据结构。
1.2.2 数据操作
- 是对系统动态特征的描述，是指对数据库中的数据进行增、删、改、查等操作的过程。常见的数据操作包括插入新数据（增）、删除数据（删）、更新已有数据（改）以及查询数据（查）。
数据库操作是通过数据库管理系统（DBMS）提供的查询语言（如SQL）来执行的。SQL提供了丰富的语法和命令，用于执行各种数据操作，从简单的查询到复杂的数据更新和事务管理。
1.2.3 完整性约束
- 是完整性规则的集合，确保数据库中数据的准确性和一致性的规则和条件。它包括实体完整性、参照完整性和用户定义的完整性。主要包括三种
（1）实体完整性（Entity Integrity），确保表中的每一行都有一个唯一的标识，通常通过主键来实现（也可以理解为主键约束）。
在设计数据库时，建议为所有的数据库表都定义一个主键，用于保证数据库表中记录的唯一性。一张表中只允许设置一个主键，当然这个主键可以是一个字段，也可以是一个字段组合。一般不建议使用复合主键，理由如下
复杂性： 复合主键增加了数据表的复杂性。查询和操作复合主键可能会变得更加繁琐，因为在涉及复合键的查询中，需要同时考虑多个列。这增加了查询的复杂性和理解难度，尤其是对于新的数据库开发者或维护者而言。
性能： 复合主键可能对性能产生一定的影响。在某些情况下，使用单一主键可能更有利于查询性能，因为涉及到的索引较少。对于复合主键，数据库系统需要同时考虑多个列，这可能导致索引结构更加复杂，影响查询的效率。
可维护性： 复合主键可能增加了数据库的维护难度。当需要修改主键结构时，涉及到多个列的复合主键可能需要更多的工作，而单一主键则相对更容易维护。
一致性： 在复合主键中，所有的列的组合必须是唯一的。这可能导致一些不直观的业务规则，使得数据的维护和验证更加困难。对于单一主键，业务规则更容易理解和实施。
虽然有这些潜在的问题，但并不是说复合主键在所有情况下都是不合适的。在某些复杂的业务需求下，使用复合主键可能是合理的选择。在设计数据库时，需要权衡使用复合主键的复杂性和带来的好处，确保选择的主键结构符合实际需求。
（2）参照完整性（Referential Integrity），确保表格之间的关系是有效的。外键约束是参照完整性的一种形式，它确保在引用表中存在的值也在被引用表中存在。
上述图形中student表的gender字段要么是null，要么来自gender表中的gender字段的值，所以students为gender的子表。
注：由于子表和父表之间的外键约束关系，如果子表的记录“参照”了父表的某条记录，那么父表这一记录的删除和修改操作都可能以失败告终。言外之意：如果试图直接删除或修改子表的外键的值，子表中的外键的值就必须是null，或者是父表中的主键值。
解决方案：先删除父表中的主键，再删除子表中的外键。
（3）用户定义的完整性（User-defined Integrity），允许数据库管理员或设计者定义额外的规则，以确保数据的一致性，如CHECK约束、触发器等。
主要来自用户自定义约束
- 唯一性约束：给某一字段设置唯一性约束以后，表中该字段值就不会重复。与主键约束不同，一张表中可以有多个唯一性约束，如高校名字不会重复。
- 非空约束：给某一字段设置非空约束以后，表中该字段值就不会为null。一张表中可以有多个非空约束，如学生性别不能为空。
- 检查约束：给某一字段设置检查约束以后，表中该字段值在存入时就会检查是否满足用户指定的条件。一张表中可以有多个检查约束，如学生单科成绩需要满足大于等于0且小于等于100的约束条件。
- 默认值约束：给某一字段设置默认值约束以后，在插入该字段数据时，如果不指定值，就会自动填入默认值。一张表中可以有多个默认值约束，如学生状态默认为健康。
1.2.4 总结
（1）数据模型
数据模型时数据库系统的核心与基础，是关于描述数据与数据之间的联系、数据的语义、数据一致性约束的概念性工具的集合。
（2）完整性约束
确保数据库中数据的准确性和一致性的规则和条件。它包括实体完整性、参照完整性和用户定义的完整性。
1.3 SQL
1.3.1 结构化查询语言
- 结构化查询语言（Structured Query Language，简称SQL）是一种应用于关系型数据库查询的结构化语言，SQL 提供了一组用于定义、操作和查询数据库的命令和语法。它是一种声明性语言，用户可以通过简洁的语法描述需要执行的操作，而不必关心具体的实现细节。
1.3.2 SQL的四种功能
（1）DDL（Data Definition Language）数据定义语言，用来定义数据库对象（数据库，表，字段），包括创建、修改和删除数据库对象。
（2）DML（Data Manipulation Language）数据操作语言，用来对数据库表中的数据进行增删改，常见的 DML 命令包括 INSERT、UPDATE 和 DELETE。
（3）DQL（Data Query Language）数据查询语言，用来查询数据中表的数据，其主要命令是 SELECT，通过该命令可以指定要查询的列、表、条件等。
（4）DCL（Data Control Language）数据控制语言，用来创建数据库用户，控制数据的访问权限，主要的 DCL 命令是 GRANT 和 REVOKE。
1.3.3 三种模式结构
（1）模式（逻辑模式/概念模式），是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据试图。一个数据库只能有一个模式。模式处于三级结构的中间层。
注：定义模式时不仅要定义数据的逻辑结构，而且要定义数据之间的联系，定义于数据有关的安全性，完整性要求
- 是指数据库中的数据组织和关系的模式，与数据库的具体实现无关。它定义了数据的结构、关系和约束，而不涉及到具体的存储细节。逻辑模式的设计通常是根据业务需求和数据之间的关系来进行的。
- 例如，在一个逻辑模式中，可以定义员工和部门之间的关系，而具体如何在数据库中实现如使用哪些表、列等，则是物理模式的问题。
（2）外模式（用户模式），是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式是模式的子集，一个数据库可以有多个外模式。
- 是指数据库中的用户所拥有的对象的集合。每个数据库用户都有一个用户模式，用于组织和管理其拥有的数据库对象，如表、视图等。用户模式有助于对数据库对象进行隔离，不同用户之间的对象命名可以相同而不会冲突。
- 例如，对于一个名为"HR"的用户，其用户模式可能包含员工表、部门表等对象，而对于名为"Finance"的用户，其用户模式可能包含财务报表、成本中心表等对象。
（3）内模式（物理模式/存储模式），一个数据库只能有一个内模式。它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。
- 是指数据库中数据的存储方式和组织结构，它与底层的数据库管理系统和硬件相关。物理模式定义了数据在存储介质上的排列方式、索引的使用方式等。
- 例如，一个表在逻辑模式中可能被定义为包含员工信息，但在物理模式中，该表可能会被拆分为多个文件，使用不同的存储结构，以优化查询性能。
1.4 E-R图
1.4.1 实体
实体（Entity）： 实体是现实世界中的一个对象或概念，可以在数据库中存储和管理。实体可以是具体的物体（如人、地点、物品），也可以是抽象的概念（如订单、课程、事件）。
通常用矩形框表示
1.4.2 属性
属性（Attribute）： 属性是实体的特征或属性，用于描述实体的特性。例如，对于实体“学生”，属性可以包括学生的姓名、学号、出生日期等。
通常用椭圆表示（与实体用无向边连接）
1.4.3 关系
关系（Relationship）： 关系表示实体之间的联系或连接。例如，学生和课程之间可以存在关系，表示学生选修了某些课程。关系可以是一对一、一对多或多对多的。
（1）一对一（1:1）
一对一关系表示两个实体之间的每个实体都最多与另一个实体相关联。每个实体在关系中都有唯一的对应项。
例如：一个医院只能有一个院长，一个院长只能在一个医院任职。
（2）一对多（1:n）
一对多关系表示一个实体的每个实例可以与另一个实体的多个实例相关联，但反之不成立。在这种关系中，通常有一个“一”的一侧和一个“多”的一侧。
例如：一个医院有多个护士，一个护士只能在一个医院任职
（3）多对多（m:n）
多对多关系表示两个实体之间的每个实体实例可以与另一个实体的多个实例相关联，反之亦然。在这种关系中，每个实体实例都可以关联多个实体实例。
例如：一本图书可以被多个读者借阅，一个读者也可以借阅多本书
二 MySQL
2.1 数据类型
2.1.1 数值类型
（1）整数类型
| 数据类型 | 字节数 | 取值范围（有符号） | 取值范围（无符号） | 说明 |
| bit（不常用） | 1 | (-128,127) | (0,255) | 最小的整数 |
| bool（不常用） | 1 | (-128,127) | (0,255) | 最小的整数 |
| tinyint | 1 | (-128,127) | (0,255) | 最小的整数 |
| smallint | 2 | (-32768,32767) | (0,65535) | 小型整数 |
| mediumint | 3 | (-8388608,8388607) | (0,16777215) | 中型整数 |
| int | 4 | (-2147483648,2147483647) | (0,4294967295) | 标准整数 |
| bigint | 8 | (-9233372036854775808, 9233372036854775807) | (0,18446744073709551615) | 大整数 |
注：
（1）字（word）
- 定义： 字是计算机中基本的存储单位，通常表示为一组二进制位的固定长度。字的长度可以是8位、16位、32位或64位，具体取决于计算机的体系结构。
- 示例： 在一个计算机体系结构中，如果字的长度为32位，那么每个字就由32个二进制位组成。
（2）字节（byte）
- 定义： 字节是计算机存储单位的基本单元，通常由8位二进制组成。字节是计算机中最小的可寻址内存单元，是数据存储和传输的基本单位。
- 示例： 一个字节可以表示为8位的二进制数字，如
01011010。（3）位（bit）
- 定义： 位是计算机中最基本的信息单元，是二进制数字的最小单元，可以表示0或1。位用于存储和传输信息的最基本形式。
- 示例： 一个位可以表示为0或1。
（4）字长（Word Length）
- 定义： 字长是计算机中一个字的二进制位数，表示计算机能够一次处理的二进制位的长度。字长直接关系到计算机的运算能力和内存访问能力。
- 示例： 如果一个计算机的字长为32位，表示它可以一次性处理32位的二进制数据。
换算：
- 在16位的系统中，1字（word）=2字节（byte）=16位（bit）
- 在32位的系统中，1字（word）=4字节（byte）=32位（bit）
- 在64位的系统中，1字（word）=8字节（byte）=64位（bit）
- 1KB=1B（byte），1MB=1024KB，1GB=1024MB
bit、bool和tinyint的区别（一般只用到tinyint，另外两个不怎么用到）
（1）bit数据类型：
- 范围：
bit数据类型通常用于表示二进制数据，可以存储 0、1 或 NULL（空值）。- 存储空间： 在很多数据库系统中，
bit类型的存储空间是固定的，通常占用 1 位。但在某些数据库系统中，一个bit字段可能占用多个位。- 用途： 通常用于存储布尔（true/false）值或类似的二进制标志。
（2）
bool数据类型：
- 范围：
bool（或boolean）数据类型是布尔类型，可以存储 TRUE、FALSE 或 NULL（空值）。- 存储空间： 通常，
bool类型的存储空间是一个字节，即 8 位。这表示一个bool变量可以存储三种值（TRUE、FALSE 或 NULL）。- 用途： 常用于表示逻辑真假值。
（3）
tinyint数据类型：
- 范围：
tinyint是一个整数数据类型，通常占用 1 个字节，可以存储范围为 -128 到 127 或 0 到 255，具体范围取决于是否使用有符号（signed）或无符号（unsigned）。- 存储空间： 通常占用 1 个字节，即 8 位。
- 用途： 主要用于存储小范围的整数值。
总的来说，这些数据类型有不同的用途，选择取决于需要存储的数据的性质和范围。
bit用于存储二进制数据，bool用于存储布尔值，而tinyint则用于存储小范围的整数。在具体的数据库系统中，这些数据类型的实现和具体用法可能会有一些差异。
（2）小数类型
| 数据类型 | 字节数 | 取值范围（负数） | 取值范围（正数） | 说明 |
| decimal | 自定义 | 可变 | 可变 | 精度确定的小数类型 |
| 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) | 双精度浮点数 |
（1）decimal(length,precision)用于表示精度确定（小数点后数字的位数确定）的小数类型
- length表示小数的最大位数，如5.555，length为4
- precision表示小数点后数字的位数，如5.555，percision为3
如decimal(4,2)表示该小数的取值范围为（-99.99，99.99）
（2）无符号关键字（unsigned）也可以用来修饰小数
- 无符号也就是在小数面前不加符号，如99.99，88.88等。
- 当我们定义面积时，面积不能为负数，所以可以在字段后加个unsigned，完整为area float unsigned
（3）单精度和双精度
IEEE754的规范中指出，数字为32位的一倍是个基准，称为“单”，所以“几倍精度”也就是32位的整数倍。常见的还有“半精度”、“双精度”、“四倍精度”、“任意精度”等。
float为4个字节，4byte=32bit，所以为单精度
double为8个字节，8byte=64bit，所以为双精度
（4）浮点数如何在计算机中储存，即符号位，指数位，小数位
1.首先我们先来了解一下浮点数是什么?
- 浮点数就是小数点可以移动的数，比如31.4×10¹=3.14×10²=0.314×10³
- 所以，浮点数被表示为N=S×rʲ，其中N为浮点数，S为小数位（也叫做尾数，可正可负），r为基数（也叫做基值），j为阶码（可正可负）。在计算机中，基数可取2、4、8或16等。
- 为了提高数据精度和便于浮点数的比较，在计算机中规定浮点数的尾数用纯小数替代，此外尾数非零的有效位的最高位为1。如0.114×10³而不能为1.14×10²或0.0114×10⁴。
2.其次我们再来了解一下浮点数在计算机中的存储（采用这种数据格式的机器称为浮点机）
3.IEEE 754 标准
现代计算机中，浮点数一般采用IEEE制定的国际标准
按IEEE标准，常用的浮点数有三种
符号位S 阶码 尾数 总位数 短实数 1 8 23 32 长实数 1 11 52 64 临时实数 1 15 64 80
- 数符表示浮点数的正负，但与其有效位（尾数）是分开的
- 阶码用移码表示，阶码的真值都被加上一个常数（偏移量），如短实数、长实数和临时实数的偏移量用十六进制表示分别为7FH、3FFH和3FFFH。
- 尾数部分都是规格化表示，即尾数非零的有效位的最高位为1。但在IEEE标准中，整数部分为1，即1.(小数部分)，如1.14×10²
4.单精度与双精度的表示
单精度：1位符号，8位指数，23位小数
双精度：1位符号，11位指数，52位小数
2.1.2 字符串类型
（1）常规字符串类型
| 类型 | 取值范围 | 说明 |
| char(n) | 0~255个字符 | 固定长度为n的字符串，单个字符占用的字节数*n，n的取值与字符集无关 |
| varchar(n) | n的取值与字符集有关 | 当字符集为gbk时，最多容纳65535/2=32767个字符 当字符集为utf8时，最多容纳65535/3=21845个字符 |
注：varchar是如何实现变长的
在
VARCHAR类型中，存储的字符串的长度可以是可变的，但不超过指定的最大长度。例如，如果定义了一个VARCHAR(50)的字段，那么该字段可以存储最多 50 个字符的字符串。如果实际存储的字符串长度小于 50 个字符，那么字段的长度就会根据实际存储的内容而变短。
（2）text和blob类型
| 类型 | 最大长度（字节数） | 说明 |
| tinyblob | 2^8~1(225) | 小blob字段 当字符集为gbk时，最多容纳255/2=127个字符 当字符集为utf8时，最多容纳255/3=85个字符 |
| tinytext | 2^8~1(225) | 小text字段 当字符集为gbk时，最多容纳255/2=127个字符 当字符集为utf8时，最多容纳255/3=85个字符 |
| blob | 2^16~1(65535) | 常规blob字段 当字符集为gbk时，最多容纳65535/2=32767个字符 当字符集为utf8时，最多容纳65535/3=21845个字符 |
| text | 2^16~1(65535) | 常规text字段 当字符集为gbk时，最多容纳65535/2=32767个字符 当字符集为utf8时，最多容纳65535/3=21845个字符 |
| mediumblob | 2^24~1(16777215) | 中型blob字段 当字符集为gbk时，最多容纳167772150/2=83886075个字符 当字符集为utf8时，最多容纳167772150/3=55924050个字符 |
| mediumtext | 2^24~1(16777215) | 中型text字段 当字符集为gbk时，最多容纳167772150/2=83886075个字符 当字符集为utf8时，最多容纳167772150/3=55924050个字符 |
| longblob | 2^32~1(4294967295) | 长blob字段 当字符集为gbk时，最多容纳4294967295/2=2147483647个字符 当字符集为utf8时，最多容纳4294967295/3=1431655765个字符 |
| longtext | 2^32~1(4294967295) | 长text字段 当字符集为gbk时，最多容纳4294967295/2=2147483647个字符 当字符集为utf8时，最多容纳4294967295/3=1431655765个字符 |
- blob：适合存储二进制数据，支持文本、声音、图像等（搜索的内容区分大小写）
- text：适合存储长文本（搜索的内容不区分大小写）
（3）enum和set类型
| 类型 | 最大值 | 说明 |
| Enum("value1","value2",......) | 65535 | 该类型的列只可以容纳所列值之一或为NULL |
| Set("value1","value2",......) | 64 | 该类型的列可以容纳一组值或为NULL |
- 要将列中的内容限制在一种选择，用enum
- 允许在一个列中有多于一个的条目，用set
2.1.3 日期时间类型
| 类型 | 字节数 | 取值范围 | 说明 |
| 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 | 年份可指定两位数字和四位数字的格式 |
| datetime | 8 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 日期和时间，格式为YYYY-MM-DD HH:MM:SS |
| timestamp | 8 | 1970-01-01 00:00:00~2038-01-19 03:14:07 | 时间标签，在处理报告时使用的显示格式取决于M的值 |
datetime和timestamp的区别
DATETIME存储的是本地日期和时间，没有时区信息。TIMESTAMP存储的是相对于 UTC 的日期和时间，包含时区信息。DATETIME的范围通常较大，但不考虑时区。TIMESTAMP在某些数据库系统中可能受到 32 位存储范围的限制。- 由于包含时区信息，
TIMESTAMP可用于处理多时区的应用场景。在选择使用
DATETIME还是TIMESTAMP时，需要考虑到具体的需求，包括是否需要时区信息以及日期时间的存储范围。
2.2 运算符
2.2.1 算术运算符
| 运算符 | 说明 |
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| /（DIV） | 除法，返回商 |
| %（MOD） | 除法，返回余数 |
2.2.2 比较运算符
| 运算符 | 说明 |
| = | 等于 |
| <>或!= | 不等于 |
| <=> | NULL安全的等于(NULL-safe) |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between | 存在于指定范围 |
| in | 存在于指定集合 |
| is null | 为null |
| is not null | 不为null |
| like | 通配符匹配 |
| regexp或rlike | 正则表达式匹配 |
2.2.3 逻辑运算符
| 运算符 | 说明 |
| not或！ | 逻辑非 |
| and或&& | 逻辑与 |
| or或|| | 逻辑或 |
| XOR | 逻辑异或 |
2.2.4 位运算符
| 运算符 | 说明 |
| & | 位与（位AND） |
| | | 位或（位OR） |
| ^ | 位异或（位XOR） |
| ~ | 位取反 |
| >> | 位右移 |
| << | 位左移 |
（1）逻辑与和位与的区别
（2）逻辑或和位或的区别
（3）逻辑异或和位异或的区别
首先，异或表示当任意一个操作数位null时返回null，当两者不一样时返回1，一样时返回0
（4）位左移和位右移
2.3 常用函数
2.3.1 数值函数
| 函数 | 说明 |
| abs(x) | 返回x的绝对值 |
| ceil(x) | 返回大于 X 的最小整数值 |
| floor(x) | 返回小于 X 的最大整数值 |
| mod(x,y) | 返回 x%y |
| rand() | 返回 0 1 内的随机值 |
| round(x,y) | 返回参数 x 的四舍五入的有 y 位小数的值 |
| truncate(x,y) | 返回数字 x 截断为 y 位小数的结果 |
2.3.2 字符串函数
| 函数 | 说明 |
| concat(S1,S2,...Sn) | 连接S1，S2，...Sn为一个字符串 |
| insert(str,x,y,instr) | 将字符串 str 从第x 位置开始，y 个字符长的子串替换为字符串 instr |
| lower(str) | 将字符串 str中所有字符变为小写 |
| upper(str) | 将字符串 str 中所有字符变为大写 |
| left(str) | 返回字符串 str 最左边的 x 个字符 |
| right(str) | 返回字符串 str 最右边的 x 个字符 |
| lpad(str,n,pad) | 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度 |
| rpad(str,n,pad) | 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度 |
| ltrim(str) | 去掉字符串 str 左侧的空格 |
| rtrim(str) | 去掉字符串 str 行尾的空格 |
| repeat(str,x) | 返回 str 重复 x 次的结果 |
| replace(str,a,b) | 用字符串 b 替换字符串 str 中所有出现的字符串 a |
| strcmp(s1,s2) | 比较字符串s1和s2 |
| trim(str) | 去掉字符串行尾和行头的空格 |
| substring(str,x,y) | 返回从字符串 str x 位置起 y 个字符长度的字串 |
2.3.3 日期时间函数
| 函数 | 说明 |
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前的日期和时间 |
| unix_timestamp(date) | 返回日期date的UNIX时间戳 |
| from_unixtime | 返回UNIX时间戳的日期值 |
| week(date) | 返回日期date为一年中的第几周 |
| year(date) | 返回日期date的年份 |
| hour(time) | 返回time的小时值 |
| minute(time) | 返回time的分钟值 |
| monthname(date) | 返回date的月份名 |
| date_fromat(date,fmt) | 返回按字符串fmt格式化日期date值 |
| date_add(date,interval expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
| datediff(expr,expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
2.3.4 流程函数
| 函数 | 说明 |
| if(value,t,f) | 如果value是真，返回t;否则返回f |
| ifnull(value1,value2) | 如果value1不为空，返回value1,否则返回value2 |
| case when [value1] then [result1]…else [default]end | 如果value1是真，返回result1,否则返回default |
| case [expr] when [valuel] then [result1]…else[default]end | 如果expr等于valuel,返回result1,否则返回default |
2.3.5 其他函数
| 函数 | 说明 |
| datebase() | 返回当前数据库名 |
| version() | 返回当前数据库版本 |
| user() | 返回当前登录用户名 |
| inet_aton(ip) | 返回IP地址的数字表示 |
| inet_ntoa(num) | 返回数字代表的IP地址 |
| password(str) | 返回字符串str的加密版本 |
| md5() | 返回字符串str的MD5值 |
三 存储引擎
1.1 MySQL存储引擎
存储引擎是负责管理数据的底层软件组件，它定义了如何存储、检索和管理数据。不同的数据库管理系统（DBMS）支持不同的存储引擎，每个存储引擎都有其特定的优势和适用场景。
注：创建新表时不指定存储引擎，系统就会使用默认的存储引擎
- MySQL5.5之前默认的存储引擎为MyISAM
- MySQL5.5之后默认的存储引擎为InnoDB
1.1.1 查询当前数据库版本
select version();
1.1.2 查询当前数据库支持的存储引擎
show engines;
分号表示结束符
"\g"的作用和";"一样
"\G"可以让结果变得更加美观
1.1.3 查询当前数据库默认的存储引擎
show variables like '%storage_engine%';
1.1.4 查询当前数据库已使用的存储引擎
SHOW TABLE STATUS;
1.2 MyISAM
1.2.1 MyISAM存储引擎的文件类型
MyISAM存储引擎的表存储成3个文件，文件的名字和表名相同，扩展名包括frm、MYD和MYI
- frm：存储表的结构
- MYD：存储数据，是MYData的缩写
- MYI：存储索引，是MYIndex的缩写
数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度
1.2.2 MyISAM存储引擎的存储格式
（1）静态表
如果所有的表列大小都是静态的（不适用可变字符串如varchar，text，blob），MySQL就会自动使用静态表格式。
优点：
- 存储非常迅速
- 容易缓存
- 出现故障容易恢复
缺点：
- 占用空间高（每列都需要分配该列的最大空间）
- 静态表的数据在存储时会按照列的宽度补足空格，但是在应用访问的时候会被去掉，这就导致了存入数据库内末尾带有空格的字符串，在取出时丢失了尾部的空格。
（2）动态表
如果有任意一个表中有任意一个字段为动态的（适用可变字符串如varchar，text，blob），MySQL就会自动使用动态表格式。
优点：
- 占用的空间小
缺点：
- 频繁地更新和删除记录会产生碎片。随着数据集中的碎片增加，数据访问性能就会相应降低
- 出现故障恢复相对比较困难
解决碎片的方法
- 尽可能使用静态数据类型
- 定期执行optimize table语句或myisamchk-r命令来改善性能
（3）压缩表
当我们只需要只读的表时，可以使用myisampack工具将其转换为压缩表来减少空间。
优点：性能显著提升
缺点：表属性为只读
1.2.3 MyISAM存储引擎的优缺点
优点：
- 占用空间小
- 处理速度快
缺点：
- 不支持事务的完整性和并发性
1.3 InnoDB
1.3.1 InnoDB存储引擎的特点
（1）InnoDB存储引擎支持自动增长列AUTO_INCREMENT
MySQL中规定自增列必须为主键。自增列的值不能为空，且必须唯一。
在插入值时，如果不输入值/0/null，则插入的值为自动增长的值。
如果插入前面没有出现过的值，也可以直接插入。
（2）InnoDB存储引擎支持外键FOREIGN KEY
参考前面完整性约束讲到的外键约束
1.3.2 InnoDB存储引擎的存储方式
（1）使用共享表空间存储
这种方式创建的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
（2）使用多表空间存储
这种方式创建的表结构保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。
1.3.3 InnoDB存储引擎的适用情况
- 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求
- 事务：InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎，这是管理敏感数据如金融信息和 用户注册信息的必需软件
- 自动灾难恢复：与其他存储引擎不同，InnoDB存储引擎能够自动从灾难中恢复。虽然MyISAM也能在灾难后恢复，但其过程要长得多
1.3.4 InnoDB存储引擎的优缺点
优点：
- 提供了良好的事务管理
- 具有崩溃修复能力
- 能够实现并发控制
缺点：
- 读写效率稍差
- 占用的数据空间相对较大
1.4 Memory
1.4.1 Memory存储引擎的文件存储形式
Memory存储引擎使用存在于内存的内容来创建表。每个基于Memory存储引擎的表实际对应一个磁盘文件。该文件的文件名和表名相同，类型为frm。
- 因为它的数据是放在内存上的，并且默认使用Hash索引，一旦服务器关闭，表中的数据就会丢失。因此，基于Memory存储引擎的表生命周期很短，一般都是一次性的。
- Memory表的大小时受到限制的。表的大小取决于max_rows和max_heap_table_size两个参数。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需进行扩大。
1.4.2 Memory存储引擎的索引类型
Memory存储引擎默认使用哈希（Hash）索引。其速度比使用B型数（BTree）索引快。如果希望使用B型数索引，可以在创建索引时选择使用。
1.4.3 Memory存储引擎的优缺点
-
速度快： 由于数据存储在内存中，读取和写入操作的速度非常快，远远快于使用磁盘存储的引擎。
-
适用于临时数据： Memory 存储引擎适合存储一些临时性的数据，例如缓存、会话数据等，因为这些数据不需要持久保存在磁盘上。
-
易于配置： Memory 存储引擎非常简单，易于使用和配置。它不需要进行复杂的磁盘 I/O 操作和事务处理。
-
低成本： 由于不涉及磁盘 I/O，Memory 存储引擎通常对于一些读取密集型的场景是一种低成本的选择。
缺点：
-
数据持久性： Memory 存储引擎的数据存储在内存中，一旦数据库服务重启或关闭，数据就会丢失。因此，不适用于需要数据持久性的场景。
-
内存占用： 由于数据存储在内存中，如果数据量过大，可能会占用过多的系统内存，导致系统性能下降。这也限制了 Memory 存储引擎适用的场景。
-
不支持事务： Memory 存储引擎不支持事务，这意味着它不能提供 ACID 特性，不适用于需要事务支持的应用。
-
不支持外键： Memory 存储引擎不支持外键约束，这限制了在具有复杂关系的数据模型中使用的可能性。
1.5 存储引擎的区别
| InnoDB | MyISAM | Memory | |
| 事务安全 | 支持 | 无 | 无 |
| 存储限制 | 64TB | 有 | 有 |
| 空间使用 | 高 | 低 | 低 |
| 内存使用 | 高 | 低 | 高 |
| 插入数据的速度 | 低 | 高 | 高 |
| 对外键的支持 | 支持 | 无 | 无 |
1214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
