SQLite 简介-程序员宅基地

技术标签: sqlite  前端  

SQLite简介

常见的关系型数据库有SQLite,MySQL,SQL Server等,通常学习关系型数据库时不会使用SQLite,但是SQLite 是世界上使用最广泛的数据库引擎。SQLite 内置于所有手机和大多数计算机中,并捆绑在人们每天使用的无数其他应用程序中。SQLite 是一个由C语音开发的嵌入式库,具有小型、 快速、 自包含、 高可靠、 功能齐全等特点。

文章以3.32.2版本为例讲述SQLite的一些基本知识

SQLite常用命令和示例

在命令行中使用SQLite命令,需要先执行sqlite3命令,进入SQLite提示符

创建或打开数据库

有两种方法可以创建或打开数据库,一个是在sqlite3命令后面加上数据库路径,另一个是使用点命令.open

sqlite3 /…/xxx.db法

通过执行这个命令进入SQLite提示符时,如果数据库文件已经存在,则直接打开对应数据库,否则不会立即在对应路径创建xxx.db文件。要等到执行了添加数据表,视图等数据库对象的命令之后。

  • 示例

    先执行如下命令,此时没有创建出comms_ease.db文件

sqlite3 comms_ease.db

​ 再执行如下命令创建一张表,在当前目录出现comms_ease.db文件

.open /…/xxx.db法

使用.open是一个点命令,使用它需要先执行sqlite3命令进入SQLite提示符.open命令的使用方式也是在命令后面追加数据库路径,不过和sqlite3 /.../xxx.db法不同的是,执行.open命令后,数据库文件会被直接创建出来,不需要再创建数据库对象。

创建表

SQLite的创建语句为CREATE TABLE,完整的创建表语句内容丰富,除了创建普通表外,还能具备判断表是否已经存在,创建临时表等能力。常见的创建普通表的句式为。

CREATE TABLE 表名 (
        列1名称 列类型 以空格隔开的一个或多个列约束, 
        列2名称 列类型 以空格隔开的一个或多个列约束,
        ...
);

默认情况下,一张表的最大列数为2000,每一行能存下的最大字节数为十亿,能满足绝大多数的需求,创建普通表的示例如下

CREATE TABLE table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

例子中,创建了一个名为table_comms_ease的数据表,包含两列,第一列是id,类型是整形,不能为空,而且是表的主键,并可以自动生成; 第二列是value,类型为字符串

在表名已经存在的情况下,调用CREATE TABLE 表名语句会报错,要避免,可以使用CREATE TABLE IF NOT EXISTS 表名语句。如果不存在,则创建表,如果存在,则什么都不做。示例如下

CREATE TABLE IF NOT EXISTS table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

如果希望临时存储一些数据,而且只对当前连接有效,可以试试临时表。临时表的创建语句为CREATE TEMP TABLE。临时表只对当前数据库连接有效,重新建立连接或者同时存在的其他连接都无法访问到。示例如下

CREATE TEMP TABLE temp_table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

其它常用点命令

点命令是SQLite数据库独特的命令形式,它们通常比较简单,而且不需要以分号结尾,常见的点命令如下表所示

命令 描述 使用示例
.backup 备份数据库到文件 .backup comms_ease_backup
.databases 列出数据库的名称及其所依附的文件 .databases
.exit 退出SQLite提示符 .exit
.help 帮助 .help
.quit 退出SQLite提示符 .quit
.show 各种设置的值 .show
.schema 查看创建命令。以LIKE`的模式匹配参数,如果没有参数,则查看所有表格的创建命令 .schema .schema table%
.tables 搜索表名。以LIKE`的模式匹配参数,如果没有参数,则搜索所有表名(临时表不会出现在结果中) .tables .tables table%

向表内添加一行,即为每一列构建一个值,并填入新的一行中。添加行的命令是INSERT,添加方式有三种,一种是指定值添加;二是添加Select语句的结果;三是默认添加。

  1. 指定值添加就是指定部分或者所有列的值,剩下列使用默认值的方式,对于没有通过DEFAULTEAUTOINCREMENT等方式声明默认值的列,如果没有指定NOT NULL则填入NULL,否则报错。至少需要指定一列的值,如需全部填写默认值,可以使用默认添加方式。需要注意的是,值的顺序要和列的顺序保持一致。示例如下:

    INSERT INTO table_comms_ease (value) VALUES ('value of 1');
    INSERT INTO table_comms_ease (value, id) VALUES ('value of 2', 2);
    

    如果需要指定填入所有列的值,也可以不把列名列举出来,将值按创建表时各个列的顺序列举出来即可。示例如下:

    INSERT INTO table_comms_ease VALUES (3, 'value of 3');
    
  2. 通过添加加Select语句的结果添加一行的格式为INSERT INTO 表名 SELECT ...通过此方法添加一行时,默认值不会被自动填充,SELECT语句查出的数据量必须和表的列数一致。假设已经存在表operator包含列keydescription而且某一行的key为’.backup’,description备份数据库到文件,给出示例如下:

    INSERT INTO table_comms_ease SELECT  4, description FROM operator WHERE key IS '.backup';
    
  3. 默认添加的格式为INSERT INTO 表名 DEFAULT VALUES;为每一列都填入默认值,如果没有特别声明默认值,则填入NULL。示例如下:

    INSERT INTO table_comms_ease DEFAULT VALUES;
    

通过创建表章节的示例语句创建出数据表,再依次调用上述示例语句,则数据表内会出现5行数据,如下

id value
1 value of 1
2 value of 2
3 value of 3
4 备份数据库到文件
5

删除表内的一行,命令是DELETE,常用格式为

DELETE FROM 表名 WHERE 过滤语句

删除命令本身比较简单,指定表名和删除条件即可删除一列,如下示例表示如果value列中的值有value of开头,则删除。

DELETE FROM table_comms_ease WHERE value LIKE 'value of %';

如上命令操作后,table_comms_ease表还剩的数据为

id value
4 备份数据库到文件
5

修改表内数据的命令为UPDATE,其常用格式为

UPDATE 表名 SET 一个或多个列的赋值 WHERE 过滤语句

修改多列内容时可以采用先写出列名,再按顺序赋值的方式,也可以采用一列一列修改的方式。如果要更新id为4的所在行的值,设置id123valuenew value,两种修改方式分别如下

UPDATE table_comms_ease SET (id, value)= (123, 'new value') WHERE id = 4;

或者

UPDATE table_comms_ease SET id=123, value='new value' WHERE id = 4;

修改后,table_comms_ease表中的数据为

id value
5
123 new value

修改单列的方法和修改多列的方法相似,比如将id为5这一行的value也修改为new value,可以如下操作

UPDATE table_comms_ease SET (value)=('new value') WHERE id = 5;

或者

UPDATE table_comms_ease SET value='new value' WHERE id = 5;

修改后,table_comms_ease表中的数据为

id value
5 new value
123 new value

查询语句的命令是SELECT,它不会修改数据库,结果的行数在自然数范围内,每一行代表一个查询结果。SELECT命令的常用格式为

SELECT 去重策略 列名列表 FROM 表名或者子查询语句 WHERE 过滤语句 ORDER BY 排序策略 LIMIT 数量限制

查询语句中可用的配置比较多,但是大都不是必须的。查询table_comms_ease表的所有内容只需要如下命令即可

SELECT * FROM table_comms_ease;

上面命令中的*表示所有列,命令相当于

SELECT id,value FROM table_comms_ease;

结果为

id value
5 new value
123 new value

另外,可以通过VALUES语句构建一个查询结果,结果的列名为column1, column2, column3等等。比如

VALUES (1,2,3),('a','b','c');

的结果为

column1 column2 Column3
1 2 3
a b c
去重策略

去重策略有两种,一种是默认策略ALL,代表不去重;另一种是DISTINCT,代表去重。table_comms_ease表中value列的值相同,使用ALLDISTINCT分别查询value列时,命令和结果如下:

ALL命令

SELECT ALL * FROM table_comms_ease;

结果为

value
new value
new value

DISTINCT命令

SELECT DISTINCT value FROM table_comms_ease;

结果为

value
new value

可以看出在有重复结果时,ALL策略会保留所有结果,而DISTINCT策略只保留其中一个

表名或者子查询语句

查询语句的FROM关键字后面可以跟表名或者子查询语句,用于限制查询范围。当填写表名时,可以填写多个表名,用逗号或者连接运算符分隔。当填写查询语句时,可以视为先查询出一张表,再从此表中查询出数据。

假设还有一张表table_comms_ease_1,列信息和table_comms_ease表相同,值为

id value
1 value of 1 in table_comms_ease_1
2 value of 2 in table_comms_ease_1

则此字段填写table_comms_ease,table_comms_ease_1时得到如下命令

SELECT * FROM table_comms_ease, table_comms_ease_1;

结果为

table_comms_ease.id table_comms_ease.value table_comms_ease_1.id table_comms_ease_id.value
5 new value 1 value of 1 in table_comms_ease_1
5 new value 2 value of 2 in table_comms_ease_1
123 new value 1 value of 1 in table_comms_ease_1
123 new value 2 value of 2 in table_comms_ease_1

当此字段填写两个子查询语句,如一个是id为5,另一个是id为123时,则得到如下命令

SELECT * FROM (SELECT * FROM table_comms_ease WHERE iD=5), (SELECT * FROM table_comms_ease WHERE iD=123);

结果为

table_comms_ease.id table_comms_ease.value table_comms_ease.id table_comms_ease.value
5 new value 123 new value

将子查询语句的结果视为一张表,则可以统一对两种填写格式的理解。另外查询命令也支持混合填写表名和查询语句。

排序条件

排序条件决定了结果的排列顺序,常用格式如下

ORDER BY 列名 COLLATE 比较方式 排序方式 NULL值的排序方式

比较方式有三种,分别为BINARY, NOCASERTRIM

  • BINARY:使用标准C库中的memcmp()函数逐字节比较
  • NOCASE:先把ASC II码中的大写字母转为小写字母,再按照BINARY方式比较
  • RTRIM:去掉末尾空格后按照BINARY方式比较

通过下面命令为表table_comms_ease添加几条数据,

INSERT INTO table_comms_ease VALUES (6, 'A'), (7, 'new value '), (8, 'Z');

则表中的数据变为

id value
5 new value
6 A
7 new value
8 Z
123 new value

注意:id为7的一行对应的value的末尾有一个空格

如下示例展示了三种不同比较方式的区别

BINARY命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE BINARY;

结果:

id value
6 A
8 Z
5 new value
123 new value
7 new value

NOCASE命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE NOCASE;

结果:

id value
6 A
5 new value
123 new value
7 new value
8 Z

RTRIM命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE RTRIM;

结果:

id value
6 A
8 Z
5 new value
7 new value
123 new value
数量限制

数量限制语句可以限制查询结果的行数,常用格式如下

LIMIT 数量 OFFET 偏移量

设数量为n,偏移量为o,则上面格式的意义是从第o+1条开始,取最多n条数据,如果没有符合条件的数据,则结果为空。

限制数量为3,得到如下表达式

SELECT * FROM table_comms_ease LIMIT 3;

结果为

id value
5 new value
6 A
7 new value

由于表的总行数是5,所以如果限制数量≥5,则会查出整张表。

如果限制数量为3,同时指定偏移量为1,得到如下表达式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 1;

结果过滤掉第一条数据(5, newvalue),并向后取3条,得到

id value
6 A
7 new value
8 Z

如果限制数量为3,同时指定偏移量为3,得到如下表达式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 3;

结果过滤掉前三条数据,并向后取3条,但是后面只有2条,所以得到

id value
8 Z
123 new value

如果偏移量≥5,则什么都查不到

SQLite常见限制

类别 限制 备注
字符串长度 1亿 由宏SQLITE_MAX_LENGTH定义,可以提高或降低限制,最大到231-1
单行最大字节数 1亿 由宏SQLITE_MAX_LENGTH定义
最大列数 2000 由宏SQLITE_MAX_COLUMN定义,可以提高或降低限制,最大到32767
语句最大长度 10亿 由宏SQLITE_MAX_SQL_LENGTH定义,可以降低限制
连接中最大表数 64 不可改变
表达式树的最大深度 1000 由宏SQLITE_MAX_EXPR_DEPTH定义,可以降低或消除限制
函数的最大参数数 100 由宏SQLITE_MAX_FUNCTION_ARG定义,可以提高,最大到 127
复合 SELECT 语句中的SELECT数 500 由宏SQLITE_MAX_COMPOUND_SELECT定义,可以降低
库文件最大页数 1073741823 由宏SQLITE_MAX_PAGE_COUNT定义,可以提高或降低限制,最大到4294967294。
最大数据库大小 281TB 结合最大页数4294967294和最大页面大小65536,得到最大数据库大小为281TB,但是这是个理论值,未经官方验证过。
表中的最大行数 2^64 无法达到,会先达到281TB的数据库大小限制

数据库中的B树

B树与B+树简介

B树是一种平衡多路查找树,每个结点包含三个部分:键,值,指向子结点的指针。假设一个B树结点中有n个键,则它同时有n个值。如果这是一个叶子结点,则它没有指向子结点的指针,否则有n+1个指向子结点的指针。下图为n==2时的结点情况。

三路B树结点.jpg
图中键1键2两个值需要满足键1<键2值1值2分别与键1键2对应;三个指向子结点的指针,分别指向具有不同范围的的子结点。子结点1中的都小于键1子结点2中的都大于键1,且小于键2子结点3中的都大于键2。如下图提供了一个3路B树的示例。

上图中每个结点有三排,第一排是;第二排是;第三排是指向子结点的指针。根节点有50100两个,因此它的左子树中结点的都小于50;中子树中结点的都大于50且小于100;右子树中结点的都大于100

B+树和B树类似,但是B+树的内部结点中只有指向子结点的指针,而叶子结点具有指向下一组值的指针,即只在叶子结点上存储数据。因此父结点中的键还会再出现在子结点上。而且B+树的叶子结点的指向下一组值的指针,将所有都串成了一个链表。由于内部结点不需要存储,B+树可以存储更多的。下图用5路B+树存储了上面3路B树的内容。

五路B+树.jpg

B树页

数据库文件由一页或多页组成。同一个数据库中,每页的大小相同,都是 在512 和 65536 之间,并且为2的整数次幂。数据库的页分为锁定字节(lock-byte)页、freelist页、B树页、负载溢出页和指针映射页。

B树算法为SQLite提供了键值存储模式,而且保证了键的有序性和唯一性。SQLite数据库中B树的结点就是一个页面,所以指向的子结点的指针实际上是对应页面的页码。SQLite数据库使用了两种B树变体,在叶子节点存储数据的表B树和不存储数据的索引B树。一颗完整的树只能是完全的表B树或者完全的索引B树

表B树和索引B树

表B树类似B+树,只将值存放在叶子结点中;索引B树是一颗没有值的树,键就是数据本身,因此索引B树其实类似于B树。下表列出了它们之间的一些差异。

对比项 表B树 索引B树
键长度(byte) 8 最长2147483647的随机值
值存放点 叶子结点 没有值,键就是数据
值长度 最长2147483647 没有值,键就是数据
叶子结点结构 键和值
主要使用场景 rowid表 索引、WITHOUT_ROWID表
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/netease_im/article/details/123741168

智能推荐

element 自定义主题出现样式重复引入问题_element样式引入多次app.css chunk-element-程序员宅基地

文章浏览阅读3.1k次。当自定义主题色的时候按照命令改变主题色,会出现样式重复加载多次问题,看着很恶心人 解决方案去element-ui官网在线设计主题色,下载下来就行https://element.eleme.cn/#/zh-CN/theme/preview ..._element样式引入多次app.css chunk-element

使用GEE(google earth engine)提取某个地区的NDVI值的代码_gee得到一个市的-程序员宅基地

文章浏览阅读1.6w次,点赞22次,收藏173次。其中gz为你导入GEE的矢量文件,L8为landsat的影像,你可以根据自己的要求去获得特定地区的特定卫星的NDVI值。比如说:北京市的哨兵NDVI//定义addNDVI函数,该函数的输入是image,然后对image执行NDVI计算,并将结果命名为ndvi//并将生成的ndvi添加到原有的image中var addNDVI = function(image) { var ndvi..._gee得到一个市的

How to get rid of 'Enter password to unlock your login keyring' in Ubuntu(转)-程序员宅基地

文章浏览阅读121次。http://karuppuswamy.com/wordpress/2010/06/18/enter-password-to-unlock-your-keyring-prompt-in-ubuntu/Ubuntu uses Centralized password management tool called Seahorse. This tool is a front end..._wordpress front end change password

jQuery事件处理概述_jq 再次点击-程序员宅基地

文章浏览阅读1k次。jQuery事件1 概述2 页面载入完毕响应事件2.1 window.onload2.2 $(document).ready()事件3 绑定与反绑定事件监听器3.1 概述3.2 绑定事件3.3 jQuery事件处理函数的好处3.4 反绑定事件3.5 一次性的事件绑定4 事件触发器4.1 trigger触发事件4.2 triggerHandler触发事件4.3 Event Helpers快捷事件5 事件的交互处理5.1 hover:模仿鼠标悬停6 事件对象6.1 使用jQuery事件对象6.2 Javascr_jq 再次点击

android spinner自定义样式,android自定义Spinner下拉菜单(下拉列表框)样式-程序员宅基地

文章浏览阅读963次。在Android中Spinner就是下拉菜单,它相当于HTML中的标签。Android中提供的Spinner Widget下拉菜单已经非常好用了,样式也适用,不过我们还是可以通过定义xml的方式来改变下拉菜单的样式。在layout目录下新建一个xml文件,名字随便(我这里叫myspinner.xml)。在这个文件里面可以定义下拉菜单的样式我们这里采用TextView来实现..._android spinner样式

sql server赋予用户备份数据库和还原数据库权限_sql新建的用户没有备份权限怎么办-程序员宅基地

文章浏览阅读4.7k次。sql server赋予用户备份数据库和还原数据库权限sa用户下,新建一个用户确定即可赋权(sa用户赋权)grant backup database to qq; ----把备份数据库的权限赋予给qqgrant create database to qq; ---吧创建数据库的权限赋予给qq两个权限都要在master数据库..._sql新建的用户没有备份权限怎么办

随便推点

./HelloWorld line 1: syntax error: "(" unexpected问题解决_./kconf/conf: 1: syntax error: "(" unexpected-程序员宅基地

文章浏览阅读1w次。在pc机上编译的程序,linux下执行时出现了这个错误:#./HelloWorld./HelloWorld line 1: syntax error: "(" unexpected这个错误是由于编译程序所使用的编译器是gcc,使用gcc编译的程序可以在pc机上运行而不能在开发板上运行;而要想程序在开发半上运行就需要使用编译器arm-linux-gcc进行编译

POj 3164 Command Network最小树形图 模板题 朱刘算法_最小树形图习题-程序员宅基地

文章浏览阅读129次。Command NetworkAfter a long lasting war on words, a war on arms finally breaks out between littleken’s and KnuthOcean’s kingdoms. A sudden and violent assault by KnuthOcean’s force has rendered a total failure of littleken’s command network. A provisional_最小树形图习题

省市县三级联动获取地址_省市县三级联动获取参保地区划-程序员宅基地

文章浏览阅读726次。1.jsp代码前端传参格式:(type,codeId)type:三种类型(“province” || “city” || “area”)codeId:为选择省的时后自动传入.后台返回JSON数据格式:&amp;lt;%@ page contentType=&quot;text/html;charset=UTF-8&quot; language=&quot;java&quot; %&amp;gt;&amp;lt;html&amp;g_省市县三级联动获取参保地区划

petshop4.0 各个文件的作用-程序员宅基地

文章浏览阅读43次。petshop4.0 各个文件的作用petshop4.0设计说明 (转) 一、项目名称及描述:(实现步骤为:4-3-6-5-2-1)1、WEB=表示层2、BLL=业务逻辑层3、IDAL=数据访问层接口定义4、Model=业务实体5、DALFactory=数据层的抽象工厂(创建反射)6、SQLServerDAL=SQLServer数据访问层 / OracleDAL=Ora...

Could not load dynamic library ‘cudart64_101.dll‘; dlerror: cudart64_101.dll not found_could not load dynamic library 'cudart64_101.dll';-程序员宅基地

文章浏览阅读343次。tensorflow/stream_executor/platform/default/dso_loader.cc:55] Could not load dynamic library ‘cudart64_101.dll’; dlerror: cudart64_101.dll not found(在Pycharm上运行tensorflow_gpu报错:dll文件没有找到)我是一个刚入门tensorflow_gpu的小白,在花了好长时间才把anaconda,CUDA10.0,Cudnn,Pycharm202_could not load dynamic library 'cudart64_101.dll'; dlerror: cudart64_101.dll

Vue 腾讯云上传视频 element-ui_elemet ui 腾讯短视频api 上传-程序员宅基地

文章浏览阅读292次,点赞2次,收藏3次。Vue 腾讯云上传视频文件 使用element-ui组件下载 腾讯云包npm i cos-js-sdk-v5 --savetemlpate 部分 <el-upload :on-remove="handleVodRemove" :before-remove="beforeVodRemove" :on-exceed="handleUploadExceed" :file-list="fileList" :on-change="handleChange" acti_elemet ui 腾讯短视频api 上传