Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use the SQLite3 sql command line, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

SQLite is a lightweight SQL database that implements an independent, serverless, zero-configuration transactional SQL database engine. Except for some commands, the command syntax used by sqlite is similar to that used by mysql and oracle, and this article describes how to use the command line to work with sqlite databases.

1. Create a SQLite database

SQLite provides a simple command to create a database. Use the following command to create the sqlite database.

# sqlite3 admin.db

Basically, the sqlite database is a file created in the current working directory.

# ls-l admin.db-rw-r--r--. 1 root root 3072 May 11 14:32 admin.db

two。 Create a table in a SQLite database

After creating the database, we create the table. Use the following query to create two tables (users, posts) in the database admin.db.

# sqlite3 admin.dbsqlite > create table users (uid integer,uname varchar (60), category varchar (50)); sqlite > create table posts (postid integer,postname varchar (50), content varchar (1000)); sqlite > create table tmp (id integer,tname varchar (50); sqlite > .resume

3. List or delete tables in SQLite

To list table names only in the SQLite database, simply use the following command.

Sqlite > .tableship tmp users

If you need to delete any tables, you can do so using the following command, as shown below.

# drop table; # drop table if exists; # drop table tmp; # drop table if tmp

4. Insert data in a table

The following command is used to insert data into the SQLite database through the SQLite prompt.

Sqlite > INSERT INTO posts VALUES; sqlite > INSERT INTO posts VALUES; sqlite > INSERT INTO users VALUES; sqlite > INSERT INTO users VALUES); sqlite > INSERT INTO posts VALUES; sqlite > INSERT INTO users VALUES

You can also execute a set of commands contained in the file.

# vi data.sqlINSERT INTO posts VALUES (10 Sample Post 'Sample Post 10'); INSERT INTO posts VALUES (11 'Sample Post 11); INSERT INTO users VALUES (10 is sample post); INSERT INTO users VALUES (11)

The following command executes all commands of data.sql in the admin.db database.

# sqlite3 admin.db

SELECT * FROM users;1 | Harry | staff2 | Rahul | Admin10 | Sarah | Support11 | Nick | Salessqlite > SELECT * FROM posts;1 | Post 1 | this is demo post 12 | Post 2 | this is demo post 210 | Sample Post 10 | this is sample post 1011 | Sample Post 11 | this is sample post 11sqlite > SELECT * FROM posts WHERE postid = 1 transfer1 | Post 1 | this is demo post 1

6. Change the output format

SQLite3 displays query results in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs" and "tcl". Use the ".mode" command to change the output format. The default output format is list.

Sqlite > .mode linesqlite > select * from users; uid = 1 uname = Harrycategory = staff uid = 2 uname = Rahulcategory = Adminsqlite > .mode columnsqlite > select * from users;1 Harry staff2 Rahul Admin10 Sarah Support11 Nick Sales

7. Convert a SQLite database to an ASCII text file

You can simply convert an SQLite database to a plain text file using the ".dump" command. Use the following command to execute.

# sqlite3 admin.db '.dump' > backup.dump

To rebuild the SQLite database from the ASCII file backup.dump, simply enter:

# cat backup.dump | the above is all about how to use the SQLite3 sql command line. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.