Home >Backend Development >PHP Tutorial >23 commonly used MySQL query statements, 23 commonly used MySQL statements_PHP tutorial
23 commonly used MySQL query statements, 23 commonly used MySQL statements_PHP tutorial
- WBOYOriginal
- 2016-07-13 09:44:351257browse
23 commonly used MySQL query statements, 23 commonly used MySQL statements
One query for numerical 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 queries for 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#
Four query logical data
SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'
Logic Operators: and or not
Five queries for non-empty data
SELECT * FROM tb_name WHERE address <>'' order by addtime desc
Note: <> is equivalent to PHP !=
6 Use 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 used Enclosed in quotation marks, because when strings in PHP are connected to numerical data, the program will automatically convert the numerical data into a string, and then connect it with the string to be connected
7. Use variables to query characters String data
SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'
Exact matching method "%%" means it can appear in any position
Eight queries for 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 statements, and the SQL statement will be ever-changing, making the program very flexible
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 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
Thirteenth query statistical data by month
SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;
Note: SQL The following functions are provided in the language. Using these functions, you can easily query by year, month, and day
year(data): Return the value corresponding to the AD 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: SQL statement The DISTINCT in 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 predicate for combined condition query
(1)NOT BERWEEN … AND … Query for data between the starting value and the ending value can be changed to
(2) IS NOT NULL Query for non-null values
( 3)IS NULL Query for null values
(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 query in descending/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 order
Note: If you do not specify a sorting method when sorting fields, the default is ASC ascending order
Nineteen Multi-condition query on data
SELECT field name FROM tb_stu WHERE condition ORDER BY field 1 ASC field 2 DESC …
Note: Sorting query information by multiple conditions is to jointly limit the output of records. Generally, since it is not restricted by a single condition, there are some differences in the output effect.
Twenty pairs of statistical results are sorted
The function SUM ([ALL] field name) or SUM ([DISTINCT] field name) can be used to sum the fields. When the function is ALL, it is all Sum all the records in 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
SELECT * FROM tb_name ORDER BY mount DESC,price ASC
Twenty-one single column 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, the grouping statement must be written in front of the sorting statement, otherwise an error will occur
Twenty-two columns of 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 followed by a sequence that is not an aggregate function, that is, it is not a column to be grouped
Twenty-three Multi-table group 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 ;