Yii Query Builder (Yii search constructor) Official Guide Translation _php tutorial

/**** Query Builder translated by PHP siege master Http://blog.csdn.net/phpgcs Preparing Query Builder prepare for query Buil Der Building Data Retrieval Queries build a lookup query Building the data manipulation Queries build a database operation query Building Schema Mani  Pulation queries building data structure operation query ****/Yii Query Builder provides a way to write SQL expressions in an object-oriented manner. Allows developers to use the methods and properties of a class to define a separate unit in an SQL expression.  The different units are then assembled into a legitimate SQL expression that allows the DAO method to invoke and execute. The following is a typical example of using Yii Query Builder to create a select SQL statement: $user = Yii::app ()->db->createcommand ()->select (' ID, u Sername, Profile ')->from (' Tbl_user u ')->join (' Tbl_profile p ', ' u.id=p.user_id ')->where (' Id=:id ')    , Array (': id ' = $id))->queryrow ();    It is best to use Yii Query Builder when you need to programmatically assemble an SQL statement, or based on some additional logic in your application.  The main benefits are: 1, allowing programmatic creation of a complex SQL Expression 2, automatic referencing and column names to prevent conflict with SQL reserved keywords and special characters 3, where possible to reference parameter values, using parameter binding, thereby reducing the risk of SQL injection attacks.    4, provides a certain degree of DB abstraction, simplifying the migration to different DB platforms.    It is not mandatory to use query Builder, in fact, if your query is simple, it is quick and easy to write SQL statements directly. Note: Query Builder cannot be modified by the termThe SQL expression query that was custom-made.  The following code is not working: $command = Yii::app ()->db->createcommand (' SELECT * from Tbl_user ');    The following line would not be append WHERE clause to the above SQL $command->where (' Id=:id ', Array (': id ' = $id));      In other words, don't mix plain SQL with Query Builder! /***** 1.  Preparing Query Builder translated by PHP Siege Division Http://blog.csdn.net/phpgcs *****/Query Builder is associated with Cdbcommand, the main  The DB query class is defined in DAO.    To get started with Query Builder, we create a Cdbcommand instance as follows: $command = Yii::app ()->db->createcommand ();    We use Yii::app ()->db to obtain the DB connection and then use Cdbconnection::createcommand () to create the instance.  Note: Here we do not want to give a whole SQL statement to CreateCommand () in DAO, but leave it blank.      This is because we will use the Query Builder method later to build the different parts of this SQL expression. /***** 2. Building data retrieval Queries translated by PHP Siege Division Http://blog.csdn.net/phpgcs *****/Data Retrieval Queries refers to     SELECT SQL statements.  Query Builder provides a series of methods to create different parts of a SELCET statement. Because all of these methods return Cdbcommand instances, we can invoke them by using the method chain. as follows: Select (): Specifies tHe select part of the Query selectdistinct (): Specifies the SELECT part of the query and turns on the DISTINCT flag from  (): Specifies the from part of the query where (): Specifies the where part of the query Andwhere (): Appends condition to The where part of the "Query with AND operator Orwhere (): Appends condition to the where part of the" Query with OR Operat or join (): Appends an inner join query fragment Leftjoin (): Appends a LEFT outer join query fragment Rightjoin (): Appen DS a RIGHT outer join query fragment CrossJoin (): Appends a cross join query fragment Naturaljoin (): Appends a natural j oin Query Fragment Group (): Specifies the group by part of the The query has (): Specifies the have part of the query O Rder (): Specifies the ORDER by part of the query limit (): Specifies the limit part of the query offset (): Specifies the OFFSET part of the Query Union (): Appends a union query fragment in the following, we explain how to use these query bui Lder methods. For SimpliCity, we assume the underlying database is MySQL.    Note that if you is using the other DBMS, the Table/column/value quoting shown in the examples could be different.  Select () function Select ($columns = ' * ') This method customizes the select part of the query.  Parameter $columns Customize the column that will be selected, either as a comma-delimited column or as an array of column names.  Column names can contain table prefixes and/or column aliases. This method will automatically refer to the column name, unless a column contains the caret (meaning that the column is provided by a DB expression) See Example://select * Select ()//select ' ID ', ' username ' select (' ID, user Name ')//select ' Tbl_user '. ' id ', ' username ' as ' name ' SELECT (' tbl_user.id, username as name ')//select ' ID ', ' usernam  E ' Select (Array (' ID ', ' username '))//select ' ID ', COUNT (*) as num Select (Array (' ID ', ' count (*) as Num ')) from ()  function from ($tables) See Example://From ' Tbl_user ' from (' tbl_user ')//' Tbl_user ' "U ', ' public '. ' Tbl_profile ' P ' From (' Tbl_user u, public.tbl_profile P ')//From ' Tbl_user ', ' Tbl_profile ' from (Array (' Tbl_user ', ' tbl_profile ')//FR OM ' Tbl_user ', (SELECT * from Tbl_profile) p from (Array (' Tbl_user ', ' "(SELECT * from tBl_profile)) where () function where ($conditions, $params =array ()) where the $conditions parameter can be either a (e.g. id=1) or a   Array:array in the following format (operator, OPERAND1, Operand2, ...)        Operator has the following several: And:array (' and ', ' id=1 ', ' id=2 ') =====> id=1 and id=2.       Array (' and ', ' type=1 ', Array (' or ', ' id=1 ', ' id=2 ')) =====> type=1 and (id=1 or id=2).    This method does not do any quoting or escaping. Or: Similar to and In:array (' in ', ' ID ', array (All-in-all)) =====> ID in (All-in).  The method would properly quote the column name and escape values in the range.             Not In:like:operand 1 ====> a column or DB expression operand 2 ====> a string or an array  Array (' Like ', ' name ', '%tester% ') =====> the name like '%tester% ' array (' like ', ' name ', Array ('%test% ', '%sample% '))     =====> name like '%test% ' and the name like '%sample% '.  The method would properly quote the column name and escape values in the range. Not Like:or Like:or don't like: see Example://WHERE id=1 or ID=2 where (' id=1 or id=2 ')//where ID=:ID1 or Id=:id2 where (' Id=:id1 or Id=:id2 ', Array (': Id1 ' =>1, ': Id2 ' =>2)// where Id=1 or id=2 where (array (' OR ', ' id=1 ', ' id=2 '))//Where id=1 and (type=2 OR type=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 ' isn't in   (1, 2) where (array (' not ', ' id ', array ())//Where ' name ' is '%qiang% ' where (' like ', ' name ', '%qiang% ')) Where ' name ' like '%qiang ' and ' name ' is '%xue ' where (array (' Like ', ' name ', Array ('%qiang ', '%xue '))//Where ' n Ame ' like '%qiang ' or ' name ' as '%xue ' where (Array (' OR like ', ' name ', Array ('%qiang ', '%xue '))//Where ' name ' is not LI  KE '%qiang% ' where (array (' Not like ', ' name ', '%qiang% ')//where ' name ' isn't like '%qiang% ' OR ' name ' isn't like '%xue% '   Where (array (' or not like ', ' name ', Array ('%qiang% ', '%xue% '))) when there is like, we need to determine the% and _. If input from the user, we should also use the following code to filter out special characters to prevent them from being treated as wildcards (wildcards) $keyword =$_get[' Q '];  Escape% and _ characters $keyword =strtr ($keyword, Array ('% ' = = ' \% ', ' _ ' = ' \_ ')); $command->where (Array (' Like ', ' title ', '% '. $keyword. '      %'));    Andwhere () function Andwhere ($conditions, $params =array ()) Orwhere () function Orwhere ($conditions, $params =array ()) Order () function order ($columns) See Example://ORDER BY ' name ', ' ID ' desc order (' Name, id desc ')//ORDER BY ' Tbl_pro File '. ' name ', ' ID ' desc order (Array (' Tbl_profile.name ', ' ID DESC ')) limit () and offset () function limit ($limit, $offs et=null) function offset ($offset) Note that some DBMS does not support limit and offset, but our Query Builder will rewrite the entire SQL statement to simulate the limit and of    Fset. The function of the.  See Example://Limit limit (Ten)//Limit offset limit (ten)//offset (+) join () and its variants function join ($table, $conditions, $params =array ()) function Leftjoin ($table, $conditions, $params =array ()) function ri Ghtjoin ($table, $conditions, $params =array ()) function CrossjoIn ($table) function Naturaljoin ($table) Note: Unlike other query builder methods, each call to join will be added to the previous join. See example://Join ' Tbl_profile ' on User_id=id join (' tbl_profile ', ' user_id=id ')//left JOIN ' pub '. ' Tbl_profile ' P ' on p. User_id=id and Type=1 leftjoin (' Pub.tbl_profile p ', ' P.user_id=id and Type=:type ', Array (': Type ' =>1) ') group () Fu Nction Group ($columns) See Example://GROUP BY ' name ', ' ID ' group (' name, id ')//GROUP BY ' Tbl_profile '. ' Name ', ' ID ' gro  Up (Array (' Tbl_profile.name ', ' ID ')) have () function having ($conditions, $params =array ()) See Example://Having id=1 or id=2 have (' id=1 or id=2 ')//having id=1 or id=2 have (array (' or ', ' id=1 ', ' id=2 ')) union () function Union  ($sql) See Example://Union (SELECT * FROM Tbl_profile) union (' SELECT * from Tbl_profile ')/***** executing Queries  Execute Query ****/by invoking the Query builder method above, we can use the DAO method to execute the query. For example: $users = Yii::app ()->db->createcommand ()->select (' * ')->from (' Tbl_user')->queryall ();    /**** Recovery Sqls ****/cdbcommand::gettext ().    $sql = Yii::app ()->db->createcommand ()->select (' * ')->from (' Tbl_user ')->text;    If the binding has parameters, we can also get the parameters by Cdbcommand::p Arams property.   Other syntax Building Queries sometimes building queries through a method chain is not the best choice.  For example, the following two expressions are equivalent, assuming $command represents a Cdbcommand object: $command->select (Array (' ID ', ' username '));    $command->select = array (' ID ', ' username ');     The further Cdbconnection::createcommand () method can take an array as an argument. as follows: $row = Yii::app ()->db->createcommand (Array (' select ' = = ' Array (' ID ', ' username '), ' from ' = = ' t        Bl_user ', ' where ' = ' id=:id ', ' params ' = = Array (': Id ' =>1),))->queryrow ();  Building multiple Queries Building multiple queries a Cdbcommand instance can be used multiple times to build several queries.    Before you build a new query, use the Reset () method to clear the previous settings.  $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 (); /****** 3. Building data manipulation Queries translated by PHP siege Http://blog.csdn.net/phpgcs *****/operation refers to a DB table Insertin    g, updating and deleting data. Insert (): Inserts a row into a table update (): Updates the data in a table delete (): Deletes the data from a table in SERT () function Insert ($table, $columns) See Example://Build and execute the following SQL://INSERT INTO ' tbl_user ' (' Name ', ' email ') VALUES (: Name,: email) $command->insert (' Tbl_user ', Array (' name ' = ' Tester ', ' email ' + =      ' tester@example.com ',)); Update () function update ($table, $columns, $conditions = ", $params =array ()) See Example://Build and execute the FOLLOWI NG SQL://UPDATE ' Tbl_user ' SET ' name ' =:name WHERE id=:id $command->update (' Tbl_user ', Array (' name ' = = ' Teste      R ',), ' Id=:id ', Array (': Id ' =>1)); Delete () function Delete ($table, $conditions = ", $params =array()) See Example://Build and execute the following SQL://DELETE from ' Tbl_user ' WHERE id=:id $command->delete (' TBL      _user ', ' Id=:id ', Array (': Id ' =>1)); /******* 4. Building Schema manipulation Queries translated by PHP siege Http://blog.csdn.net/phpgcs ******/In addition to regular search queries and operational queries, there are a number of      Methods to build and execute SQL queries that can manipulate the database structure. CreateTable (): Creates a table renametable (): Renames a Table droptable (): Drops a table truncatetable (): truncates a T Able AddColumn (): Adds a table column renamecolumn (): Renames a table column Altercolumn (): Alters a table column ADDF Oreignkey (): Adds a foreign key (available since 1.1.6) Dropforeignkey (): Drops a foreign key (available since 1.1.6) Dr Opcolumn (): Drops a table column CreateIndex (): Creates an index Dropindex (): Drops an index Abstract Data Types pumping      As data types are essentially data types built to be compatible with different DBMS, a common interface.  PK:A generic primary key type, would be is converted into int (one) not NULL auto_increment primary key for MySQL; String:strinG type, would be converted to varchar (255) for MySQL;  Text:text type (long string), would be converted to text for MySQL;  Integer:integer type, 'll is converted into int (one) for MySQL;  Float:floating number type, 'll is converted into the float for MySQL;  Decimal:decimal number type, would be converted to decimal for MySQL;  Datetime:datetime type, would be converted to datetime for MySQL;  Timestamp:timestamp type, would be converted to timestamp for MySQL;  Time:time type, would be converted to time for MySQL;  Date:date type, would be converted to date for MySQL;  Binary:binary data type, 'll be converted to blob for MySQL;  Boolean:boolean type, would be converted to tinyint (1) for MySQL; Money:money/currency type, would be converted to decimal (19,4) for MySQL.      This type has been available since version 1.1.8. CreateTable () function createtable ($table, $columns, $options =null)//CREATE table ' Tbl_user ' (//' id ' int (11) Not NULL Auto_incremeNT PRIMARY KEY,//' username ' varchar (255) Not NULL,//"location" point//) Engine=innodb createtable (' Tbl_u Ser ', array (' id ' = ' pk ', ' username ' = ' = ' string not NULL ', ' location ' = ' point ',), ' Engine=inn ODB ') renametable () function renametable ($table, $newName)//RENAME table ' tbl_users ' to ' Tbl_user ' renametable      (' tbl_users ', ' Tbl_user ') droptable () function droptable ($table)//DROP table ' Tbl_user ' droptable (' Tbl_user ') Truncatetable () function truncatetable ($table)//TRUNCATE table ' Tbl_user ' truncatetable (' Tbl_user ') Addco Lumn () function AddColumn ($table, $column, $type)//ALTER table ' tbl_user ' ADD ' email ' varchar (255) Not NULL Addcolu Mn (' tbl_user ', ' email ', ' string not NULL ') Dropcolumn () function Dropcolumn ($table, $column)//ALTER table ' Tbl_  User ' DROP COLUMN ' location ' dropcolumn (' tbl_user ', ' Location ') renamecolumn () function Renamecolumn ($table, $name, $newName)//ALTER TABLE ' tbl_users ' change ' name ' username ' varchar (255) Not NULL renamecolumn (' Tbl_user ', ' name ', ' username ') Alterco Lumn ()//ALTER TABLE ' tbl_user ' change ' username ' username ' varchar (255) Not NULL altercolumn (' tbl_user ', ' username ' , ' string not NULL ') Addforeignkey () function Addforeignkey ($name, $table, $columns, $refTable, $refColumns, $d Elete=null, $update =null)//ALTER TABLE ' tbl_profile ' ADD CONSTRAINT ' fk_profile_user_id '//FOREIGN KEY (' user_id ') REFERENCES ' Tbl_user ' (' id ')//on DELETE CASCADE on UPDATE CASCADE addforeignkey (' fk_profile_user_id ', ' tbl_profile ', ' user_id ', ' tbl_user ', ' id ', ' CASCADE ', ' CASCADE ') Dropforeignkey () function Dropforeignkey ($name, $table)//A Lter TABLE ' tbl_profile ' DROP FOREIGN KEY ' fk_profile_user_id ' Dropforeignkey (' fk_profile_user_id ', ' tbl_profile ') c Reateindex () function CreateIndex ($name, $table, $column, $unique =false)//CREATE INDEX ' idx_username ' on ' tbl_user ' (' Username ') CreateIndex (' Idx_username ', ' tbl_user ', ' username ') Dropindex () function Dropindex ($name, $table)//DROP INDEX ' Idx_username '   On ' Tbl_user ' Dropindex (' idx_username ', ' Tbl_user ')

http://www.bkjia.com/PHPjc/532694.html www.bkjia.com true http://www.bkjia.com/PHPjc/532694.html techarticle /**** Query Builder translated by PHP siege Http://blog.csdn.net/phpgcs Preparing Query Builder prepare for Query Builder Building Data retrieval Queries Build a lookup query Bui ...

