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]);