23 commonly used MySQL query statements

Query numerical data:
SELECT * FROM tb_name WHERE sum > 100;
Query predicate:>,=,,!=,!>,!, =
Second 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 queries for date data
SELECT * FROM tb_stu WHERE date = '2011-04-08'
Note: Different databases have differences in date 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 queries logical type data
SELECT * FROM tb_name WHERE type = 'T' SELECT * FROM tb_name WHERE address '' order by addtime desc
Note: Equivalent to !=
in PHP
Six uses variables to query numerical data
SELECT * FROM tb_name WHERE id = '$_POST [text]'
Note: When using variables to query data, the variables passed into SQL do not need to be enclosed in quotation marks, because when strings in PHP are connected to numerical data, the program will automatically convert the numerical data into strings. Then connect with the string to be connected

Seven use variables to query string data
SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'
The exact matching method "%%" means that it can appear anywhere

Eight query the first n records
SELECT * FROM tb_name LIMIT 0,$N;
The limit statement is used in conjunction with other statements, such as order by and other statements, and the SQL statements will be ever-changing, making the program very flexible

Nine queries after n records
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

Ten query n records starting from the specified position
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
Note: the id of the data It starts from 0

The first n records in the eleven query statistical results
SELECT * , (yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

Twelve Query the data of the specified time period
SELECT Field to be found FROM Table name WHERE Field name BETWEEN Initial value AND End value
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

Thirteen Query statistical data by month
SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;
Note: The following functions are provided in SQL language. These functions can be used to easily query by year, month and day
year(data) : Returns the value corresponding to the year and minute in the data expression
month(data): Returns the value corresponding to the month and minute in the data expression
day(data): Returns the value corresponding to the date in the data expression

Fourteen queries for records greater than the specified condition
SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

Fifteen query results do not show duplicate records
SELECT DISTINCT field name FROM table name WHERE query conditions
Note: 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 by *

Sixteen NOT and predicates are used to query combined conditions
(1) NOT BERWEEN … AND … Right The row query for data between the start value and the end value can be changed to end value
(2)IS NOT NULL for non-null values ​​
(3)IS NULL for null values Inquiry
(4)NOT IN This formula specifies the search for an expression based 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 In many cases, it is a subquery

Seventeen displays the duplicate records and the number of records in the data table
SELECT name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date

Eighteen pairs of data Perform descending/ascending order query
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 order
Note: If you do not specify a sorting method when sorting fields, the default is ASC ascending order.

Multi-condition query on data
SELECT field name FROM tb_stu WHERE condition ORDER BY field 1 ASC field 2 DESC …
Note: Multi-condition sorting of query information is to jointly limit the output of records. Generally, due to It is not restricted by a single condition, so there are some differences in output effects.

Twenty to sort the statistical results
The function SUM([ALL] field name) or SUM([DISTINCT] field name) can realize the sum of fields. When the function is ALL, it will sum all records of this field. , if it is DISTINCT, sum the fields of all unique records in this field. For example: SELECT name, SUM(price) AS sumprice FROM tb_price GROUP BY name A single column of 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 sorting statement order by appears in the SQL statement at the same time, it must be The grouping statement is written in front of the sorting statement, otherwise an error will occur

Twenty-two multi-column data grouping statistics
Multi-column data grouping statistics are similar to single-column 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 generally not an aggregate function. 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;

The above introduces 23 commonly used MySQL query statements, including relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.