微生活

Yii用户手册数据库操作篇

作者：Jeen 发布于：2013-6-8 9:35 Saturday 分类：叽叽歪歪

（ps: 往日翻译，弃之可惜，于是在这边做个简略的记录吧 :)  ）

Yii 1.1.13 版用户手册第四章翻译--简略版

语言即工具，最终目的只有一个，那就是解决问题
但是方法和方式是可以多种多样的
当然为了把工具弄到手，学习首先是必不可少的
--以上纯属扯淡，下面进入正题
第四章——操作数据库
4.1数据库操作前奏
   基于PDO扩展，Yii框架提供了DAO(Data Access Objects) 以及 AR(Active Record)两种方式访问和操作数据库。当然你也可以使用第三方扩展，只要最终实现了目的即可，对于数据操作就是CRUD(create,read,update and delete)。通俗点就是增删改查，这边就不废话了。
4.2数据访问对象(DAO)
    DAO为不同的数据库操作管理提供了一种通用的API，它是基于PDO(PHP Data Objects)创建的，适用于大多数的数据库管理，如MySql，PostgreSQL。当然你要使用DAO的话，首先得确保你的服务环境安装的PDO及相关的PDO数据库扩展。
    Yii DAO主要包含以下几个类：
    CDbConnection   实现数据库连接
    CDbCommand     实现数据库操作语句生成与执行
    CDbDataReader    实现数据流操作
    CDbTransaction    实现数据库交互
    接下来，我们就来看看不同场景下Yii DAO的使用
4.2.1 建立数据库连接
    要连接数据库，先创建一个CDbConnection实例化对象并激活它。相关的数据是必不可少的，如：数据源名称，用户名，密码，当然你得确保这些信息是对的，然后你就可以创建连接了:
    $connection=new CDbConnection($dsn,$username,$password);
    // establish connection. You may try...catch possible exceptions
    $connection->active=true;
    ......
    $connection->active=false; // close connection
    DSN的格式取决于你所使用的数据库类型，参考PDO的说明文档，我们给出以下几种数据库的DSN书写格式：
    SQLite: sqlite:/path/to/dbfile
    MySQL: mysql:host=localhost;dbname=testdb
    PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb
    SQL Server: mssql:host=localhost;dbname=testdb
    Oracle: oci:dbname=//localhost:1521/testdb
    由于CDbConnection继承自CApplicationComponent，所以我们也可以把它当作组件来使用。在配置文件设置组件db(或其他自定义名称)如下：
    array(
        ......
        'components'=>array(
            ......
            'db'=>array(
                'class'=>'CDbConnection',
                'connectionString'=>'mysql:host=localhost;dbname=testdb',
                'username'=>'root',
                'password'=>'password',
                'emulatePrepare'=>true, // needed by some MySQL installations
            ),
        ),
    )
    这样我们就可以使用Yii::app()->db 来操作某个数据库连接了。
4.2.2 执行SQL语句
    完成数据库连接后，使用CDbCommand就可以实现SQL语句的执行了。以下是创建执行语句的一个范例：
    $connection=Yii::app()->db; // assuming you have configured a "db" connection
    // If not, you may explicitly create a connection:
    // $connection=new CDbConnection($dsn,$username,$password);
    $command=$connection->createCommand($sql);
    // 需要时，SQL语句可以使用如下方式进行更新:
    // $command->text=$newSQL;
    CDbCommand执行SQL语句有两种方式：
    execute():执行一条非查询SQL语句，如INSERT,UPDATE及DELETE。如果执行成功，则返回影响的行数
    query():执行一条数据查询语句，如SELECT。如果执行成功，它将返回一个包含查询结果的CDbDataReader实例化对象。类似的，queryXXX()方法也是直接返回查询结果。
    如果执行SQL语句报错，Yii就会抛出异常
    $rowCount=$command->execute(); // 执行非查询语句
    $dataReader=$command->query(); // 执行查询语句
    $rows=$command->queryAll(); // 返回所有查询结果
    $row=$command->queryRow(); // 返回查询结果的第一行
    $column=$command->queryColumn(); // 返回查询结果的第一列
    $value=$command->queryScalar(); // 返回查询结果的第一行第一列
4.2.3 读取查询结果
    获取查询数据后，就可以通过CDbDataReader::read()来读取相应的数据了。这边你可以使用foreach的数据结构来读取：
    $dataReader=$command->query();
    // 重复调用read()方法，知道返回false
    while(($row=$dataReader->read())!==false) { ... }
    // 使用foreach遍历数据行
    foreach($dataReader as $row) { ... }
    // 一次性置入到一个数组中
    $rows=$dataReader->readAll();
    注意：与query()不同，queryXXX()方法均直接返回查询数据。
4.2.4 执行交互动作
    当应用执行一些语句对数据库进行读写操作，有必要确定数据库执行了所有的操作语句。交互动作是CDbTransaction在Yii中的实例话，通常执行流程如下：
        交互动作开始
        执行语句有序执行，数据库更新不可见
        提交交互结果，数据库更新可见
        如果中途有一条语句执行异常，交互就会执行回滚动作
    上述流程示例代码如下:
    $transaction=$connection->beginTransaction();
    try
    {
        $connection->createCommand($sql1)->execute();
        $connection->createCommand($sql2)->execute();
        //.... other SQL executions
        $transaction->commit();
    }
    catch(Exception $e) // an exception is raised if a query fails
    {
        $transaction->rollback();
    }
4.2.5 绑定参数
    为了防止SQL注入攻击，提升SQL语句可读性，我们可以先使用占位符，然后再将具体的参数值赋给占位符即可。
    占位符可以命名，也可以不命名(用？)。使用CDbCommand::bindParam()或者CDbCommand::bindValue()方法替换实际值给占位符，参数绑定需要在SQL语句执行前完成
    // an SQL with two placeholders ":username" and ":email"
    $sql="INSERT INTO tbl user (username, email) VALUES(:username,:email)";
    $command=$connection->createCommand($sql);
    // replace the placeholder ":username" with the actual username value
    $command->bindParam(":username",$username,PDO::PARAM_STR);
    // replace the placeholder ":email" with the actual email value
    $command->bindParam(":email",$email,PDO::PARAM STR);
    $command->execute();
    // insert another row with a new set of parameters
    $command->bindParam(":username",$username2,PDO::PARAM_STR);
    $command->bindParam(":email",$email2,PDO::PARAM_STR);
    $command->execute();
    bindParam()方法和bindValue()方法非常相似，唯一的区别就是前者是才PHP变量传递参数的形式，而后者是直接传递值。当参数比较复杂时，建议使用第一种方式。更多说明，请参考相关PHP文档。
4.2.6 绑定列
    当获取查询结果时，我们也可以将列绑定到PHP变量上，这样在每次取值的时候它们就会被自动命名了。
    $sql="SELECT username, email FROM tbl user";
    $dataReader=$connection->createCommand($sql)->query();
    // 绑定第一列 (username) 给变量 $username
    $dataReader->bindColumn(1,$username);
    // 绑定第二列 (email)  给变量 $email 
    $dataReader->bindColumn(2,$email);
    while($dataReader->read()!==false)
    {
        // $username and $email contain the username and email in the current row
    }
4.2.7 使用数据表前缀
    Yii提供了数据表前缀支持。表前缀是指常见表名前面附加的一个标识字符串，通常用来标识特定的应用，或者区分表格用途。例如，'tbl_','dede_'等。
    如果要使用表前缀，配置CDbConnection::tablePrefix属性值即可。在SQL语句中使用{{TableName}}的格式给表名自动添加前缀。例如有一张表'tbl_user'，而我们的tablePrefix配置为'tbl_'，那么我们就可以使用类似以下的语句直接操作tbl_user表：
    $sql='SELECT * FROM {{user}}';
    $users=$connection->createCommand($sql)->queryAll();
   
4.3 查询语句生成器
    Yii框架中，使用DAO可以自动生成相关的查询语句。以下是一个基本的查询示例：
    $user = Yii::app()->db->createCommand()
                            ->select('id, username, profile')
                            ->from('tbl user u')
                            ->join('tbl profile p', 'u.id=p.user id')
                            ->where('id=:id', array(':id'=>$id))
                            ->queryRow();
    语句生成器用在需要程序化组装查询语句或者参杂着一些应用逻辑时比较合适。使用它有几个好处：
        可以程序化的完成一条SQL语句的生成
        可以自动为表名和列名添加引号，避免语句中的单词与SQL保留词冲突
        可以给参数添加引号或者使用占位符，防止SQL注入攻击
        实现了一定程度的数据库抽象话，易于不同数据库平台下的移植
    当你的查询很简单的时候，你也可以直接写整个SQL语句，而没必要非要使用语句生成器，除非你犯傻。注意，语句生成器不能用于更改一条已成的SQL执行语句。例如，以下的添加条件代码是无效的：
    $command = Yii::app()->db->createCommand('SELECT * FROM tbl user');
    // the following line will NOT append WHERE clause to the above SQL
    $command->where('id=:id', array(':id'=>$id));
    简单来说就是，绝对的SQL语句是不能和语句生成器混用的。
4.3.1 语句生成器准备知识
    Yii框架的语句生成器由CDbCommand提供，主要的数据操作类前面我们已经介绍了。使用语句生成器，我们首先创建一个CDbCommand的实例化对象：
    $command = Yii::app()->db->createCommand();
    上面的代码，我们先使用Yii::app()->db取得了数据库连接，然后调用CDbConnection::createCommand()方法创建了我们需要的对象。注意这边createCommand()的参数我们留空了，因为接下来我们将使用程序化的方法来完成SQL查询语句的构建。
4.3.2 构建查询语句
    数据查询语句就是类似Select...的SQL语句。sql生成器提供了一系列的方法来实现查询语句的细化。因为每个方法返回的都是CDbCommand对象，所以我们可以使用方法链的形式调用。类似前面给出的，详细方法列表如下：
    select():  指定查询语句的 SELECT 部分
    selectDistinct(): 指定查询语句的 SELECT 部分并添加DISTINCT标识
    from(): 指定查询语句的FROM部分
    where(): 指定查询语句的where部分
    andWhere(): 指定查询语句的where部分，条件以AND分隔
    orWhere(): 指定查询语句的where部分，条件以OR分隔
    join(): 附加内联接查询片段
    leftJoin(): 附加一个左外连接查询片段
    rightJoin(): 附加一个右外连接查询片段
    crossJoin(): 附加一个交叉联接查询片段
    naturalJoin(): 附加自然连接查询片段
    group(): 指定查询语句的GROUP BY部分
    having(): 指定查询语句的HAVING部分
    order(): 指定查询语句的ORDER BY部分
    limit(): 指定查询语句的LIMIT部分
    offset(): 指定查询语句的OFFSET部分
    union(): 附加一个联合查询
    下面，我就详细解释一下这些生成器方法如何使用。样例代码用于MySQL，数据表字段及值与实际使用时有所差别：
    select()
    function select($columns='*')
    select()方法生成查询语句的SELECT部分。$columns参数代表需要查询的列，它可以是一个以逗号分隔列名的字符串，也可以是一个数组。列名可以包含表名或者使用别名，方法会自动为列名添加引号。下面是一些范例：
    // SELECT *
    select()
    // SELECT `id`, `username`
    select('id, username')
    // SELECT `tbl user`.`id`, `username` AS `name`
    select('tbl user.id, username as name')
    // SELECT `id`, `username`
    select(array('id', 'username'))
    // SELECT `id`, count(*) as num
    select(array('id', 'count(*) as num'))
    ---------------------------------------------
    selectDistinct()
    function selectDistinct($columns)
    与select方法类似不过生成的语句有加DISTINCT标识，比如selectDistinct('id, username') 生成的SQL形如：SELECT DISTINCT `id`, `username`
    ------------------------------------------------
    from()
    function from($tables)
    from()方法生成查询语句的FROM部分。$tables参数代表要查询的表，它可以是一个以逗号分开的字符串，也可以是一个数组。不赘述了，如下是一些范例
    // FROM `tbl user`
    from('tbl user')
    // FROM `tbl user` `u`, `public`.`tbl profile` `p`
    from('tbl user u, public.tbl profile p')
    // FROM `tbl user`, `tbl profile`
    from(array('tbl user', 'tbl profile'))
    // FROM `tbl user`, (select * from tbl profile) p
    from(array('tbl user', '(select * from tbl profile) p'))
    ---------------------------------------------
    where()
    function where($conditions, $params=array())
    where()方法生成查询语句的WHERE部分。$conditions参数为条件语句，与$params参数共同构成查询条件。$conditions可以是一个字符串(如id=1)也可以是如下格式的数组：
    array(operator, operand1, operand2, ...)
    这边operator有如下几种：
        and:  条件(operands)将以'AND'进行组合。如array('and','id=1','id=2')会生成id=1 AND id=2。如果条件是一个数组，它将以相同的规则生成查询条件。例如array('and','type=1',array('or','id=1','id=2'))会生成 type=1 AND (id=1 OR id=2)这边不会自动添加引号和去空格。
        or:   与'and'类似，不同的是在组合条件时使用OR
        in:   条件1必须为某个数据列或者表达式，条件2为此列或表达式可能的值组成的数组。例如，array('in','id',array(1,2,3))会生成id IN (1,2,3)。方法会自动添加引号，排除异常。
        not in:  与'in'类似
        like:   条件1必须为某个数据列或者表达式，条件2为此列或表达式可能符合的字符串或数组。例如，array('like','name','%tester%')会生成name LIKE '%tester%'。当条件2为数组时，条件会以AND进行关联。例如，array('like','name',array('%test%','%sample%'))会生成name LIKE '%test%' AND name LIKE '%sample%'。方法会自动添加引号，排除异常。
        not like: 与like类似
        or like: 与like类似,在条件2为数组时用OR关联
        or not like: 与or like类似
    下面是where()的一些使用范例：
    // WHERE id=1 or id=2
    where('id=1 or id=2')
    // WHERE id=:id1 or id=:id2
    where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
    // WHERE id=1 OR id=2
    where(array('or', 'id=1', 'id=2'))
    // WHERE id=1 AND (type=2 OR type=3)
    where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
    // WHERE `id` IN (1, 2)
    where(array('in', 'id', array(1, 2))
    // WHERE `id` NOT IN (1, 2)
    where(array('not in', 'id', array(1,2)))
    // WHERE `name` LIKE '%Qiang%'
    where(array('like', 'name', '%Qiang%'))
    // WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
    where(array('like', 'name', array('%Qiang', '%Xue')))
    // WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
    where(array('or like', 'name', array('%Qiang', '%Xue')))
    // WHERE `name` NOT LIKE '%Qiang%'
    where(array('not like', 'name', '%Qiang%'))
    // WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
    where(array('or not like', 'name', array('%Qiang%', '%Xue%')))
    当使用like条件时，我们需要注意明确一下通配符(比如%和_)。如果条件为用户输入，我们就需要确保这些条件语句不会被解释为通配符：
    $keyword=$ GET['q'];
    // escape % and _ characters
    $keyword=strtr($keyword, array('%'=>'n%', ' '=>'n '));
    $command->where(array('like', 'title', '%'.$keyword.'%'));
    ---------------------------------------------
    andWhere()
    function andWhere($conditions, $params=array())
    这个方法生成WHERE部分语句，以AND分隔
    ---------------------------------------------
    orWhere()
    function orWhere($conditions, $params=array())
    这个方法生成WHERE部分语句，以OR分隔
    ---------------------------------------------
    order()
    function order($columns)
    order()生成ORDER BY部分语句。$columns参数代表需要排序的列，可以是一个以逗号分隔的字符串(包含顺序ASC或DESC)或者数组。可以指定表名，如下一些范例：
    // ORDER BY `name`, `id` DESC
    order('name, id desc')
    // ORDER BY `tbl profile`.`name`, `id` DESC
    order(array('tbl profile.name', 'id desc'))
    ------------------------------------------------
    limit() 和 offset()
    function limit($limit, $offset=null)
    function offset($offset)
    以下是一些范例
    // LIMIT 10
    limit(10)
    // LIMIT 10 OFFSET 20
    limit(10, 20)
    // OFFSET 20
    offset(20)
    -----------------------------------------------
    join()及其同类方法
    function join($table, $conditions, $params=array())
    function leftJoin($table, $conditions, $params=array())
    function rightJoin($table, $conditions, $params=array())
    function crossJoin($table)
    function naturalJoin($table)
    以下是一些范例
    // JOIN `tbl_profile` ON user_id=id
    join('tbl_profile', 'user_id=id')
    // LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND type=1
    leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1))
    --------------------------------------------------------
    group()
    function group($columns)
    使用范例：
    // GROUP BY `name`, `id`
    group('name, id')
    // GROUP BY `tbl profile`.`name`, `id`
    group(array('tbl profile.name', 'id'))
    -----------------------------------------------------
    having()
    function having($conditions, $params=array())
    使用范例
    // HAVING id=1 or id=2
    having('id=1 or id=2')
    // HAVING id=1 OR id=2
    having(array('or', 'id=1', 'id=2'))
    ----------------------------------------------------
    union()
    function union($sql)
    使用范例
    // UNION (select * from tbl profile)
    union('select * from tbl profile')
    ----------------------------------------
    执行查询
    使用上面的方法后，我们成功构建了查询语句，接下来就是调用DAO的方法来执行查询了。例如我们使用CDbCommand::queryRow()查询一条记录，或者执行CDbCommand::queryAll()查询所有记录。如下：
    $users = Yii::app()->db->createCommand()
                        ->select('*')
                        ->from('tbl user')
                        ->queryAll();
    获取生成的SQL语句
    除了执行构建的语句外，我们也可以获取到这个构建的SQL语句，使用CDbCommand::getText()方法即可：
    $sql = Yii::app()->db->createCommand()
                        ->select('*')
                        ->from('tbl user')
                        ->text;
    如果构建的时候有参数，这些参数将自动被CDbCommand::params中的属性替换。
    建立查询语句替代语法
    像前面说的，方法是多种多样的，在Yii中我们可以用不同的写法来实现相同的效果，比如：
    $command->select(array('id', 'username'));
    $command->select = array('id', 'username');
    $row = Yii::app()->db->createCommand(array(
        'select' => array('id', 'username'),
        'from' => 'tbl user',
        'where' => 'id=:id',
        'params' => array(':id'=>1),
    ))->queryRow();
    查询对象的复用
    CDbCommand对象可以被多次复用。在复用时，需要使用CDbCommand::reset()方法来清除原有查询条件。如下：
    $command = Yii::app()->db->createCommand();
    $users = $command->select('*')->from('tbl users')->queryAll();
    $command->reset(); // clean up the previous query
    $posts = $command->select('*')->from('tbl posts')->queryAll();
4.3.3 构建数据操作语句
    数据操作语句就是表示一些个SQL操作，对某张表的插入，更新，删除操作。Yii提供了insert,update和delete的语句生成器，不过与SELECT有所不同，这些数据操作将被即时解析和执行
    insert(): inserts a row into a table
    update(): updates the data in a table
    delete(): deletes the data from a table
    下面是关于这些方法的详细描述
    insert()
    function insert($table, $columns)
    ($columns参数为'列=>值'数组)范例如下：
    // build and execute the following SQL:
    // INSERT INTO `tbl user` (`name`, `email`) VALUES (:name, :email)
    $command->insert('tbl user', array(
        'name'=>'Tester',
        'email'=>'tester@example.com',
    ));
    ----------------------------------------------------
    update()
    function update($table, $columns, $conditions='', $params=array())
    使用范例如下：
    // build and execute the following SQL:
    // UPDATE `tbl user` SET `name`=:name WHERE id=:id
    $command->update('tbl user', array(
        'name'=>'Tester',
    ), 'id=:id', array(':id'=>1));
    --------------------------------------------------
    delete()
    function delete($table, $conditions='', $params=array())
    使用范例如下：
    // build and execute the following SQL:
    // DELETE FROM `tbl user` WHERE id=:id
    $command->delete('tbl user', 'id=:id', array(':id'=>1));
4.3.4    构建数据库结构操作语句
   
   
   

标签: Yii