README

功能强大且轻量级的PHP SQL查询构建器，具有流畅的界面SQL语法，使用绑定和复杂的查询生成

特性

• 支持多个数据库（多个PDO实例）
• INSERT、INSERT IGNORE、INSERT DELAYED、UPDATE、SELECT、DELETE查询
• 支持LEFT JOIN、INNER JOIN、RIGHT JOIN、GROUP BY、LIMIT、HAVING等
• =、!=、>、<、>=、<=、IN、NOT IN、IS NULL、NOT NULL运算符
• 支持事务和子查询
• 支持结果缓存（《\Psr\SimpleCache\CacheInterface》）
• 支持多种数据检索模式（检索数组、检索对象等）
• 自动转义列名
• 自动格式化查询
• 自动绑定变量
• 对象化（自动将注释和属性类型从类型转换为类型）。见《demo/hydration.php》

快速安装

推荐通过Composer安装QueryBuilder。运行以下命令进行安装

composer require hemiframe/php-query-builder

设置默认PDO实例

<?php

//Create PDO instance
$pdo = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec("set names utf8");

// Set as default for all query instances
\HemiFrame\Lib\SQLBuilder\Query::$global['pdo'] = $pdo;

$query = new \HemiFrame\Lib\SQLBuilder\Query(); //Your query
$query->execute();

文档

选择查询

<?php

$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->leftJoin("details", "d", "d.userId=u.id");
$query->andWhere("u.status", 0);
$query->andWhere("u.id", [1, 2, 3]);
$query->andWhere("u.age", null);
$query->andWhere("u.gender", null, '!=');
$query->orderBy("u.id DESC");
$query->groupBy("u.id");
$query->paginationLimit(1, 10);

输出

SELECT
  u.id
  ,u.email
  ,u.name
FROM
  users AS u
LEFT JOIN details AS d
  ON d.userId=u.id
WHERE
  u.status=0
  AND u.id IN (1,2,3)
  AND u.age IS NULL
  AND u.gender IS NOT NULL
GROUP BY u.id
ORDER BY u.id DESC
LIMIT 0, 10

从子查询中选择查询

<?php

$queryInner = new \HemiFrame\Lib\SQLBuilder\Query();
$queryInner->select()->from("user");
$queryInner->andWhere("isActive", 1);

$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.name",
])->from($queryInner, "u");
$query->andWhere("status", 2, '!=');
$query->limit("1000");

输出

SELECT
  u.id
  ,u.name
FROM
  (SELECT
  *
FROM
  user
WHERE
  isActive=1) AS u
WHERE
  `status`!=2
LIMIT 1000

插入查询

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->insertInto("users")->set([
    "name" => 'Test',
    "email" => 'test@test.com',
]);
$query->onDuplicateKeyUpdate("`email`=:testVar")->setVar('testVar', 'testemail@test.com');

输出

INSERT INTO
  users
SET
  `name`="Test"
  ,`email`="test@test.com"
ON DUPLICATE KEY UPDATE
  `email`="testemail@test.com"

带有VALUES的插入查询

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->insertInto("users")->values([
    'name',
    'email',
    'age',
], [
    [
        'name 1',
        'email 1',
        '15',
    ],
    [
        'name 2',
        'email 2',
        '20',
    ],
]);
$query->onDuplicateKeyUpdate("email=:testVar")->setVar('testVar', 'testemail@test.com');

输出

INSERT INTO
  users
  (`name`,`email`,`age`)
VALUES
  ("name 1","email 1","15")
  ,("name 2","email 2","20")
ON DUPLICATE KEY UPDATE
  `email`="testemail@test.com"

更新查询

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->update("users")->set([
    "name" => 'Test',
    "email" => 'test@test.com',
]);
$query->set('totalViews = totalViews + 1');
$query->andWhere("status", 2, '!=');
$query->andWhere("id", [1, 2, 3]);
$query->andWhere("id", [10, 20, 30], '!=');

输出

UPDATE
  users
SET
  `name`="Test"
  ,`email`="test@test.com"
  ,totalViews = totalViews + 1
WHERE
  `status`!=2
  AND `id` IN (1,2,3)
  AND `id` NOT IN (10,20,30)

删除查询

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->delete()->from("users");
$query->andWhere("status", 2, '!=');
$query->andWhere("id", [1, 2, 3]);
$query->andWhere("id", [10, 20, 30], '!=');
$query->limit("1000");

输出

DELETE FROM
  users
WHERE
  `status`!=2
  AND `id` IN (1,2,3)
  AND `id` NOT IN (10,20,30)
LIMIT 1000

带有连接的删除查询

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->delete("u")->from("users", "u");
$query->leftJoin("emails", "e", "e.userId=u.id");
$query->andWhere("e.status", 2, '!=');
$query->andWhere("u.id", [1, 2, 3]);
$query->andWhere("u.id", [10, 20, 30], '!=');
$query->andWhere("e.status", 1);
$query->limit("1000");

输出

DELETE
  u
FROM
  users AS u
LEFT JOIN emails AS e
  ON e.userId=u.id
WHERE
  e.status!=2
  AND u.id IN (1,2,3)
  AND u.id NOT IN (10,20,30)
  AND e.status=1
LIMIT 1000

检索数据

以数组数组的形式检索数据

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$rows = $query->fetchArrays();

以数组对象的形式检索数据

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$rows = $query->fetchObjects();

以数组形式检索第一个结果

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$row = $query->fetchFirstArray();

以对象形式检索第一个结果

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$row = $query->fetchFirstObject();

使用多个数据库

<?php
$pdo1 = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test');
$pdo2 = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test');

$query = new \HemiFrame\Lib\SQLBuilder\Query([
    'pdo' => $pdo1,
]);
$query->select([
    "u.id",
])->from("users", "u");

$queryArticles = new \HemiFrame\Lib\SQLBuilder\Query([
    'pdo' => $pdo2,
]);
$queryArticles->select([
    "a.id",
])->from("articles", "a");