查询方式的一般使用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 ]