查询方式的一般使用1

本节课大纲：一、普通查询方式a、字符串$arr=$m->where("sex=0 and username='gege'")->find();b、数组$data['sex']=0;$data['username']='gege';$arr=$m->where($data)->find();注意：这种方式默认是and的关系，如果使用or关系，需要添加数组值$data['sex']=0;$data['username']='gege';$data['_logic']='or';二、表达式查询方式$data['id']=array('lt',6);$arr=$m->where($data)->select();EQ 等于NEQ不等于GT 大于EGT大于等于LT 小于ELT小于等于LIKE 模糊查询$data['username']=array('like','%ge');$arr=$m->where($data)->select();NOTLIKE$data['username']=array('notlike','%ge%'); //notlike中间没有空格    $arr=$m->where($data)->select();注意：如果一个字段要匹配多个通配符$data['username']=array('like',array('%ge%','%2%','%五%'),'and');//如果没有第三个值，默认关系是or关系$arr=$m->where($data)->select();BETWEEN$data['id']=array('between',array(5,7));$arr=$m->where($data)->select();//SELECT * FROM `tp_user` WHERE ( (`id` BETWEEN 5 AND 7 ) )$data['id']=array('not between',array(5,7));//注意，not 和 between中间一定要有空格$arr=$m->where($data)->select();IN$data['id']=array('in',array(4,6,7));$arr=$m->where($data)->select();//SELECT * FROM `tp_user` WHERE ( `id` IN (4,6,7) )$data['id']=array('not in',array(4,6,7));$arr=$m->where($data)->select();//SELECT * FROM `tp_user` WHERE ( `id` NOT IN (4,6,7) )三、区间查询四、统计查询五、SQL直接查询/************一、普通查询方式   1.使用字符串方式查询id=1的数据 public function show(){ $m=M('user'); $var=$m->where('id=1')->getField('username'); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       echo $var;       public function show(){ $m=M('user'); $arr=$m->where('username="gege" and sex=1')->select(); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       var_dump($arr);         $this->display();    拼接成的SQL:SELECT * FROM `user` WHERE ( username="gege" and sex=1 ) [ RunTime:0.008743s ]或者: public function show(){ $m=M('user'); $arr=$m->where("username='gege' and sex=1")->select(); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       var_dump($arr);         $this->display();       }SELECT * FROM `user` WHERE ( username='gege' and sex=1 ) [ RunTime:0.009251s ]2.使用数组方式； public function show(){ $m=M('user'); #$arr=$m->where("username='gege' and sex=1")->select(); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       #var_dump($arr);    $data['username']='gege';$data['sex']=1;var_dump ($m->where($data)->select());         $this->display();拼接成的SQL:SELECT * FROM `user` WHERE ( `username` = 'gege' ) AND ( `sex` = 1 ) [ RunTime:0.008914s ]注意:默认条件是and逻辑运算符: public function show(){ $m=M('user'); #$arr=$m->where("id<10")->select(); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       #var_dump($arr);    $data['username']='gege';$data['sex']=1;$data['_logic']='or';var_dump ($m->where($data)->select());         $this->display();       }}查询id>60: public function show(){ $m=M('user'); $arr=$m->where("id>60")->select(); #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['_logic']='or';#var_dump ($m->where($data)->select());         $this->display();       }拼接成的SQL:SELECT * FROM `user` WHERE ( id>60 ) [ RunTime:0.007781s ] public function show(){ $m=M('user'); // $arr=$m->where("id>60")->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");       // var_dump($arr);    // $data['username']='gege';// $data['sex']=1;$data['id']=array('GT',60);// $data['_logic']='or';var_dump ($m->where($data)->select());         $this->display();  SELECT * FROM `user` WHERE ( `id` > 60 ) [ RunTime:0.007851s ]不等于；拼接SQL: public function show(){ $m=M('user');  $arr=$m->where('username not like "%ge%"')->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';// var_dump ($m->where($data)->select());         $this->display();       }SELECT * FROM `user` WHERE ( username not like "%ge%" ) [ RunTime:0.008588s ]或者: public function show(){ $m=M('user');  $arr=$m->where('username  like "%ge%"')->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        #$var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';$data['username']=array('like','%ge');var_dump ($m->where($data)->select());         $this->display();       }SELECT * FROM `user` WHERE ( `username` LIKE '%ge' ) [ RunTime:0.017041s ]not like: public function show(){ $m=M('user');  $arr=$m->where('username  like "%ge%"')->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        #$var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';$data['username']=array('notlike','%ge');var_dump ($m->where($data)->select());         $this->display(); 查询一个区间: public function show(){ $m=M('user');  $arr=$m->where("id between 30 and 40")->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';#$data['username']=array('notlike','%ge');#var_dump ($m->where($data)->select());         $this->display(); 拼接成的SQL:SELECT * FROM `user` WHERE ( id between 30 and 40 ) [ RunTime:0.008523s ]多个匹配； public function show(){ $m=M('user');  $arr=$m->where(" USERNAME LIKE '%ge%' OR USERNAME LIKE '%2%' OR USERNAME LIKE '%五%'")->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';#$data['username']=array('notlike','%ge');#var_dump ($m->where($data)->select());         $this->display();       }SELECT * FROM `user` WHERE ( USERNAME LIKE '%ge%' OR USERNAME LIKE '%2%' OR USERNAME LIKE '%五%' ) [ RunTime:0.009395s ]in查询: public function show(){ $m=M('user');  $arr=$m->where(" id in (1,31,42)")->select(); // #$this->show("欢迎你.$_GET[name].'你的年龄是'.$_GET[age]");        var_dump($arr);    // $data['username']='gege';// $data['sex']=1;// $data['id']=array('LT',60);//$data['_logic']='or';#$data['username']=array('notlike','%ge');#var_dump ($m->where($data)->select());         $this->display();       }SELECT * FROM `user` WHERE ( id in (1,31,42) ) [ RunTime:0.008024s ]