Summary of commonly used query statements in MySQL Development

Summary of commonly used query statements in MySQL Development

1. query numeric data:

SELECT * FROM tb_name WHERE sum> 100;

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

2. query strings

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 data

SELECT * FROM tb_stu WHERE date = '2017-04-08 ′

Note: different databases have different types of date data ::

(1) MySQL: SELECT * from tb_name WHERE birthday = '2017-04-08 ′

(2) SQL Server: SELECT * from tb_name WHERE birthday = '2017-04-08 ′

(3) Access: SELECT * from tb_name WHERE birthday = #2011-04-08 #

4. query Logical Data

SELECT * FROM tb_name WHERE type = 'T'

SELECT * FROM tb_name WHERE type = 'F'

Logical OPERATOR: and or not

5. query non-empty data

SELECT * FROM tb_name WHERE address <> "order by addtime desc

Note: <> equivalent to PHP! =

6. query numeric data using variables

SELECT * FROM tb_name WHERE id = '$ _ POST [text]'

Note: When using variables to query data, the input SQL variables do not need to be enclosed in quotation marks, because when the strings in PHP are connected to the numeric data, the program will automatically convert the numeric data into a string, connect to the string to be connected.

7. query string data using variables

SELECT * FROM tb_name WHERE name LIKE '% $ _ POST [name] %'

The exact matching method "%" indicates that it can appear anywhere.

8. query the first n records

SELECT * FROM tb_name LIMIT 0, $ N;

When the limit statement is used together with other statements, such as order by statements, SQL statements are constantly changing, making the program very flexible.

9. n records after Query

SELECT * FROM tb_stu order by id asc limit $ n

10. query n records starting from the specified position

SELECT * FROM tb_stu order by id asc limit $ _ POST [begin], $ n

Note: The data id starts from 0.

11. query the first n records in the statistical results

SELECT *, (yw + sx + wy) AS total FROM tb_score order by (yw + sx + wy) desc limit 0, $ num

12. query data in a specified time period

SELECT the field to be searched FROM table name WHERE field name BETWEEN initial value AND termination 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 provided in the SQL language. You can use these functions to conveniently query data by year, month, or day.
Year (data): returns the value corresponding to the year in the data expression.
Month (data): returns the value corresponding to the month in the data expression.
Day (data): returns the value corresponding to the date in the data expression.

14. query records larger than the specified conditions

SELECT * FROM tb_stu WHERE age >$ _ POST [age] order by age;

15. duplicate records are not displayed in the query results

Select distinct field name FROM table name WHERE query Condition

Note: The DISTINCT in the SQL statement must be used together with the WHERE clause. Otherwise, the output information will not change and the field cannot be replaced *.

16. query the combination conditions of NOT and predicates

(1) not berween... AND... When querying data between the start value AND the end value, you can change the row query value to the <start value AND> end value.

(2) is not null queries non-NULL values.

(3) is null queries NULL values.

(4) not in this formula specifies the search expression based on whether the keyword is included IN the list or excluded from the list. The search expression can be a constant or column name, the column name can be a group of constants, but more often it is a subquery.

17. Display repeated records and records in the data table

SELECT name, age, count (*), age FROM tb_stu WHERE age = '19' group by date

18. query data in descending or Ascending Order

SELECT field name FROM tb_stu WHERE condition order by field DESC descending ORDER

SELECT field name FROM tb_stu WHERE condition order by field ASC ascending

Note: If you do not specify the sorting method when sorting fields, the default value is ASC ascending.

19. Multi-condition query of data

SELECT field name FROM tb_stu WHERE condition order by field 1 ASC Field 2 DESC...

Note: The multi-condition sorting of query information aims to limit the output of records. Generally, the output effect is different because it is not a single condition.

20. Sort the statistical results

The SUM function ([ALL] field name) or SUM ([DISTINCT] field name) can SUM the fields. If the function is ALL, ALL records of the field are summed, if DISTINCT is used, the sum of all fields of this field that do not record repeatedly is used.

For example, SELECT name, SUM (price) AS sumprice FROM tb_price group by name

SELECT * FROM tb_name order by mount DESC, price ASC

21. Grouping statistics for a single column

SELECT id, name, SUM (price) AS title, date FROM tb_price group by pid order by title DESC

Note: When the group statement group by sorting statement order by appears in the SQL statement at the same time, you must write the grouping statement before the sorting statement. Otherwise, an error occurs.

22. Grouping statistics of multiple columns

Grouping statistics for multiple columns is similar to grouping statistics for a single column

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 generally followed by a series of non-Aggregate functions, that is, it is not the column to be grouped.

23. Multi-Table grouping statistics

SELECT. name, AVG (. price), B. name, AVG (B. price) FROM tb_demo058 AS a, tb_demo058_1 AS B WHERE. id = B. id group by B. type;

This article permanently updates the link address: