SQLite3 simple operation

SQLite3 simple operation
Directory

• 1. Create Database Files
• 2. operate in the sqlite3 prompt Column
• 3. SQL command format
• 4. Create a data table
• 5. Create an index
• 6. Add a document
• 7. query data
• 8. How to change or delete Materials
• 9 other special usage of sqlite
• 10 Summary

Create Database Files

The method for creating a database with sqlite3 is very simple. You only need to type it in shell (the $ symbol below is the shell prompt number. Do not type it ):

$ sqlite3 foo.db

If the directory does not contain foo. db, sqlite3 will create this database. Sqlite3 does not force the database file name to be obtained. Therefore, if you like it, you can also obtain a file name such as foo. icannameitwhateverilike.

Operate in the sqlite3 prompt Column

After entering sqlite3, you will see the following text:

SQLite version 3.1.3
Enter ".help" for instructions
sqlite> 

At this time, if you use. help, you can get help, And. quit is to leave (Note: Not quit)

SQL Instruction format

Therefore, all SQL commands end with a semicolon. If two minus signs (--) are encountered, it indicates the annotation, and sqlite3 will be omitted.

Create a table

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

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

In this way, a data table named film is created, which contains four fields: name, length, year, and starring.

The syntax of the create table command is:

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

Table_name is the name of the data table, while fieldx is the name of the field. Unlike many SQL database software, sqlite3 does not care which data type the field belongs to. sqlite3 can store anything: Text, numbers, and a large amount of text (blub ), it will be automatically converted in due time.

Create an index

If a table contains a large amount of data, we will create indexes to speed up the process. For example:

create index film_title_index on film(title);

Create an index named film_name_index for the name field of the film data table. The syntax of this command is

create index index_name on table_name(field_to_be_indexed);

Once an index is created, sqlite3 will automatically use this index when querying this field. All these operations are automatically performed behind the scenes without special instructions from the user.

Add a document

Next, we want to add the information by using the insert into command. The syntax is as follows:

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

For example, we can add

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 this field does not contain any data, we can enter NULL.

Query Materials

At this point, we will finally begin to introduce the most powerful SQL select command. First, let's briefly introduce the basic sentence pattern of select:

select columns from table_name where expression;

The most common usage is, of course, to output the content of all databases:

select * from film;

If there are too many materials, we may want to limit the number of records:

select * from film limit 10;

Or sort by movie year:

select * from film order by year limit 10;

Or movies with relatively near year are listed first:

select * from film order by year desc limit 10;

Or we only want to watch the movie name and year:

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

Check all the movies played by jutifster:

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

Check all movies whose names start with "Judy" (The '%' symbol is a-character SQL ):

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

The following lists all actors whose names start with Judy, whose names are later than January 1, 1985, and whose names are later than the year. A maximum of ten Actives are listed. Only movie names and years are listed:

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

Sometimes we only want to know how many pieces of data a database has:

select count(*) from film;

Sometimes we only want to know how many movies will be made after 1985:

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

(For further combinations, I want to read the SQL book, but you probably already know why SQL is so popular: this language allows you to combine various query conditions -- and we haven't mentioned "cross-database joint query" yet !)

How to change or delete a document

It is very important to understand the usage of select, because the same syntax is used to change or delete a piece of data in sqlite.

For example, if the name of a document is incorrect:

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

The key field is converted to the one (or more) of 'jodee foster' and then to Jodie Foster.

delete from film where year < 1970;

All movies earlier than 1970 will be deleted.

Other special usage of sqlite

Sqlite can directly execute the command under shell:

sqlite3 film.db "select * from film;"

Output HTML table:

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

"Pour out" the database 」:

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

Use the output data to create an identical database (with the preceding command, the standard SQL database is backed up ):

sqlite3 film.db < output.sql

When inserting a large amount of data, you may need to run the following command first:

begin;

After inserting the data, remember to execute this command before the data is written into the database:

commit;

Analysis on the combination of storage and data types in SQLite3

SQLite3 installation and basic operations

Simple Application of SQLite databases in Ubuntu 12.04

How to install SQLite in Ubuntu 12.04

Basics of SQLite Database

SQLite details: click here
SQLite: click here

This article permanently updates the link address: