查询构造器

查询构建器建立在 Star_Model_Abstract 基础之上，可让你创建 程序化的、DBMS无关的SQL语句。相比于原生的SQL语句，查询构建器可以帮你 写出可读性更强的SQL相关的代码，并生成安全性更强的SQL语句，可以非常直观知道具体执行SQL语句，便于调试。

如下所示代码是查询构造器的一个典型用法：

//实例化select
$select = $this->select();
//构造SELECT语句
$select->from($this->getTableName() . " as u")
       ->where("age > ?", 18)
       ->order("age ASC")
       ->limitPage(1, 20);
//查询SQL
$this->fetchAll($select);

上面的代码将会生成并执行如下SQL语句：

 SELECT u.* FROM `users` `u` WHERE (age > "18") ORDER BY age ASC LIMIT 20 OFFSET 0

提示： echo $select对象，可以直接打印上面SQL语句，便于SQL调试

查询指定字段

//SELECT u.`username`, u.`age` FROM `users` `u`
$select->from($this->getTableName() . " as u", array("username", "age"));
//SELECT username, age FROM `users`
$select->from($this->getTableName(), "username, age");
//SELECT users.`username` `name`, users.`age` FROM `users`
$select->from($this->getTableName(), array("name" => "username", "age"));

条件查询(where、orWhere)

//SELECT users.* FROM `users` WHERE (age > 18) AND (age < 30)
$select->from($this->getTableName())
       ->where("age > 18")
       ->where("age < 30");
//SELECT users.* FROM `users` WHERE (username = "john") OR (username = "james")
$select->from($this->getTableName())
       ->where("username = ?", "john")
       ->orwhere("username = ?", "james");

警告：除非明确参数是安全可靠的，否则绝对禁止$select->where("username = '{$username}'")方式传参，避免您的应用程序受到注入攻击

排序(order)

//SELECT users.* FROM `users` WHERE (age > "18") ORDER BY age ASC
$select->from($this->getTableName())
       ->where("age > ?", 18)
       ->order("age ASC");
//----------多个排序---------
//SELECT users.* FROM `users` WHERE (age > "18") ORDER BY age ASC, username ASC
$select->from($this->getTableName())
       ->where("age > ?", 18)
       ->order(array("age ASC", "username ASC"));

group by

//SELECT users.* FROM `users` WHERE (age > 18) GROUP BY age ORDER BY age ASC
$select->from($this->getTableName())
       ->where("age > 18")
       ->group("age")
       ->order("age ASC");

having

// SELECT count(1) number FROM `users` WHERE (age > 18) GROUP BY age HAVING (number >5) ORDER BY age ASC
$select->from($this->getTableName(), "count(1) number")
       ->where("age > 18")
       ->group("age")
       ->having("number >5")
       ->order("age ASC");

连接查询(joinLeft、joinInner、joinRight)

//SELECT u.*, d.`department_name` FROM `users` `u` LEFT JOIN `departments` `d` ON u.department_id = p.department_id WHERE (u.age > 18) ORDER BY u.age ASC
$select->from($this->getTableName() . " AS u")
       ->where("u.age > 18")
       ->joinLeft($this->getTableName("departments") . " AS d", "u.department_id = p.department_id", array("department_name"))
       ->order("u.age ASC");

数量限制(limit、limitPage)

//SELECT users.* FROM `users` WHERE (age > "18") ORDER BY age ASC LIMIT 20
$select->from($this->getTableName())
       ->where("age > ?", 18)
       ->order("age ASC")
       ->limit(20);
//SELECT users.* FROM `users` WHERE (age > "18") ORDER BY age ASC LIMIT 20 OFFSET 0
$select->from($this->getTableName())
       ->where("age > ?", 18)
       ->order("age ASC")
       ->limitPage(1, 20);