Mysql common query statements

Query numerical data:

SELECT * FROM tb_name WHERE sum > 100;

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

2 Query string

SELECT * FROM tb_stu WHERE sname = '小刘' SELECT * FROM tb_stu WHERE sname like '刘%' SELECT * FROM tb_stu WHERE sname like '%程序员' SELECT * FROM tb_stu WHERE sname like '%PHP%'

3 Query 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 data

SELECT * FROM tb_name WHERE type = 'T' SELECT * FROM tb_name WHERE type = 'F'

Logical operators: and or not

Five queries non-empty data

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

Note: <> Equivalent to !=

6 Using 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, and then connect them with the strings to be connected

Seven uses variables to query string data

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

The exact matching method "%%" means it can appear in any position

Eight queries 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 used in a variety of ways, making the program very flexible

n records after nine queries

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

In the eleven query statistical results The first n records

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 Termination 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 the SQL language. These functions can be used to easily implement year, month, and day Perform query
year(data): Return the value corresponding to the year and minute in the data expression
month(data): Return the value corresponding to the month and minute in the data expression
day(data) :Return the value corresponding to the date in the data expression

Fourteen query for records greater than the specified condition

SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

Fifteen query results do not display duplicate records
SELECT DISTINCT field Name FROM table name WHERE query condition
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 with *

Sixteen NOT and Predicates are used to query combined conditions
(1)NOT BERWEEN … AND … Querying data between the starting value and the ending value can be changed to ending value
(2)IS NOT NULL Query for non-null values
(3)IS NULL Query for null values
(4)NOT IN This formula depends on whether the keyword used is included in the list or excluded from the list In addition, when searching for a specified expression, the search expression can be a constant or a column name, and the column name can be a set of constants, but in more cases it is a subquery

Seventeen displays duplicate records in the data table and the number of records

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

Eighteen pairs of data in 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

Nineteen multi-condition query for 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. In general, since it is not restricted by a single condition, there are some differences in the output effect.

Twenty to sort 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 means 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 * 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

注:当分组语句group by排序语句order by同时出现在SQL语句中时，要将分组语句书写在排序语句的前面，否则会出现错误

二十二多列数据分组统计
多列数据分组统计与单列数据分组统计类似
SELECT *，SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC

SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC

注：group by语句后面一般为不是聚合函数的数列，即不是要分组的列

二十三多表分组统计

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;