SQLite is a C library that implements an SQL database engine. It is a Relational Database Management System (or RDBMS). Most of the SQL databases work with the client/server model. Take MySQL for an example. To enter and receive data from a MySQL database you need to send a request to the MySQL server, which on reception will provide you with the appropriate response. Contrary to MySQL, SQLite databases are operated directly from the disk. There is no need to create requests to the server.

SQLite是一个实现SQL数据库引擎的C库。 它是一个关系数据库管理系统 (或RDBMS)。 大多数SQL数据库都使用客户端/服务器模型。 以MySQL为例。 要输入和接收来自MySQL数据库的数据,您需要向MySQL服务器发送请求,该服务器在接收时将为您提供适当的响应。 与MySQL相反,SQLite数据库直接从磁盘操作。 无需创建对服务器的请求。

SQLite370.svg (Custom)

安装 (Installation)

We will operate on our database using the sqlite31 Command Line Interface(CLI). We will work under the Linux Platform. sqlite3 CLI for MAC OS and Windows are just the same, but for the sake of consistency, it’s recommended you install Homestead Improved – a lightweight VM runnable in 5 minutes that will keep your computer clean of extra unneeded software.

我们将使用sqlite3 1命令行界面(CLI)在数据库上进行操作。 我们将在Linux平台下工作。 适用于MAC OS和Windows的sqlite3 CLI相同,但是为了保持一致性,建议您安装Homestead Improvement –一种可在5分钟内运行的轻量级VM,可以使您的计算机清除不必要的软件。

To install it on Homestead Improved (or any Debian based OS like Ubuntu), run the following:

要将其安装在Homestead Enhanced(或任何基于Debian的操作系统,如Ubuntu)上,请运行以下命令:

sudo apt-get install sqlite3 libsqlite3-dev

This will install sqlite3. To install it on other platforms, follow their official instructions. After it is installed we can start the session. Open the Terminal/Command Line and enter the command sqlite3. You must see something of this kind :

这将安装sqlite3 。 要将其安装在其他平台上,请遵循其官方说明 。 安装后,我们可以开始会话。 打开终端/命令行,然后输入命令sqlite3 。 您必须看到这种东西:

CLI sqlite3

The second line tells you to enter .help for instructions. There is a prompt waiting for you to type a command. So go ahead and enter .help. This will give you a list of Meta Commands and their descriptions.

第二行告诉您输入.help作为说明。 提示您等待键入命令。 因此,继续输入.help 。 这将为您提供元命令及其描述的列表。

元命令 (Meta Commands)

Meta Commands are used to define output format for tables, examine databases and for other administrative operations. They always start with a dot. Even .help is a meta command. You can go through the list. Here are some that will frequently come in handy:

元命令用于定义表的输出格式,检查数据库和其他管理操作。 他们总是以点开头 。 甚至.help都是一个meta命令。 您可以浏览列表。 以下是一些经常派上用场的东西:

.showDisplays current settings for various parameters
.databasesProvides database names and files
.quitQuit sqlite3 program
.tablesShow current tables
.schemaDisplay schema of table
.headerDisplay or hide the output table header
.modeSelect mode for the output table
.dumpDump database in SQL text format
命令 描述
。表演 显示各种参数的当前设置
。数据库 提供数据库名称和文件
。退出 退出sqlite3程序
。表格 显示当前表格
.schema 表的显示模式
.header 显示或隐藏输出表标题
。模式 输出表的选择模式
。倾倒 以SQL文本格式转储数据库

标准指令 (Standard Commands)

Let us go through the standard commands in sqlite3. Meta commands are issued to examine a database. Standard SQL commands are issued to operate on a database. Standard Commands can be classified into three groups:

让我们来看一下sqlite3中的标准命令 。 发出元命令以检查数据库。 发出标准SQL命令以在数据库上运行。 标准命令可以分为三类:

  • Data Definition Language: It provides the storage structure and methods to access data from the database system.

    数据定义语言 :它提供了用于从数据库系统访问数据的存储结构和方法。

  • Data Manipulation Language: It enables users to manipulate (add/modify/delete) data.

    数据操作语言 :它使用户能够操作(添加/修改/删除)数据。

  • Data Query Language: It enables users to retrieve required data from the database.

    数据查询语言 :它使用户能够从数据库中检索所需的数据。

Note: SQLite understands many other standard commands, a list of which can be read here. Since the tutorial is a beginner level introduction, we will cover only the above mentioned commands.

注意: SQLite可以理解许多其他标准命令,可以在此处阅读列表。 由于本教程是初学者级的介绍,因此我们将仅介绍上述命令。

SQLite databases are cross-platform portable files. They can be stored on various storage devices and can be transferred across different computers.

SQLite数据库是跨平台的可移植文件 。 它们可以存储在各种存储设备上,并且可以在不同的计算机之间传输。

We will learn to work with sqlite3 with our Comment Section database. Almost every website today has a comment section. To post a comment, the user must enter the following details:

我们将通过注释部分数据库学习与sqlite3一起工作。 今天几乎每个网站都有一个评论部分。 要发表评论,用户必须输入以下详细信息:

  • Name

  • Email

  • Website

  • Comment


Of the four of these, only website URL is optional. We must also define a column that numbers the comments. Let’s call it post_id.

在这四个中,只有网站URL是可选的。 我们还必须定义一个为注释编号的列。 我们称它为post_id

We can define the datatypes of the columns (attributes) as follows:


AttributeDatatype Required
属性 必需的数据类型
post_id 整数
名称 文本
电子邮件 文本
网址 文本
评论 文本

You can see the documentation for different Datatypes and Storage Classes provided in SQLite3.


SQLite3 uses Manifest Typing2. Most other database engines including MySQL use Static Typing.

SQLite3使用清单输入 2 。 包括MySQL在内的大多数其他数据库引擎都使用静态类型。

Now let us create a database. If you are still in the sqlite3 program, quit it by entering .quit at the sqlite prompt. Then, issue the command:

现在让我们创建一个数据库。 如果仍在sqlite3程序中, .quit在sqlite提示符下输入.quit退出。 然后,发出命令:

sqlite3 comment_section.db

This will create a database file comment_section.db in the current working directory.


Note: If no filename follows the command, sqlite3 automatically creates a temporary database. It is what happened when we entered the single command sqlite3 for the first time.

注意:如果命令后没有文件名,则sqlite3自动创建一个临时数据库。 这是我们第一次输入单个命令sqlite3时发生的事情。


To store the comments we must define a table. Let us name it comments. To create it, enter the statement:

要存储注释,我们必须定义一个表。 让我们将其命名为comments 。 要创建它,请输入以下语句:

CREATE TABLE comments ( 
	email TEXT NOT NULL, 
	website_url TEXT NULL, 
	comment TEXT NOT NULL );

NOT NULL makes sure that the particular value is not left empty in a record. PRIMARY KEY3 and AUTOINCREMENT4 elaborate the post_id attribute.

NOT NULL确保特定值在记录中不为空。 PRIMARY KEY 3AUTOINCREMENT 4详细说明了post_id属性。

To check if table has been created, issue the meta command .tables. It will display the table name comments.

要检查是否已创建表,请发出meta命令.tables 。 它将显示表名称comments

Create Table comments

Note: To get the schema of the table enter .schema comments

注意:要获取表的架构,请输入.schema comments

Now that the table has been created, we need to enter data.



Suppose a user has entered a comment with the details:


Name    : Shivam Mamgain
Email   : xyz@gmail.com
Website : shivammg.blogspot.com
Comment : Great tutorial for beginners.

To insert it, we use the INSERT command.


INSERT INTO comments ( name, email, website_url, comment )
VALUES ( 'Shivam Mamgain', 'xyz@gmail.com',
'shivammg.blogspot.com', 'Great tutorial for beginners.' );

I have not provided the value for post_id, even though it is defined as NOT NULL. The thing is, it is not needed because it is an AUTOINCREMENT field.

即使定义为NOT NULL ,我也没有提供post_id的值。 事实是,不需要它,因为它是一个AUTOINCREMENT字段。

Enter some more rows in the table to practice.



We have learned to insert data into the table. To retrieve data we use the SELECT command.

我们已经学习了将数据插入表中。 要检索数据,我们使用SELECT命令。

SELECT post_id, name, email, website_url, comment
FROM comments;

The above statement can also be written as:


FROM comments;

This will retrieve all rows from the table. The resultant table might look obfuscated without headers and proper separation between columns. To overcome this, we must alter some parameters.

这将从表中检索所有行。 结果表看起来很模糊,没有标题,并且各列之间没有适当的分隔。 为了克服这个问题,我们必须更改一些参数。

Enter .show



To display column attributes, enter .headers ON. To display rows in column style, enter .mode column. Enter the SELECT statement again.

要显示列属性,请输入.headers ON 。 要以列样式显示行,请输入.mode column 。 再次输入SELECT语句。

Column Display

Note: Select a mode of display that suits you. Enter .help and search for .mode. You will be shown the various values .mode can have.

注意:选择适合您的显示模式。 输入.help和搜索.mode 。 您将看到.mode可以具有的各种值。


Suppose email for ‘Shivam Mamgain’ was changed to ‘zyx@email.com’ . We need to update the row. Issue the following statement:

假设“ Shivam Mamgain”的电子邮件已更改为“ zyx@email.com”。 我们需要更新该行。 发出以下语句:

UPDATE comments
SET email = 'zyx@email.com'
WHERE name = 'Shivam Mamgain';

This will change the email attribute for the name ‘Shivam Mamgain’ to ‘zyx@email.com’.

这会将名称 “ Shivam Mamgain”的电子邮件属性更改为“ zyx@email.com”。

Note: The name attribute is not unique, so the above statement may affect more than one row. For every name = ‘Shivam Mamgain’ the email will be set to ‘zyx@email.com’. To update a particular row use the post_id attribute. It is defined as a PRIMARY KEY and will always be unique.

注意: name属性不是唯一的,因此上面的语句可能影响多个行。 对于每个名称 =“ Shivam Mamgain”, 电子邮件将设置为“ zyx@email.com”。 要更新特定行,请使用post_id属性。 它定义为PRIMARY KEY并且始终是唯一的。


To DELETE row(s) from the table we can use the WHERE condition, just like in UPDATE.


Suppose we need to delete a row with post_id 9. We can enter the command:

假设我们需要删除一个具有post_id 9的行。我们可以输入以下命令:

DELETE FROM comments
WHERE post_id = 9;

We are in a need to delete all comments that are posted by ‘Bart Simpson’ and ‘Homer Simpson’. We can run the following query:

我们需要删除“巴特·辛普森”和“荷马·辛普森”发表的所有评论。 我们可以运行以下查询:

DELETE FROM comments
WHERE name = 'Bart Simpson' OR name = 'Homer Simpson';

改变 (ALTER)

New columns can be added to a table using ALTER. On the web, most people don’t necessarily use their real name. They generally work under an alias, a username. We need to add a column username to our table. The query below accomplishes the task:

可以使用ALTER将新列添加到表中。 在网络上,大多数人不一定使用其真实姓名。 它们通常在别名( 用户名)下工作。 我们需要在表中添加一列用户名 。 下面的查询完成了任务:

ALTER TABLE comments

This will create a column username in comments and will set its datatype to TEXT. The value of username for already inserted rows will be set to NULL.

这将在comments创建列用户名 ,并将其数据类型设置为TEXT。 已插入的行的用户名值将设置为NULL。

The ALTER command is also used to rename tables. Let us change the comments table to Coms.

ALTER命令还用于重命名表。 让我们将comments表更改为Coms

ALTER TABLE comments

下降 (DROP)

Dropping a table means deleting the entire table. The Coms table can be deleted with the following query:

删除表意味着删除整个表。 可以使用以下查询删除Coms表:


结论 (Conclusion)

SQLite3 offers many advantages over other relational databases. Some of its distinctive features can be read here. Most PHP Frameworks and many other web frameworks including Django, Ruby on Rails and web2py have SQLite3 as their default database. Being lightweight makes it preferable for local storage in web browsers. It is also employed as client storage for many Operating Systems, including Android and Windows Phone 8. It is one of the most widely deployed database engines.

与其他关系数据库相比,SQLite3具有许多优势。 它的一些鲜明特征可以在这里阅读。 大多数PHP框架和许多其他Web框架(包括DjangoRuby on Railsweb2py)都将SQLite3作为其默认数据库。 轻量级使其更适合用于Web浏览器中的本地存储。 它还被用作许多操作系统(包括AndroidWindows Phone 8)的客户端存储。 它是部署最广泛的数据库引擎之一。

In the tutorial we interacted with an SQLite database system. There are GUI applications to do the same, without having to learn commands. Two such applications are the DB Browser for SQLite and SQLiteStudio. You can also practice SQL online at SQL Fiddle.

在本教程中,我们与SQLite数据库系统进行了交互。 有些GUI应用程序可以执行相同操作,而无需学习命令。 两个这样的应用程序是用于SQLite数据库浏览器SQLiteStudio 。 您也可以在SQL Fiddle上在线练习SQL。

This guide has covered the basics to get you started with SQLite3. I hope it will tempt you to go further. You can easily integrate it with PHP using the common PDO class.

本指南介绍了入门SQLite3的基础知识。 我希望它能吸引您走得更远。 您可以使用通用的PDO类轻松地将其与PHP集成。

  1. sqlite version 3

    sqlite版本 3↩

  2. Manifest Typing releases many restrictions on the type of value that can be entered for a particular field. This allows you to enter any value of any datatype into a column, irrespective of the declared type of the column (except for INTEGER PRIMARY KEY). You are not even required to provide the maximum size for datatypes. After the user inputs data, SQLite determines the size it would require on the disk. To get a deeper insight refer to Datatypes in SQLite3.

    清单键入对可以为特定字段输入的值的类型释放了许多限制。 这使您可以在列中输入任何数据类型的任何值,而不管该列的声明类型如何(除了INTEGER PRIMARY KEY )。 您甚至不需要为数据类型提供最大大小。 用户输入数据后,SQLite确定磁盘上所需的大小。 要获得更深入的了解,请参阅SQLite3中的数据类型

  3. A PRIMARY KEY can uniquely define a particular row. Such an attribute must always be defined as NOT NULL.

    PRIMARY KEY可以唯一地定义特定行。 这样的属性必须始终定义为NOT NULL

  4. AUTOINCREMENT automatically sets the corresponding attribute of the currently inserted record to a number that is one plus the value of AUTOINCREMENT (unless specified). AUTOINCREMENT defined attributes must necessarily be INTEGERS.

    AUTOINCREMENT自动将当前插入的记录的相应属性设置为一个数字,再加上AUTOINCREMENT的值(除非指定)。 AUTOINCREMENT定义的属性必须一定是INTEGERS

翻译自: https://www.sitepoint.com/getting-started-sqlite3-basic-commands/


  • 0
  • 0
  • 0


  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
钱包余额 0