YiiQueryBuilder (yii query constructor) Official Guide

/***** QueryBuildertranslatedbyphp attacker http://blog.csdn.net/phpgcspreparingquerybuilder#querybuilderbuildingdataretr... "/>

/*** Query Builder translated by php attacker http://blog.csdn.net/phpgcs Preparing Query Builder prepare Query Builder Building Data Retrieval Queries build Data Query Building Data Manipulation Queries build Data operation Query Building Schema Manipulation Queries build Data structure operation Query ***/Yii Query Builder provides an object-oriented method for writing SQL expressions. Allows developers to define independent units in an SQL expression using class methods and attributes. Then, different units are assembled into a valid SQL expression for DAO method calling and execution. The following is a typical example of using Yii Query Builder to create a select SQL statement: $ user = Yii: app ()-> db-> createCommand () -> select ('Id, username, profile ')-> from ('tbl _ user u')-> join ('tbl _ profile P', 'U. id = p. user_id ')-> where ('Id =: ID', array (': ID' => $ id)-> queryRow (); when you need to assemble an SQL statement programmatically or in your application based on some additional logic, it is best to use Yii Query Builder. Major advantages: 1. allow programmatic creation of a complex SQL expression 2. automatically reference tables and column names to prevent conflicts with SQL reserved keywords and special characters. 3, when possible, reference the parameter value and bind the parameter to reduce the risk of SQL injection attacks. 4. provides a certain degree of DB abstraction, which simplifies the migration to different DB platforms. It is not mandatory to use Query Builder. In fact, if your Query is very simple, it is convenient to directly write SQL statements. Note: Query Builder cannot be used to modify a custom SQL expression Query. The following code does not work: $ command = Yii: app ()-> db-> createCommand ('select * FROM tbl_user '); // the following line will NOT append WHERE clause to the above SQL $ command-> where ('Id =: ID', array (': ID' => $ id )); in other words, do not mix common SQL and Query Builder! /***** 1. Preparing Query Builder translated by php attacker http://blog.csdn.net/phpgcs * ***/Query Builder is associated with CDbCommand. The primary DB Query class is defined in DAO. To start using Query Builder, create a CDbCommand instance as follows: $ command = Yii: app ()-> db-> createCommand (); we use Yii: app () -> db obtains the DB connection and uses CDbConnection: createCommand () to create an instance. note: Here we do not want to leave a whole SQL statement in DAO to createCommand (), but leave it blank. This is because we will use the Query Builder method to construct different parts of this SQL expression later. /***** 2. Building Data Retrieval Queries translated by php attacker http://blog.csdn.net/phpgcs * ***/Data retrieval queries indicates that select SQL statements. query builder provides a series of methods to create different parts of a SELCET statement. Because all these methods return CDbCommand instances, we can call them by using method chains. 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 operator join (): appe Nds an inner join query fragment leftJoin (): appends a left outer join query fragment rightJoin (): appends a right outer join query fragment crossJoin (): appends a cross join query fragment naturalJoin (): appends a natural join query fragment group (): specifies the group by part of the query having (): specifies the HAVING part of the query order (): 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 builder methods. for simplicity, we assume the underlying database is MySQL. note that if you are using other DBMS, the table/column/value quoting shown in the examples may be different. select () function select ($ columns = '*') This method customizes the SELECT part of the query. The $ columns parameter defines the columns to be selected. it can be a column separated by commas or an array composed of column names. The column name can contain the table prefix and (or) column alias. This method will automatically reference the column name, unless a column contains the insert language (meaning that the column is provided by a DB expression). See the example: // SELECT * select () // SELECT 'id', 'username' select ('Id, username') // SELECT 'tbl _ user '. 'id', 'username' AS 'name' select ('tbl _ user. id, username as name ') // SELECT 'id', 'username' select (array ('id', 'Username') // SELECT 'id ', count (*) as num select (array ('id', 'Count (*) as num') from () function from ($ tables) see the example: // FROM 'tbl _ user' from ('TB Rochelle user ') // FROM '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 ')) // FROM 'tbl _ user', (select * from tbl_profile) p from (array ('tbl _ user', '(select * from tbl_profile) P') where () function where ($ conditions, $ params = array () where $ conditions can be either a (e.g. it can also be an array in the following format: array (opera Tor, operand1, operand2 ,...) operator has the following: 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 (1, 2, 3) ===> id in (1, 2, 3 ). the method will properly quote the column name and escape values in the range. not in: like: operand 1 ===> Column or DB expression operand 2 ===> a string or an array ('like', 'name', '% tester % ') =====> name LIKE '% tester %' array ('like', 'name', array ('% test %', '% sample % ')) =====> name LIKE '% test %' AND name LIKE '% sample % '. the method will properly quote the column name and escape values in the range. not like: or not like: See the example: // WHERE id = 1 or id = 2 where ('Id = 1 or id = 2 ') // WHERE id =: I D1 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' not in (1, 2) where (array ('not in', 'id', array (1, 2 ))) // WHERE 'name' LIKE '% Qiang %' where (array (' Like ', 'name',' % Qiang % ')) // WHERE 'name' LIKE '% Qiang' AND 'name' LIKE '% Xue' where (array ('like', 'name', array ('% Qiang ', '% Xue') // WHERE 'name' LIKE '% Qiang' OR 'name' LIKE '% Xue' where (array ('or like', 'name ', array ('% Qiang', '% Xue') // WHERE 'name' not like '% Qiang %' where (array ('not like', 'name ', '% Qiang %') // WHERE 'name' not like '% Qiang %' OR 'name' not like '% Xue %' where (array ('or Not like ', 'name', array (' % Qiang % ',' % Xue % ') when there is like, we need to determine % and _. if the input is from a 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 examples: // order by 'name', 'id' DESC order ('name, id desc ') // order by 'tbl _ profile '. 'name', 'id' DESC order (array ('tbl _ profile. name', 'id desc') limit () and offset () function limit ($ limit, $ offset = null) function offset ($ offset) note, some DBMS do not support LIMIT and OFFSET, but our Query Builder will rewrite the entire SQL statement to simulate limit and offset.. Example: // LIMIT 10 limit (10) // LIMIT 10 OFFSET 20 limit (10, 20) // OFFSET 20 offset (20) join () and its variants function join ($ table, $ conditions, $ params = array () function leftJoin ($ table, $ conditions, $ params = array ()) function rightJoin ($ 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. 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 () function group ($ columns) see the example: // group by 'name', 'id' group ('name, ID') // group by 'tbl _ profile '. 'name', 'id' group (array ('tbl _ profile. name', 'id') having () function havi Ng ($ conditions, $ params = array () example: // HAVING id = 1 or id = 2 having ('Id = 1 or id = 2 ') // HAVING id = 1 OR id = 2 having (array ('or', 'id = 1', 'id = 2') union () function union ($ SQL) example: // UNION (select * from tbl_profile) union ('select * from tbl_profile ') /***** Executing Queries to execute the query *****/by calling the preceding query builder method to construct the query, we can use the DAO method to execute the query. Example: $ users = Yii: app ()-> db-> createCommand ()-> select ('*')-> from ('tbl _ user ') -> queryAll ();/***** restore SQLs *****/CDbCommand: getText (). $ SQL = Yii: app ()-> db-> createCommand ()-> select ('*')-> from ('tbl _ user')-> text; if a parameter is bound, we can also obtain the parameter through CDbCommand: params property. Other syntax, Building Queries, sometimes using method chain to build a query is not the best choice. For example, the following two expressions are equivalent. assume that $ command represents a CDbCommand object: $ command-> select (array ('id', 'Username ')); $ command-> select = array ('id', 'Username'); the CDbConnection: createCommand () method can use array as a parameter. as follows: $ row = Yii: app ()-> db-> createCommand (array ('select' => array ('id', 'Username '), 'from' => 'tbl _ user', 'where' => 'Id =: ID', 'params' => array (': ID' => 1),)-> queryRow (); Building Multiple Queries BCommand instances can be used to construct several queries multiple times. Before creating a new query, use the reset () method to understand 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 attacker http://blog.csdn.net/phpgcs * ***/Data operations refer to inserting, updating, and deleting data in a DB table. Insert (): inserts a row into a table update (): updates the data in a table delete (): deletes the data from a table insert () function insert ($ table, $ columns) for 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 () example: // build and execute the following SQL: // UPDATE 'tbl _ user' SET 'name' =: name WHERE id =: id $ command-> update ('tbl _ user ', array ('name' => 'tester',), 'id =: ID', array (': ID' => 1); delete () function delete ($ table, $ conditions = '', $ params = array () 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 attacker http://blog.csdn.net/phpgcs * *****/In addition to regular search queries and Operation queries, there are also a series of methods to build and execute SQL queries that can operate on the database structure. CreateTable (): creates a table renameTable (): renames a table dropTable (): drops a table truncateTable (): truncates a table addColumn (): adds a table column renameColumn (): renames a table column alterColumn (): alters a table column addForeignKey (): adds a foreign key (available since 1.1.6) dropForeignKey (): drops a foreign key (available since 1.1.6) dropColumn (): drops a table column createInde X (): creates an index dropIndex (): drops an index Abstract Data Types are essentially Data Types built to be compatible with different DBMS, a common interface. Pk: a generic primary key type, will be converted into int (11) not null AUTO_INCREMENT primary key for MySQL; string: string type, will be converted into varchar (255) for MySQL; text: text type (long string), will be converted into text for MySQL; integer: integer type, will be converted into int (11) for MySQL; float: floating number type, will be converted into float for MySQL; decimal: decimal number type, will be converted into decimal for MySQL; datetime: datetime type, will be converted into datetime for MySQL; timestamp: timestamp type, will be converted into timestamp for MySQL; time: time type, will be converted into time for MySQL; date: date type, will be converted into date for MySQL; binary: binary data type, will be converted into blob for MySQL; boolean: boolean type, will be converted into tinyint (1) for MySQL; money: money/currency type, will be converted into 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 _ user ', array ('id' => 'PK', 'username' => 'string NOT Null', 'location' => 'point',), 'engine = innodb ') 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') addColumn () function addColumn ($ table, $ column, $ type) // alter table 'tbl _ user' ADD 'email 'varchar (255) not null addColumn ('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') alterColumn () // alter table 'tbl _ user' CHANGE 'username' varchar (255) not null alterColumn ('tbl _ user', 'username', 'string NOT Null ') addForeignKey () function addForeignKey ($ name, $ table, $ columns, $ refTable, $ refColumns, $ delete = 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) // alter table 'tbl _ profile 'drop foreign key' fk _ profile_user_id 'dropforeignkey ('fk _ profile_user_id', 'tbl _ profile ') createIndex () 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 ')