krinkle / sql-query-builder
Requires
- php: >=5.5
- nilportugues/sql-query-formatter: ~1.2
Requires (Dev)
- fabpot/php-cs-fixer: ~1.9
- nilportugues/php_backslasher: ~0.2
- phpunit/phpunit: 4.*
This package is auto-updated.
Last update: 2024-08-29 05:18:26 UTC
README
一个优雅、轻量级且高效的SQL查询构建器,具有流畅的接口SQL语法,支持绑定和复杂查询生成。 无需建立数据库连接即可工作。
- 1. 安装
- 2. 构建器
- 3. 构建查询
- 4. 高级查询
- 5. 查询注释
- 6. 质量代码
- 7. 作者
- 8. 许可证
1. 安装 ↑
推荐通过 Composer 安装SQL查询构建器。运行以下命令安装
php composer.phar require nilportugues/sql-query-builder
2. 构建器 ↑
SQL查询构建器允许使用标准SQL-2003方言(默认)和MySQL方言(扩展SQL-2003方言)生成复杂SQL查询。
2.1. 通用构建器 ↑
通用查询构建器是此类的默认构建器,并生成标准的SQL-2003。
默认情况下,所有列别名都使用单引号进行转义。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select()->setTable('user'); echo $builder->write($query);
输出
SELECT user.* FROM user
2.2. MySQL构建器 ↑
MySQL查询构建器有自己的类,该类继承自SQL-2003构建器。所有列都将使用波浪线符号包裹。
默认情况下,所有表和列别名都使用波浪线符号进行转义。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\MySqlBuilder; $builder = new MySqlBuilder(); $query = $builder->select()->setTable('user'); echo $builder->write($query);
输出
SELECT user.* FROM `user`
2.3. 人性化输出 ↑
通用和MySQL查询构建器都可以编写复杂的SQL查询。
每个开发者都可能需要在某一点上审查复杂查询的输出,因此SQL查询构建器包含一个人性化的输出方法,因此存在`writeFormatted`方法以帮助开发者。
请注意,在生产模式下应尽量避免使用`writeFormatted`,因为它会由于对生成的语句进行解析和重新格式化而添加不必要的开销。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select()->setTable('user'); echo $builder->writeFormatted($query);
输出
SELECT user.* FROM user
更复杂的示例可以在文档中找到。
3. 构建查询 ↑
3.1. SELECT语句 ↑
3.1.1. 基本SELECT语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(['user_id','name','email']); echo $builder->write($query);
输出
SELECT user.user_id, user.name, user.email FROM user
3.1.2. 别名SELECT语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(['userId' => 'user_id', 'username' => 'name', 'email' => 'email']); echo $builder->write($query);
输出
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user
3.1.3. 带WHERE语句的SELECT ↑
使用WHERE条件进行过滤的默认逻辑运算符是AND
。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns([ 'userId' => 'user_id', 'username' => 'name', 'email' => 'email' ]) ->where() ->greaterThan('user_id', 5) ->notLike('username', 'John') ->end(); echo $builder->writeFormatted($query);
输出
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user WHERE (user.user_id < :v1) AND (user.username NOT LIKE :v2)
3.1.4. 复杂WHERE条件 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->where() ->equals('user_id', 1) ->equals('user_id', 2) ->subWhere("OR") ->lessThan($column, 10) ->greaterThan('user_id', 100) ->end(); echo $builder->writeFormatted($query);
输出
SELECT user.* FROM user WHERE (user.user_id = :v1) AND (user.user_id = :v2) AND ( (user.user_id < :v3) OR (user.user_id > :v4) )
JOIN与LEFT/RIGHT/INNER/CROSS JOIN SELECT语句 ↑
JOIN
、LEFT JOIN
、RIGHT JOIN
、INNER JOIN
、CROSS JOIN
的语法完全相同。
以下是一个示例,选择表和连接的表列,并使用来自表和连接的表的列进行排序。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns([ 'userId' => 'user_id', 'username' => 'name', 'email' => 'email', 'created_at' ]) ->orderBy('user_id', OrderBy::DESC) ->leftJoin( 'news', //join table 'user_id', //origin table field used to join 'author_id', //join column ['newsTitle' => 'title', 'body', 'created_at', 'updated_at'] ) ->on() ->equals('author_id', 1); //enforcing a condition on the join column $query ->where() ->greaterThan('user_id', 5) ->notLike('username', 'John') ->end(); $query ->orderBy('created_at', OrderBy::DESC); echo $builder->writeFormatted($query);
输出
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email', user.created_at, news.title AS 'newsTitle', news.body, news.created_at, news.updated_at FROM user LEFT JOIN news ON (news.author_id = user.user_id) AND (news.author_id = :v1) WHERE (user.user_id < :v2) AND (user.username NOT LIKE :v3) ORDER BY user.user_id DESC, news.created_at DESC;
3.1.6. 计数行 ↑
计数行有三种可能的方式,使用ALL选择器*
、指定列或指定列及其别名。
3.1.6.1. 使用ALL选择器进行计数
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->count() echo $builder->write($query);
输出
SELECT COUNT(*) FROM user;
3.1.6.2. 使用列作为选择器进行计数
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->count('user_id') echo $builder->write($query);
输出
SELECT COUNT(user.user_id) FROM user;
3.1.6.3. 使用列作为选择器进行计数
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->count('user_id', 'total_users') echo $builder->write($query);
输出
SELECT COUNT(user.user_id) AS 'total_users' FROM user;
3.2. INSERT语句 ↑
INSERT
语句非常简单。
3.2.1 基本INSERT语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->insert() ->setTable('user') ->setValues([ 'user_id' => 1, 'name' => 'Nil', 'contact' => 'contact@nilportugues.com', ]); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
INSERT INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)
[':v1' => 1, ':v2' => 'Nil', ':v3' => 'contact@nilportugues.com'];
3.3. UPDATE语句 ↑
UPDATE
语句的工作方式正如预期,设置值和条件以匹配行,即可。
以下提供了一些示例。
3.3.1 基本UPDATE语句 ↑
重要的是包括where
语句是至关重要的,否则在执行该语句时,所有表行将被提供的值替换。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->update() ->setTable('user') ->setValues([ 'user_id' => 1, 'name' => 'Nil', 'contact' => 'contact@nilportugues.com' ]) ->where() ->equals('user_id', 1) ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
UPDATE user SET user.user_id = :v1, user.name = :v2, user.contact = :v3 WHERE (user.user_id = :v4)
[':v1' => 1, ':v2' => 'Nil', ':v3' => 'contact@nilportugues.com', ':v4' => 1];
3.3.2. 详细的UPDATE语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Syntax\OrderBy; use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->update() ->setTable('user') ->setValues([ 'name' => 'UpdatedName', ]); $query ->where() ->like('username', '%N') ->between('user_id', 1, 2000) ->end(); $query ->orderBy('user_id', OrderBy::ASC) ->limit(1); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
UPDATE user SET user.name = :v1 WHERE (user.username LIKE :v2) AND (user.user_id BETWEEN :v3 AND :v4) ORDER BY user.user_id ASC LIMIT :v5
3.4. DELETE语句 ↑
DELETE
语句的使用方式与UPDATE
类似,但不设置值。
以下提供了一些示例。
3.4.1. 使用DELETE语句清空表 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->delete() ->setTable('user'); $sql = $builder->write($query);
输出
DELETE FROM user
3.4.2. 基本DELETE语句 ↑
重要的是包括where
语句是至关重要的,否则在执行该语句时,所有表行将被提供的值删除。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->delete() ->setTable('user'); $query ->where() ->equals('user_id', 100) ->end(); $query ->limit(1); $sql = $builder->write($query); $values = $builder->getValues();
输出
DELETE FROM user WHERE (user.user_id = :v1) LIMIT :v2
[':v1' => 100, ':v2' => 1];
3.4.2. 详细的DELETE语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Syntax\OrderBy; use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->delete() ->setTable('user'); $query ->where() ->like('username', '%N') ->between('user_id', 1, 2000) ->end(); $query ->orderBy('user_id', OrderBy::ASC) ->limit(1); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
DELETE FROM user WHERE (user.username LIKE :v1) AND (user.user_id BETWEEN :v2 AND :v3) ORDER BY user.user_id ASC LIMIT :v4
3.5. INTERSECT语句 ↑
MySQL不支持INTERSECT。可以使用INNER JOIN语句替代以获得相同的结果。
INTERSECT
语句非常简单。
3.5.1 基本INTERSECT语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $select1 = $builder->select()->setTable('user'); $select2 = $builder->select()->setTable('user_emails'); $query = $builder->intersect() ->add($select1) ->add($select2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.* FROM user INTERSECT SELECT user_email.* FROM user_email
3.6. MINUS语句 ↑
MySQL不支持MINUS。可以通过结合使用LEFT JOIN语句以及IS NULL或NOT IN条件来获得相同的结果。
MINUS
语句非常简单。
3.6.1 基本MINUS语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $select1 = $builder->select()->setTable('user'); $select2 = $builder->select()->setTable('user_emails'); $query = $builder->minus($select1, $select2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.* FROM user MINUS SELECT user_email.* FROM user_email
3.7. UNION语句 ↑
UNION
语句非常简单。
3.7.1 基本UNION语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $select1 = $builder->select()->setTable('user'); $select2 = $builder->select()->setTable('user_emails'); $query = $builder->union() ->add($select1) ->add($select2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.* FROM user UNION SELECT user_email.* FROM user_email
3.8. UNION ALL语句 ↑
UNION ALL
语句非常简单。
3.8.1 基本UNION ALL语句 ↑
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $select1 = $builder->select()->setTable('user'); $select2 = $builder->select()->setTable('user_emails'); $query = $builder->unionAll() ->add($select1) ->add($select2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.* FROM user UNION ALL SELECT user_email.* FROM user_email
4. 高级查询 ↑
4.1. 使用 WHERE 条件进行过滤 ↑
以下运算符可用于使用 WHERE 条件进行过滤
public function subWhere($operator = 'OR'); public function equals($column, $value); public function notEquals($column, $value); public function greaterThan($column, $value); public function greaterThanOrEqual($column, $value); public function lessThan($column, $value); public function lessThanOrEqual($column, $value); public function like($column, $value); public function notLike($column, $value); public function match(array $columns, array $values); public function matchBoolean(array $columns, array $values); public function matchWithQueryExpansion(array $columns, array $values); public function in($column, array $values); public function notIn($column, array $values); public function between($column, $a, $b); public function notBetween($column, $a, $b); public function isNull($column); public function isNotNull($column); public function exists(Select $select); public function notExists(Select $select); public function addBitClause($column, $value); public function asLiteral($literal);
4.2. 更改 WHERE 逻辑运算符 ↑
WHERE
默认的运算符必须通过将逻辑运算符 OR
传递给 where
方法来更改。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->where('OR') ->equals('user_id', 1) ->like('name', '%N%') ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.* FROM user WHERE (user.user_id = :v1) OR (user.name LIKE :v2)
4.3. 使用 GROUP BY 和 HAVING 进行分组 ↑
连接多个 HAVING
条件时的默认逻辑运算符是 AND
。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns([ 'userId' => 'user_id', 'username' => 'name', 'email' => 'email', 'created_at' ]) ->groupBy(['user_id', 'name']) ->having() ->equals('user_id', 1) ->equals('user_id', 2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email', user.created_at FROM user GROUP BY user.user_id, user.name HAVING (user.user_id = :v1) AND (user.user_id = :v2)
4.3.1 可用的 HAVING 运算符 ↑
与 WHERE 语句中使用的相同运算符可用于 HAVING 操作。
4.4. 更改 HAVING 逻辑运算符 ↑
HAVING
默认的运算符必须通过将逻辑运算符 OR
传递给 having
方法来更改。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns([ 'userId' => 'user_id', 'username' => 'name', 'email' => 'email', 'created_at' ]) ->groupBy(['user_id', 'name']) ->having('OR') ->equals('user_id', 1) ->equals('user_id', 2); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email', user.created_at FROM user GROUP BY user.user_id, user.name HAVING (user.user_id = :v1) OR (user.user_id = :v2)
4.5. 作为 SELECT 语句的列 ↑
有时,需要将列设置为列。SQL 查询构建器也涵盖了这一点!请查看下面的示例。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $selectRole = $builder->select() ->setTable('role') ->setColumns(array('role_name')) ->limit(1) ->where() ->equals('role_id', 3); $query = $builder->select() ->setTable('user') ->setColumns(array('user_id', 'username')) ->setSelectAsColumn(array('user_role' => $selectRole)) ->setSelectAsColumn(array($selectRole)) ->where() ->equals('user_id', 4) ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id, user.username, ( SELECT role.role_name FROM role WHERE (role.role_id = :v1) LIMIT :v2, :v3 ) AS 'user_role', ( SELECT role.role_name FROM role WHERE (role.role_id = :v4) LIMIT :v5, :v6 ) AS 'role' FROM user WHERE (user.user_id = :v7)
4.6. 作为值的列 ↑
有时需要强制相同的列结构(例如:UNIONs),即使缺少列或值。使用值强制列可以解决这个问题。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(array('user_id', 'username')) ->setValueAsColumn('10', 'priority') ->where() ->equals('user_id', 1) ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id, user.username, :v1 AS 'priority' FROM user WHERE (user.user_id = :v2)
4.7. 使用函数的列 ↑
MAX 函数的示例。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(array('user_id', 'username')) ->setFunctionAsColumn('MAX', array('user_id'), 'max_id') ->where() ->equals('user_id', 1) ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id, user.username, MAX(user_id) AS 'max_id' FROM user WHERE (user.user_id = :v1)
CURRENT_TIMESTAMP 函数的示例。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(array('user_id', 'username')) ->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time') ->where() ->equals('user_id', 1) ->end(); $sql = $builder->writeFormatted($query); $values = $builder->getValues();
输出
SELECT user.user_id, user.username, CURRENT_TIMESTAMP AS 'server_time' FROM user WHERE (user.user_id = :v1)
5. 注释查询 ↑
查询构建器允许使用 setComment
方法向所有查询方法添加注释。
一些有用的用例示例可以
- 解释复杂查询或其存在的原因。
- 通过注释找到慢查询。
用法
<?php use Krinkle\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setComment('This is a comment'); $sql = $builder->write($query);
输出
-- This is a comment SELECT user.* FROM user
6. 质量代码 ↑
测试已使用 PHPUnit 和 Travis-CI 完成。所有代码都已测试,与 PHP 5.4 至 PHP 5.6 以及 HHVM 兼容。
要运行测试套件,您需要 Composer
php composer.phar install --dev php bin/phpunit
7. 作者 ↑
Nil Portugués Calderó
8. 许可证 ↑
SQL Query Builder 采用 MIT 许可证。
Copyright (c) 2014 Nil Portugués Calderó
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.