#按行提取
SELECT * FROM data1 WHERE city='beijing';

#按位置提取
SELECT * FROM data1 LIMIT 2,5;

#按條件提取并計算
SELECT AVG(price) FROM data1 WHERE city='beijing' AND age25;

#數據篩選AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;

#數據篩選IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');

#數據篩選OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

#數據篩選(不等于)
SELECT * FROM data1 WHERE city !='beijing';

#數據篩選like(模糊篩選)
SELECT * FROM data1 WHERE city LIKE 'bei%';

#篩選后計數 countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;

#篩選后求和 sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age30;

#篩選后求均值 averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

#單列分類匯總
SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

#多列分類匯總
SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

#查看原始數據表
SELECT * FROM data1;

#使用CASE WHEN進行數據透視
CREATE VIEW data_Items AS (
 SELECT
 data1.city,
 CASE WHEN colour = "A" THEN price END AS A,
 CASE WHEN colour = "B" THEN price END AS B,
 CASE WHEN colour = "C" THEN price END AS C,
 CASE WHEN colour = "F" THEN price END AS F
 FROM data1
);

#查看結果
SELECT * FROM data_Items;

#對字段進行求和匯總
CREATE VIEW data1_Extended_Pivot AS (
 SELECT
 city,
 SUM(A) AS A,
 SUM(B) AS B,
 SUM(C) AS C,
 SUM(F) AS F
 FROM data_Items
 GROUP BY city
);

#查看結果
SELECT * FROM data1_Extended_Pivot;

#對空值進行處理
CREATE VIEW data1_Extended_Pivot_Pretty AS (
 SELECT 
 city, 
 COALESCE(A, 0) AS A, 
 COALESCE(B, 0) AS B, 
 COALESCE(C, 0) AS C,
 COALESCE(F, 0) AS F
 FROM data1_Extended_Pivot
);

#查看數據透視結果
SELECT * FROM data1_Extended_Pivot_Pretty;