本节课大纲:
一、普通查询方式
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 ]