SQLite (one) of Android data storage

SQLite Learning Notes

1. Introduction to SQLite

SQLite is a lightweight database, is to comply with acid-related database management system, it is designed to be embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, PHP, Java, C + +,. NET, as well as the ODBC interface, it is faster than the two open source world-famous database management systems, such as Mysql and PostgreSQL.

Features: Lightweight, no need " Installation " , Single file , cross-platform/ portability, weak-type fields, open source

2. SQLite data type

(1) The fixed static data type used in the general data, and SQLite uses the Dynamic data type, will automatically judge according to the deposit value, SQLite has the following five kinds of common data types:

Null: null value

Integer: A signed integer, depending on the range size of the value being deposited

Real: Floating point number, storing 8 bytes of floating-point numbers

Text: string literal

Blob: Binary Object

3. Operation of SQLite database using command mode

Need to configure the environment variables for Android, http://www.cnblogs.com/bluestorm/archive/2012/09/27/2706420.html

(1) Open cmd, go to F-disk, execute Sqlite3 mydb.db, create a mydb.db file

(2) Create a t_student table and insert a piece of data

In SQLite, if the primary key is of type integer but not set to self-increment, then when inserting a data without a primary key, it finds the value of the maximum primary key in the table and then +1 as the primary key for the newly inserted data (the primary key in SQLite uses the AutoIncrement keyword), As follows:

(3) Update data for Sid 2 in the T_student table

(4) Delete data for Sid 2 in the T_student table

(5) Order BY statement ordering, using desc to sort in reverse order

(6) Grouop by statement group, grouped according to Sname

(7) Having a statement to the results of the group after the conditional filtering, the following judgment after grouping data number greater than 1

(8) The limit statement to obtain data, more for paging operations, limit has two parameters, the first is to take the index of the data, starting from 0, the second parameter is the number of data obtained, such as the following to obtain the third and fourth data in the T_student table

The SQL statements that are involved in the above:

Create TableT_student (SIDinteger Primary KeyAutoincrement,snamevarchar( -));Insert  intoT_student (Sid,sname)Values(1,'Zhangsan');SelectSid,sname fromt_student;UpdateT_studentSetSname='Lisi' whereSid= 2;Delete  fromT_studentwhereSid= 2;SelectSid,sname fromT_studentOrder  bySiddesc;SelectSid,sname,Count(sname) fromT_studentGroup  bysname;SelectSid,sname fromT_studentGroup  bySname having Count(sname)> 1;SelectSid,sname fromT_student limit0,2;

SQLite (one) of Android data storage