【英文】SQLite学习笔记

Preface

SQLite is a relational database management system that adheres to the ACID principles. It is included in a relatively small C library. Unlike many other database management systems, SQLite is not a client/server-based database engine, but is integrated into user programs. (Wikipedia)

Install SQLite

MacOS

  • SQLite is pre-installed in newer versions of MacOS. If it is not available, you can install it using brew.
1
brew install sqlite

Linux

1
apt install sqlite3

Start SQLite

  • In SQLite, you can directly use commands to manipulate databases.
1
sqlite3

Execute commands directly

  • When executing a single command, you can also append the command directly without starting SQLite.

<name>.db: The database you want to operate on.
.show: An SQLite command.

1
sqlite3 <name>.db .show

Data Types

Data Type Description
NULL Null value
int Integer
integer Signed integer. Automatically stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value size.
real Floating-point number. Stored as an 8-byte floating-point value.
text Text
blob Blob

SQLite Commands

Comment

1
-- Comment content

View help

1
.help

Open or create a database file

  • SQLite stores data in the form of files, so opening a database is essentially opening a .db file.

<name>: The name of the database file you want to create or open.

1
2
3
.open <name>.db

sqlite3 <name>.db

View information for the current database

1
.show

Show all databases

1
.databases

Show all data tables

  • Essentially, it prints the create statements of the data tables.
1
.schema

Backup database (export)

  • Export the database using input redirection.

<file>.sql: Export as an sql file.

1
sqlite3 <name>.db .dump > <file>.sql

Restore database (import)

  • Import the database using input redirection.
1
sqlite3 <name>.db < <file>.sql

SQL Statements

Create table

<table_name>: The name of the data table.
<field_name>: The field name.
<field_type>: The field type.

PRIMARY KEY: Define as primary key.

1
2
3
4
CREATE TABLE <table_name>(
<field_name_1> <field_type_1> PRIMARY KEY,
<field_name_2> <field_type_2>
);

Drop table

1
DROP TABLE <table_name>;

Insert data

Specify fields

  • Specify fields by adding data to the corresponding fields.
1
INSERT INTO <table_name>(<field_name_1>, <field_name_2>, ...) VALUES(<value_1>, <value_2>, ...);

Not specifying fields

  • Not specifying fields means adding data to all fields.
1
INSERT INTO <table_name> VALUES(<value_1>, <value_2>, ...);

Copy data from a table

Specify fields

<new_table_name>: The new table.
<select>: The select statement.

1
INSERT INTO <new_table_name> (<field_name_1>, <field_name_2>, ...) <select>;

Not specifying fields

1
INSERT INTO <new_table_name> <select>;

Query data

Query all fields

1
SELECT * FROM <table_name>;

Query specified fields

1
SELECT <field_name_1>, <field_name_2>, ... FROM <table_name>;

Limit statements

  • Use WHERE to limit the select statement.
1
SELECT * FROM <table_name> WHERE <field_name>=0;

Operators

1
SELECT 1 + 1;
Numeric operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
== Equal to
!= Not equal to
<> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
and And
or Or
is Is
is not Is not

Modify data

<where>: The where clause.

1
2
3
4
5
# Without a where clause, it modifies all data.
UPDATE <table_name> SET <field_name>=<value>

# With a where clause, it modifies the specified data.
UPDATE <table_name> SET <field_name>=<value> <where>

Delete data

1
2
3
4
5
# Without a where clause, it deletes all data.
DELETE FROM <table_name>

# With a where clause, it deletes the specified data.
DELETE FROM <table_name> <where>

Done