ThinkPHP--SQL query statement

1. Query methods

ThinkPHP provides three basic query methods: string condition query, index array condition query and object item query. In most cases, it is recommended to use index arrays and objects as query conditions, because it is safer
1. Use strings as condition queries
//Strings as condition queries
$user = M('User');
var_dump($user->where('id=1 AND user="Crayon Shin-chan"')->select());
//Finally generated SQL statement
SELECT * FROM `think_user` WHERE ( id= 1 AND user="Crayon Shin-chan" )
PS: where query method only needs to contain conditions. Multiple conditions can be added with connectors such as AND. We will learn in detail about SQL coherent operations.
2. Use the index array as the query condition
//The index array as the condition query
$user = M('User');
$condition['id'] = 1;
$condition['user'] = 'Crayon Xiaoxin';
var_dump($user->where($condition)->select());
//Finally generated SQL statement
SELECT * FROM `think_user` WHERE ( `id` = 1 ) AND ( `user` = 'Crayon Little
New' )
PS: The default logical relationship of index array query is AND. If you want to change it to OR, you can use _logic to define the query logic.
Add the following line based on the above code:
$condition['_logic'] = 'OR'; //Change the default AND to OR
3. Use the object method to query
//Object as a condition query
$user = M ('User');
$condition = new stdClass();
$condition->id = 1;
$condition->user = 'Crayon Shin-chan';
var_dump($user->where($ condition)->select());
//Finally generated SQL statement
SELECT * FROM `think_user` WHERE ( `id` = 1 ) AND ( `user` = 'Crayon Little
New' )
PS: stdClass Class is a built-in class in PHP, which can be understood as an empty class. Here it can be understood as saving the
field of the condition as a member in the stdClass class. The '' here is to set the namespace to the root directory, otherwise it will cause this class not to be found in the current directory. Using object and array queries, the effect is the same and can be interchanged. In most cases,
ThinkPHP recommends using the array form to be more efficient.

2. Expression query
For those queries that need to achieve fuzzy judgment, such as SQL queries such as greater than, equal to, and less than, you can use the table expression query method.
Query expression format: $map['field name'] = array('expression','query condition');
Expression query table

Expression meaning

EQ is equal to (=)
NEQ is not equal to (< >)
GT greater than (>)
EGT greater than or equal to (>=)
LT less than (<)
ELT less than or equal to (<=)
[NOT]LIKE fuzzy query
[NOT] BETWEEN (not here) Interval query
[NOT] IN (not)IN query
EXP expression query, supports SQL syntax
PS: Expressions are not case-sensitive.
//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', '%小%' ); //where is like %小%
//[NOT]LIKE: fuzzy query
$map['user'] = array('notlike', '%小%'); //where is not like %小% %
//[NOT]LIKE: array method of fuzzy query
$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 and 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 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 customization in the second Just write the where statement directly for the parameters
//EXP: Customize the OR statement
$map['id'] = array('exp', '=1');
$map['user'] = array(' exp', '="Crayon Shin-chan"');
$map['_logic'] = 'OR';
//WHERE is ( (`id` =1) ) OR ( (`user` =" Crayon Shin-chan New") )

Three. Quick query
The shortcut query method is a simplified way of writing multi-field queries. Multiple fields are separated by '|' to represent OR, and '&'
are separated to represent AND.
1. Same query conditions for different fields
//Use the same query conditions
$user = M('User');
$map['user|eemail'] = 'a'; //'|' is replaced with '& 'Becomes 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-chan','_multi'=>true);
var_dump($user->where($map)->select());
PS: Setting '_multi' to true is to make the id correspond to 1 and the user to correspond to 'Crayon Shin-chan'. Otherwise, there will be a situation where id corresponds to 1 and also corresponds to 'Crayon Shin-chan'. Moreover, this setting should be placed at the end of the array.
//Support expressions combined with quick queries
$user = M('User');
$map['id&user'] = array(array('gt', 0),'Crayon Shin-chan','_multi' =>true);
var_dump($user->where($map)->select());

IV. Interval query

ThinkPHP supports interval query for a certain 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 the logical OR
$user = M('User');
$map['id'] = array(array ('gt', 1), array('lt', 4), 'OR');
var_dump($user->where($map)->select());

5. Combined query

Combined query is an expanded query based on the index array query method. It adds string query (_string), complex query (_complex), and request string query (_query). Since it uses an index array, it is repeated. will be overwritten.

//String query (_string)
$user = M('User');
$map['id'] = array('eq', 1);
$map['_string'] ='user= "Crayon Shin-chan" AND email="xiaoxin@163.com"';
var_dump($user->where($map)->select());
//Request string query (_query)
$ user = M('User');
$map['id'] = array('eq', 1);
$map['_query'] ='user=Crayon Shin-chan&email=xiaoxin@163.com&_logic= OR';
var_dump($user->where($map)->select());
PS: This method is URL method and does not require 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: Compound query can build more complex queries, here id=1 or id=3 can be implemented.

Six. Statistical query
ThinkPHP provides some methods for statistical query of data.

//Total number of data items

$user = M('User');
var_dump($user->count());
//Total number of fields, no statistics when NULL is encountered
$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'));
//Find the sum
$user = M('User');
var_dump($user->sum('id'));

Seven. Dynamic query
With the features of PHP5 language, ThinkPHP implements dynamic query.

1.getBy dynamic query

//Find the data of 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('Luffy', 'id'));

eight. SQL query
ThinkPHP supports native SQL query.

1.query reads

//Query result set, if distributed read-write separation is used, it is always executed on the read server
$user = M('User');
var_dump($user->query('SELECT * FROM think_user'));
2.execute write
//Update and write, if distributed read and write separation are used, they are always executed on the write server
$user = M('User');
var_dump($user ->execute('UPDATE think_user set user="Crayon Daxin" WHERE
id=1'));
PS: Since the subquery uses a lot of coherent operations, we will explain them in the coherent operations.

The above introduces the ThinkPHP-SQL query statement, including the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.