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

This article mainly introduces "what is SQLite". In daily operation, I believe many people have doubts about what SQLite is. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts about "what is SQLite"! Next, please follow the small series to learn together!

1. introduced

SQLite is an open-source embedded relational database that implements a self-contained, zero-configuration, transaction-enabled SQL database engine. It is characterized by high portability, convenient use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is very simple to install and run, and in most cases-just make sure SQLite binaries exist to start creating, connecting, and using databases. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.

2. installation

SQLite on Windows

Enter SQL download page: www.sqlite.org/download.html

Download the precompiled binary package for Windows:

sqlite-shell-win32-x86-.zip

sqlite-dll-win32-x86-.zip

Note: is the compilation version number of sqlite

Extract the zip file to your disk and add the extracted directory to the PATH variable of your system for executing sqlite commands on the command line.

Optional: If you plan to release an application based on sqlite database, you will also need to download the source code to compile and utilize its API

sqlite-amalgamation-.zip

SQLite on Linux

Several Linux distributions provide convenient commands to get SQLite:

/* For Debian or Ubuntu /*$ sudo apt-get install sqlite3 sqlite3-dev /* For RedHat, CentOS, or Fedora/*$ yum install SQLite3 sqlite3-dev

SQLite on Mac OS X

If you are using Mac OS Snow Leopard or later, SQLite is already installed.

3. Creating the first SQLite database

Now that you have the SQLite database installed, let's create our first database. Create a database named test.db by typing the following command in a command line window:

sqlite3 test.db

Create table:

sqlite> create table mytable(id integer primary key, value text);

The table contains a primary key field named id and a text field named value.

Note: At least one table or view must be created for the new database in order to save the database to disk, otherwise the database will not be created.

Next, write some data into the table:

sqlite> insert into mytable(id, value) values(1, 'Micheal');sqlite> insert into mytable(id, value) values(2, 'Jenny');sqlite> insert into mytable(value) values('Francis');sqlite> insert into mytable(value) values('Kerk');

Query data:

sqlite> select * from mytable;1|Micheal2|Jenny3|Francis4|Kerk

Set formatting query results:

sqlite> .mode column;sqlite> .header on;sqlite> select * from mytable;id value----------- -------------1 Micheal2 Jenny3 Francis4 Kerk

.mode column will be set to column display mode and.header will display column names.

Modify the table structure and add columns:

sqlite> alter table mytable add column email text not null '' collate nocase;;

Create a view:

sqlite> create view nameview as select * from mytable;

Create index:

sqlite> create index test_idx on mytable(value);

4. Some useful SQLite commands

Display table structure:

sqlite> .schema [table]

Get all tables and views:

sqlite > .tables

Gets the index list for the specified table:

sqlite > .indices [table ]

Export database to SQL file:

sqlite > .output [filename ] sqlite > .dump sqlite > .output stdout

Import databases from SQL files:

sqlite > .read [filename ]

Format output data to CSV format:

sqlite >.output [filename.csv ] sqlite >.separator , sqlite > select * from test; sqlite >.output stdout

Import data from CSV files into tables:

sqlite >create table newtable ( id integer primary key, value text ); sqlite >.import [filename.csv ] newtable

Backup database:

/* usage: sqlite3 [database] .dump > [filename] */sqlite3 mytable.db .dump > backup.sql

Restore database:

/* usage: sqlite3 [database ] < [filename ] */ sqlite3 mytable.db

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.