1. 概述

操作数据库有2种方式：

• DAO(data access object)，不安全
• ORM(onject relational mapping)

2. DAO

Yii::app()->db->createCommand()->select()->from()->where()->andwhere()->orwhere->filterWhere()->innerjoin()->groupBy()->having();

// filterWhere 会过滤空值

Yii::app()->db->createCommand()->select()->from()->where()->andwhere()->orwhere->filterWhere()->innerjoin()->count();

Yii::app()->db->createCommand()->select()->from()->where()->andwhere()->orwhere->filterWhere()->innerjoin()->sum();

Yii::app()->db->createCommand()->select()->from()->where()->andwhere()->orwhere->filterWhere()->innerjoin()->min();

Yii::app()->db->createCommand()->select()->from()->where()->andwhere()->orwhere->filterWhere()->innerjoin()->max();

Yii::app()->db->createCommand()->select()->from()->where()->queryScalar();

Yii::app()->db->createCommand()->select()->from()->where()->queryColumn();

Yii::app()->db->createCommand()->select()->from()->where()->queryAll();

Yii::app()->db->createCommand()->select()->from()->where()->queryOne();

Yii::app()->db->createCommand()->select()->from()->where()->queryRow();

如何打印SQL:

$command = Yii::app()->db->createCommand()->from()->where();

echo $command->sql;

echo '<br/>';

var_export($command->params);

3.ORM

$obj = new Object()

$obj->save();

Object::find()->all();

Object::findOne()

;

// where的写法有三种：字符串格式、操作符格式、哈希格式（如果value可以是array）

Object::find()->select()->where()->andFilterWhere()->orderBy()->all();

Object::find()->select()->where()->andFilterWhere()->orderBy()->one();

Object::find()->select()->where()->andFilterWhere()->orderBy()->column(); // 此方法返回查询结果中的第一列的值；

Object::find()->select()->where()->andFilterWhere()->orderBy()->scalar(); // 此方法返回值的第一行第一列的查询结果；

Object::find()->select()->where()->andFilterWhere()->orderBy()->exists();  // 此方法返回一个值指示是否包含查询结果的数据行；

Object::find()->select()->where()->andFilterWhere()->orderBy()->each();    // 每次取 10 条数据

Object::find()->select()->where()->andFilterWhere()->orderBy()->batch();   // 每次取 10 条数据， 迭代查询

Object::find()->select()->where()->andFilterWhere()->orderBy()->batch();  // 此方法返回一个值指示是否包含查询结果的数据行；

Object::findBySql($sql)->all()->asArray();  // 不加asArray返回的是一个对象。

当你调用 yii\db\Query当中的一个查询方法的时候，实际上内在的运作机制如下：

• 在当前 yii\db\Query 的构造基础之上，调用 yii\db\QueryBuilder 来生成一条 SQL 语句；
• 利用生成的 SQL 语句创建一个 yii\db\Command 对象；
• 调用 yii\db\Command 的查询方法（例如，queryAll()）来执行这条 SQL 语句，并检索数据。

如何打印SQL:

$command = Object::find()->where()->createCommand();

echo $command->sql;

echo '<br/>';

var_export($command->params);

4.参考资料

• Yii DAO 数据库操作
• Yii2.0 数据库查询