Startmvc支持通过“连贯操作”方式增/删/改/查数据,也支持原生sql语句操作。操作数据库前,请配置好数据库信息config/database.php
字段选择select()
# 用法1: 字符参数
Db::select('title, content')->table('test')->get();
# 输出: "SELECT title, content FROM test"
Db::select('title AS t, content AS c')->table('test')->get();
# 输出: "SELECT title AS t, content AS c FROM test"
# 用法2: 数组参数
Db::select(['title', 'content'])->table('test')->get();
# 输出: "SELECT title, content FROM test"
Db::select(['title AS t', 'content AS c'])->table('test')->get();
# 输出: "SELECT title AS t, content AS c FROM test"select('id, name')
select(['id', 'name', ...])
如果不使用select方法,默认是查询所有字段(*)
聚合查询
方法: count() sum() avg() min() max()
$count = Db::table('users')->count(); // 直接返回 int
$sum = Db::table('orders')->sum('amount'); // 直接返回 float/int
$avg = Db::table('products')->avg('price'); // 直接返回 float
$max = Db::table('products')->max('price'); // 直接返回 mixed
$min = Db::table('products')->min('price'); // 直接返回 mixed
sum('amount')sum('amount', 'totalAmount')
选择表table()/from()
table()和from()功能是一样的,用哪个都行
Db::table('products')...table('products')table(['products as p', 'images as i'])
联合查询Join
方法有: join(),leftJoin() rightJoin() innerJoin() leftOuterJoin() rightOuterJoin() fullOuterJoin()
Db::table('test as t')->leftJoin('foo as f', 't.id', 'f.t_id')->get();
# 输出: "SELECT * FROM test as t LEFT JOIN foo as f ON t.id=f.t_id"
Db::table('test as t')->fullOuterJoin('foo as f', 't.id', 'f.t_id')->get();
# 输出: "SELECT * FROM test as t FULL OUTER JOIN foo as f ON t.id=f.t_id"leftJoin('images', 'products.id', 'images.productId')leftJoin('images', 'products.id = images.productId')leftJoin('images ON products.id = images.productId')
加入子节点查询joinNode()
通过这个方法,你可以构造一个子查询,将查到的数据作为结果的一个子元素。如果当前没有设置groupBy,会自动添加主表的id字段作为分组依据
// 基本用法
$result = Db::table('users')
->select('id', 'name')
->joinNode('profile', [
'email' => 'profiles.email',
'phone' => 'profiles.phone'
])
->where('users.id', 1)
->first();
// 自定义分组
$result = Db::table('users')
->select('id', 'name')
->joinNode('profile', [
'email' => 'profiles.email',
'phone' => 'profiles.phone'
])
->group('users.id, users.name') // 可以自定义分组字段
->where('users.id', 1)
->first();
// 获取用户及其关联订单
$user = Db::table('users AS u')
->select('u.*')
->leftJoin('orders AS o', 'o.user_id', '=', 'u.id')
->joinNode('orders', [
'id' => 'o.id',
'amount' => 'o.amount',
'created_at' => 'o.created_at'
])
->where('u.id', 1)
->group('u.id')
->first();
// 结果:
[
'id' => 1,
'username' => 'test',
'email' => 'test@example.com',
'orders' => [
[
'id' => 101,
'amount' => 199.99,
'created_at' => '2023-01-01 10:00:00'
],
[
'id' => 102,
'amount' => 299.99,
'created_at' => '2023-01-05 14:30:00'
]
]
]
// 使用其它方法获取子节点数据,比如first(), 同样也支持value(), column(),getSql()等等
$user = Db::table('users AS u')
->select('u.*')
->leftJoin('orders AS o', 'o.user_id', '=', 'u.id')
->joinNode('orders', [
'id' => 'o.id',
'amount' => 'o.amount'
])
->where('u.id', 1)
->group('u.id')
->first();000条件语句Where
where方法支持四种使用方式:
1. 数组方式 - 多个条件AND连接
$where = [
'name' => '张三',
'age' => 23,
'status' => 1
];
Db::table('test')->where($where)->first();
// 输出: "SELECT * FROM test WHERE name='张三' AND age='23' AND status='1' LIMIT 1"2. 简单条件 - 等于判断
Db::table('test')->where('active', 1)->get();
// 输出: "SELECT * FROM test WHERE active='1'"3. 带运算符的条件
Db::table('test')->where('age', '>=', 18)->get();
// 输出: "SELECT * FROM test WHERE age>='18'"4. 原生SQL条件 - 支持参数绑定
// 简单条件
Db::table('article')->where('id=10')->get();
Db::table('article')->where('status=1')->get();
Db::table('article')->where('views>100')->get();
Db::table('test')->where('age = ? OR age = ?', [18, 20])->get();
// 输出: "SELECT * FROM test WHERE age='18' OR age='20'"支持的条件方法:
- where() // 基本条件
- orWhere() // OR条件
- notWhere() // NOT条件
- orNotWhere() // OR NOT条件
- whereNull() // IS NULL条件
- whereNotNull() // IS NOT NULL条件
- whereRaw()/orWhereRaw() //原生条件语句
使用示例:
// 组合条件
Db::table('test')
->where('active', 1)
->notWhere('auth', 1)
->get();
// 输出: "SELECT * FROM test WHERE active = '1' AND NOT auth = '1'"
// OR条件
Db::table('test')
->where('age', 20)
->orWhere('age', '>', 25)
->get();
// 输出: "SELECT * FROM test WHERE age = '20' OR age > '25'"
// NULL判断
Db::table('test')->whereNotNull('email')->get();
// 输出: "SELECT * FROM test WHERE email IS NOT NULL"条件组合grouped
$db->table('users')
->grouped(function($q) {
$q->where('country', 'TURKEY')->orWhere('country', 'ENGLAND');
})
->where('status', 1)
->get();
# 输出: "SELECT * FROM users WHERE (country='TURKEY' OR country='ENGLAND') AND status ='1'"介于between()
方法: between(),orBetween(),notBetween(),orNotBetween()
Db::table('test')->where('active', 1)->notBetween('age', 18, 25)->get();
# 输出: "SELECT * FROM test WHERE active = '1' AND age NOT BETWEEN '18' AND '25'"
Db::table('test')->where('active', 1)->orBetween('age', 18, 25)->get();
# 输出: "SELECT * FROM test WHERE active = '1' OR age BETWEEN '18' AND '25'"不为空Is Null - Not Null
方法 isNull(),orIsNull(),notNull(),orNotNull()
$db->isNull('code')...isNull('slug')isNull(['slug', ...])
包含In
方法: in(),orIn(),notIn(),orNotIn()
Db::table('test')->where('active', 1)->notIn('id', [1, 2, 3])->get();
# 输出: "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"
Db::table('test')->where('active', 1)->orIn('id', [1, 2, 3])->get();
# 输出: "SELECT * FROM test WHERE active = '1' OR id IN ('1', '2', '3')"位置查询Find In Set
方法有: findInSet(),orFindInSet(),notFindInSet(),orNotFindInSet()
Db::table('test')->where('active', 1)->findInSet('selected_ids', 1)->get();
# 输出: "SELECT * FROM test WHERE active = '1' AND FIND_IN_SET (1, selected_ids)"模糊查询Like - Not Like
方法: like(),orLike(),notLike(),orNotlike()
Db::table('test')->like('title', "%php%")->get();
# 输出: "SELECT * FROM test WHERE title LIKE '%php%'"like('name')like(['name', ...])
排序Order
默认是升序排列asc
Db::table('test')->where('status', 1)->order('id')->get();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"
order('id')order('id desc')order('id', 'desc')order('rand()')
支持多字段排序:字符串逗号分隔,每个字段可带独立方向;段内未写方向的字段沿用第二参默认方向。
Db::table('test')->order('sort ASC, id DESC')->get();
# 输出: "SELECT * FROM test ORDER BY sort ASC, id DESC"
Db::table('test')->order('is_top DESC, create_time DESC, id ASC')->get();
# 输出: "SELECT * FROM test ORDER BY is_top DESC, create_time DESC, id ASC"
Db::table('test')->order('sort, id DESC', 'ASC')->get();
# 输出: "SELECT * FROM test ORDER BY sort ASC, id DESC"
order('sort ASC, id DESC')—— 多字段,方向逐个声明order('sort, id DESC', 'ASC')—— sort 段未写方向,沿用第二参 ASC;id 段保持 DESCorder(['sort', 'id'], 'DESC')—— 数组形式,所有字段共用第二参方向
多次调用是追加,不是覆盖。先写的排在最前面作为主排序,后写的依次降为次排序,所以按优先级从高到低依次调用即可,不必拼成一个字符串。
Db::table('article')
->order('is_top', 'DESC')
->order('created_at', 'DESC')
->order('id', 'ASC')
->get();
# 输出: "SELECT * FROM article ORDER BY is_top DESC, created_at DESC, id ASC"
整体替换或清空排序用 reorder()。order() 是追加,所以「先给默认排序、再按调用方参数替换」这种写法必须用 reorder():它会先清掉已有排序再设置,否则老字段会留在前面继续主导结果。
// 场景:默认按置顶+时间排,调用方传了排序参数就整体替换
$query = Db::table('article')->order('is_top DESC, id DESC');
if (!empty($sort)) {
$query->reorder($sort, 'DESC'); // 只剩 $sort,is_top / id 都不再参与
}
$list = $query->get();
// 不带参数:把排序整个清掉,SQL 里不再有 ORDER BY
Db::table('article')->order('id')->reorder()->get();
# 输出: "SELECT * FROM article"
reorder($columns, $order)—— 清空已有排序后按新字段重新排reorder()—— 只清空,不设置任何排序
多字段写法逐段校验:方向仅支持 ASC/DESC,非法方向、非法标识符或空的排序段会抛出 InvalidArgumentException;校验失败时不会改动已有的排序。
分组查询Group
Db::table('test')->where('status', 1)->groupBy('cat_id')->get();
# 输出: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id"
group('id')group(['id', 'name'])groupBy('cat_id')——group()的别名,两者完全等价
多次调用是追加,按调用顺序拼成多字段 GROUP BY:
Db::table('test')->group('cat_id')->group('status')->get();
# 输出: "SELECT * FROM test GROUP BY cat_id, status"
整体替换或清空分组用 regroup(),与 reorder() 同一套用法:
// 覆盖:旧分组不再参与
Db::table('test')->group('cat_id')->group('status')->regroup('cat_id')->get();
# 输出: "SELECT * FROM test GROUP BY cat_id"
// 清空:SQL 里不再有 GROUP BY
Db::table('test')->group('cat_id')->regroup()->get();
# 输出: "SELECT * FROM test"
筛选Having
HAVING 子句可以让我们筛选分组后的各组数据,一般与分组group配合使用
Db::table('test')->where('status', 1)->groupBy('city')->having('COUNT(person)', 100)->get();
# 输出: "SELECT * FROM test WHERE status='1' GROUP BY city HAVING COUNT(person) > '100'"
Db::table('test')->where('active', 1)->group('department_id')->having('AVG(salary)', '<=', 500)->get();
# 输出: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) <= '500'"
// 条件里带多个占位符:第一参写片段,第二参传数组
Db::table('test')->where('active', 1)->group('department_id')
->having('AVG(salary) > ? AND MAX(salary) < ?', [250, 1000])
->get();
# 输出: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"
having('stock', 5)—— 两个参数时视为stock > 5having('AVG(salary)', '<=', 500)—— 三个参数:字段、操作符、值having('AVG(salary) > ? AND MAX(salary) < ?', [250, 1000])—— 第一参是含?的片段,第二参是绑定数组
第一参必须是「字段名」或「含 ? 的片段 + 数组绑定」。写成 having('stock > 5') 或 having('stock > ?', 5)(第二参传标量而不是数组)会抛 InvalidArgumentException,不会静默降级——前者请改用 having('stock', 5),后者请改用 having('stock > ?', [5])。
多次调用是追加,默认用 AND 连接;需要 OR 用 orHaving():
Db::table('test')->group('dept_id')
->having('COUNT(*)', '>', 1)
->having('AVG(salary)', '<', 5000)
->get();
# 输出: "SELECT * FROM test GROUP BY dept_id HAVING COUNT(*) > ? AND AVG(salary) < ?"
Db::table('test')->group('dept_id')
->having('COUNT(*)', '>', 10)
->orHaving('SUM(amount)', '>', 100000)
->get();
# 输出: "SELECT * FROM test GROUP BY dept_id HAVING COUNT(*) > ? OR SUM(amount) > ?"
整体替换或清空条件用 rehaving():
// 覆盖:旧条件与旧绑定一起丢弃
Db::table('test')->group('dept_id')
->having('COUNT(*)', '>', 1)
->rehaving('SUM(amount)', '>', 1000)
->get();
# 输出: "SELECT * FROM test GROUP BY dept_id HAVING SUM(amount) > ?"
// 清空:SQL 里不再有 HAVING
Db::table('test')->group('dept_id')->having('COUNT(*)', '>', 1)->rehaving()->get();
# 输出: "SELECT * FROM test GROUP BY dept_id"
HAVING 的绑定参数由框架单独收集、按 SQL 文本顺序拼接,因此 having() 写在 where() 之前也不会出现参数错位:
// 下面两种写法的 SQL 与绑定参数完全一致
Db::table('test')->group('dept_id')->having('COUNT(*)', '>', 5)->where('status', 1)->get();
Db::table('test')->where('status', 1)->group('dept_id')->having('COUNT(*)', '>', 5)->get();
# 输出: "SELECT * FROM test WHERE status = ? GROUP BY dept_id HAVING COUNT(*) > ?"
# 绑定: [1, 5]
一个完整的统计场景——按部门统计、只看人均和总额都达标的部门,最后按总额倒序:
$rows = Db::table('orders')
->select('dept_id, COUNT(*) AS cnt, AVG(amount) AS avg_amount, SUM(amount) AS total')
->where('status', 1)
->group('dept_id')
->having('COUNT(*)', '>=', 3)
->having('AVG(amount)', '>', 100)
->order('total', 'DESC')
->get();
限制查询Limit - Offset
一般用于限制数量Limit, 偏移Offset, 和分页操作Paging.
Db::table('test')->limit(10)->get();
# 输出: "SELECT * FROM test LIMIT 10"
Db::table('test')->limit(10)->offset(10)->get();
# 输出: "SELECT * FROM test LIMIT 10 OFFSET 10"
// 分页用 page($page, $perPage),页码从 1 开始
Db::table('test')->page(2, 10)->get();
# 输出: "SELECT * FROM test LIMIT 10 OFFSET 10"
limit(10)—— 只接受行数一个参数limit(10)->offset(20)—— 跳过前 20 条,再取 10 条page($page, $perPage)—— 页码在前,内部换算成 limit + offset
limit() 不接受第二个参数,旧写法 limit($offset, $rows) 会抛 InvalidArgumentException,不会静默降级。另外 limit(0) 是「取 0 条」、返回空集,不是「不限制」;只写 offset() 而不写 limit() 时,框架会自动补上 LIMIT 18446744073709551615,因为 MySQL 不支持单独的 OFFSET。