Learning SQLite database within one hour

Source: Internet
Author: User
Tags import database

1. Introduction

SQLite is an open-source embedded Relational Database Engine for SQL database with self-contained, zero configuration, and transaction support. It is highly portable, easy to use, compact, efficient, and reliable. Unlike other database management systems, SQLite is easy to install and run. In most cases, you can create, connect to, and use a database by ensuring that the binary file of SQLite exists. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.

2. Installation

SQLite on Windows

  1. Go to the SQL download page: http://www.sqlite.org/download.html
  2. Download the pre-compiled Binary Package in Windows:
    • Sqlite-shell-win32-x86-<Build #>. Zip
    • Sqlite-dll-win32-x86-<Build #>. Zip
    Note: <build #> is the sqlite compilation version.
  3. Decompress the zip file to your disk and add the decompressed directory to the PATH variable of the system to execute the sqlite command in the command line.
  4. Optional: If you plan to release an application based on the sqlite database, you also need to download the source code to compile and use its API
    • Sqlite-amalgamation-<Build #>. Zip

SQLite on Linux

Multiple Linux releases provide convenient commands to obtain SQLite:

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

SQLite on Mac OS X

If you are using Mac OS snow leopard or a newer version of the system, SQLite is installed on the system.

3. Create the first SQLite Database

Now that you have installed the SQLite database, create the first database. In the command line window, enter the following command to create a database named test. db.

1 sqlite3 test.db

Create a table:

1 sqlite> create table mytable(id integer primary key, value text);
3 2 columns were created.

This 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 newly created database to save the database to the disk. Otherwise, the database will not be created.

Next, write some data to the table:

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

Query data:
1 sqlite> select * from test;
2 1|Micheal
3 2|Jenny
4 3|Francis
5 4|Kerk
Set to format the query result:
1 sqlite> .mode column;
2 sqlite> .header on;
3 sqlite> select * from test;
4 id          value
5 ----------- -------------
6 1           Micheal
7 2           Jenny
8 3           Francis
9 4           Kerk

. Mode column is set to column Display mode, And. header is used to display column names.

Modify the table structure and add columns:

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

Create View:
1 sqlite> create view nameview as select * from mytable;
Create an index:
1 sqlite> create index test_idx on mytable(value);

4. Some useful SQLite commands

Display table structure:
Sqlite>. Schema[Table]

Obtain all tables and views: sqlite >. Tables

Obtain the index list of the specified table: sqlite >. Indices [Table ]
Export the database to the SQL file: sqlite >. Output [Filename ]
Sqlite >. Dump
Sqlite >. Output stdout
Import database from SQL file: 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 a CSV file to a table: sqlite >Create table newtable ( IdInteger primary key, value text );
Sqlite >. Import [Filename.csv ]Newtable

Back up the database:
/*Usage: sqlite3[Database]. Dump> [Filename] */
Sqlite3 mytable. db. dump>Backup. SQL

Restore database: /*Usage: sqlite3 [Database ] < [Filename ] */
Sqlite3 mytable. db <Backup. SQL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.