yii生成sql语句操作数据库实例

class IndexController extends Controller
{
public function actionIndex()
{
$con = Yii::app()->db;//数据库连接
//查询
$sql = "select * from user";
$command = $con->createCommand($sql);
$res = $command->queryAll();
print_r($res);
//插入
$sql = "insert into user (integral,name) values (999,'www.111com.net')";
$command = $con->createCommand($sql);
$res = $command->execute();
print_r($res);
//删除
$sql = "delete from user where id=1";
$command = $con->createCommand($sql);
$res = $command->execute();
print_r($res);
//查询结果
$sql = "select * from user";
$command = $con->createCommand($sql);
$res = $command->queryAll();
print_r($res);

}

}

运行后的结果为：

Array
(
    [0] => Array
        (
            [id] => 1
            [integral] => 3000
            [name] => aa
        )

    [1] => Array
        (
            [id] => 2
            [integral] => 2000
            [name] => aa
        )

    [2] => Array
        (
            [id] => 3
            [integral] => 1000
            [name] => bb
        )

)

1

1

Array
(
    [0] => Array
        (
            [id] => 2
            [integral] => 2000
            [name] => aa
        )

    [1] => Array
        (
            [id] => 3
            [integral] => 1000
            [name] => bb
        )

    [2] => Array
        (
            [id] => 4
            [integral] => 999
            [name] => www.111com.net
        )

)

    $connection = Yii::app()->db; 
    $sql = "SELECT * FROM `project` ORDER BY id DESC"; 
    $command = $connection->createCommand($sql); 
    $result = $command->queryAll(); 
    print_r($result); 

    $db = Yii::app()->db; //you have to define db connection in config/main.php 
    $sql = "select sum(if(starttime>'09:00:00',1,0)) as late, 
      sum(if(endtime<'18:00:00',1,0)) as early          
    from present where userid=:userid and date between :date_start and :date_end" 
    $results = $db->createCommand($sql)->query(array( 
      ':userid' => 115,':date_start'=>'2009-12-1',':date_end'=>'2009-12-31', 
    )); 
    foreach($results as $result){ 
      echo $result['late']," and ",$result['early']," ＼n"; 
    } 

    $sql = "select sum(if(starttime>'09:00:00',1,0)) as late, 
      sum(if(endtime<'18:00:00',1,0)) as early          
    from present where userid=115 and date between '2009-12-1' and '2009-12-31'" 
    $results = $db->createCommand($sql)->query(); 
    foreach($results as $result){ 
      echo $result['late']," and ",$result['early']," ＼n"; 
    }