作者: 牙叔
使用情景: 数据库
DDL(Data Definition Language)数据定义语言
数据库和表的操作都属于ddl, 比如: Create、Drop和Alter,
DDL所站的高度, 最低也比记录高一层
创建或打开数据库
const TableName = "IMGDATA"; // 表名: IMGDATA // 字段1 id 整数 主键 自增 // 字段2 name 字符串 不可为空 // 字段3 img BLOB let db = sqlite.open( "./data.db", { version: 1 }, { onOpen: function (db) { db.execSQL( "CREATE TABLE IF NOT EXISTS " + TableName + "(" + "`id` INTEGER PRIMARY KEY AUTOINCREMENT, " + "`name` TEXT NOT NULL UNIQUE, " + "`img` BLOB" + ")" ); }, } );
关闭数据库
db.close();
打印所有表
let db = sqlite.open("./data.db"); log("数据库所有表的信息: ", db.rawQuery("SELECT * FROM sqlite_master WHERE type='table'", null).all());
删除表
const TableName = "IMGDATA"; let db = sqlite.open( "./data.db", { version: 1 }, { onOpen: function (db) { db.execSQL("drop TABLE " + TableName); }, } );
DML(Data Manipulation Language 数据操控语言)
操作的单位是记录, 增删改查都属于dml, 操作的单位是记录
增加
let img = images.read(files.join(files.getSdcardPath(), "test.jpg")); let r = db.insert(TableName, { name: "test", img: img, }); log(r);
删除
log("删除name的值为test的记录: ", db.delete(TableName, "name = 'test'", null));
修改
// 修改符合条件name=test的记录, 字段name的改为测试 log("修改: ", db.update(TableName, { name: "测试" }, "name = ?", ["test"])); log("修改后: ", db.rawQuery("SELECT * FROM " + TableName + " WHERE name = ?", ["测试"]).single());
条件查询
log("条件查询: ", db.rawQuery("SELECT * FROM " + TableName + " WHERE name = ?", ["测试"]).single());
事务
const TableName = "IMGDATA"; let db = sqlite.open("./data.db"); db.transaction(function () { db.update(TableName, { name: "测试改名字" }, "name = '测试'", null); }) .on("begin", function (t) { log("事务开始: ", t); }) .on("commit", function (t) { log("事务完成: ", t); }) .on("rollback", function (t) { log("事务回滚: ", t); }) .on("end", function (t) { log("事务结束: ", t); }) .on("error", function (e) { console.error(e); }); db.close();
获取version
let db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(files.cwd() + '/testTable.db', null) log(db.getVersion()) db.close()
