SQLite上手

zhangliang
1,839 阅读12分钟
  • SQLite是一款非常 轻量级 的关系数据库系统，支持多数SQL92标准。
  • SQLite是一款轻型的嵌入式关系数据库，轻量级，效率高，操作起来也特别方便。
  • SQLite的主要应用场景有作为手机应用的数据库以及小型桌面软件的数据库。

SQLite在使用前不需要安装设置，不需要进程来启动、停止或配置，而其他大多数SQL数据库引擎是作为一个单独的服务器进程，被程序使用某种内部进程通信(典型的是TCP／IP)，完成发送请求到服务器和接收查询结果的工作，SQLite不采用这种工作方式。使用SQLite时，访问数据库的程序直接从磁盘上的数据库文件读写，没有中间的服务器进程。使用SQLite一般只需要带上一个dll，就可以使用它的全部功能。

SQLite 安装

windows安装

  1. 去官网下载安装包，下载地址：www.sqlite.org/download.ht…，下载sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip 压缩文件。
  2. 创建文件夹 D:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。
  3. 配置环境变量，我的电脑右击->属性->高级系统设置->高级->环境变量->Path->编辑->新建->[你的安装目录]->ok。

Linux 安装

  • 目前，几乎所有版本的 Linux 操作系统都附带 SQLite。所以，只要使用下面的命令来检查您的机器上是否已经安装了 SQLite。
 $ sqlite3
 SQLite version 3.7.15.2 2013-01-09 11:53:05
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite>
  • 如果没有看到上面的结果，那么就意味着没有在 Linux 机器上安装 SQLite。因此，让我们按照下面的步骤安装 SQLite：请访问 SQLite 下载页面，从源代码区下载 sqlite-autoconf-*.tar.gz。
步骤如下：
 $ tar xvzf sqlite-autoconf-3071502.tar.gz
 $ cd sqlite-autoconf-3071502
 $ ./configure --prefix=/usr/local
 $ make
 $ make install

Mac OS 安装

最新版本的 Mac OS X 会预安装 SQLite，但是如果没有可用的安装，只需按照如下步骤进行：请访问 SQLite 下载页面，从源代码区下载 sqlite-autoconf-*.tar.gz。

步骤如下：
 $ tar xvzf sqlite-autoconf-3071502.tar.gz
 $ cd sqlite-autoconf-3071502
 $ ./configure --prefix=/usr/local
 $ make
 $ make install
上述步骤将在 Mac OS X 机器上安装 SQLite，您可以使用下列命令进行验证：
 $ sqlite3
 SQLite version 3.7.15.2 2013-01-09 11:53:05
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite>

SQLite 简单上手

命令行窗口

image.png

图形化界面

  1. 使用任一数据库管理工具（这里使用 DataGrip）。
  2. 连接数据库，file -> new -> datasource -> sqlite， 连接名可以随便写。

image.png

  1. 使用如下sql语句创建表，新增数据。
CREATE TABLE COMPANY(
  ID INT PRIMARY KEY     NOT NULL,
  NAME           TEXT    NOT NULL,
  AGE            INT     NOT NULL,
  ADDRESS        CHAR(50),
  SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

SELECT * FROM COMPANY;

image.png

SQLite 常用命令

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

创建或打开数据库

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

  1. sqlite3 /…/xxx.db：通过执行这个命令进入SQLite提示符时，如果数据库文件已经存在，则直接打开对应数据库，否则不会立即在对应路径创建xxx.db文件。要等到执行了添加数据表、视图等数据库对象的命令之后，在创建出comms_ease.db文件
  2. .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。临时表只对当前数据库连接有效，重新建立连接或者同时存在的其他连接都无法访问到。示例如下

常用点命令

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

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

增删改查

增命令

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

  1. 指定值添加就是指定部分或者所有列的值，剩下列使用默认值的方式，对于没有通过DEFAULTE，AUTOINCREMENT等方式声明默认值的列，如果没有指定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');
  1. 通过添加加Select语句的结果添加一行的格式为INSERT INTO 表名 SELECT ...通过此方法添加一行时，默认值不会被自动填充，SELECT语句查出的数据量必须和表的列数一致。
INSERT INTO table_comms_ease SELECT  4, name FROM sqlite_sequence WHERE seq = 4;
  1. 默认添加的格式为INSERT INTO 表名 DEFAULT VALUES;为每一列都填入默认值，如果没有特别声明默认值，则填入NULL。示例如下：
INSERT INTO table_comms_ease DEFAULT VALUES;

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

删命令

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

DELETE FROM 表名 WHERE 过滤语句

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

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

如上命令操作后，table_comms_ease表还剩的数据为 image.png

改命令

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

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

修改多列内容时可以采用先写出列名，再按顺序赋值的方式，也可以采用一列一列修改的方式。如果要更新id为4的所在行的值，设置id为123，value为new 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表中的数据为 image.png 修改单列的方法和修改多列的方法相似，比如将id为5这一行的value也修改为new value，可以如下操作:

UPDATE table_comms_ease SET (value)=('new value 1') WHERE id = 123;
-- 或者
UPDATE table_comms_ease SET value='new value 1' WHERE id = 123;

修改后，table_comms_ease表中的数据为 image.png

查命令

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

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

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

SELECT * FROM table_comms_ease;
-- 上面命令中的*表示所有列，命令相当于
SELECT id,value FROM table_comms_ease;
去重策略

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

SELECT ALL * FROM table_comms_ease;
SELECT DISTINCT value FROM table_comms_ease;

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

表名或者子查询语句

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

  • 假设还有一张表table_comms_ease_1，列信息和table_comms_ease表相同，命令和结果如下：
SELECT * FROM table_comms_ease, table_comms_ease_1;
table_comms_ease.idtable_comms_ease.valuetable_comms_ease_1.idtable_comms_ease_id.value
5new value1value of 1 in table_comms_ease_1
5new value2value of 2 in table_comms_ease_1
123new value1value of 1 in table_comms_ease_1
123new value2value 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.idtable_comms_ease.valuetable_comms_ease.idtable_comms_ease.value
5new value123new value

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

排序条件

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

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

比较方式有三种，分别为 BINARY, **NOCASE **和 RTRIM

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

为表table_comms_ease添加几条数据，表中的数据变为 image.png

  1. BINARY命令及结果：
SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE BINARY;

image.png

  1. NOCASE命令及结果：
SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE NOCASE;

image.png

  1. RTRIM命令及结果：
SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE RTRIM;

image.png

数量限制

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

LIMIT 数量 OFFET 偏移量
  • 设数量为n，偏移量为o,则上面格式的意义是从第o+1条开始，取最多n条数据，如果没有符合条件的数据，则结果为空。限制数量为3，得到如下表达式：
SELECT * FROM table_comms_ease LIMIT 3;
  • 由于表的总行数是5，所以如果限制数量≥5，则会查出整张表。如果限制数量为3，同时指定偏移量为1，得到如下表达式：
SELECT * FROM table_comms_ease LIMIT 3 OFFSET 1;
  • 如果限制数量为3，同时指定偏移量为3，得到如下表达式：
SELECT * FROM table_comms_ease LIMIT 3 OFFSET 3;

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的数据库大小限制
avatar
文章
阅读
粉丝