通过
搜索
搜索tkaratug / titandb-2
一个轻量级且高效的SQL查询构建器库
v2.0.0
2017-11-07 11:21 UTC
Requires
- php: >=5.4.0
This package is auto-updated.
Last update: 2024-08-29 04:43:03 UTC
README
一个轻量级且高效的PHP SQL查询构建器。
安装
推荐通过Composer安装TitanDB v2查询构建器。运行以下命令进行安装：
$ composer require tkaratug/titandb-2
连接
require 'vendor/autoload.php'; use Titan\DB as DB; $db = DB::init([ 'db_driver' => 'mysql', 'db_host' => 'localhost', 'db_user' => 'sample_db_user', 'db_pass' => '', 'db_name' => 'sample_db_name', 'db_charset' => 'utf8', 'db_collation' => 'utf8_general_ci', 'db_prefix' => '' ]);
内容
- SELECT
- 获取多行数据
- 获取单行数据
- WHERE
- GROUPING WHERE
- JOIN
- ORDER BY & LIMIT
- GROUP BY
- HAVING
- GROUPING HAVING
- LIKE & NOT LIKE
- IN & NOT IN
- BETWEEN & NOT BETWEEN
- INSERT
- UPDATE
- DELETE
- 最后插入的ID
- 行数
- 自定义查询
select
$db->table('test_table')->select('title, content'); // Output: "SELECT title, content FROM test_table" $db->table('test_table')->select('title as t, content as c'); // Output: "SELECT title as t, content as c FROM test_table"
获取多行数据
$db->table('test_table')->getAll(); // Output: "SELECT * FROM test_table" $db->table('test_table')->select('title, content')->getAll(); // Output: "SELECT title, content FROM test_table"
获取单行数据
$db->table('test_table')->where('status', '=', 1)->getRow(); // Output: "SELECT * FROM test_table WHERE status = 1" $db->table('test_table')->select('title, content')->where('status', '=', 1)->getRow(); // Output: "SELECT title, content FROM test_table WHERE status = 1"
where
$db->table('test_table') ->select('title, content') ->where('id', '=', 5) ->getRow(); // Output: "SELECT title, content FROM test_table WHERE id = 5" $db->table('test_table') ->select('title, content') ->where('vote', '>', 20) ->where('status', '=', 1) ->getAll(); // Output: "SELECT title, content FROM test_table WHERE vote>20 AND status=1" $db->table('test_table') ->select('title, content') ->where('vote', '>', 20) ->or_where('create_date', '>', date('Y-m-d')) ->getAll(); // Output: "SELECT title, content FROM test_table WHERE vote>20 OR create_date>'2017-11-07'"
GROUPING WHERE
$db->table('test_table') ->select('title, content') ->where('col_1', '>', 5) ->whereGroupStart() ->where('col_2', '=', 'val_2') ->orWhere('col_2', '=', 'val_3') ->whereGroupEnd() ->getAll(); // Output: "SELECT title, content FROM test_table WHERE col_1>5 AND (col_2='val_2' OR col_2='val_3')" $db->table('test_table') ->select('title, content') ->where('col_1', '>', 5) ->whereGroupStart('OR') ->where('col_2', '=', 'val_2') ->orWhere('col_2', '=', 'val_3') ->whereGroupEnd() ->getAll(); // Output: "SELECT title, content FROM test_table WHERE col_1>5 OR (col_2='val_2' OR col_2='val_3')"
join
$db->table('users as t1') ->leftJoin('comments as t2', 't1.user_id=t2.user_id') ->select('t1.username, t2.comment') ->getAll(); // Output: "SELECT t1.username, t2.comment FROM users as t1 LEFT JOIN comments as t2 ON t1.user_id=t2.user_id" $db->table('users as t1') ->rightJoin('comments as t2', 't1.user_id=t2.user_id') ->select('t1.username, t2.comment') ->getAll(); // Output: "SELECT t1.username, t2.comment FROM users as t1 RIGHT JOIN comments as t2 ON t1.user_id=t2.user_id" $db->table('users as t1') ->innerJoin('comments as t2', 't1.user_id=t2.user_id') ->select('t1.username, t2.comment') ->getAll(); // Output: "SELECT t1.username, t2.comment FROM users as t1 INNER JOIN comments as t2 ON t1.user_id=t2.user_id" $db->table('users as t1') ->outerJoin('comments as t2', 't1.user_id=t2.user_id') ->select('t1.username, t2.comment') ->getAll(); // Output: "SELECT t1.username, t2.comment FROM users as t1 FULL OUTER JOIN comments as t2 ON t1.user_id=t2.user_id"
order by & limit
$db->table('test_table')->orderBy('id')->getAll(); // Output: "SELECT * FROM test_table ORDER BY id ASC" $db->table('test_table')->orderBy('id', 'desc')->getAll(); // Output: "SELECT * FROM test_table ORDER BY id DESC" $db->table('test_table')->orderBy('id', 'desc')->limit(100)->getAll(); // Output: "SELECT * FROM test_table ORDER BY id DESC LIMIT 100" $db->table('test_table')->order_by('id')->limit(100, 50)->getAll(); // Output: "SELECT * FROM test_table ORDER BY id ASC LIMIT 100, 50"
group by
$db->table('test_table')->select('book, COUNT(*)')->groupBy('book')->getAll(); // Output: "SELECT book, COUNT(*) FROM test_table GROUP BY book"
having
$db->table('test_table') ->select('book, COUNT(*)') ->groupBy('book') ->having('COUNT(*)', '>', 15) ->getAll(); // Output: "SELECT book, COUNT(*) FROM test_table GROUP BY book HAVING COUNT(*)>15" $db->table('test_table') ->select('book, COUNT(*)') ->groupBy('book') ->having('COUNT(*)', '>', 15) ->having('COUNT(*)', '<', 50) ->getAll(); // Output: "SELECT book, COUNT(*) FROM test_table GROUP BY book HAVING COUNT(*)>15 AND COUNT(*)<50" $db->table('test_table') ->select('book, COUNT(*)') ->groupBy('book') ->having('COUNT(*)', '>', 15) ->orHaving('MAX(price)', '<', 50) ->getAll(); // Output: "SELECT book, COUNT(*) GROUP BY book HAVING COUNT(*)>15 OR MAX(price)<50"
like & not like
$db->table('test_table')->select('title, content')->like('title', 'Lorem%')->getAll(); // Output: "SELECT title, content FROM test_table WHERE title LIKE 'Lorem%'" $db->table('test_table')->select('title, content')->like('title', 'Lorem%')->like('content', '%amet')->getAll(); // Output: "SELECT title, content FROM test_table WHERE title LIKE 'Lorem%' AND content LIKE '%amet'" $db->table('test_table')->select('title, content')->like('title', 'Lorem%')->orLike('title', 'ipsum%')->getAll(); // Output: "SELECT title, content FROM test_table WHERE title LIKE 'Lorem%' OR title LIKE 'ipsum%'" $db->table('test_table')->select('title, content')->notLike('title', 'Lorem%')->getAll(); // Output: "SELECT title, content FROM test_table WHERE title NOT LIKE 'Lorem%'" $db->table('test_table')->select('title, content')->notLike('title', 'Lorem%')->orNotLike('title', 'ipsum%')->getAll(); // Output: "SELECT title, content FROM test_table WHERE title NOT LIKE 'Lorem%' OR title NOT LIKE 'ipsum%'"
in & not in
$db->table('test_table')->select('title, content')->in('categoryId', [1,3,4,6])->getAll(); // Output: "SELECT title, content FROM test_table WHERE categoryId IN(1, 3, 4, 6)" $db->table('test_table')->select('title, content')->notIn('categoryId', [1,3,4,6])->getAll(); // Output: "SELECT title, content FROM test_table WHERE categoryId NOT IN(1, 3, 4, 6)"
between & not between
$db->table('users')->select('userId, userName')->between('userAge', 20, 30)->getAll(); // Output: "SELECT userId, userName FROM users WHERE userAge BETWEEN 20 AND 30" $db->table('users')->select('userId, userName')->notBetween('userAge', 20, 30)->getAll(); // Output: "SELECT userId, userName FROM users WHERE userAge NOT BETWEEN 20 AND 30"
insert
$data = [ 'firstName' => 'John', 'lastName' => 'Doe', 'city' => 34 ]; $db->table('users')->insert($data); // Output: "INSERT INTO users SET firstName='John', lastName='Doe', city=34"
update
$data = [ 'firstName' => 'John', 'lastName' => 'Doe', 'city' => 34 ]; $db->table('users')->where('id', '=', 5)->update($data); // Output: "UPDATE users SET firstName='John', lastName='Doe', city=34 WHERE id=5"
delete
$db->table('users')->where('id', '=', 5)->delete(); // Output: "DELETE FROM users WHERE id=5"
最后插入的ID
$data = [ 'firstName' => 'John', 'lastName' => 'Doe', 'city' => 34 ]; $db->table('users')->insert($data); echo $db->lastInsertId();
行数
$db->table('users')->where('age', '>', 20)->getAll(); echo $db->numRows();
最后执行的查询
echo $db->lastQuery();
执行自定义查询
// Fetching single row $db->customQuery("SELECT * FROM test_table WHERE id=5")->getRow(); // Fetching multiple rows $db->customQuery("SELECT * FROM test_table")->getAll(); // Insert $db->customQuery("INSERT INTO test_table SET col_1='val_1', col_2='val_2'"); // Update $db->customQuery("UPDATE test_table SET col_1='val_1', col_2='val_2' WHERE id=5"); // Delete $db->customQuery("DELETE FROM test_table WHERE id=5"); // Execute Stored Procedure $db->customQuery("CALL procedure_1()");