yii2 数据库的相关操作的代码示例

基础查询

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();

条件查询

$customers = Customer::find()->where($cond)->all(); 

$cond就是我们所谓的条件，条件的写法也根据查询数据的不同存在差异，那么如何用yii2的方式来写查询条件呢？

[[简单条件]]


// SQL: (type = 1) AND (status = 2).
$cond = ['type' => 1, 'status' => 2] 

// SQL:(id IN (1, 2, 3)) AND (status = 2)
$cond = ['id' => [1, 2, 3], 'status' => 2] 

//SQL:status IS NULL
$cond = ['status' => null]
[[and]]:将不同的条件组合在一起，用法举例：

//SQL:`id=1 AND id=2`
$cond = ['and', 'id=1', 'id=2']

//SQL:`type=1 AND (id=1 OR id=2)`
$cond = ['and', 'type=1', ['or', 'id=1', 'id=2']]

//SQL:`type=1 AND (id=1 OR id=2)` //此写法'='可以换成其他操作符，例：in like != >=等
$cond = [
    'and',
    ['=', 'type', 1],
    [
        'or',
        ['=', 'id', '1'],
        ['=', 'id', '2'],
    ]
]
[[or]]:

//SQL:`(type IN (7, 8, 9) OR (id IN (1, 2, 3)))`
$cond = ['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]
[[not]]:


//SQL:`NOT (attribute IS NULL)`
$cond = ['not', ['attribute' => null]]
[[between]]: not between 用法相同


//SQL:`id BETWEEN 1 AND 10`
$cond = ['between', 'id', 1, 10]
[[in]]: not in 用法类似


//SQL:`id IN (1, 2, 3)`
$cond = ['in', 'id', [1, 2, 3]] or $cond = ['id'=>[1, 2, 3]]

//IN条件也适用于多字段
$cond = ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]

//也适用于内嵌sql语句
$cond = ['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]
[[like]]:


//SQL:`name LIKE '%tester%'`
$cond = ['like', 'name', 'tester']

//SQL:`name LIKE '%test%' AND name LIKE '%sample%'`
$cond = ['like', 'name', ['test', 'sample']]

//SQL:`name LIKE '%tester'`
$cond = ['like', 'name', '%tester', false]
[[exists]]: not exists用法类似

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

此外，您可以指定任意运算符如下
//SQL:`id >= 10`
$cond = ['>=', 'id', 10]

//SQL:`id != 10`
$cond = ['!=', 'id', 10]

新增

使用model::save()操作进行新增数据

$user= new User;         
$user->username =$username;  
$user->password =$password;  
$user->save()
使用createCommand()进行新增数据

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

批量插入数据

Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [  
    ['test01', 30],  
    ['test02', 20],  
    ['test03', 25],  
])->execute();

修改

使用model::save()进行修改

$user = User::find()->where(['name'=>'test'])->one(); //获取name等于test的模型
$user->age = 40; //修改age属性值
$user->save();   //保存

直接修改:修改用户test的年龄为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(['name'=>'test'])->one(); 
$user->delete();

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

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

根据主键删除:删除主键值为1的用户

$result = User::deleteByPk(1);

使用createCommand()删除

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