manekshms/db2util

DB2数据库实用工具和查询构建器

维护者

详细信息

github.com/manekshms/DB2Util

源代码

问题

安装: 12

依赖项: 0

建议者: 0

安全性: 0

星标: 9

关注者: 7

分支: 2

开放问题: 0

1.0.0 2019-04-16 18:45 UTC

Requires

  • php: >=5.4

Requires (Dev)

MIT 1bb3657a0bc68fbf16981c5a6ef769ed8bcf599d

This package is auto-updated.

Last update: 2024-09-17 07:16:20 UTC

README

Total Downloads Latest Stable Version

db2的数据库实用工具和查询构建器

目录

需求

  • php >= 5.4
  • 启用php扩展 pdo_ibm

安装

使用以下命令安装最新版本

composer require manekshms/db2util

基本用法

配置和连接

    $config = [];
    $config['dsn'] = 'ibm:dbname';
    $config['username'] = 'db2admin';
    $config['password'] = 'db2admin';
    $db2Util = new DB2Util\DB2Util($config);
    $db2Util->connect();

选择数据

从表中选择所有数据

$db2Util->getQueryBuilder()->table('table_name')->get();

带有WHERE条件的查询数据

$db2Util->getQueryBuilder()->table('table_name')->where('column-name' , 'column-value')->get(); 示例

$db2Util->getQueryBuilder()->table('table_name')->where('name' , 'bob')->get();

带有多个列WHERE条件的查询数据

$db2Util->getQueryBuilder()->table('table_name')->where(['column-name' => 'column-value'])->get(); 示例

$db2Util->getQueryBuilder()->table('table_name')->where(['name' => 'bob', 'age' => 40])->get();

带有WHERE条件和运算符的查询
$db2Util->getQueryBuilder()->table('table_name')->where('column-name', 'operators', 'values')->get();
示例

$db2Util->getQueryBuilder()->table('table_name')->where('AGE', '>=', 40)->get();

对多个列进行检查的WHERE条件查询
$db2Util->getQueryBuilder()->table('user')->where([ array of column name operator and value ])->get();

示例

$db2Util->getQueryBuilder()->table('user')->where([ ['AGE', '>=', 40], ['NAME', '=', 'bob'] ])->get();

OR

$db2Util->getQueryBuilder()->table('user')->where([ ['AGE', '>=', 40], ['NAME', 'bob'] ])->get();

使用IN运算符的查询
$db2Util->getQueryBuilder()->table('user')->where('column-name', 'in', [array of data])->get();

示例

$db2Util->getQueryBuilder()->table('user')->where('AGE', 'in', [40, 30])->get();

带有Limit的查询
$db2Util->getQueryBuilder()->table('table name')->limit(number of records)->get(); 示例

$db2Util->getQueryBuilder()->table('user')->limit(3)->get();

带有Limit和Offset的查询
$db2Util->getQueryBuilder()->table('table naem')->limit(limit number)->offset( offset number)->get();
示例

$db2Util->getQueryBuilder()->table('user')->limit(2)->offset(2)->get();

使用Like运算符的查询

$db2Util->getQueryBuilder()->table('table name')->where('column', 'like', '%expected value%')->get();
示例

$db2Util->getQueryBuilder()->table('user')->where('name', 'like', '%doe%')->get();

使用JOIN的查询

$db2Util->getQueryBuilder()->table('table name')->join('JOIN TABLE NAME', 'table column ', '=', 'table column')->get();
示例

    $db2Util->getQueryBuilder()
            ->table('user_PRODUCT AS USER_PRODUCT')
            ->select(['USER_PRODUCT.ID', 'USER.NAME', 'PRODUCT.PRODUCT_NAME', 'PRODUCT.PRICE'])
            ->join('user AS USER ', 'USER_PRODUCT.USER_ID', '=', 'USER.ID')
            ->join('PHPUNIT_TEST_PRODUCT AS PRODUCT', 'USER_PRODUCT.PRODUCT_ID', '=', 'PRODUCT.ID')
            ->get();

生成的SQL

SELECT USER_PRODUCT.ID, USER.NAME, PRODUCT.PRODUCT_NAME, PRODUCT.PRICE FROM user_PRODUCT AS USER_PRODUCT INNER JOIN  user AS USER  ON ( USER_PRODUCT.USER_ID = USER.ID )  INNER JOIN  PHPUNIT_TEST_PRODUCT AS PRODUCT ON ( USER_PRODUCT.PRODUCT_ID = PRODUCT.ID )

其他JOIN方法

  • leftJoin
  • rightJoin
  • outerJoin

带有嵌套WHERE条件的查询

            $db2Util->getQueryBuilder()
                           ->table('table name')
                           ->where('column name', 'operator' 'value')
                           ->where(function($query){
                                $query->where('column name', 'operator', 'value')
                                    ->orWhere('column name', 'operator', 'value');
                             })
                           ->get()

示例

    $db2Util->getQueryBuilder()
                    ->table('user')
                    ->where('AGE', '>=', 40)
                    ->where(function($query){
                        $query->where('EMAIL', 'like', 'm%')
                            ->orWhere('NAME', 'in', ['bob', 'boo']);
                        })
                    ->get()

上述示例将生成类似于以下SQL的SQL

SELECT * FROM user WHERE AGE >= '40'  AND  ( EMAIL like 'm%'  OR NAME in (  'bob',  'boo' )  )

带有Union和unionAll的查询
$db2Util->getQueryBuilder()->table('table name')->unionAll('query builder instance')->get();

示例

    $firstQuery = $db2Util->getQueryBuilder()->table('user')->where('AGE','40');
    $unionResult = $db2Util->getQueryBuilder()->table('user')->where('NAME','like', 'b%')->unionAll($firstQuery)->get();

上述示例将生成类似于以下SQL的SQL

( SELECT * FROM user WHERE NAME like 'b%'  )  UNION ALL (SELECT * FROM user WHERE AGE = '40'  )

带有groupBy和having的查询

$db2Util->getQueryBuilder()->table('table name')->select('select columns')->groupBy('column')->having('column|aggrigate functions', 'operator', value)->get();

示例

$result = $db2Util->getQueryBuilder()->table('user')->select(["count(*) as cnt", "age"])->groupBy('age')->having('count(*)', '>', 1)->get();

上述示例将生成类似于以下SQL的SQL

SELECT count(*) as cnt, age FROM user GROUP BY age HAVING count(*) >  '1'

WHERE辅助方法

Where in

$db2Util->getQueryBuilder()->table('table name')->whereIn('column name', ['values'])->get()

示例

$db2Util->getQueryBuilder()->table('user')->whereIn('first_name', ['john', 'jack'])->get();

Where not in

$db2Util->getQueryBuilder()->table('table name')->whereNotIn('column name', ['values'])->get()

示例

$db2Util->getQueryBuilder()->table('user')->whereNotIn('first_name', ['john', 'jack'])->get();

Pluck 将单个列值拉取到集合中 $db2Util->getQueryBuilder()->table('table name')->pluck('column name');

示例

$db2Util->getQueryBuilder()->table('name')->pluck('age');

统计表中记录数
$db2Util->getQueryBuilder()->table('table name')->count();

示例

$db2Util->getQueryBuilder()->table('table name')->count();

表中列的最大值
$db2Util->getQueryBuilder()->table('table name')->max('column name');

示例

$db2Util->getQueryBuilder()->table('table name')->max('age');

Min值 表中列的最小值 $db2Util->getQueryBuilder()->table('table name')->min('column name');

示例

$db2Util->getQueryBuilder()->table('table name')->min('age');

插入数据

插入单行
$db2Util->getQueryBuilder()->table('table name')->insert(data associative array);

示例

$data =  [ 'NAME' => 'bob', 'AGE' => 40,'EMAIL' => 'bob@gmail.com', 'ADDRESS' =>  'bob address goes here'];
$db2Util->getQueryBuilder()->table('user')->insert($data);

插入多行 $db2Util->getQueryBuilder()->table('table name')->insert(two dimensional data associative array);
示例

$data = [
['NAME' => 'mikee', 'AGE' => 54,'EMAIL' => 'mikee@gmail.com', 'ADDRESS' =>  'mikee address goes here'],
['NAME' => 'mark', 'AGE' => 64,'EMAIL' => 'mark@gmail.com', 'ADDRESS' =>  'mark address goes here'],
['NAME' => 'momo', 'AGE' => 34,'EMAIL' => 'momo@gmail.com', 'ADDRESS' =>  'momo address goes here']
];
$db2Util->getQueryBuilder()->table('user')->insert($data);

更新数据

更新所有记录
$db2Util->getQueryBuilder()->table('table name')->update(['column name' => 'new data']);

示例

$db2Util->getQueryBuilder()->table('user')->update(['EMAIL' => 'new@gamil.com']);

带有WHERE条件的更新

$db2Util->getQueryBuilder()->table('table name')->where('column name', 'data')->update(['column name' => 'new data']);
示例

$db2Util->getQueryBuilder()->table('user')->where([['age', 40], ['NAME', 'bob']])->update(['EMAIL' => 'newemail@gamil.com']);

增加列的值

$db2Util->getQueryBuilder()->table('table name')->increment('column name', increment cound default 1);

示例

$db2Util->getQueryBuilder()->table('user')->where('ID', '1')->increment('age', 2);

减少列的值

$db2Util->getQueryBuilder()->table('table name')->decrement('column name', decrement count default 1);

示例

$db2Util->getQueryBuilder()->table('user')->where('ID', '1')->decrement('age');

删除数据

删除所有数据 $db2Util->getQueryBuilder()->table('表名')->delete();
示例

$db2Util->getQueryBuilder()->table('user')->delete();

带有WHERE条件的删除
$db2Util->getQueryBuilder()->table('user')->where('列名', '操作符', '值')->delete();
示例

$db2Util->getQueryBuilder()->table('user')->where('age', '!=', '40')->delete();

获取最后一条SQL查询

$db2Util->getConnection()->getLastSQLQuery(debug true or false) 如果debug为false，输出将是一个字符串

$db2Util->getConnection()->getLastSQLQuery(true);

如果debug为true，输出将是一个包含查询、参数和已处理查询的关联数组

array(3) {
  'query' => " SELECT * FROM user WHERE AGE = ?  ",
  'params' =>[
    40
  ],
  'processed_query' => " SELECT * FROM user WHERE AGE = '40'  "
}

对于复杂查询

执行查询

    $sql = " INSERT INTO USER (name, age, country) VALUES ( ?, ?, ? ) ";
    $params = [
        'bob',
        20,
        'India'
    ];
   $db2Util->getConnection()->executeQuery($sql, $params);

查询

   $sql = " SELECT * FROM USER WHERE ID = ? ";
   $params = [20];
   $stmt = $db2Util->getConnection()->query($sql, $params); 
   $stmt->fetchAll();

作者

Maneksh M S - manekshms@gmail.com