SQLite using step-by-step high

The following is a brief introduction to establishing a database, setting up database tables and indexes, adding data, querying data, changing data, deleting data, sqlite3 command column options, and more.

Create a database

Suppose you want to build a database table called film, just type the following command:
Create table film (title, length, year, starring);
So we set up a database table called film, with name, length, year, and starring four fields.
The syntax for this CREATE TABLE directive is:
CREATE TABLE table_name (field1, Field2, field3, ...);
TABLE_NAME is the name of the database table, and FIELDX is the name of the field. Sqlite3 Unlike many SQL database software, it doesn't care what field type a field belongs to: sqlite3 fields can store anything: text, numbers, lots of text (BLOBs), which are automatically converted at the right time.

Build an index

If a database table has a considerable amount of data, we will build an index to speed it up. Like saying:

Create index Film_title_index on film (title);
This means that for the title field of the Film database table, an index named Film_title_index is created. The syntax of this instruction is
CREATE INDEX index_name on table_name (field_to_be_indexed);
Once an index is established, Sqlite3 automatically uses the index when it makes a query against that field. All of this is done automatically behind the scenes without special instructions from the user.

Insert a record

Next we are going to insert the record, insert the method to use the INSERT INTO directive, the syntax is:
INSERT INTO table_name values (DATA1, data2, data3, ...);
For example, we can insert
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 ', +, A, ' Yun-fat Chow ');
Insert into film values (' Hours, the ', 2002, ' Nicole Kidman ');
If a field has no data, we can fill it with null.


Querying Data

In this case, we are finally going to start with the most powerful select command in SQL. We first briefly introduce the basic sentence pattern of select:
Select columns from table_name where expression_r;
The most common usage, of course, is to query out all the contents of the File table:
SELECT * from film;
If there is too much data, we might want to limit the number of records:
SELECT * from film limit 10;
or older movies are listed first (ASC is the default):
SELECT * FROM film order by year limit 10;
Or a late-year movie, first listed:
SELECT * FROM film order BY year DESC limit 10;
Or we just want to see the movie name and year:
Select title, year from film order by year DESC limit 10;
Look at all the films Julia Jodie has played:
SELECT * from film where starring= ' Jodie Foster ';
Look at all the actors whose names start with Judy's movie (The '% ', ' _ ' symbol is the universal character of SQL, which represents any length character, which represents any one character):
SELECT * from film where starring like ' Jodie% ';
Check all the actors ' names. Start with Judy, the year is later than 1985, the year is a priority, up to 10, only the movie name and year are listed:
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 much data the database contains:
Select COUNT (*) from film;
Sometimes we just want to know that there are several films after 1985:
Select COUNT (*) from film where year >= 1985;
(There are a number of further combinations that you'll want to read in SQL, but you probably already know why SQL is so popular: this language allows you to combine various query conditions-and we haven't mentioned "federated queries across databases"! ）
How to change or delete data

It is important to understand the use of select because it is the same syntax to change or delete a record in SQLite.
For example, there is a record with the wrong name:
Update film set starring= ' Jodie Foster ' where starring= ' Jodee Foster ';
Will be in the protagonist field, is called the ' Jodee Foster ' of that (or more) data, changed back into Jodie Foster.
Delete from film where year < 1970;
Will delete all films dating back to 1970 (excluding 1970).


other special uses 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;"
To "Export" the database:
Sqlite3 film.db ". Dump" > Output.sql
Using the output data, create an identical database (plus the above directive, which is the standard SQL database backup):
Sqlite3 Film.db < Output.sql
When inserting data in large numbers, you may need to hit this command first:
Begin; (Start a transaction)
After inserting the data, remember to call this command, the data will be written into the database:
Commit

SQLite using step-by-step high