HTML5 detailed introduction to 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 for using SQL to operate client databases.

Core method

OpenDatabase-Create a database object using an existing database or a new database

Transaction-Be able to control a thing , and perform commit or rollback based on this situation

executeSql-Execute the actual SQL statement

Open the database

//用openDatabase()方法打开已存在的数据库，如果数据库不存在，则会创建一个新的数据库
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

The description of the five parameters corresponding to the openDatabase() method:

1. Database name

2. Version number

3. Description text

4. Database size (bytes)

5. Create callback (optional)

The fifth parameter, the creation callback will be called after the database is created.

Create table

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)');
});

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 (id, log) VALUES (1, "博客园")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.cnblogs.com")');
});

You can also use dynamic values ​​to insert data

//e_id 和 e_log 是外部变量，executeSql 会映射数组参数中的每个条目给 "?"
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 (?, ?)', [e_id, e_log]);
});

Read 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, "博客园")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.cnblogs.com")');
});
 
db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
      var len = results.rows.length;
      msg = "<p>查询记录条数: " + len + "</p>";
      document.querySelector(&#39;#status&#39;).innerHTML +=  msg;
    
      for (i = 0; i < len; i++){
          msg = "<p><b>" + results.rows[i].log + "</b></p>";
      }
    
   }, null);
});

Delete records

db.transaction(function (tx) {
    tx.executeSql('DELETE FROM LOGS  WHERE id=1');
});

Deleting data can also be dynamic

db.transaction(function(tx) {
    tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});

Update record

tx.executeSql("UPDATE CC SET logname='www.baidu.com' WHERE id=2");

Update data can also be dynamic

tx.executeSql("UPDATE CC SET logname='www.baidu.com' WHERE id=?", [id]);

Note : Delete and modify cannot be placed in the same executeSql as the table creation statement. It is best to write them separately

The following is the built database

The above is the detailed content of HTML5 detailed introduction to Web SQL database. For more information, please follow other related articles on the PHP Chinese website!