Yii常用createCommand类的CURD操作大全

一、查询

Yii::app()->db->createCommand($sql)->queryAll(); //查询所有行数据
Yii::app()->db->createCommand($sql)->queryRow(); //查询第一行数据
Yii::app()->db->createCommand($sql)->queryColumn(); //查询第一列数据
Yii::app()->db->createCommand($sql)->queryScalar(); //查询第一行的第一字段

二、执行

Yii::app()->db->createCommand($sql)->execute(); //创建、更新、删除，的执行

三、插入insert

$result = Yii::app()->db->createCommand()->insert('table表名',  array(
   'column1' => $value1,
   'column2' => $value2,
   'column3' =>$value3,
   'create_time' => time(),
) );

获取插入id号：

Yii::app()->db->getLastInsertID()

四、更新update

$result = Yii::app()->db->createCommand()->update('table表名',
     array(  
           'column1' => $value1,
           'column2' => $value2,
           'column3' =>$value3,
           'update_time' => time(),
     ),
     "id=:id", array(':id' => $id)
);

五、删除delete

$result = Yii::app()->db->createCommand()->delete('table表名',
      "id=:id", array(':id' => $id)
);

六、数据访问对象 (DAO)

单表查询

$goodsTypes = Yii::app()->db->createCommand()
       ->select('type_id, type_name')
       ->from('goods_type')
       ->where('status=1')
       ->queryAll();

连表查询

$goods = Yii::app()->db->createCommand()
      ->from('goods g')
      ->select('g.good_id, g.good_name, gt.type_name, g.price, g.buy_nums, g.commit_nums, g.create_time')
      ->join('goods_type gt', 'g.good_type=gt.type_id')
      ->where('g.`status`=1 and gt.`status`=1')
      ->order('g.create_time desc')
      ->queryAll();

绑定参数查询

//bindParam(name, &value, dataType=null,length = null, $driverOptions = null)
Yii::$app->db->createCommand("select * from article_status where id=:id")->bindParam(":id",$name)->getRawSql();

# 得到sql语句（注意绑定参数后获取参数不能用getsql()要用getRawSql()函数）
# string(39) "select * from article_status where id=1"

// bindValue(name,value, $dataType = null) 绑定一个参数，直接写值不用变量
Yii::$app->db->createCommand("select * from article_status where id=:id")->bindValue(':id',1)->getRawSql()

// bindValues($values) 绑定一堆参数（用数组形式）
Yii::$app->db->createCommand("select * from article_status where id=:id AND name = :name")
->bindValues([':name'=>'ss',':id'=>1])
->getRawSql();

// 首先要实例化一个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

举个实际的例子吧：

查询列表

$command=Yii::app()->db->createCommand();
$command->select("a.*,b.user_name as create_user, e.department_name, c.supplier_name,c.supplier_operator,c.supplier_product,d.bank_name");
$command->from("ab_supplier_refund a");
$command->Leftjoin("ab_user b","a.create_user_id = b.id");
$command->Leftjoin("ab_suppliers c","a.supplier_id = c.supplier_id");
$command->Leftjoin("ab_bank d","a.bank_id = d.id");
$command->Leftjoin("ab_department e","b.user_department = e.id");
$command->andwhere("a.status != -1");
if(isset($where['start_time'])) {
    $command->andwhere("a.create_time >= " . $where['start_time']);//开始时间
}
if(isset($where['end_time'])) {
    $command->andwhere("a.create_time <= " . $where['end_time']);//结束时间
}
if(isset($where['search_all'])) {
    $search_all = strtr($where['search_all'], array('%'=>'\%', '_'=>'\_'));
    $in_int=intval($search_all);
    $command->andwhere("a.id ={$in_int} or b.user_name like '%{$search_all}%' or c.supplier_name like '%{$search_all}%' or c.supplier_operator in {$where['supplier_operator_or']} or c.supplier_product like '%{$search_all}%' or d.bank_name like '%{$search_all}%' ");
}
if(isset($where['refund_status'])) {
    $command->andwhere("a.status = " . $where['refund_status']);
}
if(isset($where['refund_id'])) {
    $command->andwhere("a.id = " . $where['refund_id']);
}
if(isset($where['create_user'])) {
    $command->andwhere("b.user_name like " . $where['create_user']);
}
if(isset($where['supplier_name'])) {
    $command->andwhere("c.supplier_name like " . $where['supplier_name']);
}
if(isset($where['supplier_operator'])) {
    $command->andwhere("c.supplier_operator = " . $where['supplier_operator']);
}
if(isset($where['supplier_product'])) {
    $command->andwhere("c.supplier_product like " . $where['supplier_product']);
}
if(isset($where['bank_name'])) {
    $command->andwhere("d.bank_name like " . $where['bank_name']);
}
if(isset($where['bank_id'])) {
    $command->andwhere("d.id = " . $where['bank_id']);
}
//排序条件
if(isset($order['refund_id'])){
    $command->order(" a.id ".$order['refund_id']);
}
if(isset($order['create_time'])){
    $command->order(" a.create_time ".$order['create_time']);
}
$command->limit($length, $start);
$list=$command->queryAll();

数据库连接

$connection=Yii::app()->db;   // 假设你已经建立了一个 "db" 连接
// 如果没有，你可能需要显式建立一个连接：
// $connection=new CDbConnection($dsn,$username,$password);
$command=$connection->createCommand($sql);
// 如果需要，此 SQL 语句可通过如下方式修改：
// $command->text=$newSQL;

执行SQL语句

$rowCount=$command->execute();	//执行无查询SQL 
$dataReader=$command->query(); // 执行一个SQL查询 
$rows=$command->queryAll(); //查询并返回结果中的所有行 
$row=$command->queryRow(); //查询并返回结果中的第一行 
$column=$command->queryColumn(); //查询并返回结果中的第一列 
$value=$command->queryScalar(); //查询并返回结果中第一行的第一个字段

获取查询结果

$dataReader=$command->query(); // 重复调用 read() 直到它返回 false 
while(($row=$dataReader->read())!==false) { ... } // 使用 foreach 遍历数据中的每一行 
foreach($dataReader as $row) { ... } // 一次性提取所有行到一个数组 

$rows=$dataReader->readAll();
$rowCount = $command->execute();
$dataReader = $command->query();
$rows=$command->queryAll();
$row =$command->queryRow();
$column =$command->queryColumn();
$value = $command->queryScalar();

绑定参数

// 一条带有两个占位符 ":username" 和 ":email"的 SQL
$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)"; 
$command=$connection->createCommand($sql);
// 用实际的用户名替换占位符 ":username" 
$command->bindParam(":username",$username,PDO::PARAM_STR); 
// 用实际的 Email 替换占位符 ":email" 
$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->execute(); // 使用新的参数集插入另一行
$command->bindParam(":username",$username2,PDO::PARAM_STR);
$command->bindParam(":email",$email2,PDO::PARAM_STR); 
$command->execute();
//方法 bindParam() 和 bindValue() 非常相似。唯一的区别就是前者使用一个 PHP 变量绑定参数， 而后者使用一个值。对于那些内存中的大数据块参数，处于性能的考虑，应优先使用前者。

绑定列

当获取查询结果时，你也可以使用 PHP 变量绑定列。 这样在每次获取查询结果中的一行时就会自动使用最新的值填充。

$sql="SELECT username, email FROM tbl_user";
$dataReader=$connection->createCommand($sql)->query();
//使用 $username 变量绑定第一列 (username) 
$dataReader->bindColumn(1,$username);
//使用 $email 变量绑定第二列 (email) 
$dataReader->bindColumn(2,$email);
while($dataReader->read()!==false){
   // $username 和 $email 含有当前行中的 username 和 email
 }

表前缀

要使用表前缀，配置 CDbConnection::tablePrefix 属性为所希望的表前缀。 然后，在 SQL 语句中使用 {{TableName}} 代表表的名字，其中的 TableName 是指不带前缀的表名。 

例如，如果数据库含有一个名为 tbl_user 的表，而 tbl_ 被配置为表前缀，那我们就可以使用如下代码执行用户相关的查询：

$sql='SELECT * FROM {{user}}';
$users=$connection->createCommand($sql)->queryAll();

使用事务

当一个应用要执行几条查询，每条查询要从数据库中读取并/或向数据库中写入信息时， 保证数据库没有留下几条查询而只执行了另外几条查询是非常重要的。 事务，在 Yii 中表现为 CDbTransaction 实例，可能会在下面的情况中启动：

开始事务.

一个个执行查询。任何对数据库的更新对外界不可见。

提交事务。如果事务成功，更新变为可见。

如果查询中的一个失败，整个事务回滚。

上述工作流可以通过如下代码实现：

$transaction=$connection->beginTransaction();
try{
   $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
   //.... other SQL executions
   $transaction->commit();
}catch(Exception $e){ // 如果有一条查询失败，则会抛出异常
   $transaction->rollBack();
}