ThinkPHP

查询表达式

  1. 比较查询

  2. 区间查询

  3. 其他查询

比较查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('student')->where('sno',18008002)->select();
        return json($result);
    }

}

运行结果

1
[{"sno":"18008002","sname":"张杰","ssex":null,"sage":21,"sdept":"IS"}]

再返回一下它的SQL语句

1
SELECT * FROM `student` WHERE `sno` = 18008002

这个是简写

完整的语法应该是

1
where(字段名，表达式，查询条件)

不区分大小写

这里就演示一下不等于

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('student')->where('sno','<>',18008002)->select();
//        return Db::getLastSql();
        return json($result);
    }

}

运行结果
1
[{"sno":"18008001","sname":"刘昊然","ssex":null,"sage":23,"sdept":"IS"},{"sno":"18008003","sname":"西巴哥","ssex":"男","sage":20,"sdept":"IS"},{"sno":"18008010","sname":"杨紫","ssex":null,"sage":26,"sdept":"IS"},{"sno":"201215121","sname":"李勇","ssex":"男","sage":20,"sdept":"CS"},{"sno":"201215122","sname":"刘晨","ssex":"女","sage":19,"sdept":"CS"},{"sno":"201215123","sname":"王敏","ssex":"女","sage":18,"sdept":"MA"},{"sno":"201215124","sname":"宋洁","ssex":"女","sage":20,"sdept":"MA"},{"sno":"201215125","sname":"张立","ssex":"男","sage":19,"sdept":"IS"}]

就会把不等于18008002的其他所有的信息输出

SQL语句

1
SELECT * FROM `student` WHERE `sno` <> 18008002

区间查询

使用like的模糊查询

例如找数开头的相关学科

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('course')->where('cname','like','数%')->select();
        return json($result);

    }

}

运行结果
1
[{"cno":1,"cname":"数据库","cpno":5,"ccredit":4},{"cno":2,"cname":"数学","cpno":null,"ccredit":2},{"cno":5,"cname":"数据结构","cpno":7,"ccredit":4},{"cno":6,"cname":"数据处理","cpno":null,"ccredit":2}]

这些都是数开头的学科

还可以使用数组来查询多个信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('course')->where('cname','like',['数%','信%'],'or')->select();
        return json($result);

    }

}

运行结果
1
[{"cno":1,"cname":"数据库","cpno":5,"ccredit":4},{"cno":2,"cname":"数学","cpno":null,"ccredit":2},{"cno":3,"cname":"信息系统","cpno":1,"ccredit":4},{"cno":5,"cname":"数据结构","cpno":7,"ccredit":4},{"cno":6,"cname":"数据处理","cpno":null,"ccredit":2}]

看到就比之前多了一个信息系统

官方推荐是使用快捷方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('course')->whereLike('cname','数%')->select();
        return json($result);

    }

}

和之前查询的一样，还有一个whereNotLike，意思就是除了参数，其他的都返回

Between的区间查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('student')->whereBetween('sno',[18008001,18008003])->select();
        return json($result);

    }

}

运行结果
1
[{"sno":"18008001","sname":"刘昊然","ssex":null,"sage":23,"sdept":"IS"},{"sno":"18008002","sname":"张杰","ssex":null,"sage":21,"sdept":"IS"},{"sno":"18008003","sname":"西巴哥","ssex":"男","sage":20,"sdept":"IS"}]

如果是whereNotBetween，很好理解，和上面的NotLike一样

其他查询

使用exp来自定义sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php


namespace app\index\controller;
use app\index\model\Student;
use think\Controller;
use think\Db;
use think\db\Where;

class DataTest5 extends Controller
{
    public function index(){
        $result=Db::name('student')->where('sno','exp','In(18008001,18008002,18008003)')->select();
        return json($result);

    }

}

运行结果
1
[{"sno":"18008001","sname":"刘昊然","ssex":null,"sage":23,"sdept":"IS"},{"sno":"18008002","sname":"张杰","ssex":null,"sage":21,"sdept":"IS"},{"sno":"18008003","sname":"西巴哥","ssex":"男","sage":20,"sdept":"IS"}]

快捷模式是whereExp