Yii和ThinkPHP的增删改查

//Yii$db=Yii::app()->db->createCommand();//增$db->insert('{{student}}',array('xingming'=>'zhangsan','xingbie'=>'nv', 'grade'=>87));$db->reset();//清空上次操作//删$db->delete('{{student}}', 'id=:id', array(':id'=>$id));$db->reset();//改$db->update('{{student}}', array('grade'=>'99'), 'xingming=:xingming and xingbie=:xingbie', array(':xingming'=>$xingming,':xingbie'=>$xingbie));$db->reset();//查$db->select('c.typename, g.developers, p.description ')->from('{{game_position_data}} p')->Join( '{{category}} c', 'p.gameid = c.id' )->Join( '{{games}} g', 'g.gameid = c.id' )->where('p.posid=:posid', array(':posid'=>$posid))->queryRow();$db->reset();//queryAll()-----查询所有行//queryRow()-----查询第一行//queryColumn()--查询第一列//queryScalar()--查询第一行的第一列//sql语句实现复杂的增删改查Yii::app()->db->createCommand($sql)->query();//执行一条mysql语句（增删改查）,返回对象Yii::app()->db->createCommand($sql)->queryAll();//执行一条查询语句,并返回多行查询结果Yii::app()->db->createCommand($sql)->queryRow();//执行一条查询语句,并返回一行查询结果Yii::app()->db->createCommand($sql)->queryColumn();//执行一条查询语句,并返回一列查询结果Yii::app()->db->createCommand($sql)->queryScalar();//执行一条查询语句,并返回1个查询结果//从上面得知query的范围更广，但结果是对象，一般用来增删改。后三个一般用来查询//例子：//1、set2个，中间用逗号。where2个，中间用and//2、yii原有的update条件只能有一个，但使用sql语句，就能满足多个条件$sql="update student set starttime='2017-10-01 00:00:00',endtime='2017-10-11 23:59:59' where xingming='zs' and age='14'";Yii::app()->db->createCommand($sql)->query();//另一种：一种防注入的方式$sql1="select sum(if(starttime>'09:00:00',1,0)) as late,sum(if(endtime<'18:00:00',1,0)) as early from present where userid=:userid and date between :date_start and :date_end";$sql2=array(':userid' => 115,':date_start'=>'2009-12-1',':date_end'=>'2009-12-31');$results =Yii::app()->db->createCommand($sql)->query($sql2);foreach($results as $result){  echo $result['late']," and ",$result['early']," /n";}

//TinkPHP//增M('UserRecord')->add($data);// if_user_record表, $data是一个数组//删M('UserRecord') - >where('id=1') - >delete();//改M('UserRecord')->where("id='$id'")->save($data);//$data是一个数组M("UserRecord")->where("id='$id'")->setInc('apprentices',1.5);//数字字段+1.5//查select()---查询多行find()-----查询一行getField('name')-查询某个字段//1、单表查询M('Room')->field('roomid,roomname,roomaddr')->where("id='$id'")->find();//find获取一条记录,select获取所有条记录//2、关联查询两种方式M('User')->where("aso_workroom.status = 0 and aso_user.type = 2")->join(" left join aso_workroom on aso_user.username = aso_workroom.id")->select();M()->table('if_room r,if_user u')->where('r.id=u.roomid and u.type=2')->select();//注意：M("User")里面的表是去掉表前缀后的表，如想要整个表名，用M()->table("abc_user")