Sqlite3 introduction and application of common query statements

Sqlite3 introduction and application of commonly used query statements sqlite3 database introduction SQLite database is a very lightweight self-contained (lightweightandself-contained) DBMS, Which is portable, easy to use, very small, efficient and reliable. SQLite is embedded into applications that use it. They share the same process space, rather than a single

Sqlite3 introduction and commonly used query statement application sqlite3 database introduction SQLite database is a very lightweight self-contained (lightweight and self-contained) DBMS, Which is portable, easy to use, very small, efficient and reliable. SQLite is embedded into applications that use it. They share the same process space, rather than a single

Sqlite3 introduction and application of common query statements

Sqlite3 database Introduction

SQLite database is a lightweight and self-contained DBMS. It is portable, easy to use, small, efficient, and reliable.

SQLite is embedded into applications that use it. They share the same process space, rather than a separate process. From the external perspective, it is not like an RDBMS, but inside the process, it is complete, self-contained database engine.

One of the major advantages of an embedded database is that you do not need network configuration or management in your program. Because the client and server run in the same process space.

SQLite database permissions only depend on the file system, without the concept of user accounts. SQLite has database-level locking and no network server. It requires memory usage, but other expenses are very small. It is suitable for embedded devices. All you need to do is compile it correctly into your program.

Sqlite commonly used query statements

Create a database using SQLite

SQLite is very convenient to use. You only need to input the "sqlite3" command with the name of the SQLite database. If the file does not exist, a new (database) file is created. Then, the sqlite3 program prompts you to enter SQL. The SQL statement is ended with a semicolon (;). After you press the Enter key, the SQL statement is executed. For example, create an SQLite database that contains the "user. db" table "cars.

CREATE command:

Create Database user. db

Sqlite3 user. db

Create Table cars

Create table carsl (name varchar (10), years smallint, price float );

Query a table

. Table

Insert data

Insert into cars values ('audi ', 1994,530 000.5 );

Insert into cars values ('volv', 2001,303 478.5 );

Display Header

. Header on

Modify Display Mode

. Mode column

Query data

Select * from cars; // query all data in the cars table

Sqlite> select * from tbl1 where one = 'xxx ';

Delete all statements

Sqlite> delete from cars;

Update statement

Sqlite> update cars set name = 'kaka 'where name = "mini ";

Delete statement

Sqlite> delete from cars where name = "kaka ";

Sqlite> delete from cars; // delete all data in the cars table

Conditional statement application "and" "or" "and or"

Sqlite> delete from cars where name = "volvo" or name = "audi ";

Sqlite> delete from cars where name = "volvo" and price = 324200;

Sqlite> delete from cars where (name = "volvo" or name = "audi") and price = 324203;

Display result:

Its operations are not much different from common relational databases, so it is convenient to add, delete, modify, and query data.

SQLite can display the query results in eight ways, which greatly facilitates data processing. The sqlite3 program can display the query results in eight different formats:

. Mode MODE? TABLE? Set output mode where MODE is one:

Csv Comma-separated values

Column Left-aligned columns. (See. width)

Html HTML

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

Usage:. mode column (switch the output format to row mode, for example)

SQLite3 Import and Export Database

Export Database

Sqlite>. databases (display database)

Sqlite>. backup main user. SQL (back up the database main, that is, copying the database)

Sqlite>. backup user2. SQL (backup default database main)

Code