最常用的 SQL 指令

SQL 語法是我每天 WEB 開發工作中離不開的東西, 因此把這些最常用指令摘要如下, 以備忘記時查考 (記性越來越不好囉), 其實用來用去就是底下這些個, 沒啥大學問啦 :

1. SELECT
   SELECT * FROM users WHERE gender='F' AND age < 40 ORDER BY name DESC, age ASC
   SELECT name AS 姓名, age AS 年齡 FROM users  (使用欄位別名)
   SELECT * FROM users WHERE (name LIKE 'T%' OR name LIKE '%R') AND gender='M'  (組合條件 : % 代表字串, _ 代表一個字元, # 代表 0-9 數字)
   SELECT * FROM users WHERE NOT name LIKE '%介%'  (否定條件)
   SELECT * FROM users WHERE age BETWEEN 20 AND 60  (數值範圍)
   SELECT * FROM users WHERE birthday BETWEEN #1966/1/1# AND #2012/1/23#   (日期範圍)
   SELECT * FROM users WHERE age IN (20,30,40,50)   (列舉)
   SELECT * FROM users WHERE city IN ('台北','桃園','高雄')   (列舉)
   SELECT TOP 50 * FROM users  (選取前 50 筆)
   SELECT TOP 20 PERCENT name,age FROM users  (選取前 20%)
   注意, MySQL 沒有 TOP 與 PERCENT 語法, TOP 必須改用 LIMIT  :
   SELECT * FROM users WHERE gender='F' ORDER BY age LIMIT 10
   SELECT * FROM users ORDER BY age LIMIT 3,10 (從第 3 筆開始抓 10 筆)
   而 PERCENT 必須用兩步驟模擬, 首先求出全部列數, 再乘以百分比得到筆數 :
   $SQL="SELECT COUNT(*) FROM users";
   $RS=run_sql($SQL); //執行 SQL 操作
   $percent=20; //列出前百分比紀錄
   if (is_array($RS)) {
       $total=$RS[0][0]; //總筆數
       $limit=round($percent*$total/100); //將百分比換算成筆數
       $SQL="SELECT * FROM users LIMIT ".$limit;
       $RS=run_sql($SQL);
       ...
       }
   SELECT DISTINCT occupation FROM users  (群組 : 把相同值的合為一筆)
   SELECT occupation FROM users GROUP BY occupation (群組 : 功能同上)
   SELECT COUNT(*) FROM users   (計算總筆數)
   SELECT COUNT(age) FROM users  (計算指定欄位有值之總筆數)
2. INSERT
   INSERT INTO users(name,age,gender) VALUES('武大郎',25,'M')
3. UPDATE
   UPDATE users SET country='台灣'   (更新全部記錄)
   UPDATE users SET gender='F' WHERE name='崔鶯鶯'  (更新一欄)
   UPDATE users SET age=23,gender='F' WHERE name='崔鶯鶯'  (更新多欄)
4. DELETE
   DELETE * FROM users   (刪除全部紀錄)
   DELETE * FROM users WHERE age > 30   (條件刪除)
   注意, ACCESS 才需要 *, 而 MySQL 不可用 * :
   DELETE FROM users   (刪除全部紀錄)
   DELETE FROM users WHERE age > 30   (條件刪除)