Add 23 common MySQL query statements to favorites

Add 23 common MySQL query statements to favorites
1. query numeric data:
SELECT * FROM tb_name WHERE sum> 100;
Query predicate:>, =, <, <> ,! =,!> ,! <, >=, <=
 
2. query strings
SELECT * FROM tb_stu WHERE sname = '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 %'
 
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. querying 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 match "%" indicates that the method 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 result
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 greater than the specified conditions
SELECT * FROM tb_stu WHERE age >$ _ POST [age] order by age;
 
Record duplication is not displayed in the query result
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 *.
 
Sixteen NOT statements and predicates are used to query the combination conditions.
(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. Perform descending/ascending queries on Data
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.
 
Perform multi-condition query on 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.
 
Sort the statistical results in 20 pairs
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.
 
Group statistics for more than 22 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.
 
Group statistics for 23 + tables
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;