御品VPS

御品VPS---独特的心情尽在御品VPS

HTML5 Web SQL 数据库

<div> <div> <h1>HTML5 Web SQL 数据库 </h1> <p>Web SQL 数据库 API 并不是 HTML5 规范的一部分,但是它是一个独立的规范,引入了一组使用 SQL 操作客户端数据库的 APIs。</p> <p>如果你是一个 Web 后端程序员,应该很容易理解 SQL 的操作。</p> <p>你也可以参考我们的 ,了解更多数据库操作知识。</p> <p>Web SQL 数据库可以在最新版的 Safari, Chrome 和 Opera 浏览器中工作。</p>


<h2>核心方法</h2> <p>以下是规范中定义的三个核心方法:</p> <ol> <li>openDatabase:这个方法使用现有的数据库或者新建的数据库创建一个数据库对象。</li> <li>transaction:这个方法让我们能够控制一个事务,以及基于这种情况执行提交或者回滚。</li> <li>executeSql:这个方法用于执行实际的 SQL 查询。</li> </ol>
<h2>打开数据库</h2> <p>我们可以使用 openDatabase() 方法来打开已存在的数据库,如果数据库不存在,则会创建一个新的数据库,使用代码如下:</p> <pre> var db = openDatabase('mydb', '1.0', 'Test DB', 2 1024 1024); </pre> <p>openDatabase() 方法对应的五个参数说明:</p> <ol> <li>数据库名称</li> <li>版本号</li> <li>描述文本</li> <li>数据库大小</li> <li>创建回调</li> </ol> <p>第五个参数,创建回调会在创建数据库后被调用。</p>
<h2>执行查询操作</h2> <p>执行操作使用 database.transaction() 函数:</p> <div> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span> </span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div></div> <p>上面的语句执行后会在 'mydb' 数据库中创建一个名为 LOGS 的表。</p>
<h2>插入数据</h2> <p>在执行上面的创建表语句后,我们可以插入一些数据:</p> <div> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span> </span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (1, "菜鸟教程")</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")</span><span>'</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div></div> <p>我们也可以使用动态值来插入数据:</p> <div> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span> </span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id,log) VALUES (?, ?)</span><span>'</span><span>, </span><span>[</span><span>e_id</span><span>, </span><span>e_log</span><span>]</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div></div> <p> 实例中的 e_id 和 e_log 是外部变量,executeSql 会映射数组参数中的每个条目给 "?"。 </p>
<h2>读取数据</h2> <p>以下实例演示了如何读取数据库中已经存在的数据:</p> <div> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span> </span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (1, "菜鸟教程")</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")</span><span>'</span><span>)</span><span>; </span><span>}</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>SELECT FROM LOGS</span><span>'</span><span>, </span><span>[</span><span>]</span><span>, </span><span>function</span><span> </span><span>(</span><span>tx</span><span>, </span><span>results</span><span>)</span><span> </span><span>{</span><span> </span><span>var</span><span> </span><span>len</span><span> = </span><span>results</span><span>.</span><span>rows</span><span>.</span><span>length</span><span>, </span><span>i</span><span>; </span><span>msg</span><span> = </span><span>"</span><span><p>查询记录条数: </span><span>"</span><span> + </span><span>len</span><span> + </span><span>"</span><span></p></span><span>"</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> += </span><span>msg</span><span>; </span><span>for</span><span> </span><span>(</span><span>i</span><span> = </span><span>0</span><span>; </span><span>i</span><span> < </span><span>len</span><span>; </span><span>i</span><span>++</span><span>)</span><span>{</span><span> </span><span>alert</span><span>(</span><span>results</span><span>.</span><span>rows</span><span>.</span><span>item</span><span>(</span><span>i</span><span>)</span><span>.</span><span>log</span><span> </span><span>)</span><span>; </span><span>}</span><span> </span><span>}</span><span>, </span><span>null</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div></div>
<h2>完整实例</h2> <div> <h2>实例</h2> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span>
</span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>var</span><span> </span><span>msg</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (1, "菜鸟教程")</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")</span><span>'</span><span>)</span><span>; </span><span>msg</span><span> = </span><span>'</span><span><p>数据表已创建,且插入了两条数据。</p></span><span>'</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> = </span><span>msg</span><span>; </span><span>}</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>SELECT FROM LOGS</span><span>'</span><span>, </span><span>[</span><span>]</span><span>, </span><span>function</span><span> </span><span>(</span><span>tx</span><span>, </span><span>results</span><span>)</span><span> </span><span>{</span><span> </span><span>var</span><span> </span><span>len</span><span> = </span><span>results</span><span>.</span><span>rows</span><span>.</span><span>length</span><span>, </span><span>i</span><span>; </span><span>msg</span><span> = </span><span>"</span><span><p>查询记录条数: </span><span>"</span><span> + </span><span>len</span><span> + </span><span>"</span><span></p></span><span>"</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> += </span><span>msg</span><span>; </span><span>for</span><span> </span><span>(</span><span>i</span><span> = </span><span>0</span><span>; </span><span>i</span><span> < </span><span>len</span><span>; </span><span>i</span><span>++</span><span>)</span><span>{</span><span> </span><span>msg</span><span> = </span><span>"</span><span><p><b></span><span>"</span><span> + </span><span>results</span><span>.</span><span>rows</span><span>.</span><span>item</span><span>(</span><span>i</span><span>)</span><span>.</span><span>log</span><span> + </span><span>"</span><span></b></p></span><span>"</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> += </span><span>msg</span><span>; </span><span>}</span><span> </span><span>}</span><span>, </span><span>null</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div>
</div> <p>以上实例运行结果如下图所示:</p> <p>HTML5 Web SQL 数据库</p>
<h2>删除记录</h2> <p>删除记录使用的格式如下:</p> <pre> db.transaction(function (tx) { tx.executeSql('DELETE FROM LOGS WHERE id=1'); }); </pre> <p>删除指定的数据id也可以是动态的:</p> <pre> db.transaction(function(tx) { tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]); }); </pre>
<h2>更新记录</h2> <p>更新记录使用的格式如下:</p> <pre> db.transaction(function (tx) { tx.executeSql('UPDATE LOGS SET log=/'www.w3cschool.cc/' WHERE id=2'); }); </pre> <p>更新指定的数据id也可以是动态的:</p> <pre> db.transaction(function(tx) { tx.executeSql('UPDATE LOGS SET log=/'www.w3cschool.cc/' WHERE id=?', [id]); }); </pre>
<h2>完整实例</h2> <div> <h2>实例</h2> <div> <div><span>var</span><span> </span><span>db</span><span> = </span><span>openDatabase</span><span>(</span><span>'</span><span>mydb</span><span>'</span><span>, </span><span>'</span><span>1.0</span><span>'</span><span>, </span><span>'</span><span>Test DB</span><span>'</span><span>, </span><span>2</span><span> </span><span>1024</span><span> </span><span>1024</span><span>)</span><span>; </span><span>var</span><span> </span><span>msg</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>CREATE TABLE IF NOT EXISTS LOGS (id unique, log)</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (1, "菜鸟教程")</span><span>'</span><span>)</span><span>; </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")</span><span>'</span><span>)</span><span>; </span><span>msg</span><span> = </span><span>'</span><span><p>数据表已创建,且插入了两条数据。</p></span><span>'</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> = </span><span>msg</span><span>; </span><span>}</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>DELETE FROM LOGS WHERE id=1</span><span>'</span><span>)</span><span>; </span><span>msg</span><span> = </span><span>'</span><span><p>删除 id 为 1 的记录。</p></span><span>'</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> = </span><span>msg</span><span>; </span><span>}</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>UPDATE LOGS SET log=</span><span>/'</span><span>www.w3cschool.cc</span><span>/'</span><span> WHERE id=2</span><span>'</span><span>)</span><span>; </span><span>msg</span><span> = </span><span>'</span><span><p>更新 id 为 2 的记录。</p></span><span>'</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> = </span><span>msg</span><span>; </span><span>}</span><span>)</span><span>; </span><span>db</span><span>.</span><span>transaction</span><span>(</span><span>function</span><span> </span><span>(</span><span>tx</span><span>)</span><span> </span><span>{</span><span> </span><span>tx</span><span>.</span><span>executeSql</span><span>(</span><span>'</span><span>SELECT * FROM LOGS</span><span>'</span><span>, </span><span>[</span><span>]</span><span>, </span><span>function</span><span> </span><span>(</span><span>tx</span><span>, </span><span>results</span><span>)</span><span> </span><span>{</span><span> </span><span>var</span><span> </span><span>len</span><span> = </span><span>results</span><span>.</span><span>rows</span><span>.</span><span>length</span><span>, </span><span>i</span><span>; </span><span>msg</span><span> = </span><span>"</span><span><p>查询记录条数: </span><span>"</span><span> + </span><span>len</span><span> + </span><span>"</span><span></p></span><span>"</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> += </span><span>msg</span><span>; </span><span>for</span><span> </span><span>(</span><span>i</span><span> = </span><span>0</span><span>; </span><span>i</span><span> < </span><span>len</span><span>; </span><span>i</span><span>++</span><span>)</span><span>{</span><span> </span><span>msg</span><span> = </span><span>"</span><span><p><b></span><span>"</span><span> + </span><span>results</span><span>.</span><span>rows</span><span>.</span><span>item</span><span>(</span><span>i</span><span>)</span><span>.</span><span>log</span><span> + </span><span>"</span><span></b></p></span><span>"</span><span>; </span><span>document</span><span>.</span><span>querySelector</span><span>(</span><span>'</span><span>#status</span><span>'</span><span>)</span><span>.</span><span>innerHTML</span><span> += </span><span>msg</span><span>; </span><span>}</span><span> </span><span>}</span><span>, </span><span>null</span><span>)</span><span>; </span><span>}</span><span>)</span><span>;</span></div> </div>
</div> <p>以上实例运行结果如下图所示:</p> <p>HTML5 Web SQL 数据库</p> </div> </div>

未经允许不得转载:御品VPS » HTML5 Web SQL 数据库

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址