SQLite

概述

SQLite是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite是一个增长最快的数据库引擎,这是在普及方面的增长,与它的尺寸大小无关。SQLite 源代码不受版权限制。

不需要一个单独的服务器进程或操作的系统(无服务器的),SQLite 不需要配置,这意味着不需要安装或管理,一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件,SQLite 是非常小的,是轻量级的,完全配置时小于 400KiB,省略可选功能配置时小于250KiB,SQLite 事务是完全兼容 ACID 的,允许从多个进程或线程安全访问,SQLite 支持 SQL92(SQL2)标准的大多数查询语言的功能,SQLite 使用 ANSI-C 编写的,并提供了简单和易于使用的 API。

SQLite 可在 UNIX(Linux, Mac OS-X, Android, iOS)和 Windows(Win32, WinCE, WinRT)中运行。

安装SQLite

sudo apt install sqlite3	#linux

常用命令

在完成安装后,就可以使用命令了,这些命令不同于sql语句,它们都以点开头,且不以分号结尾

例如:

$ sqlite3
sqlite >

下面就可以输入一些点命令。输入.help将展示所有的点命令,下面是一些常用的命令:

CommandDescription
.backup ?DB? FILE备份 DB 数据库(默认是 “main”)到 FILE 文件。
.databases列出数据库的名称及其所依附的文件。
.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表,则只转储匹配 LIKE 模式的 TABLE 表。
.exit退出 SQLite 提示符。
.header(s) ON|OFF开启或关闭头部显示。
.timer ON|OFF显示SQL语句执行时间、CPU信息。
.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE?显示所有索引的名称。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表的索引。
.mode MODEMODE 可以是下列之一:column 左对齐的列,tabs 由 Tab 分隔的值,line 每行一个值,insert TABLE 表的 SQL 插入(insert)语句等。
.read FILENAME执行 FILENAME 文件中的 SQL。
.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表。
.show显示各种设置的当前值。
.width NUM NUM为 “column” 模式设置列宽度。
.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。

sqlite_master 表格,主表中保存数据库表的关键信息,并把它命名为 sqlite_master

sqlite>.header on
sqlite>.mode column
sqlite>

执行上述命令后,查询的数据将以表格显示在终端。

有时,由于要显示的列的默认宽度导致 输出被截断,.mode column情况下。此时,您可以使用 .width num, num… 命令设置显示列的宽度。

SQLite语法

SQL 注释以两个连续的 “-” 字符(ASCII 0x2d)开始,并扩展至下一个换行符(ASCII 0x0a)或直到输入结束,以先到者为准,也可以使用 C 风格的注释,/* 开始,并扩展至下一个 */ 字符对或直到输入结束,以先到者为准。SQLite的注释可以跨越多行。

所有的 SQLite 语句可以以任何关键字开始,如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等,所有的语句以分号 ; 结束

数据类型

SQLite是关系数据库,可以看成二维表,下面是它所支持的数据类型。(每个存储在 SQLite 数据库中的值都具有以下存储类之一)

存储类描述
NULL值是一个 NULL 值。
INTEGER值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。
TEXT值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。
BLOB值是一个二进制数据,完全根据它的输入存储。

大多数SQL数据库引擎(除了SQLite以外的所有SQL数据库)使用的是静态的,刚性的类型。由此,使用静态的、刚性的数据库,其存储值的类型就由容器决定,只能存储特定类型的列数据。在SQLite中,数据类型和值本身有关,而不是与容器类型相关。

使用刚性类型的SQL数据库引擎通常会自动地将存储值转换为适当的类型。

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
#刚性类型的数据库会先将字符串“123”转换成整型123,将整型456转换成字符串“456”,然后再去做值插入。

SQLite支持的数据类型只有五种,而其它的大型DBMS支持的数据类型有几十种,那么如果要将其它的数据转换成SQLite下的数据就根本不能实现,所以就将它的数据类型设计为亲和性的,数据类型种类少了系统实现会简单很多,整个系统也就不会太庞大,SQLite所以支持了一种列的“亲和类型”的概念。任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储方式

下表列出了当创建 SQLite3 表时可使用的各种数据类型名称,同时也显示了相应的亲和类型

数据类型亲和类型
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
INTEGER
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB
TEXT
BLOB
no datatype specified
NONE
(不做任何的转换,直接以该数据所属的数据类型进行存储。)
REAL
DOUBLE
DOUBLE PRECISION
FLOAT
REAL
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME
NUMERIC
  • SQLite 没有单独的 Boolean 存储类。相反,布尔值被存储为整数 0(false)和 1(true)。

  • SQLite 没有一个单独的用于存储日期和/或时间的存储类,但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值,以任何表格内展示的类型来存储日期和时间,并且可以使用内置的日期和时间函数来自由转换不同格式。

存储类日期格式
TEXT格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
REAL从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER从 1970-01-01 00:00:00 UTC 算起的秒数。

创建一个数据库

$ sqlite3 MyDataBase.db
sqlite>.open test.db
#打开已存在数据库也是用 .open 命令,以上命令如何 test.db 存在则直接会打开,不存在就创建它。

上面两种方式都会创建数据库,并创建的数据库文件在当前文件夹下。

一旦数据库被创建,您就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中。

sqlite> .database
main: /home/hao/Database/MyDataBase.db
sqlite>

您可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中。

sqlite3 MyDataBase.db .dump > MyDataBase.sql
#上面的命令将转换整个数据库的内容到 SQLite 的语句中,并将其转储到 ASCII 文本文件中
sqlite3 MyDataBase.db < MyDataBase.sql
#可以通过简单的方式从生成的文本文件中恢复

附加数据库

在开发过程中,经常需要多个表之间进行操作。对于同一个数据库来说,相互之间连接表,容易实现。但是对于不同的数据库,使用Sqlite该怎么实现呢?

针对这种需要不同数据库之间的表连接操作的情况,SQLite提供了一种将外部数据库附加到当前数据库连接的机制——ATTACH DATABASE。

ATTACH DATABASE file_name AS database_name;
/*
如果数据库尚未被创建,上面的命令将创建一个数据库,如果数据库已存在,则把数据库文件名称与逻辑数据库 'Alias-Name' 绑定在一起。
打开的数据库和使用 ATTACH附加进来的数据库的必须位于同一文件夹下。
*/

举个例子:

创建两个不同的数据库,并通过链接2个不同数据库内的表来实现查询。

sqlite3 DB1.db
create table user(id integer primary key, password text);
insert into user values (1, '12345678');
.q
sqlite3 DB2.db
create table info (id integer primary key, status text);
insert into info values (1, 'OK');
.q
sqlite3 DB1.db
attach database 'DB2.db' as 'adb';
.table

此时终端显示:

adb.info  user

下面查询登录状态为OK的用户的账号和密码。

select * from user where user.id in (select adb.info.id from adb.info where status = 'OK');

终端显示:

id  password
--  --------
1   12345678

分离数据库

SQLite 的 DETACH DATABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来,连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名DETACH 命令将只断开给定名称的连接,而其余的仍然有效

DETACH DATABASE 'Alias-Name';

下面是执行上述语句前后.databases的执行结果:

main: /home/hao/Database/DB1.db
adb: /home/hao/Database/DB2.db
main: /home/hao/Database/DB1.db

管理表

创建表

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

下面是一个例子:

sqlite> CREATE TABLE COMPANY(
   ...>    ID INT PRIMARY KEY AUTOINCREMENT NOT NULL,
   ...>    NAME           TEXT    NOT NULL,
   ...>    AGE            INT     NOT NULL,
   ...>    ADDRESS        CHAR(50),
   ...>    SALARY         REAL
   ...> );

AUTOINCREMENT 是一个关键字,用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加,关键字 AUTOINCREMENT 只能用于整型(INTEGER)字段

设置约束

**约束是在表的数据列上强制执行的规则。**这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级列级约束仅适用于列,表级约束被应用到整个表。

以下是在 SQLite 中常用的约束:

约束描述
NOT NULL 约束确保某列不能有 NULL 值。
DEFAULT 约束当某列没有指定值时,为该列提供默认值。
UNIQUE 约束确保某列中的所有值是不同的。
PRIMARY Key 约束唯一标识数据库表中的各行/记录。
主键是表中的一个字段,唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值,一个表只能有一个主键,它可以由一个或多个字段组成。当多个字段作为主键,它们被称为复合键
CHECK 约束确保某列中的所有值满足一定条件

下面是一个例子:

CREATE TABLE INFOTABLE(
   ID INT PRIMARY KEY     NOT NULL,
   NAME TEXT UNIQUE    NOT NULL,
   AGE INT CHECK (AGE > 18)     NOT NULL,
   ADDRESS CHAR(50)    DEFAULT 'unknow',
   SALARY REAL    CHECK(SALARY > 0)
);

管理约束

修改表

ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表,使用 ALTER TABLE 语句还可以在已有的表中添加额外的列

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

下面是一个例子:

ALTER TABLE COMPANY ADD COLUMN SEX char(1) check (SEX = 'm' or SEX = 'w');
--新添加的列是以 NULL 值来填充的。

清空表

在 SQLite 中,并没有 TRUNCATE TABLE 命令,但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。

DELETE 命令的基本语法如下:

sqlite> DELETE FROM table_name;

但这种方法无法将递增数归零

当 SQLite 数据库中包含自增列时,会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列:name 和 seq。name 记录自增列所在的表,seq 记录当前序号(下一条记录的编号就是当前序号加 1)。如果想把某个自增列的序号归零,只需要修改 sqlite_sequence 表就可以了。

DELETE FROM sqlite_sequence WHERE name = 'table_name';

删除表

DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称,如下所示:

DROP TABLE database_name.table_name;

下面删除一个表:

drop table user;

CRUD

SQLite 支持标准的sql语句,下面将直接列举它们的实现例子,具体sql语法不再描述。

Insert

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);
--您可以通过在一个有一组字段的表上使用 select 语句,填充数据到另一个表中。下面是语法:
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];
insert into COMPANY (ID, NAME, AGE) values (1, 'hao', 18);
insert into LIST 
	select ID, NAME from COMPANY;

Select

基本查询

SELECT column1, column2, columnN FROM table_name;
select * from sqlite_master;
SELECT CURRENT_TIMESTAMP; --日期表达式返回当前系统日期和时间值,这些表达式将被用于各种数据操作
SELECT COUNT(*) AS "RECORDS" FROM COMPANY; --有几个内置的函数,比如 avg()、sum()、count(),等等,执行被称为对一个表或一个特定的表列的汇总数据计算。
SELECT (15 + 6) AS ADDITION;

DISTINCT 关键字与 SELECT 语句一起使用,来消除所有重复的记录,并只获取唯一一次记录。

SELECT DISTINCT name FROM COMPANY;--将不会出现重复的NAME值

Where子句

SQLite所支持的算术运算:

运算符描述实例
+加法 - 把运算符两边的值相加a + b 将得到 30
-减法 - 左操作数减去右操作数a - b 将得到 -10
*乘法 - 把运算符两边的值相乘a * b 将得到 200
/除法 - 左操作数除以右操作数b / a 将得到 2
%取模 - 左操作数除以右操作数后得到的余数b % a will give 0

SQLite所支持的比较运算符:

运算符描述实例
==检查两个操作数的值是否相等,如果相等则条件为真。(a == b) 不为真。
=检查两个操作数的值是否相等,如果相等则条件为真。(a = b) 不为真。
!=检查两个操作数的值是否相等,如果不相等则条件为真。(a != b) 为真。
<>检查两个操作数的值是否相等,如果不相等则条件为真。(a <> b) 为真。
>检查左操作数的值是否大于右操作数的值,如果是则条件为真。(a > b) 不为真。
<检查左操作数的值是否小于右操作数的值,如果是则条件为真。(a < b) 为真。
>=检查左操作数的值是否大于等于右操作数的值,如果是则条件为真。(a >= b) 不为真。
<=检查左操作数的值是否小于等于右操作数的值,如果是则条件为真。(a <= b) 为真。
!<检查左操作数的值是否不小于右操作数的值,如果是则条件为真。(a !< b) 为假。
!>检查左操作数的值是否不大于右操作数的值,如果是则条件为真。(a !> b) 为真。

下面是SQLite所支持的逻辑运算符:

运算符描述
ANDAND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEENBETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTSEXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
ININ 运算符用于把某个值与一系列指定列表的值进行比较。
NOT ININ 运算符的对立面,用于把某个值与不在一系列指定列表的值进行比较。
LIKELIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOBGLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于,它是大小写敏感的。
NOTNOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN,等等。它是否定运算符。
OROR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULLNULL 运算符用于把某个值与 NULL 值进行比较。
ISIS 运算符与 = 相似。
IS NOTIS NOT 运算符与 != 相似。
||连接两个不同的字符串,得到一个新的字符串。
UNIQUEUNIQUE 运算符搜索指定表中的每一行,确保唯一性(无重复)

下面就可利用算术和逻辑表达式来对记录进行过滤了。

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]
SELECT * FROM COMPANY 
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

Join 子句

Join 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段

链接方式语法名
交叉连接CROSS JOIN
内连接INNER JOIN
外连接OUTER JOIN
  • 虽然 SQL 标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite 只支持 左外连接(LEFT OUTER JOIN)
SELECT ... FROM table1 CROSS JOIN table2 ...
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

下面是一些例子:

--内连接
select Sname, Sdept from student join SC on student.Sno = SC.Sno join Course on course.Sno = SC.Sno where Cname = 'SQLite';
--外连接(只限制join后的表满足on后条件,另一张表无需满足on后条件,不满足条件将放置NULL)
select S.sno, count(SC.Cno) from student as s left join SC on S.Sno = SC.Sno where Sdept = 'COM' group by S.Sno;
--使用as为表起了别名后,其余使用该名字的地方必须使用别名。
--SQLite只支持左外连接,SQL Server支持ANCI方式的外连接,Oracle支持theta方式的外连接
SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;//theta语法

Like子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配,LIKE 运算符将返回真(true),也就是 1。这里有两个通配符与 LIKE 运算符一起使用:

  • 百分号 (%)
  • 下划线 (_)

百分号(%)代表零个、一个或多个数字或字符下划线(_)代表一个单一的数字或字符。这些符号可以被组合使用

下面是一个例子:

select * from COMPANY where ADDRESS like 'N%';

下面一些实例演示了 带有 ‘%’ 和 ‘_’ 运算符的 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 结尾的任意值

Glob 子句

GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配,GLOB 运算符将返回真(true),也就是 1。与 LIKE 运算符不同的是,GLOB 是大小写敏感的,对于下面的通配符,它遵循 UNIX 的语法。

  • 星号 (*)
  • 问号 (?)

星号(*)代表零个、一个或多个数字或字符问号(?)代表一个单一的数字或字符。这些符号可以被组合使用

它的用法和LIKE’类似。

select * from COMPANY where ADDRESS glob 'N*';

Limit 子句

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行

SELECT * FROM COMPANY LIMIT 6;

Order By子句

ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据

select * from COMPANY order by SALARY desc;
#默认升序asc,降序在结尾加上desc

Group By子句

GROUP BY 子句用于与 SELECT 语句一起使用,来对相同的数据进行分组。在 SELECT 语句中,GROUP BY 子句放在 WHERE 子句之后,放在 ORDER BY 子句之前。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

下面是一个例子:

select AGE as age, COUNT(SALARY) as salary from COMPANY
	group by age order by COUNT(SALARY) desc;

Having 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

下面是各子句在 SELECT 查询中的允许出现的位置:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
select AGE as age, COUNT(SALARY) as salary from COMPANY
	group by age having COUNT(SALARY) > 0 order by COUNT(SALARY) desc;

Unions 子句

SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果不返回任何重复的行,每个 SELECT 被选择的列数必须是相同的,相同数目的列表达式,相同的数据类型,并确保它们有相同的顺序

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL 运算符用于结合两个 SELECT 语句的结果,包括重复行。适用于 UNION 的规则同样适用于 UNION ALL 运算符,只需要将上述例子中UNION改为UNION ALL。

  • 上面各种子句主要实现了对数据的筛选,它们筛选信息的部分子句也可以使用在delete和update的条件筛选上。

子查询

子查询或称为内部查询、嵌套查询,指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句,一个 SELECT 语句的查询结果能够作为另一个语句的输入值。子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用,可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000); 
INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY);
UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27);
DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27);

Update

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
update COMPANY set ADDRESS = 'NANJING' where ID = 1;
#使用 UPDATE 语句来设置一些允许空值的值为 NULL
UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Delete

DELETE FROM table_name
WHERE [condition];
DELETE FROM COMPANY WHERE ID = 7;

PRAGMA

PRAGMA 命令是一个特殊的命令,可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取,也可以根据需求进行设置。

PRAGMA pragma_name;	#查询当前的 PRAGMA 值
PRAGMA pragma_name = value;	#为 PRAGMA 设置一个新的值

下面是一些常用的设置:

--case_sensitive_like Pragma 控制内置的 LIKE 表达式的大小写敏感度。默认情况下,该 Pragma 为 false,这意味着,内置的 LIKE 操作符忽略字母的大小写。
PRAGMA case_sensitive_like = [true|false];
--该 Pragma 将返回一个单行三列的表格,每当打开或附加数据库时,会给出数据库中的序列号,它的名称和相关的文件。
PRAGMA database_list;
--控制字符串如何编码及存储在数据库文件中.
encoding Pragma
--返回关于数据库索引的信息,结果集将为每个包含在给出列序列的索引、表格内的列索引、列名称的列显示一行。
PRAGMA [database.]index_info( index_name );
--列出所有与表相关联的索引,结果集将为每个给出列序列的索引、索引名称、表示索引是否唯一的标识显示一行。
PRAGMA [database.]index_list( table_name );
--获取或设置递归触发器功能。如果未启用递归触发器,一个触发动作将不会触发另一个触发。
PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

存储过程

在大型关系数据库中,可以调用存储过程来执行代码中的语句。在嵌入式的SQL语言中,采用SQL语句访问数据库,当某些SQL语句需要被多次重复执行时,应该考虑存储过程,这样避免了SQL语句多次重复的解析,存储过程实际上是预编译的集合。它可以接受输入参数,返回一个或多个结果集。SQL-92标准中包括了存储过程,SQLite只支持部分SQL-92,不支持存储过程

在 SQLite 中,SQL92 不支持的特性如下所示:

特性描述
RIGHT OUTER JOIN只实现了 LEFT OUTER JOIN
FULL OUTER JOIN只实现了 LEFT OUTER JOIN。
ALTER TABLE支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令,不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT
Trigger 支持支持 FOR EACH ROW 触发器,但不支持 FOR EACH STATEMENT 触发器
VIEWs在 SQLite 中,视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。
GRANT 和 REVOKE可以应用的唯一的访问权限是底层操作系统的正常文件访问权限

触发器

SQLite 触发器(Trigger)数据库的回调函数,它会在指定的数据库事件发生时自动执行/调用,当触发器相关联的表删除时,自动删除触发器,BEFORE 或 AFTER 关键字决定何时执行触发器动作,决定是在关联行的插入、修改或删除(完整性约束总是先于触发器操作)之前或者之后执行触发器动作。SQLite 的触发器(Trigger)与标准触发器存在一些差异。

SQLite 只支持 FOR EACH ROW 触发器(Trigger)(每行受影响,触发器都执行,叫行级触发器。),没有 FOR EACH STATEMENT 触发器(Trigger)(语句级触发器)。因此,明确指定 FOR EACH ROW 是可选的。

--创建触发器
CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 --触发器逻辑
END;
--在表名后选择指定 FOR EACH ROW,例如:
CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 --触发器逻辑
END;
--列出触发器
SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';
--删除触发器
DROP TRIGGER trigger_name;

下面是一个例子:

--对COMPANY创建了一个触发器,名字为audit_log,在INSERT完成后触发(指定多个操作用逗号分隔),触发内容是BEGIN-END之间的语句。
--对同一操作可建立多个触发器
CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
--在SQL server中还有前级触发器,这种触发器不会执行触发触发器的SQL语句,而是只执行触发器中的事务,受影响的表将保存在临时表中。

在SQL server中,在触发器中可以使用INSERTED和DELETED表,这两个表结构与建立触发器的表完全相同,分别保存了INSERT、UPDATE和DELETE、UPDATE操作中的数据,可以实现比CHECK更复杂的约束。而在SQLite中它们会被临时保存在new表中。

索引

  • 建立索引的目的是为了加快数据访问速度,索引一般采用B树的结构,它按索引的关键字排序。

    聚集索引:B树自上而下建立,最下层叶子存放数据(数据页),如此上推,直到生成根节点索引页,高一层的节点的索引页中的关键字是下一层索引关键字的最大或最小值。索引建立后,意味着数据按聚集索引项物理排序

    索引建立后,当出现增加或删除数据时,数据库将对索引页分裂合并来保证B树平衡。因此聚集索引适合于:包含大量非重复列,返回大型结果集,经常用于JOIN,ORDRY BY,GROUP BY子句的列,频繁查找的列。

    字节长度很长,数据要频繁改变的列不适合建立聚集索引。这将花费大量开销。

    非聚集索引:数据不按关键字的顺序排序,页节点不存放数据页,每个索引行存放的是索引关键字以及一个或多个行定位器,其定位器所指向的位置就是数据所在物理位置。

    非聚集索引适合于包含大量非重复列,不返回大型结果集,经常用于JOIN,GROUP BY子句的列,频繁作为查找条件。

    从上面描述可以理解:一个表只可能有一个聚集索引,但可有多个非聚集索引。

  • 索引可以创建或删除,但不会影响数据,使用 CREATE INDEX 语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列。

CREATE INDEX index_name ON table_name ... ;

创建索引

  • 单列索引是一个只基于表的一个列上创建的索引。基本语法如下:
CREATE INDEX index_name
ON table_name (column_name);
  • 使用唯一索引不仅是为了性能,同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中,因为只有当数据具有唯一特征时,指定唯一索引才有意义

    基本语法如下:

CREATE UNIQUE INDEX index_name
on table_name (column_name);
  • 组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下:
CREATE INDEX index_name
on table_name (column1, column2);
  • 隐式索引是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束

查看索引

使用 .indices.indexes 命令列出某个表上所有可用的索引,如下所示:

sqlite> .indices COMPANY
#sqlite_autoindex_COMPANY_1 是创建表时创建的隐式索引。

列出数据库范围的所有索引:

SELECT * FROM sqlite_master WHERE type = 'index';

删除索引

一个索引可以使用 SQLite 的 DROP 命令删除:

DROP INDEX index_name;

Indexed By

“INDEXED BY index-name” 子句规定必须需要命名的索引来查找前面表中值

“NOT INDEXED” 子句规定当访问前面的表(包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引)时,没有使用索引。(即使指定了 “NOT INDEXED”,INTEGER PRIMARY KEY 仍然可以被用于查找条目。

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

下面是是一个例子:

SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

视图

视图(View)只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图(View)实际上是一个以预定义的 SQLite 查询形式存在的表的组合,视图(View)是一种虚表。它提供了一定程度的数据逻辑性,增加了数据安全性,为用户提供了从不同角度看问题的方法。

SQLite 视图是只读的,因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器,当尝试 DELETE、INSERT 或 UPDATE 视图时触发,需要做的动作在触发器内容中定义。

创建一个视图:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

下面是一个例子:

CREATE VIEW COMPANY_VIEW AS
    SELECT ID, NAME, AGE FROM  COMPANY;

通过.table就可以看到所创建的视图。

要删除视图,只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下:

DROP VIEW view_name;

事务

事务(Transaction)是一个对数据库执行工作单元。事务(Transaction)是以逻辑顺序完成的工作单位或序列,可以是由用户手动操作完成,也可以是由某种数据库程序自动完成。

事务(Transaction)具有以下四个标准属性,通常根据首字母缩写为 ACID:

  • 原子性(Atomicity):确保工作单位内的所有操作都成功完成,否则,事务会在出现故障时终止,之前的操作也会回滚到以前的状态。
  • 一致性(Consistency):确保数据库在成功提交的事务上正确地改变状态。
  • 隔离性(Isolation):使事务操作相互独立和透明。
  • 持久性(Durability):确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用,因为这些操作在数据库中是自动提交的。

事务(Transaction)可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去,直到遇到下一个 COMMIT 或 ROLLBACK 命令COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令,ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。在数据库关闭或发生错误时,事务处理也会回滚。

下面是一个例子:

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

注入

如果您的站点允许用户通过网页输入,并将输入内容插入到 SQLite 数据库中,这个时候您就面临着一个被称为 SQL 注入的安全问题。注入通常在请求用户输入时发生,比如需要用户输入姓名,但用户却输入了一个 SQLite 语句。

永远不要相信用户提供的数据,所以只处理通过验证的数据,这项规则要通过模式匹配来完成的。必须确保在sql中用户输入的字段是合法的。

Vacuum

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件,然后清空主数据库,并从副本中重新载入原始的数据库文件。这消除了空闲页,把表中的数据排列为连续的,另外会清理数据库文件结构。

如果表中没有明确的整型主键(INTEGER PRIMARY KEY),VACUUM 命令可能会改变表中条目的行 ID(ROWID)

**VACUUM 命令只适用于主数据库,附加的数据库文件是不可能使用 VACUUM 命令。**如果有一个活动的事务,VACUUM 命令就会失败。

VACUUM table_name;

SQLite 的 Auto-VACUUM 与 VACUUM 不大一样,它只是把空闲页移到数据库末尾,从而减小数据库大小。通过这样做,它可以明显地把数据库碎片化,而 VACUUM 则是反碎片化。所以 Auto-VACUUM 只会让数据库更小。

sqlite> PRAGMA auto_vacuum = NONE;  -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL;  -- 1 means enable incremental vacuum
sqlite> PRAGMA auto_vacuum = FULL;  -- 2 means enable full auto vacuum

SQLite函数

时间与日期

SQLite 支持以下五个日期和时间函数:

序号函数实例
1date(timestring, modifier, modifier, …)以 YYYY-MM-DD 格式返回日期。
2time(timestring, modifier, modifier, …)以 HH:MM:SS 格式返回时间。
3datetime(timestring, modifier, modifier, …)以 YYYY-MM-DD HH:MM:SS 格式返回。
4julianday(timestring, modifier, modifier, …)这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。
5strftime(format, timestring, modifier, modifier, …)这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。strftime() 函数也可以把格式字符串 format 作为其第一个参数

一个时间字符串可以采用下面任何一种格式

序号时间字符串实例
1YYYY-MM-DD2010-12-30
2YYYY-MM-DD HH:MM2010-12-30 12:10
3YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.100
4MM-DD-YYYY HH:MM30-12-2010 12:10
5HH:MM12:10
6YYYY-MM-DDTHH:MM
您可以使用 “T” 作为分隔日期和时间的文字字符。
2010-12-30 12:10
7HH:MM:SS12:10:01
8YYYYMMDD HHMMSS20101230 121001
9now2013-05-07

时间字符串后边可跟着零个或多个的修饰符,这将改变有上述五个函数返回的日期和/或时间。下面列出了可在 SQLite 中使用的修饰符:

SQLite 中使用的修饰符
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

SQLite 提供了非常方便的函数 strftime()格式化任何日期和时间。使用以下的替换来格式化日期和时间:

替换描述
%d一月中的第几天,01-31
%f带小数部分的秒,SS.SSS
%H小时,00-23
%j一年中的第几天,001-366
%J儒略日数,DDDD.DDDD
%m月,00-12
%M分,00-59
%s从 1970-01-01 算起的秒数
%S秒,00-59
%w一周中的第几天,0-6 (0 is Sunday)
%W一年中的第几周,01-53
%Y年,YYYY
%%% symbol
--当前月份的最后一天
SELECT date('now','start of month','+1 month','-1 day');
--给定 UNIX 时间戳 1092941466 的日期和时间(定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数)
SELECT datetime(1092941466, 'unixepoch');
--计算当前的 UNIX 时间戳
SELECT strftime('%s','now');
--计算从 2004 年某一特定时刻以来的秒数
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
--计算当年 10 月的第一个星期二的日期
SELECT date('now','start of year','+9 months','weekday 2');
--在 UTC 与本地时间值之间进行转换
SELECT time('12:00', 'localtime');

常用函数

函数描述
COUNT计算一个数据库表中的行数。
MAX返回选择某列的最大值。
MIN返回某列的最小值。
AVG返回计算某列的平均值。
SUM返回一个数值列计算总和。
RANDOM函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
ABS返回参数的绝对值。
UPPER把字符串转换为大写字母。
LOWER把字符串转换为小写字母。
LENGTH返回字符串的长度。
sqlite_version返回 SQLite 库的版本。

SQLite提供的C语言API

操作描述
打开数据库sqlite3_open(const char *filename, sqlite3 **pDb)
打开一个指向 SQLite 数据库文件的连接,返回一个用于其他 SQLite 程序的数据库连接对象。
如果 filename 参数是 NULL 或 ‘:memory:’,那么 sqlite3_open() 将会在 RAM 中创建一个内存数据库,这只会在 session 的有效时间内持续。如果文件名 filename 不为 NULL,那么 sqlite3_open() 将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在,sqlite3_open() 将创建一个新的命名为该名称的数据库文件并打开。
执行SQL语句*sqlite3_exec(sqlite3* pDb, const char *sql, sqlite_callback, void *data, char *errmsg)
执行 SQL 命令,SQL 命令由 sql 参数提供,可以由多个 SQL 命令组成。
在这里,第一个参数 sqlite3 是打开的数据库对象,sqlite_callback 是一个回调,data 作为其第一个参数errmsg 将被返回用来获取程序生成的任何错误。sqlite3_exec() 程序解析并执行由 sql 参数所给的每个命令,直到字符串结束或者遇到错误为止。
关闭数据库sqlite3_close(sqlite3*)
关闭打开的数据库连接。
所有与连接相关的语句都应在连接关闭之前完成。如果还有查询没有完成,sqlite3_close() 将返回 SQLITE_BUSY 禁止关闭的错误消息。

下面是一个例子:

/*
 * @Author: Hao
 * @Date: 2021-02-19 21:10:15
 * @LastEditors: Hao
 * @LastEditTime: 2021-02-19 22:06:47
 * @Description: 
 * @FilePath: /workSta/dbTest.c
 */

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **colName);

int main(int argc, char* argv[])
{
    sqlite3 *db;
    int rc;

    //open database
    rc = sqlite3_open("test.db", &db);
    if( rc ){
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        exit(0);
    }else{
        fprintf(stderr, "Opened database successfully\n");
    }

    char* errMsg = NULL;
    char* sql;

    //create a table
    sql = "CREATE TABLE COMPANY("  \
        "ID INT PRIMARY KEY     NOT NULL," \
        "NAME           TEXT    NOT NULL," \
        "AGE            INT     NOT NULL," \
        "ADDRESS        CHAR(50)," \
        "SALARY         REAL );";
    rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    }else{
        fprintf(stdout, "Table created successfully\n");
    }
    
    //insert some records
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
        "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
        "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
        "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
        "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
        "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
    rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
    if( rc != SQLITE_OK ){
       fprintf(stderr, "SQL error: %s\n", errMsg);
       sqlite3_free(errMsg);
    }else{
       fprintf(stdout, "Records created successfully\n");
    }

    //delete opt
    sql = "DELETE from COMPANY where ID=2;";
    rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    }else{
        fprintf(stdout, "Operation done successfully\n");
    }

    //update data
    sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
         "SELECT * from COMPANY";
    rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    }else{
        fprintf(stdout, "Operation done successfully\n");
    }

    //select opt
    sql = "SELECT * from COMPANY";
    rc = sqlite3_exec(db, sql, callback, sql, &errMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    }else{
        fprintf(stdout, "Operation done successfully\n");
    }

    return 0;
}

/**
 * @description: 
 *     传给sqlite3_exec的回调函数,用来显示查询结果
 *     对每一条查询结果调用一次该回调函数
 * @param {
 *     回调函数的格式如下:
 *     int sqlite_callback(
 *     void* pv, 由 sqlite3_exec() 的第四个参数传递而来
 *     int argc, 表的列数 
 *     char** argv, 指向查询结果的指针数组, 可以由 sqlite3_column_text() 得到
 *     char** col   指向表头名的指针数组, 可以由 sqlite3_column_name() 得到
 *     );
 * }
 * @return {
 *     返回值:
 *     1:中断查找
 *     0:继续列举查询到的数据
 * }
 */
static int callback(void *data, int argc, char **argv, char **colName){
    int i;
    puts((char*)data);
    for(i=0; i<argc; i++){
        printf("%s = %s\n", colName[i], argv[i] ? argv[i] : "NULL");
    }
   return 0;
}

#END

  • 1
    点赞
  • 1
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页

打赏作者

'Best Wishes

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值