IPhone development application Sqlite User Manual

IPhoneDevelop applicationsSqliteThis document describes the user manual in detail.SqliteIn this article, we hope you can learn moreSqlite. Not to mention, Let's first look at the content.

Create Database Files

UseSqlite3. CreateDatabaseAs long as you type the following $ symbol in shell as the shell prompt number, do not type ):

 
 

  
  
$ 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.3Enter ".help" for instructionssqlite> 
 
 

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 there are two minus signs --), 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:

Sqlite3 fields can store anything: Text, numbers, and a large number of text blub), which will be automatically converted in a timely manner.

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 go to the SQL book, but you probably know why SQL is so popular: This language allows you to combine various query conditions -- we haven't mentioned
「 Cross-database joint query !)

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'; 
 
 

In this case, the information in the main character field is converted into one or more copies of the 'jodee foster' file.

 
 

  
  
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 
 
 

Using the output data, create an identical database and add the preceding commands, that is, 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; 
 
 

Remember to enter this command after inserting the information.DatabaseMedium:

 
 

  
  
commit;  
 
 

Summary:IPhoneDevelop applicationsSqliteThe content of the user manual has been introduced. I hope this article will help you!