A preliminary understanding of SQLite database

Reprinted with: Http://www.jcodecraeer.com/a/anzhuokaifa/androidkaifa/2013/0714/1438.html from: The days of the online bubble.

As with other databases, the general operations of the SQLite database include creating a database, opening a database, creating a table, adding data to a table, deleting data from a table, modifying data in a table, shutting down a database, deleting a specified table, deleting a database, and querying a table for a single piece of data. Let's learn these basic operations separately.

1. Create and open a database
Creating and opening a database in Android can be done using the Openorcreatedatabase method, because it automatically detects the existence of the database, opens it if it exists, creates a database if it does not exist, and then returns a Sqlitedatabase object, otherwise throws an exception filenotfoundexception. Let's create a database named "Examples_06_05.db" and return a Sqlitedatabase object msqlitedatabase.

1	mSQLiteDatabase = this.openOrCreateDatabase("Example_06_05.db", MODE_PRIVATE, null);

3. Add a piece of data to the table
You can use the Insert method to add data, but the Insert method requires that the data be packaged into Contentvalues, Contentvalues is actually a map, the key value is the field name, and the value value is the field. With the Contentvalues put method, you can put the data into the contentvalues and then insert it into the table. The specific implementation is as follows:

12345678

ContentValues  cv  =  new ContentValues();cv.put(TABLE_NUM, 1);cv.put(TABLE_DATA, "测试数据");mSQLiteDatabase.insert(TABLE_NAME, null, cv);                                                                                                                                                                                                                                                                                            //这样同样可以使用execSQL方法来执行一条“插入”的SQL语句，代码如下：String  INSERT_DATA = "INSERT INTO table1 (_id, num, data) values (1, 1, ‘通过SQL语句插入‘)" ;mSQLiteDatabase.execSQL(INSERT_DATA);

4. Deleting data from a table
To delete the data you can use the Delete method, below we delete the field "_id" is equal to 1 of the data, the code is as follows:

1234	mSQLiteDatabase.delete("Examples_06_05.db", " where_id="+0, null);通过 execSQL方法执行SQL语句删除数据如下：String  DELETE_DATA = "DELETE FROM table1 WHERE _id=1";mSQLiteDatabase.execSQL(DELETE_DATA);

5. Modify the data in the table
If data is found to be incorrect after you add it, you need to modify the data, and you can use the Updata method to update a single piece of data. Let's change the "num" value to 0 data, the code is as follows:

1234	ContentValues cv = newContentValues();cv.put(TABLE_NUM, 3);cv.put(TABLE_DATA, "修改后的数据");mSQLiteDatabase.update("table1" cv, "num " + "="+ Integer.toString(0), null);

6. Close the database
Shutting down the database is important, and it's often easy to forget. The Close method is simple and uses the Sqlitedatabase Close method directly. The specific code is as follows:

1	mSQLiteDatabase.close();

7. Delete the specified table
Here we use the Execsql method to implement, the specific code is as follows:

1	mSQLiteDatabase.execSQL("DROP TABLE table1");

8. Deleting a database
To delete a database, use the DeleteDatabase method directly, the code is as follows:

1	this.deleteDatabase("Examples_06_05.db");

9. Querying a table for a piece of data
Querying data in Android is done through the cursor class, and when we use the Sqlitedatabase.query () method, we get a cursor object that points to every piece of data. It provides a number of methods for querying, as follows:

Method	Description
Move	Moves the cursor to the specified position at the current position, returns true successfully, and fails to return false
Movetoposition	Moves the cursor to the specified position, returns true successfully, fails to return false
MoveToNext	Move cursor forward one position, success returns TRUE, Failure returns false
Movetolast	Moves the cursor back one position, returns true successfully, and returns false for failure.
Movetofirst	Moves the cursor to the first row, returns true successfully, fails to return false
Isbeforefirst	Returns whether the cursor points before the first data
Isafterlast	Returns whether the cursor points to the last data
IsClosed	Returns whether the cursor is closed
IsFirst	Returns whether the cursor points to the first data

Islast	Returns whether the cursor points to the last data
IsNull	Returns whether the value at the specified position is null
GetCount	Returns the total number of data items
GetInt	Returns the index data specified in the current row

Here we are using the cursor to query the database data, the specific code is as follows:

Reprinted with: Http://www.jcodecraeer.com/a/anzhuokaifa/androidkaifa/2013/0714/1438.html from: The days of the online bubble.

123456789

Cursor cur = mSQLiteDatabase.rawQuery("SELECT * FROM table", null);if( cur != null ){   if( cur.moveToFirst() ){       do{                  int numColumn = cur.getColumnIndex("num");                  int num            = cur.getInt(numColumn);            }while( cur.moveToNext());      } }

A preliminary understanding of SQLite database