Sqlite3 Simple operation

Directory

• 1 Setting Up Database Archives
• 2 operating under the SQLITE3 hint column
• 3 SQL instruction format
• 4 Creating a data table
• 5 building an index
• 6 Add a piece of information
• 7 Search Information
• 8 How to change or delete data
• 9 Special usage of other SQLite
• Ten Summary

Set Up Database Archives

The way to build a database with Sqlite3 is simple, just type it under the shell (the following $ symbol is the shell prompt, do not type):

$ sqlite3 foo.db

If there is no foo.db,sqlite3 in the directory, this database will be established. Sqlite3 does not enforce the database file name, so if you like, you can also take a file name such as Foo.icannameitwhateverilike.

Operating under the SQLITE3 hint column

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

SQLite version 3.1.3
Enter '. Help ' for instructions

At this point, if you use. Help, you can get assistance. Quit is leave (Please note: not quit)

instruction format for SQL

So the SQL instructions are all terminated with a semicolon (;). If a two minus sign (--) is encountered, the sqlite3 will be slightly over.

Create a data table

Suppose we want to build a table called film, just type the following command:

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

So we set up a 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 table, and FIELDX is the name of the field. Sqlite3 Unlike many SQL database software, it doesn't care about which data type a field belongs to: Sqlite3 's field can store anything: text, numbers, lots of text (blub), and it will be automatically converted in a timely manner.

Build an index

If the table has quite a lot of information, we will build the index to speed up. Like saying:

Create index Film_title_index on film (title);

This means that for the name field of the film table, an index named Film_name_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.

Add a piece of information

Next we want to add the data, the method to join is to use the INSERT INTO directive, the 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 ', +, A, ' Yun-fat Chow ');
Insert into film values (' Hours, the ', 2002, ' Nicole Kidman ');

If the field has no data, we can fill in null.

Enquiry Information

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;

The most common usage, of course, is to pour out the contents of all databases:

SELECT * from film;

If there is too much information, we may want to limit the number of pens:

SELECT * from film limit 10;

or according to the year of the film:

SELECT * FROM film order by year limit 10;

Or a movie that is closer to the year, listed first:

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):

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 a profile

It is important to understand the use of select because it is the same syntax to change or delete a piece of data in SQLite.

For example, the name of a piece of information is incorrectly called:

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

Will be the protagonist in the field, was beaten to ' Jodee Foster ' the pen (or more pen) information, changed back into Jodie Foster.

Delete from film where year < 1970;

It will delete all films that were older than 1970 (not included).

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 "pour out" 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 a large amount of data, you may need to call this command first:

Begin

After inserting the data, remember to call this command, the data will be written into the database:

Commit

Sqlite3 Simple operation