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

  • 相关阅读:
    兄弟连学python——MongoDB相关
    兄弟连学python——redis相关
    python学习——tkinter实战(猜价格)
    python学习——tkinter实战(计算器)
    python学习——while实战小练习
    python学习——循环结构
    python学习——数据类型转换
    python学习——通过while循环语句实现九九乘法表的四种表达方式
    python—Html表单
    python—Redis简介及应用
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13350598.html
Copyright © 2020-2023  润新知