phossa/phossa-query

PHP SQL 查询构建器库

维护者

详细信息

github.com/phossa/phossa-query

主页

源代码

问题

安装: 32

依赖: 0

建议: 0

安全: 0

星标: 1

关注者: 2

分支: 1

开放问题: 3

1.0.4 2016-05-27 06:49 UTC

Requires

MIT 6f82bf65e79b92e6f40b02d71715fee30a1c94fc

sqlqueryquery builderphossa

This package is not auto-updated.

Last update: 2024-09-14 19:20:00 UTC

README

Build Status HHVM Latest Stable Version License

phossa-query 是一个具有简洁语法的 PHP SQL 查询构建器库，支持 Mysql、SQLite、Postgres、Sql server、Oracle 等。

它需要 PHP 5.4，并支持 PHP 7.0+、HHVM。它符合 PSR-1、PSR-2、PSR-4。

功能

安装

通过 composer 工具安装。

composer require "phossa/phossa-query=1.*"

或者在您的 composer.json 中添加以下行

{
    "require": {
        "phossa/phossa-query": "^1.0.0"
    }
}

用法

  • 入门

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

    use Phossa\Query\Builder;
use Phossa\Query\Dialect\Mysql;

// a builder with mysql dialect,default 'users' table
$users = new Builder(new Mysql(), 'users');

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

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

// A new builder (cloned) with default table 'sales'
$sales = $users->table(['sales' => 's']);
$query = $sales->select()->where('user_id', 12);

// SELECT * FROM `sales` AS `s` WHERE `user_id` = ?
$sql = $query->getStatement(['positionedParam' => true]);

// value bindings: [12]
$val = $query->getBindings();

  • SELECT

    • 列

      列可以指定在 select() 或 col()（或其别名 field()）中。

      带可选别名的列，

      // SELECT `user_name` AS `n` FROM `users`
$query = $users->select('user_name', 'n');

      多列，

      // SELECT `id`, `user_name` AS `n` FROM `users`
$query = $users->select()->col(['id', 'user_name' => 'n']);

// same as above
$query = $users->select()->col('id')->field('user_name', 'n');

      原始模式，

      // SELECT COUNT(user_id) AS `cnt` FROM `users`
$query = $users->select()->colRaw(['COUNT(user_id)' => 'cnt']);

      在列中可以使用常见的函数如 count()、min()、max()、avg()、sum() 和 sumDistinct()。

      // SELECT COUNT(`user_id`) AS `cnt`, MAX(`user_id`) AS `max_id` FROM `users`
$query = $users->select()->count('user_id', 'cnt')->max('user_id', 'max_id');

      使用 func($template, $colName, $colAlias) 进行通用函数，

      // SELECT CONCAT(`user_name`, "XXX") AS `new_name` FROM `users`
$query = $users->select()->func('CONCAT(%s, "XXX")', 'user_name', 'new_name');

    • 唯一

      可以通过 distinct() 指定 DISTINCT。

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

    • From

      FROM 可以与构建器对象或选择对象一起使用。

      使用 select(false) 忽略构建器的默认表，

      // SELECT * FROM `sales` AS `s`
$query = $users->select(false)->from('sales', 's');
    
  Builder tables are carried over,

  ```php
  // SELECT * FROM `users`, `sales`
  $query = $users->select()->from('sales');

    支持多表（带别名），

    // SELECT * FROM `users` AS `u`, `accounts` AS `a`
$query = $users->select()->from(['users' => 'u', 'accounts' => 'a']);

    在 from() 中可以使用子查询，

    // builder without default table[s]
$builder = $users->table(false);

// SELECT * FROM (SELECT `user_id` FROM `oldusers`) AS `u`
$query = $builder->select()->from(
    $builder->select('user_id')->from('oldusers'), 'u'
);

    • Group by

      单个 GROUP BY，

      // SELECT `group_id`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`
$query = $users->select()->col('group_id')->count('*', 'cnt')->groupBy('group_id');

      可以使用多个 groupBy() 和原始模式，

      // SELECT `group_id`, `age`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`, age ASC
$query = $users->select()->col('group_id')->col('age')->count('*', 'cnt')
    ->groupBy('group_id')->groupByRaw('age ASC');

    • Join

      与具有相同列名的另一张表进行连接，

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

      指定连接表的别名，

      // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
$query = $users->select()->join('accounts a', 'id');

      与不同列名的连接表，

      // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`user_id`
$query = $users->select()->join('accounts a', 'id', 'user_id');

      指定运算符的连接，

      // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` <> `a`.`user_id`
$query = $users->select()->join('accounts a', 'id', '<>', 'user_id');

      支持多连接，

      // SELECT * FROM `users`
// INNER JOIN `sales` AS `s` ON `users`.`uid` = `s`.`uid`
// INNER JOIN `order` AS `o` ON `users`.`uid` = `o`.`o_uid`
$query = $users->select()
            ->join('sales s', 'uid', '=', 'uid')
            ->join('order o', 'uid', 'o_uid')
            ->getStatement();

      连接中的子查询，

      // SELECT * FROM `users` INNER JOIN (SELECT `uid` FROM `oldusers`) AS `x`
// ON `users`.`uid` = `x`.`uid`
$query = $users->select()->join(
    $builder->select('uid')->from('oldusers')->alias('x'),
    'uid'
);

      支持其他连接 outerJoin()、leftJoin()、leftOuterJoin()、rightJoin()、rightOuterJoin()、fullOuterJoin()、crossJoin()。如果想要使用自己的连接，realJoin() 非常有用。

      // SELECT * FROM `users` OUTER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
$query = $users->select()->outerJoin('accounts a', 'id');

// SELECT * FROM `users` NATURAL JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
$query = $users->select()->realJoin('NATURAL', 'accounts a', 'id');

    • Limit

      支持 LIMIT 和 OFFSET，

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

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

      或者使用 page($pageNum, $pageLength)，

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

    • Order by

      按 ASC 或 DESC 排序，

      // SELECT * FROM `users` ORDER BY `age` ASC, `score` DESC
$query = $users->select()->orderByAsc('age')->orderByDesc('score');

      或者原始模式，

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

    • Where

      简单的 Where，

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

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

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

      多 Where，

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

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

      复杂的 Where，

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

      原始模式，

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

      带 NOT，

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

      Where IN 和 BETWEEN，

      // SELECT * FROM `users` WHERE `age` IN (10,12,15,18,20) OR `score` NOT BETWEEN 90 AND 100
$query = $users->select()->whereIn('age', [10,12,15,18,20])
        ->orWhereNotBetween('score', 90, 100);

      Where IS NULL 和 IS NOT NULL。

      // SELECT * FROM `users` WHERE `age` IS NULL OR `score` IS NOT NULL
$query = $users->select()->whereNull('age')->orWhereNotNull('score');

      存在,

      $qry1  = $users->select('user_id')->where('age', '>', 60);
$sales = $users->table('sales');

// SELECT * FROM `sales` WHERE EXISTS (SELECT `user_id` FROM `users`
// WHERE `age` > 60)
$sql = $sales->select()->whereExists($qry1)->getStatement();

    • 拥有

      类似于 WHERE 子句,

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

    • 并集

      // SELECT * FROM `users` UNION SELECT * FROM `oldusers1`
// UNION ALL SELECT `user_id` FROM `oldusers2`
$sql = $users->select()
        ->union()
            ->select()->from('oldusers1')
        ->unionAll()
            ->select('user_id')->from('oldusers2')
            ->getStatement()

  • INSERT

    单个插入语句,

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

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

    多行数据,

    // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test')
$query = $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')
$query = $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 = $query->getStatement(['useNullAsDefault' => true]);

    使用 SELECT 子查询进行插入,

    // INSERT INTO `users` (`uid`, `uname`)
// SELECT `user_id`, `user_name` FROM `oldusers`
$query = $users->insert()->set(['uid', 'uname'])
             ->select(['user_id', 'user_name'])
             ->from('oldusers');

  • UPDATE

    常见的更新语句,

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

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

    带有 Mysql 扩展,

    // UPDATE IGNORE `users` SET `user_id` = user_id + 10 ORDER BY `user_id` ASC LIMIT 10
$query = $users->update()->addHint('IGNORE')->set('user_id', $builder->raw('user_id + 10'))
    ->orderByASC('user_id')->limit(10);

  • REPLACE

    Mysql 版本的 replace,

    // REPLACE LOW_PRIORITY INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
$query = $users->replace(['user_id' => 3, 'user_name' => 'phossa'])
    ->addHint('low_priority');

    Sqlite 版本的 replace,

    // INSERT INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
// ON CONFLICT REPLACE
$query = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);

  • DELETE

    单表删除,

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

    多表删除

    // DELETE `users`.* FROM `users` AS `u` INNER JOIN `accounts` AS `a`
// ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 0
$query = $users->delete('users')->from('users', 'u')
    ->join('accounts a', 'user_id')->where('a.total_amount', '<', 0);

  • CREATE TABLE

    创建表在大多数 ORM 库中被使用。

    $builder = new Builder();

$builder->create()->table('new_table')
    ->temp()
    ->ifNotExists()
    ->addCol('id', 'INT')
        ->notNull()
        ->autoIncrement()
    ->addCol('name', 'VARCHAR(20)')
        ->notNull()
        ->unique()
        ->defaultValue('NONAME')
    ->addCol('alias', 'VARCHAR(10)')
        ->colConstraint('CHECK ()')
    ->primaryKey(['id'])
    ->uniqueKey(['name(4) ASC', 'alias'], 'ON CONFLICT REPLACE')
    ->uniqueKey(['id', 'alias'], 'ON CONFLICT ROLLBACK')
    ->constraint('FOREIGN KEY (...)')
    ->tblOption('DELAY_KEY_WRITE=1')
    ->tblOption('MAX_ROWS=100')
    ->getStatement([
        'seperator' => "\n",
        'indent'    => "  ",
    ]);

    以下输出结果,

    CREATE TEMPORARY TABLE IF NOT EXISTS "new_table"
(
  "id" INT NOT NULL AUTO_INCREMENT,
  "name" VARCHAR(20) NOT NULL DEFAULT 'NONAME' UNIQUE,
  "alias" VARCHAR(10) CHECK (),
  PRIMARY KEY ("id"),
  UNIQUE ("name"(4) ASC, "alias") ON CONFLICT REPLACE,
  UNIQUE ("id", "alias") ON CONFLICT ROLLBACK,
  FOREIGN KEY (...)
)
  DELAY_KEY_WRITE=1,
  MAX_ROWS=100

高级主题

  • expr()

    表达式可以用来构建复杂的 WHERE

    // SELECT
//     *
// FROM
//     "Users"
// WHERE
//    ("age" < 18 OR "gender" = 'female')
//    OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female'))
$query = $builder->select()->from('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 连接,

    $builder = $users->table(false);

// 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')
)->getStatement();

  • raw()

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

    // SELECT id FROM "students" WHERE "time" = NOW()
$query = $builder->select()->field($builder->raw("id"))
    ->from("students")->where("time", $builder->raw('NOW()'));

    带参数的原始字符串,

    // SELECT * FROM "students" WHERE "age" IN RANGE(1, 1.2)
$query = $builder->select()->from("students")->where("age", "IN",
    $builder->raw('RANGE(?, ?)', 1, 1.2));

  • before() 和 after()

    有时，需要非标准的 SQL 而没有找到方法。 before() 和 after() 将会救命。

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

  • 参数

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

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

// SELECT * FROM "users" WHERE "user_id" = ?
$sql = $query->getPositionedStatement();

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

    或命名参数,

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

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

  • 设置

    设置可以在实例化时应用于 $builder，

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

    或者在调用 getStatement() 输出时应用,

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

    设置列表,

    • autoQuote: 布尔值。是否引号数据库标识符。

    • positionedParam: 布尔值。是否使用定位参数。

    • namedParam: 布尔值。是否使用命名参数。

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

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

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

    • useNullAsDefault: 布尔值。

方言

  • Mysql

依赖

  • PHP >= 5.4.0

  • phossa/phossa-shared 1.*

许可

MIT 许可证