Getting Started with SQLite3 - Basic Commands
March 23, 2015
is a C library that implements an SQL database engine. It is a
(or RDBMS). Most of the SQL databases work with the client/server model. Take
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.
Installation
We will operate on our database using the sqlite3 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
– a lightweight VM runnable in 5 minutes that will keep your computer clean of extra unneeded software.
To install it on Homestead Improved (or any Debian based OS like Ubuntu), run the following:
sudo apt-get install sqlite3 libsqlite3-dev
This will install sqlite3. To install it on other platforms, follow their . 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 :
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.
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:
Description
Displays current settings for various parameters
.databases
Provides database names and files
Quit sqlite3 program
Show current tables
Display schema of table
Display or hide the output table header
Select mode for the output table
Dump database in SQL text format
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
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 . Since the tutorial is a beginner level introduction, we will cover only the above mentioned commands.
SQLite databases are cross-platform portable files. They can be stored on various storage devices and can be transferred across different computers.
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:
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.
We can define the datatypes of the columns (attributes) as follows:
Datatype Required
website_url
You can see the documentation for different
provided in SQLite3.
SQLite3 uses Manifest Typing. Most other database engines including MySQL use Static Typing.
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 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.
To store the comments we must define a table. Let us name it comments. To create it, enter the statement:
CREATE TABLE comments (
post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
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 KEY and AUTOINCREMENT elaborate the post_id attribute.
To check if table has been created, issue the meta command .tables. It will display the table name comments.
Note: To get the schema of the table enter
.schema comments
Now that the table has been created, we need to enter data.
Suppose a user has entered a comment with the details:
: Shivam Mamgain
Comment : Great tutorial for beginners.
To insert it, we use the INSERT command.
INSERT INTO comments ( name, email, website_url, comment )
VALUES ( 'Shivam Mamgain', '',
'', '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.
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 post_id, name, email, website_url, comment
The above statement can also be written as:
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.
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.
Suppose email for ‘Shivam Mamgain’ was changed to ‘’ . We need to update the row. Issue the following statement:
UPDATE comments
SET email = ''
WHERE name = 'Shivam Mamgain';
This will change the email attribute for the name ‘Shivam Mamgain’ to ‘’.
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 ‘’. To update a particular row use the post_id attribute. It is defined as a PRIMARY KEY and will always be unique.
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:
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';
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 TABLE comments
ADD COLUMN username TEXT;
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.
The ALTER command is also used to rename tables. Let us change the comments table to Coms.
ALTER TABLE comments
RENAME TO C
Dropping a table means deleting the entire table. The Coms table can be deleted with the following query:
DROP TABLE C
Conclusion
SQLite3 offers many advantages over other relational databases. Some of its distinctive features can be read . Most PHP Frameworks and many other web frameworks including ,
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
and . It is one of the most widely deployed database engines.
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
and . You can also practice SQL online at .
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 .
Tags: , , ,
is an Open-source Software Developer with a keen interest in Web Development and Competitive Programming. A Python Zealot, casual gamer and a(n infrequent) .
Git into it! Bonus course Introduction to Git is yours when you take up a free 14 day SitePoint Premium trial.sqlite3命令方式操作大全_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
sqlite3命令方式操作大全
上传于||文档简介
&&s​q​l​i​t​e ​在​l​i​n​u​x​环​境​下​才​操​作​手​册
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
下载文档到电脑，查找使用更方便
还剩22页未读，继续阅读
你可能喜欢一 sqlite简单地说SQLite数据库对应的就是一个文件（一般命名为xxx.db）。可以通过sqlite shell命令行来操作sqlite数据库，也可以在其他的语言中通过api来操作sqlite数据库。sqlite数据库的访问甚至不需要用户名和密码。&在windows上可以下载：&， 其他平台可以下载对应的shell命令行。&帮助：&d:\sqlite_test&sqlite3.exe test.dbSQLite version 3.7.13
02:05:22Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite& .help.backup ?DB? FILE & & &Backup DB (default "main") to FILE.bail ON|OFF & & & & & Stop after hitting an error. &Default OFF.databases & & & & & & List names and files of attached databases.dump ?TABLE? ... & & &Dump the database in an SQL text format& & & & & & & & & & & & &If TABLE specified, only dump tables matching& & & & & & & & & & & & &LIKE pattern TABLE..echo ON|OFF & & & & & Turn command echo on or off.exit & & & & & & & & &Exit this program.explain ?ON|OFF? & & &Turn output mode suitable for EXPLAIN on or off.& & & & & & & & & & & & &With no args, it turns EXPLAIN on..header(s) ON|OFF & & &Turn display of headers on or off.help & & & & & & & & &Show this message.import FILE TABLE & & Import data from FILE into TABLE.indices ?TABLE? & & & Show names of all indices& & & & & & & & & & & & &If TABLE specified, only show indices for tables& & & & & & & & & & & & &matching LIKE pattern TABLE..load FILE ?ENTRY? & & Load an extension library.log FILE|off & & & & &Turn logging on or off. &FILE can be stderr/stdout.mode MODE ?TABLE? & & Set output mode where MODE is one of:& & & & & & & & & & & & &csv & & &Comma-separated values& & & & & & & & & & & & &column & Left-aligned columns. &(See .width)& & & & & & & & & & & & &html & & HTML &table& code& & & & & & & & & & & & &insert & SQL insert statements for TABLE& & & & & & & & & & & & &line & & One value per line& & & & & & & & & & & & &list & & Values delimited by .separator string& & & & & & & & & & & & &tabs & & Tab-separated values& & & & & & & & & & & & &tcl & & &TCL list elements.nullvalue STRING & & &Print STRING in place of NULL values.output FILENAME & & & Send output to FILENAME.output stdout & & & & Send output to the screen.prompt MAIN CONTINUE &Replace the standard prompts.quit & & & & & & & & &Exit this program.read FILENAME & & & & Execute SQL in FILENAME.restore ?DB? FILE & & Restore content of DB (default "main") from FILE.schema ?TABLE? & & & &Show the CREATE statements& & & & & & & & & & & & &If TABLE specified, only show tables matching& & & & & & & & & & & & &LIKE pattern TABLE..separator STRING & & &Change separator used by output mode and .import.show & & & & & & & & &Show the current values for various settings.stats ON|OFF & & & & &Turn stats on or off.tables ?TABLE? & & & &List names of tables& & & & & & & & & & & & &If TABLE specified, only list tables matching& & & & & & & & & & & & &LIKE pattern TABLE..timeout MS & & & & & &Try opening locked tables for MS milliseconds.trace FILE|off & & & &Output each SQL statement as it is run.vfsname ?AUX? & & & & Print the name of the VFS stack.width NUM1 NUM2 ... & Set column widths for "column" mode.timer ON|OFF & & & & &Turn the CPU timer measurement on or off&&二 使用sqlite1）打开或创建sqlite数据库&d:\sqlite_test&sqlite3.exe test.dbSQLite version 3.7.13
02:05:22Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite&&&&2） 创建表及插入数据sqlite& create table mytable(name varchar(10), age smallint);sqlite& insert into mytable values("itech", 5);sqlite& insert into mytable values("jason", 10);sqlite& select *itech|5jason|10sqlite&&&3）查看表及表的schemasqlite& .tablesmytablesqlite& .schema mytableCREATE TABLE mytable(name varchar(10), age smallint);sqlite&&&4）数据库导出为ascii的sql文件 + 重新导入到新的数据库d:\sqlite_test&echo .dump | sqlite3 test.db & test.db.dump&d:\sqlite_test&type test.db.dumpPRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE mytable(name varchar(10), age smallint);INSERT INTO "mytable" VALUES('itech',5);INSERT INTO "mytable" VALUES('jason',10);COMMIT;&&d:\sqlite_test&type test.db.dump | sqlite3 test2.dbd:\sqlite_test&sqlite3 test2.dbSQLite version 3.7.13
02:05:22Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite& .tablesmytablesqlite&&&5）格式化输出sqlite& .mode columnsqlite& .header onsqlite& select *name & & & &age---------- &----------itech & & & 5jason & & & 10sqlite&&&完！&
阅读(...) 评论()二次元同好交流新大陆
扫码下载App
汇聚2000万达人的兴趣社区下载即送20张免费照片冲印
扫码下载App
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
或者，如果你给".schema"命令一个参数，由于你只想得到一个表的结构，查询可以是这样：
SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
你可以担供一个参数给.schema命令。如果这橛，查询可以是这样的：
SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
在查询中“%S“为你的参数所取代。这使你可以询数据库结构的某个子集。
sqlite&&.schema&%abc%
与这些一起，“.table”命令也接受一个模式作为他的参数。如果你给“.table”一个参数，“%”将被前后扩展并且一个LIKE子句被附加到查询上。这让你可以列出只与特定模式相匹配的的表。
“.datebasae”命令显示所有当前连接打开的数据库的一个列表。将允许一次到少两个。第一个是“main”，最初打开的那个数据库。第二个是"temp"，用于临时表的数据库。对于用ATTACH语句附加的数据也许有附加数据库列表。输出的第一列与之相联的数据库名，第二列是外部文件名。
sqlite&&.databases
将整个数据库转换为ASCII文本文件
".dump"命令成一个单一的ASCII文本文件。这个文件可以被用作管道传递给sqlite3命令来转换回数据库。
一个最好的制作一个数据库档案拷贝的命令是：
$&echo&'.dump'&|&sqlite3&ex1&|&gzip&-c&&ex1.dump.gz
它产生一个一个名为ex1.dump.gz的文件，它包含了你以后或在其它机器上重构数据库的所有的信息。要重构数据库，只须敲入：
$&zcat&ex1.dump.gz&|&sqlite3&ex2
这个文本格式是纯粹的SQL语句所以你可以用.dump命令来导出一个SQLite数据库到另一个常用的SQL数据库引擎。比如：
$&createdb&ex2$&sqlite3&ex1&.dump&|&psql&ex2 其它的点命令".explain"命令可以被用来设置输出格式为“column”&并设置列宽为EXPLAIN命令看起来比较合理的宽度。EXPLAIN命令是SQLite特有的SQL扩展，它是对调试有用。如果任何常规的SQL被EXPLAIN执行，那么SQL命令被分解并分析但并不执行。取而代之的是，虚拟机指令序列将被用于执行SQL命令并返回一个类似的查询结果。如：sqlite&&.explainsqlite&&explain&delete&from&tbl1&where&two&20;addr&&opcode&&&&&&&&p1&&&&&p2&&&&&p3&&&&&&&&&&----&&------------&&-----&&-----&&-------------------------------------&&&0&&&&&ListOpen&&&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&1&&&&&Open&&&&&&&&&&0&&&&&&1&&&&&&tbl1&&&&&&&&2&&&&&Next&&&&&&&&&&0&&&&&&9&&&&&&&&&&&&&&&&&&3&&&&&Field&&&&&&&&&0&&&&&&1&&&&&&&&&&&&&&&&&&4&&&&&Integer&&&&&&&20&&&&&0&&&&&&&&&&&&&&&&&&5&&&&&Ge&&&&&&&&&&&&0&&&&&&2&&&&&&&&&&&&&&&&&&6&&&&&Key&&&&&&&&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&7&&&&&ListWrite&&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&8&&&&&Goto&&&&&&&&&&0&&&&&&2&&&&&&&&&&&&&&&&&&9&&&&&Noop&&&&&&&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&10&&&&ListRewind&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&11&&&&ListRead&&&&&&0&&&&&&14&&&&&&&&&&&&&&&&&12&&&&Delete&&&&&&&&0&&&&&&0&&&&&&&&&&&&&&&&&&13&&&&Goto&&&&&&&&&&0&&&&&&11&&&&&&&&&&&&&&&&&14&&&&ListClose&&&&&0&&&&&&0
“.timeout”命令设置sqlite3等待一个试图存储文件锁定请除直到错误返回的总时间。默认的超时值是0因此如果任何需要的数据库表或序列列被锁定时它将立即返回一个错误。
最后，我们提到“.exit”命令它交导致sqlite3退出。在命令和脚本中使用sqlite3
一个在脚本命令中使用sqlite3的方式是用“echo”或“cat”来产生一个命令序列在一个文件中，然后当从一个产生的命令行中重定向输入时调用sqlite3。它有用并且适应许多环境。但作为一附加的便利是，sqlite3允许一个单一的SQL语句在命令行中作为数据库名后的第二个参数输入。当sqlite3程序带着两个参数启动时，第二个参数被传递给SQLite库处理，查询以列表模式打印到标准输出，然后程序退出。这个机制被设计用于让sqlite3容易于用于连接诸如"AWK"的程序。例如：
$&sqlite3&ex1&'select&*&from&tbl1'&|&&&awk&'{printf&"&tr&&td&%s&td&%s\n",$1,$2&}'&tr&&td&hello&td&10&tr&&td&goodbye&td&20$ 结束命令行命令
SQLite命令通常以一个分号结束。在一个命令行中你也可以用“GO”单词（大小写敏感）或者一个“/”斜线在它所在好行结束一个命令。这常被SQL Server和Oracle使用。这些将不在sqlite3_exec()中有用，因为命令行在传递它们到函数之前把这些翻译为分号。从源文件中编译sqlite3
sqlite3程序当你编译SQLite库时自动被建立。只须取得一个源文件树的拷贝，运行“configure"然后"make"即可。本页最后一次候改 14:31:36
阅读(5798)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_082070',
blogTitle:'sqlite3用法',
blogAbstract:'&&& SQLite库包含一个名字叫做sqlite3的命令行,它可以让用户手工输入并执行面向SQLite数据库的SQL命令。本文档提供一个样使用sqlite3的简要说明。\r\n开始\r\n&&&&& 启动sqlite3程序，仅仅需要敲入带有SQLite数据库名字的\"sqlite3\"命令即可。如果文件不存在，则创建一个新的（数据库）文件。然后sqlite3程序将提示你输入SQL。敲入SQL语句（以分号“；”结束），敲回车键之后，SQL语句就会执行。\r\n&',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:6,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}