1. sql语句是从右往左执行的。
eg. select * from stu,credit; 把credit看做主表
多表连接
查询女生少于200人的系
select count(*) girls,sdept from student where ssex='F' group by sdept having girls<200
select t1.*,c.cname from course c,(select count(*),cid from studcourse where grade<60 group by cid) t1 where t1.cid=c.cid
course c,c 是course的别名,(select count(*),cid from studcourse where grade<60 group by cid) 是暂时的表,起别名为t1,查找t1的总共人数,及科目序号cid,以及c.cname;
若是只需要查t1表中的count(*),而不需要cid,则需要给count(*)起别名,
select t1.nums,c.cname from course c,(select count(*) nums,cid from studcourse where grade<60 group by cid) t1 where t1.cid=c.cid
聚集函数
select count(*),sdept from student where sdept="计算机系" 此语句错误
当一个是聚集函数,一个变化的值时,必须后面要用分组,即group by
正确:select count(*),"计算机系" from student where sdept="计算机系"
select count(*) sdept from student group by sdept
2. quteInto() 参数绑定
可读性好,防注入,性能提高
单条件+查询全部字段 二维数组 需要toArray()
$db = $tableModel->getAdapter();
$where = $db->quoteInto("id=?","10");
$order="";//按什么排序 $count="";//显示多少,$offset="";//从哪里开始显示
$res = $tableModel->fetchAll($where,$order,$count,$offset)->toArray();
多条件+全部字段 二维数组 需要toArray()
$where = $db->quoteInto("id=?","10").$db->quoteInto("AND xx=?","**");
$order....
$res = $tableModel->fetchAll($where,$order,$count,$offset)->toArray();
单条件+部分字段 一维数组 不需要toArray()
$sql=$db->quoteInto("select a,b from table where id=?","1");
$res = $db->query($sql)->fetchAll();
多条件+部分字段 一维数组 不需要toArray()
$res = $db->query("select a,c from table where a=:a and b=:b",array('a'=>5,'b'=>7))->fetchAll();