1.SQLite概述

SQLite是一款轻量级的关系型数据库，由于它占用的资源非常少，所以在很多嵌入式设备都是用SQLite来存储数据。Android作为目前主流的移动操作系统，完全符合SQLite占用资源少的优势，故在Android平台上，集成了一个嵌入式关系型数据库SQLite。

继承SQLiteOpenHelper类，同时覆盖onCreate(SQLiteDatabase)，onUpgrade(SQLiteDatabase,int,int)和定义构造函数，使用该类实例对象的getReadableDatabase()或者getWritableDatabase()得到当前数据库的实例化对象，可直接对其进行操作

2.SQLite实现增删改查

[java]view plaincopy
  1. //数据库的基本操作，增删改查
  2. @Override
  3. protectedvoidonCreate(BundlesavedInstanceState){
  4. super.onCreate(savedInstanceState);
  5. setContentView(R.layout.activity_main);
  7. DbOpenHelperdbOpenHelper=newDbOpenHelper(this,"mytest.db",1);
  8. SQLiteDatabasedb=dbOpenHelper.getWritableDatabase();
  10. //////////////////////////////////////////////////////////
  11. db.execSQL("insertintouser(name,age)values(?,?)",newObject[]{"zhang",10});//1.1增
  13. db.execSQL("deletefromuserwhere_id=?",newString[]{"1"});//1.2删
  15. db.execSQL("updateusersetname=?where_id=?",newString[]{"haha","2"});//1.3改
  17. Cursorcursor=db.rawQuery("select*fromuserwhereage>=?",newString[]{"1"});//1.4查
  18. while(cursor.moveToNext()){
  19. Toast.makeText(this,cursor.getString(1),Toast.LENGTH_SHORT).show();
  20. }
  22. //////////////////////////////////////////////////////////
  23. ContentValuesvalues=newContentValues();//2.1增
  24. values.put("name","John");
  25. values.put("age",20);
  26. db.insert("user",//表格名
  27. "name",//StringnullColumnHack
  28. //SQl不允许插入一个空行，当values的值为空时，将相当于执行于
  29. //insertintoage(name)values("null");
  30. values);//插入值，相当于一个键值对的集合
  32. db.delete("user","age>=?andage<=?",newString[]{"12","18"});//2.2删
  34. ContentValuesvalues1=newContentValues();//2.3改
  35. values.put("name","liu");
  36. db.update("user",values1,"_id=?",newString[]{"8"});
  38. Cursorcursor1=db.query("user",//表格名//2.4查
  39. null,//String[]columns如果为空，则返回所有列
  40. "age>=?",//Stringselection查询条件
  41. newString[]{"15"},//String[]selectionArgs查询参数
  42. null,//StringgroupBy
  43. null,//Stringhaving
  44. null);//StringorderBy
  45. while(cursor.moveToNext()){
  46. intid=cursor1.getInt(cursor1.getColumnIndex("_id"));
  47. Toast.makeText(this,cursor1.getString(cursor1.getColumnIndex("name")),Toast.LENGTH_SHORT).show();
  48. }
  49. }
[java]view plaincopy
  1. /**
  2. *DbOpenHelper.java
  3. */
  4. importandroid.content.Context;
  5. importandroid.database.sqlite.SQLiteDatabase;
  6. importandroid.database.sqlite.SQLiteOpenHelper;
  7. publicclassDbOpenHelperextendsSQLiteOpenHelper{
  8. publicDbOpenHelper(Contextcontext,Stringname,intversion){
  9. super(context,name,null,version);
  10. }
  12. /**
  13. *数据库只有在第一次创建时才调用该方法,对于一个已经存在的数据库，该方法不执行
  14. *SQLiteDatabase.openOrCreateDatabase()
  15. */
  16. @Override
  17. publicvoidonCreate(SQLiteDatabasedb){
  18. Stringsql="createtableuser(_idintegerprimarykeyautoincrement,namevarchar(10),ageinteger)";
  19. db.execSQL(sql);
  20. }
  22. /**
  23. *只有当数据库的版本号增加时，才会执行到该函数
  24. *初始默认数据库版本为0，当构造函数传递newVersion时，这时会有db.getVersion()与newVersion比较觉得该函数是否执行
  25. */
  26. @Override
  27. publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
  28. }
  29. }

3.getReadableDatabase()和getWritableDatabase()区别

(1).getReadableDatabase()

使用SQLiteDatabase实例对象的getReadableDatabase()方法，首先会检查数据库是否已经实例化并处于打开状态，如果满足要求直接返回数据库对象，否则试图返回一个可读写模式的数据库，但当磁盘空间已满，只能得到只读模式数据库对象

(2).getWritableDatabase()

使用SQLiteDatabase实例对象的getWritableDatabase()方法，目的是得到一个具有可读写的数据库实例对象，首先判断mDatabase是否不为空且已打开且不是只读模式，则直接返回该实例对象，否则如果mDatabase不为空则加锁，然后开始打开或创建数据库，进行版本相关操作并解锁，最后返回数据库实例对象！

总之，一般情况下，通过这两种方法获得的数据库实例对象时一样的，只有在数据库空间已满或其它情况下，通过getReadableDatabase得到的才是只读的实例对象

[java]view plaincopy
  1. //得到一个具有可读写的数据库实例对象
  2. publicsynchronizedSQLiteDatabasegetWritableDatabase(){
  3. if(mDatabase!=null&&mDatabase.isOpen()&&!mDatabase.isReadOnly()){
  4. returnmDatabase;//如mDatabase不为空已打开并且不是只读模式则返回该对象
  5. }
  7. if(mIsInitializing){//判断是否正在初始化
  8. thrownewIllegalStateException("getWritableDatabasecalledrecursively");
  9. }
  11. booleansuccess=false;//默认为false
  12. SQLiteDatabasedb=null;
  13. if(mDatabase!=null)
  14. mDatabase.lock();//如果mDatabase不为空则加锁阻止其他的操作
  15. try{
  16. mIsInitializing=true;//初始化完成
  17. if(mName==null){
  18. db=SQLiteDatabase.create(null);
  19. }else{
  20. db=mContext.openOrCreateDatabase(mName,0,mFactory);//打开或创建数据库
  21. }
  22. intversion=db.getVersion();//获取数据库版本(如果新创建的数据库版本为0)
  23. if(version!=mNewVersion){//获得的数据库版本号与指定的数据库版本号进行比较
  24. db.beginTransaction();//开始事务
  25. try{
  26. if(version==0){//新建的数据库版本号为0，则直接执行onCreate方法
  27. onCreate(db);
  28. }else{
  29. onUpgrade(db,version,mNewVersion);//如果版本号发生变化，且不是新建的数据库，则执行onUpgrade方法
  30. }
  31. db.setVersion(mNewVersion);//为当前的数据库设置新版本号
  32. db.setTransactionSuccessful();//设置事务成功提交
  33. }finally{
  34. db.endTransaction();//结束事务
  35. }
  36. }
  38. onOpen(db);
  39. success=true;
  40. returndb;//返回当前的可读写模式数据库实例对象
  41. }finally{
  42. mIsInitializing=false;
  43. if(success){
  44. if(mDatabase!=null){
  45. try{
  46. mDatabase.close();//如果mDatabase存在则先关闭
  47. }catch(Exceptione){
  48. }
  49. mDatabase.unlock();//为当前的数据库实例对象进行解锁操作
  50. }
  51. mDatabase=db;
  52. }else{
  53. if(mDatabase!=null)
  54. mDatabase.unlock();
  55. if(db!=null)
  56. db.close();
  57. }
  58. }
  59. }
[java]view plaincopy
  1. /*
  2. *在getReadableDatabase()方法中，首先检查数据库是否已经实例化并处于打开状态
  3. *如果满足要求直接返回数据库对象，否则试图获取一个可读写模式的数据库实例；
  4. *当磁盘空间已满，再以只读模式打开数据库
  5. */
  6. publicsynchronizedSQLiteDatabasegetReadableDatabase(){
  7. if(mDatabase!=null&&mDatabase.isOpen()){
  8. returnmDatabase;//如果发现mDatabase存在并已打开，则直接返回该数据库对象
  9. }
  11. if(mIsInitializing){//检查数据库是否正在进行初始化操作
  12. thrownewIllegalStateException("初始化");
  13. }
  15. try{
  16. /******注意！！！调用了getWritableDatabase()方法*****/
  17. returngetWritableDatabase();//一般情况下使用getReadableDatabase得到的是getWritableDatabase的数据库对象
  18. }catch(SQLiteExceptione){
  19. if(mName==null)
  20. throwe;
  21. }
  23. /*
  24. *使用可读写不能得到数据库对象，下面的操作只能得到对对象
  25. *常见数据库已满，不能再添加数据，此时只能写，不能读
  26. */
  27. SQLiteDatabasedb=null;
  28. try{
  29. mIsInitializing=true;
  30. Stringpath=mContext.getDatabasePath(mName).getPath();
  31. db=SQLiteDatabase.openDatabase(path,mFactory,SQLiteDatabase.OPEN_READONLY);//以只读方式打开数据库
  32. if(db.getVersion()!=mNewVersion){//只读数据库，不接收新版本数据库
  33. thrownewSQLiteException("Can'tupgraderead-onlydatabasefromversion"+db.getVersion()+"to"
  34. +mNewVersion+":"+path);
  35. }
  36. onOpen(db);
  37. Log.w(TAG,"Opened"+mName+"inread-onlymode");
  38. mDatabase=db;//mDatabase为打开的数据库对象
  39. returnmDatabase;
  40. }finally{
  41. mIsInitializing=false;
  42. if(db!=null&&db!=mDatabase)
  43. db.close();
  44. }
  45. }

4.Cursor对象

(1).主要方法

[java]view plaincopy
  1. c.move(intoffset);//以当前位置为参考,移动到指定行
  2. c.moveToFirst();//移动到第一行
  3. c.moveToLast();//移动到最后一行
  4. c.moveToPosition(intposition);//移动到指定行
  5. c.moveToPrevious();//移动到前一行
  6. c.moveToNext();//移动到下一行
  7. c.isFirst();//是否指向第一条
  8. c.isLast();//是否指向最后一条
  9. c.isBeforeFirst();//是否指向第一条之前
  10. c.isAfterLast();//是否指向最后一条之后
  11. c.isNull(intcolumnIndex);//指定列是否为空(列基数为0)
  12. c.isClosed();//游标是否已关闭
  13. c.getCount();//总数据项数
  14. c.getPosition();//返回当前游标所指向的行数
  15. c.getColumnIndex(StringcolumnName);//返回某列名对应的列索引值
  16. c.getString(intcolumnIndex);//返回当前行指定列的值

(2).Cursor对象的管理

Activity提供了LoaderManager去管理cursor的生命周期，可参考cursor管理>>

(3).Cursor注意情况

1).要求

需要注意的是，在cursor的结果集中必须要包含一个“_id”的列，否则SimpleCursorAdapter会不识别，因为这源于SQLite的规范，主键以“_id”为标准。

2).措施

第一，建表时根据规范去做；
第二，查询时用别名，例如：SELECT id AS _id FROM person；
第三，使用CursorWrapper

[java]view plaincopy
  1. CursorWrappercursorWrapper=newCursorWrapper(c){
  2. @Override
  3. publicintgetColumnIndexOrThrow(StringcolumnName)throwsIllegalArgumentException{
  4. if(columnName.equals("_id")){
  5. returnsuper.getColumnIndex("id");
  6. }
  7. returnsuper.getColumnIndexOrThrow(columnName);
  8. }
  9. }

5.防止数据重复插入

[java]view plaincopy
  1. @Override
  2. publicvoidonCreate(SQLiteDatabasedb){
  3. Stringsql="createtablebitmap("+
  4. "_idintegerprimarykeyautoincrement,"+
  5. /*
  6. *防止重复,如果发现重复，数据不能添加
  7. *fileNamevarchar(100)UNIQUE
  8. *
  9. *如果是下面这样声明，重复冲突后，原有的数据将会被替换
  10. *fileNamevarchar(100)UNIQUEONCONFLICTREPLACE
  11. */
  12. "fileNamevarchar(100)UNIQUE,"+
  13. "latitudevarchar(100),"+
  14. "longitudevarchar(100),"+
  15. "addrvarchar(100),"+
  16. "isSavedinteger,"+
  17. "modelvarchar(100))";
  18. db.execSQL(sql);
  19. }

6.事务Transacation

[java]view plaincopy
  1. //数据库事务
  2. publicvoiddbTransacation(){
  3. db.beginTransaction();//开始事务
  4. try{
  5. {
  6. //多个sql执行语句
  7. }
  8. db.setTransactionSuccessful();//设置事务成功完成，将缓冲区的数据提交
  9. }finally{
  10. db.endTransaction();//结束事务
  11. }
  12. }

7.关于CursorIndexOutOfBoundsException

在使用SQLiteDatabase.openOrCreateDatabase()去打开一个sdcard上的db文件时，此时光标应该移动为记录的最后，如果没有使用cursor.moveToFirst()，就会报该异常，所有任何游标使用时，应该先moveToFirst()

[java]view plaincopy
  1. if(cursor!=null&&cursor.getCount()>0){
  2. cursor.moveToFirst();
  3. intindexTrainsetType=cursor.getColumnIndex(TrainsetTypeMetaDate.trainsetType);
  4. do{
  5. StringtrainsetType=cursor.getString(indexTrainsetType);
  6. list.add(trainsetType);
  7. }while(cursor.moveToNext());
  8. }

8.SQLITE分页查询

[java]view plaincopy
  1. //方法一：select*fromtable_namewhereid>7limit10offset3;
  2. limit10offset3;//limitN表示取N个记录,offsetP表示跳过P行.
  4. //方法二：select*fromtable_namewhereid>7limit3,10;
  5. limit3,10;//limitindex,count表示跳过index行后，取出count个记录

9.Update or Replace

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-color: rgb(255, 255, 255);"><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INSERT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">OR</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> REPLACE </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INTO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">id</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> name</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> role</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">   </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">VALUES</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'John Foo'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'CEO'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">);</span></code>

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-color: rgb(255, 255, 255);"><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INSERT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">OR</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> REPLACE </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INTO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">id</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> role</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">   </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">VALUES</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'code monkey'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">);</span></code>

GOOD: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be default (NULL).

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-color: rgb(255, 255, 255);"><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INSERT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">OR</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> REPLACE </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INTO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">id</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> role</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> name</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">   </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">VALUES</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">  </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">             </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'code monkey'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">            </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">SELECT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> name </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">WHERE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> id </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">          </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">);</span></code>

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-color: rgb(255, 255, 255);"><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INSERT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">OR</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> REPLACE </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">INTO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">id</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> name</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> role</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">   </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">VALUES</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">  </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">             </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'Susan Bar'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">            </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">COALESCE</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">((</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">SELECT</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> role </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> Employee </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(0, 0, 139); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">WHERE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> id </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">),</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'Benchwarmer'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">          </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">);</span></code>

10.Replace部分字符串

You can use the built inreplace()function to perform a string replace in a query.

Other string manipulation functions (and more) are detailed in theSQLite core functions list

The following should point you in the right direction.

UPDATE table SET field = replace( field, 'C:\afolder\', 'C:\anewfolder\' ) WHERE field LIKE 'C:\afolder\%'

更多相关文章

  1. android 数据库操作 插入彩信，数据库子查询
  2. Android(安卓)Sqlite数据库升级时注意事项
  3. android webview读取html和js .
  4. Android中实现图文并茂的按钮实例代码
  5. 利用Java反射技术调用Android中被隐藏的API
  6. Android数据的四种存储方式之三——SharedPreferences
  7. 常用方法(2)------根据图片的url路径获得Bitmap对象
  8. Android(安卓)studio连接数据库后出现的问题——insert插入问题
  9. Android中string转换成int

随机推荐

  1. 面试题：文本摘要中的NLP技术
  2. 请用Python手写实现插入排序
  3. 电脑本地换IP用派克斯如何实现
  4. 面试题：预训练方法 BERT和OpenAI GPT有什
  5. cp网站源码安装步骤详情
  6. ORA-29770: global enqueue process LMS
  7. Github最火！程序员必须知道22大定律和法则
  8. python常用的图像处理工具有哪些?工具推
  9. Ryft Cloud迁移到AWS EC2 F1实例上来了！
  10. 频谱分析仪中的 Spartan-6 FPGA