MySQL Common query statements

First, query numeric data:
SELECT * from Tb_name WHERE sum > 100;
Query predicate:>,=,<,<>,!=,!>,!<,=>,=<

Second, query string
SELECT * from tb_stu WHERE sname = ' Xiao Liu '
SELECT * from Tb_stu WHERE sname like ' Liu '
SELECT * from Tb_stu WHERE sname like '% Programmer '
SELECT * from Tb_stu WHERE sname like '%php% '

Third, query date type data
SELECT * from tb_stu WHERE date = ' 2011-04-08 '
Note: Different databases have differences in date type data:
(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#

Four, query logical type data
SELECT * from tb_name WHERE type = ' T '
SELECT * from tb_name WHERE type = ' F '
Logical operators: And OR not

Five, query non-empty data
SELECT * FROM Tb_name WHERE address <> ' ORDER BY addtime DESC
Note:<> is equivalent to! = in PHP

Vi. using variables to query numeric data
SELECT * FROM tb_name WHERE id = ' $_post[text] '
Note: When querying data with variables, the variables passed into SQL do not have to be enclosed in quotation marks, because when a string in PHP is connected to a numeric data, the program automatically transforms the numeric data into a string and then connects to the string to be concatenated

Querying string data with variables
SELECT * from Tb_name WHERE name like '%$_post[name]% '
The exact match method "percent" means that it can appear in any position

Eight, query the first n records
SELECT * from Tb_name LIMIT 0, $N;
The limit statement is used in conjunction with other statements, such as an order by, and uses a variety of SQL statements to make the program very flexible

Nine, after the query n records
SELECT * from Tb_stu ORDER by ID ASC LIMIT $n

X. Querying n records starting from a specified position
SELECT * from Tb_stu ORDER by ID ASC LIMIT $_post[begin], $n
Note: The ID of the data is starting from 0

Xi. the first n records in the query statistics results
SELECT *, (Yw+sx+wy) as total from Tb_score ORDER by (yw+sx+wy) DESC LIMIT 0, $num

12. Querying data for a specified time period
SELECT the field to find from table name WHERE field name between initial value and terminating value
SELECT * from Tb_stu WHERE age between 0 and 18

13. Query statistics by month
SELECT * from Tb_stu WHERE month (date) = ' $_post[date] ' ORDER by date;
Note: The following functions are available in the SQL language, which makes it easy to query by year, month, and day
Year (data): Returns the value corresponding to the A.D. in the data expression
Month (data): Returns the value of the month in the data expression
Day (data): Returns the numeric value of the date in the data expression

14. Query for records larger than the specified criteria
SELECT * from Tb_stu WHERE Age>$_post[age] ORDER by age;

The query results do not show duplicate records
SELECT DISTINCT field name from table name WHERE query condition
Note: The distinct in the SQL statement must be used in conjunction with the WHERE clause, otherwise the output information will not change and the field cannot be replaced with *

16, not and predicates to combine the conditions of the query
(1) Not Berween ... And ... Query on rows between start and end values can be changed to < start value and > End value
(2) is not null to query for non-null values
(3) Is null to query for null values
(4) In this style, depending on whether the keyword used is included in the list or excluded from the list, the search expression can be a constant or a column name, and the column name can be a set of constants, but more of a subquery

17. Display the number of duplicate records and records in the data table
SELECT Name,age,count (*), age from tb_stu WHERE age = ' + ' GROUP by date

18, the data in descending/ascending query
SELECT field name from Tb_stu WHERE condition order BY field desc Descending
SELECT field name from Tb_stu WHERE condition ORDER by field ASC Ascending
Note: When sorting a field without specifying a sort order, the default is ASC ascending

19, the data for multi-conditional query
SELECT field name from Tb_stu WHERE condition ORDER by field 1 ASC Field 2 DESC ...
Note: The query information is ordered in order to jointly limit the output of the record, in general, because it is not a single condition limit, so there are some differences in the output effect.

20, the statistical results are sorted
The function sum ([All] field name) or sum ([DISTINCT] field name) can be used to sum the fields, sum all records for all of the fields in the function, and sum the fields for all of the fields that are not repeating records for DISTINCT.
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 Row data grouping statistics
SELECT Id,name,sum (Price) as title,date from Tb_price GROUP by PID ORDER by title DESC
Note: When the grouping statement group by sort statement order by is present in the SQL statement, the grouping statement is written before the sort statement, otherwise an error occurs

22, multi-column data grouping statistics
Multi-column data grouping statistics are similar to single row data grouping statistics
SELECT *,sum (Field 1* field 2) as (new Field 1) from table name GROUP by field ORDER by new Field 1 DESC
SELECT Id,name,sum (Price*num) as Sumprice from Tb_price GROUP by PID ORDER by Sumprice DESC
Note: The group BY statement is usually followed by a series that is not an aggregate function, that is, a column that is not to be grouped

23, Multi-table grouping 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;

MySQL Common query statements