Yiru@Studio

下載匯入agriculture.sql

匯入方式

---

尋找宜蘭縣的資料

SELECT * FROM agriculture WHERE `county`='宜蘭縣'

SELECT COUNT(pk) FROM `agriculture` WHERE county='宜蘭縣';
 

Ans:49筆

關鍵字查詢-縣市中找出"台"開頭的資料共幾筆

SELECT * FROM agriculture WHERE county LIKE '台%'

SELECT COUNT(pk) FROM agriculture WHERE county LIKE '台%'

Ans:575筆

關鍵字查詢-縣市中找出有"東"的資料

SELECT * FROM agriculture WHERE county LIKE '%東%'

SELECT COUNT(pk) FROM agriculture WHERE county LIKE '%東%'

Ans:597筆

找出1月產什麼蔬菜，共幾筆?

SELECT * FROM agriculture WHERE month =1 AND type="蔬菜";

SELECT COUNT(pk) FROM agriculture WHERE month =1 AND type="蔬菜";

共140筆

SELECT * FROM agriculture WHERE month =1 AND type="蔬菜" GROUP by crop;

共17筆

找出月份為2月~4月的資料

SELECT * FROM agriculture WHERE month BETWEEN 2 AND 4

SELECT COUNT(pk) FROM agriculture WHERE month BETWEEN 2 AND 4

SELECT * FROM agriculture WHERE month BETWEEN 2 AND 4 GROUP by crop;

Ans:843筆

找出月份為1月~3月的水果

SELECT * FROM agriculture WHERE month BETWEEN 1 AND 3 AND type='水果'

SELECT COUNT(pk) FROM agriculture WHERE month BETWEEN 1 AND 3 AND type='水果'

SELECT * FROM agriculture WHERE month BETWEEN 1 AND 3 AND type='水果' GROUP by crop;

Ans:336筆

 

WHERE IN

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

找出縣市為南投縣與彰化縣的縣市

SELECT * FROM agriculture WHERE county IN ('南投縣' ,'彰化縣')

Ans:699

有哪幾個月產辣椒

SELECT * FROM agriculture WHERE crop='辣椒'

SELECT * FROM agriculture WHERE crop='辣椒' GROUP BY month

Ans:1~10份

宜蘭縣11月~1月有產什麼蔬菜水果

SELECT * FROM `agriculture` WHERE MONTH in(11,12,1) AND county = '宜蘭縣'

SELECT * FROM `agriculture` WHERE `county` ='宜蘭縣' and type ="蔬菜" and month in(10,11)

使用"蔥"關鍵字，找出什麼縣市中有產 符合 "蔥" 的

SELECT month,crop,county FROM agriculture WHERE crop LIKE '%蔥%' GROUP BY county;

 

 

Yiru@Studio - 關於我 - 意如