Mysql | Inquire

1, query numeric data:

SELECT * from Tb_name WHERE sum > 100;

Query predicate:>,=,<,<>,!=,!>,!<,=>,=<

2. 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. 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#

4, query the logical type of data

SELECT * from tb_name WHERE type = ' T '

SELECT * from tb_name WHERE type = ' F '

Logical operators: And OR not

5. Query non-empty data

SELECT * FROM Tb_name WHERE address <> ' ORDER BY addtime DESC

Note:<> is equivalent to! = in PHP

6. 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

7. Query 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

8, 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

9, after the query n records

SELECT * from Tb_stu ORDER by ID ASC LIMIT $n

10. Query 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 first n records in the statistic 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;

15, 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 = ' 19′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;

Reference

Keep updating ...

Mysql | Inquire