MYSQL中的多類型查詢及進階查詢操作

標籤：進階   多表   分組查詢   取出   去掉   bsp   avg   group   limit   

　　離散查詢
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)取出資料
select * from car where price not in(30,40,50,60)去掉資料

　　彙總函式（統計查詢）
select count(*) from car
select count(code) from car #取所有的資料條數
select sum(price) from car #求價格總和
select avg(price) from car #求價格的平均值
select max(price) from car #求最大值
select min(price) from car #求最小值

　　分頁查詢
select * from car limit 0,10  #分頁查詢，跳過幾條資料(0)取幾條(10)
規定一個每頁顯示的條數：m
當前頁數：n]
select * from car limit (n-1)*m,m

　　去重查詢
select distinct brand from car

　　分組查詢
查詢汽車表中，每個系列下汽車的數量
select brand,count(*) from car group by brand
分組之後，只能查詢該列或彙總函式

取該系列價格平均值大於40的系列代號
select brand from car group by brand having（加條件） avg(price)>40

取該系列油耗最大值大於8的系列代號
select brand from car group by brand having max(oil)>8

進階查詢

（1）串連查詢
SELECT t1.`Name`,t2.Brand_Name FROM brand t2,car t1 -- 笛卡爾乘積
WHERE t2.Brand = t1.Brand
-- 多表串連查詢
SELECT t1.`Name`,t2.Brand_Name,t3.prod_name  

FROM car t1
LEFT JOIN brand t2
ON t1.Brand = t2.Brand

LEFT JOIN productor t3 ON t2.Prod = t3.Prod
（2） 聯集查詢 欄位數必須一樣
SELECT `Name`,Price FROM car
UNION
SELECT Brand_Name,Brand_Memo FROM brand
（3）子查詢
SELECT * FROM car
WHERE car.brand in
(SELECT Brand FROM brand WHERE Prod = ‘p001‘)

MYSQL中的多類型查詢及進階查詢操作