yii2.0也使用了一段时间了,对数据库的一些简单操作基本都用到了,总结如下:
Table::find() -> where(['name' => 'chengcheng']) -> one();
Table::find() -> where(['name' => 'chengcheng']) ->all();
Table::find() -> where(['name' => 'chengcheng']) -> andWhere(['sex' => 'male']) -> all();
等价于:
Table::find() -> where(['name' => 'chengcheng', 'sex' => 'male']) -> all();
Table::find() -> andWhere(['between', 'id', 1, 100]) -> all();
注:这里两边是包括边界值的
Table::find() -> andWhere(['and', 'id = 1', 'id = 2']) -> all();
Table::find() -> andWhere(['or', 'id = 1', 'id = 2']) -> all();
Table::find() -> andWhere(['in', 'id', [1,2,3,4]]) -> all();
Table::find() -> andWhere(['like', 'name', 'cheng']) -> all();
Table::find() -> andWhere(['like', 'name', ['cheng', 'ma']]) -> all();
Table::find() -> andWhere(['like', 'name', '%cheng', false]) -> all();
Table::find() -> andWhere(['>', 'id', 10]) -> all();
$query->orderBy([
'id' => SORT_ASC, 升序 默认
'name' => SORT_DESC, 降序
]);
$query->groupBy(['id', 'name']);
$query->having(['id' => 1]);
$query->limit(10)->offset(20);
Table::findBySql('SELECT * FROM table')->one();
或者:
Yii::$app->db->createCommand('SELECT * FROM table')->queryOne();
Table::findBySql('SELECT * FROM table')->all();
或者:
Yii::$app->db->createCommand('SELECT * FROM table')->queryAll();
注:引用变量时不要直接写到sql语句中
错误实例:
Yii::$app->db->createCommand("SELECT * FROM table WHERE id=$id")->queryOne();
上面这条sql不安全,如果别人攻击你服务器,$id传入一个delete sql语句很有可能会把你的数据表删除掉
正确示例:
Yii::$app->db->createCommand("SELECT * FROM table WHERE id=:id", [':id' => $id])->queryOne();
以上罗列了常用的sql操作方法,你既可以通过yii框架方法实现,也可以执行原生sql。