数据库之DAO

数据库之DAO DAO (Data Access Object) 数据访问对象是第一个面向对象的接口 –百度百科 数据库之DAO CRUD 查询条件 联合查询 join 事务Transaction CRUD class IndexController extends CController { public function actionCreate () { $rval = Yii::app(

数据库之DAO

DAO (Data Access Object) 数据访问对象是第一个面向对象的接口
–百度百科

数据库之DAO CRUD 查询条件 联合查询 join 事务Transaction

CRUD

   class   IndexController   extends   CController { 
     public    function   actionCreate  () 
    { 
         $rval  = Yii::app()->db->createCommand()->insert( 'user' , array (
             'username' => 'blue'   //传入数组不需要担心注入 ， yii自动会对数组进行 参数绑定的操作写入 
        ));
    }
     public    function   actionDelete  ( $id ) 
    { 
        Yii::app()->db->createCommand()->delete( 'user' , 'id=:id' , array ( ':id' => $id ));
    }
     public    function   actionUpdate  ( $id ) 
    { 
        Yii::app()->db->createCommand()->update( 'user' , array ( 'username' => 'blue' ), 'id=:id' , array ( ':id' => $id ));
    }
     //一维数组 单条数据 
     public    function   actionReadRow  ( $id ) 
    { 
         $res  = Yii::app()->db->createCommand()->select( 'username' )->from( 'user' )->where( 'id=:id' , array ( ':id' => $id ))->queryRow();
        var_dump( $res );
    }
     //查询列 
     //比如说 查询的是所有的username 
     //返回的数组是 array('姓名1','姓名2','姓名3') 
     public    function   actionReadColumn  ( $id ) 
    { 
         $res  = Yii::app()->db->createCommand()->select( 'username' )->from( 'user' )->where( 'id=:id' , array ( ':id' => $id ))->queryColumn();
        var_dump( $res );
    }
     //二维数组 查询所有 
     public    function   actionReadAll  ( $id ) 
    { 
         $res  = Yii::app()->db->createCommand()->select( 'username' )->from( 'user' )->where( 'id=:id' , array ( ':id' => $id ))->queryAll();
        var_dump( $res );
    }
     //查询数量 
     //直接返回对应值，而不是数组 
     public    function   actionReadScalar  () 
    { 
         $res  = Yii::app()->db->createCommand()->select( 'count(*)' )->from( 'user' )->queryScalar();
        var_dump( $res );
    }
}  

查询条件

where,like,in,limit,order,group

  public    function   actionWhere  () 
    { 
         $connect  = Yii::app()->db;

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( 'id , array ( ':id' => 3 ))
                ->queryAll();


         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( 'id>:lid and id  , array ( ':lid' => 3 , ":mid" => 7 ))
                ->queryAll();


         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( 'id > :lid' , array ( ':lid' => 3 ))
                ->andWhere( 'id  , array ( ':mid' => 7 ))
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'and' , 'id > :lid' , 'id  ), array ( ':lid' => 3 , ":mid" => 8 ))
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'and' , 'id > :lid' , 'id  ), array ( ':lid' => 3 , ":mid" => 8 ))
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'in' , 'id' , array ( 3 , 4 , 5 )))
                 //->where(array('not in','id',array(3,4,5))) 
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'like' , 'username' , '%g%' ))
 //                ->where(array('not like','username','%g%')) 
 //                ->where(array('like','username',array('%g%','%o%'))) 
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'and' , 'id > :id' , 'id  , array ( 'or' , 'username = :user1' , 'username = :user2' )),
                         array ( ':id' => 4 ,
                             ':mid' => 10 ,
                             'user1' => 'blue' ,
                             'user2' => 'green' 
                            ))
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'like' , 'username' , '%g%' ))
                ->offset( 1 )
                ->limit( 2 )
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*' )->from( 'user' )
                ->where( array ( 'like' , 'username' , '%g%' ))
                ->order( 'id desc' )
                ->queryAll();

         $res  =  $connect ->createCommand()->select( '*,count(*)' )->from( 'user' )
                ->group( 'username' )
                ->queryAll();

        var_dump( $res );
    }  

联合查询 (join)

  public    function   actionJoin  () 
    { 
         $res  = Yii::app()->db->createCommand()
                ->select( '*' )
                ->from( 'user as u' )
                ->join( 'city as c' , 'u.city = c.id' )
 //                ->leftJoin('city as c','u.city = c.id') 
                ->queryAll();
        var_dump( $res );
    }  

事务(Transaction)

  //yiiChina 例子 
 $transaction  =  $connection  -> beginTransaction();
try
{
     $connection  -> createCommand( $sql1 ) -> execute();
     $connection  -> createCommand( $sql2 ) -> execute();
     //.... other SQL executions 
     $transaction  -> commit();
}
catch(Exception  $e )  // 如果有一条查询失败，则会抛出异常 
{
     $transaction  -> rollBack();
}  

查看更多关于数据库之DAO的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did95958