1.配置数据库
数据模型创建:
public function actionTest()
{
//查询数据
//$sql = 'select * from test where id = 1';
$sql = 'select * from test where id = :id';
// select * from test where id=1 or 1=1
// 防止 sql 注入
//$resutl = Test::findBySql($sql)->all();
//$resutl = Test::findBySql($sql, array(':id' => 1))->all();
//以数组方式表达
//$resutl = Test::find()->where(['id' => 1])->all();
//$resutl = Test::find()->where(['>', 'id', 0])->all();
//id >= 1 ,id <= 2
// $resutl = Test::find()->where(['between', 'id', 1,2])->all();
//title like "%title1%"
// $resutl = Test::find()->where(['like', 'title', 'title1'])->all();
// 把查询结果转化为数组,因为对象在内存占有量比数组高
$resutl = Test::find()->where(['like', 'title', 'title1'])->asArray()->all();
//批量查询, batch(n) 每次取数据库取多少
foreach (Test::find()->batch(1) as $key => $val) {
var_dump(count($val));
echo 'foreach<br/>';
}
echo '<br/>done';
// var_dump($resutl);
}
删除数据:
//删除数据
/*$result = Test::find()->where(['id' => 1])->all();
$a = $result[0]->delete();
var_dump($a);*/
//清楚所有
//Test::deleteAll();
// Test::deleteAll('id > 0');
Test::deleteAll('id > :id', array(':id' => 0));
添加数据:
//添加数据
$test = new Test();
$test->id = 4 ;
$test->title = 'tit4' ;
//调用验证器
$test->validate();
//判断是否有错误
if ($test->hasErrors()) {
echo 'cuo le';
die;
}
$test->save();
修改数据:
//修改数据
$result = Test::find()->where(['id' => 4])->one();
$result->title = 'edit3';
$result->save();
var_dump($result);
关联查询:
public function actionTest1()
{
//根据顾客查询他的订单信息
$customer = Customer::find()->where(['name' => 'zhangsan'])->one();
// order 中的 customer_id ,id 为 customer
// customer 1 : n order
//$orders = $customer->hasMany('app\models\Order', ['customer_id' => 'id'])->asArray()->all();
// $orders = $customer->hasMany(Order::className(), ['customer_id' => 'id'])->asArray()->all();
// $orders = $customer->getOrders();
// $orders = $customer->orders;
// var_dump($orders);
//根据订单查询顾客信息
$order = Order::find()->where(['id' => 1])->one();
// $order->getCustomer()->one();
$customer = $order->customer;
var_dump($customer);
}
关联查询性能问题
public function actionTest2()
{
$customer = Customer::find()->where(['name' => 'zhangsan'])->one();
//第一次会执行select * from order where customer_id = ...
$orders1 = $customer->orders;
//第二次 直接从 orders 获取数据
$orders2 = $customer->orders;
//如果还想从数据库获取数据
unset($customer->orders);
$orders2 = $customer->orders;
//关联查询的多次查询
$customers = Customer::find()->all();//假设有100个顾客,$orders = $customer->orders;就会执行100次,加上这次100+1 //select * from customer
// select * from order where custoemr in (...),这样下面就不会执行 sql,就变成只有2次sql 查询
$customers = Customer::find()->with('orders')->all();
foreach ($customers as $customer) {
//都会调用 sql
$orders = $customer->orders;
}
}
//打印出 sql
$result = Test::find()->where([‘like’,‘name’,‘a’])->createCommand()->getRawSql();
// where() 使用
https://www.yiichina.com/doc/api/2.0/yii-db-queryinterface#where()-detail