Nodejs平台上操纵sqlite数据库的工具,语法简洁,运行效率高。
详细教程地址
安装
在工程中添加:
yarn add better-sqlite3
对象类
better-sqlite3中的对象有两类,一个是Database类,一个是Statement类,Database对象可以实现数据库的连接和基本操作,Database.prepare()
用于生成Statement对象,Statement对象是对SQL语句的封装,方便执行固定的语句。
初步使用
新建一个数据库对象:
const Database=require("better-sqlite3")
const db=new Database('../database.db',{verbose:console.log})
新建一个查询语句对象:
const stmt=db.prepare('SELECT * FROM users ');
使用查询:
var row=stmt.all()//返回全部记录
//var row=stmt.get()//返回第一条记录
console.log(row)
带参数查询:
const get_user_by_id=db.prepare('SELECT * FROM articles WHERE id = ?');//一个查询语句对象
var a=get_user_by_id.get(4)//执行查询
console.log(a)
执行SQL语句
直接执行SQL语句:
创建表:
const create_table_cats=
`CREATE TABLE cats
(
id INT PRIMARY KEY NOT NULL,
name CHAR(50) NOT NULL,
age INT
);`
db.exec(create_table_cats)//执行命令
//db.exec(`DROP TABLE cats`)//删除表
增删改查
插入一条记录:
//创建插入语句
const insert = db.prepare('INSERT INTO cats (id,name, age) VALUES (@id,@name, @age)');
//插入一条记录
insert.run({id:0,name:'Jack',age:2})
//创建批量插入语句
const insertMany = db.transaction((cats) => {
for (const cat of cats) insert.run(cat);
});
//执行批量插入语句
insertMany([
{id:2, name: 'Joey', age: 2 },
{id:3, name: 'Sally', age: 4 },
{id:4, name: 'Junior', age: 1 },
]);
查询记录:
const select_stmt=db.prepare('SELECT * FROM cats')
var cats=select_stmt.all()
console.log(cats)
更新记录:
const updata_stmt = db.prepare('UPDATE cats SET name =@name ,age=@age WHERE id=@id')
updata_stmt.run({id:1,name:"Tom",age:3})
删除记录:
const delete_stmt=db.prepare('DELETE FROM cats WHERE id = @id;')
delete_stmt.run({id:3})
全部实验代码
const Database=require("better-sqlite3")
const db=new Database('temp.sqlite',{verbose:console.log})
/*
const create_table_cats=
`CREATE TABLE cats
(
id INT PRIMARY KEY NOT NULL,
name CHAR(50) NOT NULL,
age INT
);`
db.exec(create_table_cats)
db.exec(`DROP TABLE cats`)
*/
//创建插入语句
const insert = db.prepare('INSERT INTO cats (id,name, age) VALUES (@id,@name, @age)');
insert.run({id:1,name:'Jack',age:2})
//创建循环插入语句
const insertMany = db.transaction((cats) => {
for (const cat of cats) insert.run(cat);
});
insertMany([
{id:2, name: 'Joey', age: 2 },
{id:3, name: 'Sally', age: 4 },
{id:4, name: 'Junior', age: 1 },
]);
//查询语句
const select_stmt=db.prepare('SELECT * FROM cats')
var cats=select_stmt.all()
console.log(cats)
//更改数据
const updata_stmt = db.prepare('UPDATE cats SET name =@name ,age=@age WHERE id=@id')
updata_stmt.run({id:1,name:"Tom",age:3})
//删除数据
const delete_stmt=db.prepare('DELETE FROM cats WHERE id = @id;')
delete_stmt.run({id:3})