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

        说起数据库,那自然就会想到SQL语句,说起SQL语句,那又不得不提SQL的标准或版本, 一般来讲,SQL语句有两个标准或版本:SQL92SQL99,两者有何区别?以我个人来说,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的运行,这是重中之中,也是主要目的】

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值