yii2增删改查(model、db、Query三种方式) + mysql主从配置

!!!https://getyii.com/topic/219
==================
ExampleModel::findOne和findAll无法直接在后面->asArray()，因为这个方法已经写死了
find()->asArray()->all()/one()可以

public static function findOne($condition)
{
    return static::findByCondition($condition)->one();
}

public static function findAll($condition)
{
    return static::findByCondition($condition)->all();
}

public static function find()
{
    return Yii::createObject(ActiveQuery::className(), [get_called_class()]);
}
===================
!!!https://blog.csdn.net/woshihaiyong168/article/details/53044322
!!https://www.yiiframework.com/doc/guide/2.0/zh-cn/db-query-builder 

http://www.yiichina.com/tutorial/996
http://www.yiichina.com/tutorial/834
https://www.jianshu.com/p/e89e9580fc67

------------------------------------------
//mysql主从配置：config/db.php
return 
[
    'class' => 'yii\db\Connection',

    // master 
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',

    // slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]
------------------------------------------
//简单crud
$db=Yii::$app->db->createCommand(); 
$db->insert()->execute();
$db->batchInsert()->execute();
$db->delete()->execute();
$db->update()->execute();
//直接执行语句
Yii::$app->db->createCommand($sql)->queryAll();
//事务类提交
$transaction1 = $connection->beginTransaction();
try {
    $connection->createCommand($sql1)->execute();

    // internal
    $transaction2 = $connection->beginTransaction();
    try {
        $connection->createCommand($sql2)->execute();
        $transaction2->commit();
    } catch (Exception $e) {
        $transaction2->rollBack();
    }

    $transaction1->commit();
} catch (Exception $e) {
    $transaction1->rollBack();
}
------------------------------------------

(new \yii\db\Query())->find()
$StudentModel->find()
Student::find()
select()->all()/one()/each(100);
------------------------------------------

增
//普通插入
$user= new User;         
$user->username =$username;  
$user->password =$password;  
$user->save()

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

Yii::$app->db->createCommand()->batchInsert(UserModel::tableName(), ['user_id','username'], [
    ['1','test1'],
    ['2','test2'],
    ['3','test3'],   
])->execute();

删
User::findOne($id)->delete();
//单个/批量 删除
Customer::deleteAll(['status' => 1], 'type = :type',[':type'=>1]);
$user = User::find()->where(['name' => 'test'])->one()/all()->delete();

Yii::$app->db->createCommand()
->delete('{{%user}}', 'user_id=:user_id', [':user_id' => $this->id])
   ->execute();

Yii::$app->db->createCommand()
->delete('{{%user}}', "user_id in($userIds) AND parent_id in($parentIds)")->execute();

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

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

Yii::$app->db->createCommand()
->delete('{{%user}}', 'user_id=:user_id', [':user_id' => $this->id])->execute();

Yii::$app->db->createCommand()
->delete('{{%user}}', "user_id in($userIds) AND parent_id in($parentIds)")->execute();

改
$user = User::find()->where(['name'=>'test'])->one();
$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();

//runValidation boolen 是否通过validate()校验字段 默认为true。attributeNames array 需要更新的字段 
$model->update($runValidation , $attributeNames);  

Customer::updateAll(['status' => 1], 'status = 2'); 

Customer::updateAll(['status' => 1], ['status'=> '2','uid'=>'1']);

// 修改username
$user = User::findOne(1);
$user->username = test;
$user->save()/update();

// 单个/批量更新  team_num累加1
UserStats::updateAll(['team_num' => new Expression("team_num + 1")], ['in', 'user_id', $parentIds]);
//单个/批量更新
Customer::updateAll(['status' => 1], 'type = :type',[':type'=$type]);

// grade3 累加1
$db->createCommand()->update('yii_users', [
    'grade3' => $grade3 + 1
], 'user_id=:id', [':id' => $parentId])->execute();

查
http://www.kuitao8.com/20141104/3214.shtml
$db = Yii::app()->db;
$db->createCommand()->update('{{online}}',['addtime'=>$time,],'ip=:ip',[':ip'=>$ip])->getRawSql();
$db->createCommand()->update('{{online}}',['addtime'=>$time,],'ip=:ip',[':ip'=>$ip])->execute();
$db->createCommand()->update('table', ['field'=>':valuefield'], 'id_table=:id_table', [':id_table'=>$id_table, ':valuefield'=>$valuefield])->getRawSql();
$db->createCommand()->update('table', ['field'=>':valuefield'], 'id_table=:id_table', [':id_table'=>$id_table, ':valuefield'=>$valuefield])->execute();

简单查询
one/all/count/sum/average/min/max/scalar/column/exists/where/with/indexBy/asArray
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()->asArray()->one();    以数组形式返回一条数据；
Customer::find()->asArray()->all();    以数组形式返回所有数据；
Customer::find()->where($condition)->asArray()->one();    根据条件以数组形式返回一条数据；
Customer::find()->where($condition)->asArray()->all();    根据条件以数组形式返回所有数据；
Customer::find()->where($condition)->asArray()->orderBy('id DESC')->all();    根据条件以数组形式返回所有数据,并根据ID倒序；
$customers = Customer::findAll(10);
$customer = Customer::findOne(10);
$customers = Customer::find()->where(['id' => 10])->all()/one();

$customers = Customer::findAll([10, 11, 12]);
$customers = Customer::find()->where(['IN','id',[10,11,12]])->all();
$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();

$customers = Customer::findAll(['age' => 30, 'status' => 1]);
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();
$customers = Customer::find()->where('age=:age AND status=:status', [':age'=>30, ':status'=>1])->all();
$customers = Customer::find()->where('age=:age AND status=:status')->addParams([':age'=>30,':status'=>1])->all();

$customers = Customer::find()->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();

$customers = Customer::find()->select('name, sex')->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();
$customers = Customer::find()->select(['name', 'sex'])->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();
$customers = Customer::find()->select(['xingming'=>'name', 'sex'])->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();
$customers = Customer::find()->select(['concat(firtname,'',lastname) as fullname', 'sex'])->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();

//根据条件增加查询语句
$customerModel = new Customer();
$query = $customerModel->find()->select('id, name')->where(['status'=> 1]);
if (!empty($type)) $query->andWhere(['type' => $type]);
$query->asArray()->all();

// 返回 [100 => ['id' => 100, 'age' => '...', 'status' => ...], 101 => [...], 103 => [...], ...]
$customers = Customer::find()->indexBy('id')->where(['age' => 30, 'status' => 1])->all();

// 根据sql来查询：findBySql
$customers = Customer::findBySql('SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();

$count = Customer::find()->where(['age' => 30, 'status' => 1])->count();

关联查询
hasOne/hasMany：返回对应关系的1条/多条记录
====================================================
如下链接的文章讲得非常透彻，
https://www.cnblogs.com/yiifans/p/3786374.html 
我觉得a->hasOne(b,['b_id'=>'a_id'])只能查到b表的字段，没法查找a表的字段
就算joinWith可以查到a表和b表，但是查到的a表字段显示方式也很烦人，见下面例子,总得来说，多表联查还是只有Query方式靠谱：
class ItHelperEmailContent extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'it_helper_email_content';
    }

    public function getItHelper()
    {
        return $this->hasOne(ItHelper::className(), ['id' => 'email_id']);
    }
}

class ItHelper extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'it_helper';
    }
}

class test extends \yii\web\Controller
public function actionTest()
{
    $re = ItHelperEmailContent::find()->joinWith('itHelper')->asArray()->all();
    var_dump($re);die;
}