A Query method
thinkphp provides three basic query methods: String conditional query, indexed array condition query, and object bar
Query. In most cases, it is recommended to use indexed arrays and object methods as query criteria because it is more secure
1. Using a string as a conditional query
string as a conditional query
$user = M (' user ');
Var_dump ($user->where (' id=1 and user= ' crayon small New ')->select ());
The resulting SQL statement
SELECT * from ' Think_user ' WHERE (id=1 and user= "crayon Small New")
Ps:where Query method as long as the inclusion of conditions, multiple conditions plus and and other connectors can be. We'll
Learn more in SQL coherent operations.
2. Using indexed arrays as query criteria
Indexed arrays as conditional queries
$user = M (' user ');
$condition [' id '] = 1;
$condition [' user '] = ' crayon small new ';
Var_dump ($user->where ($condition)->select ());
The resulting SQL statement
SELECT * from ' think_user ' WHERE (' id ' = 1) and (' user ' = ' crayon Small
New ')
PS: The default logical relationship for indexed array queries is and, if you want to change to or, you can use the _logic definition to check
To consult the logic.
Add the following line based on the code above:
$condition [' _logic '] = ' OR '; Change default and to or
3. Use object mode to query
Object as a conditional query
$user = M (' user ');
$condition = new \stdclass ();
$condition->id = 1;
$condition->user = ' crayon small new ';
Var_dump ($user->where ($condition)->select ());
The resulting SQL statement
SELECT * from ' think_user ' WHERE (' id ' = 1) and (' user ' = ' crayon Small
New ')
The Ps:stdclass class is a PHP built-in class that can be understood as an empty class, which can be understood here as a conditional
The field is saved as a member in the Stdclass class. The "\" Here is to set the namespace to the root directory, otherwise it will
This class cannot be found for the current directory. Using objects and array queries, the effect is the same and can be interchanged. In most cases,
Thinkphp is recommended for more efficient use of array forms.
Two An expression query
For queries that want to implement fuzzy judgments, such as greater than, equal to, or less than SQL queries, you can use the table
Up-to-the-way query.
Query expression format: $map [' field name '] = Array (' expression ', ' query condition ');
Expression query Table
Meaning of an expression
EQ Equals (=)
NEQ not equal to (<>)
GT greater than (>)
EGT greater than or equal to (>=)
LT is less than (<)
ELT less than or equal (<=)
[NOT] Like fuzzy query
[NOT] Between (not) interval query
[NOT] In (not) in query
EXP expression query, support SQL syntax
PS: The expression is case-insensitive.
EQ: Equals (=)
$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 (<=)
$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: Array mode for fuzzy queries
$map [' user '] = array (' Like ', array ('% small% ', '% wax% '), ' and ');
The generated SQL
SELECT * from ' think_user ' WHERE (' user ' like '% small ' and ' user '
Like '% wax% '))
[NOT] Between: Interval query
$map [' id '] = array (' Between ', ' 1,3 ');
Where is ' id ' between ' 1 ' and ' 2 '
Ibid equivalent
$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 ', ' 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 custom to write a where statement directly in the second parameter
EXP: Custom Add or statement
$map [' id '] = Array (' exp ', ' = 1 ');
$map [' user '] = Array (' exp ', ' = ' crayon small new ');
$map [' _logic '] = ' OR ';
WHERE is ((' id ' =1)) OR ((' user ' = "crayon Small New"))
Three Quick Query
The shortcut query method is a simplified way of querying a multi-field, using ' | ' between multiple fields. To denote or, with ' & '
Separates represents and.
1. Same query criteria for different fields
Use the same query criteria
$user = M (' user ');
$map [' user|eemail '] = ' a '; | Change to ' & ' into and
Var_dump ($user->where ($map)->select ());
2. Different fields and different query conditions
Use different query criteria
$user = M (' user ');
$map [' id&user '] = Array (1, ' Crayon small new ', ' _multi ' =>true);
Var_dump ($user->where ($map)->select ());
PS: Set ' _multi ' to true, is to let the ID corresponding to 1, let the user corresponding ' crayon small new ', otherwise
There will be an ID corresponding to 1 also corresponding to the "crayon small new" situation. Also, this setting is placed at the end of the array.
Support for using expressions in conjunction with shortcut queries
$user = M (' user ');
$map [' id&user '] = Array (array (' GT ', 0), ' crayon Little new ', ' _multi ' =>true);
Var_dump ($user->where ($map)->select ());
Four Interval query
Thinkphp supports interval queries for a field.
Interval query
$user = M (' user ');
$map [' id '] = Array (array (' GT ', 1), Array (' LT ', 4));
Var_dump ($user->where ($map)->select ());
The third parameter sets a logical OR
$user = M (' user ');
$map [' id '] = array (' GT ', 1), Array (' LT ', 4), ' OR ');
Var_dump ($user->where ($map)->select ());
Five Combination Query
Combinatorial queries are an extensibility query based on indexed array queries, with the addition of string queries (_string), complex
Query (_complex), request string Query (_query), because the index array is used, duplicate will be overwritten.
String query (_string)
$user = M (' user ');
$map [' id '] = array (' eq ', 1);
$map [' _string '] = ' user= ' crayon small new ' and email= ' [email protected] ';
Var_dump ($user->where ($map)->select ());
Request string Query (_query)
$user = M (' user ');
$map [' id '] = array (' eq ', 1);
$map [' _query '] = ' user= crayon small new &[email protected]&_logic=or ';
Var_dump ($user->where ($map)->select ());
PS: This method is URL-style and does not need to be quoted.
Compound query (_complex)
$user = M (' user ');
$where [' user '] = array (' Like ', '% small% ');
$where [' id '] = 1;
$where [' _logic '] = ' OR ';
$map [' _complex '] = $where;
$map [' id '] = 3;
$map [' _logic '] = ' OR ';
Var_dump ($user->where ($map)->select ());
PS: Compound queries can build more complex queries, where id=1 or id=3 can build implementations.
Six Statistics Query
Thinkphp provides a number of methods for data statistical queries.
Total number of data bars
$user = M (' user ');
Var_dump ($user->count ());
Total number of field bars, encountered null 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
$user = M (' user ');
Var_dump ($user->avg (' id '));
Find sum
$user = M (' user ');
Var_dump ($user->sum (' id '));
Seven Dynamic Query
With the feature of PHP5 language, thinkphp realizes dynamic query.
1.getBy Dynamic Query
Find data for [email protected]
$user = M (' user ');
Var_dump ($user->getbyemail (' [email protected] '));
2.getFieldBy Dynamic Query
Get the relative ID value by user
$user = M (' user ');
Var_dump ($user->getfieldbyuser (' Lu Fei ', ' id '));
Eight SQL Query
thinkphp supports native SQL queries.
1.query Read
Query result set, which is always performed at the read server if distributed read/write separation is used
$user = M (' user ');
Var_dump ($user->query (' SELECT * from Think_user '));
2.execute Write
Updates and writes, which are always performed at the write server if distributed read-write separation is used
$user = M (' user ');
Var_dump ($user->execute (' UPDATE think_user set user= ' Crayon Daxin ' WHERE
Id=1 '));
PS: As a result of the sub-query with a lot of consistent operation, we will explain in a coherent operation.
Thinkphp--sql Query Statements
