Yii2数据库CURD汇总最新

基础查询

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 条数据, 迭代查询

//根据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();

其他查询方法

//查询一条
\Yii::$app->db->createCommand('SELECT * FROM test')->queryOne();

//查询多条
\Yii::$app->db->createCommand('SELECT * FROM test')->queryAll();

//绑定单个防SQL注入参数
\Yii::$app->db->createCommand('SELECT * FROM test WHERE id=:id')->bindValue(':id',5)->queryOne();

//绑定多个防SQL注入参数
\Yii::$app->db->createCommand('SELECT * FROM test WHERE id=:id AND name=:name')->bindValues([':id'=>3,':name'=>'Tom'])->queryOne();

//统计查询
\Yii::$app->db->createCommand('SELECT COUNT("id") FROM test')->queryScalar();


//查询一条
$row = (new \yii\db\Query())
    ->select('*')
    ->from('test')
    ->where('id=:id', [':id'=>6])
    ->one();

//查询多条
$rows = (new \yii\db\Query())
    ->select('id,name')
    ->from('test')
    ->where(['>','id', 2])
    ->andWhere('class=:class', [':class'=>'A'])
    ->all();

//统计查询
$count = (new \yii\db\Query())
    ->select('count(id)')
    ->from('test')
    ->where('class=:class', [':class'=>'B'])
    ->count();

//关联查询
$join = (new \yii\db\Query())
    ->from('student s')
    ->select('s.*,t.id t_id,t.name t_name')
    ->leftJoin('teacher t','s.class = t.class')
    ->where('t.id=:t_id', [':t_id' => '2'])
    ->offset(3)
    ->limit(2)
    ->all();
     
//查询一条
$row = Test::find()->where('id=:id',[':id'=>5])->asArray()->one();

//查询多条
$rows = Test::find()->where('id>:id and class=:class',[':id'=>12,':class'=>'C'])->asArray()->all();

//统计查询
$count = Test::find()->count();

增加数据

//插入数据
\Yii::$app->db->createCommand()->insert('test',['name'=>'James','age'=>'22'])->execute();

//一次插入多行
\Yii::$app->db->createCommand()->batchInsert('test',['name','age'],[
        ['Hong','33'],
        ['Wei','44']
    ])->execute();

//对象方式---插入数据
$add = new Test();
$add->name = 'Xing';
$add->class = 'B';
$add->save();

修改数据

\Yii::$app->db->createCommand()->update('test',['name'=>'Li'],'id=:id and class=:class',[':id'=>1,':class'=>'E'])->execute();

//save()进行修改
$update = Test::find()->where('id=:id',[':id'=>6])->one();
$update->score = 100;//修改属性值
$update->save();

//方法updateAll()
$updateAll = Test::updateAll(['name'=>'Ping'],'id=:id',[':id'=>10]);
$updateAll = Test::updateAll(['is_del'=>1],['in','id',['101','102','103']]);

//对象方式---修改
$update = Test::findOne(['id'=>1]);
$update->is_del = 1;
$update->num += 1;  //自增1
$update->save();

删除数据

\Yii::$app->db->createCommand()->delete('test','id>:id',[':id'=>6])->execute();

//先查后删
$del = Test::find()->where('id=:id',[':id'=>6])->one();$del->delete();
//直接删除
Test::deleteAll('id>:id', [':id' => 29]);
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇