SQLite总结

阳光穿透心脏的1/2处 2022-04-16 05:24 300阅读 0赞

**分享一下我老师大神的人工智能教程！零基础，通俗易懂！[http://blog.csdn.net/jiangjunshow][http_blog.csdn.net_jiangjunshow]**

**也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！**

SQLite Database

SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. 其实只需要记住SQLite不需要数据库服务器，不需要额外配置，其数据库本身就是一个文件的一个小型数据库。 通常情况下在android中创建数据库，系统会在"DATA/data/APP\_NAME/databases".目录为我们创建数据库文件。

SQLite BasicDataTypes  
SQLite支持的数据类型有很多，但他们总共可以分为以下几类:

1.  desc <table> //查看表结构  
    
2.  select \* from <table> //查询所有更  
    
3.  select , from table ;//查看指定列  
    
4.  select distinct , from table ;//非重复查询  
    
5.  insert into users(\_id,username,password) select \* from users;//复制  
    
6.  select username from users where username like 'S%' ;//非重名字首字母为大写S的用户  
    
7.  select username from users where username like '\_\_S%' ;//非重名字第三个字母为大写S的用户  
    
8.  select \* from users where \_id in(001,220,230);  
    
9.  select \* from user order by \_id;//以id的顺序排列  
    
10. select \* from user order by \_id desc;//以id反的顺序排

复制代码

常用Select语句

复制代码

图形环境

很少有人直接在程序当中直接编写SQL语句，麻烦且容易出错。所以最好应该先在一个图形化的环境把SQL语句写好，测试好再加入到代码当中。 而且，SQLite数据库本身就是一个独立的文件，很容易从模拟器上抓取出来。可以使用图形化界面进行分析，和修改 etc.  
SQLite expert 是个简单小巧的SQLite数据库管理软件,有免费版和，收费版两种，一般来讲免费版足以应付我们需求。  
[sqliteexpert官方网址][sqliteexpert]

软件界面截图

![2011072510154966.png][]

使用SQLite expert 创建一张table，并让它为我们生成所需的sql语句

下面的例子，我们创建一张表用来存储用户的用户名和密码;数据库名"users",表名"user\_accounts",有三个列：row\_id INTEGER自增类型的主键,usernameTEXT类型 用来保存用户名，passwordTEXT类型，用来保存用户密码;

创建表格  
为了创建一张table 首先要有一个数据库文件，点击"File"->""New Database然后选择文件的路径，点击OK便创建了一个数据库文件 创建完数据库之后创建表格，右击数据库文件名选择"New Table"如下图所示：

![2011072510173817.png][]

这时我们便进入了"设计状态"，填写表名(user\_accounts),点击"Add",在Name中填写"row\_id",Type选择Integer,如下图所示:

![2011072510181261.png][]

因为row\_id这个列比较特殊是我们的索引的主键，所以还要点击"Index"，之后会看到下图:

![2011072510184530.png][]

左边的Available Fields 中有我们刚刚创建的row\_id,点击"Add"把row\_id加入右边的Index Fields当中。这时上面的Primary和AutoIncrement会变成可先状态 ，勾选Primary和AutoIncrement点击OK；

![2011072510195726.png][]

再回到Field当中创建其它两列，分别为username类型为TEXT,password类型也为TEXT，但不需要创建Index; 完成之后点击Apply,这样我们便使用SQLite expert创建一张表格，点击DDL，我们可以看到SQLite expert已经为我们生成好了生成这张表格所需的SQL语句：

![2011072510204056.png][]

在android中需要代码生成表格时，只要把这段代码复制过去，就可以了。

增加用户  
点击SQL,执行以下SQL语句，为表格增加一个用户：

1.  INSERT INTO user\_accounts(row\_id,username,password) VALUES(001,'John','abcdef');

复制代码

![2011072510215316.png][]

点击Data会发现数据库里边增加了一个用户名为John的用户。为了练习，我们不妨再增加两个用户，David和Sarah

1.  INSERT INTO user\_accounts(row\_id,username,password) VALUES(002,'David','123456');  
    
2.    
    
3.  INSERT INTO user\_accounts(row\_id,username,password) VALUES(003,'Sarah','00000000');

复制代码

删除用户  
执行下面的语句删除用户David:

1.  DELETE FROM user\_accounts WHERE username = 'David';

复制代码

修改密码  
执行以下语句修改Sarah的密码：

1.  update user\_accounts SET password='666666' WHERE username = 'Sarah';

复制代码

查看所用户信息可以使用如下语句查看表内所有用户的信息：

1.  SELECT \* FROMM user\_accounts;

复制代码

一般来讲select \* 的语句只在测试的时候时候，在正式代码中不推荐使用。

查看指定列的内容执行以下语句查看所有用户的用户名和密码：

1.  SELECT username,password FROM user\_accounts;

复制代码

这时个就发现row\_id列没有显示出来。

查询特定条件的信息  
SQL可以通过给定查询条件进行精确查找，比如我们只需要John的密码。就可以使用这样的语句

1.  SELECT password FROM user\_accounts　WHERE　username = 'John';

复制代码

以下流程图，来源于SQLite官方文档

Create Table Statement

![2011072510251139.png][]

column-def

![2011072510252943.png][]

type-name

![2011072510254545.png][]

column-constraint

![2011072510261474.png][]

table-constraint

![2011072510263282.png][]

foriegn-key-clause

![2011072510265392.png][]

Insert Statement

![2011072510270987.png][]

Delete Statement

![2011072510272823.png][]

qualified-table-name

![2011072510275873.png][]

Update Statement

![2011072510281753.png][]

qualified-table-name

![2011072510284297.png][]

Select Statement

![2011072510290859.png][]

select-core

![2011072510292785.png][]

result-column

![2011072510294772.png][]

join-source

![2011072510300479.png][]

single-source

![2011072510302683.png][]

join-op

![2011072510304523.png][]

join-constraint

![2011072510310365.png][]

ordering-term

![2011072510312289.png][]

compound-operator

![2011072510314080.png][]

Dealing with large amount of data分页当数据库数据量很大时，分页显示是个很简单且符合人们习惯的一种处理方法。  
获取数据行总数:  
最简单的方法是：SELECT所有的行，再调用curosr.getCount() ;取得行数，但这样效率会很低。 SQLite为我们提供了一个函数很容易查出总共有多少行。有个名为cet6\_table的表格，我们想知道总共有多少行的word;可以使用如下 语句来完成:

1.  SELECT count(word) AS number FROM cet6\_table;

复制代码

count()函数为我们返回一个Int整形，代表有多少行数据。返回的列的名字叫count(word)，为了方便阅读和处理用as number给这个列取个 别名number;

1.  SELECT \[word\],\[explanation\] FROM cet6\_table ORDER BY word LIMIT 100 OFFSET 200"

复制代码

上语句，返回自第200行的最多100条数据。分页时我们只要修改offset 后面的数据即可取得对应页面的数据。  
详情见示例程序！

多线程自带数据库  
有些程序比如，字典，输入法等程序会在apk中带有数据库。思路很简单，把预先准备好的数据库放入asset或者raw目录当中 使用时复制到对应的手机目录上；再打开。以下代码会把raw目录中的名为cet6的数据库文件写入sd卡根目录并打开。 对于一些常用或者比较重要的数据建议放在程序的私有目录当中。

1.  public void init(Context context) \{  
    
2.  try \{  
    
3.  String databasePath = Environment.getExternalStorageDirectory().getAbsolutePath();  
    
4.  String databaseFilename = databasePath + "/" + DATABASE\_FILENAME;  
    
5.  File dir = new File(databasePath);  
    
6.  if (!dir.exists())\{  
    
7.  dir.mkdir();  
    
8.  \}  
    
9.  if (!(new File(databaseFilename)).exists()) \{  
    
10. InputStream is = context.getResources().openRawResource(  
    
11. R.raw.cet6);  
    
12. FileOutputStream fos = new FileOutputStream(databaseFilename);  
    
13. byte\[\] buffer = new byte\[1024\];  
    
14. int count = 0;  
    
15. while ((count = is.read(buffer)) > 0) \{  
    
16. fos.write(buffer, 0, count);  
    
17. \}  
    
18. fos.flush();  
    
19. fos.close();  
    
20. is.close();  
    
21. \}  
    
22. mCet6Db = SQLiteDatabase.openOrCreateDatabase(databaseFilename,  
    
23. null);  
    
24. \} catch (Exception e) \{  
    
25. e.printStackTrace();  
    
26. \}  
    
27. \}

复制代码

单词提示，IME使用AutoCompleteText + SQLite很容易实现类似于baidu ，Google等搜索时出现的提示文字。 输入法也是同样的原理，以拼音或五笔等作为索引，非常容易实现。可以加一个列标明一个字被选择的次数， 在select的时候可以按照这个列来排序，这样可以保存用户的习惯；作出来的输入法将更人性化。因为输入法的查询量比较大 可以考虑把数据库文件放入内存当中，SQLite本身是支持内存数据库的。但android中封装上没有提供这一功能，而且SQLiteDataBase类 很多核心功能都是通过JNI实现的，所以要把数据库搬入内存还需编写c/c++的代码。也可以考虑使用Lucene的全文搜索功能来实现。Date  
SQLite有很多函数支持日期的方便处理。

1.  %d day of month: 00  
    
2.  %f fractional seconds: SS.SSS  
    
3.  %H hour: 00-24  
    
4.  %j day of year: 001-366  
    
5.  %J Julian day number  
    
6.  %m month: 01-12  
    
7.  %M minute: 00-59  
    
8.  %s seconds since 1970-01-01  
    
9.  %S seconds: 00-59  
    
10. %w day of week 0-6 with Sunday==0  
    
11. %W week of year: 00-53  
    
12. %Y year: 0000-9999  
    
13. %% %

复制代码

SQLite支持以下格式的日期:

1.  YYYY-MM-DD  
    
2.  YYYY-MM-DD HH:MM  
    
3.  YYYY-MM-DD HH:MM:SS  
    
4.  YYYY-MM-DD HH:MM:SS.SSS  
    
5.  YYYY-MM-DDTHH:MM //T 就是字母T，用于分开日期和时间  
    
6.  YYYY-MM-DDTHH:MM:SS  
    
7.  YYYY-MM-DDTHH:MM:SS.SSS  
    
8.  HH:MM  
    
9.  HH:MM:SS  
    
10. HH:MM:SS.SSS  
    
11. now  
    
12. DDDDDDDDDD //is the Julian day number expressed as a floating point value.

复制代码

strftime('%Y-%m-%d', column\_name)方法可以很方便的对日期进行格式化，而不用Java对String进行再处理。  
ContentValues 不支持直接把datetime('now')之类函数作为值插入数据库。 比如想把当前系统时间插件数据库使用ContentValues：

1.  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
    
2.  Date date = new Date();  
    
3.  ContentValues initialValues = new ContentValues();  
    
4.  initialValues.put("date\_created", dateFormat.format(date));  
    
5.  long rowId = mDb.insert(DATABASE\_TABLE, null, initialValues);

复制代码

或者如附带程序记事本程序一样使用exeSQL(sql);

SqliteOpenHelper

![2011072510353396.png][]

上流程图来自O'REILLY 视频公开课"Developing Android Applications with Java"中的视频截图  
SqliteOpenHelper是android框架为我们提供的一个数据库助手类。对于数据库的操作一般都是从这个类继承。它主要负责数据库的创建，打开，更新 等一些操作。

Singleton DAO  
很多有j2ee背景的开发者都喜欢建立一个单例模式的数据库操作类，专门处理数据库之间的交流。也有很多人把这个习惯带到的Android的开发当中。 而且我们也经常需要在几个Activity，Service同时访问一个数据库文件，所以经常能看到类似于这样的代码：

1.  public class DbAccess \{  
    
2.    
    
3.  class DbHelper extends SQLiteOpenHelper \{  
    
4.  //此处代码被省略  
    
5.  \}  
    
6.  private static DB\_NAME = "users";  
    
7.  private mDbHelper = null;  
    
8.  private static mDbAccess;//to make sure there is only one instance exist;  
    
9.    
    
10. private DbAccess(Context context)\{  
    
11. //private constructer;  
    
12. if(mDbHelper == null)\{  
    
13. mDbHelper = new DbHelper(context,DB\_NAME);  
    
14. \}  
    
15. \}  
    
16.   
    
17. public DbAccess getInstance(Context context)\{  
    
18. if(mDbAccess == null)\{  
    
19. mDbAccess = new DbAccess(context);  
    
20. \}  
    
21. return mDbAccess;  
    
22. \}  
    
23.   
    
24. //部分代码被省略  
    
25. //insert  
    
26. //delete  
    
27. //update  
    
28. //query  
    
29. \}  
    
30.   
    
31. public class ActivityA extends Activity\{  
    
32. @Override  
    
33. protected void onCreate(Bundle savedInstanceState) \{  
    
34. super.onCreate(savedInstanceState);  
    
35. dao = DbAccess.getInstance(this);  
    
36. 部分代码被省略  
    
37. \}  
    
38. 部分代码被省略  
    
39. \}

复制代码

程序的流程应该是这样的：

![2011072510371574.png][]

假设ActivityA创建了Dao的对象，又开启了ServiceA，A使用了一段时间再跳转到了ActivityB与B再接着使用同一个Dao对象。ActivityA创建Dao对象的时候 把自己的引用向上转型成一个Context对象，Dao又用这个context创建了SQLiteOpenHelper，所以SQLiteOpenHelper就持有了ActivityA的引用。当ActivityA被销毁 之后因为单例的Dao保留有ActivityA的引用，所以ActivityA不能被GC所回收，造成整个Acitivity内存的泄漏。

解决方案  
1，最简单的，不使用单例模式，因为SQLiteOpenHelper保证了数据库的单例，所以实际上我们只是创建了多个SQLiteOpenHelper的对象，数据库 对象本身还是单例的。缺点：会有一些重复的代码，并生成多个SQLiteOpenHelper的子类对象。  
2，把Dao对象放入Application当中，在Application的onCreate()中使用Application的Context进行初始化。优点，Application对象的创建在Activity和Service之前 所以保证了，Activity和Service等等使用时数据库已经准备好了。缺点：Application的生命周期是最长的，并且Android会尽量不让程序关闭，也就是尽量不 去销毁Application对象，所以数据库一直保持打开状态，依靠Application的onDestroy()关闭数据库，不确定的因素比较大，并且因为打开数据库的时候直接 影响程序启动时间。  
3,先看一个问题，为什么SQLiteOpenHelper需要一个Context对象？查看SQLiteOpenHelper的源码发现如下代码：

1.  SQLiteDatabase db = null;  
    
2.  try \{  
    
3.  mIsInitializing = true;  
    
4.  String path = mContext.getDatabasePath(mName).getPath();  
    
5.  db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN\_READONLY);  
    
6.  if (db.getVersion() != mNewVersion) \{  
    
7.  throw new SQLiteException("Can't upgrade read-only database from version " +  
    
8.  db.getVersion() + " to " + mNewVersion + ": " + path);  
    
9.  \}  
    
10. onOpen(db);  
    
11. Log.w(TAG, "Opened " + mName + " in read-only mode");  
    
12. mDatabase = db;

复制代码

所以我们可以自己仿照SQLiteOpenHelper写一个不需要Context的助手类，大constructer当中把路径传进行，而不是一个context对象。缺点：需要考虑多线程同步等 很多因素，有一定的复杂度。  
4,在创建Dao对象时，不使用Activity的引用作为Context,而是把Application作为Context传入Dao的构造函数中进行创建。优点，不会影响程序的启动时间。 缺点：对于数据库的关闭还是不确定。  
5,在第四种方案的基础上加入计数器。第调用getInstance计数器自增，再加入一个release()方法，计数器自减，当计数器为0时，关闭数据库(或者当计数器为零且 过一段时间没有连接时关闭)。

#### 给我老师的人工智能教程打call！[http://blog.csdn.net/jiangjunshow][http_blog.csdn.net_jiangjunshow] ####

![这里写图片描述][SouthEast]

[http_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485
[sqliteexpert]: http://www.sqliteexpert.com/
[2011072510154966.png]: /images/20220416/b7a920b867d844f6ab2ab6ff4ce1d604.png
[2011072510173817.png]: /images/20220416/8dee8bf358224135b1b472b601bb02db.png
[2011072510181261.png]: /images/20220416/bd034183fcda4d8ab64f1bdc342b0b78.png
[2011072510184530.png]: /images/20220416/248d9c6811e9485186ce29c73c68c209.png
[2011072510195726.png]: /images/20220416/5c33735065f741fca61e063516a373d9.png
[2011072510204056.png]: /images/20220416/ea15aa5b867d4b4285eab0310eba57db.png
[2011072510215316.png]: /images/20220416/7b84701ac0b740189ee6990072867ea1.png
[2011072510251139.png]: /images/20220416/d3effca09d43472daaf11f33e9796d96.png
[2011072510252943.png]: /images/20220416/5486db209b694770bbdf0eccad154273.png
[2011072510254545.png]: /images/20220416/e040cd30746a47fb881e9ddbdd52773b.png
[2011072510261474.png]: /images/20220416/77af445003c44283a9c0bc47991f1613.png
[2011072510263282.png]: /images/20220416/8cd7f9c796d2456c8a47556e257cd756.png
[2011072510265392.png]: /images/20220416/23041751a7814e65a50b3a3fb23c9387.png
[2011072510270987.png]: /images/20220416/5a447ee6a73e43c8937d98a4612df7c8.png
[2011072510272823.png]: /images/20220416/81794e9368144fbe91cb03b680f40910.png
[2011072510275873.png]: /images/20220416/8dde8189b439478fbd92d60271ded59b.png
[2011072510281753.png]: /images/20220416/22330369a32d4f0e92f7746b406dd9e1.png
[2011072510284297.png]: /images/20220416/e5d0779c21b143fd824ffd8db1feb364.png
[2011072510290859.png]: /images/20220416/38e67896ebd5487082e0bb79f2b09846.png
[2011072510292785.png]: /images/20220416/121dd8c4073d4b6b86cb0e7c81911f92.png
[2011072510294772.png]: /images/20220416/08ab8e5bfdb74861a922382abd8b901f.png
[2011072510300479.png]: /images/20220416/e290fbf3d187404c9529906e4ba0011d.png
[2011072510302683.png]: /images/20220416/742cb8cd1c5943e7bbc912fe292525cf.png
[2011072510304523.png]: /images/20220416/17898f43419b463495af3e112b524506.png
[2011072510310365.png]: /images/20220416/653bc8df4d4a4cf2ab63848907e72a35.png
[2011072510312289.png]: /images/20220416/d3f3d4ed98ab4c4eaed63dbde31387ab.png
[2011072510314080.png]: /images/20220416/05e4b533f5134cee913ee0ea38c59f68.png
[2011072510353396.png]: /images/20220416/d1997a0157ca4109baf9fe9235aff7ec.png
[2011072510371574.png]: /images/20220416/3860254b7a0e4b7588c451a2520a7405.png
[SouthEast]: /images/20220416/7b5adc5a74e740a3943d480d17801d7a.png