簡單SQL語法

1.資料庫

show databases -- 顯示所有的資料庫

use databasename -- 使用某資料庫

show tables (from databasename) -- 顯示某資料庫的所有表格名稱


2.表格

describe tablename -- 顯示某表格所有欄位資料設定

show columns from tablename -- 同上


3.查詢資料

select * from tablename -- 顯示某表格的所有資料與欄位
(*表示所有的欄位)
select columnnames from tablename -- 顯示某表格的某(些)欄位
(columnname間以","連接、順序有關)

select columnnames from tablename where columnname = ' '

-- 篩選條件、數學式之間可用and、or


select zone,area,zipcode from zip where zone = '雲林縣' or zone = '桃園縣'


select columnnames from tablename group by columnname

(select distinct columnname from tablename)

-- 相同的column只顯示一筆

select columnname , count(columnname) from tablename group by columnname

函式：
count(columnname)
Avg(columnname)
min(columnname)
max(columnname)
sum(columnname)
std(columnname)
stddev(columnname)

select columnname , count(columnname) as qoo from tablename group
by columnname having qoo >= 10

select zone , min(zipcode),max(zipcode),count(zone) as foo from zip group
by zone having foo >= 10