对象操作
一、查询
1.简单查询
$admin= Admin: : model ( ) - > findAll ( $condition, $params) ;
$admin= Admin: : model ( ) - > findAll ( "username=:name" , array ( ":name" => $username) ) ;
$infoArr= NewsList: : model ( ) - > findAll ( "status = '1' ORDER BY id DESC limit 10 " ) ;
2. findAllByPk(根据主键查询一个集合,可以使用多个主键)
$admin= Admin: : model ( ) - > findAllByPk ( $postIDs, $condition, $params) ;
$admin= Admin: : model ( ) - > findAllByPk ( $id, "name like :name and age=:age" , array ( ':name' => $name, 'age' => $age) ) ;
$admin= Admin: : model ( ) - > findAllByPk ( array ( 1 , 2 ) ) ;
3.findAllByAttributes (根据条件查询一个集合,可以是多个条件,把条件放到数组里面)
$admin= Admin: : model ( ) - > findAllByAttributes ( $attributes, $condition, $params) ;
$admin= Admin: : model ( ) - > findAllByAttributes ( array ( 'username' => 'admin' ) ) ;
4.findAllBySql (根据SQL语句查询一个数组)
$admin= Admin: : model ( ) - > findAllBySql ( $sql, $params) ;
$admin= Admin: : model ( ) - > findAllBySql ( "select * from admin where username like :name" , array ( ':name' => '%ad%' ) ) ;
User: : find ( ) - > all ( ) ; 此方法返回所有数据;
User: : findOne ( $id) ; 此方法返回 主键 id= 1 的一条数据 ( 举个例子) ;
User: : find ( ) - > where ( [ 'name' => '小伙儿' ] ) - > one ( ) ; 此方法返回 [ 'name' => '小伙儿' ] 的一条数据;
User: : find ( ) - > where ( [ 'name' => '小伙儿' ] ) - > all ( ) ; 此方法返回 [ 'name' => '小伙儿' ] 的所有数据;
User: : find ( ) - > orderBy ( 'id DESC' ) - > all ( ) ; 此方法是排序查询;
User: : findBySql ( 'SELECT * FROM user' ) - > all ( ) ; 此方法是用 sql 语句查询 user 表里面的所有数据;
User: : findBySql ( 'SELECT * FROM user' ) - > one ( ) ; 此方法是用 sql 语句查询 user 表里面的一条数据;
User: : find ( ) - > andWhere ( [ 'sex' => '男' , 'age' => '24' ] ) - > count ( 'id' ) ; 统计符合条件的总条数;
User: : find ( ) - > one ( ) ; 此方法返回一条数据;
User: : find ( ) - > all ( ) ; 此方法返回所有数据;
User: : find ( ) - > count ( ) ; 此方法返回记录的数量;
User: : find ( ) - > average ( ) ; 此方法返回指定列的平均值;
User: : find ( ) - > min ( ) ; 此方法返回指定列的最小值 ;
User: : find ( ) - > max ( ) ; 此方法返回指定列的最大值 ;
User: : find ( ) - > scalar ( ) ; 此方法返回值的第一行第一列的查询结果;
User: : find ( ) - > column ( ) ; 此方法返回查询结果中的第一列的值;
User: : find ( ) - > exists ( ) ; 此方法返回一个值指示是否包含查询结果的数据行;
User: : find ( ) - > batch ( 10 ) ; 每次取 10 条数据
User: : find ( ) - > each ( 10 ) ; 每次取 10 条数据, 迭代查询
二、查询对象的方法
1、根据主键查询出一个对象,如:findByPk(1)
$admin= Admin: : model ( ) - > findByPk ( $postID, $condition, $params) ;
$admin= Admin: : model ( ) - > findByPk ( 1 ) ;
2、根据一个条件查询出一组数据,可能是多个,但是他只返回第一行数据
$row= Admin: : model ( ) - > find ( $condition, $params) ;
$row= Admin: : model ( ) - > find ( 'username=:name' , array ( ':name' => 'admin' ) ) ;
3、根据条件查询一组数据,可以是多个条件,把条件放到数组里面,查询的也是第一条数据
$admin= Admin: : model ( ) - > findByAttributes ( $attributes, $condition, $params) ;
$admin= Admin: : model ( ) - > findByAttributes ( array ( 'username' => 'admin' ) ) ;
4、根据SQL语句查询一组数据,他查询的也是第一条数据
$admin= Admin: : model ( ) - > findBySql ( $sql, $params) ;
$admin= Admin: : model ( ) - > findBySql ( "select * from admin where username=:name" , array ( ':name' => 'admin' ) ) ;
5、拼一个获得SQL的方法,在根据find查询出一个对象
$criteria= newCDbCriteria;
$criteria- > select= 'username' ;
$criteria- > condition= 'username=:username' ;
$criteria- > params= array ( ":username=>'admin'" ) ;
$criteria- > order = "id DESC" ;
$criteria- > limit = "3" ;
$post= Post: : model ( ) - > find ( $criteria) ;
6、多条件查询的语句
$criteria= new CDbCriteria ;
$criteria- > addCondition ( "id=1" ) ;
$criteria- > addInCondition ( 'id' , array ( 1 , 2 , 3 , 4 , 5 ) ) ;
$criteria- > addNotInCondition ( 'id' , array ( 1 , 2 , 3 , 4 , 5 ) ) ;
$criteria- > addCondition ( 'id=1' , 'OR' ) ;
$criteria- > addSearchCondition ( 'name' , '分类' ) ;
$criteria- > addBetweenCondition ( 'id' , 1 , 4 ) ;
$criteria- > compare ( 'id' , 1 ) ;
$criteria- > compare ( 'id' , array ( 1 , 2 , 3 ) ) ;
$criteria- > select = 'id,parentid,name' ;
$criteria- > join = 'xxx' ;
$criteria- > with = 'xxx' ;
$criteria- > limit = 10 ;
$criteria- > offset = 1 ;
$criteria- > order = 'xxx DESC,XXX ASC' ;
$criteria- > group = 'group 条件' ;
$criteria- > having = 'having 条件 ' ;
$criteria- > distinct = FALSE ;
三、查询个数,判断查询是否有结果
1、根据一个条件查询一个集合有多少条记录,返回一个int型数字
$n= Post: : model ( ) - > count ( $condition, $params) ;
$n= Post: : model ( ) - > count ( "username=:name" , array ( ":name" => $username) ) ;
2、根据SQL语句查询一个集合有多少条记录,返回一个int型数字
$n= Post: : model ( ) - > countBySql ( $sql, $params) ;
$n= Post: : model ( ) - > countBySql ( "select * from admin where username=:name" , array ( ':name' => 'admin' ) ) ;
3、根据一个条件查询查询得到的数组有没有数据,如果有数据返回一个true,否则没有找到
$exists= Post: : model ( ) - > exists ( $condition, $params) ;
$exists= Post: : model ( ) - > exists ( "name=:name" , array ( ":name" => $username) ) ;
四、新增
$admin= new Admin ;
$admin- > username = $username;
$admin- > password = $password;
if ( $admin- > save ( ) > 0 ) { echo "添加成功" ; } else { echo "添加失败" ; }
五、修改
Post: : model ( ) - > updateAll ( $attributes, $condition, $params) ;
$count= Admin: : model ( ) - > updateAll ( array ( 'username' => '11111' , 'password' => '11111' ) , 'password=:pass' , array ( ':pass' => '1111a1' ) ) ;
if ( $count> 0 ) { echo "修改成功" ; } else { echo "修改失败" ; }
$rt= PostList: : model ( ) - > updateAll ( array ( 'status' => '1' ) , 'staff_id=:staff AND host_id=:host' , array ( ':staff' => $staff_id, ':host' => $host_id) ) ;
Post: : model ( ) - > updateByPk ( $pk, $attributes, $condition, $params) ;
$count= Admin: : model ( ) - > updateByPk ( 1 , array ( 'username' => 'admin' , 'password' => 'admin' ) ) ;
$count= Admin: : model ( ) - > updateByPk ( array ( 1 , 2 ) , array ( 'username' => 'admin' , 'password' => 'admin' ) , 'username=:name' , array ( ':name' => 'admin' ) ) ;
if ( $count> 0 ) { echo "修改成功" ; } else { echo "修改失败" ; }
Post: : model ( ) - > updateCounters ( $counters, $condition, $params) ;
$count= Admin: : model ( ) - > updateCounters ( array ( 'status' => 1 ) , 'username=:name' , array ( ':name' => 'admin' ) ) ;
if ( $count> 0 ) { echo "修改成功" ; } else { echo "修改失败" ; }
六、删除
Post: : model ( ) - > deleteAll ( $condition, $params) ;
$count= Admin: : model ( ) - > deleteAll ( 'username=:name and password=:pass' , array ( ':name' => 'admin' , ':pass' => 'admin' ) ) ;
$count= Admin: : model ( ) - > deleteAll ( 'id in("1,2,3")' ) ;
if ( $count> 0 ) { echo"删除成功" ; } else { echo "删除失败" ; }
Post: : model ( ) - > deleteByPk ( $pk, $condition, $params) ;
$count= Admin: : model ( ) - > deleteByPk ( 1 ) ;
$count= Admin: : model ( ) - > deleteByPk ( array ( 1 , 2 ) , 'username=:name' , array ( ':name' => 'admin' ) ) ;
if ( $count> 0 ) { echo "删除成功" ; } else { echo "删除失败" ; }
操作数据库
查询
$sql= "SELECT u.account,i.* FROM sys_user as u left join user_info as i on u.id=i.user_id" ;
$rows= Yii: : $app- > db- > createCommand ( $sql) - > query ( ) ;
foreach ( $rows as $k => $v) {
echo$v[ 'add_time' ] ;
}
查询返回多行:
$command = $connection- > createCommand ( 'SELECT * FROM post' ) ;
$posts = $command- > queryAll ( ) ;
返回单行:
$command = $connection- > createCommand ( 'SELECT * FROM post WHERE id=1' ) ;
$post = $command- > queryOne ( ) ;
查询多行单值:
$command = $connection- > createCommand ( 'SELECT title FROM post' ) ;
$titles = $command- > queryColumn ( ) ;
查询标量值/ 计算值:
$command = $connection- > createCommand ( 'SELECT COUNT(*) FROM post' ) ;
$postCount = $command- > queryScalar ( ) ;
更新
$command = $connection- > createCommand ( 'UPDATE post SET status=1 WHERE id=1' ) ;
$command- > execute ( ) ;
插入更新删除
// INSERT
$connection->createCommand()->insert('user', [
'name' => 'Sam',
'age' => 30,
])->execute();
// INSERT 一次插入多行
$connection->createCommand()->batchInsert('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
])->execute();
// UPDATE
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
// DELETE
$connection->createCommand()->delete('user', 'status = 0')->execute();
事务
//事务的基本结构(多表更新插入操作请使用事务处理)
$dbTrans= Yii::app()->db->beginTransaction();
try{
$post= new Post;
$post->'title'= 'Hello dodobook!!!';
if(!$post->save())throw newException("Error Processing Request", 1);
$dbTrans->commit();
// $this->_end(0,'添加成功!!!');
}catch(Exception$e){
$dbTrans->rollback();
// $this->_end($e->getCode(),$e->getMessage());