一：字符串条件查询

        //直接实例化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\'));

十：SQL查询

　　　　　// 1.query 读取
        //查询结果集，如果采用分布式读写分离，则始终在读服务器执行
        //SELECT * FROM user1
        $user = M(\'user1\');
        var_dump($user->query(\'SELECT * FROM user1\'));
        // 2.execute写入
        //更新和写入，如果采用分布式读写分离，则始终在写服务器执行
        //UPDATE user1 set name="xuexi" WHERE id="1";
        $user = M(\'user1\');
        var_dump($user->execute(\'UPDATE user1 set name="xuexi" WHERE
         id="1";\'));

 十一：连贯查询

 通过连贯操作可以有效的提供数据存取的代码清晰度和开发效率，并且支持所有的 CURD 操作

        //连贯操作
        //PS：这里的 where、order 和 limit 方法都是连贯操作方法，所以它们都能返回$user本身，可以互换位置。而 select 方法不是连贯方法，需要放在最后，用以显示数据集。
        //SELECT * FROM `user1` WHERE ( id in (1,2,3,4) ) LIMIT 2 
        $user=M(\'user1\');
        var_dump($user->where(\'id in (1,2,3,4)\')->limit(2)->select());
        //数组操作
        //SELECT * FROM `user1` WHERE id in (1,2,3,4) LIMIT 2 
        $user = M(\'user1\');
        var_dump($user->select(array(\'where\'=>\'id in (1,2,3,4)\', \'limit\'=>\'2\',
        )));
        // CURD处理
        // SELECT * FROM `user1` WHERE ( id=1 ) LIMIT 1
        // DELETE FROM `user1` WHERE ( id=2 )
        $user = M(\'user1\');
        var_dump($user->where(\'id=1\')->find());
        var_dump($user->where(\'id=2\')->delete());

 

 1.where(支持字符串条件、数组条件（推荐用法）和多次调用。)

        //字符串方式
        //SELECT * FROM `user1` WHERE ( id=6 )
        var_dump($user->where(\'id=6\')->select());

        //索引数组方式
        // SELECT * FROM `user1` WHERE `id` = 6
        $map[\'id\']=6;
        var_dump($user->where($map)->select());

        //多次调用方式
        SELECT * FROM `user1` WHERE `id` = 6 AND ( name="test" ) 
        $map[\'id\']=array(\'eq\',6);
        var_dump($user->where($map)->where(\'name="test"\')->select());

2.order 用于对结果集排序

    // 倒序
        // SELECT * FROM `user1` ORDER BY id desc
        var_dump($user->order(\'id desc\')->select());

        // 第二排序
        // SELECT * FROM `user1` ORDER BY id desc,age desc
        var_dump($user->order(\'id desc,age desc\')->select());

        //数组形式，防止字段和mysql关键字冲突
        // SELECT * FROM `user1` ORDER BY `id` DESC,`email` DESC
        $map[\'id\']=1;
        var_dump($user->order(array(\'id\'=>\'DESC\',\'email\'=>\'DESC\'))->select());

3.feild（feild 方法可以返回或操作字段，可以用于查询和写入操作。）

　　　　// 只显示id和name两个字段
      // SELECT `id`,`name` FROM `user1` 
        var_dump($user->field(\'id,name\')->select());

      //使用SQL函数和别名
      // SELECT SUM(id) as count,`name` FROM `user1`
      var_dump($user->field(\'SUM(id) as count,name\')->select());

      // 使用数组参数结合SQL函数
      // SELECT `id`,LEFT(name,1) AS `left_user` FROM `user1`
      var_dump($user->field(array(\'id\',\'LEFT(name,1)\'=>\'left_user\'))->select());

      //获取所有字段
      // SELECT * FROM `user1` 
      var_dump($user->field()->select());

      //用于写入
      $user->field(\'name,email\')->create();

 4.limit（主要用于指定查询和操作的数量）

      //限制结果集数量
      // SELECT * FROM `user1` LIMIT 2
      var_dump($user->limit(2)->select());

      //分页查询
      // SELECT * FROM `user1` LIMIT 1,2 
      var_dump($user->limit(1,2)->select());

5.page（page 方法完全用于分页查询）

      //page分页
      // SELECT * FROM `user1` LIMIT 3,3
      var_dump($user->page(2,3)->select());

6.table（用于数据表操作，主要是切换数据表或多表操作）

      //切换数据表
      // SELECT * FROM `test_user`
      var_dump($user->table(\'test_user\')->select());

7.alias （用于设置数据表别名）

      // 设置别名
      // SELECT * FROM user1 a 
      var_dump($user->alias(\'a\')->select());

8.group（用于对结合函数统计的结果集分组）

　　　　// 分组统计
      // SELECT `name`,max(id) FROM `user1` GROUP BY id
      var_dump($user->field(\'name,max(id)\')->group(\'id\')->select());

9.having（用于配合 group 方法完成从分组的结果中再筛选数据）

      //分组统计结合having
      // SELECT `user`,max(id) FROM `user1` GROUP BY id HAVING id>2 
      var_dump($user->field(\'user,max(id)\')->group(\'id\')->having(\'id>2\')->select());

10.comment （用于对 SQL 语句进行注释）

      //SQL注释
      // SELECT * FROM `user1` /* test */
      var_dump($user->comment(\'test\')->select());

命名空间

命名范围其实就是将 SQL 语句封装在模型定义类里，而不在控制器里。

在Model下新建一个User1Model.class.php文件

<?php
namespace Home\Model;
use Think\Model;
class User1Model extends Model
{
    //定义属性
protected $_scope = array( //属性名必须是_scope
        \'sql1\'=>array(
        \'where\'=>array(\'id\'=>1),
        ),
        \'sql2\'=>array(
        \'order\'=>\'date DESC\',
        \'limit\'=>2,
        ),
        \'default\'=>array(
        \'where\'=>array(\'id\'=>2),
        ),
        );
}

命名范围支持的属性有：where、field、order、table、limit、page、having、group、lock、distinct、cache

在Controller目录下新建一个User1Controller.class.php文件

<?php
namespace Home\Controller;
use Think\Controller;
use Home\Model\User1Model;
class User1Controller extends Controller {
    public function model(){
        // ps：一定要填入表名，user1
        // 他会先去model文件夹找有没有user1model
        // 如果有就应用
        // 如果没有，会直接引用model基类
        $user=D(\'user1\');
        // sql语句：SELECT * FROM `user1` WHERE `id` = 1
        var_dump($user->scope(\'sql1\')->select());
    }
}

版权声明：本文为8013-cmf原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/8013-cmf/p/8317720.html