php基本的查询方法,查询方法详解

[TOC]

### select($columns)

~~~php

//不写select，默认是*

//SELECT * FROM `user`

$query->from('user');

//字符串形式

//SELECT `id`, `lying`.`sex`, count(id) AS `count`

$query->select('id, lying.sex, count(id) as count');

//如果需要对括号内的字段进行反引号处理

//SELECT `id`, `lying`.`sex`, count([[id]]) AS `count`

$query->select('id, lying.sex, count(`id`) as count');

//数组形式

//SELECT `id`, `lying`.`sex`, `username` AS `name`

$query->select(['id', 'lying.sex', 'name'=>'username']);

//使用到包含逗号的数据库表达式的时候，你必须使用数组的格式，以避免自动的错误的引号添加

//SELECT CONCAT(first_name, ' ', last_name) AS `full_name`, `email`

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

//或者

//SELECT CONCAT(first_name, ' ', last_name) AS `full_name`, `email`

$query->select(['full_name'=>"CONCAT(first_name, ' ', last_name)", 'email']);

//同理，如果需要对括号内的字段添加反引号，则

//SELECT CONCAT(`first_name`, ' ', `last_name`) AS `full_name`, `email`

$query->select(['full_name'=>"CONCAT([[first_name]], ' ', [[last_name]])", 'email']);

//使用子查询

//SELECT (SELECT count(`money`) from `pay` where` id`=1) AS `money`

$subquery = $db->query()->select('count([[money]])')->from('pay')->where(['id'=>1]);

$query->select('money'=>$subquery);

~~~

### distinct($distinct = true)

~~~php

//在查询字段的前面加上DISTINCT

//SELECT DISTINCT `id`, `username`

$query->select(['id', 'username'])->distinct();

//去掉DISTINCT

//SELECT `id`, `username`

$query->select(['id', 'username'])->distinct(false);

~~~

### from($tables)

~~~php

//字符串形式

//SELECT * FROM `user`, `lying`.`member` AS `member`

$query->from('user, lying.menber as member');

//使用表前缀

//SELECT * FROM `prefix_user`

$query->from('{{%user}}');

//使用数组，别名以及表前缀

//SELECT * FROM `prefix_table1`, `table2` AS `t2`

$query->from(['{{%table1}}', 't2'=>'table2']);

//使用子查询

//SELECT * FROM (SELECT `id`, `username` FROM `user` WHERE `id`=1) AS `t`

$subquery = $db->query()->select('id, username')->from('user')->where(['id'=>1]);

$query->from(['t'=>$subquery]);

~~~

### join($type, $table, $on = null, $params = [])

~~~php

//支持的join类型：'left join','right join','inner join'

//SELECT * FROM `user` LEFT JOIN `table` ON user.id=table.uid

$query->from('user')->join('left join', 'table', 'user.id=table.uid');

//关联的table请参考`from()`的用法，包括别名、子查询等

//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON user.id=t.uid

$query->from('user')->join('left join', ['t'=>'table'], 'user.id=t.uid');

//子查询

//SELECT * FROM `user` LEFT JOIN (SELECT `uid`, `username` FROM `pay` WHERE `uid` = 1) AS `p` ON user.id=p.uid

$subquery = $db->query()->select('uid, username')->from('pay')->where(['uid'=>1]);

$query->from('user')->join('left join', ['p'=>$subquery ], 'user.id=p.uid');

//ON条件支持字符串形式和数组形式，如果要使用'字段1 = 字段2'的形式,请用字符串带入,用数组的话'字段2'将被解析为绑定参数

//字符串形式如果要给字段加上反引号，可以用[[字段]]形式

//字符串形式ON + 参数绑定(ON条件参见where()用法)

//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `user`.`id`=`t`.`uid` AND `t`.`id` < 100

$query->from('user')->join('left join', ['t'=>'table'], '[[user.id]]=[[t.uid]] and [[t.id]] < :tid', [':tid'=>100]);

//数组形式ON(ON条件参见where()用法)

//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `t`.`id` > 100

$query->from('user')->join('left join', ['t'=>'table'], ['>', 't.id', 100]);

//下面这种用法是错误的，因为`user.id`会被解析成字符串，而不是字段，除非你确定你要这么用

//SELECT * FROM `user` LEFT JOIN `table` AS `t` ON `t`.`id` = 'user.id'

$query->from('user')->join('left join', ['t'=>'table'], ['t.id'=>'user.id']);

//join支持多次调用哦

$query->from('user')->join('left join', ['t1'=>'table1'], '[[user.id]]=[[t1.uid]]');

->join('right join', ['t2'=>'table2'], '[[user.id]]=[[t2.uid]]');

~~~

### leftJoin($table, $on = null, $params = [])

~~~php

其实就是join的封装，type默认为left join

~~~

### rightJoin($table, $on = null, $params = [])

~~~php

其实就是join的封装，type默认为right join

~~~

### innerJoin($table, $on = null, $params = [])

~~~php

其实就是join的封装，type默认为inner join

~~~

### where($condition, $params = [])

> 字符串形式

~~~php

//WHERE id=1

$query->where('id=1 and sex > 0');

//WHERE `id`=1

$query->where('[[id]]=1'); //对字段名加上反引号

//WHERE `id`=1

$query->where('[[id]]=:id', [':id'=>1]); //参数绑定(推荐)

~~~

> 数组形式

数组形式的条件标准格式为：

~~~

[运算符, 操作1, 操作2, ...]

~~~

并且操作支持无限级嵌套：

~~~

[运算符, 操作1, [运算符1, 操作2, 操作3, ...], ...]

~~~

1. =

~~~php

//WHERE `id`=1

$where = ['=', 'id', 1];

$where = ['id'=>1]; //简写方式，等同于上面用法

$subquery = $db->query()->select(['MAX([[id]])'])->from(['user']); //SELECT MAX(`id`) FROM `user`

//WHERE `id` = (SELECT MAX(`id`) FROM `user`)

$where = ['=', 'id', $subquery]; //子查询

//WHERE `id` IN (SELECT MAX(`id`) FROM `user`)

$where = ['id'=>$subquery]; //子查询，简写方式会变成IN，这里要特别注意

~~~

2. AND

~~~php

//WHERE `id`=1 AND `sex`=2

$where = ['and', ['=', 'id', 1], ['=', 'sex', 2]];

$where = [['=', 'id', 1], ['=', 'sex', 2]]; //AND条件为默认值，可以省略，等同于上面用法

$where = ['id'=>1, 'sex'=>2]; //简写形式，等同于上面用法

~~~

3. OR

* 和`AND`用法一致，只不过`OR`运算符不能省略

4. IN

~~~php

//WHERE `id` IN (1, 2, 3)

$where = ['in', 'id', [1, 2, 3]];

$subquery = $db->query()->select(['id'])->from(['user']); //SELECT `id` FROM `user`

//WHERE `id` IN (SELECT `id` FROM `user`)

$where = ['in', 'id', $subquery];

$where = ['id'=>$subquery]; //简写方式，等同于上面用法

~~~

注意：错误用法 `$where = ['in', 'id', '(1, 2, 3)'];`

5. NOT IN

* 和`IN`用法一致，只不过不能使用简写方式

6. BETWEEN

~~~php

//WHERE `id` BETWEEN 1 AND 10

$where = ['between', 'id', [1, 10]];

~~~

7. NOT BETWEEN

* 和`BETWEEN`用法一致

8. LIKE

~~~php

//WHERE `name` LIKE '%lying%'

$where = ['like', 'name', '%lying%'];

~~~

9. NOT LIKE

* 和`LIKE`用法一致

10. IS NULL / IS NOT NULL

~~~php

//WHERE `sex` IS NULL

$where = ['null', 'sex', true];

$where = ['sex'=>null]; //简写方式，等同于上面用法

//WHERE `sex` IS NOT NULL

$where = ['null', 'sex', false];

~~~

11. EXISTS

~~~php

$subquery = $db->query()->select(['id'])->from(['user']);

//WHERE EXISTS (SELECT `id` FROM `user`)

$where = ['EXISTS', 'id', $subquery];

~~~

注意：错误用法 `$where = ['EXISTS', 'id', '(SELECT id FROM user)'];`

12. NOT EXISTS

* 和`EXISTS`用法一致

13. \>、=、<= 等这边没有列出的标准DB操作符

~~~php

//WHERE id > 1

$where = ['>', 'id', 1];

//WHERE id <= 1

$where = ['<=', 'id', 1];

~~~

> 数组用法示例

~~~php

$subquery = $db->query()->select(['id'])->from(['user']);

//WHERE `sex` = 1 AND (`id` > 100 OR `time` IS NULL) AND `id` IN (SELECT `id` FROM `user`)

$where = ['sex'=>1, ['or', ['>', 'id', 100], 'time'=>null], 'id'=>$subquery];

~~~

### andWhere($condition, $params = [])

* 和`where()`用法一致，只不过是在`where()`的条件上再追加`AND`条件

~~~php

$query->where(['status' => 1]);

if (!empty($search)) {

$query->andWhere(['like', 'title', "%{$search}%"]);

}

~~~

### orWhere($condition, $params = [])

* 和`andWhere()`用法一致，只不过是在`where()`的条件上再追加`OR`条件

### groupBy($columns)

~~~php

//GROUP BY `id`, `sex`

$query->groupBy('id, sex');

$query->groupBy(['id', 'sex']); //等同于上面的用法

~~~

### having($condition, $params = [])

* 参见`where()`的用法

### andHaving($condition, $params = [])

* 参见`andWhere()`的用法

### orHaving($condition, $params = [])

* 参见`orWhere()`的用法

### orderBy($columns)

~~~php

//ORDER BY `id` ASC, `sex` DESC

$query->orderBy('id, sex desc');

$query->orderBy(['id', 'sex'=>SORT_DESC]); //等同于上面的用法

~~~

### limit($offset, $limit = null)

~~~php

//LIMIT 1

$query->limit(1);

//LIMIT 10, 20

$query->limit(10, 20);

~~~

### union(Query $query, $all = false)

~~~php

$query1 = $db->query()->from(['user1']);

$query2 = $db->query()->from(['user2']);

//SELECT * FROM `user1` UNION (SELECT * FROM `user2`)

$query1->union($query2);

//SELECT * FROM `user1` UNION ALL (SELECT * FROM `user2`)

$query1->union($query2, true);

~~~