Mastering Thinkphp3.2.0----SQL queries

First of all, the understanding of the query: the object of the query is the database table, one or more, the limit of the query is a variety of conditions or requirements; The result of the query = Table Object + limit (condition).

Working with Data tables (CRUD) queries is one of the most complex and critical steps! ' SELECT * from where [condition] ', where is the basic constraint.

form of the condition

1. The basic form----the string form. The universal form, the query is unsafe (does not automatically add the security symbol "), is equivalent to the front of the SQL statement concatenating behind the condition.

1 $user = M (' user '); 2 // query criteria for strings 3 $where _str = "This is a string"; 4 // Show test Results 5 $result $user , where ($where _str), Select (); 6 Var_dump ($result);

Results:

It is visible that the string is written in the condition after the where, and the form can implement all the SQL queries for the WHERE keyword.

For example:

　　

1         $user = M (' user '); 2         // query criteria for strings 3         $where _str = ' id=1 '; 4         // Show test Results 5         $result $user , where ($where _str), Select (); 6         Var_dump ($result);    

Results:

　　

2. Array form conditions. or the where query, that is, where the conditions of the query is separated, such as the id=1 condition, divided into ID and 12 parts, write the array ID do the key, 1 to do the value, and write the control character.

Arrays are the more recommended form, here is the array form, the value is a string or integer or floating-point

Example:

1  $user = M (' user '); 2 // conditions in the form of arrays 3 $condition Array (1); 4 // Show test Results 5 $result $user , where ($condition), Select (); 6 Var_dump ($result);    

Results:

Example

3. Conditions in the form of objects. And the array is like, just replace the data carrier as object (Stdclass empty object, PHP program provided)

4. An expression query. The actual is to change the operator in the condition. Depending on the operator, there may be different structures, this form is relatively flexible, you can write very complex SQL query statements, not a custom.

Basic structure: Query expression format: $map [' field name '] = Array (' expression ', ' query condition '),---array form, and value is an array. The query condition can be an array, but it has no effect on an expression, such as an EQ

Like/notlike

Between/not between

 1   $user  = M (' user '  4   $map  [' id '] = array  (' between ', array  (1,10  5  6   $result  =  $user , where ( $map )->< Span style= "COLOR: #000000" > select ();  7  var_dump  (  $result ); 

In/notin-----In is In,between is between!

$user = M (' user '); // form of an expression $map Array (' Between ', ' 1,10 '); // $map [' id '] = array (' In ', Array (1,6)); Both of these forms $result $user , where ($map), Select (); Var_dump ($result);

Custom form: A bit like concatenating

There are several other forms, write more, find the right easy to do

Shortcuts | &

　　

Equivalent effect:

1 $user= M (' User ');2         //Shortcuts3         $map[' id&user '] = 1;4         //equivalent to5 //$MAP [' id '] = 1;6 //$map [' user '] = 1;7 //$map [' _logic '] = ' and ';8 //or equivalent to9 //$condition = ' id=1 and user=1 ';Ten         $result=$user, where ($map),Select (); One         Var_dump($result);

In combination with an expression:

 1   $user  = M (' user ' array  (array  (' NEQ ', 1),  Array  (' EQ ', 1), ' _multi ' =>true   4   $result  =  $user , where ( $map )-> select ();  5  var_dump  (  $result ); 

Results:

SELECT * from ' think_user ' WHERE (' id ' <> 1) and (' user ' = 1)) [runtime:0.001000s] Feel this is the best

Also, not necessarily a field, it can be 3 or more fields

Again

$map [' _string '] = '; concatenating string that can be connected to other conditions

$map [' query '] = '; URL mode, more convenient

$map [' _complex '] = '; used when overridden

1 $user= M (' User ');2         //Combination Query3         /*$map [' id '] = 1;4 $map [' _string '] = ' user=1 and email like 3 '; Continue to connect strings as conditions5         */6         /*$map [' id '] = 1;7 $map [' _query '] = ' user=1&email=2 ';//The seemingly operator can only be =8         */9         /*$where [' id '] = 1;Ten $map [' id '] = 1; One $map [' _complex '] = $where; A $map [' _logic '] = ' OR '; -         */         -         $result=$user, where ($map),Select (); the         Var_dump($result);

Statistics Query: $user->count ();

Dynamic query: GetById () Getfieldbyuser ();

SQL native

Query (); read

Excute (); write

Mastering Thinkphp3.2.0----SQL queries