"Play The SQLite series" (a) Start with SQLite and regain the SQL statement

Reprint Please specify source: http://blog.csdn.net/linglongxin24/article/details/53230842
This article is from "Dylanandroid's blog"

"Play The SQLite series" (a) Start with SQLite and regain the SQL statement

SQLite is a lightweight, embedded database that is used in Android systems. In Android development
We will inevitably use the SQLite database. Next, use a series of articles to data the SQLite database.

I. Learn about SQLite

• 1. What is SQLite

  SQLite is a lightweight database that adheres to the acid-based relational database management system, which is contained in a relatively small C library. It is the public domain project established by D.richardhipp. It is designed to be embedded, and has been used in many embedded products, it occupies a very low resource, in the embedded device, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do. The first alpha version of SQLite was born in May 2000. To 2016 has been 16 years, SQLite also ushered in a version of SQLite 3 has been released.

• 2.SQLite Main function characteristics

  1.ACID transactions

  2.0 Configuration – No installation and management configuration required

  3. A complete database stored in a single disk file

  4. Database files can be freely shared between machines of different byte order

  5. Support database size up to 2TB

  6. Small enough, roughly 130,000 lines C code, 4.43M

  7. Faster than some popular databases in most common database operations

  8. Independence: No additional reliance

  9. Source code is completely open source

  10. Support multiple development languages, C, C + +, PHP, Perl, Java, C#,python, Ruby, etc.

• 3. Supported types that we will often use

  INTEGER, Float,boolean,varchar,text

Second, the basic SQL statement that should be mastered

• 1.CREATE table: Create a sheet

/** *  CREATE TABLE *               IF NOT EXISTS *               表名( *               列名  列类型(大小)  性, *               列名  列类型(大小)  属性, *               列名  列类型(大小)  属性 *              ) * */CREATE TABLE             IF NOT EXISTS             User(                id Integer primary key,                name varchar not null,                age Integer)

• 2.DROP table: Delete a single sheet

/** *  DROP TABLE IF  EXISTS 表名 */DROP TABLE IF  EXISTS User

• 3.INSERT into: Insert a piece of data into a table

/** *  INSERT INTO 表名 VALUES (值,值,值...) *  INSERT INTO 表名(列名,列名,列名...) VALUES(值,值,值...) */INSERT INTO User VALUES (1,‘张三‘,26)INSERT INTO User(id,name,age) VALUES (1,‘张三‘,26)

• 4.UPDATE: Modify one of the data in the table

/** *  UPDATE 表名 SET 字段名=字段值  WHERE 修改的条件表达式 */SET name="李四" WHERE id=2

• 5.DELETE from: Delete A single piece of data from a table

/** *  DELETE FROM 表名 WHERE 删除的条件表达式 */DELETE FROM User WHERE id=2

• 6.SELECT * FROM: Querying data in a table

/** * SELECT * FROM table name where query conditional expression GROUP by Group field order by Sort field * SELECT field name from table name where query conditional expression GROUP BY field of the group order by sort field * /SELECT* from  UserSELECT* from  User WHEREId=2SELECTName,age from  User WHEREAge> -SELECTName,age from  User WHEREAge between -ADN +SELECTName,age from  User WHEREName like "Light"SELECTName,age from  User WHEREName is NULLSELECTName,age from  User  ORDER  byAge

"Play The SQLite series" (a) Start with SQLite and regain the SQL statement