SQLite

From ALT Linux Wiki

Русский оригинал этого перевода текста.

SQLite is a local database. Nevertheless, it allows you to use the advantages and concepts of the classic SQL standard language. SQLite does not use the client-server paradigm, the software stores all data on one device. The DBMS is built into the application and works as its integral part. The database format is one file.


Installation

Before installing SQLite, it is highly recommended to upgrade your system.

You can read more about updating the system here RPM. 

$ su-
# apt-get update && apt-get dist-upgrade

After we have updated the system, we can start installing the SQLite package. 

# apt-get install sqlite3

Meta Commands

Meta commands are mainly used for administrative operations such as checking databases and specifying output formats. All of these commands are unique in that they always begin with a dot (.). Here are some of the more common ones:


Command Description
.dump Make a copy of the database in text format
.show Shows the current settings of the given parameters
.databases Shows the name of databases and files
.quit Exit
.tables Show current tables
.schema Table structure
.header Display table header
.mode Selecting the table display mode

Standard Commands

When working with SQLite, there are common commands used for various actions in the database. They are called standard commands because they are the most commonly used. They are classified into three groups according to their different functions throughout the volume.

The first group is the commands responsible for the storage structure, as well as methods for accessing data from the database:

  • CREATE
  • DROP
  • ALTER

Second group is the commands for the data manipulation:

  • INSERT
  • UPDATE
  • DELETE

The third type of commands are commands that allow users to get certain data from databases:

  • SELECT

Creating, modifying and deleting a database

In this tutorial, we will cover only the basic work with databases.

Database creation

First, let's create a database (the database name must be unique). This does not require superuser privileges (su -). 

$ sqlite3 altlinux.db
sqlite> .databases

A screenshot showing that our database named altlinux has been successfully created.

If you exit the SQLite program and want to reconnect your database, you must use the command: 

 sqlite>.open altlinux.db


Create tables

Creating a table means that you must name the table, define the columns, and define the data type for each column. A semicolon (;) must be used to terminate any command. Table creation example: 

 sqlite> CREATE TABLE products(
   ...> ID INT PRIMARY KEY NOT NULL,
   ...> NAME TEXT NOT NULL,
   ...> UNITS INT NOT NULL,
   ...> PRICE INT,
   ...> DISCOUNT REAL
   ...>);

Let's see how it looks on the command line.

The table with name "products" has been created: 

 sqlite> .tables
 products

Delete table

This command is used when a developer wants to delete a table along with all its contents. You should always be careful when using this command because once a table is dropped, all subsequent data is lost forever. 

sqlite> DROP TABLE products

ALT Linux OS packages

  • sqlite3 - CLI utility that allow you to create and work with SQLite datafiles from the command line
  • sqlite3-doc - API documentation
  • Bindings:
  1. sqlite3-tcl - TCL. By the way, TCL & SQLite has one author. SQLite was part of TCL before it became indepedent project.
  2. perl-DBD-SQLite - DBI/DBD driver for the Perl programming language
  3. python3-modules-sqlite3 - Pyhton 3 modules
  4. libsqlite3-devel - classical C & object oriented C++ headers

Sources

  1. English
  2. Russian
For the system administrator
 
SQLite