LV07-02-SQLite-数据类型、运算符和表达式

发表于 分类于 ， ， 本文字数： 4.6k 阅读时长 ≈ 17 分钟

本文主要是SQLite数据库的数据类型、运算符和表达式相关笔记，若笔记中有错误或者不合适的地方，欢迎批评指正😃。

点击查看使用工具及版本
Windows windows11
Ubuntu Ubuntu16.04的64位版本
VMware® Workstation 16 Pro 16.2.3 build-19376536
SecureCRT Version 8.7.2 (x64 build 2214) - 正式版-2020年5月14日
开发板 正点原子 i.MX6ULL Linux阿尔法开发板
uboot NXP官方提供的uboot，NXP提供的版本为uboot-imx-rel_imx_4.1.15_2.1.0_ga(使用的uboot版本为U-Boot 2016.03)
linux内核 linux-4.15(NXP官方提供)
STM32开发板 正点原子战舰V3(STM32F103ZET6)
点击查看本文参考资料
参考方向 参考原文
SQLite官网SQLite Home Page
SQLite中文网SQLite 中文网
SQLite 基础教程SQLite参考手册 - 在线原生手册
菜鸟教程SQLite 教程
易百教程SQLite 教程
点击查看相关文件下载
--- ---

一、数据类型

我们既然要通过数据库来存储数据，自然需要数据类型啦，不然怎么知道这列都存储了什么样的数据呢？

SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。我们可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。说实话，这段话我并不怎么理解，但是吧，暂时会用就好，哈哈哈，后边懂了再补充。

1. SQLite 存储类

存储类描述
NULL值是一个 NULL 值。
INTEGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB值是一个 blob 数据，完全根据它的输入存储。
一个存储类比一个数据类型更加的通用。比如，`INTEGER`存储类包含`6`种不同长度的`integer`数据类型。这使得在磁盘存储上有所差异。但是只要一个`INTEGER`类的值磁盘所读取，它们就会被转换成更加通用的数据类型（`8`进制有符号整型`integer`）。因此，在大多数情况下，“存储类”和“数据类型”无法区分，并且两个属于可以互相转换。

2. SQLite 亲和(Affinity)类型

2.1 什么是亲和类型？

SQLite不强制数据类型约束，也就是说任何数据都可以插入任何列。例如，我们可以向一个整型列中插入任意长度的字符串，向布尔型列中插入浮点数，或者向字符型列中插入日期型值。

在创建数据表时所指定的数据类型不会限制在该列中插入任何数据。任何列均可接受任意长度的字符串（只有一种情况除外，就是标志为INTEGER PRIMARY KEY的列只能存储64位整数， 当向这种列中插数据除整数以外的数据时，将会产生错误。）

SQLite确实使用声明的列类型来指示我们所期望的格式。所以，当我们向一个整型列中插入字符串时，SQLite会试图将该字符串转换成一个整数。如果可以转换，它将插入该整数；否则，将插入字符串。这是一个特性，而不是一个bug。这种特性被称为列亲和性(type or column affinity).

而列的亲和类型是指该列数据的推荐类型。需要注意，这个类型只是推荐的，而不是必须的。上边已经说过任何列可以存储任何类型的数据，只是说根据选择，某些列更倾向于使用一个存储类型而不是另一个存储类型。

2.2 SQLite支持的亲和类型

上边我们知道了SQLite支持列的类型近似性。列可以存储任何类型的数据，而列的首选存储类称为它的近似性类型。在SQLite3数据库中有以下类型近似可用于分配：

亲和类型描述
TEXT 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL 其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE 不做任何的转换，直接以该数据所属的数据类型进行存储。
## 3. `SQLite` 亲和类型(`Affinity`)及类型名称

当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

亲和类型数据类型
INTEGER INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
TEXT CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB
NONE BLOB, no datatype specified
REAL REAL, DOUBLE, DOUBLE PRECISION, FLOAT
NUMERIC NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME

二、运算符

运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。常用于where子句中，以便于对指定数据记录执行相关操作。SQlite中的运算符主要有算术运算符、比较运算符、逻辑运算符和位运算符。

1. 算术运算符

假设变量 a=10，变量 b=20，则：

运算符说明示例
+加法运算符：用于将运算符两侧的值相加。a+b = 50
-减法运算符：用于从左操作数减去右操作数。a-b = -10
*乘法运算符：用于将运算符两边的操作数相乘。a*b = 200
/除法运算符：它是将左操作数除以右操作数。b/a = 2
%取模运算符：用于通过左操作数除以右操作数并返回余数。b%a = 0

2. 比较运算符

假设变量 a=10，变量 b=20，则：

运算符描述实例
==检查两个操作数的值是否相等，如果相等则返回true,否则返回false。(a == b) 不为真。
=检查两个操作数的值是否相等，如果相等则返回true,否则返回false。(a = b) 不为真。
!=检查两个操作数的值是否不相等，如果不相等则返回true,否则返回false。(a != b) 为真。
<>检查两个操作数的值是否不相等，如果不相等则返回true,否则返回false。(a <> b) 为真。
>检查左操作数的值是否大于右操作数的值，如果是则返回true,否则返回false。(a > b) 不为真。
<检查左操作数的值是否小于右操作数的值，如果是则返回true,否则返回false。(a < b) 为真。
>=检查左操作数的值是否大于等于右操作数的值，如果是则返回true,否则返回false。(a >= b) 不为真。
<=检查左操作数的值是否小于等于右操作数的值，如果是则返回true,否则返回false。(a <= b) 为真。
!<检查左操作数的值是否不小于(大于等于)右操作数的值，如果是则返回true,否则返回false。 (a !< b) 为假。
!>检查左操作数的值是否不大于(小于等于)右操作数的值，如果是则返回true,否则返回false。(a !> b) 为真。

3. 逻辑运算符

运算符 描述
AND AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件
IS NULL NULL 运算符用于把某个值与 NULL 值进行比较。
IS IS 运算符与 = 相似。
IS NOT IS NOT 运算符与 != 相似。
|| 连接两个不同的字符串，得到一个新的字符串。
UNIQUE UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

4. 位运算符

4.1 一个真值表

位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：

pqp & qp | q
0000
0101
1111
1001

4.2 运算符说明

假设变量 A=60(0011 1100)，变量 B=13(0000 1101)，则：

运算符描述实例
&如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。(A & B) 将得到 12，即为 0000 1100
|如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。(A \| B) 将得到 61，即为 0011 1101
~二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。(~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<二进制左移运算符。左操作数的值向左移动右操作数指定的位数。A << 2 将得到 240，即为 1111 0000
>>二进制右移运算符。左操作数的值向右移动右操作数指定的位数。A >> 2 将得到 15，即为 0000 1111

三、表达式

表达式是一个或多个值、运算符和计算值的SQL函数的组合。SQL 表达式与公式类似，都写在查询语言中，我们还可以使用特定的数据集来查询数据库。

1. 语法格式

SQLite表达式用查询语言(SQL)编写，并与SELECT（后边会详细学习）语句一起使用。一般语法格式如下：

1
2
3
4
5
6
7
-- 多行语句
SELECT column1, column2, columnN   
FROM table_name   
WHERE [CONDITION | EXPRESSION];

-- 单行语句
SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];

【注意】

（1）写成小写也是可以的。

（2）关于SELECT和WHERE后边会详细学习，这里就简单了解就好啦。

2. 布尔表达式

2.1 语法格式

一般语法格式如下：

1
2
3
SELECT column1, column2, columnN   
FROM table_name   
WHERE SINGLE VALUE MATCHTING EXPRESSION;

2.2 使用实例

假设 COMPANY 表有以下记录：

1
2
3
4
5
6
7
8
9
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

在SQLite命令提示符中输入以下语句：

1
sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

则会看到如下输出（默认的输出可不是这样的格式，后边学习到SQLite命令的时候就会了解到如何设置成这样的显示格式啦）：

1
2
3
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

3. 数值表达式

3.1 语法格式

一般语法格式如下：

1
2
SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;
  • numerical_expression ：用于数学表达式或任何公式。

3.2 使用实例

3.2.1 实例1

在SQLite命令提示符中输入以下语句：

1
2
sqlite> SELECT (15 + 6) AS ADDITION;
ADDITION = 21

3.2.2 实例2

有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。例如，

假设 COMPANY 表有以下记录：

1
2
3
4
5
6
7
8
9
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

在SQLite命令提示符中输入以下语句：

1
2
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

4. 日期表达式

日期表达式返回当前系统日期和时间值，这些表达式可以被用于各种数据操作。

4.1 语法格式

这里的话似乎没什么固定的语法格式，直接看实例把

4.2 使用实例

4.2.1 获取当前时间

在SQLite命令提示符中输入以下语句：

1
2
sqlite> SELECT datetime('now','localtime');
2022-07-20 07:29:05