总结
查询数据库的四种方式:
AR是模型方式 示例:表明::方法名
querybuild是new新建方式 示例:$db = new \yii\db\Query(); $db->方法名;
command是应用主体 示例:createCommand
findbysql 示例:参照例子
控制器的方式:
self是引用本身控制器中的静态类方法
AR
查询
1. 查询所有
Article::findAll(['status'=>1]);
2.查询一条
Article::findOne(1); //根据ID查询
Article::findOne(['status' => 1]); //根据条件查询
3.find()方法返回yii\db\ActiveQuery查询
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的数据并根据排序
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();
增改删
//修改
//save()方法的第一个参数布尔值表示更新或插入时是否开启验证,默认为true
//if($user->save(true,['password_hash']))
$article = Article::findOne(1);
$article->title = '更改';
$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]);
Querybuild
$db = new \yii\db\Query();
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();
createCommand
查询
$db = \Yii::$app->db;
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();
增改删
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();
findbysql
//查询所有的
$sql = "select * from user where UserId = :id";
$res = user::findBySql($sql,['id'=>1])->all();
print_r($res[0]);
//只需查询一条的
$res = user::findBySql($sql,['id'=>1])->one();
print_r($res);