Database Basic Query method

MySQL Table Query method:

Inquire:

1. Simple query

SELECT * FROM Info--Check all data
Select Code,name from Info--check the data for the specified column
Select Code as ' code name ', name as ' from Info '--Assign alias to column

2. Conditional query

SELECT * from Info where code= ' p001 '
SELECT * from Info where sex= ' true ' and nation= ' n001 '--multi-conditional and relational
SELECT * from Info where sex= ' true ' or nation= ' n001 '--multi-condition or relationship

3. Scope Query

SELECT * from Car where price>40 and price<50
SELECT * from Car where price between and 50

4. Discrete query

SELECT * from Car where Code in (' c001 ', ' c005 ', ' c010 ', ' c015 ')
SELECT * from Car where Code not in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

5. Fuzzy query

SELECT * from Car where Name like '% BMW% '--check contains BMW's
SELECT * from Car where Name like ' BMW% '--look at the beginning of the BMW
SELECT * from Car where Name like '% BMW '--look at the end of BMW
SELECT * from Car where Name like ' BMW '-check equals BMW's

SELECT * from Car where Name like ' __e% '--check the third character is E's

% represents any number of characters

_ Represents a character

6. Sort queries

SELECT * from Car ORDER BY price ASC-in ascending order of prices
SELECT * from Car ORDER BY price desc--In descending order of prices
SELECT * from Car ORDER BY oil desc,price ASC-sorted in two fields, preceded by a secondary condition after the main condition

7. Paging Query

Select Top 5 * from Car
Select Top 5 * from car where Code not in (select Top 5 Code from car)

Current: page = 2; Display per page: row = 10;

Select Top row * from car where Code not in (select Top (page-1) *row Code from car)

8. Go to re-query

Select distinct Brand from Car

9. Group queries

Select brand from Car GROUP by Brand has count (*) >2

10. Aggregation function (statistical query)

Select COUNT (*) from Car--Query all data bars
Select COUNT (Code) from Car--Query all data bars

Select SUM (Price) from Car--sum
Select AVG (price) from Car--averaging
Select Max from Car--Maximum value
Select min (Price) from Car--minimum value

Advanced Query

1. Connection Query

SELECT * FROM Info,nation--to form a Cartesian product

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

SELECT * FROM Info join Nation in info.nation = Nation.code--join on form

2. Joint queries

Select Code,name from Info
Union
Select Code,name from Nation

3. Sub-query

An SQL statement contains two queries, one of which is the parent query (the outer query), the other is a subquery (the inner query), and the result of the subquery query is the condition of the parent query.

--Query the people for the Han nationality of all personnel information
SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

(1) Unrelated subqueries

Subqueries can be executed independently, and the subquery does not have a relationship with the parent query

--Inquiry series is all the car information of BMW 5 system
SELECT * from Car where brand = (select Brand_Code from Brand where brand_name = ' BMW 5 Series ')

(2) Related sub-query

--find cars with fuel consumption below the average fuel consumption of the series

SELECT * from Car where oil< (average fuel consumption for this series)
Select AVG (oil) from Car where Brand = (this series)

SELECT * from Car a where oil< (select Avg. from car b where B.brand = A.brand)

Database Basic Query method