基于 SQLite 3 的 C 学习：2-高级操作
基于 SQLite 3 的 C/C++ 学习：2-高级操作与有关函数
背景
基于 SQLite 3 的 C/C++ 学习：开发流程 与 基本函数 中，我们简单介绍了有关 SQLite3 函数的使用。
这一讲我们更加深入地介绍一些更加高级的操作。
附加数据库(ATTACH)/分离数据库(DETACH)
在实际的开发中，我们可以给数据库起别名。
数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。无法被附加或分离。
# 附加
ATTACH DATABASE 'file_name' AS 'database_name';
# 分离
DETACH DATABASE 'Alias-Name';
例程：
$ ./sqlite3 example.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .database
main: /home/schips/sqlite/example.db
## 起别名
sqlite> ATTACH DATABASE 'example.db' as exp;
sqlite> .database
main: /home/schips/sqlite/example.db
exp: /home/schips/sqlite/example.db
## 取消别名
sqlite> DETACH DATABASE exp;
sqlite> .database
main: /home/schips/sqlite/example.db
创建/删除表
创建表
CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。
CREATE TABLE table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
约束
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 SQLite 中常用的约束。
- NOT NULL 约束：确保某列不能有 NULL 值。
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
- DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);
- UNIQUE 约束：确保某列中的所有值是不同的。
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);
- PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的。主键是唯一的 ID。
我们使用主键来引用表中的行。可通过把主键设置为其他表的外键，来创建表之间的关系。由于"长期存在编码监督"，在 SQLite 中，主键可以是 NULL，这是与其他数据库不同的地方。
主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。
一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
- CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);
删除表
DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。
DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称，如下所示：
DROP TABLE database_name.table_name;
查询(select)/新建(insert)/更新(update)/删除(delete) 语句
SELECT 语句
用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
SELECT column1, column2, columnN FROM table_name;
Distinct 关键字
SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。
INSERT INTO 语句
用于向数据库的某个表中添加新的数据行。
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
UPDATE
查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
DELETE
查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
DELETE FROM table_name
WHERE [condition];
表达式与 运算符
运算符
一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。
- 算术运算符
| 运算符 | 描述 | 实例 |
|---|---|---|
| + | 加法 - 把运算符两边的值相加 | a + b 将得到 30 |
| - | 减法 - 左操作数减去右操作数 | a - b 将得到 -10 |
| * | 乘法 - 把运算符两边的值相乘 | a * b 将得到 200 |
| / | 除法 - 左操作数除以右操作数 | b / a 将得到 2 |
| % | 取模 - 左操作数除以右操作数后得到的余数 | b % a will give 0 |
- 比较运算符
| 运算符 | 描述 | 实例 |
|---|---|---|
| == | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a == b) 不为真。 |
| = | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a = b) 不为真。 |
| != | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a != b) 为真。 |
| <> | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a <> b) 为真。 |
| > | 检查左操作数的值是否大于右操作数的值，如果是则条件为真。 | (a > b) 不为真。 |
| < | 检查左操作数的值是否小于右操作数的值，如果是则条件为真。 | (a < b) 为真。 |
| >= | 检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 | (a >= b) 不为真。 |
| <= | 检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 | (a <= b) 为真。 |
| !< | 检查左操作数的值是否不小于右操作数的值，如果是则条件为真。 | (a !< b) 为假。 |
| !> | 检查左操作数的值是否不大于右操作数的值，如果是则条件为真。 | (a !> b) 为真。 |
- 逻辑运算符
| 运算符 | 描述 |
|---|---|
| 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 运算符搜索指定表中的每一行，确保唯一性（无重复）。 |
- 位运算符
| 运算符 | 描述 | 实例 |
|---|---|---|
| & | 如果同时存在于两个操作数中，二进制 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 |
sqlite> .tables
table1
sqlite> SELECT * FROM table1;
#id text
100|update
101|update
102|update
103|update
104|update
105|text
211|schips
sqlite> SELECT * FROM table1 WHERE id > 103;
104|update
105|text
211|schips
sqlite>
表达式
一个或多个值、运算符和计算值的SQL函数的组合。SQL 表达式与公式类似，都写在查询语言中。您还可以使用特定的数据集来查询数据库。
- 布尔表达式：SQLite 的布尔表达式在匹配单个值的基础上获取数据。语法如下：
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHING EXPRESSION;
例如:
sqlite> SELECT * FROM table1 WHERE id > 103;
104|update
105|text
211|schips
- 数值表达式：这些表达式用来执行查询中的任何数学运算。语法如下：
SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;
有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。
例如:
sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21
sqlite> SELECT COUNT(*) AS "RECORDS" FROM table1;
RECORDS = 7
- 日期表达式：日期表达式返回当前系统日期和时间值，这些表达式将被用于各种数据操作。
sqlite> SELECT CURRENT_TIMESTAMP;
2020-03-26 01:34:15
Where 子句
子句：无法单独成SQL的句子成分，需要搭配语句进行使用
SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。
如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录，只获取需要的记录。
WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中。
Like 子句
LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这些符号可以被组合使用。这里有两个通配符与 LIKE 运算符一起使用：
- 百分号 （%）：代表零个、一个或多个数字或字符。
- 下划线 （_）：下划线（_）代表一个单一的数字或字符。
我们看看具体的例子
| 语句 | 描述 |
|---|---|
| WHERE SALARY LIKE '200%' | 查找以 200 开头的任意值 |
| WHERE SALARY LIKE '%200%' | 查找任意位置包含 200 的任意值 |
| WHERE SALARY LIKE '_00%' | 查找第二位和第三位为 00 的任意值 |
| WHERE SALARY LIKE '2_%_%' | 查找以 2 开头，且长度至少为 3 个字符的任意值 |
| WHERE SALARY LIKE '%2' | 查找以 2 结尾的任意值 |
| WHERE SALARY LIKE '_2%3' | 查找第二位为 2，且以 3 结尾的任意值 |
| WHERE SALARY LIKE '2___3' | 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值 |
sqlite> SELECT * FROM table1;;
100|update
101|update
102|update
103|update
104|update
105|text
211|schips
sqlite> SELECT * FROM table1 WHERE id LIKE '1%';
100|update
101|update
102|update
103|update
104|update
105|text
Glob 子句
GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。这些符号可以被组合使用。
- 星号 （*）：代表零个、一个或多个数字或字符。
- 问号 （?）：代表一个单一的数字或字符。
我们看看一些例子：
| 语句 | 描述 |
|---|---|
| WHERE SALARY GLOB '200*' | 查找以 200 开头的任意值 |
| WHERE SALARY GLOB '200' | 查找任意位置包含 200 的任意值 |
| WHERE SALARY GLOB '?00*' | 查找第二位和第三位为 00 的任意值 |
| WHERE SALARY GLOB '2??' | 查找以 2 开头，且长度至少为 3 个字符的任意值 |
| WHERE SALARY GLOB '*2' | 查找以 2 结尾的任意值 |
| WHERE SALARY GLOB '?2*3' | 查找第二位为 2，且以 3 结尾的任意值 |
| WHERE SALARY GLOB '2???3' | 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值 |
sqlite> SELECT * FROM table1;;
100|update
101|update
102|update
103|update
104|update
105|text
211|schips
sqlite> SELECT * FROM table1 WHERE name GLOB '*s*';
211|schips
LIMIT 和OFFSET 子句
LIMIT 子句：用于限制由 SELECT 语句返回的数据数量。
OFFSET 子句：指明在开始返回行之前忽略多少行。
OFFSET 0 和省略 OFFSET 子句是一样的。如果 OFFSET 和 LIMIT 都出现了，那么在计算LIMIT 个行之前先忽略 OFFSET 指定的行数。
使用 LIMIT 和 OFFSET 子句可以只取出查询结果中的一部分数据行：
SELECT select_list
FROM table_expression
[ORDER BY sort_expression1 [ASC | DESC] [, sort_expression2 [ASC | DESC] ...]]
[LIMIT { number | ALL }] [OFFSET number]
sqlite> SELECT * FROM table1;
100|update
101|update
102|update
103|update
104|update
105|text
211|schips
sqlite> SELECT * FROM table1 WHERE name GLOB '*u*' LIMIT 2;
100|update
101|update
sqlite> SELECT * FROM table1 WHERE name GLOB '*u*' LIMIT 2 OFFSET 2;
102|update
103|update
Order By
ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
# ASC 升序、DESE 降序
例子：
sqlite> SELECT * FROM table1 ORDER BY ID DESC;
211|schips
105|text
104|update
103|update
102|update
101|update
100|update
Group By
GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组（求总结果）
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
SELECT column-list
FROM table_name
WHERE [ conditions ]
▲GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
$ ./sqlite3 example2.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
sqlite> INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
sqlite> INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
sqlite> INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
James|10000.0
Paul|20000.0
Having 子句
HAVING 子句允许指定条件来过滤将出现在有GROUP BY的最终结果中的分组结果。统计GROUPT BY结果中满足指定重复次数的结果。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
HAVING 子句在 SELECT 查询中的位置：
SELECT
FROM
WHERE
GROUP BY
▲HAVING
ORDER BY
在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
James|19000.0
Paul|20000.0
sqlite> SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
8|Paul|24|Houston|20000.0
sqlite> SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
9|James|44|Norway|5000.0
sqlite>
若在页首无特别声明，本篇文章由 Schips 经过整理后发布。
博客地址：https://www.cnblogs.com/schips/
