StartMVC开发手册

可以快速上手的开发文档

手册目录

数据库链式操作

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 段保持 DESC
  • order(['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 > 5
  • having('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。