Multi-type queries and advanced query operations in MySQL

Discrete Query
SELECT * from car where price=30 or price=40 or price=50 or price=60;
SELECT * from car where price in (30,40,50,60) takes out the data
Select * from car where price is not in (30,40,50,60) removes data

Aggregate function (System Query)
Select COUNT (*) from car
Select count (code) from car #取所有的数据条数
Select sum (price) from car #求价格总和
Select a VG (price) from car #求价格的平均值
Select Max, from car #求最大值
Select min (price) from car #求最小值

Paging query
Selec T * from car limit 0,10  #分页查询, skip a few data (0) take a few ()
Specify the number of bars to display per page: M
Current page: n]
Select * from car limit (n-1) *m,m

Go to query
SELECT DISTINCT brand from car

Group Query
Query car table, number of cars under each series
Select Brand,count (*) from car GROUP by After brand
is grouped, you can only query the column or aggregate function

Take the series designator for the series price average greater than 40
Select brand from Car GROUP by Brand have (conditional) AVG (price) >

Take the series designator of the family with a maximum fuel consumption greater than 8
Select brand from Car GROUP by Brand have Max (oil) >8

Advanced Query

(1) Connection query
SELECT t1. ' Name ', T2. Brand_Name from brand T2,car T1--Cartesian product
WHERE T2. Brand = t1. Brand
--Multi-table connection query
SELECT t1. ' Name ', T2. Brand_name,t3.prod_name

From car t1
Left JOIN brand T2
on T1. Brand = t2. Brand

Left joins Productor T3 on T2. Prod = T3. Prod
(2) The number of joint query fields must be the same
SELECT ' Name ', price from car
UNION
SELECT Brand_name,brand_memo from Brand
(3) Sub-query
SELECT * FROM car
WHERE Car.brand in
(SELECT brand from brand WHERE Prod = ' p001 ')

Multi-type queries and advanced query operations in MySQL