HTML5 Web SQL
HTML5 Web SQL Database
The Web SQL Database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs that use SQL APIs for operating client databases.
If you are a web back-end programmer, it should be easy to understand SQL operations.
You can also refer to our SQL tutorial to learn more about database operations.
Web SQL database works in the latest versions of Safari, Chrome and Opera browsers.
Core methods
The following are the three core methods defined in the specification:
openDatabase: This method Create a database object using an existing database or a new database.
transaction: This method allows us to control a transaction and perform commit or rollback based on this situation.
executeSql: This method is used to execute the actual SQL query.
Open the database
We can use the openDatabase() method to open an existing database, if the database does not exist , a new database will be created with the following code:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
Description of the five parameters corresponding to the openDatabase() method:
Database name
Version number
Description text
Database size
Create callback
The fifth parameter, the creation callback will be called after the database is created.
Perform the query operation
Perform the operation using the database.transaction() function:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
});
After the above statement is executed, a table named LOGS will be created in the 'mydb' database.
Insert data
After executing the above create table statement, we can insert some data:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx .executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "php中文网")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.php.cn")');
});
We can also use dynamic values to insert data:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS
) VALUES (?, ?'), [e_id, e_log];
});
The e_id and e_log in the instance are external variables, executeSql will map the array Each entry in the parameter gives "?"
Reading data
The following example demonstrates how to read data that already exists in the database:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx .executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "php中文网")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.php.cn")');
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Query records Number of items: " + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
alert(results.rows.item(i).log );
}
}, null);
});
Complete example
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>php中文网(php.cn)</title>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "php中文网")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.php.cn")');
msg = '<p>数据表已创建，且插入了两条数据。</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>查询记录条数: " + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
</script>
</head>
<body>
<div id="status" name="status">状态信息</div>
</body>
</html>Program running result:
Delete records
The format used to delete records is as follows:
Deleting the specified data id can also be dynamic:##db.transaction(function (tx) { tx.executeSql('DELETE FROM LOGS WHERE id=1');
});
db.transaction(function(tx) { tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});
Update records
The format used for update records is as follows:
db. transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});
Update the specified data id can also be dynamic:
db.transaction(function(tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=?', [id]);
});
Update record
The format used to update the record is as follows:
Update the specified The data id can also be dynamic:##db.transaction(function (tx) { tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});
db.transaction(function(tx) { tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\ ' WHERE id=?', [id]);
})
;
##Complete example <!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>php中文网(php.cn)</title>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "php中文网")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.php.cn")');
msg = '<p>数据表已创建，且插入了两条数据。</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
msg = '<p>删除 id 为 1 的记录。</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'php.cn\' WHERE id=2');
msg = '<p>更新 id 为 2 的记录。</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>查询记录条数: " + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
</script>
</head>
<body>
<div id="status" name="status">状态信息</div>
</body>
</html>
Program running result: 
