数据库之DAO

数据库之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('id3))
                ->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();
}