欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。

文摘  Yii常用查询,sql查询笔记汇总

PHP框架 本站 567 0评论

常用查询:

TableClass::find()      ->返回yii\db\ActiveQuery Object

->select(string|array)  ->要查询的字段,如['id','name','age']

->where($condition(array|string), $params = [])      ->查询条件

->asArray()                ->返回数组

->groupBy(string|array)    ->分组eg:"id, name"  或  ["id, name")]

->distinct( true)  ->是否去重

->limit(int)       ->限制返回结果条数 eg:limit(3)      结果:limit 3

->offset(int)      ->跳过 x 条数据       eg:->offset(3) 结果:offset 3

->orderBy(string|array)    ->排序规则eg:id ASC, name DESC

                             或 ['id' => SORT_ASC, 'name' => SORT_DESC]

->scalar()        ->查询一个字段值,返回string

->one()           ->查询一条,返回array

->all()           ->查询所有,返回array

->count()         ->查询结果的条数,返回string|null|false|0

->sum(string);    ->求和,eg:->sum("(local_num+taobao_num)");


1.查询单条数据,返回类实例:


    ProductMovie::findOne(['id'=>'10']);       //返回ProductMovie Object

    ProductMovie::findOne(10);     //默认where条件是  主键=>10

    同

    ProductMovie::find()->where(['id'=>'10'])->one();   //返回ProductMovie Object


2.查询数据,返回数组:


//asArray是yii\db\ActiveQueryTrait中的方法

//需要在DicineProductRecord 中 use yii\db\ActiveQueryTrait

Product::find()->where(['id'=>'1'])->asArray()->one();  //返回一维数组,单条数据

Product::find()->where(['id'=>'1'])->asArray()->all();  //返回二维数组,多条数据


3.查询单个字段值


$res = DicineProductRecord::find()->select(['name'])->where(['id'=>8])->scalar();

$res返回值:string    蛋糕99


sql练习:

//use yii\db\ActiveQueryTrait


$query = Product::find();

$res   = $query->where

([

    'OR',

    ['AND','is_valid =1','id<3'],

    ['AND','is_deleted =0','name="蛋糕99"'],

    ['between','id',9,11],

])

->andFilterWhere([ 'like', 'product_name', $nameVal, false, ])   // andFilterWhere(array)   如果你不确定$nameVal是否有值,可以这样写,如果$nameVal 没值,函数不会将字段product_name查询条件添加到sql语句的where条件中,这在筛选查询时非常有用

->asArray()

->select(['name'])

->limit(10)

->offset(1)

->orderBy('id DESC')

->distinct()

->all();

echo $query->createCommand()->getRawSql();   //打印sql语句


生成的sql:

SELECT DISTINCT `name` FROM `db_product` WHERE ((is_valid =1) AND (id<3)) OR ((is_deleted =0) AND (name="蛋糕99")) OR (`id` BETWEEN 9 AND 11) ORDER BY `id` DESC LIMIT 10 OFFSET 1


按固定顺序排序:

$query = Product::find();

$res =$query->where(

['id'=>[8,4,5,1,7]

])->asArray()

->orderBy(new Expression("FIELD(id,8,4,5,1,7)"))    //按照id  8,4,5,1,7排序,field(str,str1,str2,str3)是mysql自定义的函数,SELECT FIELD("c", "a", "b", "c", "d", "e"); 返回字符串 c 在列表值中的位置->3

->select(['id','name'])

->limit(10)

->all();


//type=3的排在最前边,type=1的排在第二,同时按id倒序

->orderBy(new Expression("case when type=3 then 1 when type=1 then 2  else 3 end,id desc"))


以下我们介绍where()方法当中,条件的拼装方式。

 

#某个值为null,会用IS NULL来生成语句:

['type' => 1, 'status' => 2]            // 生成:(type = 1) AND (status = 2)

['id' => [1, 2, 3], 'status' => 2]      // 生成:(id IN (1, 2, 3)) AND (status = 2)

['status' => null]                      // 生成:status IS NULL

['NOT', ['type' => null]] // 生成:type IS NOT NULL

 

#对比

['>', 'id', 1]                                        // 生成:id > 1

['<', 'id', 100]                                      // 生成:id < 100

['=', 'id', 10]                                       // 生成:id = 10

['>=', 'id', 1]                                       // 生成:id >= 1

['<=', 'id', 100]                                     // 生成:id <= 100

['!=', 'id', 10]                                      // 生成:id != 10

 

['and', 'id' => 1, 'id' => 2]                        // 生成:id=1 AND id=2

['and', 'id=1', 'id=2']                              // 生成:id=1 AND id=2

['and', 'type=1', ['or', 'id=1', 'id=2']]            // 生成:type=1 AND (id=1 OR id=2)

 

['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]]   // 生成:(type IN (7, 8, 9) OR (id IN (1, 2, 3)))

 

['not', ['attribute' => null]]                       // 生成:NOT (attribute IS NULL)

 

['between', 'id', 1, 10]                             // 生成:id BETWEEN 1 AND 10

['not between', 'id', 1, 10]                         // 生成:id NOT BETWEEN 1 AND 10

 

['in', 'id', [1, 2, 3]]                               // 生成:id IN (1, 2, 3)

['id' => [4, 8, 15]]   // 生成:id IN (4, 8, 15)

['not in', 'id', [1, 2, 3]]                           // 生成:id NOT IN (1, 2, 3)

 

['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]  // 生成:(`id`, `name`) IN ((1, 'foo'), (2, 'bar'))

 

#用子查询作为IN条件的值,如下:

['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]

 

['like', 'name', 'tester']                             // 生成:name LIKE '%tester%'

['like', 'name', ['test', 'sample']]                   // 生成:name LIKE '%test%' AND name LIKE '%sample%'

['like', 'name', '%tester', false]                     // 生成:name LIKE '%tester',false表示自定义like条件,加上false后,程序不会在数组的第三个参数tester两侧增加'%'

                                                       // 这是自定义查询方式,要传入值为false的运算数3,并且自行添加%

['or like', 'name', ['test', 'sample']]                 // 生成:name LIKE '%test%' OR name LIKE '%sample%'

['not like', 'name', 'tester']                          // 生成:name NOT LIKE '%tester%'

['or not like', 'name', ['test', 'sample']]             // 生成:name NOT LIKE '%test%' OR name NOT LIKE '%sample%'

 

['exists', (new Query())->select('id')->from('users')->where(['active' => 1])] // 生成:EXISTS (SELECT "id" FROM "users" WHERE "active"=1)


5.插入数据:


插入多条数据:

     \Yii::$app->db->createCommand()->batchInsert(Product::tableName(), ['name', 'age'], [

          ['Tom', 30],

          ['Jane', 20],

          ['Linda', 25],

     ])->execute();

插入一条:

    $product = new Product();

    $product->setAttributes($common);

    $product->save();


6.更新数据表:


1.更新数值:在原来基础上增加+或减少-

   ActiveRecord::updateAllCounters($counters, $condition = '', $params = []);

    

   //updateAllCounters更新符合 $condition条件的所有数据

   $res =Product::updateAllCounters(['sale_num' => -3],['>','id' , 743]);

   

   //updateCounters只更新一条

   $res =Product::findOne(['id' => 747])->updateCounters(['sale_num' => -3]);

   或

   $res = Product::find()->where(['id' => '747'])->one()->updateCounters(['sale_num' => -3]);

2.更新其他:

      第一种:

        $model = Product::findOne(['id' => 8]);

        $model->setAttributes(['name' => '蛋糕']);

        $res = $model->save();

      第二种:function updateAll($attributes, $condition = '', $params = []){...}

    ProductRecord::updateAll(

           ['name'=>'花雕1'],

           ['<','id','3']

    );



7.删除数据:


 //物理删除

 Product::findOne(['id' => 8])->delete();

 

 8.with关联查询:


在数据表类ProductClass中设置好关联关系,比如关联关系为category

在ProductClass中定义方法getCategory()方法

eg:


    public function getCategory()

    {

        return $this->hasOne(ProductCategory::class, [

            'id' => 'category_id',

        ])->select(['id','name']);

    }


查询:

$condition = 0;

$query = Product::find();

$res   = $query

        ->select('category_id')

        ->where(['<','id','6' ])

        ->with([

            'category' => function ($query) use($condition) {

                $query->where(['=','is_deleted',$condition]);

            }

        ])      //关联表设置条件is_deleted = 0

        ->asArray()

        ->all();


或 ->with( 'category' )->with('supply')    //关联表不设置查询条件,需定义getSupply方法

或 ->with([

            'category' => function ($query) {

                $query->where(['=','is_deleted',0]);

            }

        ])

返回结果:

Array(

    [0] => Array

        (

            [category_id] => 1

            [category] => 

        )


    [1] => Array

        (

            [category_id] => 4

            [category] => Array

                (

                    [id] => 4

                    [name] => 食品

                )


        )


)   


//搜索

$connection = Yii::$app->db;

$user_id = Yii::$app->user->id;

//print_r($user_id);


/*

 * 报名的赛事

 */

$sql = "select ybm from bm_userybm where user_id=$user_id";

$command = $connection->createCommand($sql);

$result = $command->queryAll();

/*

 * 获奖的赛事

 */

$sql_yhj = "select yhj from bm_useryhj where user_id=$user_id";

$command_yhj = $connection->createCommand($sql_yhj);

$result_yhj = $command_yhj->queryAll();

//print_r($result);

//echo "<br/>";

//添加


$sql = ” INSERT INTO `tbl_user` (`username`, `password`, `email`) VALUES (‘test’, ‘test’, ‘test@test.com’) “;

$command=$connection->createCommand($sql);

print_r($command->execute());


//添加 返回自增id

$command1 = $connection->createCommand(“SELECT last_insert_id()”);

$result = $command1->queryAll();


//常用函数


1)如果你执行的SQL语句有返回结果集: 例如SELECT。通常用query开头的系列函数:

$dataReader=$command->query(); // 执行一个 SQL 查询

$rows=$command->queryAll(); // 查询并返回结果中的所有行

$row=$command->queryRow(); // 查询并返回结果中的第一行

$column=$command->queryColumn(); // 查询并返回结果中的第一列

$value=$command->queryScalar(); // 查询并返回结果中第一行的第一个字


2)你执行的SQL语句返回的不是结果集,只是状态值,例如:INSERT ,UPDATE,DELETE.则用execute()

$this->command->execute();

//使用事务的一种常见情形:CDbTransaction

$transaction = $connection->beginTransaction();

try{

$connection->createCommand($sql1)->execute();

$connection->createCommand($sql2)->execute();

………

$transaction->commit();

}

catch(Exception $e){     // 如果有一条查询失败,则会抛出异常

$transaction->rollBack();

}


转载请注明: ITTXX.CN--分享互联网 » Yii常用查询,sql查询笔记汇总

最后更新:2021-08-12 14:27:48

赞 (6) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽