【一起学SQLite】--SQLite中一些与众不同的特性(2)

gz_SkyWin

已于 2022-10-15 16:36:17 修改

阅读量962

点赞数 1

分类专栏： SQLite 文章标签： sqlite 数据库 sql

于 2022-09-24 00:09:11 首次发布

本文链接：https://blog.csdn.net/gzhubin/article/details/127019309

版权

SQLite 专栏收录该内容

4 篇文章 3 订阅

订阅专栏

说起数据库，那自然就会想到SQL语句，说起SQL语句，那又不得不提SQL的标准或版本，一般来讲，SQL语句有两个标准或版本：SQL92和SQL99，两者有何区别？以我个人来说，SQL92书写精简易阅读，SQL99功能较多效率比较高，但事实上，你写SQL92时，很多数据库自动会对你写的SQL语句进行优化的，如SQL Server，我一般情况下常写SQL92，很少写SQL99，除非语句特别的复杂，至于SQL语句优化，一则是自身功底，二则是供助第三方软件或工具，比如：我比较常用的是在SQL Server新建视图放入SQL语句，不保存点“执行”看看自动优化后的结果，一般会事办功倍，特别是使用SQL Server时...
废话不讲了，下面我就简单的讲一些SQLite中一些与众不同的特性(想到什么就写什么，请见谅，要详情或专业的，请参阅官方的E文说明)：

1、【第三方的工具】：磨刀不误砍柴功，柴刀在手，哪都可以走...。SQLite因开源免费及精简，所以，官方是没有什么UI工具的(至于自带的命令行，高高手可以玩玩)，类似SQL Server的SQL Server Management Studio之类的，你只能想想就行了......，So，只能使用第三方的工具，目前第三方的软件或工具主要有：

1）、SQLiteSpy
SQLiteSpy是一个快速和紧凑的数据库SQLite的GUI管理软件。它的图形用户界面使得它很容易探讨，分析和操纵sqlite3数据库，支持 Unicode。免费！最新版本为：SQLiteSpy 1.9.16(6 Jul 2022)，SQLiteSpy 官方详情和下载地址是：SQLiteSpy: Version History [Yunqa • The Delphi Inspiration]
2）、SQLiteStudio
SQLiteStudio 是一个开源、跨平台的 SQLite 数据库的管理工具，支持导出数据格式：CSV、HTML、XML、SQL、PDF、JSON、dBase等。可同时打开多个数据库文件，支持查看和编辑二进制字段。这个工具不错，我一直在使用！当前最新版为：SQLiteStudio 3.3.3(12 April 2021)，支持SQLite (3.35.4) ，更新有点慢，不过，开源免费也就这样了，官方介绍及下载网址为：SQLiteStudio
3）、SQLite Expert
SQLite Expert 提供两个版本，分别是个人版和专业版。其中个人版是免费的，提供了大多数基本的管理功能。我没用过，听同事说，不如SQLiteStudio好用。
4）、。。。。，懒得说了，自己百度吧

2、【SQLite中一些与众不同的特性】：粗想一下，这个比较杂乱了，不知从何说起，只能想到什么就说什么了，下面我就分条来说说吧，不足之处，各位只能自己百度一下了。
1）、SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义；
2）、SQL 注释有两种格式：--注释内容或者/*注释内容*/；
例如：
--查询BsEmployee表
/*查询BsEmployee表*/
SELECT * FROM BsEmployee;
3）、所有的语句以分号(;)结束，所有的 SQLite 语句以关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，以分号(;)结束。
例如：
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) VALUES (1, 'Paul', 32, 'California' );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) VALUES (2, 'Allen', 25, 'Texas' );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) VALUES (3, 'Teddy', 23, 'Norway' );
SELECT * FROM table_name;
DROP TABLE database_name.table_name;
4）、没有存储过程，当也不支持IF...ELSE 和 WHILE 等语句了。
  但支持视图，SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。这个跟SQL Server有区别要注意。
还有支持触发器（Trigger），SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的；
5）、Limit 子句：语法如下：
SELECT column1, column2, columnN FROM table_name
LIMIT [no of rows] OFFSET [row num]
6）、没有TRUNCATE TABLE 命令，只能使用 DELETE 命令从已有的表中删除全部的数据，删除的数据过多时，建议配合使用VACUUM命令来压缩数据库文件或空间（清除未使用的空间）；
7）、SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置，常用的或例子如下：
PRAGMA table_info(sqlite_master);
8）、sqlite_master是一个特殊表, 存储数据库的元信息, 如表(table), 索引(index), 视图(view), 触发器(trigger), 可通过select查询相关信息，例如：
select * from sqlite_master where type="table";
--通过sqlite_master表判断特定的表、视图或者索引是否存在
select count(*) from sqlite_master where type = 'table' and name = '表名';
9）、但是SQLite不支持if语句，我们可以使用case when来实现，如下：
UPDATE table_name
SET column1 = CASE
   WHEN column1 < 1 THEN 0
   ELSE column1 - 1
END
WHERE columnID = 1
10）、sqlite部分特色函数

sqlite部分特色函数
coalesce(X,Y,...)	返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数
ifnull(X,Y)	返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。与coalesce()类似。
length(X)	返回X的长度，以字符计。如果SQLite被配置为支持UTF-8，则返回UTF-8字符数而不是字节数
like(X,Y [,Z])	用于实现SQL语法"X LIKE Y [ESCAPE Z]".若使用可选的ESCAPE子句，则函数被赋予三个参数，否则只有两个。可使用sqlite3_create_function() 重载该函数从而改变LIKE运算符的功能。注意同时重载like()的两参数和三参数版本，否则在使用/不使用 ESCAPE子句时，LIKE运算符的实现可能使用的是不同的代码
nullif(X,Y)	当两参数不同时返回X，否则返回NULL
quote(X)	返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号，在内部的引号前会加入逃逸符号。 BLOB被编码为十六进制文本。当前的VACUUM使用这一函数实现。在使用触发器实现撤销/重做功能时这一函数也很有用
round(X,Y)	将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0
substr(X,Y,Z)	返回输入字符串X中以第Y个字符开始，Z个字符长的子串。 X最左端的字符序号为1。若Y为负，则从右至左数起。若SQLite配置支持UTF-8，则“字符”代表的是UTF-8字符而非字节
max(X,Y,...)	返回最大值。参数可以不仅仅为数字，可以为字符串。大小顺序由常用的排序法则决定。注意，max()在有2个或更多参数时为简单函数，但当仅给出一个参数时它变为聚集函数

11）、数据库锁：SQLite采用粗粒度锁，当一个连接要写数据库时，所有其他的连接都被锁住，直到写连接结束它的事务。SQLite有一个加锁表，帮助不同的写数据库能够在最后一刻加锁，以保证最大的并发性。SQLite有五种不同的锁状态：未加锁、共享、预留、未决和排它。数据库在同一时刻只能有一种锁状态。
(1)、未加锁：最初的状态，连接却没有访问数据库或者已经begin一个事务时，都是未加锁状态。
(2)、共享锁：为了从数据库读数据，连接必现进入共享锁，多个连接可以同时获得并保持共享锁，多个连接可以同一时刻同时从一个数据库读数据。但是如果有共享锁没有释放就不可以写数据。
(3)、预留锁：如果想要写数据，必须获取一个预留锁，一个数据库只能有一个预留锁，预留锁可以与共享锁共存，他不会影响其他连接继续获取共享锁。一旦连接获取到预留锁就可以写数据库，但是只写到缓存中，并没有写到磁盘中。
(4)、排它锁：当一个连接写完数据想要提交时候，就需要从预留锁提升为排它锁。但是在这之前需要先提升为未决锁，获得未决锁之后其他连接就不可以继续获得共享锁，但是拥有共享锁的仍可以继续工作。此时拥有未决锁的连接等待其他获得共享锁的连接释放共享锁。当其他共享锁都释放完之后，未决锁提升为排它锁，此时就可以对数据库进行修改，然后保存到数据库文件。
12）、Replace Into 或者 Insert Or Replace Into语句：SQLite也支持这样的语句，这是很有意思的语句，在SQLite数据库时，可以创建唯一索引，使用replace into 来替代insert和update，其类似在MSSQL中的：
IF NOT EXISTS( SELECT * FROM Book WHERE ….) THEN INSERT INTO ... ELSE UPDATE SET ...
【注意事务或特别说明】：
(1)、replace语句会删除原有的一条记录，并且插入一条新的记录来替换原记录;
(2)、一般用replace语句替换一条记录的所有列，如果在replace语句中没有指定某列，在replace之后这列的值被置空;
(3)、replace根据主键确定被替换的是哪一条记录;
(4)、replace语句不能根据where子句来定位要被替换的记录;
(5)、如果执行replace语句时，不存在要替换的记录，那么就会插入一条新的记录;
(6)、如果新插入的或替换的记录中，有字段和表中的其他记录冲突，那么会删除那条其他记录;
(7)、replace语句和update语句的对比：
a)、对于update语句，因为经常使用到，应该算比较熟悉。下面对比一下update和replace语句的行为，只是简单陈述，不再以具体实例说明。
b)、update语句使用where子句定位被更新的记录；
c)、update语句可以一次更新一条记录，也可以更新多条记录，只要这多条记录都复合where子句的要求；
d)、update只会在原记录上更新字段的值，不会删除原有记录，然后再插入新纪录；
如果在update语句中没有指定一些字段，那么这些字段维持原有的值，而不会被置空；
13）、SQLite 支持多线程 ，SQLite官方多线程参考文档，SQLite官方参数设置说明参考，这里需要提及的是 Synchronous 参数的三个选项分别为：0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA，实际测试，如果设置为normal 或者full的情况下，多线程高并发时，会导致效率极其低下。 SQLite官方文档说明显示，这三个选项主要是意外中止sqlite（掉电，强退程序等），有可能会导致数据库损坏。
如果是使用delphi官方的FireDAC来读写SQLite时，建议开启为 full模式，是因为需要让其他线程可见。实际测试，分别建立100个读和100个写线程(真的有很多多线程时，我想用SQLite就没必要了吧)，同时读写，Synchronous=off 的情况下，数据仍然是同步的，其他线程写的时候，读线程仍然可以立即得到最新的数据状态。仍然是接受sqlite的建议，在数据安全要求不太严的情况下，设置为off模式，网上有人测试，讲off比full模式，效率要高50倍以上。如果没有忘记的话，我在后面的有关使用FireDAC来读写SQLite时，再重点讲一讲。
14）、SQLite 的数据库编码方式：SQLite数据库支持UTF-8编码和UTF-16编码(含UTF-16BE、UTF-16LE)，一般采用UTF-8编码，具体各编码的区别请百度了解。这点大家要特别的注意，网上众多的乱码之类的，其要源就在于此。

有关SQLite的基础教程部分，就到处为止了，我就不想再写了，因为网上一堆堆的，随便百度一下就有，如有需要的，自行百度，同时下面我附上几个：【SQLite 教程】【官网帮助文档】【SQL语句(官网)】、【SQL Functions(官网)】、【SQLite C Interface Objects(官网)】、【SQLite C Interface Functions(官网)】，建议去官网看更新版本以及相关说明，这个实时是最新的，特别是之前不支持的功能，新版本支持了还有bug...

【PS：接下来，主要讲在Delphi 下的SQLite的运行，这是重中之中，也是主要目的】