前面已经介绍了,Yii中createCommand()可以直接执行原生 SQL 外,还可以执行框架写法。
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(); //创建、更新、删除,的执行
$result = Yii::app()->db->createCommand()->insert('table表名',
array(
'column1' => $value1,
'column2' => $value2,
'column3' =>$value3,
'create_time' => time(),
)
);
$result = Yii::app()->db->createCommand()->update('table表名',
array(
'column1' => $value1,
'column2' => $value2,
'column3' =>$value3,
'update_time' => time(),
),
"id=:id", array(':id' => $id)
);
$result = Yii::app()->db->createCommand()->delete('table表名',
"id=:id", array(':id' => $id)
);
单表查询
$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();
// 首先要实例化一个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();
一般这样用:
参考资料:
http://blog.csdn.net/qq_36373262/article/details/53445954