[SQLite] -- usage record

I. How to install sqlite3 in Linux:

1. Tar-xvzf sqlite3....tar.gz

2. CD sqlite3 ..

3../configure-Disable-TCL -- prefix =/usr/local/sqlite3

4. Make; make install

Ii. Basic addition, deletion, modification, and query operations

Insert: insert into <Table Name> values (...) // table data

Create Table: Create Table <Table Name> (...) // the data contained in the table and its type

Query: Select * from <Table Name>

Callback method in C: sqlite3_exec (dB, SQL, select_callback, DB, & zerrmsg );

Int select_callback (void * notused, int argc, char * argv, char ** azcolname)

After the callback function is called in sqlite3_exec, argc stores the number of queried data records, argv stores the data values, and azcolname stores the names of each data item; the first parameter passed by the second dB to the callback function, which is not used;

Non-callback method in C: sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg)

For (I = 0; I <(nrow + 1) * ncolumn; I ++)

Printf ("value [% d] = % s \ n", I, azresult [I]);

The Select result is saved in the azresult, The nrow stores the number of rows of the retrieved data, and the ncolumn stores the number of columns of the retrieved data.

Modify: Update <Table Name> set <column Name> = "New Value" Where condition // if no condition is set, this column will be updated

Delete: delete from <Table Name> where <condition> // if no conditions are set, the records used are deleted.

Alter table: the alter table command of SQLite only allows users to rename or add new fields to existing tables. fields cannot be deleted from the table, and only columns can be added at the end of the table;

Modify Table Name: alter table <old table name> Rename to <new Table Name>

Add a column: alter table <Table Name> Add column <column Name> <data type> [qualifier]

Delete table: Drop table <Table Name>

Iii. Common sqlite3 command line commands

Go to sqlite3 command line mode and run the command with "." + "keyword"

. Tables -- list tables contained in the current database

. Schema <Table Name> -- displays the operations performed during table creation.

. Headers on -- display the header information during output

. Database -- display database information

. Mode [] -- set the display mode: CSV, column, HTML, insert, line, list, tabs, and TCL, such as. Model CSV.

4. import data from Excel

1. Create a table in test. DB: Create Table bookroom (ID integer, roomname nvarchar (20), mapfilename nvarchar (20 ));

2. Create an exc file and generate data in CSV format. The format is the same as that in the preceding table, as shown below:

30001, mathematics, no. 1, third floor

30002, Chinese language, No. 18, 2nd floor

Save excel as CSV: bookroom.csv

3. import data:

① Open the database and enter the sqlite3 command line mode, sqlite3 test. DB

②. Separator ','

③ Import bookroom.csv bookroom

Or:

# Sqlite3 test. DB ". Import bookroom.csv bookroom"

4. export data from the database to the TXT file

# Sqlite3-header test. DB "select * From bookroom;"> license.txt

References:

Http://www.2cto.com/database/201112/113683.html

Http://it.100xuexi.com/ExtendItem/OTDetail.aspx? Id = 15123afe-d40f-4439-b14b-f08c034ce295

Http://blog.chinaunix.net/uid-1844931-id-2981048.html

Http://www.cnblogs.com/nbsofer/archive/2012/05/02/2479168.html