ThinkPHP -- SQL query statement, ThinkPHP -- SQL statement
I. Query Method
ThinkPHP provides three basic query methods: String condition query, Index Array condition query, and object bar query.
Component query. In most cases, we recommend that you use index arrays and objects as the query conditions, because they are more secure.
1. query using strings as conditions
// String as a condition Query
$ User = M ('user ');
Var_dump ($ user-> where ('Id = 1 AND user = "crayon" ')-> select ());
// The final generated SQL statement
SELECT * FROM 'think _ user' WHERE (id = 1 AND user = "crayon ")
PS: You only need to include the conditions in the where query method. You can add AND other connectors to multiple conditions. We will
Learn more about SQL coherent operations.
2. Use the index array as the query Condition
// Index array as a condition Query
$ User = M ('user ');
$ Condition ['id'] = 1;
$ Condition ['user'] = 'crayon Shinichi ';
Var_dump ($ user-> where ($ condition)-> select ());
// The final generated SQL statement
SELECT * FROM 'think _ user' WHERE ('id' = 1) AND ('user' = 'crayon small
New ')
PS: the default logical relationship of the Index Array query is AND. If you want to change to OR, you can use _ logic to define the query.
Query logic.
Add the following line based on the above Code:
$ Condition ['_ logic'] = 'OR'; // change AND to OR by default.
3. query using objects
// Query objects as conditions
$ User = M ('user ');
$ Condition = new \ stdClass ();
$ Condition-> id = 1;
$ Condition-> user = 'crayon shin ';
Var_dump ($ user-> where ($ condition)-> select ());
// The final generated SQL statement
SELECT * FROM 'think _ user' WHERE ('id' = 1) AND ('user' = 'crayon small
New ')
PS: The stdClass class is a PHP built-in class, which can be understood as an empty class. Here it can be understood as a condition
Fields are saved to the stdClass as members. The '\' Here sets the namespace as the root directory. Otherwise
This class cannot be found in the current directory. The query results of objects and arrays are the same and can be exchanged. In most cases,
ThinkPHP recommends using Arrays for greater efficiency.
Ii. Expression Query
You can use tables for queries that require fuzzy judgment, such as SQL queries greater than, equal to, or less
Dashboard query method.
Query expression format: $ map ['field name'] = array ('expression', 'query condition ');
Expression query table
Expression meaning
EQ equals (=)
NEQ is not equal to (<>)
GT greater than (>)
EGT greater than or equal to (> =)
LT is less than (<)
ELT less than or equal to (<=)
[NOT] LIKE fuzzy query
[NOT] BETWEEN (NOT) interval Query
[NOT] IN (NOT) IN Query
EXP expression query, supporting SQL syntax
PS: The expression is case insensitive.
// EQ: equal to (=)
$ Map ['id'] = array ('eq ', 1); // where is id = 1
// NEQ: not equal to (<>)
$ Map ['id'] = array ('neq', 1); // where is id <> 1
// GT: greater than (>)
$ Map ['id'] = array ('gt ', 1); // where is id> 1
// EGT: greater than or equal to (> =)
$ Map ['id'] = array ('egt', 1); // where is id> = 1
// LT: less than (<)
$ Map ['id'] = array ('lt ', 1); // where is id <1
// ELT: less than or equal to (<=)
$ Map ['id'] = array ('elt', 1); // where is id <= 1
// [NOT] LIKE: fuzzy query
$ Map ['user'] = array ('like', '% small %'); // where is like % small %
// [NOT] LIKE: fuzzy query
$ Map ['user'] = array ('notlike', '% small %'); // where is not like % small %
// [NOT] LIKE: fuzzy query Array Method
$ Map ['user'] = array ('like', array ('%', '% wax %'), 'AND ');
// Generated SQL
SELECT * FROM 'think _ user' WHERE ('user' LIKE '%' AND 'user'
LIKE '% wax % '))
// [NOT] BETWEEN: interval Query
$ Map ['id'] = array ('between', '1, 3 ');
// Where is 'id' BETWEEN '1' AND '2'
// Same as above
$ Map ['id'] = array ('between', array ('1', '3 '));
// [NOT] BETWEEN: interval Query
$ Map ['id'] = array ('not between', '1, 3 ');
// Where is 'id' not between '1' AND '2'
// [NOT] IN: interval Query
$ Map ['id'] = array ('in', '1, 2, 4 ');
// Where is 'id' IN ('1', '2', '4 ')
// [NOT] IN: interval Query
$ Map ['id'] = array ('not in', '1, 2, 4 ');
// Where is 'id' not in ('1', '2', '4 ')
// EXP: custom
$ Map ['id'] = array ('exp ', 'in (1, 2, 4 )');
// Where is 'id' not in ('1', '2', '4 ')
PS: Use exp to customize where statements in the second parameter.
// EXP: Add OR statement
$ Map ['id'] = array ('exp ',' = 1 ');
$ Map ['user'] = array ('exp ',' = "crayon "');
$ Map ['_ logic'] = 'OR ';
// WHERE is ('id' = 1) OR ('user' = "crayon "))
3. Quick query
Quick query is a simplified method of multi-field query. Multiple fields are separated by '|' to indicate OR, and '&' to indicate '&'
AND.
1. The same query conditions for different fields
// Use the same query Conditions
$ User = M ('user ');
$ Map ['user | eemail'] = 'a'; // '| change' to '&' AND
Var_dump ($ user-> where ($ map)-> select ());
2. Different query conditions for different fields
// Use different query Conditions
$ User = M ('user ');
$ Map ['id & user'] = array (1, 'crayon shin', '_ multi' => true );
Var_dump ($ user-> where ($ map)-> select ());
PS: Set '_ multi' to true to allow the id to correspond to 1 and the user to correspond to 'crayon shin'. Otherwise
If the id corresponds to 1 and corresponds to 'crayon shin. In addition, this setting should be placed at the end of the array.
// Supports using expressions in combination with quick query
$ User = M ('user ');
$ Map ['id & user'] = array ('gt ', 0), 'crayon update', '_ multi' => true );
Var_dump ($ user-> where ($ map)-> select ());
Iv. interval Query
ThinkPHP supports interval query for a field.
// Interval Query
$ User = M ('user ');
$ Map ['id'] = array ('gt ', 1), array ('lt', 4 ));
Var_dump ($ user-> where ($ map)-> select ());
// The third parameter setting logic OR
$ User = M ('user ');
$ Map ['id'] = array ('gt ', 1), array ('lt', 4), 'or ');
Var_dump ($ user-> where ($ map)-> select ());
5. Combined Query
Composite query is an extended query based on Index Array query.
Query (_ complex) and request string query (_ query). Because index arrays are used, duplicate queries are overwritten.
// String query (_ string)
$ User = M ('user ');
$ Map ['id'] = array ('eq ', 1 );
$ Map ['_ string'] = 'user = "crayon" AND email = "xiaoxin@163.com "';
Var_dump ($ user-> where ($ map)-> select ());
// Query the request string (_ query)
$ User = M ('user ');
$ Map ['id'] = array ('eq ', 1 );
$ Map ['_ query'] = 'user = crayon & email = xiaoxin@163.com & _ logic = OR ';
Var_dump ($ user-> where ($ map)-> select ());
PS: This method is URL without quotation marks.
// Composite query (_ complex)
$ User = M ('user ');
$ Where ['user'] = array ('like', '% ');
$ Where ['id'] = 1;
$ Where ['_ logic'] = 'OR ';
$ Map ['_ complex'] = $ where;
$ Map ['id'] = 3;
$ Map ['_ logic'] = 'OR ';
Var_dump ($ user-> where ($ map)-> select ());
PS: Composite queries can be used to construct more complex queries. Here, id = 1 or id = 3 can be used to construct and implement these queries.
6. Statistical Query
ThinkPHP provides some data statistics query methods.
// Total number of data entries
$ User = M ('user ');
Var_dump ($ user-> count ());
// The total number of fields. NULL is not counted.
$ User = M ('user ');
Var_dump ($ user-> count ('email '));
// Maximum value
$ User = M ('user ');
Var_dump ($ user-> max ('id '));
// Minimum value
$ User = M ('user ');
Var_dump ($ user-> min ('id '));
// Average Value
$ User = M ('user ');
Var_dump ($ user-> avg ('id '));
// Sum
$ User = M ('user ');
Var_dump ($ user-> sum ('id '));
7. dynamic query
With the features of PHP5, ThinkPHP enables dynamic queries.
1. getBy dynamic query
// Find the data for email = xiaoin@163.com
$ User = M ('user ');
Var_dump ($ user-> getByemail ('xiaoxin @ 163.com '));
2. getFieldBy dynamic query
// Get the corresponding id value through user
$ User = M ('user ');
Var_dump ($ user-> getFieldByUser ('lufei ', 'id '));
8. SQL query
ThinkPHP supports native SQL queries.
1. query reading
// Query result set. If distributed read/write splitting is used, it is always executed on the read server.
$ User = M ('user ');
Var_dump ($ user-> query ('select * FROM think_user '));
2.exe cute write
// Update and write. If distributed read/write splitting is used, the data is always executed on the write server.
$ User = M ('user ');
Var_dump ($ user-> execute ('Update think_user set user = "crayons" WHERE
Id = 1 '));
PS: Since subqueries use a lot of consistent operations, we will explain them in a consistent manner.
