php操作SQLite基础

SQLite-PHP

SQLite 为轻量级的数据库。没SQL server那么复杂。虽然Apache自带MySql。但是本着技多不压身的原则学习一波。
学习操作数据库，关键四点，增、删、改、查。

连接数据库

    如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象

<?php
  //定义继承SQLite模块名为MyDB的类
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }
   $db = new MyDB();
   //判断连接或创建数据库成功
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

创建表

    在先前创建的数据库中创建一个表

<?php
  //定义继承SQLite模块名为MyDB的类
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   //定义sql语句
   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
   EOF;

  //调用SQLit3模块的额exec()方法执行$sql语句
   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Table created successfully\n";
   }
   $db->close();
?>

INSERT 操作

<?php
 class MyDB extends SQLite3
 {
    function __construct()
    {
       $this->open('test.db');
    }
 }
 $db = new MyDB();
 if(!$db){
    echo $db->lastErrorMsg();
 } else {
    echo "Opened database successfully\n";
 }
//定义sql语句
 $sql =<<<EOF
    //INSERT INTO 表名(键名，键名，....) VALUES(键值，键值，....);
    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
    VALUES (1, 'Paul', 32, 'California', 20000.00 );

    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
    VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
    VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
    VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;
////调用SQLit3模块的额exec()方法执行$sql语句
 $ret = $db->exec($sql);
 if(!$ret){
    echo $db->lastErrorMsg();
 } else {
    echo "Records created successfully\n";
 }
 $db->close();
?>

SELECT 操作

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
    //SEKECT 键名 from 表名
    // * 代表全部
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql);
   //循环遍历数组
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "<p>";
      echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n";
      echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY =  ".$row['SALARY'] ."\n\n";
      echo "</P>";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

UPDATE 操作

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }


   $sql =<<<EOF
      //UPDATE 表名 set 需修改的键名 = 值 where （ID=1 为用于定位的键名和键值）
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;


   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record updated successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n";
      echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY =  ".$row['SALARY'] ."\n\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

DELETE 操作

<?php
 class MyDB extends SQLite3
 {
    function __construct()
    {
       $this->open('test.db');
    }
 }
 $db = new MyDB();
 if(!$db){
    echo $db->lastErrorMsg();
 } else {
    echo "Opened database successfully\n";
 }

 $sql =<<<EOF
  //DELETE from 表名 where （ID=2 为用于定位的键名和键值）
    DELETE from COMPANY where ID=2;
EOF;

 $ret = $db->exec($sql);
 if(!$ret){
   echo $db->lastErrorMsg();
 } else {
    echo $db->changes(), " Record deleted successfully\n";
 }

 $sql =<<<EOF
    SELECT * from COMPANY;
EOF;
 $ret = $db->query($sql);
 while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
    echo "ID = ". $row['ID'] . "\n";
    echo "NAME = ". $row['NAME'] ."\n";
    echo "ADDRESS = ". $row['ADDRESS'] ."\n";
    echo "SALARY =  ".$row['SALARY'] ."\n\n";
 }
 echo "Operation done successfully\n";
 $db->close();
?>

该案例来源于菜鸟教程网，附上本人理解。如需更详细的请移步菜鸟教程