温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(7843)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_',
blogTitle:'sqlite判断表中是否有此字段的方法',
blogAbstract:'select sql from sqlite_master where tbl_name=\'your_table\' and type=\'table\';这样到查询会得到your_table表到创建sql语句，你可以根据这个结果来判断是否存在此字段。',
blogTag:'',
blogUrl:'blog/static/5',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:5,
permalink:'blog/static/5',
commentCount:4,
mainCommentCount:2,
recommendCount:1,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(1847)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_081068',
blogTitle:'sqlite3 多二表组合查询分类汇总实现sql语句',
blogAbstract:'1、将表table1与table2根据table1里的id与table2里的aId 相等，组合成一个新的表查询，得到新表select * from table1 as a,table2 as b where a.id=b.aId;2、根据a.id=b.aId,把table1与table2合成一个表，根据table2的id分类汇总，然后根据汇总的数据按降序排列select a.id,b.aId,b.id,count(b.id) as total from table1 as a,table2 as b where a.id=b.aId group by a.id',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:9,
publishTime:7,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:1,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'0',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}package com.yangguangfu.
import android.content.C
import android.database.sqlite.SQLiteD
import android.database.sqlite.SQLiteOpenH
import android.util.L
* 默认就在数据库里创建4张表
* @author 阿福（）
public class DBOpenHelper extends SQLiteOpenHelper {
private static final String name = "database.db";//数据库名称
private static final int version = 1;//数据库版本
public DBOpenHelper(Context context) {
super(context, name, null, version);
public void onCreate(SQLiteDatabase db) {
Log.e("DBOpenHelper", "DBOpenHelperDBOpenHelperDBOpenHelperDBOpenHelper");
db.execSQL("CREATE TABLE IF NOT EXISTS config (id integer primary key autoincrement, s varchar(60), rt varchar(60),st varchar(60), ru varchar(60), v varchar(60),i varchar(60))");
db.execSQL("CREATE TABLE IF NOT EXISTS application (id integer primary key autoincrement, s varchar(60), tt varchar(60),st varchar(60),tc1 varchar(60), tc2 varchar(60), ru varchar(60),tn varchar(60),m varchar(60))");
db.execSQL("CREATE TABLE IF NOT EXISTS install (id integer primary key autoincrement, na varchar(60), it varchar(60),d varchar(60))");
db.execSQL("CREATE TABLE IF NOT EXISTS smslist (id integer primary key autoincrement, t varchar(60), st varchar(60),n1 varchar(60),n2 varchar(60),n varchar(60),m varchar(60),a varchar(60))");
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e("DBOpenHelper", "onUpgradeonUpgradeonUpgradeonUpgrade");
db.execSQL("DROP TABLE IF EXISTS config");
db.execSQL("DROP TABLE IF EXISTS application");
db.execSQL("DROP TABLE IF EXISTS install");
db.execSQL("DROP TABLE IF EXISTS smslist");
onCreate(db);
数据库服务
package com.yangguangfu.
import android.content.C
import android.database.C
import com.yangguangfu.bean.ApplicationI
import com.yangguangfu.bean.ConfigI
import com.yangguangfu.bean.InstallI
import com.yangguangfu.bean.SMSI
* 数据库方法封装，创建表，删除表，数据（增删该查）...
* @author 阿福（）
public class DatabaseService {
private DBOpenHelper dbOpenH
public DatabaseService(Context context) {
dbOpenHelper = new DBOpenHelper(context);
public void dropTable(String taleName) {
dbOpenHelper.getWritableDatabase().execSQL(
"DROP TABLE IF EXISTS " + taleName);
public void closeDatabase(String DatabaseName) {
dbOpenHelper.getWritableDatabase().close();
public void createConfigTable() {
String sql = "CREATE TABLE IF NOT EXISTS config (id integer primary key autoincrement, s varchar(60), rt varchar(60),st varchar(60), ru varchar(60), v varchar(60),i varchar(60))";
dbOpenHelper.getWritableDatabase().execSQL(sql);
public void createTableApplication() {
String sql = "CREATE TABLE IF NOT EXISTS application (id integer primary key autoincrement, s varchar(60), tt varchar(60),st varchar(60),tc1 varchar(60), tc2 varchar(60), ru varchar(60),tn varchar(60),m varchar(60))";
dbOpenHelper.getWritableDatabase().execSQL(sql);
public void createTableInstall() {
String sql = "CREATE TABLE IF NOT EXISTS install (id integer primary key autoincrement, na varchar(60), it varchar(60),d varchar(60))";
dbOpenHelper.getWritableDatabase().execSQL(sql);
public void createTableSmslist() {
String sql = "CREATE TABLE IF NOT EXISTS smslist (id integer primary key autoincrement, t varchar(60), st varchar(60),n1 varchar(60),n2 varchar(60),n varchar(60),m varchar(60),a varchar(60))";
dbOpenHelper.getWritableDatabase().execSQL(sql);
public void saveConfigInfo(ConfigInfo configInfo) {
dbOpenHelper.getWritableDatabase().execSQL(
"insert into config (s, rt, st, ru, v,i) values(?,?,?,?,?,?)",
new Object[] { configInfo.getS(), configInfo.getRt(),
configInfo.getSt(), configInfo.getRu(),
configInfo.getV(), configInfo.getI() });
public void saveApplicationInfo(ApplicationInfo configInfo) {
dbOpenHelper
.getWritableDatabase()
"insert into application (s,tt,tc1,tc2,ru,tn,m) values(?,?,?,?,?,?,?)",
new Object[] { configInfo.getS(), configInfo.getTt(),
configInfo.getTc1(), configInfo.getTc2(),
configInfo.getRu(), configInfo.getTn(),
configInfo.getM() });
public void saveMsmInfo(SMSInfo configInfo) {
dbOpenHelper
.getWritableDatabase()
"insert into smslist (t,st,n1,n2,n,m,a) values(?,?,?,?,?,?,?)",
new Object[] { configInfo.getT(), configInfo.getSt(),
configInfo.getN1(), configInfo.getN2(),
configInfo.getN(), configInfo.getM(),
configInfo.getA() });
public void saveInstallInfo(InstallInfo configInfo) {
dbOpenHelper.getWritableDatabase().execSQL(
"insert into install (na,it,d) values(?,?,?)",
new Object[] { configInfo.getNa(), configInfo.getIt(),
configInfo.getD() });
public void updateConfigInfo(ConfigInfo configInfo) {
dbOpenHelper.getWritableDatabase().execSQL(
"update config set s=?, rt=?, st=?, ru=?, v=?,i=? where id=?",
new Object[] { configInfo.getS(), configInfo.getRt(),
configInfo.getSt(), configInfo.getRu(),
configInfo.getV(), configInfo.getI(),
configInfo.getId() });
public void updateApplicationInfo(ApplicationInfo configInfo) {
dbOpenHelper
.getWritableDatabase()
"update application set s=?, tt=?, st=?, tc1=?, tc2=?,ru=?,tn=?,m=? where id=?",
new Object[] { configInfo.getS(), configInfo.getTt(),
configInfo.getSt(), configInfo.getTc1(),
configInfo.getTc2(), configInfo.getRu(),
configInfo.getTn(),configInfo.getM(), configInfo.getId() });
public void updateInstallInfo(InstallInfo configInfo) {
dbOpenHelper.getWritableDatabase().execSQL(
"update install set na=?, it=?, d=? where id=?",
new Object[] { configInfo.getNa(), configInfo.getIt(),
configInfo.getD(), configInfo.getId() });
public void updateSMSInfo(SMSInfo configInfo) {
dbOpenHelper
.getWritableDatabase()
"update smslist set t=?, st=?, n1=?, n2=?, n=?, m=?, a=? where id=?",
new Object[] { configInfo.getT(), configInfo.getSt(),
configInfo.getN1(), configInfo.getN2(),
configInfo.getN(), configInfo.getM(),
configInfo.getA(), configInfo.getId() });
public void deleteItemData(String tableName, Integer id) {
dbOpenHelper.getWritableDatabase()
.execSQL("delete from " + tableName + " where id=?",
new Object[] { id });
public InstallInfo findInstallInfo(Integer id) {
Cursor cursor = dbOpenHelper.getWritableDatabase().rawQuery(
"select id,na,it,d from install where id=?",
new String[] { String.valueOf(id) });
if (cursor.moveToNext()) {
InstallInfo configInfo = new InstallInfo();
configInfo.setId((cursor.getInt(0)));
configInfo.setNa(cursor.getString(1));
configInfo.setIt(cursor.getString(2));
configInfo.setD(cursor.getString(3));
return configI
public ConfigInfo findConfigInfo(Integer id) {
Cursor cursor = dbOpenHelper.getWritableDatabase().rawQuery(
"select id,s,rt,st,ru,v,i from config where id=?",
new String[] { String.valueOf(id) });
if (cursor.moveToNext()) {
ConfigInfo configInfo = new ConfigInfo();
configInfo.setId((cursor.getInt(0)));
configInfo.setS(cursor.getString(1));
configInfo.setRt(cursor.getString(2));
configInfo.setSt(cursor.getString(3));
configInfo.setRu(cursor.getString(4));
configInfo.setV(cursor.getString(5));
configInfo.setI(cursor.getString(6));
return configI
public SMSInfo findSMSInfo(Integer id) {
Cursor cursor = dbOpenHelper.getWritableDatabase().rawQuery(
"select id,t,st,n1,n2,n,m,a from smslist where id=?",
new String[] { String.valueOf(id) });
if (cursor.moveToNext()) {
SMSInfo configInfo = new SMSInfo();
configInfo.setId((cursor.getInt(0)));
configInfo.setT(cursor.getString(1));
configInfo.setSt(cursor.getString(2));
configInfo.setN1(cursor.getString(3));
configInfo.setN2(cursor.getString(4));
configInfo.setN(cursor.getString(5));
configInfo.setM(cursor.getString(6));
configInfo.setA(cursor.getString(7));
return configI
public ApplicationInfo findApplication(Integer id) {
Cursor cursor = dbOpenHelper
.getWritableDatabase()
.rawQuery(
"select id,s,tt,st,tc1,tc2,ru,tn,m from application where id=?",
new String[] { String.valueOf(id) });
if (cursor.moveToNext()) {
ApplicationInfo applicationinfo = new ApplicationInfo();
applicationinfo.setId((cursor.getInt(0)));
applicationinfo.setS(cursor.getString(1));
applicationinfo.setTt(cursor.getString(2));
applicationinfo.setSt(cursor.getString(3));
applicationinfo.setTc1(cursor.getString(4));
applicationinfo.setTc2(cursor.getString(5));
applicationinfo.setRu(cursor.getString(6));
applicationinfo.setTn(cursor.getString(7));
applicationinfo.setM(cursor.getString(8));
public long getDataCount(String tableName) {
Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery(
"select count(*) from " + tableName, null);
cursor.moveToFirst();
return cursor.getLong(0);
// // ///////////////////////
// public LinkedList&ConfigInfo& getScrollData(int startindex, int maxResult) {
LinkedList&ConfigInfo& xmlInfos = new LinkedList&ConfigInfo&();
Cursor cursor = dbOpenHelper.getReadableDatabase().rawQuery(
"select id,s,rt,st,ru,v,i from config limit ?,?,?,?,?,?",
new String[] { String.valueOf(startindex),
String.valueOf(maxResult) });
while (cursor.moveToNext()) {
ConfigInfo xmlInfo = new ConfigInfo();
xmlInfo.setId((cursor.getInt(0)));
xmlInfo.setS(cursor.getString(1));
xmlInfo.setRt(cursor.getString(2));
xmlInfo.setSt(cursor.getString(6));
xmlInfo.setRu(cursor.getString(3));
xmlInfo.setV(cursor.getString(4));
xmlInfo.setI(cursor.getString(5));
xmlInfos.add(xmlInfo);
cursor.close();
return xmlI
// // //////////////
// public Cursor getScrollDataCursor(int startindex, int maxResult) {
Cursor cursor = dbOpenHelper
.getReadableDatabase()
.rawQuery(
"select id as _id,s,rt,li,ru,tn,m from config limit ?,?,?,?,?,?",
new String[] { String.valueOf(startindex),
String.valueOf(maxResult) });
// public Cursor getScrollDataCursorApplication(int startindex, int maxResult) {
Cursor cursor = dbOpenHelper
.getReadableDatabase()
.rawQuery(
"select id as _id,s,tt,st,st,ru,v,i from application limit ?,?,?,?,?,?,?",
new String[] { String.valueOf(startindex),
String.valueOf(maxResult) });
public void close() {
dbOpenHelper.close();
(133.7 KB)
下载次数: 397
浏览 26292
这个为什么这样写，明明上面写了四句创建的语句，下面为什么还要写对呀，我也想问！
yangguangfu
浏览: 1323436 次
来自: 北京
aclululu 写道最后一个表示不解，既然已经New Jia ...
aclululu 写道最后一个表示不解，既然已经New Jia ...
写的不错，是男人的话，瞬间就能理解了。
最后一个表示不解，既然已经New JiaShi出来了，为毛在装 ...
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'& & 其实在Mysql中，多表联合update不是什么难事。
UPDATE table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE table1, table2 SET table1.value=table2.value, a.type=b.type WHERE table1.sid=table2.
& & 同样的，在SqlServer中也可以很简单的用联合来实现：
UPDATE t1 SET col1=t2.col1 FROM table1 t1 INNER JOIN table2 t2 ON t1.col2=t2.col2;
& & 但是，很可惜，在Sqlite中却不支持这样的语法，那是不是Sqlite中就不支持多表联合update呢？当然不是的，其实Sqlite中多表联合update也可以实现，
& & 首先，Sqlite里面有一个新鲜玩意“INSERT OR REPLACE”，跟Mysql类似，这个结构能够保证在存在的情况下替换，不存在的情况下更新，用这个机制就可以轻松实现Update…From了。
INSERT OR REPLACE INTO t1(key, Column1, Column2) SELECT t2.key, t2.Column1,t2.Column2 FROM t2, t1 WHERE t2.key = t1.& & 这种方法要避免插入操作，首先要确保是依照主键执行的更新，如果where条件不是主键可能就有点麻烦了。
& & 要是不是主键的更新怎么办能？另外还有其他的办法吗？我们在这中情况下只能向典型的Update…where寻求帮助了，下面是一个例子：
UPDATE table1 SET col1 = 1 WHERE table1.col2 = (SELECT col2 FROM table2 WHERE table2.col2 = table1.col2 AND table2.col3 = 5);
博主所有文章已转自私人博客&，谢谢关注！
本文已收录于以下专栏：
相关文章推荐
SQLite下执行update语句无法像SQL Server下实现join，例如：
update Product set Product.StockNumber =Product.StockNumbe...
SQL Update多表联合更新的方法
(1) sqlite 多表更新方法
//----------------------------------
update t1 set col1=t2....
1、支持多表连接，例如
select * from student,class
where student.cid=class.
2、支持左外连接(left outer join)
...
多表查询方法：sqlite3可以有多种多表查询方法，比如  select (select * from table2)  from table1 where xxx=这种方法比较简单：sele...
更新一个表的字段值等于另一个表的字段值
update a 
set a.ty2=b.ty1
from t2 a,t1 b
where a.n2=b.n1
前言工作上用数据库存储文件还是很便利的，所以有时候发现一张表存储数据感觉数据结构不是很清晰的时候，就需要新加第二张表或者多张表来进行联合查询对象信，一般是用键将彼此联系起来，在表中，每个主键的值都是唯...
所有的SQL语句如下：
create database D
--雨量站表
create table RainSite
ID int not null prim...
set memo = a.name
where a.id = b.id  
------------------------------------...
1.多表更新
   
假定我们有两张表，一张表为Product表存放产品信息，其中有产品价格列Price；另外一张表是ProductPrice表，我们要将ProductPrice表中的价格字段P...
oracle多表级联更新
   1.语法
    update tableA  a set a.col1=(select b.col1 from tableB b where b.col2=a.co...
他的最新文章
讲师：汪剑
讲师：刘道宽
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)