SQLITE学习之进阶操作(一)

最新推荐文章于 2024-05-27 10:35:28 发布

蜀中豪杰

最新推荐文章于 2024-05-27 10:35:28 发布

阅读量713

点赞数

分类专栏：数据库文章标签：数据库入门

本文链接：https://blog.csdn.net/cainiaoxiakexing/article/details/93485226

版权

数据库专栏收录该内容

10 篇文章 1 订阅

订阅专栏

1、PRAGMA命令

PRAGMA语句是SQLITE数据的SQL扩展，是它独有的特性，主要用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。。它采用与SELECT、INSERT等语句一样的形式来发出请求，但也有几个重要的不同：

特定的PRAGMA语句可能被移走，新的PRAGMA语句可能在新的版本中添加。因此，后向兼容无法保证。
未知的PRAGMA命令不会有错误消息出现，它只是简单的忽略。
有些PRAGMA只在SQL的编译阶段起作用，而不是执行阶段。这意味着如果使用C语言，sqlite3_prepare(), sqlite3_step(), sqlite3_finalize()这几个API，pragma命令可能只在prepare()的调用里运行，而不是在后两个API当中执行。或者，pragma可能在sqlite3_step()执行的时候运行。到底在哪个阶段执行，取决于pragma从本身，以及是哪个sqlite的release版本。
pragma命令是sqlite特有的，基本上不可能与其它数据库保持兼容。

要查询当前的 PRAGMA 值，只需要提供该 pragma 的名字，使用命令pragma的基本语法结构：

PRAGMA pragma_name;

要设置当前的 PRAGMA 值，使用命令pragma的基本语法结构：

PRAGMA pragma_name = value;

设置模式，可以是名称或等值的整数，但返回的值将始终是一个整数。

(1)auto_vacuum Pragma，获取或设置 auto-vacuum 模式。语法如下：

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

其中，mode 可以是以下任何一种：
在这里插入图片描述 补充：

进行移动设备开发，例如Windows Mobile或者Wince(Windows Embedded CE)的开发，有时候会使用到SQLite作为存储数据库。SQLite默认配置是不会自动回收空间，如何进行大量数据的删除以后，数据库文件大小不会自动减少，因此需要手工压缩SQLite的数据文件大小。
导致原因：数据库实时的插入和删除会使得数据库文件结构混乱，数据库访问速度减慢。当数据库中的表，索引或触发器被撤销，会留下空白磁盘空间。使数据库比需要的大小更大，但能加快INSERT插入速度。
解决方法：调用一下SQL命令即可，具体操作如下：

sqlite>VACUUM；

VACUUM命令工作原理：复制当前数据库文件到临时数据库并从临时数据库重新载入主数据库，以整理数据库文件。这将除去空白页，使表数据彼此相邻排列，并整理数据库文件结构。
Note：VACUUM命令不会改变表中记录的ROWID值，不对表的ROWID值从1开始重新分配。
SQLite3.1中，可以通过使用auto-vacuum模式取代VACUUM命令，使用 auto_vacuum pragma开启该模式。

.建议：
不要打开。也就是“PRAGMA auto_vacuum = 0；”执行命令。原因如下：
对于SQLite3.1，可以使用“auto-vacuum模式”代替“VACUUM命令”在删除数据后回收空间，可以通过使用auto_vacuum编译指示来使能auto-vacuum模式。使能auto_vacuum模式之后，一个数据库在删除数据后留下的空闲页就可能会被回收，从而缩小数据库文件，而不用使用VACUUM命令重新构建整个数据库。然而，使用auto_vacuum模式将会导致额外的数据库文件碎片。并且使用auto_vacuum模式不会压缩部分数据库的填充页而VACUUM命令则会（压缩）。

(2)cache_size Pragma,查询或者修改打开的数据库内存里头能容纳的最多的数据库页数。语法如下：

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

pages 值表示在缓存中的页面数。内置页面缓存的默认大小为 2,000 页，最小尺寸为 10 页。

(3)case_sensitive_like Pragma,控制内置的 LIKE 表达式的大小写敏感度。默认情况下，该 Pragma 为 false，这意味着，内置的 LIKE 操作符忽略字母的大小写。语法如下：

PRAGMA case_sensitive_like = [true|false];

Note: 目前没有办法查询该 Pragma 的当前状态。

(4)count_changes Pragma，获取或设置数据操作语句的返回值，如 INSERT、UPDATE 和 DELETE。语法如下：

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

默认情况下，该 Pragma 为 false，这些语句不返回任何东西。如果设置为 true，每个所提到的语句将返回一个单行单列的表，由一个单一的整数值组成，该整数表示操作影响的行。

(5)database_list Pragma,将用于列出了所有的数据库连接。语法如下：

PRAGMA database_list;

该 Pragma 将返回一个单行三列的表格，每当打开或附加数据库时，会给出数据库中的序列号，它的名称和相关的文件。

(6)encoding Pragma,控制字符串如何编码及存储在数据库文件中。语法如下：

PRAGMA encoding;
PRAGMA encoding = format;

格式值可以是 UTF-8、UTF-16le 或 UTF-16be 之一。

(7)freelist_count Pragma,返回一个整数，表示当前被标记为免费和可用的数据库页数。语法如下：

PRAGMA [database.]freelist_count;

格式值可以是 UTF-8、UTF-16le 或 UTF-16be 之一。

(8)index_info Pragma,返回关于数据库索引的信息。语法如下：

PRAGMA [database.]index_info( index_name );

结果集将为每个包含在给出列序列的索引、表格内的列索引、列名称的列显示一行。

(9)index_list Pragma,列出所有与表相关联的索引。语法如下：

PRAGMA [database.]index_list( table_name );

结果集将为每个给出列序列的索引、索引名称、表示索引是否唯一的标识显示一行。

(10)journal_mode Pragma，获取或设置控制日志文件如何存储和处理的日志模式。语法如下：

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

在这里插入图片描述
(11)max_page_count Pragma, 为数据库获取或设置允许的最大页数。语法如下：

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

默认值是 1,073,741,823，这是一个千兆的页面，即如果默认 1 KB 的页面大小，那么数据库中增长起来的一个兆字节。

(12)page_count Pragma, 返回当前数据库中的网页数量。语法如下：

PRAGMA [database.]page_count;

(13)page_size Pragma,获取或设置数据库页面的大小。语法如下：

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

默认情况下，允许的尺寸是 512、1024、2048、4096、8192、16384、32768 字节。改变现有数据库页面大小的唯一方法就是设置页面大小，然后立即 VACUUM 该数据库。
Note:数据库文件的大小应该是 page_count * page_size。

(14)parser_trace Pragma,随着它解析 SQL 命令来控制打印的调试状态，语法如下：

PRAGMA parser_trace = [true|false];

默认情况下，它被设置为 false，但设置为 true 时则启用，此时 SQL 解析器会随着它解析 SQL 命令来打印出它的状态。

(15)recursive_triggers Pragma，获取或设置递归触发器功能。如果未启用递归触发器，一个触发动作将不会触发另一个触发。语法如下：

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

(16)schema_version Pragma,获取或设置存储在数据库头中的的架构版本值。语法如下：

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

这是一个 32 位有符号整数值，用来跟踪架构的变化。每当一个架构改变命令执行（比如 CREATE… 或 DROP…）时，这个值会递增。

(17)secure_delete Pragma,用来控制内容是如何从数据库中删除。语法如下：

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

安全删除标志的默认值通常是关闭的，但是这是可以通过 SQLITE_SECURE_DELETE 构建选项来改变的。

(18)sql_trace Pragma，用于把 SQL 跟踪结果转储到屏幕上。语法如下：

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite 必须通过 SQLITE_DEBUG 指令来编译要引用的该 Pragma。

(19)synchronous Pragma,获取或设置当前磁盘的同步模式，该模式控制积极的 SQLite 如何将数据写入物理存储。语法如下：

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite 支持下列同步模式：
在这里插入图片描述
(20)temp_store Pragma, 获取或设置临时数据库文件所使用的存储模式。语法如下：

PRAGMA temp_store;
PRAGMA temp_store = mode;

在这里插入图片描述
(21)temp_store_directory Pragma,获取或设置用于临时数据库文件的位置。语法如下：

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

(22)user_version Pragma,获取或设置存储在数据库头的用户自定义的版本值。语法如下

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

这是一个 32 位的有符号整数值，可以由开发人员设置，用于版本跟踪的目的。

(23)writable_schema Pragma，获取或设置是否能够修改系统表。语法如下：

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

如果设置了该 Pragma，则表以 sqlite_ 开始，可以创建和修改，包括 sqlite_master 表。使用该 Pragma 时要注意，因为它可能导致整个数据库损坏。

2、SQLite 约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。以下是在 SQLite 中常用的约束：

NOT NULL 约束：确保某列不能有 NULL 值。
默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。如：
```
 CREATE TABLE COMPANY(
    ID INT PRIMARY KEY     NOT NULL,
    NAME           TEXT    NOT NULL,
    AGE            INT     NOT NULL,
    ADDRESS        CHAR(50),
    SALARY         REAL
 );
```

NULL 与没有数据是不一样的，它代表着未知的数据。SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。如:
创建一个新的表 COMPANY，并增加了五列。在这里，SALARY 列默认设置为 5000.00。所以当 INSERT INTO 语句没有为该列提供值时，该列将被设置为 5000.00。
```
 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 约束：确保某列中的所有值是不同的。
UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。如：创建一个新的表 COMPANY，并增加了五列。在这里，AGE 列设置为 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 值。
一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。如：
```
 CREATE TABLE COMPANY(
    ID INT PRIMARY KEY     NOT NULL,
    NAME           TEXT    NOT NULL,
    AGE            INT     NOT NULL,
    ADDRESS        CHAR(50),
    SALARY         REAL
 );
```
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。如：创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：
```
 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)
 );
```

蜀中豪杰

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQLITE学习之进阶操作(一)

1、PRAGMA命令PRAGMA语句是SQLITE数据的SQL扩展，是它独有的特性，主要用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。。它采用与SELECT、INSERT等语句一样的形式来发出请求，但也有几个重要的不同：特定的PRAGMA语句可能被移走，新的PRAGMA语句可能在新的版本中添加。因此，后向兼容无法保证。未知的...
复制链接

扫一扫