數(shù)據(jù)庫之DAO

DAO (Data Access Object) 數(shù)據(jù)訪問對象是第一個面向?qū)ο蟮慕涌?
?–百度百科

數(shù)據(jù)庫之DAO CRUD 查詢條件 聯(lián)合查詢 join 事務(wù)Transaction

CRUD

class?IndexController?extends?CController{
????public?function?actionCreate()
????{
????????$rval?=?Yii::app()->db->createCommand()->insert('user',array(
????????????'username'=>'blue'?//傳入數(shù)組不需要擔心注入?，?yii自動會對數(shù)組進行?參數(shù)綁定的操作寫入
????????));
????}
????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));
????}
????//一維數(shù)組?單條數(shù)據(jù)
????public?function?actionReadRow($id)
????{
????????$res?=?Yii::app()->db->createCommand()->select('username')->from('user')->where('id=:id',array(':id'=>$id))->queryRow();
????????var_dump($res);
????}
????//查詢列
????//比如說?查詢的是所有的username
????//返回的數(shù)組是?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);
????}
????//二維數(shù)組?查詢所有
????public?function?actionReadAll($id)
????{
????????$res?=?Yii::app()->db->createCommand()->select('username')->from('user')->where('id=:id',array(':id'=>$id))->queryAll();
????????var_dump($res);
????}
????//查詢數(shù)量
????//直接返回對應(yīng)值，而不是數(shù)組
????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);
????}

聯(lián)合查詢 (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);
????}

事務(wù)(Transaction)

//yiiChina?例子
$transaction=$connection->beginTransaction();
try
{
????$connection->createCommand($sql1)->execute();
????$connection->createCommand($sql2)->execute();
????//....?other?SQL?executions
????$transaction->commit();
}
catch(Exception?$e)?//?如果有一條查詢失敗，則會拋出異常
{
????$transaction->rollBack();
}