MYSQL database High-frequency query statement collation

a query for numeric data:
SELECT * from Tb_name WHERE sum >;
query predicate:>,=,<,<>,!=,!>,!<,=>,=<
 
Two 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% '
 
three querying 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 querying logical type data
SELECT * from tb_name WHERE type = ' T '
SELECT * from tb_name WHERE type = ' F '
logical operators: and OR not
 
five querying non-empty data
SELECT * from Tb_name WHERE address <> "ORDER BY addtime Desc
Note:<> is equivalent to! = in PHP
 
Six use 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
 
Seven 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 pre-query 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 query after N records
SELECT * from Tb_stu ORDER by ID ASC LIMIT $n
 
10 querying N records starting at a specified location
SELECT * from Tb_stu ORDER by ID ASC LIMIT $_post[begin], $n
NOTE: The ID of the data is starting from 0
 
11 Query the top N records in the 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
 
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 querying for records larger than the specified criteria
SELECT * from Tb_stu WHERE age>$_post[age] ORDER by age;
 
15 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 queries that do not combine conditions with predicates
(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 query for null values
(4) Not 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 displaying duplicate records and record bars in a datasheet
SELECT Name,age,count (*), age from tb_stu WHERE age = ' + ' GROUP by date
 
18 Descending/Ascending query of data
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 Querying the data in multiple conditions
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 Sorting the results of a statistic
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 and single row data grouping statistics are similar to  
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, which is not the column 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 database High-frequency query statement collation