PHP网页编程之yii Query Builder （yii 查询机关器）... - 仓酷云

/****QueryBuildertranslatedbyphp攻城师http://blog.csdn.net/phpgcsPreparingQueryBuilder筹办QueryBuilderBuildingDataRetrievalQueries构建数据查找查询BuildingDataManipulationQueries构建数据操纵查询BuildingSchemaManipulationQueries构建数据布局操纵查询****/YiiQueryBuilder供应了一个以面向工具的体例写SQL表达式的办法。同意开辟者用类的办法和属性来明白一个SQL表达式中的自力的单位。然后将分歧的单位组装成一个正当的SQL表达式，进而让DAO办法挪用和实行。上面是一个典范的利用YiiQueryBuilder来创建一个SELECTSQL语句的例子：$user=Yii::app()->db->createCommand()->select(id,username,profile)->from(tbl_useru)->join(tbl_profilep,u.id=p.user_id)->where(id=:id,array(:id=>$id))->queryRow();当你必要程序化地组装一个SQL语句时，大概基于一些分外的逻辑在你的使用中时，用YiiQueryBuilder是最好不外的了。次要的优点是：1,同意程序化创建一个庞大的SQL表达式2，主动援用标明和列名来避免跟SQL保存关头字和特别字符的抵触3，在能够的情形下援用参数值，利用参数绑定，从而下降了SQL注进打击的风险。4，供应必定水平的DB笼统，从而简化了向分歧DB平台迁徙的事情。并非强迫要利用QueryBuilder，现实上，假如你的查询很复杂，仍是间接写SQL语句来的快速便利。注重：QueryBuilder不成以被用语修正一个已被定制了的SQL表达式查询。以下代码是不会事情的：$command=Yii::app()->db->createCommand(SELECT*FROMtbl_user);//thefollowinglinewillNOTappendWHEREclausetotheaboveSQL$command->where(id=:id,array(:id=>$id));换句话说，不要把一般SQL和QueryBuilder夹杂利用！/*****1.PreparingQueryBuildertranslatedbyphp攻城师http://blog.csdn.net/phpgcs*****/QueryBuilder是跟CDbCommand相干联的，主DB查询类界说在DAO中。要入手下手利用QueryBuilder，我们创立一个CDbCommand实比方下：$command=Yii::app()->db->createCommand();我们利用Yii::app()->db取得DBconnection,然后用CDbConnection::createCommand()来创立实例.注重：这里我们不是想在DAO中把一全部SQL语句给了createCommand()，而是留空了。这是由于我们将在前面利用QueryBuilder的办法来构建这个SQL表达式的分歧部分。/*****2.BuildingDataRetrievalQueriestranslatedbyphp攻城师http://blog.csdn.net/phpgcs*****/Dataretrievalqueries指的是SELECTSQLstatements.querybuilder供应了一系列办法来创建一个SELCET语句的分歧部分。由于一切这些办法前往了CDbCommand实例，我们能够经由过程利用办法链来挪用它们。以下：select():specifiestheSELECTpartofthequeryselectDistinct():specifiestheSELECTpartofthequeryandturnsontheDISTINCTflagfrom():specifiestheFROMpartofthequerywhere():specifiestheWHEREpartofthequeryandWhere():appendsconditiontotheWHEREpartofthequerywithANDoperatororWhere():appendsconditiontotheWHEREpartofthequerywithORoperatorjoin():appendsaninnerjoinqueryfragmentleftJoin():appendsaleftouterjoinqueryfragmentrightJoin():appendsarightouterjoinqueryfragmentcrossJoin():appendsacrossjoinqueryfragmentnaturalJoin():appendsanaturaljoinqueryfragmentgroup():specifiestheGROUPBYpartofthequeryhaving():specifiestheHAVINGpartofthequeryorder():specifiestheORDERBYpartofthequerylimit():specifiestheLIMITpartofthequeryoffset():specifiestheOFFSETpartofthequeryunion():appendsaUNIONqueryfragmentInthefollowing,weexplainhowtousethesequerybuildermethods.Forsimplicity,weassumetheunderlyingdatabaseisMySQL.NotethatifyouareusingotherDBMS,thetable/column/valuequotingshownintheexamplesmaybedifferent.select()functionselect($columns=*)这个办法定制了查询的SELECT部分。参数$columns定制了将要被选择的列，既能够是被逗号分开开的列，也能够是一个由列名组成的数组。列名能够包括表前缀和（或）列别号。这个办法将主动援用列名，除非某个列包括了拔出语（意味着谁人列是由一个DB表达式供应）看例子：//SELECT*select()//SELECT`id`,`username`select(id,username)//SELECT`tbl_user`.`id`,`username`AS`name`select(tbl_user.id,usernameasname)//SELECT`id`,`username`select(array(id,username))//SELECT`id`,count(*)asnumselect(array(id,count(*)asnum))from()functionfrom($tables)看例子：//FROM`tbl_user`from(tbl_user)//FROM`tbl_user``u`,`public`.`tbl_profile``p`from(tbl_useru,public.tbl_profilep)//FROM`tbl_user`,`tbl_profile`from(array(tbl_user,tbl_profile))//FROM`tbl_user`,(select*fromtbl_profile)pfrom(array(tbl_user,(select*fromtbl_profile)p))where()functionwhere($conditions,$params=array())个中$conditions参数既能够是一个(e.g.id=1)也能够是一个以下格局的array:array(operator,operand1,operand2,...)operator有以下几个:and:array(and,id=1,id=2)=====>id=1ANDid=2.array(and,type=1,array(or,id=1,id=2))=====>type=1AND(id=1ORid=2).这个办法不会做任何的quoting大概escaping.or:相似andin:array(in,id,array(1,2,3))=====>idIN(1,2,3).Themethodwillproperlyquotethecolumnnameandescapevaluesintherange.notin:like:operand1====>acolumnorDBexpressionoperand2====>astringoranarrayarray(like,name,%tester%)=====>nameLIKE%tester%array(like,name,array(%test%,%sample%))=====>nameLIKE%test%ANDnameLIKE%sample%.Themethodwillproperlyquotethecolumnnameandescapevaluesintherange.notlike:orlike:ornotlike:看例子：//WHEREid=1orid=2where(id=1orid=2)//WHEREid=:id1orid=:id2where(id=:id1orid=:id2,array(:id1=>1,:id2=>2))//WHEREid=1ORid=2where(array(or,id=1,id=2))//WHEREid=1AND(type=2ORtype=3)where(array(and,id=1,array(or,type=2,type=3)))//WHERE`id`IN(1,2)where(array(in,id,array(1,2))//WHERE`id`NOTIN(1,2)where(array(notin,id,array(1,2)))//WHERE`name`LIKE%Qiang%where(array(like,name,%Qiang%))//WHERE`name`LIKE%QiangAND`name`LIKE%Xuewhere(array(like,name,array(%Qiang,%Xue)))//WHERE`name`LIKE%QiangOR`name`LIKE%Xuewhere(array(orlike,name,array(%Qiang,%Xue)))//WHERE`name`NOTLIKE%Qiang%where(array(notlike,name,%Qiang%))//WHERE`name`NOTLIKE%Qiang%OR`name`NOTLIKE%Xue%where(array(ornotlike,name,array(%Qiang%,%Xue%)))当有like时，我们必要断定%and_.假如来自用户的输出，我们还应当利用以下的代码来过滤失落特别字符避免它们被看成通配符（wildcards）$keyword=$_GET[q];//escape%and_characters$keyword=strtr($keyword,array(%=>\%,_=>\_));$command->where(array(like,title,%.$keyword.%));andWhere()functionandWhere($conditions,$params=array())orWhere()functionorWhere($conditions,$params=array())order()functionorder($columns)看例子：//ORDERBY`name`,`id`DESCorder(name,iddesc)//ORDERBY`tbl_profile`.`name`,`id`DESCorder(array(tbl_profile.name,iddesc))limit()andoffset()functionlimit($limit,$offset=null)functionoffset($offset)注重，一些DBMS不撑持LIMITandOFFSET，可是我们的theQueryBuilder将会重写全部SQL语句来摹拟limitandoffset.的功效。看例子：//LIMIT10limit(10)//LIMIT10OFFSET20limit(10,20)//OFFSET20offset(20)join()anditsvariantsfunctionjoin($table,$conditions,$params=array())functionleftJoin($table,$conditions,$params=array())functionrightJoin($table,$conditions,$params=array())functioncrossJoin($table)functionnaturalJoin($table)注重：不像其他的querybuilder办法，每次挪用join将会被增加到之前的join。看例子：//JOIN`tbl_profile`ONuser_id=idjoin(tbl_profile,user_id=id)//LEFTJOIN`pub`.`tbl_profile``p`ONp.user_id=idANDtype=1leftJoin(pub.tbl_profilep,p.user_id=idANDtype=:type,array(:type=>1))group()functiongroup($columns)看例子：//GROUPBY`name`,`id`group(name,id)//GROUPBY`tbl_profile`.`name`,`id`group(array(tbl_profile.name,id))having()functionhaving($conditions,$params=array())看例子：//HAVINGid=1orid=2having(id=1orid=2)//HAVINGid=1ORid=2having(array(or,id=1,id=2))union()functionunion($sql)看例子：//UNION(select*fromtbl_profile)union(select*fromtbl_profile)/*****ExecutingQueries实行查询****/经由过程挪用下面的querybuilder办法来机关查询，我们就能够用DAO办法来实行这个查询了。比方：$users=Yii::app()->db->createCommand()->select(*)->from(tbl_user)->queryAll();/****回复SQLs****/CDbCommand::getText().$sql=Yii::app()->db->createCommand()->select(*)->from(tbl_user)->text;假如绑定有参数，我们也能够经由过程CDbCommand::paramsproperty来取得参数。其他语法BuildingQueries偶然候经由过程办法链来构建查询并非最好选择。举例来讲，上面的两个表达是等价的，假定$command代表了一个CDbCommand工具：$command->select(array(id,username));$command->select=array(id,username);进一步CDbConnection::createCommand()办法能够把array作为参数.以下：$row=Yii::app()->db->createCommand(array(select=>array(id,username),from=>tbl_user,where=>id=:id,params=>array(:id=>1),))->queryRow();BuildingMultipleQueries构建多查询一个CDbCommand实例能够被用屡次来构建几个查询。在构建新查询之前，要利用reset()办法来分明之前的设置。$command=Yii::app()->db->createCommand();$users=$command->select(*)->from(tbl_users)->queryAll();$command->reset();//cleanupthepreviousquery$posts=$command->select(*)->from(tbl_posts)->queryAll();/******3.BuildingDataManipulationQueriestranslatedbyphp攻城师http://blog.csdn.net/phpgcs*****/数据操纵指的是在一个DB表中inserting,updatinganddeleting数据。insert():insertsarowintoatableupdate():updatesthedatainatabledelete():deletesthedatafromatableinsert()functioninsert($table,$columns)看例子：//buildandexecutethefollowingSQL://INSERTINTO`tbl_user`(`name`,`email`)VALUES(:name,:email)$command->insert(tbl_user,array(name=>Tester,email=>tester@example.com,));update()functionupdate($table,$columns,$conditions=,$params=array())看例子：//buildandexecutethefollowingSQL://UPDATE`tbl_user`SET`name`=:nameWHEREid=:id$command->update(tbl_user,array(name=>Tester,),id=:id,array(:id=>1));delete()functiondelete($table,$conditions=,$params=array())看例子：//buildandexecutethefollowingSQL://DELETEFROM`tbl_user`WHEREid=:id$command->delete(tbl_user,id=:id,array(:id=>1));/*******4.BuildingSchemaManipulationQueriestranslatedbyphp攻城师http://blog.csdn.net/phpgcs******/除惯例的查找查询和操纵查询，另有一系列的办法来构建和实行能够操纵数据库布局的SQL查询。createTable():createsatablerenameTable():renamesatabledropTable():dropsatabletruncateTable():truncatesatableaddColumn():addsatablecolumnrenameColumn():renamesatablecolumnalterColumn():altersatablecolumnaddForeignKey():addsaforeignkey(availablesince1.1.6)dropForeignKey():dropsaforeignkey(availablesince1.1.6)dropColumn():dropsatablecolumncreateIndex():createsanindexdropIndex():dropsanindexAbstractDataTypes笼统数据范例本色上就是为了兼容分歧的DBMS而构建的数据范例，一个通用的接口。pk:agenericprimarykeytype,willbeconvertedintoint(11)NOTNULLAUTO_INCREMENTPRIMARYKEYforMySQL;string:stringtype,willbeconvertedintovarchar(255)forMySQL;text:texttype(longstring),willbeconvertedintotextforMySQL;integer:integertype,willbeconvertedintoint(11)forMySQL;float:floatingnumbertype,willbeconvertedintofloatforMySQL;decimal:decimalnumbertype,willbeconvertedintodecimalforMySQL;datetime:datetimetype,willbeconvertedintodatetimeforMySQL;timestamp:timestamptype,willbeconvertedintotimestampforMySQL;time:timetype,willbeconvertedintotimeforMySQL;date:datetype,willbeconvertedintodateforMySQL;binary:binarydatatype,willbeconvertedintoblobforMySQL;boolean:booleantype,willbeconvertedintotinyint(1)forMySQL;money:money/currencytype,willbeconvertedintodecimal(19,4)forMySQL.Thistypehasbeenavailablesinceversion1.1.8.createTable()functioncreateTable($table,$columns,$options=null)//CREATETABLE`tbl_user`(//`id`int(11)NOTNULLAUTO_INCREMENTPRIMARYKEY,//`username`varchar(255)NOTNULL,//`location`point//)ENGINE=InnoDBcreateTable(tbl_user,array(id=>pk,username=>stringNOTNULL,location=>point,),ENGINE=InnoDB)renameTable()functionrenameTable($table,$newName)//RENAMETABLE`tbl_users`TO`tbl_user`renameTable(tbl_users,tbl_user)dropTable()functiondropTable($table)//DROPTABLE`tbl_user`dropTable(tbl_user)truncateTable()functiontruncateTable($table)//TRUNCATETABLE`tbl_user`truncateTable(tbl_user)addColumn()functionaddColumn($table,$column,$type)//ALTERTABLE`tbl_user`ADD`email`varchar(255)NOTNULLaddColumn(tbl_user,email,stringNOTNULL)dropColumn()functiondropColumn($table,$column)//ALTERTABLE`tbl_user`DROPCOLUMN`location`dropColumn(tbl_user,location)renameColumn()functionrenameColumn($table,$name,$newName)//ALTERTABLE`tbl_users`CHANGE`name``username`varchar(255)NOTNULLrenameColumn(tbl_user,name,username)alterColumn()//ALTERTABLE`tbl_user`CHANGE`username``username`varchar(255)NOTNULLalterColumn(tbl_user,username,stringNOTNULL)addForeignKey()functionaddForeignKey($name,$table,$columns,$refTable,$refColumns,$delete=null,$update=null)//ALTERTABLE`tbl_profile`ADDCONSTRAINT`fk_profile_user_id`//FOREIGNKEY(`user_id`)REFERENCES`tbl_user`(`id`)//ONDELETECASCADEONUPDATECASCADEaddForeignKey(fk_profile_user_id,tbl_profile,user_id,tbl_user,id,CASCADE,CASCADE)dropForeignKey()functiondropForeignKey($name,$table)//ALTERTABLE`tbl_profile`DROPFOREIGNKEY`fk_profile_user_id`dropForeignKey(fk_profile_user_id,tbl_profile)createIndex()functioncreateIndex($name,$table,$column,$unique=false)//CREATEINDEX`idx_username`ON`tbl_user`(`username`)createIndex(idx_username,tbl_user,username)dropIndex()functiondropIndex($name,$table)//DROPINDEX`idx_username`ON`tbl_user`dropIndex(idx_username,tbl_user)

复制代码