README

一个轻量级的PHP查询构建器，便于数据库交互。

安装

您可以通过 Composer 安装此库。请确保您的项目符合最低PHP版本要求7.4或更高。

composer require devcoder-xyz/php-query-builder

用法

SQL查询构建器库允许您使用面向对象的方法流畅地构建SQL查询。以下是一些用法示例

创建SELECT查询

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query
$query = QueryBuilder::select('name', 'email')
    ->from('users')
    ->where('status = "active"')
    ->orderBy('name')
    ->limit(10);

echo $query; // Outputs: SELECT name, email FROM users WHERE status = "active" ORDER BY name LIMIT 10

QueryBuilder中的SQL连接类型

SQL查询构建器库支持多种JOIN操作类型，以根据它们之间的相关列组合多个表中的行。以下是您可以与QueryBuilder一起使用的不同JOIN类型示例

1. INNER JOIN

INNER JOIN返回两个表中都存在匹配值的记录。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with INNER JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->innerJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u INNER JOIN addresses a ON u.id = a.user_id

2. LEFT JOIN

LEFT JOIN返回左侧表（第一个表）中的所有记录以及右侧表（第二个表）中匹配的记录。如果没有匹配项，则右侧的结果为NULL。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with LEFT JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->leftJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u LEFT JOIN addresses a ON u.id = a.user_id

3. RIGHT JOIN

RIGHT JOIN返回右侧表（第二个表）中的所有记录以及左侧表（第一个表）中匹配的记录。如果没有匹配项，则左侧的结果为NULL。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with RIGHT JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->rightJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u RIGHT JOIN addresses a ON u.id = a.user_id

使用DISTINCT创建SELECT查询

您可以使用distinct()方法使用QueryBuilder指定SELECT DISTINCT查询。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with DISTINCT using QueryBuilder
$query = QueryBuilder::select('name', 'email')
    ->distinct()
    ->from('users')
    ->where('status = "active"')
    ->orderBy('name')
    ->limit(10);

echo $query; // Outputs: SELECT DISTINCT name, email FROM users WHERE status = "active" ORDER BY name LIMIT 10

使用GROUP BY创建SELECT查询

您可以使用groupBy()方法使用QueryBuilder指定GROUP BY子句。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with GROUP BY using QueryBuilder
$query = QueryBuilder::select('category_id', 'COUNT(*) as count')
    ->from('products')
    ->groupBy('category_id');

echo $query; // Outputs: SELECT category_id, COUNT(*) as count FROM products GROUP BY category_id

使用HAVING子句创建SELECT查询

您可以使用having()方法使用QueryBuilder指定HAVING子句。

use DevCoder\SqlBuilder\QueryBuilder;

// Create a SELECT query with HAVING using QueryBuilder
$query = QueryBuilder::select('category_id', 'COUNT(*) as count')
    ->from('products')
    ->groupBy('category_id')
    ->having('COUNT(*) > 5');

echo $query; // Outputs: SELECT category_id, COUNT(*) as count FROM products GROUP BY category_id HAVING COUNT(*) > 5

创建INSERT查询

use DevCoder\SqlBuilder\QueryBuilder;

// Create an INSERT query
$query = QueryBuilder::insert('users')
    ->setValue('name', '"John Doe"')
    ->setValue('email', '"john.doe@example.com"')
    ->setValue('status', '"active"');

echo $query; // Outputs: INSERT INTO users (name, email, status) VALUES ("John Doe", "john.doe@example.com", "active")

创建UPDATE查询

use DevCoder\SqlBuilder\QueryBuilder;

// Create an UPDATE query
$query = QueryBuilder::update('users')
    ->set('status', '"inactive"')
    ->where('id = 123');

echo $query; // Outputs: UPDATE users SET status = "inactive" WHERE id = 123

创建DELETE查询

use DevCoder\SqlBuilder\QueryBuilder;

// Create a DELETE query
$query = QueryBuilder::delete('users')
    ->where('status = "inactive"');

echo $query; // Outputs: DELETE FROM users WHERE status = "inactive"

使用自定义表达式创建SELECT查询

use DevCoder\SqlBuilder\QueryBuilder;
use DevCoder\SqlBuilder\Expression\Expr;

// Example of a query with a custom expression
$whereClause = Expr::greaterThan('age', '18');
$query = QueryBuilder::select('name', 'email')
    ->from('users')
    ->where($whereClause);

echo $query; // Outputs: SELECT name, email FROM users WHERE age > 18

可用表达式（Expr）列表

以下是一个完整的可用静态方法列表，包括展示它们使用方法的示例

Expr::equal(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate an equal comparison expression
$equalExpr = Expr::equal('age', '30');
echo "Equal Expression: $equalExpr"; // Outputs: Equal Expression: age = 30

Expr::notEqual(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a not equal comparison expression
$notEqualExpr = Expr::notEqual('status', '"active"');
echo "Not Equal Expression: $notEqualExpr"; // Outputs: Not Equal Expression: status <> "active"

Expr::greaterThan(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a greater than comparison expression
$greaterThanExpr = Expr::greaterThan('salary', '50000');
echo "Greater Than Expression: $greaterThanExpr"; // Outputs: Greater Than Expression: salary > 50000

Expr::greaterThanEqual(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a greater than or equal comparison expression
$greaterThanEqualExpr = Expr::greaterThanEqual('points', '100');
echo "Greater Than or Equal Expression: $greaterThanEqualExpr"; // Outputs: Greater Than or Equal Expression: points >= 100

Expr::lowerThan(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a lower than comparison expression
$lowerThanExpr = Expr::lowerThan('price', '50');
echo "Lower Than Expression: $lowerThanExpr"; // Outputs: Lower Than Expression: price < 50

Expr::lowerThanEqual(string $key, string $value)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a lower than or equal comparison expression
$lowerThanEqualExpr = Expr::lowerThanEqual('quantity', '10');
echo "Lower Than or Equal Expression: $lowerThanEqualExpr"; // Outputs: Lower Than or Equal Expression: quantity <= 10

Expr::isNull(string $key)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate an IS NULL expression
$isNullExpr = Expr::isNull('description');
echo "IS NULL Expression: $isNullExpr"; // Outputs: IS NULL Expression: description IS NULL

Expr::isNotNull(string $key)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate an IS NOT NULL expression
$isNotNullExpr = Expr::isNotNull('created_at');
echo "IS NOT NULL Expression: $isNotNullExpr"; // Outputs: IS NOT NULL Expression: created_at IS NOT NULL

Expr::in(string $key, array $values)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate an IN expression
$inExpr = Expr::in('category_id', [1, 2, 3]);
echo "IN Expression: $inExpr"; // Outputs: IN Expression: category_id IN (1, 2, 3)

Expr::notIn(string $key, array $values)

use DevCoder\SqlBuilder\Expression\Expr;

// Example: Generate a NOT IN expression
$notInExpr = Expr::notIn('role', ['"admin"', '"manager"']);
echo "NOT IN Expression: $notInExpr"; // Outputs: NOT IN Expression: role NOT IN ("admin", "manager")

这些示例展示了如何使用每个Expr类方法生成用于各种比较和条件操作的SQL表达式。将这些方法结合到您的SQL查询构建器使用中，以有效地构建复杂和精确的SQL查询。

功能

• 流畅地生成SELECT、INSERT、UPDATE和DELETE查询。
• 安全的SQL查询构建以防止SQL注入漏洞。
• 支持WHERE、ORDER BY、GROUP BY、HAVING、LIMIT和JOIN子句。
• 创建自定义SQL表达式的简化方法。

许可证

此库是开源软件，受MIT许可证许可。