当前位置：Gxlcms > mysql > 数据库之DAO

数据库之DAO

时间：2021-07-01 10:21:17 帮助过：54人阅读

数据库之DAO DAO (Data Access Object) 数据访问对象是第一个面向对象的接口 –百度百科 数据库之DAO CRUD 查询条件 联合查询 join 事务Transaction CRUD class IndexController extends CController { public function actionCreate () { $rval = Yii::app(

数据库之DAO

DAO (Data Access Object) 数据访问对象是第一个面向对象的接口
–百度百科

  • 数据库之DAO
  • CRUD
  • 查询条件
  • 联合查询 join
  • 事务Transaction

CRUD

  1. <code><span><span>class</span> <span>IndexController</span> <span>extends</span> <span>CController</span>{</span>
  2.     <span>public</span> <span><span>function</span> <span>actionCreate</span><span>()</span>
  3.     {</span>
  4.         <span>$rval</span> = Yii::app()->db->createCommand()->insert(<span>'user'</span>,<span>array</span>(
  5.             <span>'username'</span>=><span>'blue'</span> <span>//传入数组不需要担心注入 ， yii自动会对数组进行 参数绑定的操作写入</span>
  6.         ));
  7.     }
  8.     <span>public</span> <span><span>function</span> <span>actionDelete</span><span>(<span>$id</span>)</span>
  9.     {</span>
  10.         Yii::app()->db->createCommand()->delete(<span>'user'</span>,<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>));
  11.     }
  12.     <span>public</span> <span><span>function</span> <span>actionUpdate</span><span>(<span>$id</span>)</span>
  13.     {</span>
  14.         Yii::app()->db->createCommand()->update(<span>'user'</span>,<span>array</span>(<span>'username'</span>=><span>'blue'</span>),<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>));
  15.     }
  16.     <span>//一维数组 单条数据</span>
  17.     <span>public</span> <span><span>function</span> <span>actionReadRow</span><span>(<span>$id</span>)</span>
  18.     {</span>
  19.         <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryRow();
  20.         var_dump(<span>$res</span>);
  21.     }
  22.     <span>//查询列</span>
  23.     <span>//比如说 查询的是所有的username</span>
  24.     <span>//返回的数组是 array('姓名1','姓名2','姓名3')</span>
  25.     <span>public</span> <span><span>function</span> <span>actionReadColumn</span><span>(<span>$id</span>)</span>
  26.     {</span>
  27.         <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryColumn();
  28.         var_dump(<span>$res</span>);
  29.     }
  30.     <span>//二维数组 查询所有</span>
  31.     <span>public</span> <span><span>function</span> <span>actionReadAll</span><span>(<span>$id</span>)</span>
  32.     {</span>
  33.         <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryAll();
  34.         var_dump(<span>$res</span>);
  35.     }
  36.     <span>//查询数量</span>
  37.     <span>//直接返回对应值，而不是数组</span>
  38.     <span>public</span> <span><span>function</span> <span>actionReadScalar</span><span>()</span>
  39.     {</span>
  40.         <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'count(*)'</span>)->from(<span>'user'</span>)->queryScalar();
  41.         var_dump(<span>$res</span>);
  42.     }
  43. }</code>

查询条件

where,like,in,limit,order,group

  1. <code><span>public</span> <span><span>function</span> <span>actionWhere</span><span>()</span>
  2.     {</span>
  3.         <span>$connect</span> = Yii::app()->db;
  4.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  5.                 ->where(<span>'id<:id'</span>,<span>array</span>(<span>':id'</span>=><span>3</span>))
  6.                 ->queryAll();
  7.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  8.                 ->where(<span>'id>:lid and id < :mid'</span>,<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>7</span>))
  9.                 ->queryAll();
  10.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  11.                 ->where(<span>'id > :lid'</span>,<span>array</span>(<span>':lid'</span>=><span>3</span>))
  12.                 ->andWhere(<span>'id < :mid'</span>,<span>array</span>(<span>':mid'</span>=><span>7</span>))
  13.                 ->queryAll();
  14.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  15.                 ->where(<span>array</span>(<span>'and'</span>,<span>'id > :lid'</span>,<span>'id < :mid'</span>),<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>8</span>))
  16.                 ->queryAll();
  17.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  18.                 ->where(<span>array</span>(<span>'and'</span>,<span>'id > :lid'</span>,<span>'id < :mid'</span>),<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>8</span>))
  19.                 ->queryAll();
  20.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  21.                 ->where(<span>array</span>(<span>'in'</span>,<span>'id'</span>,<span>array</span>(<span>3</span>,<span>4</span>,<span>5</span>)))
  22.                 <span>//->where(array('not in','id',array(3,4,5)))</span>
  23.                 ->queryAll();
  24.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  25.                 ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
  26. <span>//                ->where(array('not like','username','%g%'))</span>
  27. <span>//                ->where(array('like','username',array('%g%','%o%')))</span>
  28.                 ->queryAll();
  29.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  30.                 ->where(<span>array</span>(<span>'and'</span>,<span>'id > :id'</span>,<span>'id < :mid'</span>,<span>array</span>(<span>'or'</span>,<span>'username = :user1'</span>,<span>'username = :user2'</span>)),
  31.                         <span>array</span>(<span>':id'</span>=><span>4</span>,
  32.                             <span>':mid'</span>=><span>10</span>,
  33.                             <span>'user1'</span>=><span>'blue'</span>,
  34.                             <span>'user2'</span>=><span>'green'</span>
  35.                             ))
  36.                 ->queryAll();
  37.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  38.                 ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
  39.                 ->offset(<span>1</span>)
  40.                 ->limit(<span>2</span>)
  41.                 ->queryAll();
  42.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
  43.                 ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
  44.                 ->order(<span>'id desc'</span>)
  45.                 ->queryAll();
  46.         <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*,count(*)'</span>)->from(<span>'user'</span>)
  47.                 ->group(<span>'username'</span>)
  48.                 ->queryAll();
  49.         var_dump(<span>$res</span>);
  50.     }</code>

联合查询 (join)

  1. <code><span>public</span> <span><span>function</span> <span>actionJoin</span><span>()</span>
  2.     {</span>
  3.         <span>$res</span> = Yii::app()->db->createCommand()
  4.                 ->select(<span>'*'</span>)
  5.                 ->from(<span>'user as u'</span>)
  6.                 ->join(<span>'city as c'</span>,<span>'u.city = c.id'</span>)
  7. <span>//                ->leftJoin('city as c','u.city = c.id')</span>
  8.                 ->queryAll();
  9.         var_dump(<span>$res</span>);
  10.     }</code>

事务(Transaction)

  1. <code><span>//yiiChina 例子</span>
  2. <span>$transaction</span><span>=</span><span>$connection</span><span>-></span>beginTransaction();
  3. try
  4. {
  5.     <span>$connection</span><span>-></span>createCommand(<span>$sql1</span>)<span>-></span>execute();
  6.     <span>$connection</span><span>-></span>createCommand(<span>$sql2</span>)<span>-></span>execute();
  7.     <span>//.... other SQL executions</span>
  8.     <span>$transaction</span><span>-></span>commit();
  9. }
  10. catch(Exception <span>$e</span>) <span>// 如果有一条查询失败，则会抛出异常</span>
  11. {
  12.     <span>$transaction</span><span>-></span>rollBack();
  13. }</code>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！