Yii 使用数据库 之 Query Builder详细讲解

1、构建执行
手册文档：https://www.yiichina.com/doc/api/1.1/CDbCommand

$command = Yii::app()->db->createCommand();

2、查询select

$command = Yii::app()->db->createCommand();
//        $command->select('id,username');                     #字符串
//        $command->select(['id','username']);                 #数组
//        $command->select('test.id,test.username');           #表名.字段名
$command->select(array('id', 'count(*) as num'));               #// SELECT `id`, count(*) as num
$command->from('test');
$data = $command->queryRow();

3、唯一查询：selectDistinct()

#去重查询：selectDistinct()
$command->selectDistinct('username,sex');

4、where

#where()
#1.and：username="hao" AND (sex=1 OR sex=2)
//        $command->where(['and','username="hao"',['or','sex=1','sex=2']]);       #and数组，or数组
#2.or：与and类似
#3.in：
//        $command->where(['in','sex',[1,2]]);
#4.not in：与in类似
//        $command->where(['not in','sex',[1,2]]);
#5.like
//        $command->where(['like','username','%hao%']);
#where username like '%hao%' and username like %王%
//        $command->where(['like','username',['%hao%','%王%']]);
#6.not like : where username not like "%hao%"
#7.or like : WHERE `username` LIKE '%hao%'
//        $command->where(['or like','username','%hao%']);
#8.WHERE `username` LIKE '%hao%' OR `username` LIKE '%王%'
//        $command->where(['or like','username',['%hao%','%王%']]);
#9.WHERE `username` NOT LIKE '%hao%' OR `username` NOT LIKE '%王%'
//        $command->where(['or not like','username',['%hao%','%王%']]);

5、andWhere

//andWhere($conditions, $params=array())

#where username = 'hao'
$command->andWhere('username=:username',[':username'=>'hao']);

#where username = 'hao' or sex=1
$command->andWhere('username=:username or sex=1',[':username'=>'hao']);

6、orWhere

//orWhere($conditions, $params=array())

7、

//orWhere($conditions, $params=array())

8、order

//order($columns)
#ORDER BY `username`, `id` DESC
//        $command->order('username, id desc');

#ORDER BY `username ASE`, `id` DESC
//        $command->order(['username ASE', 'id desc']);

9、limit() and offset()

//limit() and offset()
//limit($limit, $offset=null);offset($offset)

#LIMIT 10
//        $command->limit(10);

#LIMIT 10 OFFSET 2
//        $command->limit(10,2);

#OFFSET 2：offset必须与limit一起使用，组合成;LIMIT 10 OFFSET 2
//        $command->offset(2);

10、join() and its variants

#JOIN：连接查询：存在两张表中的数据
#10.1:join($table, $conditions, $params=array())
#即是内连接：INNER JOIN；两边表同时符合条件的组合，但是通常情况下，使用INNER JOIN需要指定连接条件。
#JOIN `test_copy` `t2` ON t1.id=t2.t_id and t1.sex=2;
$command->join('test_copy as t2','t1.id=t2.t_id and sex=:sex',[':sex'=>2]);

 #10.2:leftJoin($table, $conditions, $params=array())
#LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
#显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL
#LEFT JOIN `test_copy` `t2` ON t1.id=t2.t_id
$command->leftJoin('test_copy as t2','t1.id=t2.t_id');

#10.3:rightJoin($table, $conditions, $params=array())
#RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
#显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL
#RIGHT JOIN `test_copy` `t2` ON t1.id=t2.t_id
$command->rightJoin('test_copy as t2','t1.id=t2.t_id');

#10.4：crossJoin($table)
#交叉连接CROSS JOIN：会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
#CROSS JOIN `test_copy` `t2`
$command->crossJoin('test_copy as t2');

#10.5：naturalJoin($table)
#具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。
#因此，NATURAL JOIN 不用指定匹配条件。NATURAL JOIN 默认是同名字段完全匹配的 INNER JOIN
#NATURAL JOIN `test_copy` `t2`
$command->naturalJoin('test_copy as t2');

11：group($columns)

#group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析，以及配合having进行筛选后过滤。
#GROUP BY `sex`
$command->group('sex','username');          #第一个字段，一般不要这样写
#GROUP BY `sex`, `username`
$command->group('sex,username');            #字符串
#GROUP BY `sex`, `username`     
$command->group(['sex','username']);        #数组

12：having()

#12：having($conditions, $params=array())
#having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
#HAVING id=1 or id=2
$command->having('id=1 or id=2');
$command->having(['or', 'id=1', 'id=2']);

13、union()

#13:union()
#union($sql)
#UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
#同时，每条 SELECT 语句中的列的顺序必须相同.
#UNION (select * from test_copy)
$command->union('select * from test_copy');


#如果允许重复的值，请使用 UNION ALL。
SELECT *
FROM `test`
UNION ALL (select * from test_copy)

14、其他

#1.select
$command->select(array('id', 'username'));
$command->select = array('id', 'username');

#2.createCommand组装查询
$row = Yii::app()->db->createCommand(array(
    'select' => array('id', 'username'),
    'from' => 'tbl_user',
    'where' => 'id=:id',
    'params' => array(':id'=>1),
))->queryRow();

#3.释放先前查询语句
$command = Yii::app()->db->createCommand();
$users = $command->select('*')->from('tbl_users')->queryAll();
$command->reset();  // clean up the previous query
$posts = $command->select('*')->from('tbl_posts')->queryAll();

15、增

15、insert($table, $columns)
#INSERT INTO `test` (`username`, `sex`) VALUES (:username, :sex)
$data = $command->insert('test', array(
    'username' => 'hello',
    'sex'=>1,
));                      #成功返回1

#

16、改

#16、update($table, $columns, $conditions='', $params=array())
#单条修改
$data = $command->update('test',['username'=>'修改','update_time'=>date('Y-m-d H:i:s')],
          'id=:id',[':id'=>2]);

17、删

#17、delete($table, $conditions='', $params=array())
 #delete from test where id = 2
 $data = $command->delete('test','id=:id',[':id'=>2]);