Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use SQLite3". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to use SQLite3.

Start using this powerful and versatile database.

Applications often need to save data. Whether your users are creating simple text documents, complex graphic layouts, game schedules, or intricate lists of customer and order numbers, software usually means generating data. There are many ways to store data for reuse. You can dump the text into INI, YAML, XML, or JSON configuration formats, output raw binary data, or store the data in a structured database. SQLite is a self-contained, lightweight database that makes it easy to create, parse, query, modify, and transfer data.

Download SQLite3 memo

SQLite is dedicated to the public domain, which technically means that it has no copyright and therefore does not require a license. If you need a license, you can buy a title guarantee. SQLite is very common, and there are about 1 trillion SQLite databases in use. In every Webkit-based Web browser, modern TV, automotive multimedia system, and countless other software applications, Android and iOS devices, macOS and Windows 10 computers, most Linux systems contain multiple such databases.

All in all, it is a reliable and simple system for storing and organizing data.

Installation

You may already have a SQLite library on your system, but you need to install its command-line tools to use it directly. You may have installed these tools on Linux. The command provided by this tool is sqlite3 (not just sqlite).

If you do not have SQLite installed on your Linux or BSD, you can install SQLite from the software repository or ports tree, or you can download and install it from source code or compiled binaries.

On macOS or Windows, you can download and install the SQLite tool from sqlite.org.

Use SQLite

It is common to interact with a database through a programming language. As a result, programming languages like Java, Python, Lua, PHP, Ruby, C++, and other programming languages provide interfaces (or "bindings") to SQLite. However, before using these libraries, it is helpful to understand the reality of the database engine and why your choice of databases is important. This article introduces you to the SQLite and sqlite3 commands so that you can familiarize yourself with the basics of how the database handles data.

Interact with SQLite

You can use the sqlite3 command to interact with SQLite. This command provides an interactive shell program so that you can view and update the database.

$sqlite3SQLite version 3.34.0 2020-12-01 16:14:00Enter ".help" for usage hints.Connected to a transient in-memory database.Use ".open FILENAME" to reopen on a persistent database.sqlite >

This command puts you in a child shell of SQLite, so the prompt now is the prompt for SQLite. The Bash command you used previously will no longer apply here. You must use the SQLite command. To view a list of SQLite commands, enter .help:

Sqlite > .help.archive... Manage SQL archives.auth ON | OFF SHOW authorizer callbacks.backup? DB? FILE Backup DB (DEFAULT "main") TO FILE.bail ON | off Stop after hitting an error. DEFAULT OFF.binary ON | off Turn BINARY output ON OR off. DEFAULT OFF.cd DIRECTORY CHANGE the working directory TO DIRECTORY [...]

Some of these commands are binary, while others require unique parameters (such as file names, paths, and so on). These are administrative commands for SQLite Shell, not for database queries. The database is queried in structured query language (SQL), and many SQLite queries are the same as those you already know from MySQL and MariaDB databases. However, data types and functions are different, so if you are familiar with another database, pay special attention to subtle differences.

Create a database

When you start SQLite, you can open the in-memory database or select the database to open:

$sqlite3 mydatabase.db

If you do not already have a database, you can create a database at the SQLite prompt:

Sqlite > .open mydatabase.db

Now, you have an empty file on your hard drive that can be used as an SQLite database. The file extension .db is arbitrary. You can also use .sqlite or any suffix you want.

Create a table

The database contains tables table that can be visualized as spreadsheets. There are many rows (called record record in the database) and columns. The intersection of rows and columns is called the field field.

Structured query language (SQL) is named for what it provides: a method of querying database content in a predictable and consistent syntax to receive useful results. SQL reads a lot like ordinary English sentences, even if it's a little mechanized. Currently, your database is an empty database without any tables.

You can use CREATE to create a new table, and you can use it with IF NOT EXISTS. So that it will not destroy the existing table of the same name.

You cannot create an empty table without any fields in SQLite, so you must consider the type of data that the table will store before you try the CREATE statement. In this example, I will create a table named member with the following columns:

Unique identifier

Name of person

Record the time and date of creation

Unique identifier

It's best to reference the record with a unique number, but fortunately, SQLite recognizes this and creates a column called rowid to automatically do this for you.

This field can be created without a SQL statement.

Data type

For my sample table, I am creating a name column to hold data of type TEXT. To prevent records from being created without specifying field data, you can add a NOT NULL directive.

Create it with a name TEXT NOT NULL statement.

There are five data types (actually storage categories) in SQLite:

TEXT: text string

INTEGER: a number

REAL: a floating point number (unlimited number of decimal places)

BLOB: binary data (for example, .jpeg or .webp images)

NULL: null

Date and time stamp

SQLite has a convenient date and time stamp function. It is not a data type itself, but a function in SQLite that generates strings or integers based on the desired format. In this example, I leave it as the default value.

The SQL statement to create this field is: datestamp DATETIME DEFAULT CURRENT_TIMESTAMP.

Create a statement for a table

Create the full SQL of this sample table in SQLite:

Sqlite > CREATE TABLE... > IF NOT EXISTS... > member (name TEXT NOT NULL,... > datestamp DATETIME DEFAULT CURRENT_TIMESTAMP)

In this code example, I press enter after the clause of the statement. To make it easier to read. SQLite will not run your SQL statement unless terminated with a semicolon (;).

You can use the SQLite command .tables to verify that the table has been created:

Sqlite > .tablesmember view all columns in the table

You can use the PRAGMA statement to verify which columns and rows the table contains:

Sqlite > PRAGMA table_info (member); 0 | name | TEXT | 1 | | 01 | datestamp | DATETIME | 0 | CURRENT_TIMESTAMP | 0 data input

You can use the INSERT statement to populate some sample data into the table:

> INSERT INTO member (name) VALUES ('Alice'); > INSERT INTO member (name) VALUES (' Bob'); > INSERT INTO member (name) VALUES ('Carol'); > INSERT INTO member (name) VALUES (' David')

View the data in the table:

> SELECT * FROM member;Alice | 2020-12-15 22:39:00Bob | 2020-12-15 22:39:02Carol | 2020-12-15 22:39:05David | 2020-12-15 add multiple rows of data at 22:39:07

Now create a second table:

> CREATE TABLE IF NOT EXISTS linux (. > distro TEXT NOT NULL)

Fill in some sample data, this time using small VALUES shortcuts, so you can add multiple lines to a command. The keyword VALUES expects lists to be listed in parentheses, while multiple lists are separated by multiple commas:

> INSERT INTO linux (distro)... > VALUES ('Slackware'), (' RHEL'),... > ('Fedora'), (' Debian'); modify the table structure

You have two tables now, but so far, there is no relationship between the two. Each of them contains separate data, but you may need to associate the members of the first table with specific items listed in the second table.

To do this, you can create a new column for the first table, which corresponds to the second table. Because both tables are designed to have unique identifiers (thanks to the automatic creation of SQLite), the easiest way to join them is to use the rowid field of one as a selector for the other.

Create a new column in the first table to store the values in the second table:

> ALTER TABLE member ADD os INT

Use the unique identifier in the linux table as the value of the os field in each record in the member table. Because the record already exists. So you can use UPDATE statements instead of INSERT statements to update data. It is important to note that you need to select a specific row before you can update one of the fields. Syntactically, this is a bit on the contrary, the update occurs first, and the choice match occurs last:

> UPDATE member SET os=1 WHERE name='Alice'

Repeat the same process for other rows in the member table. Update the os field to allocate three different distributions (one of which doubles) on four rows of records for data diversity.

Join table

Now that the two tables are related to each other, you can use SQL to display the associated data. There are many ways to join in the database, but once you have mastered the basics, you can try all of them. This is a basic join that associates the value in the os field of the member table with the rowid field of the linux table:

> SELECT * FROM member INNER JOIN linux ON member.os=linux.rowid;Alice | 2020-12-15 22:39:00 | 1 | SlackwareBob | 2020-12-15 22:39:02 | 3 | FedoraCarol | 2020-12-15 22:39:05 | 3 | FedoraDavid | 2020-12-15 22:39:07 | 4 | Debian

The os and rowid fields are associated.

In a graphical application, you can imagine that the os field is a drop-down menu of options where the value is the data in the distro field in the linux table. Associating related datasets through unique fields ensures data consistency and validity, and with SQL, you can associate them dynamically in the future.

Learn more

SQLite is a very useful self-contained, portable open source database. Learning to use it interactively is an important first step towards managing Web applications or using it through programming language libraries.

Thank you for your reading, the above is the content of "how to use SQLite3", after the study of this article, I believe you have a deeper understanding of how to use SQLite3, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.