GET

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // 获取全表
  4. $builder->get('getTable');
  5. // 表前缀
  6. $builder->setPrefix('easyswoole_')->get('getTable');
  7. // 获取总数。下面两个结果相同
  8. $builder->withTotalCount()->where('col1', 1, '>')->get('getTable');
  9. $builder->setQueryOption('SQL_CALC_FOUND_ROWS')->where('col1', 1, '>')->get('getTable');
  10. // fields。支持一维数组或字符串
  11. $builder->fields('col1, col2')->get('getTable');
  12. $builder->get('getTable', null, ['col1','col2']);
  13. // limit 1。下面两个结果相同
  14. $builder->get('getTable', 1)
  15. $builder->getOne('getTable')
  16. // offset 1, limit 10
  17. $builder->get('getTable',[1, 10])
  18. // 去重查询。
  19. $builder->get('getTable', [2,10], ['distinct col1','col2']);
  20. // where查询
  21. $builder->where('col1', 2)->get('getTable');
  22. // where查询2
  23. $builder->where('col1', 2, '>')->get('getTable');
  24. // 多条件where
  25. $builder->where('col1', 2)->where('col2', 'str')->get('getTable');
  26. // whereIn, whereNotIn, whereLike,修改相应的operator(IN, NOT IN, LIKE)
  27. $builder->where('col3', [1,2,3], 'IN')->get('getTable');
  28. // orWhere
  29. $builder->where('col1', 2)->orWhere('col2', 'str')->get('getTable');
  30. // 复杂where
  31. // 生成大概语句:where status = 1 AND (id > 10 or id < 2)
  32. $builder->where('status', 1)->where(' (id > 10 or id <2) ')->get('getTable');

JOIN

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // join。默认INNER JOIN
  4. $builder->join('table2', 'table2.col1 = getTable.col2')->get('getTable');
  5. $builder->join('table2', 'table2.col1 = getTable.col2', 'LEFT')->get('getTable');
  6. // join Where
  7. $builder->join('table2','table2.col1 = getTable.col2')->where('table2.col1', 2)->get('getTable');

GROUPBY HAVING

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // groupBy.
  4. $builder->groupBy('col1')->get('getTable');
  5. $builder->where('col1',2)->groupBy('col1')->get('getTable');
  6. // having
  7. $builder->groupBy('col1')->having('col1')->get('getTable');
  8. $builder->groupBy('col1')->having('col1', 1, '>')->get('whereGet');
  9. // and having. having第4个参数默认是 `AND`,默认多having是`且`关系
  10. $builder->groupBy('col1')->having('col1', 1, '>')->having('col2', 1, '>')->get('whereGet');
  11. // or having. 下面两种方法效果相等
  12. $builder->groupBy('col1')->having('col1', 1, '>')->orHaving('col2', 1, '>')->get('whereGet');
  13. $builder->groupBy('col1')->having('col1', 1, '>')->having('col2', 1, '>', 'OR')->get('whereGet');

ORDERBY

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // orderBy. 默认DESC
  4. $builder->orderBy('col1', 'ASC')->get('getTable');
  5. $builder->where('col1',2)->orderBy('col1', 'ASC')->get('getTable');

UNION

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // union. 相当于 adminTable UNION userTable
  4. $builder->union((new QueryBuilder)->where('userName', 'user')->get('userTable'))
  5. ->where('adminUserName', 'admin')->get('adminTable');

UPDATE

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // update
  4. $builder->update('updateTable', ['a' => 1]);
  5. // limit update
  6. $builder->update('updateTable', ['a' => 1], 5);
  7. // where update
  8. $builder->where('whereUpdate', 'whereValue')->update('updateTable', ['a' => 1]);

DELETE

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // delete all
  4. $builder->delete('deleteTable');
  5. // limit delete
  6. $builder->delete('deleteTable', 1);
  7. // where delete
  8. $builder->where('whereDelete', 'whereValue')->delete('deleteTable');

INSERT

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // insert into
  4. $builder->insert('insertTable', ['a' => 1, 'b' => "b"]);
  5. // replace into
  6. $builder->replace('replaceTable', ['a' => 1]);

SUBQUERY

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // 单一where条件子查询
  4. // 等同于 SELECT * FROM users WHERE id in ((SELECT userId FROM products WHERE qty > 2))
  5. $sub = $this->builder::subQuery();
  6. $sub->where("qty", 2, ">");
  7. $sub->get("products", null, "userId");
  8. $builder->where("id", $sub, 'in')->get('users');
  9. // 多where条件子查询
  10. // 等同于 SELECT * FROM users WHERE col2 = 1 AND id in ((SELECT userId FROM products WHERE qty > 2))
  11. $sub = $this->builder::subQuery();
  12. $sub->where ("qty", 2, ">");
  13. $sub->get ("products", null, "userId");
  14. $this->builder->where('col2',1)->where ("id", $sub, 'in')->get('users');
  15. // INSERT 包含子结果集
  16. // 等同于 INSERT INTO products (`productName`, `userId`, `lastUpdated`) VALUES ('test product', (SELECT name FROM users WHERE id = 6 LIMIT 1), NOW())
  17. $userIdQ = $this->builder::subQuery();
  18. $userIdQ->where ("id", 6);
  19. $userIdQ->getOne ("users", "name");
  20. $data = Array (
  21. "productName" => "test product",
  22. "userId" => $userIdQ,
  23. "lastUpdated" => $this->builder->now()
  24. );
  25. $this->builder->insert ("products", $data);

LOCK

  1. use EasySwoole\Mysqli\QueryBuilder;
  2. $builder = new QueryBuilder();
  3. // FOR UPDATE 排它锁。下面两个方法效果相同
  4. $builder->setQueryOption("FOR UPDATE")->get('getTable');
  5. $builder->selectForUpdate(true)->get('getTable');
  6. // LOCK IN SHARE MODE。共享锁
  7. $builder->lockInShareMode()->get('getTable');
  8. $builder->setQueryOption(['LOCK IN SHARE MODE'])->get('getTable');
  9. // LOCK TABLES 获取表锁
  10. $builder->lockTable('table');
  11. // UNLOCK TABLES 释放表锁
  12. $builder->unlockTable('table');