简单示例
新建数据库
创建一个数据库,名为mydb,版本1.0,数据库介绍Test,数据库大小2M
const db = openDatabase('mydb', '1.0', 'Test', 2 * 1024 * 1024);
创建表
创建要给USERS表,它有两个字段,id,name,id字段类型为数字类型,自增主键。
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS USERS (id integer primary key AutoIncrement, name)')
})
修改表结构
添加一列
db.transaction(function (tx) {
tx.executeSql('ALTER TABLE result ADD master')
})
添加数据
向USERS表添加一条数据,因为ID自增,所以不用插入数据时候不用传ID
db.transaction(function (tx) {
tx.executeSql('INSERT INTO USERS (name) VALUES (?)', ["张三"])
})
修改数据
修改USERS表id为2的数据,修改它的name为王五
db.transaction(function (tx) {
tx.executeSql('UPDATE USERS SET name=? WHERE id=?', [ "王五",2])
})
删除数据
删除表中id为2的数据
db.transaction(function (tx) {
tx.executeSql('DELETE FROM USERS WHERE id=?', [2])
})
查询数据
查询所有USERS表里的数据,数据结果放在results.rows。
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM USERS', [], function (tx, results) {
console.log("结果",results.rows)
}, null)
})
删除表
删除一个名为USERS的表
db.transaction(function (tx) {
tx.executeSql('DROP TABLE USERS')
})
完整案例
通过websql实现增删改查的DEMO演示
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>WEBSQL</title>
</head>
<body>
<h2>列表</h2>
<button onclick="refresh()">刷新</button>
<table border="1">
<thead>
<tr>
<td>ID</td>
<td>姓名</td>
<td>操作</td>
</tr>
</thead>
<tbody id="list">
</tbody>
</table>
<h2>新增</h2>
<input id="addName">
<button onclick="add()">添加</button>
<h2>编辑</h2>
<input id="editName">
<button onclick="save()">保存</button>
</body>
<script>
console.log("websql")
// 创建数据库
const db = openDatabase('mydb', '1.0', 'Test', 2 * 1024 * 1024);
// 创建表
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS USERS (id integer primary key AutoIncrement, name)')
})
let editId = ''
this.refresh()
function template(id,name) {
let trDom = document.createElement('tr')
let idDom = document.createElement('td')
idDom.innerText = id
let nameDom = document.createElement('td')
nameDom.innerText = name
let actionDom = document.createElement('td')
let actionButtonEdit = document.createElement('button')
actionButtonEdit.innerText = '编辑'
actionButtonEdit.setAttribute("onclick",'edit(' + id +',"' + name +'")')
let actionButtonDel = document.createElement('button')
actionButtonDel.innerText = '删除'
actionButtonDel.setAttribute("onclick",'del(' + id +')')
actionDom.appendChild(actionButtonEdit)
actionDom.appendChild(actionButtonDel)
trDom.appendChild(idDom)
trDom.appendChild(nameDom)
trDom.appendChild(actionDom)
return trDom
}
function add() {
console.log("添加")
let dom = document.getElementById('addName')
let name = dom.value
console.log("姓名",name)
db.transaction(function (tx) {
tx.executeSql('INSERT INTO USERS (name) VALUES (?)', [name])
dom.value = ''
})
this.refresh()
}
function edit(id,name) {
console.log("编辑",id)
this.editId = id
let dom = document.getElementById('editName')
dom.value = name
}
function del(id) {
console.log("删除",id)
db.transaction(function (tx) {
tx.executeSql('DELETE FROM USERS WHERE id=?', [id])
})
this.refresh()
}
function save() {
console.log("保存")
if(this.editId){
let dom = document.getElementById('editName')
console.log("保存",this.editId,dom.value)
let name = dom.value
let sql = "UPDATE USERS SET name='" + name + "' WHERE id=" + this.editId
console.log("sql",sql)
db.transaction(function (tx) {
tx.executeSql(sql)
})
this.editId = ''
dom.value = ''
this.refresh()
}else{
alert("请先选择编辑项")
}
}
function refresh() {
console.log("刷新列表")
const list = document.getElementById('list')
list.innerHTML = ''
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM USERS', [], function (tx, results) {
console.log("查询结果",results)
for(let i =0;i<results.rows.length;i++){
let item = results.rows[i]
console.log("结果",item)
list.appendChild(template(item.id,item.name))
}
}, null)
})
}
</script>
</html>