Table of Contents
1 to establish a database file
2 Tip column operated at sqlite3
3 SQL instruction format
4 Create a Table
6 added a profile
8 How do you change or delete data
9 other sqlite Special Usage
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):
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: