PHP path--mysql query statement

Basic structure of 1,select query

Select field  from table  where filter condition  GROUP BY grouping condition  ORDER by sort condition having   filter second condition  

2, the simplest query statement

Mysql> select * from user;  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  1 | Kenan |    Kenan    | 2012-10-31 15:32:26 |  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+

3, query one or more columns

Mysql> select Username,createtime from user;  +----------+---------------------+  | username | createtime          |  +----------+---------------------+  | Kenan    | 2012-10-31 15:32:26 |  | Kenan    | 2012-10-31 15:32:26 |  +----------+---------------------+  

4, multi-table query

Mysql> select * from user; +----+----------+----------+---------------------+  | ID | Username | password |  Createtime |  +----+----------+----------+---------------------+  | 1 | Kenan | Kenan |  2012-10-31 15:32:26 |  | 3 | Kenan | Lele |  2012-10-31 15:32:26 |  +----+----------+----------+---------------------+ 2 rows in Set (0.00 sec) mysql> select * from teacher; +----+------------+---------------------+  | ID | Username |  Createtime |  +----+------------+---------------------+  | 1 | Teacher Li |  2012-11-01 21:00:58 |  | 2 | Teacher Le |  2012-11-01 21:02:07 |  +----+------------+---------------------+ 2 rows in Set (0.00 sec) mysql> select * from User,teacher; +----+----------+----------+---------------------+----+------------+---------------------+  | ID | Username | password | Createtime | ID | Username |  Createtime | +----+----------+----------+---------------------+----+------------+---------------------+  | 1 | Kenan | Kenan |  2012-10-31 15:32:26 | 1 | Teacher Li |  2012-11-01 21:00:58 |  | 3 | Kenan | Lele |  2012-10-31 15:32:26 | 1 | Teacher Li |  2012-11-01 21:00:58 |  | 1 | Kenan | Kenan |  2012-10-31 15:32:26 | 2 | Teacher Le |  2012-11-01 21:02:07 |  | 3 | Kenan | Lele |  2012-10-31 15:32:26 | 2 | Teacher Le |  2012-11-01 21:02:07 |   +----+----------+----------+---------------------+----+------------+---------------------+ 4 rows in Set (0.00 sec) In a multi-table query, if there is no restriction, the records of the two tables will be matched separately, resulting in a Cartesian product of two tables, called an equivalent connection under the full join, which is the equivalent of the conditional link in the query results above, and the record that satisfies the condition behind the where is selected. Because none of the results are empty.

　　

Mysql> SELECT * from user,teacher where user.username = Teacher.username;  

　　

5, with a simple WHERE clause condition query

Mysql> SELECT * from user where id>2;  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  

　　

6, with in query, in to indicate range

Mysql> SELECT * from user where username in (' Kenan ', ' Micheal ');  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  1 | Kenan |    Kenan    | 2012-10-31 15:32:26 |  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  

Here, username is Kenan or Micheal's record.

7,between and the query used to represent the range of a field

Mysql> SELECT * from user where ID between 2 and 3;  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  1 row in Set (0.00 sec)   mysql> select * from user where ID between 3 and 4;  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  

Here, respectively, the ID between 2 and 3 records, 3 to 4 records, from the results can be seen between and is inclusive of boundaries, is >=2 and <=3

8, is null query null value

Mysql> select * from user;  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  1 | Kenan |    Kenan    | 2012-10-31 15:32:26 |  |  3 | Kenan |    lele     | 2012-10-31 15:32:26 |  |  4 | Lele     | Lele     | NULL                |  +----+----------+----------+---------------------+  3 rows in Set (0.00 sec)   mysql> select * from user where Createtime is null;  +----+----------+----------+------------+  | id | username | password | createtime  | +----+----------+----------+------------+  |  4 | Lele     | Lele     | NULL       |  +----+----------+----------+------------+  

　　

9, connect the multi-condition query with and relations

Mysql> SELECT * FROM user where username = ' Kenan ' and password = ' Kenan ';  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  1 | Kenan |    Kenan |    2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  

　　

10, with or multi-criteria query This is or relationship, meet a condition can

Mysql> SELECT * FROM user where username = ' Kenan ' or password = ' Kenan ';  +----+----------+----------+---------------------+  | id | username | password | createtime |  +----+----------+----------+---------------------+  |  1 | Kenan |    Kenan    | 2012-10-31 15:32:26 |  |  3 | Kenan |    Lele |     2012-10-31 15:32:26 |  +----+----------+----------+---------------------+  

　　

11, aggregate function count () is used to count the total

Mysql> Select COUNT (*) from user;  +----------+  | count (*) |  +----------+  |        3 |  +----------+  

　　

12, sum with SUM (), averaging using AVG function

Mysql> select id from user;  +----+  | id |  +----+  |  1 |  |  3 |  |  4 |  +----+  3 rows in Set (0.00 sec)   mysql> select sum (ID) from user;  +---------+  | sum (ID) |  +---------+  |       8 |  +---------+  1 row in Set (0.00 sec)   mysql> select AVG (ID) from user;  +---------+  | avg (ID) |  +---------+  |  2.6667 |  +---------+  1 row in Set (0.04 sec)

13, use Max () to find the maximum value, use min to find the minimum value

Mysql> select id from user;  +----+  | id |  +----+  |  1 |  |  3 |  |  4 |  +----+  3 rows in Set (0.00 sec)   mysql> select Max (id) from user;  +---------+  | max (ID) |  +---------+  |       4 |  +---------+  1 row in Set (0.00 sec)   mysql> select min (id) from user;  +---------+  | min (id) |  +---------+  |       1 |  +---------+  1 row in Set (0.00 sec)

Original link: http://soukenan.blog.51cto.com/5130995/1047201

PHP path--mysql query statement