Basic learning of SQLite

SQLite is a lightweight database, integrated in Android, the following from the sharing of their own learning.

There are some good instructions to use when accessing the information:
The main curd statements

The following SQL statement gets 5 records, skipping the previous 3 recordsSelect* fromAccount limit5Offset3OrSelect* fromAccount limit3,5Insert statement:Insert  intoTable name (field list)Values(Value list). Such as:Insert  intoPerson (name, age)Values(' ATM ',3) UPDATE statement:UpdateTable nameSetField name = valuewhereThe conditional clause. Such as:UpdatePersonSetName= ' ATM 'whereId=1Delete statement:Delete  fromTable namewhereThe conditional clause.
如：delete from person where id=1

Differences between Getwritabledatabase () and Getreadabledatabase ()

getWritableDatab ASE   and getreadabledatabase   ()  method can obtain a sqlitedatabase  instance that is used to manipulate the database. 
 
 
 But getwritabledatabase   ()  method opens the database in read-write mode, Once the database disk space is full, the database can only read and not write, if using getwritabledatabase   ()  opening the database will cause an error. getreadabledatabase   The  method first opens the database in read-write mode, assuming that the database is full of disk space, it will fail to open, and will continue to attempt to open the database in a read-only manner when the open fails. 
   
  note: getwritabledatabase   () , getreadabledatabase  the difference is that when the database is full, call the former will be an error, call the latter will not. Therefore, it is better to call the latter to obtain a database connection if the database is not updated. 

A simple description of the cursor

CursorgetCount()moveToFirst()moveToNext()isAfterLast()getColumnNames()getColumnIndex()getString()，getInt()requery()close() 方法释放游标资源；

提示：数据库中读取到的数据在cursor中。在写入到对象中时。要先推断cursor中是否有数据，否则当查询结果为空集时会报错。推断cursor为空时。不能够用cursor==null，及时cursor中没有数据，cursor也不为空。
须要用cursor.getCount()函数来推断是否为空，结果为0时为空。

The specific code:

First: How to create a database
Create databases and tables.
The default save path for the database is: "data/data/com.example.sqlitetest/databases/"
Com.example.SqliteTest is your own project.

 Public  class dbopenhelp extends sqliteopenhelper {     Public Dbopenhelp(Context context) {Super(Context,"User_atm.db",NULL,1);//TODO auto-generated constructor stub}@Override     Public void onCreate(Sqlitedatabase db) {//TODO auto-generated method stubStringBuilder strSQL =NewStringBuilder (); Strsql.append ("CREATE TABLE IF not EXISTS user_info ("); Strsql.append ("user_id INTEGER PRIMARY KEY autoincrement not NULL,"); Strsql.append ("User_age INT not NULL,"); Strsql.append ("user_name VARCHAR () not NULL)");    Db.execsql (Strsql.tostring ()); }@Override     Public void Onupgrade(Sqlitedatabase DB,intOldversion,intNewVersion) {//Database churn is done here. Add a table field , for example}

The following is the code for adding and deleting changes and transactions:

 Public classservicetest {///delete and change    //Get the databaseDbopenhelp Dbopenhelp; Public servicetest(Context context) {super (); This. Dbopenhelp =NewDbopenhelp (context); }//Start transaction     Public void transactiontest() {Sqlitedatabase db = Dbopenhelp.getwritabledatabase (); Db.begintransaction ();Try{Db.execsql ("Update user_info set user_age=user_age+1 where user_id=10"); Db.execsql ("Update user_info set user_age=user_age-1 where user_id=11");        Db.settransactionsuccessful (); }finally{db.endtransaction ();} }//Increase     Public void Save(UserInfo UserInfo)        {Sqlitedatabase db = Dbopenhelp.getwritabledatabase (); Db.execsql (Insert into User_info (user_id, user_age, user_name) VALUES (?,?,?
) ", new  object[]{userinfo.getuser_id (), Userinfo.getuser_age (), Userinf O.getuser_name ()}); } //delete  public  void< /span> delete  (Integer user_id) {sqlitedatabase db = Dbopenhelp.getwritabledatabase ( ); Db.execsql ( "delete from User_info where user_id=?  
 ", new  object[]{user_id}); } //changes  public  void  update  (UserInfo UserInfo) {Sqlitedatabase db = Dbopenhelp.getwritabledataba SE (); Db.execsql ( "update user_info set user_age=?,user_name=?" where user_id=? ", new  object[]{userinfo.getuser_age (), userinfo.ge Tuser_name (), userinfo.getuser_id ()}); } //query  public  UserInfo find  (Integer ID) {Sqlitedatabase db = Dbopenhelp.getreadabledatabase (); cursor cursor = db.rawquery ( "select * from User_info where user_id=?  
", new String[]{id.toString()}); if(cursor.moveToFirst()){ int user_id = cursor.getInt(cursor.getColumnIndex("user_id")); int user_age = cursor.getInt(cursor.getColumnIndex("user_age")); String user_name = cursor.getString(cursor.getColumnIndex("user_name")); return new UserInfo(user_id, user_name, user_age); } cursor.close(); return null; }}

Visual Database management tools –sqlite Expert Professional
I can download it on the Internet.
After downloading, open the software and enter the main interface:

The middle Red box section is for importing and deleting databases.

In the new table.

There are also very versatile features that can be re-imported into the device (the path that starts everywhere) after the changes are made.

view the database with cmd
Open the database under the development path

Simple database operation. Query a table.

SQLite Trigger: android:sqlite– Trigger Specific explanation

Basic learning of SQLite