thinkphp中条件查询的使用方法

一：字符串条件查询

1.  //直接实例化Model
2.  $user=M('user1');
3.  var_dump($user->where ('id=1 OR age=55')->select());

最终生成的sql语句为：SELECT * FROM `user1` WHERE ( id=1 OR age=55 )

PS:where 查询方法里面只要包含条件即可，多个条件加上 AND 等连接符即可

二：使用索引数组作为查询条件

1.  $user=M('user1');
2.  $condition['age']='55';
3.  $condition['name']='zs';
4.  // 索引数组查询的默认逻辑关系是 AND，如果想改变为 OR，可以使用_logic 定义查询逻辑
5.  $condition['_logic'] = 'OR';
6.  var_dump($user->where($condition)->select());

最终生成的sql语句为：SELECT * FROM `user1` WHERE `id` = ‘1’ AND `name` = 'zs'

三：使用对象方式查询

1.  <?php
2.  namespace Home\Controller;
3.  use Think\Controller;
4.  use Think\stdClass;
5.  class EleController extends Controller
6.  {
7.  $user=M('user1');
8.  $condition=new \stdClass;
9.  $condition->id = '1';
10.  var_dump($user->where($condition)->select());
11.  }

最终生成的sql语句为：SELECT * FROM `user1` WHERE `id` = ‘1’

四：表达式查询

查询表达式格式：$map['字段名'] = array('表达式','查询条件');

1.  $user=M('user1');
2.  $map['age'] = array('eq', 55); //where 为 age=55
3.  var_dump($user->where($map)->select());

 五：快捷查询

1.  //使用相同查询条件
2.  $user = M('user1');
3.  $map['name|email'] = 'a'; //'|'换成'&'变成AND
4.  var_dump($user->where($map)->select());
5.  // 不同字段不同查询条件
6.  //使用不同查询条件
7.  $user = M('user1');
8.  $map['name&email'] =array('a','test@qq.com','_multi'=>true);
9.  var_dump($user->where($map)->select());

第一条查询的结果：SELECT * FROM `user1` WHERE ( `name` = 'a' OR `email` = 'a' ) 

第二条查询的结果：SELECT * FROM `user1` WHERE ( (`name` = 'a') AND (`email` = 'test@qq.com') )

六：区间查询

1.  // 区间查询
2.  $user = M('user1');
3.  $map['id'] = array(array('gt', 1), array('lt', 4));
4.  var_dump($user->where($map)->select());
5.  //第三个参数设置逻辑OR
6.  $user = M('User1');
7.  $map['id'] = array(array('gt', 1), array('lt', 4), 'OR');
8.  var_dump($user->where($map)->select()
9.  }

七：组合查询

组合查询是基于索引数组查询方式的一个扩展性查询，添加了字符串查询(_string)、复合查询(_complex)、请求字符串查询(_query)，由于采用的是索引数组，重复的会被覆盖。

1.  //字符串查询(_string)
2.  $user = M('user1');
3.  $map['name'] = array('eq', 'zs');
4.  $map['_string'] ='age="30" AND email="zs@qq.com"';
5.  var_dump($user->where($map)->select());
6.  //请求字符串查询(_query)
7.  $user = M('user1');
8.  $map['id'] = array('eq', "1");
9.  $map['_query'] ='name=zs&email=zs@qq.com&_logic=OR';
10.  var_dump($user->where($map)->select());
11.  //复合查询(_complex)
12.  $user = M('user1');
13.  $where['name'] = array('like', 'z');
14.  $where['id'] = 1;
15.  $where['_logic'] = 'OR';
16.  $map['_complex'] = $where;
17.  $map['id'] = 3;
18.  $map['_logic'] = 'OR';
19.  var_dump($user->where($map)->select());

第一条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" )

第二条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" ) AND ( `name` = 'zs' OR `email` = 'zs@qq.com' )

第三条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' OR ( age="30" AND email="zs@qq.com" ) OR ( `name` = 'zs' OR `email` = 'zs@qq.com' ) OR ( `name` LIKE 'z' OR `id` = 1 ) 

 八：统计查询

1.  //数据总条数
2.  //SHOW COLUMNS FROM `user1`
3.  $user = M('user1');
4.  var_dump($user->count());
5.  //字段总条数，遇到NULL不统计
6.  //SELECT COUNT(*) AS tp_count FROM `user1` LIMIT 1
7.  $user = M('user1');
8.  var_dump($user->count('email'));
9.  //最大值
10.  //SELECT MAX(id) AS tp_max FROM `user1` LIMIT 1
11.  $user = M('user1');
12.  var_dump($user->max('id'));
13.  //最小值
14.  //SELECT MIN(id) AS tp_min FROM `user1` LIMIT 1
15.  $user = M('user1');
16.  var_dump($user->min('id'));
17.  //平均值
18.  //SELECT AVG(id) AS tp_avg FROM `user1` LIMIT 1
19.  $user = M('user1');
20.  var_dump($user->avg('id'));
21.  //求总和
22.  //SELECT SUM(id) AS tp_sum FROM `user1` LIMIT 1
23.  $user = M('user1');
24.  var_dump($user->sum('id'));

九：动态查询

1.  // 1.getBy 动态查询
2.  //查找email=xiaoin@163.com的数据
3.  //SELECT * FROM `user1` LIMIT 1
4.  $user = M('user1');
5.  var_dump($user->getByemail('zs@qq.com'));
6.  // 2.getFieldBy 动态查询
7.  //通过user得到相对应id值
8.  //SELECT `id` FROM `user1` LIMIT 1
9.  $user = M('user1');
10.  var_dump($user->getFieldByUser('ls', 'id'));

十：SQL查询　

1.  　 // 1.query 读取
2.  //查询结果集，如果采用分布式读写分离，则始终在读服务器执行
3.  //SELECT * FROM user1
4.  $user = M('user1');
5.  var_dump($user->query('SELECT * FROM user1'));
6.  // 2.execute写入
7.  //更新和写入，如果采用分布式读写分离，则始终在写服务器执行
8.  //UPDATE user1 set name="xuexi" WHERE id="1";
9.  $user = M('user1');
10.  var_dump($user->execute('UPDATE user1 set name="xuexi" WHERE
11.  id="1";'));

 十一：连贯查询

 通过连贯操作可以有效的提供数据存取的代码清晰度和开发效率，并且支持所有的 CURD 操作

1.  //连贯操作
2.  //PS：这里的 where、order 和 limit 方法都是连贯操作方法，所以它们都能返回$user本身，可以互换位置。而 select 方法不是连贯方法，需要放在最后，用以显示数据集。
3.  //SELECT * FROM `user1` WHERE ( id in (1,2,3,4) ) LIMIT 2
4.  $user=M('user1');
5.  var_dump($user->where('id in (1,2,3,4)')->limit(2)->select());
6.  //数组操作
7.  //SELECT * FROM `user1` WHERE id in (1,2,3,4) LIMIT 2
8.  $user = M('user1');
9.  var_dump($user->select(array('where'=>'id in (1,2,3,4)', 'limit'=>'2',
10.  )));
11.  // CURD处理
12.  // SELECT * FROM `user1` WHERE ( id=1 ) LIMIT 1
13.  // DELETE FROM `user1` WHERE ( id=2 )
14.  $user = M('user1');
15.  var_dump($user->where('id=1')->find());
16.  var_dump($user->where('id=2')->delete());

 1.where(支持字符串条件、数组条件（推荐用法）和多次调用。)

1.  //字符串方式
2.  //SELECT * FROM `user1` WHERE ( id=6 )
3.  var_dump($user->where('id=6')->select());  
4.  //索引数组方式
5.  // SELECT * FROM `user1` WHERE `id` = 6
6.  $map['id']=6;
7.  var_dump($user->where($map)->select()); 
8.  //多次调用方式
9.  SELECT * FROM `user1` WHERE `id` = 6 AND ( name="test" )
10.  $map['id']=array('eq',6);
11.  var_dump($user->where($map)->where('name="test"')->select());

 $menuWhere = [
                '_complex' => [
                    'model' => MODULE_NAME . '/' . CONTROLLER_NAME,
                    'action' => ACTION_NAME,
                    'identity' => $_SESSION['user']['identity']
                ],
                'relevant' => CONTROLLER_NAME . '/' . ACTION_NAME,
                '_logic' => 'or',
            ];
            $menu_id = M('Menu')->where($menuWhere)->getField('id');
 var_dump(M('Menu')->getLastSql());