Android数据存储SQLite数据库存储

来源：互联网发布：ye2系列电机绕组数据编辑：程序博客网时间：2024/05/22 12:59

Android数据存储SQLite数据库存储

SQLite是Android系统内置的数据库，是一种轻量级的关系型数据库，它运算速度快，占用资源少，非常适合在移动设备上使用。同时，它不仅支持标准的SQL语法，还遵循了数据库的ACID事务。

一、创建数据库

Android为了让我们方便地管理数据库，提供了一个SQLiteOpenHelper帮助类，借助于这个类可以非常简单地对数据库进行创建和升级。

1 . SQLiteOpenHelper类介绍

该类是一个抽象类，其中包含两个抽象方法，分别是onCreate()和onUpgrade()我们必须在自己的类中重写这两个方法，分别在两个方法中实现创建、升级数据库的逻辑。

SQLiteOPenHelper中还有两个重要的实例方法，getReadableDatabase()和getWritableDatabase()这两个方法都可以创建或打开一个现有的数据库（如果数据库存在则直接打开，否则创建一个新的数据库），并返回一个可对数据库进行读写操作的对象。不同的是，当数据库不可写入的时候（比如磁盘空间已满），getReadableDatabase()返回的对象将以只读方式打开数据库，而getWritableDatabase()方法将抛出异常。

SQLiteOpenHelper中有两个构造方法可供重写，一般使用参数少一点的那个构造方法。该构造方法接收四个参数，第一个参数是Context，第二个参数是数据库名，第三个参数允许我们在查询数据的时候返回一个自定义的Cursor，一般传入null，第四个参数表示当前数据库的版本号，可用于对数据库进行升级操作。构建出SQLiteOPenHelper的实例之后，再调用它的getReadableDatabase()或getWritableDatabase()方法就能够创建数据库了。此时，重写的onCreate()方法也会得到执行，所以通常会在这里处理一些创建表的逻辑。

2 . SQLite的数据类型

integer —————— 整型
real ————————浮点型
text ————————文本类型
blob ————————二进制类型

3 . 代码示例

1 . 首先创建一个Book表，建表语句如下：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> Book(    id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">integer</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> autoincrement,    author text,    price <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">real</span>,    pages <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">integer</span>,    name text)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

2 . 新建类继承自SQLiteOpenHelper，重写其中的构造方法和抽象方法。

<code class="hljs java has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">MyDatabaseHelper</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">extends</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">SQLiteOpenHelper</span>{</span>    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建Book表的语句</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String CREATE_BOOK = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"create table Book("</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"id integer primary key autoincrement, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author text, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price real, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages integer, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name text)"</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> Context mContext;    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//参数较少的构造方法</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-title" style="box-sizing: border-box;">MyDatabaseHelper</span>(Context context, String name, SQLiteDatabase.CursorFactory factory, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> version) {        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">super</span>(context, name, factory, version);        mContext = context;    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onCreate</span>(SQLiteDatabase sqLiteDatabase) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行建表语句</span>        sqLiteDatabase.execSQL(CREATE_BOOK);        Toast.makeText(mContext, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"数据表创建成功"</span>, Toast.LENGTH_SHORT).show();    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onUpgrade</span>(SQLiteDatabase sqLiteDatabase, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> i, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> i1) {    }}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li></ul>

3 . 在Activity中通过点击按钮触发创建数据库操作。

<code class="hljs java has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">SqliteActivity</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">extends</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">AppCompatActivity</span> {</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> MyDatabaseHelper myDatabaseHelper;    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">protected</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onCreate</span>(Bundle savedInstanceState) {        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">super</span>.onCreate(savedInstanceState);        setContentView(R.layout.activity_sqlite);        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建数据库为BookStore.db,不返回自定义的Cursor,数据库版本为1</span>        myDatabaseHelper = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> MyDatabaseHelper(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"BookStore.db"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>);        Button btnCreateDb = (Button) findViewById(R.id.create_db);        btnCreateDb.setOnClickListener(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> View.OnClickListener() {            <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onClick</span>(View view) {                <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//点击按钮创建数据库,同时执行onCreate()中的建表语句</span>                myDatabaseHelper.getWritableDatabase();            }        });    }}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li></ul>

先使用构造函数获得SQLiteOpenHelper实例，然后在点击按钮时触发getWritableDatabase()方法，执行了数据库创建，然后默认调用onCreate()方法在数据库BookStore.db里面创建数据表Book。

这个时候运行程序，数据库BookStore.db和数据表应Book应该就创建好了。接下来我们要查看创建好的数据库。

二、查看数据库

这里我们采用adb和sqlite3来查看创建的数据库和数据表。

adb是Android SDK中自带的一个调试工具，它可以通过命令行对连接在电脑上的真机或者模拟器进行调试。它存放在SDK的platform-tools目录下。你可以把该路径加到环境变量里面，以便在任意目录下可以通过命令行使用adb工具。

由于我使用的是Genymotion 模拟器，并且使用的是Genymotion默认的自带的adb工具，所以需要在Genymotion安装目录的tools目录下找到adb工具。

1 . 打开命令行，输入 adb shell打开adb交互命令行。

2 . 通过 cd 命令切换到指定目录， cd /data/data/packagename/databases/，此处的packagename是指你项目的包名，通过输入 ls 命令查看该目录下的文件。会发现有刚才创建的BookStore.db数据库文件。

3 . 通过sqlite3来查看数据库结构。继续输入 sqlite3 BookStore.db打开数据库。输入.table就可以看见刚刚创建的Book表，输入.schema就可以看见看见建表语句。

如下图:
这里写图片描述

说明：
1 . 在 databases目录下还会有一个BookStore.db-journal文件是为了让数据库能够支持事务而产生的临时文件。
2 . 输入.table 之后还有一张表 android_metadata是每个数据库都会自动生成的表。

三、升级数据库
如果我们已经创建好了数据表Book，现在想再创建另一张数据表，我们是不能简单在onCreate()方法里面再添加

<code class="hljs avrasm has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">sqLiteDatabase<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.execSQL</span>(CREATE_BOOK)</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

这句的，由于Book表已经存在，我们的onCreate()方法不会再执行，这样就不能再创建数据表了。

解决方法有两种:

卸载程序，此时数据库也会被删除，重新安装程序运行，就会执行onCreate()方法，就会创建两张数据表。
借助于onUpgrade()方法。要想让onUpgrade()方法执行，我们只需要在new SQLiteOpenHelper时将数据库的版本改为大于2，这样就会执行该方法，代码如下。

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">myDatabaseHelper = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> MyDatabaseHelper(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"BookStore.db"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

同时修改onUpgrade()中的代码：

<code class="hljs java has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">MyDatabaseHelper</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">extends</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">SQLiteOpenHelper</span>{</span>    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建Book1表的语句</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String CREATE_BOOK1 = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"create table Book1("</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"id integer primary key autoincrement, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author text, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price real, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages integer, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name text)"</span>;    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建Book2表的语句</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> String CREATE_BOOK2 = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"create table Book2("</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"id integer primary key autoincrement, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author text, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price real, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages integer, "</span>            + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name text)"</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> Context mContext;    <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//参数较少的构造方法</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-title" style="box-sizing: border-box;">MyDatabaseHelper</span>(Context context, String name, SQLiteDatabase.CursorFactory factory, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> version) {        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">super</span>(context, name, factory, version);        mContext = context;    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onCreate</span>(SQLiteDatabase sqLiteDatabase) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//执行建表语句</span>        sqLiteDatabase.execSQL(CREATE_BOOK1);        sqLiteDatabase.execSQL(CREATE_BOOK2);        Toast.makeText(mContext, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"数据表创建成功"</span>, Toast.LENGTH_SHORT).show();    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onUpgrade</span>(SQLiteDatabase sqLiteDatabase, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> i, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> i1) {        sqLiteDatabase.execSQL(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"drop table if exists Book1"</span>);        sqLiteDatabase.execSQL(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"drop table if exists Book2"</span>);        onCreate(sqLiteDatabase);    }}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li></ul>

先看要新建的表是否存在，如果存在先删除，然后在onUpgrade()方法中调用onCreate()方法创建数据表。

四、插入数据

上面我们在使用getReadableDatabase()创建和升级数据库，其实它还会返回一个SQLiteDatabase对象，借助于这个对象我们对数据库进行操作。

插入数据使用insert()方法，该方法接受三个参数，第一个参数是要插入数据的表名，第二个参数在未指定添加数据的情况下给某些可为空的列自动赋值NULL，一般直接传入null即可，第三个参数是ContentValues对象，它提供一系列的put()方法以键值对的方式给每个列名传入对应的数据。

<code class="hljs avrasm has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">//向数据库中添加数据private void addData() {   db = myDatabaseHelper<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.getWritableDatabase</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   ContentValues contentValues1 = new ContentValues()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"bingjian"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"冰鉴"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages"</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">453</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price"</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">16.0</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   //插入第一组数据   db<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.insert</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Book1"</span>, null, contentValues1)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.clear</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"bingjian2"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"冰鉴2"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages"</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">455</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   contentValues1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.put</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price"</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">12.0</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>   //插入第二组数据   db<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.insert</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Book1"</span>, null, contentValues1)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li></ul>

接下来按照上面的方式使用adb和sqlite3打开BooStore数据库，输入select * from Book1;就可以查看的刚才插入的数据。如下图:
这里写图片描述

五、更新数据

更新数据使用update()方法，该方法有四个参数，第一个参数是表名，第二个参数是ContentValues对象，在这里面组装要更新的数据，第三第四个参数用于约束更新某一行或某几行中的数据。不指定就是默认更新所有行。

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">updateData</span>() {    ContentValues contentValues = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ContentValues();    contentValues.put(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price"</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10.99</span>);    db.update(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Book1"</span>, contentValues, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name = ?"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> String[]{<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"bingjian"</span>});}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

上面的update()函数第三个参数对应的SQL语句的where部分，表示去更新所有name等于？的行，而？是一个占位符，可以通过第四个参数的字符串替换占位符。

六、删除数据

更新数据使用delete()方法，该方法有三个参数，第一个参数是表名，第二、第三个参数是用于约束删除某一行或某几行中的数据。不指定就是默认删除所有行。

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//删除数据库中的数据</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">deleteData</span>() {        db.delete(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Book1"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages > ?"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> String[]{<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"400"</span>});    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>

上面的代码会删除Book1表中的pages大于400的数据。也是使用了占位符来指定约束条件。

七、查询数据

对sqlite数据库的查询使用query()方法，哎方法有很多不同的重载方式，其中参数最少的一种也需要以下七个参数，下表给出了每个参数的含义。

query() 方法参数对应SQL部分描述tablefrom table_name指定查询的表名columnsselect column1, column2指定查询的列名selectionwhere column = value指定where的约束条件selectionsArgs-为where中的占位符提供具体的值groupBygroup by column指定需要group by 的列havinghaving column = value对group by 后的结果进一步约束orderByorder by column1, column2指定查询结果的排序方式

一般我们传入query的几个参数就可以获取到要查询的数据，该方法返回一个Cursor对象，查询到的所有数据都将从这个对象中取出。

<code class="hljs lasso has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//从数据库中查询数据</span><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">private</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> queryData() {    Cursor cursor <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> db<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>query(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Book1"</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">null</span>);    <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">""</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> (cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>moveToFirst()){        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">do</span>{            <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//遍历Cursor对象,取出数据(注意字段的类型)</span>            <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> name <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getString(cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getColumnIndex(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"name"</span>));            <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> author <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getString(cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getColumnIndex(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"author"</span>));            int pages <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getInt(cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getColumnIndex(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"pages"</span>));            double price <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getDouble(cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>getColumnIndex(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"price"</span>));            content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">+</span> name;            content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">+</span> author;            content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">+</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>valueOf(pages);            content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">=</span> content <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">+</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>valueOf(price);        }<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">while</span> (cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>moveToNext());    }    cursor<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>close();    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> content;}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li></ul>

上面通过循环遍历从cursor中取出了查询结果。我们可以给query指定更多参数来获取我们想要的数据。

七、使用SQL语操作数据库

Android中可以直接通过函数执行SQL语句来操作数据库。下面是是简单的增删改查的例子。

<code class="hljs javascript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//添加数据</span>db.execSQL(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"insert into Book1(name, author, pages, price) values(?, ?, ?, ?)"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[] {<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"mybook"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"my"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"123"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"15.5"</span>});<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//删除数据</span>db.execSQL(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"delete from Book1 where pages > ?"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[] {<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"120"</span>});<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//更新数据</span>db.execSQL(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"update Book1 set price = ? where name = ?"</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[]{});<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//查询数据</span>db.rawQuery(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"select * from Book1"</span>, <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">null</span>);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

以上就是sqlite的一些基础知识。

0 0