yii2 数据库的相关操作的代码示例

基础查询

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()->batch(10);  每次取10条数据 

Customer::find()->each(10);  每次取10条数据，迭代查询 

//根据sql语句查询：查询name=test的客户
Customer::model()->findAllBySql("select * from customer where name = test"); 

//根据主键查询：查询主键值为1的数据
Customer::model()->findByPk(1); 

//根据条件查询(该方法是根据条件查询一个集合,可以是多个条件,把条件放到数组里面) 
Customer::model()->findAllByAttributes(["username"=>"admin"]); 

//子查询
$subQuery = (new Query())->select("COUNT(*)")->from("customer");
// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`
$query = (new Query())->select(["id", "count" => $subQuery])->from("customer");

//关联查询：查询客户表（customer）关联订单表（orders），条件是status=1，客户id为1，从查询结果的第5条开始，查询10条数据
$data = (new Query())
    ->select("*")
    ->from("customer")
    ->join("LEFT JOIN","orders","customer.id = orders.customer_id")
    ->where(["status"=>"1","customer.id"=>"1"])
    ->offset(5)
    ->limit(10)
    ->all()

直接查询

//createCommand(执行原生的SQL语句)  
$sql= "SELECT u.account,i.* FROM sys_user as u left join user_info as i on u.id=i.user_id";  
$rows=Yii::$app->db->createCommand($sql)->query();  

查询返回多行：    
$command = Yii::$app->db->createCommand("SELECT * FROM post");  
$posts = $command->queryAll();

返回单行
$command = Yii::$app->db->createCommand("SELECT * FROM post WHERE id=1");  
$post = $command->queryOne();

查询多行单值：  
$command = Yii::$app->db->createCommand("SELECT title FROM post");  
$titles = $command->queryColumn();

查询标量值/计算值：  
$command = Yii::$app->db->createCommand("SELECT COUNT(*) FROM post");  
$postCount = $command->queryScalar();

关联查询

/**
 *客户表Model：CustomerModel 
 *订单表Model：OrdersModel
 *国家表Model：CountrysModel
 *首先要建立表与表之间的关系 
 *在CustomerModel中添加与订单的关系
 */     
Class CustomerModel extends yiidbActiveRecord
{
    ...
    //客户和订单是一对多的关系所以用hasMany
    //此处OrdersModel在CustomerModel顶部别忘了加对应的命名空间
    //id对应的是OrdersModel的id字段，order_id对应CustomerModel的order_id字段
    public function getOrders()
    {
        return $this->hasMany(OrdersModel::className(), ["id"=>"order_id"]);
    }

    //客户和国家是一对一的关系所以用hasOne
    public function getCountry()
    {
        return $this->hasOne(CountrysModel::className(), ["id"=>"Country_id"]);
    }
    ....
}

// 查询客户与他们的订单和国家
CustomerModel::find()->with("orders", "country")->all();

// 查询客户与他们的订单和订单的发货地址（注：orders 与 address都是关联关系）
CustomerModel::find()->with("orders.address")->all();

// 查询客户与他们的国家和状态为1的订单
CustomerModel::find()->with([
    "orders" => function ($query) {
        $query->andWhere("status = 1");
        },
        "country",
])->all();

条件查询

$customers = Customer::find()->where($cond)->all(); 

$cond就是我们所谓的条件，条件的写法也根据查询数据的不同存在差异，那么如何用yii2的方式来写查询条件呢？

[[简单条件]]


// SQL: (type = 1) AND (status = 2).
$cond = ["type" => 1, "status" => 2] 

// SQL:(id IN (1, 2, 3)) AND (status = 2)
$cond = ["id" => [1, 2, 3], "status" => 2] 

//SQL:status IS NULL
$cond = ["status" => null]
[[and]]:将不同的条件组合在一起，用法举例：

//SQL:`id=1 AND id=2`
$cond = ["and", "id=1", "id=2"]

//SQL:`type=1 AND (id=1 OR id=2)`
$cond = ["and", "type=1", ["or", "id=1", "id=2"]]

//SQL:`type=1 AND (id=1 OR id=2)` //此写法"="可以换成其他操作符，例：in like != >=等
$cond = [
    "and",
    ["=", "type", 1],
    [
        "or",
        ["=", "id", "1"],
        ["=", "id", "2"],
    ]
]
[[or]]:

//SQL:`(type IN (7, 8, 9) OR (id IN (1, 2, 3)))`
$cond = ["or", ["type" => [7, 8, 9]], ["id" => [1, 2, 3]]
[[not]]:


//SQL:`NOT (attribute IS NULL)`
$cond = ["not", ["attribute" => null]]
[[between]]: not between 用法相同


//SQL:`id BETWEEN 1 AND 10`
$cond = ["between", "id", 1, 10]
[[in]]: not in 用法类似


//SQL:`id IN (1, 2, 3)`
$cond = ["in", "id", [1, 2, 3]] or $cond = ["id"=>[1, 2, 3]]

//IN条件也适用于多字段
$cond = ["in", ["id", "name"], [["id" => 1, "name" => "foo"], ["id" => 2, "name" => "bar"]]]

//也适用于内嵌sql语句
$cond = ["in", "user_id", (new Query())->select("id")->from("users")->where(["active" => 1])]
[[like]]:


//SQL:`name LIKE "%tester%"`
$cond = ["like", "name", "tester"]

//SQL:`name LIKE "%test%" AND name LIKE "%sample%"`
$cond = ["like", "name", ["test", "sample"]]

//SQL:`name LIKE "%tester"`
$cond = ["like", "name", "%tester", false]
[[exists]]: not exists用法类似

//SQL:EXISTS (SELECT "id" FROM "users" WHERE "active"=1)
$cond = ["exists", (new Query())->select("id")->from("users")->where(["active" => 1])]

此外，您可以指定任意运算符如下
//SQL:`id >= 10`
$cond = [">=", "id", 10]

//SQL:`id != 10`
$cond = ["!=", "id", 10]

新增

使用model::save()操作进行新增数据

$user= new User;         
$user->username =$username;  
$user->password =$password;  
$user->save()
使用createCommand()进行新增数据

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

修改

使用model::save()进行修改

$user = User::find()->where(["name"=>"test"])->one(); //获取name等于test的模型
$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();

删除

使用model::delete()进行删除

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

直接删除:删除年龄为30的所有用户

$result = User::deleteAll(["age"=>"30"]);

根据主键删除:删除主键值为1的用户

$result = User::deleteByPk(1);

使用createCommand()删除

Yii::$app->db->createCommand()->delete("user", "age = 30")->execute();