The Web SQL database API is not part of the HTML5 specification, but it is a separate specification that introduces a set of APIs that uses SQL to operate on client-side databases.
If you are a Web back-end programmer, it should be easy to understand the operation of SQL.
The Web SQL database works in the latest versions of Safari, Chrome and Opera browsers. The following are the three core methods defined in the specification: We can use it. 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 operations to use The above statement, when executed, creates a table named LOGS in the ‘mydb’ database. After executing the above create table statement, we can insert some data: We can also use dynamic values to insert data: In the instance The following example shows how to read data that already exists in the database: The running result of the above example is shown in the following figure: The format used to delete records is as follows: Deleting the specified data id can also be dynamic: The format used to update records is as follows: Updating the specified data id can also be dynamic: The running result of the above example is shown in the following figure: 11.45.1. Core method ¶
openDatabase
this method creates a database object using either an existing database or a newly created database
transaction
this method allows us to control a transaction and perform a commit or rollback based on this situation.
executeSql
this method is used to execute the actual SQL query 11.45.2. Open the database ¶
openDatabase()
method to open an existing database, and if the database does not exist, a new database is created, using the following code:var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
openDatabase()
the five parameters corresponding to the method describe: 11.45.3. Perform query operation ¶
database.transaction()
function:vardb=openDatabase('mydb','1.0','Test
DB',2\*1024\*1024);db.transaction(function(tx){tx.executeSql('CREATE
TABLE IF NOT EXISTS LOGS (id unique, log)');});
11.45.4. Insert data ¶
vardb=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, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2, "www.runoob.com")');});
vardb=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]);});
e_id
and
e_log
is an external variable
executeSql
each entry in the array parameter is mapped to “?”. 11.45.5. Read data ¶
vardb=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, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query records:"+len+"</p>";document.querySelector('#status').innerHTML+=msg;for(i=0;i<len;i++){alert(results.rows.item(i).log);}},null);});
11.45.6. Complete instance ¶
Example ¶
vardb=openDatabase('mydb','1.0','Test
DB',2\*1024\*1024);varmsg;db.transaction(function(tx){tx.executeSql('CREATE
TABLE IF NOT EXISTS LOGS (id unique, log)');tx.executeSql('INSERT INTO
LOGS (id, log) VALUES (1, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');msg='<p>The data table has been created and two pieces of data have been inserted.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query records:"+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);});
11.45.7. Delete record ¶
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
});
db.transaction(function(tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});
11.45.8. Update record ¶
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]);
});
11.45.9. Complete instance ¶
Example ¶
vardb=openDatabase('mydb','1.0','Test
DB',2\*1024\*1024);varmsg;db.transaction(function(tx){tx.executeSql('CREATE
TABLE IF NOT EXISTS LOGS (id unique, log)');tx.executeSql('INSERT INTO
LOGS (id, log) VALUES (1, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');msg='<p>The data table has been created and two pieces of data have been inserted.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('DELETE
FROM LOGS WHERE id=1');msg='<p>delete id is 1
record.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('UPDATE
LOGS SET log=\\'www.w3cschool.cc\\'WHERE id=2');msg='<p>Update id to 2
record.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query records:"+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);});
