phossa2/query

一款针对PHP的简洁SQL查询构建库。

维护者

详细信息

github.com/phossa2/query

主页

源代码

问题

安装: 9

依赖: 0

建议者: 0

安全: 0

星标: 2

关注者: 3

分支: 0

开放问题: 0

2.0.0 2016-09-18 11:36 UTC

Requires

Requires (Dev)

Replaces

MIT 46dc922a2707e2ba17a9be755da2a597f781abb3

queryphossa

This package is not auto-updated.

Last update: 2024-09-14 19:53:03 UTC

README

Build Status Code Quality Code Climate PHP 7 ready HHVM Latest Stable Version License

phossa2/query 是一个PHP的简洁SQL查询构建库。它支持MySQL方言，并持续增加更多。

它需要PHP 5.4，支持PHP 7.0+和HHVM。它符合 PSR-1、PSR-2、PSR-3、PSR-4，以及提议的 PSR-5。

特性

安装

使用 composer 工具安装。

composer require "phossa2/query"

或者将以下行添加到您的 composer.json

{
    "require": {
       "phossa2/query": "2.*"
    }
}

用法

  • 入门

    首先使用查询构建器，然后进行查询。

    use Phossa2\Query\Builder;

// a builder default to table 'Users' and Mysql as default dialect
$builder = new Builder();
$users = $builder->table('Users');

// SELECT * FROM `Users` LIMIT 10
$sql = $users->select()->limit(10)->getSql();

// INSERT INTO `Users` (`usr_name`) VALUES ('phossa')
$sql = $users->insert(['usr_name' => 'phossa'])->getSql();

// reset builder to table 'Sales' as 's'
$sales = $users->table('Sales', 's');

// SELECT * FROM `Sales` AS `s` WHERE `user_id` = 12
$qry = $sales->select()->where('user_id', 12);

// SELECT * FROM `Sales` AS `s` WHERE `user_id` = ?
$sql = $qry->getStatement(); // with positioned parameters

// [12]
var_dump($qry->getBindings());

  • SELECT

    • 列/字段

      可以在 select($col, ...)、col($col, $alias) 或 col(array $cols) 中指定列。

      // SELECT * FROM `Users`
$qry = $users->select();

// SELECT `user_id`, `user_name` FROM `Users`
$qry = $users->select('user_id', 'user_name');

// SELECT `user_id`, `user_name` AS `n` FROM `Users`
$qry = $users->select()->col('user_id')->col('user_name', 'n');

// same as above
$qry = $users->select()->col(['user_id', 'user_name' => 'n']);

      可以使用 colRaw($string, array $parameters) 提供原始字符串。

      // SELECT COUNT(user_id) AS cnt FROM `Users`
$qry = $users->select()->colRaw('COUNT(user_id) AS cnt');

// SELECT CONCAT(user_name, 'x') AS con FROM `Users`
$qry = $users->select()->colRaw('CONCAT(user_name, ?) AS con', ['x']);

      可以直接使用如 cnt($col, $alias)、min($col, $alias)、max($col, $alias)、avg($col, $alias)、sum($col, $alias) 等常见函数。

      // SELECT MAX(`user_id`) AS `maxId` FROM `Users`
$qry = $users->select()->max('user_id', 'maxId');

      使用 colTpl($template, $cols, $alias) 通过通用列模板。

      // SELECT SUM(DISTINCT `score`) AS `s` FROM `Users`
$qry = $users->select()->colTpl('SUM(DISTINCT %s)', 'score', 's');

// SELECT CONCAT(`fname`, ' ', `lname`) AS `fullName` FROM `Users`
$qry = $users->select()->colTpl("CONCAT(%s, ' ', %s)", ['fname', 'lname'], 'fullName');

      子查询也可以在 col() 中使用。

      // SELECT (SELECT MAX(`user_id`) FROM `oldUsers`) AS `maxId` FROM `Users`
$qry = $users->select()->col(
    $users->select()->max('user_id')->table('oldUsers'),
    'maxId'
);

    • 唯一

      可以使用 distinct(...) 指定 DISTINCT。

      // SELECT DISTINCT `user_alias` FROM `Users`
$qry = $users->select()->distinct('user_alias');

// SELECT DISTINCT `user_alias` AS `a` FROM `Users`
$qry = $users->select()->distinct()->col('user_alias', 'a');

    • 从

      可以使用 from($table, $alias) 或 table($table, $alias) 与 $builder 对象或查询对象（如 $builder->select()）一起使用。

      使用 table() 替换任何现有表。

      // $sales is a clone of builder $users with table replaced
$sales = $users->table('Sales');

// or replace table in the select query object
$select = $users->select()->table('Sales', 's');

// SELECT * FROM `Users` AS `u`, `Accounts` AS `a`
$qry = $users->select()->table(['Users' => 'u', 'Accounts' => 'a']);

      使用 from() 添加到任何现有表。

      // SELECT * FROM `Users`, `Sales` AS `s`
$select = $users->select()->from('Sales', 's');

// builder has two tables now
$usersAndSales = $users->from('Sales', 's');

      子查询可以在 from() 或 table() 中使用。

      // SELECT * FROM (SELECT `user_id` FROM `oldUsers`) AS `u`
$qry = $users->select()->table(
    $users->select('user_id')->table('oldUsers'),
    'u'
);

    • 分组

      使用 group($col, ...) 进行分组。

      // SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id`
$qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->group('grp_id');

      多个 group() 和 groupRaw($str, array $params)。

      // SELECT `grp_id`, `age`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id`, age ASC
$qry = $users->select('grp_id', 'age')->cnt('*', 'cnt')
    ->group('grp_id')->groupRaw('age ASC');

      模板也可以与 groupTpl($template, $cols) 一起使用。

      // GROUP BY `year` WITH ROLLUP
$users->select()->groupTpl('%s WITH ROLLUP', 'year')

    • 连接

      使用 join($table, $col) 进行连接。

      // SELECT * FROM `Users` INNER JOIN `Accounts`
$qry = $users->select()->join('Accounts');

// SELECT * FROM `Users` INNER JOIN `Accounts` ON `Users`.`id` = `Accounts`.`id`
$qry = $users->select()->join('Accounts', 'id');

      指定连接表的别名。

      // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id`
$qry = $users->select()->join(['Accounts', 'a'], 'id');

      使用不同列名的连接表。

      // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`user_id`
$qry = $users->select()->join(['Accounts'], 'a'], ['id', 'user_id']);

// same as above
$qry = $users->select()->join(['Accounts'], 'a'], ['Users.id', 'a.user_id']);

      使用指定运算符的连接。

      // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` <> `a`.`user_id`
$qry = $users->select()->join(['Accounts', 'a'], ['id', '<>', 'user_id']);

      多个连接。

      // SELECT * FROM `Users`
// INNER JOIN `Sales` AS `s` ON `Users`.`uid` = `s`.`uid`
// INNER JOIN `Orders` AS `o` ON `Users`.`uid` = `o`.`oid`
$qry = $users->select()
            ->join(['Sales', 's'], ['uid', '=', 'uid'])
            ->join(['Orders', 'o'], ['uid', 'o.oid']);

      连接中的子查询。

      // SELECT * FROM `Users` INNER JOIN (SELECT `uid` FROM `oldUsers`) AS `x` ON `Users`.`uid` = `x`.`uid`
$qry = $users->select()->join(
    [$builder->select('uid')->from('oldUsers'), 'x'],
    'uid'
);

      支持其他连接 leftJoin()、rightJoin()、outerJoin()、leftOuterJoin()、rightOuterJoin()、crossJoin()。如果想要使用自己的连接，joinRaw() 很方便。

      // SELECT * FROM `Users` OUTER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id`
$qry = $users->select()->outerJoin(['Accounts', 'a'], 'id');

// SELECT * FROM `Users` NATURAL JOIN Accounts AS a ON Users.id = a.id
$qry = $users->select()->joinRaw('NATURAL JOIN', 'Accounts AS a ON Users.id = a.id');

    • 限制

      支持 LIMIT 和 OFFSET。

      // SELECT * FROM `Users` LIMIT 30 OFFSET 10
$qry = $users->select()->limit(30, 10);

// SELECT * FROM `Users` LIMIT 20 OFFSET 15
$qry = $users->select()->limit(20)->offset(15);

      或者使用 page($pageNum, $pageLength)，其中 $pageNum 从 1 开始。

      // SELECT * FROM `Users` LIMIT 30 OFFSET 60
$qry = $users->select()->page(3, 30);

    • 排序

      按升序或降序排序

      // SELECT * FROM `Users` ORDER BY `age` ASC, `score` DESC
$qry = $users->select()->order('age')->orderDesc('score');

      或原始模式

      // SELECT * FROM `Users` ORDER BY age ASC, score DESC
$qry = $users->select()->orderRaw('age ASC, score DESC');

    • 其中

      简单where子句

      // SELECT * FROM `Users` WHERE age > 18
$qry = $users->select()->where('age > 18');

// SELECT * FROM `Users` WHERE `age` = 18
$qry = $users->select()->where('age', 18);

// SELECT * FROM `Users` WHERE `age` < 18
$qry = $users->select()->where('age', '<', 18);

      多个where子句

      // SELECT * FROM `Users` WHERE `age` > 18 AND `gender` = 'male'
$qry = $users->select()->where(['age' => ['>', 18], 'gender' => 'male']);

// same as above
$qry = $users->select()->where('age', '>', 18)->andWhere('gender','male');

      复杂where子句

      // SELECT * FROM `Users` WHERE (`id` = 1 OR (`id` < 20 OR `id` > 100)) OR `name` = 'Tester'
$qry = $users->select()->where(
            $users->expr()->where('id', 1)->orWhere(
                $users->expr()->where('id', '<', 20)->orWhere('id', '>', 100)
            )
         )->orWhere('name', 'Tester');

      原始模式

      // SELECT * FROM `Users` WHERE age = 18 OR score > 90
$qry = $users->select()->whereRaw('age = 18')->orWhereRaw('score > 90');

      带有NOT

      // SELECT * FROM `Users` WHERE NOT `age` = 18 OR NOT `score` > 90
$qry = $users->select()->whereNot('age', 18)->orWhereNot('score', '>', 90);

      Where IN 和 BETWEEN

      // SELECT * FROM `Users` WHERE `age` IN (10,12,15,18,20)
$qry = $users->select()->where('age', 'IN', [10,12,15,18,20]);

// SELECT * FROM `Users` WHERE `age` NOT BETWEEN 10 AND 20
$qry = $users->select()->where('age', 'NOT BETWEEN', [10,20]);

      IS NULL,

      // SELECT * FROM `Users` WHERE `age` IS NULL
$qry = $users->select()->where('age', 'IS', NULL);

      EXISTS,

      // SELECT * FROM `Sales` WHERE EXISTS (SELECT `user_id` FROM `Users`)
$sql = $sales->select()->where('', 'EXISTS', $users->select('user_id'))->getSql();

    • Having

      类似于WHERE子句

      // SELECT * FROM `Users` HAVING `age` = 10 OR `level` > 20
$qry = $users->select()->having('age', 10)->orHaving('level', '>', 20);

    • 联合

      union() 或 unionAll() 可与构建器或查询对象一起使用

      // SELECT * FROM `Users`
// UNION
//     SELECT * FROM `oldUsers1`
// UNION ALL
//     SELECT `user_id` FROM `oldUsers2`
$sql = $users->select()
        ->union()
            ->select()->table('oldUsers1')
        ->unionAll()
            ->select('user_id')->table('oldUsers2')
        ->getSql()

// (SELECT * FROM `Users`) UNION (SELECT * FROM `oldUesrs`) ORDER BY `user_id` ASC LIMIT 10
$sql = $builder->union(
    $builder->select()->table('Users'),
    $builder->select()->table('oldUsers')
)->order('user_id')->limit(10)->getSql();

  • INSERT

    单个插入语句

    // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa')
$sql = $users->insert(['uid' => 2, 'uname' => 'phossa'])->getSql();

// same as above
$sql = $users->insert()->set('uid', 2)->set('uname', 'phossa')->getSql();

// same as above
$sql = $users->insert()->set(['uid' => 2, 'uname' => 'phossa'])->getSql();

    多行数据

    // INSERT INTO `Users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test')
$qry = $users->insert()
          ->set(['uid' => 2, 'uname' => 'phossa'])
          ->set(['uid' => 3, 'uname' => 'test']);

    使用DEFAULT值插入

    // INSERT INTO `Users` (`uid`, `uname`, `phone`) VALUES (2, 'phossa', DEFAULT), (3, 'test', '1234')
$qry = $users->insert([
    ['uid' => 2, 'uname' => 'phossa'],
    ['uid' => 3, 'uname' => 'test', 'phone' => '1234']
]);

    使用NULL代替默认值

    // INSERT INTO `Users` (`uid`, `uname`, `phone`) VALUES (2, 'phossa', NULL), (3, 'test', '1234')
$sql = $qry->getSql(['useNullAsDefault' => true]);

    使用SELECT子查询插入

    // INSERT INTO `Users` (`uid`, `uname`) SELECT `user_id`, `user_name` FROM `oldUsers`
$qry = $users->insert()->set(['uid', 'uname'])
    ->select('user_id', 'user_name')->table('oldUsers');

  • UPDATE

    常见更新语句

    // UPDATE `Users` SET age = age + 1
$qry = $users->update()->set('age = age + 1');

// UPDATE `Users` SET `user_name` = 'phossa' WHERE `user_id` = 3
$qry = $users->update(['user_name' => 'phossa'])->where('user_id', 3);

// UPDATE `Users` SET `user_name` = 'phossa', `user_addr` = 'xxx' WHERE `user_id` = 3
$qry = $users->update()->set('user_name','phossa')
    ->set('user_addr', 'xxx')->where('user_id', 3);

    increment($col, $step) 和 decrement($col, $step)

    // UPDATE `Users` SET `age` = `age` + 2 WHERE `user_id` = 2
$qry = $users->update()->increment('age', 2)->where('user_id', 2);

    使用Mysql扩展

    // UPDATE IGNORE `Users` SET `user_id` = `user_id` + 10, `user_status` = user_status | 2 ORDER BY `user_id` ASC LIMIT 10
$qry = $users->update()->hint('IGNORE')
    ->setTpl('user_id', '%s + ?', 'user_id', [10])
    ->setRaw('user_status', 'user_status | 2')
    ->order('user_id')->limit(10);

  • REPLACE

    Mysql版本的replace

    // REPLACE INTO `Users` (`user_id`, `user_name`) VALUES (3, 'phossa')
$qry = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);

  • DELETE

    单表删除

    // DELETE FROM `Users` WHERE `user_id` > 10 ORDER BY `user_id` ASC LIMIT 10
$qry = $users->delete()->where('user_id', '>', 10)
    ->order('user_id')->limit(10);

    多表删除

    // DELETE `u`, `a` FROM `Users` AS `u` INNER JOIN `Accounts` AS `a`
// ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 10
$qry = $builder->delete('u', 'a')->table('Users', 'u')
    ->join(['Accounts', 'a'], 'user_id')->where('a.total_amount', '<', 10);

高级主题

  • expr()

    表达式可用于构建复杂的WHERE

    // SELECT
//     *
// FROM
//     "Users"
// WHERE
//    ("age" < 18 OR "gender" = 'female')
//    OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female'))
$qry = $builder->select()->table('Users')->where(
    $builder->expr()->where('age', '<', 18)->orWhere('gender', 'female')
)->orWhere(
    $builder->expr()->where('age', '>' , 60)->orWhere(
        $builder->expr()->where('age', '>', 55)->where('gender', 'female')
    )
);

    与复杂的ON连接

    // SELECT * FROM `Users` INNER JOIN `Sales`
// (ON `Users`.`uid` = `Sales`.`s_uid` OR `Users`.`uid` = `Sales`.`puid`)
$sql = $users->select()->join('Sales',
    $builder->expr()->on('Users.uid', 'Sales.s_uid')->orOn('Users.uid', 'Sales.puid')
)->getSql();

  • raw()

    使用原始字符串绕过引号和转义

    // SELECT score + 10 FROM `Students` WHERE `time` < NOW()
$qry = $builder->select()->colRaw('score + 10')
    ->from("Students")->where('time', '<', $builder->raw('NOW()'));

// SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY grp_id ASC
$qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->groupRaw('grp_id ASC');

    带有定位参数的原始字符串

    // SELECT * FROM `Students` WHERE `age` IN RANGE(1, 1.2)
$qry = $builder->select()->from("Students")->where("age", "IN",
    $builder->raw('RANGE(?, ?)', [1, 1.2]));

// same as above
$qry = $builder->select()->from("Students")
  ->whereRaw("`age` IN RANGE(?, ?)", [1, 1.2]);

  • 模板（使用colTpl()、groupTpl()等）

    使用模板可以使数据库名称的引号成为可能

    // SELECT MAX(`score`) AS max FROM `Users`
$sql = $users->select()->colTpl('MAX(%s)', 'score', 'max')->getSql();

  • before()、after()、hint() 和 option()

    有时，可能需要非标准SQL，但找不到方法。此时，before() 和 after() 将会提供帮助。

    // INSERT INTO "users" ("id", "name") VALUES (3, 'phossa') ON DUPLICATE KEY UPDATE id=id+10
$qry = $users->insert()->set('id', 3)->set('name', 'phossa')
    ->after('VALUES', 'ON DUPLICATE KEY UPDATE id=id+?', [10]);

    hint() 在语句词后添加提示，而 option() 将附加到SQL末尾

    // INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa') ON DUPLICATE KEY UPDATE id=id+10
$qry = $users->insert()->hint('IGNORE')
    ->set('id', 3)->set('name', 'phossa')
    ->option('ON DUPLICATE KEY UPDATE id=id+?', [10]);

  • 参数

    phossa2/query 可以返回用于驱动程序准备和使用的语句，并使用 getBindings() 获取要绑定的值。

    $qry = $users->select()->where("user_id", 10);

// SELECT * FROM `Users` WHERE `user_id` = ?
$sql = $qry->getStatement();

// values to bind: [10]
$val = $qry->getBindings();

    或命名参数

    $qry = $users->select()->where("user_name", ':name');

// SELECT * FROM `Users` WHERE `user_name` = :name
$sql = $query->getNamedStatement();

    参数可以应用于原始或模板方法

    // SELECT * FROM `Users` GROUP BY `year` + 10
$sql = $users->select()->groupRaw('`year` + ?', [10])->getSql();

// same as above
$sql = $users->select()->groupTpl('%s + ?', 'year', [10])->getSql();

  • 设置

    设置可以在实例化时应用于 $builder 或使用 setSettings() 应用

    // builder instantiation
$users = new Builder('Users', new Mysql(), ['autoQuote' => false]);

// adjust settings
$users->setSettings(['autoQuote' => true]);

    或在输出 getSql() 或 getStatement() 时应用

    $sql = $users->select()->getSql(['autoQuote' => false]);

    缩进SQL

    // SELECT
//     *
// FROM
//     `Users`
$sql = $users->select()->getSql(['seperator' => "\n", 'indent' => "    "]);

    设置列表

    • autoQuote: boolean. 是否引用数据库标识符。

    • positionedParam: boolean. 是否输出带定位参数的SQL。

    • namedParam: boolean. 是否输出带命名参数的SQL。

    • seperator: string，默认为' '。子句之间的分隔符。

    • indent: string，默认为''。子句的缩进前缀。

    • escapeFunction: 可调用对象，默认为null。用于引号和转义值的函数。

    • useNullAsDefault: boolean。

变更日志

请参阅CHANGELOG 以获取更多信息。

测试

$ composer test

贡献

请参阅CONTRIBUTE 以获取更多信息。

依赖项

  • PHP >= 5.4.0

  • phossa2/shared >= 2.0.21

许可证

MIT许可证