Yii2.0 数据库增删改查操作语句 [ 2.0 版本 ]

遇到$号有空格的地方，删除空格
一、查询
1、
$ request = Yii:: $app->request;
$id = $request->get(‘id’);
$sql = “SELECT FROM article WHERE id=:id”;
$ r = Article::findBySql($ sql,[’:id’=>$id])->all();
2、
$rows = (new \yii\db\Query())
->select([‘id’, ‘email’])
->from(‘user’)
->where([‘name’ => ‘Lily’])
->limit(10)
->all();
3、
$posts = Yii:: $app->db->createCommand(‘SELECT FROM post’)->queryAll();//queryOne()返回第一行、queryColumn()返回第一列、queryScalar()返回一个标量值
4、
Article::find()->all();
Article::find()->where([‘id’=>3])->all();
Article::find()->where([’>’,‘id’,4])->all();//id大于4的
Article::find()->where([‘between’,‘id’,1,5])->all(); //id在1-5之间
Article::find()->where([‘like’,‘title’,‘你好啊’])->all();
Article::find()->where([‘id’=>1])->one();
Article::findOne(2);//id为2的值
Article::find()->orderBy(‘id DESC’)->asArray()->all();//返回数组 ORDER BY id DESC
Article::find()->select(‘name’)->asArray()->all();//只查询某个字段
Article::find()->where([‘and’, ‘id=1’, ‘id=2’)->all();//id=1 AND id=2
Article::find()->where([‘and’, ‘state=1’, [‘or’, ‘id=1’, ‘id=2’]])->all();//state=1 AND (id=1 OR id=2)
Article::find()->where([‘between’, ‘id’, 1, 10])->all();//id BETWEEN 1 AND 10
Article::find()->where([‘in’, ‘id’, [1, 2, 3]])->all();//id IN (1, 2, 3)
Article::find()->where([‘like’, ‘name’, ‘tester’])->all();//name LIKE ‘%tester%’
Article::find()->where([‘like’, ‘name’, [‘test’, ‘sample’]])->all();//name LIKE ‘%test%’ AND name LIKE ‘%sample%’

二、增加
1、
$article = new Article();
$article->title = ‘ef2e232f32f232f2’;
// $data = $article->insert();//添加
$article->save();//添加
$data = $article->attributes[‘id’];//获取当前添加的记录ID值
2、
$db->createCommand(‘INSERT INTO customer (name) VALUES (:name)’, [’:name’ => ‘哈哈’,])->execute();
3、
$values = [
‘name’ => ‘Lily’,
‘tel’ => ‘1324567890’,
];
$customer = new Customer();
$customer->attributes = $values;
$customer->save();

三、修改
1、
$article = Article::findOne(10);
// $article = Article::find()->where([‘name’=>‘Lily’])->one();
$article->title = ‘你好你好你好！’;
// $data = $article->update();
$data = $article->save();
2、
Article::updateAllCounters([‘num’=>1],[‘id’=>8]);// update article set num=num+1 where id=8
3、
Article::updateAll([‘status’ => 1], [‘like’, ‘tel’, ‘1234567890’]);// UPDATE article SET status = 1 WHERE tel LIKE %1234567890%

四、删除
1、
$article = Article::findOne(15);
// $article = Article::find()->where([‘id’=>14])->one();
// $article = Article::find()->where([‘id’=>13])->all();
$data = $article->delete();
2、
$data = Article::deleteAll(‘id=:id’,[’:id’=>13]);
$data = Article::deleteAll(‘id>:id And num<:num’,[’:id’=>13,‘num’=>100]);
作者：rainbow-0
来源：CSDN
原文：https://blog.csdn.net/weixin_43356295/article/details/82969233
版权声明：本文为博主原创文章，转载请附上博文链接！