一、ActiveRecord活动记录的CURD

• DQL

1. 查询所有
Article::findAll(["status"=>1]);

2.查询一条
Article::findOne(1);                   //根据ID查询
Article::findOne(["status" => 1]);     //根据条件查询

3.find()方法返回yiidbActiveQuery查询
Article::find()->where(["id"=>1])->one();                          //ID等于1的一条数据
Article::find()->where(["status"=>1])->all();                      //状态等于1的所有数据
Article::find()->where("status=:status",[":status"=>1])->all();    //状态等于1的所有数据

//查询状态等于1的数据并根据pubdate排序
Article::find()->where(["status"=>1])->orderBy("pubdate DESC")->all();

//查询状态等于1的数据并根据pubdate排序,从第10条开始，取4条
Article::find()->where(["status"=>1])->orderBy("pubdate ASC")->offset(10)->limit(4)->all();

• DML

//save()方法的第一个参数布尔值表示更新或插入时是否开启验证，默认为true
$article = Article::findOne(1);
$article->title = "更改测试1标题";
$article->save();

//跟新指定
Article::updateAll(["title"=>"测试1指定的跟新"],["id"=>1]);

//添加一条
$article = new Article();
$article->title = "测试添加标题1";
$article->content = "测试添加内容1";
$article->desc = "测试添加描述1";
$article->save();

//删除一条
Article::findOne(16)->delete();

//删除指定
Article::deleteAll(["id"=>16]);

二、查询构建器yiidbQuery

$db = new yiidbQuery();
1.查询一条ID为2的数据
$db->select("id,title,content")->from("article")->where("id=:id",[":id"=>2])->one();
$db->select("id,title,content")->from("article")->where(["id"=>2])->one());

2.查询多条
$db->select("id,title,content")->from("article")->where(["status"=>1])->all();
$db->select("id,title,content")->from("article")->where(["id"=>[1,2]])->all()

3.根据pubdate排序,从第10条开始，取4条
$db->select("id,title,content")->from("article")->orderBy("pubdate DESC")->offset(10)->limit(4)->all();

4.统计查询
$db->select("id")->from("article")->count();

三、yiidbCommand

• DQL

$db = Yii::$app->db;
可通过$db->tablePrefix获取表前缀如果有的话

1.查询一条
$db->createCommand("SELECT * FROM `article`")->queryOne();

2.绑定单个防SQL注入参数
$db->createCommand("SELECT * FROM `article` WHERE id=:id")->bindValue(":id",2)->queryOne();

3.绑定多个防SQL注入参数
$db->createCommand("SELECT * FROM `article` WHERE id=:id AND status=:status")->bindValues([":id"=>1,":status"=>1])->queryOne();

4.查询多条
$db->createCommand("SELECT * FROM `article`")->queryAll();

5.统计查询
$db->createCommand("SELECT COUNT("id") FROM `article`")->queryScalar();

• DML

1.更新数据
$db->createCommand()->update("`article`",["status"=>0],"id=:id",[":id"=>9])->execute();

2.插入数据
$db->createCommand()->insert("`article`",["title"=>"标题16","desc"=>"描述16","content"=>"内容16"])->execute();

3.一次插入多行
$db->createCommand()->batchInsert("`article`",["title","desc","content"],[
    ["17","17","17"],
    ["18","18","18"],
    ["19","19","19"]
])->execute();

4.删除数据

$db->createCommand()->delete("`article`","status=0")->execute();