1. 基础查询
User::find()->all(); //此方法返回所有数据; User::findOne($id); //此方法返回 主键 id=1 的一条数据(举个例子); User::find()->where(['name' => '小伙儿'])->one(); //此方法返回 ['name' => '小伙儿'] 的一条数据; User::find()->where(['name' => '小伙儿'])->all(); //此方法返回 ['name' => '小伙儿'] 的所有数据; User::find()->orderBy('id DESC')->all(); //此方法是排序查询; User::findBySql('SELECT * FROM user')->all(); //此方法是用 sql 语句查询 user 表里面的所有数据; User::findBySql('SELECT * FROM user')->one(); //此方法是用 sql 语句查询 user 表里面的一条数据; User::find()->andWhere(['sex' => '男', 'age' => '24'])->count('id'); //统计符合条件的总条数; User::find()->andFilterWhere(['like', 'name', '小伙儿']); //此方法是用 like 查询 name 等于 小伙儿的 数据 User::find()->one(); //此方法返回一条数据; User::find()->all(); //此方法返回所有数据; User::find()->count(); //此方法返回记录的数量; User::find()->average(); //此方法返回指定列的平均值; User::find()->min(); //此方法返回指定列的最小值 ; User::find()->max(); //此方法返回指定列的最大值 ; User::find()->scalar(); //此方法返回值的第一行第一列的查询结果; User::find()->column(); //此方法返回查询结果中的第一列的值; User::find()->exists(); //此方法返回一个值指示是否包含查询结果的数据行; User::find()->batch(10); // 每次取 10 条数据 User::find()->each(10); //每次取 10 条数据, 迭代查询
2. 直接查询
createCommand(执行原生的SQL语句) :
$sql= "SELECT u.account,i.* FROM sys_user as u left join user_info as i on u.id=i.user_id";
$rows=Yii::$app->db->createCommand($sql)->query();
查询返回多行:
$command = Yii::$app->db->createCommand('SELECT * FROM post');
$posts = $command->queryAll();
返回单行:
$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1');
$post = $command->queryOne();
查询多行单值:
$command = Yii::$app->db->createCommand('SELECT title FROM post');
$titles = $command->queryColumn();
查询标量值/计算值:
$command = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post');
$postCount = $command->queryScalar();
3. 参数绑定
$post = 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)
4. 执行非查询语句
// INSERT (table name, column values) Yii::$app->db->createCommand()->insert('user', [ 'name' => 'Sam', 'age' => 30, ])->execute(); // UPDATE (table name, column values, condition) Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute(); // DELETE (table name, condition) Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute(); // table name, column names, column values Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [ ['Tom', 30], ['Jane', 20], ['Linda', 25], ])->execute();
5. 执行事务
$db = Yii::$app->db; $transaction = $db->beginTransaction(); try { $db->createCommand($sql1)->execute(); $db->createCommand($sql2)->execute(); // ... executing other SQL statements ... $transaction->commit(); } catch(\Exception $e) { $transaction->rollBack(); throw $e; }
6. 关联查询
/** *客户表Model:CustomerModel *订单表Model:OrdersModel *国家表Model:CountrysModel *首先要建立表与表之间的关系 *在CustomerModel中添加与订单的关系 */ Class CustomerModel extends \yii\db\ActiveRecord { ... //客户和订单是一对多的关系所以用hasMany //此处OrdersModel在CustomerModel顶部别忘了加对应的命名空间 //id对应的是OrdersModel的id字段,order_id对应CustomerModel的order_id字段 public function getOrders() { return $this->hasMany(OrdersModel::className(), ['id'=>'order_id']); } //客户和国家是一对一的关系所以用hasOne public function getCountry() { return $this->hasOne(CountrysModel::className(), ['id'=>'Country_id']); } .... } // 查询客户与他们的订单和国家 CustomerModel::find()->with('orders', 'country')->all(); // 查询客户与他们的订单和订单的发货地址(注:orders 与 address都是关联关系) CustomerModel::find()->with('orders.address')->all(); // 查询客户与他们的国家和状态为1的订单 CustomerModel::find()->with([ 'orders' => function ($query) { $query->andWhere('status = 1'); }, 'country', ])->all();
原文地址:https://www.cnblogs.com/meetuj/p/14694857.html
转载请注明: ITTXX.CN--分享互联网 » Yii2 数据库查询汇总
最后更新:2023-05-04 06:25:28