首页 > 代码库 > thinkphp sql连惯操作

thinkphp sql连惯操作

  这里使用字符串形式 与关键字冲突的风险 SELECT * FROM `weibo_user` WHERE ( id>1 ) ORDER BY id DESC LIMIT 2
 var_dump($user->where(‘id>1‘)->order(‘id DESC‘)->limit(2)->select());
 数组形式 避免了关键字冲突 SELECT * FROM `weibo_user` WHERE `id` = ‘1‘ ORDER BY `create` DESC LIMIT 2
 var_dump($user->where(array(‘id‘=>array(‘eq‘,‘1‘)))->order(array(‘create‘=>‘DESC‘))->limit(2)->select());
   CURD 操作
 显示一条数据 SELECT * FROM `weibo_user` WHERE ( id in (1,2,3) ) LIMIT 1
 var_dump($user->where(‘id in (1,2,3)‘)->find());
 删除一条数据 DELETE FROM `weibo_user` WHERE ( id=42 )
 $user->where(‘id=42‘)->delete();
 条件查询 SELECT * FROM `weibo_user` WHERE `id` = 1 AND ( username="蜡笔小新" )
 多个where使用时 尽量使用变量形式否则为引起冲突
 $map[‘id‘]=1;
 var_dump($user->where($map)->where(‘username="蜡笔小新"‘)->select());
字符串形式排序SELECT * FROM `weibo_user` ORDER BY id DESC
 var_dump($user->order(‘id DESC‘)->select());
数组形式排序 SELECT * FROM `weibo_user` ORDER BY `id` DESC
 var_dump($user->order(array(‘id‘=>‘DESC‘))->select());
多列字符串形式排序 SELECT * FROM `weibo_user` ORDER BY id DESC,email DESC
 var_dump($user->order(‘id DESC,email DESC‘)->select());
 多列数组形式排序 SELECT * FROM `weibo_user` ORDER BY `id` DESC,`email` DESC
 var_dump($user->order(array(‘id‘=>‘DESC‘,‘email‘=>‘DESC‘))->select());
 字段投影      SELECT `id`,`username` FROM `weibo_user`
 dump($user->field(‘id,username‘)->select());
 字段投影      SELECT sum(id) AS count FROM `weibo_user`  将字段名称 作为别名处理
 dump($user->field(‘sum(id) AS count‘)->select());
 字段投影  截取前3位字符并作别名处理SELECT `id`,LEFT(username,3) AS `left_username` FROM `weibo_user`
 dump($user->field(array(‘id‘,‘LEFT(username,3)‘=>‘left_username‘))->select());
获取所有字段 field() field(‘*‘)和效果相同
 dump($user->field(‘*‘)->select())     ;
写入操作
 $user->field(‘id,email‘)->create();
 limit SELECT * FROM `weibo_user` LIMIT 2
 dump($user->limit(2)->select());
 SELECT * FROM `weibo_user` LIMIT 2,3
 dump($user->limit(2,3)->select());
 page SELECT * FROM `weibo_user` LIMIT 4(页码数据),2(显示条数)
 dump($user->page(3,2)->select());
 table 表切换 SELECT * FROM `weibo_topic`
 dump($user->table(‘weibo_topic‘)->select());
 简化表名   SELECT * FROM `weibo_topic` __TOPIC__ => ‘weibo_topic‘
 dump($user->table(‘__TOPIC__‘)->select());
 多表查询  SELECT * FROM weibo_user a,weibo_topic b
 dump($user->table(‘__USER__ a,__TOPIC__ b‘)->select());
 数组形式多表查询避免关键字冲突 SELECT a.id,b.id FROM `weibo_user` `a`,`weibo_topic` `b`
 dump($user->field(‘a.id,b.id‘)->table(array(‘weibo_user‘=>‘a‘,‘weibo_topic‘=>‘b‘))->select());


thinkphp sql连惯操作