Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ SQLite3 simple operation     - Depth understanding of Python character set encoding (Programming)

- KVM usb passthrough configuration (Linux)

- SQLite (Database)

- Linux / Raspberry Pi using the following command-line based web browser (Linux)

- Object Oriented Programming Java reflection (Programming)

- Ubuntu is expected to allow you to install the latest Nvidia Linux drivers easier (Linux)

- Web database security tips (Linux)

- Linux file compression and file system packaged with instructions (Linux)

- OpenSSL for secure transmission and use of skills of files between Windows and Linux (Linux)

- Easily create RPM packages using the FPM (Linux)

- Linux command in the dialog whiptail (Linux)

- Setting Derby as Linux / Windows running as a service from the start (Server)

- Java Collections Framework interfaces map (Programming)

- Python kills corresponding process according to pid (Programming)

- Android Dynamic efficiency articles: a brilliant Loading Analysis and Implementation (Programming)

- Java Builder mode (Programming)

- Five useful commands to manage file types and system time in linux (Linux)

- How to install and use the Snort in Ubuntu 15.04 (Linux)

- Installation of Ubuntu Make under Ubuntu 15.10 (Linux)

- Varnish achieve page jump (Server)

  SQLite3 simple operation
  Add Date : 2018-11-21      
  Table of Contents

1 to establish a database file
2 Tip column operated at sqlite3
3 SQL instruction format
4 Create a Table
5 Indexing
6 added a profile
7 Enquiry
8 How do you change or delete data
9 other sqlite Special Usage
10 Summary
Establishment of a database file

Establishment of a database with the sqlite3 method is very simple, as long as the shell type (hereinafter $ symbol shell prompt number, do not type):

$ Sqlite3 foo.db

If the directory is not under foo.db, sqlite3 will create the database. sqlite3 database file name does not mandate how to get, so if you prefer, you can also take a filename such as foo.icannameitwhateverilike.

Tip column operating in sqlite3

After entering the sqlite3, you will see the following text:

SQLite version 3.1.3

Enter ".help" for instructions


Then if you can get help using .help, .quit is left (please note: not quit)

SQL instruction format

Therefore, the SQL command with a semicolon (;) at the end of. If you have two minus (-) represents the comment, sqlite3 will be slightly in the past.

Create a Table

Suppose we want to build a data table named film, just type the following command can be:

create table film (title, length, year, starring);

So we built a table called the film, which has a name, length, year, starring four fields.

This create table command syntax is:

create table table_name (field1, field2, field3, ...);

table_name is the name of the table, fieldx is the name of the field. sqlite3 with many SQL database software is different, it does not matter which part of the field data type: sqlite3 fields can store anything: text, numbers, a lot of text (blub), it will be automatically converted in a timely manner.


If the table has a considerable amount of information, we will be indexed to speed up. for example:

create index film_title_index on film (title);

It means for the film table name field, establishing a film_name_index index name. The syntax for this directive

create index index_name on table_name (field_to_be_indexed);

Once indexed, sqlite3 will in making queries against that field, automatically uses the index. All this action happens automatically in the background without user special instructions.

Added a profile

Next we want to add information, and join methods to use insert into command syntax is:

insert into table_name values (data1, data2, data3, ...);

For example, we can join

insert into film values ( 'Silence of the Lambs, The', 118, 1991, 'Jodie Foster');

insert into film values ( 'Contact', 153, 1997, 'Jodie Foster');

insert into film values ( 'Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');

insert into film values ( 'Hours, The', 114, 2002, 'Nicole Kidman');

If the field is no information, we can fill in NULL.


Mentioned here, we finally began to introduce SQL select command the most powerful. We first briefly introduce the basic sentence select:

select columns from table_name where expression;

The most common usage, of course, poured the contents of the database:

select * from film;

If too much information, we might want to limit the items:

select * from film limit 10;

Or arranged according Movie Year:

select * from film order by year limit 10;

Year or more recent movies listed first:

select * from film order by year desc limit 10;

Or we just want to watch movies with names Year:

select title, year from film order by year desc limit 10;

Check all played Jodie Foster's movie:

select * from film where starring = 'Jodie Foster';

At the beginning of the investigation all the actors named Judy movies ( '%' symbol is the SQL wildcard characters):

select * from film where starring like 'Jodie%';

Check all the actors name begins with Judy, years later in 1985, the year listed first night, up to ten pens, list only the name and the year the film:

select title, year from film where starring like 'Jodie%' and year> = 1985 order by year desc limit 10;

Sometimes we just want to know how many pens a database of information:

select count (*) from film;

Sometimes we just want to know that since 1985 the film has a few:

select count (*) from film where year> = 1985;

(Further various combinations, to see the SQL Books, but you probably already know why so popular SQL: This language allows you to combine various search criteria -- And we have not mentioned the "cross-database joint inquiry 'too!)

How to change or delete data

Learn select the usage is very important because you want to change or delete information in a sqlite, also rely on the same syntax.

For example, the name of a data wrong:

update film set starring = 'Jodie Foster' where starring = 'Jodee Foster';

The field will be the protagonist, was labeled 'Jodee Foster' that pen (or pen) data, change back into Jodie Foster.

delete from film where year <1970;

It will remove all the years prior to the 1970 film (not included) of the.

Other special usage of sqlite

sqlite can execute commands directly under the shell:

sqlite3 film.db "select * from film;"

Output HTML table:

sqlite3 -html film.db "select * from film;"

The database "pour out":

sqlite3 film.db ".dump"> output.sql

The use of the output data, the establishment of an identical database (add more instructions, that is a standard SQL database backup):

sqlite3 film.db
When inserted into a large number of data, you may need to hit this command:


After inserting End To remember playing this command, the data will be written into the database:

- Zabbix installation and configuration process (Server)
- Linux Getting Started tutorial: Experience VirtualBox Virtual Machine chapter (Linux)
- Oracle View Object Information (Database)
- Killall five options you might not know (Linux)
- CentOS 7 - use cgroups limit process resource (Linux)
- To create a file in Linux directory by setfacl (Linux)
- Common data structures and functions of Linux process scheduling (Programming)
- Installation and Configuration Munin monitoring server on Linux (Server)
- Let the router be your security barrier against external attack (Linux)
- Python3 multi-thread download codes (Programming)
- Attic-- delete duplicate data backup program (Linux)
- Qt for file splitting and fusion gadgets (Programming)
- Oracle user lock how to know what causes (Database)
- GO five stages of language learning (Programming)
- Linux Learning Notes: Users and Groups (Linux)
- When Linux Detailed time zone and common function of time (Linux)
- OpenWRT environment to build (Linux)
- Encrypted with GnuPG signature to verify the authenticity and integrity of downloaded file (Linux)
- Arronax allows you to easily create desktop startup file (Linux)
- Denyhosts prevent hackers using SSH scanning (Linux)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.