thinkphp-查詢語句

標籤：sql文法   array   think   count()   map   數組   set   最大   amp   

//SQL查詢語句

//數組索引查詢
/*
$user=M(‘Sks‘);
$condition[‘id‘]=1;
$condition[‘name‘]=160;
$condition[‘pass‘]=asd;
echo "<pre>";
var_dump($user->where($condition)->select());
echo "</pre>";
*/

//物件查詢
/*
$user=M(‘Sks‘);
$condtion=new \stdClass();
$condition->id=1;
$condition->name=160;
$condition->pass=asd;
echo "<pre>";
var_dump($user->where($condition)->select());
echo "</pre>";
*/

//運算式查詢

//$user=M(‘Sks‘);
//$map[‘id‘]=array(‘eq‘,1);//查詢id等於1的
//$map[‘id‘]=array(‘neq‘,1);//不等於
//$map[‘id‘]=array(‘gt‘,1);//大於
//$map[‘id‘]=array(‘egt‘,1);//大於等於
//$map[‘id‘]=array(‘lt‘,1);//小於
//$map[‘id‘]=array(‘elt‘,1);//小於等於

//$map[‘id‘]=array(‘like‘,‘%小%‘);//模糊查詢-有‘小‘的
//$map[‘id‘]=array(‘notlike‘,‘%小%‘);//模糊查詢-沒有‘小‘的
//$map[‘id‘]=array(‘like‘,array(‘%小%‘,‘%大%‘),‘AND‘);//模糊查詢-即有‘小’的，又有‘大’的

//$map[‘id‘]=array(‘between‘,‘1,3‘);//1,3區間查詢
//$map[‘id‘]=array(‘notbetween‘,‘1,3‘);//（不在）1,3區間查詢

//$map[‘id‘]=array(‘in‘,‘1,2,3‘);//in查詢1,2,3
//$map[‘id‘]=array(‘notin‘,1,2,3);//（不在）in查詢1,2,3

//$map[‘id‘]=array(‘exp‘,‘=1‘);//運算式查詢，支援sql文法
//查詢id等於1的>1 !=1 ......
/*
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/

//不同欄位相同查詢條件
/*
$user=M(‘Sks‘);
$map[‘name|pass‘]=‘160‘;
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/

//同欄位不同查詢條件
/*
$user=M(‘Sks‘);
$map[‘id|pass‘]=array(1,123,‘_multi‘=>true);
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/

/*
$user=M(‘Sks‘);
$map[‘id‘]=array(array(‘gt‘,1),array(‘lt‘,4));
$map[‘id‘]=array(array(‘gt‘,1),array(‘lt‘,4),‘OR‘);
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/

//字串查詢(_string)
/*
$user=M(‘Sks‘);
$map[‘id‘]=array(‘eq‘,1);
$map[‘_string‘]=‘name="160" AND pass="asd"‘;
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/
//請求字串查詢(_query)
/*
$user=M(‘Sks‘);
$map[‘id‘]=array(‘eq‘,1);
$map[‘_query‘]=‘name=160&pass=asd‘;
$map[‘_logic‘]=‘OR‘;
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/
//複合查詢
/*
$user=M(‘Sks‘);
$map[‘id‘]=array(‘eq‘,1);
$where[‘id‘]=2;
$map[‘_complex‘]=$where;
$map[‘_logic‘]=‘OR‘;//OR:或AND:和
echo "<pre>";
var_dump($user->where($map)->select());
echo "</pre>";
*/

//資料總條數
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->count());
echo "</pre>";
*/
//欄位總條數，遇null不統計
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->count(‘id‘));
echo "</pre>";
*/
//最大值
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->max(‘id‘));
echo "</pre>";
*/
//最小值
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->min(‘id‘));
echo "</pre>";
*/
//平均值
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->avg(‘id‘));
echo "</pre>";
*/
//求總和
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->sum(‘id‘));
echo "</pre>";
*/

//getBy動態查詢
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->getByname(‘160‘));
echo "</pre>";
*/
//getFieldBy動態查詢
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->getFieldBySks(‘123‘,‘pass‘));
echo "</pre>";
*/

//SQL讀取
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->query(‘SELECT * FROM think_sks‘));
echo "</pre>";
*/
//esecute寫入或修改
/*
$user=M(‘Sks‘);
echo "<pre>";
var_dump($user->execute(‘UPDATE think_sks SET name="460" WHERE id=2‘));
echo "</pre>";
*/

thinkphp-查詢語句