thinkphp之SQL查询语句（全） - 花花妹子。

Data: 2013-11-01 08:46:50Form: JournalClick: 7

花花妹子最努力~

【三十三】thinkphp之SQL查询语句（全） - 花花妹子。 - 博客园

一：字符串条件查询

        //直接实例化Model
        $user=M('user1');
        var_dump($user->where ('id=1 OR age=55')->select());

最终生成的sql语句为：SELECT * FROM `user1` WHERE ( id=1 OR age=55 )

PS:where 查询方法里面只要包含条件即可，多个条件加上 AND 等连接符即可

二：使用索引数组作为查询条件

        $user=M('user1');
        $condition['age']='55';
        $condition['name']='zs';
        // 索引数组查询的默认逻辑关系是 AND，如果想改变为 OR，可以使用_logic 定义查询逻辑
        $condition['_logic'] = 'OR';
        var_dump($user->where($condition)->select());

最终生成的sql语句为：SELECT * FROM `user1` WHERE `id` = ‘1’ AND `name` = 'zs'

三：使用对象方式查询

<?php
namespace Home\Controller;
use Think\Controller;
use Think\stdClass;
class EleController extends Controller
{
        $user=M('user1');
        $condition=new \stdClass;
        $condition->id = '1';
        var_dump($user->where($condition)->select());
}

最终生成的sql语句为：SELECT * FROM `user1` WHERE `id` = ‘1’

四：表达式查询

查询表达式格式：$map['字段名'] = array('表达式','查询条件');

        $user=M('user1');
        $map['age'] = array('eq', 55); //where 为 age=55
        var_dump($user->where($map)->select());

五：快捷查询

        //使用相同查询条件
        $user = M('user1');
        $map['name|email'] = 'a'; //'|'换成'&'变成AND
        var_dump($user->where($map)->select());
        // 不同字段不同查询条件
        //使用不同查询条件
        $user = M('user1');
        $map['name&email'] =array('a','test@qq.com','_multi'=>true);
        var_dump($user->where($map)->select());

第一条查询的结果：SELECT * FROM `user1` WHERE ( `name` = 'a' OR `email` = 'a' )

第二条查询的结果：SELECT * FROM `user1` WHERE ( (`name` = 'a') AND (`email` = 'test@qq.com') )

六：区间查询

　　　　// 区间查询
        $user = M('user1');
        $map['id'] = array(array('gt', 1), array('lt', 4));
        var_dump($user->where($map)->select());
        //第三个参数设置逻辑OR
        $user = M('User1');
        $map['id'] = array(array('gt', 1), array('lt', 4), 'OR');
        var_dump($user->where($map)->select()
    }

七：组合查询

组合查询是基于索引数组查询方式的一个扩展性查询，添加了字符串查询(_string)、复合查询(_complex)、请求字符串查询(_query)，由于采用的是索引数组，重复的会被覆盖。

        //字符串查询(_string)
        $user = M('user1');
        $map['name'] = array('eq', 'zs');
        $map['_string'] ='age="30" AND email="zs@qq.com"';
        var_dump($user->where($map)->select());
        //请求字符串查询(_query)
        $user = M('user1');
        $map['id'] = array('eq', "1");
        $map['_query'] ='name=zs&email=zs@qq.com&_logic=OR';
        var_dump($user->where($map)->select());
        //复合查询(_complex)
        $user = M('user1');
        $where['name'] = array('like', 'z');
        $where['id'] = 1;
        $where['_logic'] = 'OR';
        $map['_complex'] = $where;
        $map['id'] = 3;
        $map['_logic'] = 'OR';
        var_dump($user->where($map)->select());

第一条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" )

第二条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" ) AND ( `name` = 'zs' OR `email` = 'zs@qq.com' )

第三条查询语句：SELECT * FROM `user1` WHERE `name` = 'zs' OR ( age="30" AND email="zs@qq.com" ) OR ( `name` = 'zs' OR `email` = 'zs@qq.com' ) OR ( `name` LIKE 'z' OR `id` = 1 )

八：统计查询

        //数据总条数
        //SHOW COLUMNS FROM `user1`
        $user = M('user1');
        var_dump($user->count());
        //字段总条数，遇到NULL不统计
        //SELECT COUNT(*) AS tp_count FROM `user1` LIMIT 1
        $user = M('user1');
        var_dump($user->count('email'));
        //最大值
        //SELECT MAX(id) AS tp_max FROM `user1` LIMIT 1 
        $user = M('user1');
        var_dump($user->max('id'));
        //最小值
        //SELECT MIN(id) AS tp_min FROM `user1` LIMIT 1
        $user = M('user1');
        var_dump($user->min('id'));
        //平均值
        //SELECT AVG(id) AS tp_avg FROM `user1` LIMIT 1 
        $user = M('user1');
        var_dump($user->avg('id'));
        //求总和
        //SELECT SUM(id) AS tp_sum FROM `user1` LIMIT 1
        $user = M('user1');
        var_dump($user->sum('id'));

九：动态查询

        // 1.getBy 动态查询
        //查找email=xiaoin@163.com的数据
        //SELECT * FROM `user1` LIMIT 1 
        $user = M('user1');
        var_dump($user->getByemail('zs@qq.com'));
        // 2.getFieldBy 动态查询
        //通过user得到相对应id值
        //SELECT `id` FROM `user1` LIMIT 1
        $user = M('user1');
        var_dump($user->getFieldByUser('ls', 'id'));

我是 PHP 程序员