YII 数据库相关操作

$connection = new CDbConnection($dsn, $username, $password);
$connection->active = true;     // 只有激活了连接才可以使用
$connection->active = false;  // 关闭连接 

Yii::app()->db  

$connection=Yii::app()->db;
$command=$connection->createCommand($sql);

$newSQL = "SQL语句";
$command->text=$newSQL;

$rowCount=$command->execute();  // execute the non-query SQL
$dataReader=$command->query();  // execute a query SQL // 返回CDbDataReader对像
$rows=$command->queryAll();     // query and return all rows of result
$row=$command->queryRow();      // query and return the first row of result
$column=$command->queryColumn();    // query and return the first column of result
$value=$command->queryScalar();     // query and return the first field in the first row

$dataReader=$command->query();

while(($row=$dataReader->read())!==false) 

foreach($dataReader as $row)

$rows=$dataReader->readAll();

// 首先，建立一个连接
$connection = Yii::app()->db;
// 第二，开始事务
$transaction=$connection->beginTransaction();
// 第三，执行SQL，如果错误就抛出异常，在异常处理中回滚。
try
{
$connection->createCommand($sql1)->execute();
$connection->createCommand($sql2)->execute();
//.... other SQL executions
// 如果SQL执行都没有抛出异常，那就提交。
$transaction->commit(); 
} catch(Exception $e) {
    $transaction->rollBack();   // 在异常处理中回滚
}

// 第一，建立一个连接：
$connection = Yii::app()->db;
// 第二，写下无敌的SQL语句，比如：
$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";
// 第三，创建CDbCommand对象用于执行SQL
$command=$connection->createCommand($sql);
// 接下来，将SQL语句中的形式参数，替换为实际参数
$command->bindParam(":username",$username,PDO::PARAM STR);   // 这与PDO有点不同，PDO中不带冒号
$command->bindParam(":email",$email,PDO::PARAM STR);    // 同样
// 最后，执行
$command->execute();

$connection = Yii::app()->db;
$sql = "SELECT username, email FROM tbl_user";
$dataReader = $connection->createCommand($sql)->query();    //很赞的方法链, 可惜不能接着.each()
$dataReader->bindColumn(1, $username);  //第一列值绑定到$username
$dataReader->bindColumn(2, $email);     //第二列值绑定到$email
//接着循环读取并操作数据
while( $dataReader->read() !== false ) {
    ... // 与先前的 while(($row=$dataReader->read())!==false) 有所不同哦！
}

$user = Yii::app()->db->createCommand();    
        ->select("id, username, profile")
        ->from("tbl_user u")
        ->join("tbl_profile p", "u.id=p.user_id")
        ->where("id=:id", array(":id"=>$id)
        ->queryRow();       // 返回匹配的结果集的第一行
        // 其实这条语句是这样的： $newSQL ="SELECT id, username, profile from tbl_user u INNER JOIN tbl_profile p ON u.id = p.user_id WHERE u.id =:id"

// 首先要实例化一个CDbCommand对象
$command = Yii::app()->db->createCommand();     // 注意参数留空了。。
// 可用的方法列表如下：
->select(): SELECT子句
->selectDistinct(): SELECT子句，并保持了记录的唯一性
->from():         构建FROM子句
->where():        构建WHERE子句
->join():         在FROM子句中构建INNER JOIN 子句
->leftJoin():     在FROM子句中构建左连接子句
->rightJoin():    在FROM子句中构建右连接子句
->crossJoin():    添加交叉查询片段(没用过)
->naturalJoin():  添加一个自然连接子片段
->group():        GROUP BY子句
->having():       类似于WHERE的子句，但要与GROUP BY连用
->order():        ORDER BY子句
->limit():        LIMIT子句的第一部分
->offset():       LIMIT子句的第二部分
->union():        appends a UNION query fragment

// 但你可以这样：
select("username, email");

// 或使用表限定，或使用别名
select("tbl_user.id, username name");

// 或使用数组作为参数
select(array("id", "count(*) as num"));

from("tbl_user, tbl_post, tbl_profile");
// 当然，你也可以使用表别名, 还可以使用完整的数据库限定名
from("tbl_user u, public.tbl_profile p");

// 在where()中使用 AND
where(array("and", "id=:id", "username=:username"), array(":id"=>$id, ":username"=>$username);
// 在where()中使用 OR 与 AND用法相同，如下：  ##看起来比直接写更加繁琐##
where( array("and", "type=1", array("or", "id=:id","username=:username") ),array(":id"=>$id, ":username"=>$username ));
// IN 操作符用法
where(array("in", "id", array(1,2,3)))
// LIKE 用法
where( array("like", "name", "%tester%") );
where( array("like","name", array("%test%", "%sample%")) ) // 等于 name LIKE "%test%" AND name LIKE "%sample%
    // 再这样复杂下去, 使用这种方法简直是自杀行为。

$keyword=$ GET["q"];
// escape % and characters
$keyword=strtr($keyword, array("%"=>"n%", " "=>"n "));
$command->where(array("like", "title", "%".$keyword."%"));

$command = Yii::app()->db->createCommand();
$users = $command->select("*")->from("tbl_users")->queryAll();
$command->reset(); // clean up the previous query
$posts = $command->select("*")->from("tbl_posts")->queryAll();

$post = new Post();
$post->title = "new title";
$post->content = "new content";
$post->save();      // 保存即插入

class Post extends CACtiveRecord
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    public function tablName()
    {
        return "{{post}}";
    }
}

public function primaryKey()
{
    return "id";        // "id" 是关联表中的一个字段，但他不是主键，现在将它指定为主键
}

$post = new Post;
$post->title = "sample post";
$post->content = "content for the sample post";
$post->create_time = time();
$post->save();  // 保存/插入

$post=Post::model()->find($condition,$params);                  // 返回Post对象（如果有匹配记录的话）, 否则返回NULL
$post=Post::model()->findByPk($postID,$condition,$params);
$post=Post::model()->findByAttributes($attributes,$condition,$params);
$post=Post::model()->findBySql($sql,$params);

$post=Post::model()->find("postID=:postID", array(":postID"=>10));

$criteria = new CDbCriteria;
$criteria->select="title";
$creteria->condition="postID=:postID";
$criteria->params=array(":postID"=>10);
$post=Post::model()->find($criteria);   // 不需要第二个参数

$post=Post::model()->find(array(
        "select" => "title",
        "condition" => "postID=:postID",
        "params" => array(":postID" => 10)
        ));

// find all rows satisfying the specified condition
$posts=Post::model()->findAll($condition,$params);
// find all rows with the specified primary keys
$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
// find all rows with the specified attribute values
$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
// find all rows using the specified SQL statement
$posts=Post::model()->findAllBySql($sql,$params);

// get the number of rows satisfying the specified condition
$n=Post::model()->count($condition,$params);
// get the number of rows using the specified SQL statement
$n=Post::model()->countBySql($sql,$params);
// check if there is at least a row satisfying the specified condition
$exists=Post::model()->exists($condition,$params);

$post=Post::model()->findByPk(10);
$post->title="new post title";
$post->save(); // save the change to database

if( CActiveRecord::isNewRecord )
// update the rows matching the specified condition
Post::model()->updateAll($attributes,$condition,$params);
// update the rows matching the specified condition and primary key(s)
Post::model()->updateByPk($pk,$attributes,$condition,$params);
// update counter columns in the rows satisfying the specified conditions
Post::model()->updateCounters($counters,$condition,$params);

$post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
$post->delete(); // delete the row from the database table

// 类级别的方法
// delete the rows matching the specified condition
Post::model()->deleteAll($condition,$params);
// delete the rows matching the specified condition and primary key(s)
Post::model()->deleteByPk($pk,$condition,$params);

$post->title = $_POST["title"];
$post->content = $_POST["content"];
$post->save();

// assume $ POST["Post"] is an array of column values indexed by column names
$post->attributes=$ POST["Post"];
$post->save();

public function relations()
{
    return array(
        "author"=>array(self::BELONGS_TO, "User", "author_id"),     // 返回User对象
        "categories"=>array(self::MANY_MANY, "Category", "tbl_post_category(post_id, category_id)"),
    );
}

public function relations()
{
    return array(
            "posts" => array(self::HAS_MANY, "Post", "author_id"),
            "profile" => array(self::HAS_ONE, "Profile", "owner_id")
    );
}

$author = User::model()->findByPk(1);
$author->posts;         // posts关系已经定义。

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post"s author: a relational query will be performed here
$author=$post->author;  // 如果先前没有执行过，现在才执行这个关系查询(事情拖到这一步才做，真的是很懒啊！)

$posts=Post::model()->with("author")->findAll();
// SQL => "SELECT tbl_post.*, author.* FROM tbl_post t INNER JOIN tbl_user author ON t.author = tbl_user.id"

$posts=Post::model()->with("author","categories")->findAll();
// SQL => "SELECT * FROM tbl_post t INNER JOIN tbl_user u ON t.author = u.id INNER JOIN categories c ON t.id = c.post_id"

$posts=Post::model()->with(
    "author.profile",
    "author.posts",
    "categories")->findAll();

$criteria=new CDbCriteria;
$criteria->with=array(
"author.profile",
"author.posts",
"categories",
);
$posts=Post::model()->findAll($criteria);

$posts=Post::model()->findAll(array(
                                    "with"=>array(
                                    "author.profile",
                                    "author.posts",
                                    "categories",
                                    )
                             );

$user = User::model()->with("posts")->findAll();
                "VS"
$user = User::model()->with(array(
                                    "posts" => array(
                                                        "select" => false,
                                                        "joinType" => "INNER JOIN",
                                                        "condition" => "posts.published = 1"
                                                    ),
                                 )
                           )->findAll();

class User extends CActiveRecord
{
    public function relations()
    {
            return array(
                    "posts"=>array(self::HAS MANY, "Post", "author id",
                    "order"=>"posts.create time DESC",
                    "with"=>"categories"),
                    "profile"=>array(self::HAS ONE, "Profile", "owner id"),
                    );
    }
}

$posts=Post::model()->with("comments")->findAll(array(
        "order"=>"t.create time, comments.create time"
    ));

User::model()->with(array(
        "posts"=>array("order"=>"posts.create time ASC"),
        "profile",
    ))->findAll();

$user=User::model()->findByPk(1);
$posts=$user->posts(array("condition"=>"status=1"));        // 返回的都是AR对象, 而不是数据

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            "commentCount"=>array(self::STAT, "Comment", "post_id"),
            "categoryCount"=>array(self::STAT, "Category", "post_category(post_id, category_id)"),
        );
    }

}