php链式操作mysql数据库的方法是什么

本篇内容介绍了“php链式操作mysql数据库的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

封装类常用操作使用示例

// 初始化db连接
$db = new WorkermanMySQLConnection('host', 'port', 'user', 'password', 'db_name');

// 获取所有数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
//等价于
$db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1");

// 获取一行数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
//等价于
$db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'");

// 获取一列数据
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
//等价于
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");

// 获取单个值
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
//等价于
$db->single("SELECT ID FROM `Persons` WHERE sex='M'");

// 复杂查询
$db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did'))
->limit(10)->offset(20)->bindValues(array('age' => 13));
// 等价于
$db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid`
WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20');

// 插入
$insert_id = $db->insert('Persons')->cols(array(
    'Firstname'=>'abc',
    'Lastname'=>'efg',
    'Sex'=>'M',
    'Age'=>13))->query();
等价于
$insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`)
VALUES ( 'abc', 'efg', 'M', 13)");

// 更新
$row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1')
->bindValue('sex', 'F')->query();
// 等价于
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// 等价于
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");

// 删除
$row_count = $db->delete('Persons')->where('ID=9')->query();
// 等价于
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");

// 事务
$db->beginTrans();
....
$db->commitTrans(); // or $db->rollBackTrans();

封装源码（保存文件引用）

<?php
/**
 * 数据库连接类，依赖 PDO_MYSQL 扩展
 * 在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成
 */
class Connection
{
    /**
     * SELECT
     *
     * @var array
     */
    protected $union = array();

    /**
     * 是否是更新
     *
     * @var bool
     */
    protected $for_update = false;

    /**
     * 选择的列
     *
     * @var array
     */
    protected $cols = array();

    /**
     * 从哪些表里面 SELECT
     *
     * @var array
     */
    protected $from = array();

    /**
     * $from 当前的 key
     *
     * @var int
     */
    protected $from_key = -1;

    /**
     * GROUP BY 的列
     *
     * @var array
     */
    protected $group_by = array();

    /**
     * HAVING 条件数组.
     *
     * @var array
     */
    protected $having = array();

    /**
     * HAVING 语句中绑定的值.
     *
     * @var array
     */
    protected $bind_having = array();

    /**
     * 每页多少条记录
     *
     * @var int
     */
    protected $paging = 10;

    /**
     * sql 中绑定的值
     *
     * @var array
     */
    protected $bind_values = array();

    /**
     * WHERE 条件.
     *
     * @var array
     */
    protected $where = array();

    /**
     * WHERE 语句绑定的值
     *
     * @var array
     */
    protected $bind_where = array();

    /**
     * ORDER BY 的列
     *
     * @var array
     */
    protected $order_by = array();

    /**
     * ORDER BY 的排序方式,默认为升序
     *
     * @var bool
     */
    protected $order_asc = true;
    /**
     * SELECT 多少记录
     *
     * @var int
     */
    protected $limit = 0;

    /**
     * 返回记录的游标
     *
     * @var int
     */
    protected $offset = 0;

    /**
     * flags 列表
     *
     * @var array
     */
    protected $flags = array();

    /**
     * 操作哪个表
     *
     * @var string
     */
    protected $table;

    /**
     * 表.列 和 last-insert-id 映射
     *
     * @var array
     */
    protected $last_insert_id_names = array();

    /**
     * INSERT 或者 UPDATE 的列
     *
     * @param array
     */
    protected $col_values;

    /**
     * 返回的列
     *
     * @var array
     */
    protected $returning = array();

    /**
     * sql 的类型 SELECT INSERT DELETE UPDATE
     *
     * @var string
     */
    protected $type = '';

    /**
     * pdo 实例
     *
     * @var PDO
     */
    protected $pdo;

    /**
     * PDOStatement 实例
     *
     * @var PDOStatement
     */
    protected $sQuery;

    /**
     * 数据库用户名密码等配置
     *
     * @var array
     */
    protected $settings = array();

    /**
     * sql 的参数
     *
     * @var array
     */
    protected $parameters = array();

    /**
     * 最后一条直行的 sql
     *
     * @var string
     */
    protected $lastSql = '';

    /**
     * 是否执行成功
     *
     * @var bool
     */
    protected $success = false;

    /**
     * 选择哪些列
     *
     * @param string|array $cols
     * @return self
     */
    public function select($cols = '*')
    {
        $this->type = 'SELECT';
        if (!is_array($cols)) {
            $cols = explode(',', $cols);
        }
        $this->cols($cols);
        return $this;
    }

    /**
     * 从哪个表删除
     *
     * @param string $table
     * @return self
     */
    public function delete($table)
    {
        $this->type  = 'DELETE';
        $this->table = $this->quoteName($table);
        $this->fromRaw($this->quoteName($table));
        return $this;
    }

    /**
     * 更新哪个表
     *
     * @param string $table
     * @return self
     */
    public function update($table)
    {
        $this->type  = 'UPDATE';
        $this->table = $this->quoteName($table);
        return $this;
    }

    /**
     * 向哪个表插入
     *
     * @param string $table
     * @return self
     */
    public function insert($table)
    {
        $this->type  = 'INSERT';
        $this->table = $this->quoteName($table);
        return $this;
    }

    /**
     *
     * 设置 SQL_CALC_FOUND_ROWS 标记.
     *
     * @param bool $enable
     * @return self
     */
    public function calcFoundRows($enable = true)
    {
        $this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
        return $this;
    }

    /**
     * 设置 SQL_CACHE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function cache($enable = true)
    {
        $this->setFlag('SQL_CACHE', $enable);
        return $this;
    }

    /**
     * 设置 SQL_NO_CACHE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function noCache($enable = true)
    {
        $this->setFlag('SQL_NO_CACHE', $enable);
        return $this;
    }

    /**
     * 设置 STRAIGHT_JOIN 标记.
     *
     * @param bool $enable
     * @return self
     */
    public function straightJoin($enable = true)
    {
        $this->setFlag('STRAIGHT_JOIN', $enable);
        return $this;
    }

    /**
     * 设置 HIGH_PRIORITY 标记
     *
     * @param bool $enable
     * @return self
     */
    public function highPriority($enable = true)
    {
        $this->setFlag('HIGH_PRIORITY', $enable);
        return $this;
    }

    /**
     * 设置 SQL_SMALL_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function smallResult($enable = true)
    {
        $this->setFlag('SQL_SMALL_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 SQL_BIG_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function bigResult($enable = true)
    {
        $this->setFlag('SQL_BIG_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 SQL_BUFFER_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function bufferResult($enable = true)
    {
        $this->setFlag('SQL_BUFFER_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 FOR UPDATE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function forUpdate($enable = true)
    {
        $this->for_update = (bool)$enable;
        return $this;
    }

    /**
     * 设置 DISTINCT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function distinct($enable = true)
    {
        $this->setFlag('DISTINCT', $enable);
        return $this;
    }

    /**
     * 设置 LOW_PRIORITY 标记
     *
     * @param bool $enable
     * @return self
     */
    public function lowPriority($enable = true)
    {
        $this->setFlag('LOW_PRIORITY', $enable);
        return $this;
    }

    /**
     * 设置 IGNORE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function ignore($enable = true)
    {
        $this->setFlag('IGNORE', $enable);
        return $this;
    }

    /**
     * 设置 QUICK 标记
     *
     * @param bool $enable
     * @return self
     */
    public function quick($enable = true)
    {
        $this->setFlag('QUICK', $enable);
        return $this;
    }

    /**
     * 设置 DELAYED 标记
     *
     * @param bool $enable
     * @return self
     */
    public function delayed($enable = true)
    {
        $this->setFlag('DELAYED', $enable);
        return $this;
    }

    /**
     * 序列化
     *
     * @return string
     */
    public function __toString()
    {
        $union = '';
        if ($this->union) {
            $union = implode(' ', $this->union) . ' ';
        }
        return $union . $this->build();
    }

    /**
     * 设置每页多少条记录
     *
     * @param int $paging
     * @return self
     */
    public function setPaging($paging)
    {
        $this->paging = (int)$paging;
        return $this;
    }

    /**
     * 获取每页多少条记录
     *
     * @return int
     */
    public function getPaging()
    {
        return $this->paging;
    }

    /**
     * 获取绑定在占位符上的值
     */
    public function getBindValues()
    {
        switch ($this->type) {
            case 'SELECT':
                return $this->getBindValuesSELECT();
            case 'DELETE':
            case 'UPDATE':
            case 'INSERT':
                return $this->getBindValuesCOMMON();
            default :
                throw new Exception("type err");
        }
    }

    /**
     * 获取绑定在占位符上的值
     *
     * @return array
     */
    public function getBindValuesSELECT()
    {
        $bind_values = $this->bind_values;
        $i           = 1;
        foreach ($this->bind_where as $val) {
            $bind_values[$i] = $val;
            $i++;
        }
        foreach ($this->bind_having as $val) {
            $bind_values[$i] = $val;
            $i++;
        }
        return $bind_values;
    }

    /**
     *
     * SELECT选择哪些列
     *
     * @param mixed  $key
     * @param string $val
     * @return void
     */
    protected function addColSELECT($key, $val)
    {
        if (is_string($key)) {
            $this->cols[$val] = $key;
        } else {
            $this->addColWithAlias($val);
        }
    }

    /**
     * SELECT 增加选择的列
     *
     * @param string $spec
     */
    protected function addColWithAlias($spec)
    {
        $parts = explode(' ', $spec);
        $count = count($parts);
        if ($count == 2 && trim($parts[0]) != '' && trim($parts[1]) != '') {
            $this->cols[$parts[1]] = $parts[0];
        } elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
            $this->cols[$parts[2]] = $parts[0];
        } else {
            $this->cols[] = trim($spec);
        }
    }

    /**
     * from 哪个表
     *
     * @param string $table
     * @return self
     */
    public function from($table)
    {
        return $this->fromRaw($this->quoteName($table));
    }

    /**
     * from的表
     *
     * @param string $table
     * @return self
     */
    public function fromRaw($table)
    {
        $this->from[] = array($table);
        $this->from_key++;
        return $this;
    }

    /**
     *
     * 子查询
     *
     * @param string $table
     * @param string $name The alias name for the sub-select.
     * @return self
     */
    public function fromSubSelect($table, $name)
    {
        $this->from[] = array("($table) AS " . $this->quoteName($name));
        $this->from_key++;
        return $this;
    }


    /**
     * 增加 join 语句
     *
     * @param string $table
     * @param string $cond
     * @param string $type
     * @return self
     * @throws Exception
     */
    public function join($table, $cond = null, $type = '')
    {
        return $this->joinInternal($type, $table, $cond);
    }

    /**
     * 增加 join 语句
     *
     * @param string $join inner, left, natural
     * @param string $table
     * @param string $cond
     * @return self
     * @throws Exception
     */
    protected function joinInternal($join, $table, $cond = null)
    {
        if (!$this->from) {
            throw new Exception('Cannot join() without from()');
        }

        $join                          = strtoupper(ltrim("$join JOIN"));
        $table                         = $this->quoteName($table);
        $cond                          = $this->fixJoinCondition($cond);
        $this->from[$this->from_key][] = rtrim("$join $table $cond");
        return $this;
    }

    /**
     * quote
     *
     * @param string $cond
     * @return string
     *
     */
    protected function fixJoinCondition($cond)
    {
        if (!$cond) {
            return '';
        }

        $cond = $this->quoteNamesIn($cond);

        if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
            return $cond;
        }

        if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
            return $cond;
        }

        return 'ON ' . $cond;
    }

    /**
     * inner join
     *
     * @param string $table
     * @param string $cond
     * @return self
     * @throws Exception
     */
    public function innerJoin($table, $cond = null)
    {
        return $this->joinInternal('INNER', $table, $cond);
    }

    /**
     * left join
     *
     * @param string $table
     * @param string $cond
     * @return self
     * @throws Exception
     */
    public function leftJoin($table, $cond = null)
    {
        return $this->joinInternal('LEFT', $table, $cond);
    }

    /**
     * right join
     *
     * @param string $table
     * @param string $cond
     * @return self
     * @throws Exception
     */
    public function rightJoin($table, $cond = null)
    {
        return $this->joinInternal('RIGHT', $table, $cond);
    }

    /**
     * joinSubSelect
     *
     * @param string $join inner, left, natural
     * @param string $spec
     * @param string $name sub-select 的别名
     * @param string $cond
     * @return self
     * @throws Exception
     */
    public function joinSubSelect($join, $spec, $name, $cond = null)
    {
        if (!$this->from) {
            throw new Exception('Cannot join() without from() first.');
        }

        $join                          = strtoupper(ltrim("$join JOIN"));
        $name                          = $this->quoteName($name);
        $cond                          = $this->fixJoinCondition($cond);
        $this->from[$this->from_key][] = rtrim("$join ($spec) AS $name $cond");
        return $this;
    }

    /**
     * group by 语句
     *
     * @param array $cols
     * @return self
     */
    public function groupBy(array $cols)
    {
        foreach ($cols as $col) {
            $this->group_by[] = $this->quoteNamesIn($col);
        }
        return $this;
    }

    /**
     * having 语句
     *
     * @param string $cond
     * @return self
     */
    public function having($cond)
    {
        $this->addClauseCondWithBind('having', 'AND', func_get_args());
        return $this;
    }

    /**
     * or having 语句
     *
     * @param string $cond The HAVING condition.
     * @return self
     */
    public function orHaving($cond)
    {
        $this->addClauseCondWithBind('having', 'OR', func_get_args());
        return $this;
    }

    /**
     * 设置每页的记录数量
     *
     * @param int $page
     * @return self
     */
    						
						
						
						
						
						
						
					

以上就是php链式操作mysql数据库的方法是什么的详细内容，更多关于php链式操作mysql数据库的方法是什么的资料请关注九品源码其它相关文章！