事务
1、开启事务
//注意 引入 Yii (use Yii;)
$transaction = Yii::$app->db->beginTransaction();
2、回滚
$transaction->rollBack();
3、提交
$transaction->commit();
增加
//方法1
//实例
$model = new User();
//数据(注意结构)
$data['User']['name'] = '张三';
$data['User']['sex'] = '男';
$data['User']['age'] = 18;
if($model->load($data) && $model ->save()){
//插入成功,返回id
return $model->id;
}
//插入错误
return $model->getErrors();//返回错误(一般返回值为models里面的规则验证)
批量插入:
Yii::$app->db->createCommand()->batchInsert(UserModel::tableName(), ['user_id','username'], [
['1','test1'],
['2','test2'],
['3','test3'],
])->execute();
删除
方法1:
$model = User::findOne($id);
$model->delete();
方法2:
User::deleteAll($where);//注意$where的判断,避免删库跑路
更新
1、某字段加减 $num ( $num 可为负数 负数为 减$num)
$model::updateAllCounters(['count' => $num], ['id' => $id] ]);
2、更新数据
方法1 :
$model::updateAll($set, $where);
方法2 :
//实例(注意传$id,否则会变插入)
$model = User::findOne($id);
//注意数据结构
$data['User']['name'] = '张三';
$data['User']['sex'] = '男';
//save()会验证模型中的规则
if( $model ->load($data) && $model->save() ){
return true;
}
//返回错误信息
return $model->getErrors();
查询
1、基本的查询操作
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 条数据, 迭代查询
2、连表查询(使用hasOne()以及hasMany() )
//+---------------------------
//| models目录下 Order.php
//+---------------------------
/**
* @return \yii\db\ActiveQuery
*/
public function getUser()
{
return $this->hasOne(User::className(), ['id' => 'user_id'])->select(['id','name','avatar']);
}
/**
* @return 获取商品数据
*/
public function getGoods()
{
return $this->hasMany(OrderGoods::className(), ['order_id' => 'id']);
}
//使用方式一、
$joinWith = [
'user as u' => function($res){
$res->where($where);//在user补充条件
},
'goods as g' => function($res){
$res->select('id,name');//输出指定字段
}
];
Order::find()->alias('o')->joinWith($joinWith)->asArray()->all();
//注意点
使用joinWith时,要有alias();
asArray()//输出数组
//使用方式二、
$with = [
'user' => function($res){
$res->where($where);//在user补充条件
},
'goods' => function($res){
$res->select('id,name');//输出指定字段
}
];
Order::find()->with($with)->asArray()->all();
总结:方式一与方式二输出的结果一样,但是查询的sql语句不一样,在尝试中了解他们的差异吧!
限制查询:
// WHERE admin_id >= 10 LIMIT 0,10
User::find()->select('*')->where(['>=', 'admin_id', 10])->offset(0)->limit(10)->all()
条件
$customers = Customer::find()->where($cond)->all();
$cond写法举例:
// 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']]
[[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]]
//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]
参考地址:https://blog.csdn.net/guacuo1042/article/details/78802218