資料庫之DAO

DAO (Data Access Object) 資料訪問物件是第一個物件導向的介面
–百度百科

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<:id',array(':id'=>3))
                ->queryAll();


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


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

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

        $res = $connect->createCommand()->select('*')->from('user')
                ->where(array('and','id > :lid','id < :mid'),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 < :mid',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();
}