Query statement, SQL query statement

Query statement, SQL query statement

1. Simple Query

1. Simple query (query all data)
Select*From table name Note: * indicates all columns, not all rows
Example: select * from test

2. query specified Columns
SelectColumn nameFrom table name
Example: select code, name from test

3. Modify the column name of the result set
Select column nameAs'Display word' from Table Name
Example: select code as 'codeny', name as 'name' from test

4. Conditional Query
Select * from Table NameWhere condition
Example: select * from test where code = 'n003'

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

And: select * from table name where ConditionAndCondition
Example: select * from test where code = 'p004 'and nation = 'n001'

6. Range Query (the data between WHO and who is the content of a column)
Example: two methods: Find the car price between 40 and 60

(1) select * from car where price> = 40AndPrice> = 60
(2) select * from car where priceBetween40And60

7. discrete Query
Query Information about a vehicle price in (10, 20, 30, 40, 50, 60)
Example: Two Methods

(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 priceIn(10, 20, 30, 40, 50, 60)

Information not found in (10, 20, 30, 40, 50, 60)
Example: select * from car where priceNot in(10, 20, 30, 40, 50, 60)

8. Fuzzy search (keyword query) like
%: Any n characters
_: Any character

The car table name contains Audi
Example: select * from car where nameLike'% Audi %'

Query the car with the second character "horse" in the car table name
Example: select * from car where nameLike'_ Horse %'

9. Sort query order
Asc in ascending order, which can be omitted
For example, the price column in the car table is in ascending order.

Select * from carOrderPrice asc

Desc in descending order (from high to low)
For example, the fuel consumption column in the vehicle table is in descending order.

Select * from carOrderOil desc

First column a in ascending order and then Column B in descending order
For example, in the car Table, column a is first sorted in ascending order, and column B is then in descending order.

Select * from carOrderA, B desc

10. deduplicate query distinct
For example, search for deduplication with the same model in the car table.

SelectDistinctBrand from car

11. querying by PAGE

M data entries on one page are currently n pages
Limit (n-1) * m, m

One page shows how many records are skipped and how many records are retrieved from the second page.
Example: select * from chinastatesLimit10, 10

12. Aggregate functions (statistical functions)
(1) Total count (*): Total number of queried data items
Example: selectCount (*)From chinastates

Count (Primary Key ColumnAreacode)
Example: select count (areacode) from chinastates

(2) sum (calculate the price and column)
Example: selectSum (Price)From car

(3) avg (average price column)
Example: selectAvg (Price)From car

(4) maximum and minimum values (price column)
Example:

SelectMax (Price)From car
SelectMin (Price)From car

13. group query group
Query the number of vehicles in each series in the vehicle table.
Example: select brand, count (*) from car group by brand

Query the series with more than 3 cars sold in the car table. Note: group by... having (condition)
Example: select brand from carGroupBrandHavingCount (*)> 3

 

Ii. Advanced Query

1. Connection query, expansion of result set Columns
Select * from info, nation # form a large redundancy (Cartesian Product)
If the columns in multiple tables have duplicate names, you must write the table name and then the column name. The format is as follows:Table name. Column name
Two methods:
(1) select * from info, nation whereInfo. nation = nation. code

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

(2) select * from infoJoinNationOnInfo. nation = nation. code

2. join query: expand the number of rows in the result set. The number of columns must be the same.Union
Select code, name from info
Union
Select code, name from nation

3. subquery
Parent query: outer Query
Subquery: query in (the query result serves as the condition of the parent query)

(1) No sub-query: the sub-query is irrelevant to the parent query during execution (the Sub-query can be executed separately)
A. query information of all persons whose nationalities are Han.
Parent query: select * from info where nation = ()
Subquery: select code from nation where name = 'hangzhou'
After merging, the result is:
Select * from info where nation = (select code from nation where name = 'hangzhou ')

B. query information about all vehicles whose series names are "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 during execution (subqueries cannot be executed separately)
A. query information about all vehicles whose fuel consumption is less than the average fuel consumption of this series in the vehicle table
Parent query: select * from car where oil <(average fuel consumption of this series)
Subquery: select avg (oil) from car where brand = this series
After merging, the result is:
Select * from carAsWhere oil <(select avg (oil) from carAs BWhere B. brand = a. brand)
Note: When Using as to modify the table name, no quotation marks are required''