YII1 增、删、改、查数据库操作

 

增

//注意插入的时候 是用的new Post
$objectPost = new Post;      
$objectPost->username = $username;
$objectPost->password = $password;
//或许
$objectPost->attributes = $arrNewData;
if($objectPost->save()){
    $intPostId= $objectPost->primaryKey; //生成主键id
    echo "添加成功";
}else{
    echo "添加失败";
    //在调试的状态下可以使用$objectPost->getErrors()来打印数据模型错误信息 
}

删 

根据条件删除集合 deleteAll($condition='',$params=array());

$condition = 'username=:name and password=:pass';
$params = array(':name'=>'www.aipanshi.com',':pass'=>'aipanshi.com');
//删除postid为这些的数据
$boolean = Post::model()->deleteAll('postid in("1,2,3")');
if($boolean > 0){
    echo "删除成功";
}else{
    echo "删除失败";
}
 ```

根据主键删除一个集合 deleteByPk($pk,$condition='',$params=array())

$boolean = Post::model()->deleteByPk(1);
$boolean = Post::model()->deleteByPk(array(1,2),'username=:name',array(':name'=>'aipanshi.com'));
if($boolean > 0){
    echo "删除成功";
}else{
    echo "删除失败";
}

改

根据条件 updateAll($attributes,$condition='',$params=array())

$attributes = array('username'=>'www.aipanshi.com','postsort'=>'99');
$condition = 'status=:s';
$params = array(':s'=>2);
$boolean = Post::model()->updateAll($attributes,$condition,$params);
if($boolean > 0){
    echo "修改成功";
}else{
    echo "修改失败";
}

根据主键 updateByPk($pk,$attributes,$condition='',$params=array())

/**
 * $pk主键 也可以是一个数组集合
 * $attributes是要修改的字段的集合
 * $condition条件
 * $params传入的值
 */ 
$attributes = array('username'=>'www.aipanshi.com','password'=>'www.aipanshi.com');
$count =Post::model()->updateByPk(1,$attributes);
$attributes2 = array('username'=>'www.aipanshi.com1','password'=>'www.aipanshi.com1');
$condition2 = 'username=:name';
$params2 = array(':name'=>'www.aipanshi.com');
$boolean =Post::model()->updateByPk(array(1,2),$attributes2,$condition2,$params2);
if($boolean > 0){
    echo "修改成功";
}else{
    echo "修改失败";
}

自增和自减 updateCounters($counters,$condition='',$params=array());

//数据库中的post表根据条件username='www.aipanshi.com',查询出的所有结果status字段都自加1
$counters = array('status'=>1);//-1自减
$condition = 'username=:name';
$params = array(':name'=>'www.aipanshi.com');
$boolean=Post::model()->updateCounters($counters,$condition,$params);
if($boolean > 0){
    echo "修改成功";
}else{
    echo "修改失败";
}

查

查询指定字段

$res = KnowledgeAuthList::model()->find([
            'select' => ['id'],
            'condition' => 'auth_name=:auth_name',
            'params' => [':auth_name'=>'基础']
        ]);

根据条件查询一个集合 findAll($condition='',$params=array())

$res=Post::model()->findAll("username=:name",array(":name"=>$username));
$res=Post::model()->findAll("username=:name and status=:s",array(":name"=>'jack',':s'=>1));
$info = Post::model()->findAll("status = '1' ORDER BY postid DESC limit 10 ");
 ```

根据主键查询一个集合,可以使用多个主键 findAllByPk($pk,$condition='',$params=array())

$condition = "username like :name and status=:s";
$res=Post::model()->findAllByPk($ids,$condition,array(':name'=>$name,':s'=>1));
$res=Post::model()->findAllByPk(array(1,2));

根据数组条件查询一个集合 findAllByAttributes($attributes,$condition='',$params=array())

$res=Post::model()->findAllByAttributes($attributes,$condition,$params);
$res=Post::model()->findAllByAttributes(array('username'=>'www.aipanshi.com'));

根据SQL语句查询一个数组 findAllBySql($sql,$params=array())

$res=Post::model()->findAllBySql($sql,$params);
$sql = "select * from tbl_post where username like :name";
$res=Post::model()->findAllBySql($sql,array(':name'=>'%Jack%'));

根据主键查询出一个对象 findByPk($pk,$condition='',$params=array())

$res=Post::model()->findByPk($postID,$condition,$params);
$res=Post::model()->findByPk(1);

根据条件查询,他只返回第一行数据find($condition='',$params=array())

$res=Post::model()->find('username=:name',array(':name'=>'www.aipanshi.com'));

根据数组条件查询,查询的也是第一条数据findByAttributes($attributes,$condition='',$params=array())

$res=Post::model()->findByAttributes(array('username'=>'www.aipanshi.com'));

根据SQL语句查询,查询的也是第一条数据 findBySql($sql,$params=array())

$sql = "select * from objectResult where username=:name";
$res=Post::model()->findBySql($sql,array(':name'=>'www.aipanshi.com'));

通过CDbCriteria类find查询出一个对象

$c=new CDbCriteria; 
$c->select='username'; // 限制显示哪些字段 
$c->condition='username=:username';     //一个查询条件用aCondition.多条件用addCondition
$c->params=array(":username=>'www.aipanshi.com'");
$c->order = "postsort DESC";
$c->limit = "3";
$post=Post::model()->find($c);

多条件查询的语句

$c = new CDbCriteria;     
$c->addCondition("postid=1"); //等同于 where postid = 1 
$c->addInCondition('postid', array(1,2,3,4,5)); //等同于 where postid IN (1,2,3,4,5,); 
$c->addNotInCondition('postid', array(1,2,3,4,5));//等同于 NOT IN (1,2,3,4,5,)
$c->addCondition('postid=1','OR');//等同于 OR而非AND 
$c->addSearchCondition('username', 'www.aipanshi.com');//等同于 where name like '%www.aipanshi.com%' 
$c->addBetweenCondition('postid', 1, 4);// 等同于 between 1 and 4
$c->compare('postid', 1);    //根据你的参数自动处理成addCondition或者addInCondition.
$c->compare('postid', array(1,2,3));   //数组就会调用addInCondition 
$c->select = 'postid,parentid,name'; //限制显示哪些字段 
$c->join = 'xxx'; //连接表 
$c->with = 'xxx'; //调用relations  
$c->limit = 10;    //取1条数据，如果小于0，则不作处理 
$c->offset = 1;   //两条合并起来，则表示 limit 10 offset 1,或者代表了。limit 1,10 
$c->order = 'xxx DESC,XXX ASC' ;//排序条件 
$c->group = 'group 条件'; 
$c->having = 'having 条件 '; 
$c->distinct = FALSE; //是否唯一查询

根据一个条件查询一个集合有多少条记录,返回一个int型数字count($condition='',$params=array())

$intCount=Post::model()->count("username=:name",array(":name"=>'aipanshi.com'));

根据SQL语句查询一个集合有多少条记录,返回int型 countBySql($sql,$params=array())

$sql = "select * from objectResult where username=:name";
$intCount=Post::model()->countBySql($sql,array(':name'=>'aipanshi.com'));

根据条件查询有无数据返回一个boolean exists($condition='',$params=array())

$boolExist=Post::model()->exists("name=:name",array(":name"=>'aipanshi.com'));

查询出复合条件的用户数组，指定了index以后，结果数组$users已经是用id做为主键的了

$posts = Post::model()->findAll(array('condition'=>'postsort > 18', 'index'=> 'id'));

摘自：http://www.aipanshi.com/post/type.html?name=yii