My collection of 23 MySQL common SQL query statements [Absolute Dry Goods]

A query of numeric data:
select*fromtb_namewheresum>100;
Query predicate: >,=,<,<>,!=,!>,!<,>=,<=

Two query strings
Select*fromtb_stuwheresname= ' Xiao Liu '
Select*fromtb_stuwheresnamelike ' Liu% '
Select*fromtb_stuwheresnamelike '% Programmer '
Select*fromtb_stuwheresnamelike '%php% '

Three query date type data
Select*fromtb_stuwheredate= ' 2011-04-08 '
Note: Different databases differ in date-type data:
(1) Mysql:select*fromtb_namewherebirthday= ' 2011-04-08 '
(2) Sqlserver:select*fromtb_namewherebirthday= ' 2011-04-08 '
(3) access:select*fromtb_namewherebirthday= #2011 -04-08#

Four-Query logical data
Select*fromtb_namewheretype= ' T '
Select*fromtb_namewheretype= ' F '
Logical operators: Andornot

Five query non-empty data
Select*fromtb_namewhereaddress<> ' Orderbyaddtimedesc
Note:<> equivalent to!= in PHP

Using variables to query numeric data
Select*fromtb_namewhereid= ' $_post[text] '
Note: When querying data with variables, the incoming SQL variables do not have to be enclosed in quotes, because when a string in PHP is connected to a numeric data, the program automatically converts the numeric data into a string and then connects to the string to be connected

Seven query string data using variables
Select*fromtb_namewherenamelike '%$_post[name]% '
A fully matched method of "percent%" means that it can appear in any location

Eight queries before N records
Select*fromtb_namelimit0, $N;
Limit statements are used in conjunction with other statements, such as an order by, and can be used in a variety of SQL statements, making the program very flexible

Nine query after n record
Select*fromtb_stuorderbyidasclimit$n

Ten query n records starting at a specified location
Select*fromtb_stuorderbyidasclimit$_post[begin], $n
Note: The ID of the data starts at 0

11 Query statistics Results of the first N records
select*, (Yw+sx+wy) Astotalfromtb_scoreorderby (Yw+sx+wy) DESCLIMIT0, $num

12 querying data for a specified time period
Select the field to find from table name where field name between initial value and terminate value
Select*fromtb_stuwhereagebetween0and18

13 Monthly Query Statistics
Select*fromtb_stuwheremonth (date) = ' $_post[date] ' orderbydate;
Note: The following functions are provided in the SQL language, which makes it easy to implement queries by year, month, and day
Year (data): Returns the numeric value corresponding to the A.D. in the data expression
Month (data): Returns the number corresponding to the month in the data expression
Day (data): Returns the numeric value of the date in the data expression

14 A record with a query greater than the specified condition
select*fromtb_stuwhereage>$_post[age]orderbyage;

XV query results do not show duplicate records
selectdistinct 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 the

16 A query that combines a condition with a predicate
(1) Notberween ... And ... Rows query can be changed to < start value and> termination value for data between start and end values
(2) Isnotnull Query for Non-null values
(3) IsNull query for null values
(4) Notin whether the keyword is included in the list or excluded from the list, the search expression can be a constant or column name, and a column name can be a set of constants, but more often a subquery

17 displaying duplicate records and record bars in a datasheet
Selectname,age,count (*), agefromtb_stuwhereage= ' Groupbydate '

18 Descending/Ascending query of data
Select field name fromtb_stuwhere conditional order BY field desc Descending
Select field name fromtb_stuwhere conditional order BY field ASC Ascending
Note: If you do not specify a sort method when sorting a field, the default is ASC ascending

19 Multiple criteria query for data
Select field name fromtb_stuwhere condition by field 1ASC field 2DESC ...
Note: The query information for multiple conditional ordering is to jointly limit the output of records, in general, because not a single condition limit, so there are some differences in the output effect.

20 Sorting the statistical results
function sum ([All] field name) or sum ([DISTINCT] field name) To sum the field, sum all records for all fields in the function as all, and sum all the fields that are not duplicates for DISTINCT
such as: Selectname,sum (price) assumpricefromtb_pricegroupbyname

Select*fromtb_nameorderbymountdesc,priceasc

21 single row data grouping statistics
Selectid,name,sum (Price) Astitle,datefromtb_pricegroupbypidorderbytitledesc
Note: When the group statement groupby the sort statement by appearing in the SQL statement, the group statement is written in front of the sort statement, or an error occurs

22 Multi-column data grouping statistics
Multi-column data grouping statistics is similar to single column data grouping statistics
Select*,sum (Field 1* field 2) as (new Field 1) from Table name GroupBy field by new field 1DESC
Selectid,name,sum (Price*num) Assumpricefromtb_pricegroupbypidorderbysumpricedesc
Note: The GroupBy statement is typically followed by a sequence that is not an aggregate function, that is, not a column to be grouped

23 Multi-table grouping statistics
Selecta.name,avg (A.price), B.name,avg (B.price) Fromtb_demo058asa,tb_demo058_1asbwherea.id=b.idgroupbyb.type;