10.17 (morning) One months 13 days (database query)

1. Scope Lookup

SELECT * FROM table name where price>40 and price<80

SELECT * from table name where price between and 80

2. Discrete query

SELECT * FROM table name where price=30 or price=50

SELECT * from table name where price in (30,40,50)

SELECT * from table name where price isn't in (30,40,50)

3. Aggregation function (statistical query)

Select COUNT (*) from table name

Select count (code) from table name # takes all data bars

Select sum(price) #求价格Total from car

Select avg(price) #求价格的average from car

Select Max#求maximum from car

Select min(price) #求minimum from car

4. Paging Query

SELECT * FROM car limit 0,10 # paged Query , skip a few data (0) take a few (10)

Specify a number of bars to display per page: M
Current page: N
SELECT * FROM car limit (n-1) *m,m

5. Go to re-query

Select distinct column name from table name # go to weight

6. Group queries

Check the number of cars under each series in the car table

Select brand,count (*) sum from car Group by brand#分组

Only the column or aggregate function can be queried after grouping

Take the series code of the series price average greater than 40

Select brand from Car group by Brand had avg (price) >40

Take the series code of the series with maximum fuel consumption greater than 8
Select brand from Car group by Brand have max (oil) >8

10.17 (morning) One months 13 days (database query)