1, Numerical data query:
SELECT * FROM tb_name WHERE sum> 100;
Query predicate: >, =, <, < >, =,!>, < , =, >, =
2, the query string
SELECT * FROM tb_stu WHERE sname = 'Cricode.com'
SELECT * FROM tb_stu WHERE sname like 'Uncle% Too'
SELECT * FROM tb_stu WHERE sname like '% programmer'
SELECT * FROM tb_stu WHERE sname like '% PHP%'
3, the query date data type
SELECT * FROM tb_stu WHERE date = '2011-04-08'
Note: Different databases exist differences date data type::
(1) MySQL: SELECT * from tb_name WHERE birthday = '2011-04-08'
(2) SQL Server: SELECT * from tb_name WHERE birthday = '2011-04-08'
(3) Access: SELECT * from tb_name WHERE birthday = # 2011-04-08 #
4. Data query logic
SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'
Logical operators: and or not
5, the query is not empty data
SELECT * FROM tb_name WHERE address < > "order by addtime desc
NOTE: <> = equivalent in PHP!
6, the use of variable numerical data query
SELECT * FROM tb_name WHERE id = '$ _POST [text]'
NOTE: When you use the variable query data, incoming SQL variable does not need to use quotes, because PHP string and numeric data connection, the program will automatically convert numeric data into strings, and then to be connected string connection
7, the use of query string variable data
SELECT * FROM tb_name WHERE name LIKE '% $ _ POST [name]%'
Exact match method "%%" indicates that can appear in any position
8, query the top n records
SELECT * FROM tb_name LIMIT 0, $ N;
limit the other statements, and other statements such as order by joint use of ever-changing uses SQL statements, make the program very flexible
9, n records after inquiry
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $ n
10, n query records from the specified starting position
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $ _POST [begin], $ n
Note: The data id is starting from 0
11, query statistics in the first n records
SELECT *, (yw + sx + wy) AS total FROM tb_score ORDER BY (yw + sx + wy) DESC LIMIT 0, $ num
12, query data specified period of time
Field FROM table WHERE field names BETWEEN initial value AND SELECT termination value you want to find
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
13, monthly statistical data queries
SELECT * FROM tb_stu WHERE month (date) = '$ _POST [date]' ORDER BY date;
NOTE: SQL language provides the following functions using these functions can be easily achieved by year, month, day queries
year (data): Returns the data expression of the year corresponding to the value branch
month (data): Returns the data expression value corresponding month branch
day (data): Returns the data corresponding to the date of the expression values
14, the query is greater than the record specified conditions
SELECT * FROM tb_stu WHERE age> $ _ POST [age] ORDER BY age;
15, the query results do not display duplicate records
SELECT DISTINCT field FROM table WHERE name query condition
Note: SQL statement DISTINCT must be used in conjunction with a WHERE clause, or the output of information will not change, and the field can not be used in place of *
16, NOT predicate query condition combination
(1) NOT BERWEEN ... AND ... the rows can be changed when the query data between start and end values between termination value
(2) IS NOT NULL values of non-empty query
(3) IS NULL for null query
(4) NOT IN Depending on the type of keywords are included in the list or exclusion from the list, the search for the specified expression, search expression can be a constant or a column name, column name can be a constant, but more under many cases subquery
17, the display data in the table duplicate records and the number of records
SELECT name, age, count (*), age FROM tb_stu WHERE age = '19' group by date
18, the data in descending / ascending order inquiry
SELECT field name FROM tb_stu WHERE condition ORDER BY field DESC DESC
SELECT field name FROM tb_stu WHERE condition ORDER BY field ASC ASC
NOTE: When you sort the fields do not specify a sort order, the default is ascending order ASC
19, the data multi-criteria query
SELECT field name FROM tb_stu WHERE condition ORDER BY 1 ASC field Field 2 DESC ...
Note: For more information query condition sort is common to limit the recording of output, under normal circumstances, since it is not a single condition restrictions, so there are some differences in the output.
20, the statistical results are sorted
Function SUM ([ALL] field name) or SUM ([DISTINCT] field names) can be realized on the field summation, when a function is ALL for all fields of all records that sum, if it is not all that the field was DISTINCT summation field duplicate records
Such as: SELECT name, SUM (price) AS sumprice FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC, price ASC
21, single data packet statistics
SELECT id, name, SUM (price) AS title, date FROM tb_price GROUP BY pid ORDER BY title DESC
NOTE: When the packet group by statement to sort order by statement also appear in the SQL statement to the grouping of statements written in front sorted statement, or an error
22, multi-column data packet statistics
Similar multi-column data packet data packet statistics and separate statistics
SELECT *, SUM (1 * Field Field 2) AS (new field 1) FROM table GROUP BY field ORDER BY 1 DESC new field
SELECT id, name, SUM (price * num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC
NOTE: by group behind the statement is generally the number of columns is not an aggregate function, that is, not to be grouped columns
23, multi-table packet statistics
SELECT a.name, AVG (a.price), b.name, AVG (b.price) FROM tb_demo058 AS a, tb_demo058_1 AS b WHERE a.id = b.id GROUP BY b.type;