数据库链式操作-StartMVC超轻量PHP框架

Startmvc支持通过“连贯操作”方式增/删/改/查数据，也支持原生sql语句操作。操作数据库前，请配置好数据库信息config/database.php

字段选择select()

# 用法1: 字符参数
Db::select('title, content')->table('test')->getAll();
# 输出: "SELECT title, content FROM test"

Db::select('title AS t, content AS c')->table('test')->getAll();
# 输出: "SELECT title AS t, content AS c FROM test"

# 用法2: 数组参数
Db::select(['title', 'content'])->table('test')->getAll();
# 输出: "SELECT title, content FROM test"

Db::select(['title AS t', 'content AS c'])->table('test')->getAll();
# 输出: "SELECT title AS t, content AS c FROM test"

select('id, name')

select(['id', 'name', ...])

如果不使用select方法，默认是查询所有字段(*)

更多选择功能

方法: count() sum() avg() min() max()

# 用法1:
Db::table('test')->max('price')->get();
# 输出: "SELECT MAX(price) FROM test"

# 用法2:
Db::table('test')->count('id', 'total_row')->get();
# 输出: "SELECT COUNT(id) AS total_row FROM test"

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')->getAll();
# 输出: "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')->getAll();
# 输出: "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)
    ->get();

// 自定义分组
$result = Db::table('users')
    ->select('id', 'name')
    ->joinNode('profile', [
        'email' => 'profiles.email',
        'phone' => 'profiles.phone'
    ])
    ->group('users.id, users.name') // 可以自定义分组字段
    ->where('users.id', 1)
    ->get();
    
// 获取用户及其关联订单
$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')
    ->get();
// 结果: 
[
    '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();

条件语句Where

where方法支持四种使用方式:

1. 数组方式 - 多个条件AND连接

$where = [
    'name' => '张三',
    'age' => 23,
    'status' => 1
];
Db::table('test')->where($where)->get();
// 输出: "SELECT * FROM test WHERE name='张三' AND age='23' AND status='1' LIMIT 1"

2. 简单条件 - 等于判断

Db::table('test')->where('active', 1)->getAll();
// 输出: "SELECT * FROM test WHERE active='1'"

3. 带运算符的条件

Db::table('test')->where('age', '>=', 18)->getAll();
// 输出: "SELECT * FROM test WHERE age>='18'"

4. 原生SQL条件 - 支持参数绑定

Db::table('test')->where('age = ? OR age = ?', [18, 20])->getAll();
// 输出: "SELECT * FROM test WHERE age='18' OR age='20'"

支持的条件方法:

- where()        // 基本条件
- orWhere()      // OR条件
- notWhere()     // NOT条件
- orNotWhere()   // OR NOT条件
- whereNull()    // IS NULL条件
- whereNotNull() // IS NOT NULL条件

使用示例:

// 组合条件
Db::table('test')
   ->where('active', 1)
   ->notWhere('auth', 1)
   ->getAll();
// 输出: "SELECT * FROM test WHERE active = '1' AND NOT auth = '1'"

// OR条件
Db::table('test')
   ->where('age', 20)
   ->orWhere('age', '>', 25)
   ->getAll();
// 输出: "SELECT * FROM test WHERE age = '20' OR age > '25'"

// NULL判断
Db::table('test')->whereNotNull('email')->getAll();
// 输出: "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)
	->getAll();
# 输出: "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)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND age NOT BETWEEN '18' AND '25'"

Db::table('test')->where('active', 1)->orBetween('age', 18, 25)->getAll();
# 输出: "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])->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"

Db::table('test')->where('active', 1)->orIn('id', [1, 2, 3])->getAll();
# 输出: "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)->getAll();
# 输出: "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%")->getAll();
# 输出: "SELECT * FROM test WHERE title LIKE '%php%'"

like('name')
like(['name', ...])

排序Order

默认是降序排列desc

Db::table('test')->where('status', 1)->orderBy('id')->getAll();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"

order('id')
order('id desc')
order('id', 'desc')
order('rand()')

分组查询Group

Db::table('test')->where('status', 1)->groupBy('cat_id')->getAll();
# 输出: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id"

group('id')
group(['id', 'name'])

筛选Having

HAVING 子句可以让我们筛选分组后的各组数据，一般与分组group配合使用

Db::table('test')->where('status', 1)->groupBy('city')->having('COUNT(person)', 100)->getAll();
# 输出: "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)->getAll();
# 输出: "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])->getAll();
# 输出: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"

having('stock', 5)
having('stock > 5')
having('stock > ?', 5)

限制查询Limit - Offset

一般用于限制数量Limit, 偏移Offset, 和分页操作Paging.

Db::table('test')->limit(10)->getAll();
# 输出: "SELECT * FROM test LIMIT 10"

Db::table('test')->limit(10, 20)->getAll();
# 输出: "SELECT * FROM test LIMIT 10, 20"

Db::table('test')->limit(10)->offset(10)->getAll();
# 输出: "SELECT * FROM test LIMIT 10 OFFSET 10"

StartMVC开发手册

数据库链式操作