查询
1. table方法查询数据
$data = Db::table("user")->select();
//查询一条数据
$data = Db::table("user")->find();
//进行条件匹配
//单条件查询
$data = Db::table("user")->where("id", ">", 2)->select();
//多条件查询
//多条件且查询
$data = Db::table("user")->where("id", ">", 2)->where("id", "<", 3)->select();
//字符串形式查询
$data = Db::table("user")->where("id > 2 and id > 3")->select();
//多条件或查询
$data = Db::table("user")->where("id", "<=", 5)->whereOr("id", ">=", 2)->select();
//字符串形式查询
$data = Db::table("user")->where("id > 2 or id > 3")->select();
//模糊查询
$data = Db::table("user")->where("name", "like", "%user1%")->select();
2. limit()方法截取数据:
// 对应的查询语句SELECT * FROM `user` LIMIT 3
$data = Db::table("user")->limit(3)->select();
$data = Db::table("user")->limit(3,3)->select();
3. order()方法对结果集进行排序:
//SELECT * FROM `user` ORDER BY `id` 默认降序
$data = Db::table("user")->order("id")->select();
//SELECT * FROM `user` ORDER BY `id` DESC
$data = Db::table("user")->order("id", "desc")->select();
4. field()方法查询特定字段:
//设置查询字段
//SELECT `name`,`pass` FROM `user`
$data = Db::table("user")->field("name,pass")->select();
//SELECT name unname,`pass` FROM `user`
$data = Db::table("user")->field("name unname,pass")->select();
//以数组的形式查询特定字段
//SELECT `name`,`pass` FROM `user`
$data = Db::table("user")->field(["name", "pass"])->select();
5. 使用数组进行特定字段的条件查询:
//SELECT `name` AS `uname`,`pass` FROM `user`
$data = Db::table("user")->field(["name"=>"uname", "pass"])->select();
//不查询name 和 pass SELECT `id`,`age` FROM `user`
$data = Db::table("user")->field("name, pass", true)->select();
6. group()方法实现分组聚合操作:
//SELECT pass, count(*) total FROM `user` GROUP BY `pass`
$data = Db::table("user")->field("pass, count(*) total")->group("pass")->select();
7. having()方法实现过滤(注:Having只能结合group一起使用):
//SELECT pass, count(*) total FROM `user` GROUP BY `pass` HAVING total >= 2
$data = Db::table("user")->field("pass, count(*) total")->having("total >= 2")->group("pass")->select();
8. join()方法实现多表查询:
//SELECT `goods`.*,type.name tname FROM `goods` INNER JOIN `type` ON `type`.`id`=`goods`.`cid`
$data = Db::table("goods")->field("goods.*, type.name tname")->join("type", "type.id=goods.cid")->select();
//右连接查询,即需要在join方法中传递第三个参数'right' 右连接 'left'左连接
$data = Db::table("goods")->field("goods.*, type.name tname")->join("type", "type.id=goods.cid", 'right')->select();
//左连接
$data = Db::table("goods")->field("goods.*, type.name tname")->join("type", "type.id=goods.cid", 'left')->select();
9. 对查询的表设置自定义名称:
//SELECT `g`.*,t.name tname FROM `goods` `g` LEFT JOIN `type` `t` ON `t`.`id`=`g`.`cid`
$data = Db::table("goods")->alias("g")->field("g.*, t.name tname")->join("type t", "t.id=g.cid", 'left')->select();
10. 对查询结果集的求和、计数、最大、最小值:
//SELECT MAX(age) AS tp_max FROM `user` LIMIT 1
$data = Db::table("user")->max("age");
//SELECT MIN(age) AS tp_min FROM `user` LIMIT 1
$data = Db::table("user")->min("age");
//SELECT AVG(age) AS tp_avg FROM `user` LIMIT 1
$data = Db::table("user")->avg("age");
//SELECT SUM(age) AS tp_sum FROM `user` LIMIT 1
$data = Db::table("user")->sum("age");
//SELECT COUNT(age) AS tp_count FROM `user` LIMIT 1
$data = Db::table("user")->count("age");
增加操作
1. insert()方法实现插入一条数据:
//插入数据
//数组中的字段名 必须和数据库中二点字段名一致
$data = [
"name"=>"张三",
"pass"=>"123",
"age"=>18
];
//INSERT INTO `user` (`name` , `pass` , `age`) VALUES ('张三' , 123 , 18)
//返回影响行数
$code = Db::table("user")->insert($data);
2. insertAll()方法实现插入多条数据:
//即以二维数组的形式作为insertAll()方法的参数
$data = [
[
"name"=>"张三1",
"pass"=>"123",
"age"=>18
],
[
"name"=>"张三2",
"pass"=>"123",
"age"=>19
]
];
//INSERT INTO `user` (`name` , `pass` , `age`) VALUES ( '张三1','123',18 ) , ( '张三2','123',19 )
//返回结果影响行数2
$code = Db::table("user")->insertAll($data);
3. insetrtGetId()方法实现插入并获取插入的主键值(此处指ID):
/获取最后一次插入ID
$data = [
"name"=>"张三",
"pass"=>"123",
"age"=>18
];
// INSERT INTO `user` (`name` , `pass` , `age`) VALUES ('张三' , 123 , 18)
// string(2) "32"
//返回值时最后插入的ID
$code = Db::table("user")->insertGetId($data);
更新数据
1. 修改数据:
// UPDATE `user` SET `pass`=444,`age`=2 WHERE `id` > 25
// int(14)
$code = Db::table("user")->where("id", ">", 25)->update(["pass"=>444, "age"=>2]);
//update()中需以数组的形式进行对相应字段的更新操作
2. setInc()方法实现自增、setDec()方法实现自减:
//实现自增,默认增1
$code = Db::table("user")->where("id", 23)->setInc("age");
//实现自减,修改默认,即此时减3
$code = Db::table("user")->where("id", 23)->setdec("age", 3);
删除数据
1. delete()方法实现删除一条数据:
//DELETE FROM `user` WHERE `id` = 23
$code = Db::table("user")->where("id", 23)->delete();
//入如果删除数据和主键有关,那么直接把相应的主键的值放在delete中就行
//DELETE FROM `user` WHERE `id` = 22
$code = Db::table("user")->delete(22);
2. 通过in()的方式删除多条数据:
//DELETE FROM `user` WHERE ( id in(20,21) )
$code = Db::table("user")->where("id in(20,21)")->delete();
//DELETE FROM `user` WHERE `id` IN (17,18,19)
$code = Db::table("user")->delete([17, 18, 19]);
3. 通过where条件限制删除区间数据:
//DELETE FROM `user` WHERE ( id > 10 and id <19 )
$code = Db::table("user")->where("id > 10 and id <19")->delete();