Yii2 Sql语句

查询语句

//  start_time =< $time <= end_time 并且 status = 1
$model = TopNews::find()
    ->where(['and', ['<=', 'start_time', $time], ['>=', 'end_time', $time], ['=', 'status', 1]])
    ->asArray()->all();

$model = (new \yii\db\Query())
            ->from('{{%article}}')
            ->select('art_id, title, updated_at, image')
            ->where(['cat_id' => $cat_id, 'status' => 1])
            ->orderBy('sort_order Desc')
            ->offset($offset)
            ->limit($size)
            ->all();

$query = (new Query())->select('b.*')
            ->from(['a' => $this->assignmentTable, 'b' => $this->itemTable])
            ->where('{{a}}.[[item_name]]={{b}}.[[name]]')
            ->andWhere(['a.user_id' => (string) $userId])
            ->andWhere(['b.type' => Item::TYPE_PERMISSION]);

// 复杂点的where or  and
$offset = ($page - 1) * $size;
$model = (new \yii\db\Query())
            ->select('id, title, description, updated_at, logo, type, brand_ids')
            ->from('{{%user_qrcode}}')
            ->where(['status' => 1, 'type' => $type])
            ->andWhere([
                'OR',
                ['brand_ids' => '0'],
                ['like', 'brand_ids', ',' . $brandId . ',']
            ])
            ->orderBy('updated_at Desc')
            ->offset($offset)
            ->limit($size)
            ->all();

// 关联查询
$models = (new \yii\db\Query())
            ->select('u.id, u.username, u.realname, u.email, u.status, u.group_id, g.group_name')
            ->from('{{%user}} as u')
            ->join('LEFT JOIN', '{{%user_group}} as g', 'u.group_id = g.group_id')
            ->where(['u.parent_id' => $userId])
            ->offset($offset)
            ->limit($size)
            ->all();

// groupBy
$models = Contract::find()
            ->select("confirm_time, sum(money) as total_money")
            ->where(['status' => 1])
            ->groupBy(['FROM_UNIXTIME(confirm_time, \'%Y-%m\')'])
            ->having(['FROM_UNIXTIME(confirm_time, \'%Y-%m\')' => $month])
            ->asArray()->all();

// 按月份
$user = User::find()
    ->alias('a')
    ->select(['SUM(a.real_fee) AS fee', 'date_format(FROM_UNIXTIME(a.fee_time), "%c") AS month'])
    ->where(['a.status' => StatusEnum::ENABLED])
    ->andWhere(['between', 'a.fee_time', $beginTime, $endTime])
    ->leftJoin(Fee::tableName() . ' AS fee', 'a.fee_id = fee.id')
    ->groupBy(['month'])
    ->asArray()
    ->all();

插入语句

$user = new User();         
$user->username = $username;  
$user->password = $password;  
$user->save();

Yii::$app->db->createCommand()->insert('yii_user', ['username' => $name, 'created_at' => time()])->execute();

修改语句

// 修改username
$user = User::findOne(1);
$user->username = test;
$user->save();

// grade3 累加1
$db->createCommand()->update('yii_users', [
    'grade3' => $grade3 + 1
], 'user_id=:id', [':id' => $parentId])->execute();

// team_num 累加1
UserStats::updateAll(['team_num' => new Expression("team_num + 1")], ['in', 'user_id', $parentIds]);

// UPDATE `yii_goods` SET `stock`=stock - 6 WHERE (goods_id=37) AND (`stock` >= 6)
Yii::$app->db->createCommand()->update('{{%goods}}', [
                'stock' => new Expression("stock - $quantity")
            ], ['and', 'goods_id=:goods_id', ['>=', 'stock', $quantity]], [':goods_id' => $goods_id]);

删除语句

// 删除 name = test 的一条数据
$user = User::find()->where(['name' => 'test'])->one();
$user->delete();

// 删除年龄为30的所有用户
$result = User::deleteAll(['age'=>'30']);

// 删除user_id = 指定id 用户
$db->createCommand()
   ->delete('{{%user}}', 'user_id=:user_id', [':user_id' => $this->id])
   ->execute();

// 删除user_id、parent_id在指定数组用户
$command = $db->createCommand();
$command->delete('{{%user}}', "user_id in($userIds) AND parent_id in($parentIds)");
$command->execute();

// DELETE FROM `yii_cart` WHERE (user_id=29) AND (`goods_id` IN (29, 30, 1000))
Cart::deleteAll([
            'and',
            'user_id=:user_id',
            ['in', 'goods_id', array_keys($goods)]
        ],
            [
                ':user_id' => $uid
            ]);

加我微信公众号【皮蛋馅儿】，一起学习哦~