大佬教程收集整理的这篇文章主要介绍了我想像图片中那样创建一个 SQLite 数据库，我该怎么做？我只想知道如何创建链接列以链接数据库？，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
Database 1
Database 2
我想知道如何将一个数据库链接到另一个数据库。 例如，days 表中的 Apps 列应打开具有以下日期的 Apps 表的一个实例。
将所有表都放在一个数据库中会更简单。拥有包含所有表的单个数据库将允许您通过利用外键约束（即链接确实链接）来强制执行参照完整性。
以下是通过单个数据库助手（sqliteOpenHelper 的子类）附加第二个数据库的示例，这两个数据库都基于您的架构。
数据库助手
class DatabaseHelper extends sqliteOpenHelper {
public static final String dbname = "database1";
public static final int DBVERSION = 1;
public static final String OTHER_dbname = "database2";
public static final String ATTACH_scheR_111_11845@A = "a1";
public static final String TB_DAYTABLE = "daytable";
public static final String COL_DATE_DAYTABLE = "date";
public static final String COL_@R_522_10586@LTIME_DAYTABLE = "@R_522_10586@ltime";
public static final String COl_UNLOCKCOUNTS_DAYTABLE = "unlockcounts";
public static final String col_aPPS_DAYTABLE = "apps";
public static final String TB_APPS = "apps";
public static final String COl_NAME_APPS = "name";
public static final String COl_@R_522_10586@LTIME_APPS = "@R_522_10586@ltime";
public static final String COl_COUNTS_APPS = "counts";
public static final String COL_TIMELINE_APPS = "timeline";
public static final String TB_TIMELINE = "timeline";
public static final String COL_STARTTIME_TIMELINE = "starttime";
public static final String COL_ENDTIME_TIMELINE = "endtime";
public static final String COL_@R_522_10586@LTIME_TIMELINE = "@R_522_10586@ltime";
private static volatile DatabaseHelper instance;
private sqliteDatabase db;
private DatabaseHelper(@Nullable Context context) {
super(context,DBNAME,null,DBVERSION);
db = this.getWritableDatabase();
db.execSQL("ATTACH DATABASE '" + context.getDatabasePath(OTHER_DBName) + "' AS " + ATTACH_scheR_111_11845@A);
cursor csr = db.query(ATTACH_scheR_111_11845@A + ".sqlite_master",new String[]{"name"},"name=?",new String[]{TB_TIMELINE},null);
if (!csr.moveToFirst()) {
createAttachedTables(db);
}
}
public static DatabaseHelper geTinstance(Context context) {
if (instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
@Override
public void onCreate(sqliteDatabase db) {
db.execSQL("create table IF NOT EXISTS " + TB_DAYTABLE + " (" +
COL_DATE_DAYTABLE + " TEXT " +
"," + COL_@R_522_10586@LTIME_DAYTABLE + " IntegeR " +
"," + COl_UNLOCKCOUNTS_DAYTABLE + " IntegeR " +
"," + col_aPPS_DAYTABLE + " IntegeR " +
")" );
db.execSQL("create table IF NOT EXISTS " + TB_APPS + " (" +
COl_NAME_APPS + " TEXT " +
"," + COl_@R_522_10586@LTIME_APPS + " IntegeR " +
"," + COl_COUNTS_APPS + " IntegeR " +
"," + COL_TIMELINE_APPS + " TEXT " +
")");
}
private void createAttachedTables(sqliteDatabase db) {
db.execSQL("create table IF NOT EXISTS " + ATTACH_scheR_111_11845@A + "." + TB_TIMELINE + "(" +
COL_STARTTIME_TIMELINE + " TEXT " +
"," + COL_ENDTIME_TIMELINE + " TEXT " +
"," + COL_@R_522_10586@LTIME_TIMELINE + " IntegeR " +
")");
db.execSQL("create table IF NOT EXISTS " + ATTACH_scheR_111_11845@A + "." + TB_APPS + "(" +
COl_NAME_APPS + " TEXT " +
"," + COL_TIMELINE_APPS + " TEXT " +
")");
}
@Override
public void onUpgrade(sqliteDatabase sqliteDatabase,int i,int i1) {
}
}
已在活动 (MainActivity) 中使用以下内容对 Helper 进行了测试（附加第二个数据库并创建表）：-
public class MainActivity extends AppCompatActivity {
private DatabaseHelper db;
@Override
protected void onCreate(Bundle savedInstanceStatE) {
super.onCreate(savedInstanceStatE);
setContentView(R.layout.activity_main);
db = DatabaseHelper.geTinstance(this);
}
}
结果
在 Android studio 的数据库检查器中打开数据库并从两个模式（即各自的 sqlite_master 表）中提取数据，然后可以看到以下内容：-
即附加了 2 个数据库并且所有 4 个表都存在。
使用database_list PRAGMA 显示:-
即又是两个数据库
考虑到您的问题，您想要存储应用使用数据。我相信两张桌子就足够了。
那是 APPS 表似乎是重复的，所以不是必需的。 其次，可以通过存储链接到相应应用程序的开始和结束时间来计算统计数据解锁次数、总时间等。
因此，可以使用类似于以下内容的模式来确定值：-
现在假设 app 表包含：-
使用表包含：-
然后是一个查询，例如：-
SELECT
date(usage.startdatetiR_111_11845@E) AS date,app.appname,count(*) AS unlocks,time(sum(strftime('%s',usage.enddatetiR_111_11845@E) - strftime('%s',usage.startdatetiR_111_11845@E) ),'unixepoch') AS elapsedtime
FROM app JOIN usage ON app._id = usage.applink
GROUP BY date(usage.startdatetiR_111_11845@E),app._id
ORDER BY date(usage.startdatetiR_111_11845@E) ASC,appname ASC
将返回以下结果：-
示例 2 - 适用于 Android 的 2 表解决方案：-
数据库助手AltDBHelper :-
class AltDBHelper extends sqliteOpenHelper {
public static final String dbname = "appusage.db";
public static final int DBVERSION = 1;
private static sqliteDatabase db;
private static volatile AltDBHelper instance;
private AltDBHelper(@Nullable Context context) {
super(context,DBVERSION);
db = this.getWritableDatabase();
}
public static AltDBHelper geTinstance(Context context) {
if (instance == null) {
instance = new AltDBHelper(context);
}
return instance;
}
@Override
public void onCreate(sqliteDatabase db) {
db.execSQL(AppTable.getCreateSQL());
db.execSQL(UsageTable.getCreateSQL());
}
@Override
public void onUpgrade(sqliteDatabase db,int i1) {
}
/*
Insert an App into the database
*/
public long insert(String appName) {
ContentValues cv = new ContentValues();
cv.put(AppTable.APPNAME,appName);
return AltDBHelper.db.insert(AppTable.TBNAME,cv);
}
/*
Query to return the Date,App,number of unlocks and Sum of the Elapsed times (@R_522_10586@l timE)
by each day by each App
* Based upon the query :-
* SELECT
* app.appname,* date(usage.startdatetiR_111_11845@E),* time(sum(strftime('%s',enddatetiR_111_11845@E) - strftime('%s',startdatetiR_111_11845@E)),'unixepoch')
* FROM usage JOIN app ON app._id = usage.applink
* GROUP BY date(startdatetiR_111_11845@E),app._id
* ORDER BY date(startdatetiR_111_11845@E),app.appName
*
*/
public static final String DATE = "date";
private static final String DATE_COL_EXPRESSION = "date(" + UsageTable.STARTdatetiR_111_11845@E_QUALIFIED + ") AS " + DATE;
public static final String APPUNLOCKS = "unlocks";
private static final String APPUNLOCKS_COL_EXPRESSION = "count(*) AS " + APPUNLOCKS;
public static final String ELAPSEDTIME = "elapsedtime";
private static final String ELAPSEDTIME_EXPRESSION =
"time(" +
"sum(" +
"strftime('%s'," + UsageTable.ENDdatetiR_111_11845@E_QUALIFIED + ") " +
" - " +
" strftime('%s'," + UsageTable.STARTdatetiR_111_11845@E_QUALIFIED + ") " +
")" +
",'unixepoch'" +
")" +
" AS " + ELAPSEDTIME;
private static final String APPSTATS_TABLE =
AppTable.TBNAME + " JOIN " + UsageTable.TBNAME +
" ON " +
AppTable.APPID_QUALIFIED + " = " +
UsageTable.APPLINK_QUALIFIED;
private static final String DATEPART_GROUPBY = "date(" + UsageTable.STARTdatetiR_111_11845@E_QUALIFIED + ")";
private static final String APPPART_GROUPBY = AppTable.APPID_QUALIFIED;
private static final String groupBy = DATEPART_GROUPBY + "," + APPPART_GROUPBY;
public cursor getApPDAilyStatsAscursor(Long id) {
String whereClause = null;
String[] whereArgs = null;
if (id != null) {
whereClause = AppTable.APPID_QUALIFIED + "=?";
whereArgs = new String[]{String.valueOf(id)};
}
return AltDBHelper.db.query(
APPSTATS_TABLE,new String[]{
DATE_COL_EXPRESSION,AppTable.APPNAME_QUALIFIED,APPUNLOCKS_COL_EXPRESSION,ELAPSEDTIME_EXPRESSION
},whereClause,whereArgs,GROUPBY,"date(" + UsageTable.STARTdatetiR_111_11845@E_QUALIFIED + ") " + " ASC" + "," + AppTable.APPNAME + " ASC"
);
}
/* The App Table name and column definitions */
public static final class AppTable {
public static final String TBNAME = "app";
public static final String APPID = Basecolumns._ID;
public static final String APPID_QUALIFIED = TBNAME + "." + APPID;
public static final String APPID_UNIQUE = TBNAME+APPID;
public static final String APPNAME = "appname";
public static final String APPNAME_QUALIFIED = TBNAME + "." + APPNAME;
public static String getCreateSQL() {
return "create table IF NOT EXISTS " + TBNAME + "(" +
APPID + " INTEGER PRIMARY KEY " +
"," + APPNAME + " TEXT UNIQUE " +
")";
}
}
/* The Usage table name and column definitions also method for inserTing rows (alternativE) */
public static final class UsageTable {
public static final String TBNAME = "usage";
public static final String USAGEID = Basecolumns._ID;
public static final String USAGEID_QUALIFIED = TBNAME + "." + USAGEID;
public static final String USAGEID_UNIQUE = TBNAME + USAGEID;
public static final String APPLINK = "applink";
public static final String APPLINK_QUALIFIED = TBNAME + "." + APPLINK;
public static final String STARTdatetiR_111_11845@E = "startdatetiR_111_11845@e";
public static final String STARTdatetiR_111_11845@E_QUALIFIED = TBNAME + "." + STARTdatetiR_111_11845@E;
public static final String ENDdatetiR_111_11845@E = "enddatetiR_111_11845@e";
public static final String ENDdatetiR_111_11845@E_QUALIFIED = TBNAME + "." + ENDdatetiR_111_11845@E;
public static String getCreateSQL() {
return "create table IF NOT EXISTS " + TBNAME + "(" +
USAGEID + " INTEGER PRIMARY KEY" +
"," + APPLINK + " IntegeR REFERENCES " + AppTable.TBNAME + "(" + AppTable.APPID + ") ON deletE CASCADE ON updatE CASCADE" +
"," + STARTdatetiR_111_11845@E + " TEXT " +
"," + ENDdatetiR_111_11845@E + " TEXT " +
")";
}
public static long insert(long appId,String startdatetiR_111_11845@e,String enddatetiR_111_11845@E) {
ContentValues cv = new ContentValues();
cv.put(APPLINK,appId);
cv.put(STARTdatetiR_111_11845@E,startdatetiR_111_11845@E);
cv.put(ENDdatetiR_111_11845@E,enddatetiR_111_11845@E);
return AltDBHelper.db.insert(TBNAME,cv);
}
}
}
在活动中使用上述内容，@H_61_18@mainActivity 产生与上述附加部分相同的结果：-
public class MainActivity extends AppCompatActivity {
private DatabaseHelper db;
private AltDBHelper altdb;
@Override
protected void onCreate(Bundle savedInstanceStatE) {
super.onCreate(savedInstanceStatE);
setContentView(R.layout.activity_main);
db = DatabaseHelper.geTinstance(this); /* The original answer attaching a second database */
/* using alternative with just 2 tables */
altdb = AltDBHelper.geTinstance(this); /* get an Instance of the DBHelper */
/* Add data for App1 along with some usage data */
long a1 = altdb.insert("App1");
AltDBHelper.UsageTable.insert(a1,"2021-01-01 15:30:00","2021-01-01 16:00:00");
AltDBHelper.UsageTable.insert(a1,"2021-01-01 17:21:34","2021-01-01 19:33:12");
AltDBHelper.UsageTable.insert(a1,"2021-01-02 09:11:27","2021-01-02 09:15:28");
AltDBHelper.UsageTable.insert(a1,"2021-01-03 00:00:00","2021-01-03 05:00:59");
/* Likewise for App2 */
long a2 = altdb.insert("App2");
AltDBHelper.UsageTable.insert(a2,"2021-01-01 13:04:27","2021-01-01 13:06:07");
AltDBHelper.UsageTable.insert(a2,"2021-01-03 12:51:51","2021-01-03 12:55:55");
AltDBHelper.UsageTable.insert(a2,"2021-01-02 03:33:30","2021-01-02 03:33:35");
AltDBHelper.UsageTable.insert(a2,"2021-01-02 01:01:01","2021-01-02 01:03:04");
AltDBHelper.UsageTable.insert(a2,"2021-01-01 14:25:36","2021-01-01 15:26:37");
/* Add just App3 with no usage data */
long a3 = altdb.insert("App3");
/* Get the Daily App Usage */
cursor csr = altdb.getApPDAilyStatsAscursor(null);
DatabaseUtils.dumpcursor(csr); /* Dump the cursor (just to show what the cursor contains) */
csr.close(); /* done with the cursor so close it */
}
}
当上面第一次运行时（不是为了重新运行，因为它只是一个演示），光标的转储如下：-
2021-05-30 18:29:23.739 I/System.out: >>>>> Dumping cursor android.database.sqlite.sqlitecursor@9ba4107
2021-05-30 18:29:23.742 I/System.out: 0 {
2021-05-30 18:29:23.742 I/System.out: date=2021-01-01
2021-05-30 18:29:23.743 I/System.out: appname=App1
2021-05-30 18:29:23.743 I/System.out: unlocks=2
2021-05-30 18:29:23.743 I/System.out: elapsedtime=02:41:38
2021-05-30 18:29:23.744 I/System.out: }
2021-05-30 18:29:23.744 I/System.out: 1 {
2021-05-30 18:29:23.744 I/System.out: date=2021-01-01
2021-05-30 18:29:23.744 I/System.out: appname=App2
2021-05-30 18:29:23.745 I/System.out: unlocks=2
2021-05-30 18:29:23.745 I/System.out: elapsedtime=01:02:41
2021-05-30 18:29:23.745 I/System.out: }
2021-05-30 18:29:23.746 I/System.out: 2 {
2021-05-30 18:29:23.746 I/System.out: date=2021-01-02
2021-05-30 18:29:23.746 I/System.out: appname=App1
2021-05-30 18:29:23.747 I/System.out: unlocks=1
2021-05-30 18:29:23.747 I/System.out: elapsedtime=00:04:01
2021-05-30 18:29:23.747 I/System.out: }
2021-05-30 18:29:23.747 I/System.out: 3 {
2021-05-30 18:29:23.748 I/System.out: date=2021-01-02
2021-05-30 18:29:23.748 I/System.out: appname=App2
2021-05-30 18:29:23.749 I/System.out: unlocks=2
2021-05-30 18:29:23.749 I/System.out: elapsedtime=00:02:08
2021-05-30 18:29:23.749 I/System.out: }
2021-05-30 18:29:23.749 I/System.out: 4 {
2021-05-30 18:29:23.750 I/System.out: date=2021-01-03
2021-05-30 18:29:23.750 I/System.out: appname=App1
2021-05-30 18:29:23.750 I/System.out: unlocks=1
2021-05-30 18:29:23.750 I/System.out: elapsedtime=05:00:59
2021-05-30 18:29:23.751 I/System.out: }
2021-05-30 18:29:23.751 I/System.out: 5 {
2021-05-30 18:29:23.751 I/System.out: date=2021-01-03
2021-05-30 18:29:23.751 I/System.out: appname=App2
2021-05-30 18:29:23.751 I/System.out: unlocks=1
2021-05-30 18:29:23.752 I/System.out: elapsedtime=00:04:04
2021-05-30 18:29:23.752 I/System.out: }
2021-05-30 18:29:23.752 I/System.out: <<<<<
以上是大佬教程为你收集整理的我想像图片中那样创建一个 SQLite 数据库，我该怎么做？我只想知道如何创建链接列以链接数据库？全部内容，希望文章能够帮你解决我想像图片中那样创建一个 SQLite 数据库，我该怎么做？我只想知道如何创建链接列以链接数据库？所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。