YII 2   学习整理的一些笔记，一下是关于数据库操作的一下内容：

1.查询：SELECT

1)// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `people`   
 $subQuery = (new Query())->select('COUNT(*)')->from('user');    
 $query = (new Query())->select(['id', 'count' => $subQuery])->from('people');

// SELECT DISTINCT `user_id` ... 

 User::find()->select('user_id')->distinct();

2)findOne()和findAll():

//查询key值为3,4,5的用户

$model = User::findAll([3,4,5]);

$model = User::find()->where(['id'=>[3,4,5]])->all();

3）where()条件

// SQL:(id IN (3, 4, 5)) AND (type = 1)

$con = ['id'=>[3,4,5],'type'=>1];

//SQL:type IS NULL

$con = ['type' => null]

4)[and]

//SQL:`id=3 AND id=4`

$con = ['and','id=3','id=4'];

//SQL:`status=1 AND (id=3 OR id=4)`

$con = ['and','status=1',['or','id=3','id=4']]

5)[or]

//SQL:`(status IN (1, 2, 3) OR (id IN (3, 4, 5)))`

$con = ['or', ['status'=>'1,2,3']], ['id'=>[3,4,5]];

6)[not]

//SQL:`NOT (attribute IS NULL)`

$cond = ['not', ['attribute' => null]]

7)[between]

//SQL:`id BETWEEN 1 AND 5`

$con = ['between', 'id', 1, 5]

8)[in]

//IN条件也适用于多字段

$con = ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]

//也适用于内嵌sql语句

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

9)[like]

//SQL:`name LIKE '%tester%'`

$con = ['like', 'name', 'tester']

//SQL:`name LIKE '%test%' AND name LIKE '%sample%'`

$con = ['like', 'name', ['test', 'sample']]

//SQL:`name LIKE '%tester'`

$con = ['like', 'name', '%tester', false]

2.删除：DELETE

$model = user::findOne($id);
$model->delete();

$model->deleteAll(['id'=>1]);

3.插入：INSERT

1)批量插入：

Yii:$app->db->createCommand()->batchInsert(UserModel::tabelName(), ['uid,'username'], [

['1','test1'],

['2','test2'],

])->execute();

//执行SQL

$query = UserModel::find()->where(['status'=>1]);

echo $query->createCommand()->getRawSql();