Yii2的数据库操作总结

1.原生sql语句执行

//执行命令

Yii::$app->db->createCommand("SET time_zone = "UTC"")->execute();

//返回数据格式[[‘name’=>”,’age’=>”],[‘name’=>”,’age’=>”]]

Yii::$app->db->createCommand("SELECT * FROM post")->queryAll();

//返回格式[‘name’=>”,’age’=>”]

Yii::$app->db->createCommand("SELECT * FROM post WHERE id=1")->queryOne();

//返回格式[‘a’,’b’,’c’]

Yii::$app->db->createCommand("SELECT title FROM post")->queryColumn();

//返回格式value

Yii::$app->db->createCommand("SELECT COUNT(*) FROM post")->queryScalar();

//绑定参数

Yii::$app->db->createCommand("SELECT * FROM post WHERE id=:id AND status=:status")
           ->bindValue(":id", $_GET["id"])
           ->bindValue(":status", 1)
           ->queryOne();

$params = [":id" => $_GET["id"], ":status" => 1];
$post = Yii::$app->db->createCommand("SELECT * FROM post WHERE id=:id AND status=:status")
           ->bindValues($params)
           ->queryOne();

$post = Yii::$app->db->createCommand("SELECT * FROM post WHERE id=:id AND status=:status", $params)->queryOne();

$command = Yii::$app->db->createCommand("SELECT * FROM post WHERE id=:id");
$post1 = $command->bindValue(":id", 1)->queryOne();
$post2 = $command->bindValue(":id", 2)->queryOne();