Yii 2 16データベースを使用した操作の要約
9776 ワード
オブジェクトアクション
検索
直接データベース操作
検索
検索
//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 ，
、
// , ：findByPk(1);
$admin=Admin::model()->findByPk($postID,$condition,$params);
$admin=Admin::model()->findByPk(1);
// , ,
$row=Admin::model()->find($condition,$params);
$row=Admin::model()->find('username=:name',array(':name'=>'admin'));
// , , ,
$admin=Admin::model()->findByAttributes($attributes,$condition,$params);
$admin=Admin::model()->findByAttributes(array('username'=>'admin'));
// SQL ,
$admin=Admin::model()->findBySql($sql,$params);
$admin=Admin::model()->findBySql("select * from admin where username=:name",array(':name'=>'admin'));
// SQL , find
$criteria=newCDbCriteria;
$criteria->select='username';// only select the 'title' column
$criteria->condition='username=:username'; // , , . addCondition
$criteria->params=array(":username=>'admin'");
$criteria->order ="id DESC";
$criteria->limit ="3";
$post=Post::model()->find($criteria);// $params isnot needed
//
$criteria= new CDbCriteria;
$criteria->addCondition("id=1");// ， where id = 1
$criteria->addInCondition('id',array(1,2,3,4,5));// where id IN (1,2,3,4,5,);
$criteria->addNotInCondition('id',array(1,2,3,4,5));// ， NOT IN
$criteria->addCondition('id=1','OR');// OR ， ， OR AND
$criteria->addSearchCondition('name',' ');// ， 。。where name like '% %'
$criteria->addBetweenCondition('id', 1, 4);//between 1 and 4
$criteria->compare('id', 1); // , addCondition addInCondition.
$criteria->compare('id',array(1,2,3)); // addInCondition
$criteria->select ='id,parentid,name';// ， select='*';
$criteria->join ='xxx'; //
$criteria->with ='xxx'; // relations
$criteria->limit = 10; // 1 ， 0，
$criteria->offset = 1; // ， limit 10 offset 1, 。limit 1,10
$criteria->order ='xxx DESC,XXX ASC' ;//
$criteria->group ='group ';
$criteria->having ='having ';
$criteria->distinct = FALSE;//
、 ,
// , int
$n=Post::model()->count($condition,$params);
$n=Post::model()->count("username=:name",array(":name"=>$username));
// SQL , int
$n=Post::model()->countBySql($sql,$params);
$n=Post::model()->countBySql("select * from admin where username=:name",array(':name'=>'admin'));
// , 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));
//$pk , ,$attributes ,$condition ,$params
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 " "; }
//array('status'=>1) admin username='admin', status 1
、
//deleteAll
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")');// id
if($count>0){echo" "; }else{echo " "; }
//deleteByPk
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 " "; }
直接データベース操作
検索
//createCommand( SQL )
$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());
参照先:
http://blog.csdn.net/yafei450225664/article/details/41543591
http://www.kuitao8.com/20150115/3471.shtml