Learn the basic syntax of SQLite

Source: Internet
Author: User
Tags sqlite sqlite database win32



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



2. Installation



SQLite on Windows


    1. Go to SQL download page: http://www.sqlite.org/download.html
    2. To download the precompiled binaries package under Windows:
      • sqlite-shell-win32-x86-<build#>. zip
      • sqlite-dll-win32-x86-<build#>. zip
      Note: <build#> is the compiled version number of SQLite
    3. Unzip the zip file to your disk and add the extracted directory to the system's PATH variable for easy execution of the SQLite command at the command line.
    4. Optional: If you plan to publish an application based on the SQLite database, you will also need to download the source code to compile and utilize its API
      • sqlite-amalgamation-<build#>. zip


SQLite on Linux



A number of Linux distributions provide convenient commands to get SQLite:

/* For Debian or Ubuntu /*$sudoapt-getinstallsqlite3 sqlite3-dev/* For RedHat, CentOS, or Fedora/*$ yuminstallSQLite3 sqlite3-dev


SQLite on Mac OS X



If you are using Mac OS Snow Leopard or a newer version of the system, then the system is already equipped with SQLite.



3. Create your first SQLite database

Now that you have the SQLite database installed, we will create the first database. In the Command Line window, enter the following command to create a database named Test.db.


sqlite3 /data/test.db  (Open if there is, new if no)To Create a table:sqlite> create table mytable(idinteger primary key, value text);2 columns were created.


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



Note: At a minimum, you must create a table or view for the newly created database so that you can save the database to disk or the database will not be created.



Next, write some data into the table:


sqlite> insertintomytable(id, value) values(1, ‘Micheal‘);sqlite> insertintomytable(id, value) values(2, ‘Jenny‘);sqlite> insertintomytable(value) values(‘Francis‘);sqlite> insertintomytable(value) values(‘Kerk‘);


Query data:


sqlite> select* fromtest;1|Micheal2|Jenny3|Francis4|KerkTo set the results of a formatted query:sqlite> .mode column;sqlite> .header on;sqlite> select* fromtest;id          value----------- -------------1           Micheal2           Jenny3           Francis4           Kerk


The. Mode column is set to the column display mode, and the. Header displays the column name.



To modify the table structure, add columns:


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


To create a view:


sqlite>createviewnameviewasselect*frommytable;To create an index:sqlite> create index test_idx on mytable(value);4. Some useful SQLite commands



Show Table structure:
sqlite>. Schema [table]



Get all tables and views:


SQLite >. Tables

Gets the list of indexes for the specified table: SQLite >. indices [table]



To export a database to a SQL file:


SQLite >. output [filename]
SQLite > Dump
SQLite >. Output stdout



To import a database from a 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 into a table:


SQLite >create table newtable (ID integer primary key, value text);
SQLite >.import [Filename.csv] NewTable


To back up the database:
/* Usage:sqlite3 [Database]. dump > [filename] */
Sqlite3 mytable.db dump > Backup.sql



To recover a database:


/* Usage:sqlite3 [Database] < [filename] */
Sqlite3 Mytable.db < Backup.sql turn from: http://www.oschina.net/question/12_53183


Learn the basic syntax of SQLite


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.