23 MySQL Common query statements

23 MySQL Common query statements

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

Two query strings
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-query Date 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 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

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 use variables to query string data
SELECT * from Tb_name WHERE name like '%$_post[name]% '
The exact match method "percent" means that it can appear in any position

Eight queries before 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

Ten queries N records starting at the specified position
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 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 querying for records larger than the specified criteria
SELECT * from Tb_stu WHERE Age>$_post[age] ORDER by age;

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 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 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 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;

23 MySQL Common query statements