Simple introduction and application of Sqlite

Simple introduction and application of Sqlite (I) Why should I use sqlite3? I believe many people do not know much about sqlite at the beginning like me. I don't understand why sqlite is used. Why not use Mysql (also one of my favorite databases )? In fact, the two most important points of my understanding are simple, convenient, and exquisite. However, I still want to share the features of sqlite with you.

Simple introduction and application of Sqlite (I) Why should I use sqlite3? I believe many people do not know much about sqlite at the beginning like me. I don't understand why sqlite is used. Why not use Mysql (also one of my favorite databases )? In fact, the two most important points of my understanding are simple, convenient, and exquisite. However, I still want to share the features of sqlite with you.

Simple introduction and application of Sqlite

(1) Why sqlite3?

I believe many people do not know much about sqlite at the beginning like me. I don't understand why sqlite is used. Why not use Mysql (also one of my favorite databases )? In fact, the two most important points of my understanding are simple, convenient, and exquisite. But let's talk about the features of sqlite,

Advantages of sqlite3: www.2cto.com

(1) The vast majority of SQL92 standards are implemented. The entire database is stored in a single file.

(2) database files can be freely shared between machines of different bytes.

(3) supports binding in multiple languages, such as C/C ++, python, and java.

(4) relatively popular Client/Server database engines run faster

(5) There is no external dependency. The source code is located in the public domain and can be used for any purpose.

(6) the source code is open, and code 95% has better comments.

(7) No need for configuration, installation, or administrator

(8) sqlite is not a client used to connect to large database servers, but a database server suitable for desktop programs (such as dictionaries) and small websites.

(9) sqlite directly reads and writes database files on the hard disk.

(2) install www.2cto.com on sqlite

(1) Use the system package manager for Installation

Sudo apt-get install sqlite3 sqlite3-doc

If you want to install the graphic interface, enter the following command

Sudo apt-get install sqliteman-doc

(2) Compile and install

Go to the official website (www.sqlite.org) to download the compressed package sqlite-3. *. * .tar.gz

$ Tar-xvf sqlite-3. *. * .tar.gz

$ Cd sqlite-3 .*.*

$./Configure

$ Make

$ Sudo make install

(3) simple use of sqlite

Sqlite3 is compatible with most SQL92 syntaxes, so we only need to learn a few special sqlite3 commands. However, for the sake of completeness, I will demonstrate some basic operations below (if you cannot understand them, I think you need to add some knowledge about the database :-))

First, execute sqlite *. db in the command line to create a new library file, and all our data is saved in this file.

$ Sqlite3 test. db

SQLite version 3.7.9 2011-11-01 00:52:41

Enter ". help" for instructions

Enter SQL statements terminated with ";"

Sqlite> create table test (www.2cto.com

...> ID integer primary key,

...> Name varchar (12)

...> );

Sqlite> insert into test values (1, "one ");

Sqlite> insert into test values (2, "Two ");

Sqlite> insert into test values (3, "Three ");

Sqlite> select * from test;

1 | one

2 | Two

3 | Three

Sqlite> update test set name = "One" where ID = 1;

Sqlite> select * from test;

1 | One

2 | Two

3 | Three

Sqlite> delete from test where ID = 3;

Sqlite> create index ID_INDEX on test (ID );

All right, the above operations comply with the SQL92 standard, and some of the following commands are the characteristics of sqlite

The special commands of sqlite are as follows. for example, the prompt "Enter" is displayed when you Enter the command line of sqlite ". help "for instructions", where ". help "is a special command, we enter. help will see the following information, all of which are ".. Www.2cto.com

Sqlite>. help

. Backup? DB? FILE Backup DB (default "main") to FILE

. Bail ON | OFF Stop after hitting an error. Default OFF

. Databases List names and files of attached databases

. Dump? TABLE? ... Dump the database in an SQL text format

......

There are several frequently used ones here.

Sqlite>. tables -- display all tables

Test

Sqlite>. schema -- display all definitions

Create table test (

ID integer primary key,

Name varchar (12)

);

Create index ID_INDEX on test (ID );

In addition

. Quit |. exit to exit

. Output FILENAME backup database

For other online documents, see the. help Command.

(4) special usage of sqlite www.2cto.com

Sqlite can directly run commands in shell

$ Sqlite3 test. db "select * from test;" -- displays the table content

$ Sqlite3-html test. db "select * from test;" -- output the table as HTML

$ Sqlite3 test. db ". dump"> test. SQL -- backup database

$ Sqlite3 test. db <test. SQL -- output database

(5) operate sqlite using C/C ++

If we use the Package Manager to install sqlite3, we are sorry that we did not use C to operate sqlite3 required library, we must install libsqlite3-dev

$ Sudo apt-get install libsqlite3-dev

If you compile the SQL statement, congratulations. You can use C to operate sqlite3. Below is a test database creation, insertion, deletion, and update compiled by me, a c program such as query is ugly for everyone.

// Name: testdb. c

// Autor: prape

// Date: 2012-10-7

# Include

# Include

# Include

Int main (int argc, char ** argv ){

Sqlite3 * db = NULL; www.2cto.com

Char * errmsg = 0;

Int sf; // open the status returned by the sqlite3 Database

// Open the database

Sf = sqlite3_open ("test. db", & db); // open test. db and pay the opened database to db.

If (sf ){

Printf ("can't open database test. db \ n ");

Sqlite3_close (db );

Return 0;

}

Else {

Printf ("open test. db successfully \ n ");

} Www.2cto.com

// Create a database

Char * SQL = "create table test (\

ID integer primary key ,\

Name varchar (12 )\

);";

Sqlite3_exec (db, SQL, 0, 0, & errmsg );

// Insert record

SQL = "insert into \" test \ "values (1, \" one \");";

Sqlite3_exec (db, SQL, 0, 0, & errmsg );

SQL = "insert into \" test \ "values (2, \" Two \");";

Sqlite3_exec (db, SQL, 0, 0, & errmsg );

SQL = "select * from \" test \";";

// Query records

Int row = 0, column = 0;

Char ** result;

Sqlite3_get_table (db, SQL, & result, & row, & column, & errmsg );

Int I = 0;

For (I = 0; I <(row + 1) * column; I ++)

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

Sqlite3_free_table (result); // clear the memory occupied by the table

SQL = "delete from \" test \ "where ID = 3 ;";

Sqlite3_exec (db, SQL, 0, 0, & errmsg );

Sqlite3_close (db );

Return 0; www.2cto.com

}

Then compile and execute

$ Gcc testdb. c-lsqlite3-o testdb.exe

If you have installed the libsqllite3-dev before, you must have compiled it successfully, but remember to link the sqlite3 library.

There are still many shortcomings in this program. For example, it will be much better to print the table after the content of each table is changed, but the code is repeated. I will not (I am a little lazy) Here ), let's leave the content for everyone to try.