reat any tro getc address func row count() eat
基础查询
- Customer::find()->one(); 此方法返回一条数据;
- Customer::find()->all(); 此方法返回所有数据;
- Customer::find()->count(); 此方法返回记录的数量;
- Customer::find()->average(); 此方法返回指定列的平均值;
- Customer::find()->min(); 此方法返回指定列的最小值 ;
- Customer::find()->max(); 此方法返回指定列的最大值 ;
- Customer::find()->scalar(); 此方法返回值的第一行第一列的查询结果;
- Customer::find()->column(); 此方法返回查询结果中的第一列的值;
- Customer::find()->exists(); 此方法返回一个值指示是否包含查询结果的数据行;
- Customer::find()->batch(10); 每次取10条数据
- Customer::find()->each(10); 每次取10条数据,迭代查询
- //根据sql语句查询:查询name=test的客户
- Customer::model()->findAllBySql("select * from customer where name = test");
- //根据主键查询:查询主键值为1的数据
- Customer::model()->findByPk(1);
- //根据条件查询(该方法是根据条件查询一个集合,可以是多个条件,把条件放到数组里面)
- Customer::model()->findAllByAttributes([‘username‘=>‘admin‘]);
- //子查询
- $subQuery = (new Query())->select(‘COUNT(*)‘)->from(‘customer‘);
- // SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`
- $query = (new Query())->select([‘id‘, ‘count‘ => $subQuery])->from(‘customer‘);
- //关联查询:查询客户表(customer)关联订单表(orders),条件是status=1,客户id为1,从查询结果的第5条开始,查询10条数据
- $data = (new Query())
- ->select(‘*‘)
- ->from(‘customer‘)
- ->join(‘LEFT JOIN‘,‘orders‘,‘customer.id = orders.customer_id‘)
- ->where([‘status‘=>‘1‘,‘customer.id‘=>‘1‘])
- ->offset(5)
- ->limit(10)
- ->all()
直接查询
- //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();
关联查询
- /**
- *客户表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();
yii2查询汇总
reat any tro getc address func row count() eat
原文:http://www.cnblogs.com/yszr/p/7896881.html
来源: http://www.bubuko.com/infodetail-2407050.html