yii2.0总结笔记2:数据库操作

最新推荐文章于 2020-06-30 11:47:16 发布

原创最新推荐文章于 2020-06-30 11:47:16 发布 · 247 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#yii2数据库操作

yii2.0 专栏收录该内容

8 篇文章

订阅专栏

欢迎加入laravel技术交流群 ：784030154
<?php  User::find()->one();//返回单行数据

User::find()->all();//返回多行数据

User::find()->count();//统计行数

User::find()->average();//统计平均数

User::find()->max();

User::find()->scalar();

User::find()->column();

User::find()->exists();

User::find()->batch(10);

User::find()->each();

User::find()->findByPk(1);

User::find()->findAllBySql('select * from user');

$data = (new Query)

        ->select('*')//查询什么字段

        ->from('user')//查询什么表

        ->join('LEFT JOIN','orders','user.id = order.user_id')//外连接

        ->where(['status'=>1,'user_id'=>1])//条件

        ->offset(5)//跳过几条

        ->limit(10)//查询几条

        ->all()

直接查询

$sql = 'select user_id,name,account from user where user_id>10';

$rows = Yii::$app->db->createCommand($sql)->query();//

$rows = Yii::$app->db->createCommand($sql)->queryAll();

$row  = Yii::$app->db->createCommand($sql)->queryOne();



User::find()->scalar();

User::find()->column();

User::find()->exists();

User::find()->batch(10);

User::find()->each();

User::find()->findByPk(1);

User::find()->findAllBySql('select * from user');

$data = (new Query)

        ->select('*')//查询什么字段

        ->from('user')//查询什么表

        ->join('LEFT JOIN','orders','user.id = order.user_id')//外连接

        ->where(['status'=>1,'user_id'=>1])//条件

        ->offset(5)//跳过几条

        ->limit(10)//查询几条

        ->all()

直接查询

$sql = 'select user_id,name,account from user where user_id>10';

$rows = Yii::$app->db->createCommand($sql)->query();//

$rows = Yii::$app->db->createCommand($sql)->queryAll();

$row  = Yii::$app->db->createCommand($sql)->queryOne();

关联查询

/**

*客户表，订单表，国家表

*user     order  country

*/

class UserModel extends yii\db\ActiveRecord{

    //客户与订单的关系是：一对多

    public function getOrder(){
        return  $this->hasMany(OrderModel::className(),['user_id'=>'id']);

//客户与国家的关系：一对一

    public function getCountry(){

return $this->hasOne(CountryModel::className(),['id'=>'country_id])

}

//查询客户他们订单与国家的所有信息

UserModel::find()->with('order','country')->all()

//查询客户与他们的订单和订单地址（注order与address都是关联关系）

  UserModel::find()->with('order.address')->all();UserModel::find()->with('order.address')->all();

增加

$user = new User;

$user->username = $username;

$user->password  = $password;

$user->save();

批量增加

Yii::$app->db->createCommand->batchInsert('user',[

    ['test1',30],

    ['test2',20],

    ['test3',15]

])->execute();

修改

$user = User::find()->where(['name'=>'test'])->one();

$user->age = 40;

$user->save();

直接修改:修改test用户的age为40

$result = User::model->updateAll(['age'=>40],['name'=>'test']);

使用createCommand修改

Yii::$app->db->createCommand()->update('user',['age'=>40],'name=test')->execute();

删除

使用model::delete()删除

$user = User::find()->where()->one();

$user ->delete();

直接删除：年龄为30的所有用户

$result = User::deleteAll(['age'=>'30']);

根据主键删除：

$result = USer::deleteByPk(1);

Yii::$app->db->createCommand()->delete('user','age'=>30)->execute();