How to use SQLite3 sql command line?

How to use SQLite3 sql command line?ite is a lightweight How to use SQLite3 sql command line? database that implements a stand-alone, serverless, zero-configuration transactional How to use SQLite3 sql command line? database engine. Except for some commands, the command syntax used by How to use SQLite3 sql command line?ite is similar to that used by MyHow to use SQLite3 sql command line? and Oracle. This article will introduce how to use the command line to use the How to use SQLite3 sql command line?ite database.

1. Create a How to use SQLite3 sql command line?ite database

How to use SQLite3 sql command line?ite provides a simple command to create a database. Use the following command to create a 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

2. Create a table in the How to use SQLite3 sql command line?ite 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.db
sqlite> 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> .quit

3. List or delete tables in How to use SQLite3 sql command line?ite

To list only table names in How to use SQLite3 sql command line?ite database, just use the following command.

sqlite> .tables
posts  tmp    users

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

#drop table <tablename>; 
#drop table if exists <tablename>;
#drop table tmp; 
#drop table if tmp;

4. Insert data into the table

The following command is used to insert data into the How to use SQLite3 sql command line?ite database through the How to use SQLite3 sql command line?ite prompt.

sqlite> INSERT INTO posts VALUES(1, 'Post 1','this is demo post 1');
sqlite> INSERT INTO posts VALUES(2, 'Post 2','this is demo post 2');
sqlite> INSERT INTO users VALUES(1,'Harry','staff');
sqlite> INSERT INTO users VALUES(2,'Rahul','Admin');

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

# vi data.sql
INSERT INTO posts VALUES(10, 'Sample Post 10','this is sample post 10');
INSERT INTO posts VALUES(11, 'Sample Post 11','this is sample post 11');
INSERT INTO users VALUES(10,'Sarah','Support');
INSERT INTO users VALUES(11,'Nick','Sales');

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

# sqlite3 admin.db < data.sql

5. Get data from the table

Use the SELECT command to view the data of the table in the How to use SQLite3 sql command line?ite database, as shown in the following example.

sqlite> SELECT * FROM users;
1|Harry|staff
2|Rahul|Admin
10|Sarah|Support
11|Nick|Sales

sqlite> SELECT * FROM posts;
1|Post 1|this is demo post 1
2|Post 2|this is demo post 2
10|Sample Post 10|this is sample post 10
11|Sample Post 11|this is sample post 11

sqlite> SELECT * FROM posts WHERE postid = 1;
1|Post 1|this is demo post 1

6. Change the output format

How to use SQLite3 sql command line?ite3 displays query results in eight different formats: "csv", "column", "html", "insert" , "line", "list", "tabs" and "tcl". The output format can be changed using the ".mode" command. The default output format is "list".

sqlite> .mode line
sqlite> select * from users;
     uid = 1
   uname = Harry
category = staff

     uid = 2
   uname = Rahul
category = Admin

sqlite> .mode column
sqlite> select * from users;
1           Harry       staff
2           Rahul       Admin
10          Sarah       Support
11          Nick        Sales

7. Convert How to use SQLite3 sql command line?ite database to ASCII text file

How to use SQLite3 sql command line?ite database can be simply converted to plain text file using the ".dump" command. Use the following command to execute.

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

To rebuild the How to use SQLite3 sql command line?ite database from the ASCII file backup.dump, just enter:

#cat backup.dump | sqlite3 admin-1.db

The above is the detailed content of How to use SQLite3 sql command line?. For more information, please follow other related articles on the PHP Chinese website!