SQL query statement [2]

First, 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

In Home/controller/usercontroller. insert in class. PHP

// use the same query criteria $user = M (' user '); $map // ' | ' Change to ' & ' intoandvar_dump($user->where ($map)->select ());

At this point in the browser under the debugging of the result diagram shown as:

2. Different fields and different query conditions

// use different query criteria $user = M (' user '); $map [' id&user '] =  array(1, ' Crayon small new ', 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.

If you do not add ' _multi ' to the true line statement, then in debugging:

// support for using expressions in conjunction with shortcut queries $user = M (' user '); $map [' id&user '] =  arrayarraytrue); Var_dump ($user->where ($map)->select ());

where array(' GT ', 0) has been mentioned in the previous article, refers to data greater than 0, that is, the data with IDs greater than 0 are eligible

Second, interval query

Thinkphp supports interval queries for a field.

// interval Query $user = M (' user '); $map [' id '] =  arrayarray(' GT ', 1),  array(' lt ', 4)); Var_dump ($user->where ($map)->select ());

Data that conforms to the ID greater than 1 and less than 4

// The third parameter sets a logical OR $user = M (' user '); $map [' id '] =  arrayarray(' GT ', 1),  array(' lt ', 4), ' OR '); Var_dump ($user->where ($map)->select ());

This is data with an ID greater than 1 or an ID of less than 4.

Third, the combination of 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.

1. string Query (_string)

// 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 ());

At this point in the browser debugging SQL Query method is:

See the query statement in the user and email is not similar to single quotation marks "(1 on the keyboard on the left of the symbol), such a query is not safe;

2. Request string Query (_query)

// 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 ());

This way is more useful, can also be called URL Query method, only with & symbols can be, do not add single quotation marks, and SQL query method is:

You can see that this time the query statement in the user and email are added ", this way of query is also more secure.

It is also stated that

$user =  M (' user '); $map [' ID ']=array(' eq ', 1); $map [' id ']=2; Var_dump ($user->where ($map)->select ());

At this time, SQL query only id=2 data, $map[' id ']=2;  Will $map[' id ']=array(' eq ', 1); Cover out

3. Compound Query (_complex)

$user =  M (' user '); $map [' ID ']=array(' eq ', 1); $where [' id ']=2; $map [' _complex ']=$where; Var_dump ($user->where ($map)->select ());

Here a new array $where is added, the 2nd method also says $map[' id ']=2; will $map[' id ']=array(' EQ ', 1); Cover out

When you use a composite query, the corresponding SQL query statement is:

At this point the default connector is and, if you want to replace or, add a statement $map[' _logic ']= ' or ';

Iv. Statistical Enquiry

How many entries are queried

$user =  M (' user '); Var_dump ($user,Count());

At this point the SQL query statement is:

Then query how many data are in the database under a name

First of all, our database is like this.

Then we'll look at how many of them are in the email individually:

$user =  M (' user '); Var_dump ($user,count(' email '));

The result displayed after the query is string(1) "4" then we manually add a piece of data

First we set the email to be empty, as shown in

Then add a piece of data, then the email bar does not add any data, add as shown in

At this time, and then query the number of e-mail, the display is a string(1) "4" , we give the user "Li Yanhui" email as ' [email protected] ', and then query, then display as String(1) "5"

That is, the total number of fields, encountered null is not counted.

Here are some additional information about querying the database

var_dump($user,max(' id ')); Maximum value of the query ID

var_dump($user,min(' id ')); Minimum value for Query ID

var_dump($user->avg (' id ')); Average of query IDs

var_dump($user->sum (' id ')); The total value of the query ID

Five, dynamic query

With the feature of PHP5 language, thinkphp realizes dynamic query.

1.getBy Dynamic Query

$user =  M (' user '); Var_dump ($user->getbyemail (' [email protected] '));

Direct query email for [email protected] Data

You can also use var_dump($user->getbyuser (' Crayon small New ' ) to get the data for this entry

That is, the name of the data table that must be created after the Getby.

2, Getfieldby dynamic Query

// get the relative ID value by user $user = M (' user '); Var_dump ($user->getfieldbyuser (' Lu Fei ', ' id '));

At this point, change the ID to email, you can also get the value of Eamil, but the first parameter in Getfieldbyuser must be the user's corresponding name.

The same Getfieldbyuser can also be replaced with Getfieldbyid , and then the first parameter in parentheses is the value corresponding to the ID, and the next parameter is the name of the query's established data table.

Vi. 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') ");

SQL query statement [2]