MySQL Query statement

First, simple query

1. Most simple query (check all data)
Select * from table name Note: * represents all columns and does not represent all rows
Example: SELECT * FROM Test

2. Querying a specified column
Select column name, column name from table name
Example: Select Code,name from Test

3. Modify the column name of the result set as
Select column Name as ' display word ' from table name
Example: Select Code as ' Code ', name as ' name ' from test

4. Conditional query
SELECT * FROM table name WHERE Condition
Example: SELECT * from Test where code= ' n003 '

5. Multi-Criteria Query
or Or:select * from table name where condition or condition
Example: SELECT * from Test where code= ' p003 ' or nation= ' n001 '

and And:select * from table name where condition and condition
Example: SELECT * from Test where code= ' p004 ' and nation= ' n001 '

6. Scope query (the content of a column is the data of who to whom)
Example: Two ways to find a car price between 40 and 60

(1) SELECT * from car where price>=40 and price>=60
(2) SELECT * from car where price between and 60

7. Discrete query
Find information on car prices in (10, 20, 30, 40, 50, 60) in
Example: two ways of writing

(1) SELECT * from car where price=10 or price=20 or price=30 or price=40 or price=50 or price=60

(2) SELECT * from car where price in (10,20,30,40,50,60)

Information not appearing in (10, 20, 30, 40, 50, 60) not in
Example: SELECT * from car where price not in (10,20,30,40,50,60)

8. Fuzzy query (keyword query) like
%: any of n characters
_: Any one character

Query car table name contains Audi
Example: SELECT * from car where name like '% audi% '

Query car table name the second character is "horse" car
Example: SELECT * from car where name like '_ Horse% '

9. Sort query ORDER BY
Ascending ASC, can be omitted
Example: Price column ascending in car table

SELECT * FROM car order by Price ASC

Descending desc (from high to low)
Example: Fuel consumption column descending in car table

SELECT * FROM car order by oil desc

Column A ascending after column B descending
Example: A column in a car table is first ascending and then column B descending

SELECT * FROM car order by a b desc

10. To re-query distinct
Example: Find the same type of de-weight in a car table

Select distinct brand from car

11. Paging Query

One page shows that M-bar data is currently nth page
Limit (n-1) *m,m

A page of 10 data is currently the second page to skip how many bars, to take how many
Example: SELECT * from chinastates limit 10,10

12. Aggregate functions (statistical functions)
(1) Total count (*): Total number of query data
Example: Select count (*) from Chinastates

COUNT ( primary key column AreaCode)
Example: SELECT count (AreaCode) from Chinastates

(2) Sum sum (for price and column)
Example: Select sum (price) from car

(3) Averaging AVG (price averaging)
Example: Select avg (price) from car

(4) Maximum value, minimum value (Price column)
Cases:

Select Max (price) from car
Select min (price) from car

13. Packet Query GROUP BY
Find out how many cars are under each series in the car table
Example: Select Brand,count (*) from car GROUP by brand

Check the number of cars sold in the car table greater than 3 Note: Group by....having (conditions)
Example: Select brand from car group by Brand has count (*) >3

Second, advanced query

1. Connection query, extension to result set columns
SELECT * from Info,nation #形成很大的冗余 (Cartesian product)
Multiple table columns have duplicate names, write the table name, and then write the column name in the following format: table name. Column Name
Two different ways:
(1) SELECT * from Info,nation where info.nation=nation.code

Select Info.code,info.name,sex,nation.name,birthday from Info,nation where
Info.nation=nation.code

(2) SELECT * FROM info join Nation on Info.nation=nation.code

2. Union query, extension of result set row, number of columns to be the same Union
Select Code,name from Info
Union
Select Code,name from Nation

3. Sub-query
Parent Query: Outer query
Subquery: Query (query result as condition of parent query)

(1) Unrelated subqueries: subqueries do not have a relationship with parent queries at execution time (subqueries can be executed separately)
A. Querying information on all people who are ethnic Han
Parent query: SELECT * FROM info where nation= ()
Subquery: Select code from Nation where Name= ' Han '
The result after merging is:
SELECT * FROM info where nation= (select code from Nation where Name= ' Han ')

B. Search all car information for "BMW 5 Series"
SELECT * FROM car where brand= (select Brand_Code from brand where Brand_name= ' BMW 5 Series ')

(2) Related subqueries: subqueries are related to parent queries at execution time (subqueries cannot be performed separately)
A. Inquire about all vehicles with fuel consumption less than the average fuel consumption in the car table
Parent query: SELECT * from car where oil< (average fuel consumption for this series)
Subquery: Select AVG (oil) from car where brand= the series
The result after merging is:
SELECT * FROM car as a where oil< (select Avg. from car as b where B.brand=a.brand)
Note: Use as to modify table names without quoting '

MySQL Query statement