網站系統規劃 - 資料庫語法應知 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

DAY 13

網站系統規劃實務系列第 13 篇

網站系統規劃 - 資料庫語法應知

網頁設計程式設計 database 鐵人賽

tony1223

2012-10-06 23:50:21

5778 瀏覽

分享至

本篇文章作為第二週的第六篇，我們將繼續討論 SQL 語言在操作資料表的更多指令。

--------系列簡介--------

網站系統可說是現在最多學子與新人想要入門的一個領域，
這個原本屬於新興的領域，這幾年來也累積許多年的知識與 pattern ，
在有限的環境（stateless）與有限的伺服器端、瀏覽器資源下，
成為許多人進入程式的一塊入門鐵板(?)。

這個系列希望能夠就網站系統設計幾個門檻著手，
這是設定給初心者作為學習，給同好們作為回顧，

重新認識有關網站系統的每個環節。
好的，昨天的內容我們正式進入到 SQL ，並且成功在資料庫建立三張資料表。

一般而言，我們建立完資料表後，會需要進行對資料的操作。

在業界，我們有個專有術語來稱呼這些常見的操作，叫 CRUD 。

這是四個字的縮寫：Create, Read, Update and Delete （建立、讀取、修改、刪除）。

一般而言我們能對資料進行的主要就是這四種操作，
只是條件不同、資料不同的問題而已，

而這四種操作都是針對資料表進行的。

＠新增資料(Insert)

昨天我們最後有將之前的資料建入資料表中，
所以在昨天的 SQL Fiddle 中，我們使用了以下的語法，

insert into messagecategory values (1,'生活');
insert into messagecategory values (2,'工作');
insert into messagecategory values (3,'聚會');

insert into user values(1,'小王','wang@tonyq.org');
insert into user values(2,'我','tony@tonyq.org');
insert into user values(3,'小王','wang2@tonyq.org');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,1,'小王',1,'最近那個鐵人賽好像很紅捏，你有沒有去參加啊？？','2012/10/2 14:58:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,1,'我',2,'有啊，我每天晚上下班後都在寫文章，哭哭。','2012/10/2 15:00:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,1,'小王',1,'是喔～～那加油啦','2012/10/2 15:05:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,2,'我',2,'剛剛您是說後天啊啊啊','2012/10/2 15:25:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,2,'我',2,'剛剛您是說明天啊啊啊','2012/10/2 15:45:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,3,'小王',3,'晚上有沒有空？一起去吃飯？','2012/10/2 17:00:00');

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,3,'我',1,'@小王(wang2) 沒有問題啊～','2012/10/2 17:05:00');

相信大家閱讀以上新增資料的語法（ Insert ），
都還可以跟原始資料相呼應瞭解我們建入了什麼。

但應該也還有很多細部的疑問，我們接下來會仔細說明細節。

我們先從最複雜的案例開始：

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,3,'小王',3,'晚上有沒有空？一起去吃飯？','2012/10/2 17:00:00');

我們將用這個案例來說明 insert 的句型跟如何表達資料。

首先是句型：

Insert Into <資料表名稱>(<要新增資料表欄位>) Values(<要新增的資料>);

Insert Into ... Values ... 左邊這些字是固定的，可以直接背起來，

以剛剛的例子而言，轉譯過來我們能理解的語言，就是

新增到 messageboard 資料表 (messageId,MessageCategory,Author,
UserId,Content,postDate) 這些欄位，

用這些值（null,3,'小王',3,'晚上有沒有空？一起去吃飯？',
'2012/10/2 17:00:00'）

這些值跟欄位的對應是按照順序，所以我們可以重新整理一下思緒，
新增的資料是以下的序列

messageId = null,
MessageCategory = 3,
Author = '小王',
UserId = 3,
Content = '晚上有沒有空？一起去吃飯？',
postDate = '2012/10/2 17:00:00'

接下來我們將繼續說明這些資料的細節，並將幾個常見問題寫出來

1.messageId 為什麼是 null ？

我們上一篇提過的 AUTO_INCREMENT 嗎？ :)

因為 messageId 有設定為 AUTO_INCREMENT （也就是流水號），
它會幫我們取，所以我們如果對 AUTO_INCREMENT 丟入 null（無資料），

資料表會自動取得自動取得的一個流水號數字。

一般而言如果在其他非 AUTO_INCREMENT 的欄位底下，
這就是指定說，我們目前沒有這個欄位的資料，

希望這個欄位先留空或使用預設值的意思。

我學過一些程式語言，大多程式語言都可以用兩個雙引號 ""
或兩個單引號 '' 表示字串，所以這裡我也可以用雙引號表示嗎？

答案是不行，SQL 的語法在這裡有非常嚴謹的定義。
如果是文字類型的資料，必須一定要用兩個單引號 '' 作為定義。

如果資料內容已經含有單引號怎麼辦？

像是 "'Test' is very important." 這樣的字串，
我們要怎麼把它透過 SQL 放到 DB 裡面？

答案是，將內文的 ' 替換為兩個單引號。

像是這個例子

select '"''Test'' is very important."';

線上測試範例： http://sqlfiddle.com/#!2/d41d8/2580/0

更多有關資料表達的細節

在 SQL 指令裡面，只有分成有單引號跟沒單引號的資料，
就算是數字資料，你要加上單引號，SQL 也是很 Nice 不會阻止你的。XD

一般而言我們在分類上，主要朝用的還是文字跟數字兩種。

其他的資料類型基本上都可以被歸在這幾類，
像是代表 true/false 的 boolean 屬性，可以用 0 / 1 表示。

我發現有件事情怪怪的，我們明明已設定 postDate 是日期欄位
(datetime)，我輸入文字不會有問題嗎？

只要你有按照資料庫允許的方式輸入資料就不會，
像是如果你輸入 '2012/12/6 12:00' (yyyy/MM/dd HH:mm) 這種表達式，
在進入資料庫時它會自動作 datetime 的檢查、轉換後，放進資料庫。

6.那如果我亂打呢？

我們要有實驗精神，所以稍微作一些嘗試，將原本的指令改成以下

insert into messageboard(messageID,MessageCategory,
          Author,UserId ,Content,postDate )
    values (null,3,'小王',3,'晚上有沒有空？一起去吃飯？',
            'this is a wrong date format');

然後執行看看：

就會收到如上圖的錯誤訊息
Incorrect date value: 'this is a wrong date format' for column 'postDate'

所以瞭解資料庫伺服器不同欄位可以接受什麼樣的格式內容是很重要的。

7.我可以指定欄位，那如果我沒有指定所有欄位呢？

像是如果我把上面的語法中， MessageId 跟 MessageCategory ，
只剩下以下的語法

insert into messageboard(Author,UserId ,Content,postDate )
    values ('小王',3,'晚上有沒有空？一起去吃飯？',
            '2012/10/2 17:00:00');

你會發現，沒有被指定的欄位可以視為會統統給 null 值，如下圖：

MessageId 雖然是 null，但因為 AUTO_INCREMENT 的關係，而有預設值，
MessageCategory 就直接是 null 了。

線上測試範例: http://sqlfiddle.com/#!2/edfb4/1/0

8.我發現原本昨天的案例裡面也有一個很特別的表達式：

insert into messagecategory values (1,'生活');

我們不是說要指定欄位嗎？為什麼這裡沒有出現被指定的欄位？

如果沒有寫明要指定什麼欄位，
預設就是按照欄位的順序，引入 "所有欄位"。

所以可以將上面的 code 看成跟底下的程式碼一樣

insert into messagecategory(messageCategoryID,Name) values (1,'生活');

這是一種簡化的作法，對入門的讀者而言，
建議還是直接指定欄位可以避免不必要的問題。

（如欄位有所異動造成的困擾等。）

到這裡我們應該說明完新增資料，你需要知道的大多數的細節了，
接下來我們要繼續談談 CRUD 裡的其他操作。

＠ Read 讀取資料 (Select )

相信從之前的圖片裡面，讀者都對不斷出現的 select 有所印象，
接下來我們要繼續介紹這東西：

假設取得所有留言板裡的資料，我們要下的 SQL 語法：

select * from messageboard

表達式是
SELECT <欄位> from <資料表>

這指的是讀取 messageboard 資料表裡面所有欄位(*) 的每一行資料，
是最常用的（偷懶）作法之一，範例如下圖。

線上操作範例 http://sqlfiddle.com/#!2/93871/1/0

接下來我們將繼續說明其中一些細節：

那如果我想要只選取 "作者"、"內容"、"時間" 該怎麼作呢？

語法很簡單，將需要的欄位詳細列出即可，如下：

select author,content,postdate from messageboard

示例結果：

線上範例操作：http://sqlfiddle.com/#!2/93871/13/0

特別注意的是如果在資料庫檢視工具中，
select 指令欄位的順序會影響工具呈現欄位結果的順序。

如果我只想挑選其中幾筆資料呢？

可以使用 where 指令進行條件式篩選，

Select <欄位> from <資料表> where <條件式>

這裡礙於時間起見，我們直接示範幾種常見的篩選方式，
有興趣的朋友可以點入連結看執行結果，這裡就不一一貼圖了。

* 依照 ID 篩選

指定 MessageId 為 2 的資料

select * from messageboard where messageid = 2

線上測試範例：<http://sqlfiddle.com/#!2/93871/16/0 >

* 依照作者篩選

select * from messageboard where author ='小王'

線上範例操作：http://sqlfiddle.com/#!2/93871/18/0

* 多個條件同時使用時的情況

** 同時要滿足兩個不同條件（and）

以下的例子是說明，
取出同時滿足 category 是 3 且作者是小王的資料：

select * from messageboard
where messagecategory =3 and author ='小王'

線上範例操作：http://sqlfiddle.com/#!2/93871/20/0

** 只要滿足兩個條件其中之一（or）

以下的例子是說明，
取出 category 是 3 或作者是小王的資料：

select * from messageboard
where messagecategory =3 or author ='小王'

線上範例操作：http://sqlfiddle.com/#!2/93871/21/0

3.還有什麼我要注意的嗎？

還有一個是排序，對我們而言排序是重要的，
但是礙於時間的關係，我們不能多作介紹，

在此只列出指令說明如何按照一個欄位進行排序。

Select <欄位> from <資料表> <where 條件，如果有的話> Order by <欄位名稱> <ASC,DESC> (從小到大或從大到小)

在此我們以 MessageId 進行排序說明：

* 從小到大

select * from messageboard order by messageId asc

線上範例操作：http://sqlfiddle.com/#!2/93871/22/0

* 從大到小

select * from messageboard order by messageId desc

線上範例操作：http://sqlfiddle.com/#!2/93871/23/0

4.只有這樣嗎？還有沒有別的？

還有一些複雜的多表單查詢，或者是一些特別的 function ，
像是計算一個資料表裡面有多少資料的語法，

不過筆者認為今天介紹的已經有點太多了，
我們把這個比較進階的部份，留到明天再進行說明。

接下來我們繼續進入資料更新跟刪除的部份，
因為時間有點不夠，我們將快速帶過 Update 跟 Delete 的部份。

之後我們正式使用到他們時，都會再進一步的說明。

＠ Update 資料更新

更新一個資料表是透過類似條件判斷的方式，
我們會更新 所有符合的資料表 。

指令是
Update <資料表> set <欄位名稱一> = <欄位一資料> , <欄位名稱二> = <欄位二資料> <where 條件判斷>

直接舉實際例子

update user set name ='test' , email ='test@tonyq.org'

這會將[b]所有的user[b]的 name 跟 email ，
都改成 'test' , 'test@tonyq.org' 。

線上測試範例：http://sqlfiddle.com/#!2/98822/1/0

* 那如果我只想修改其中一筆呢？

還記得我們剛剛聊過 select 時的 where 語法嗎？
把它用進來就可以囉！

像是如果我只想修改使用者編號是2 的資料，
語法就是

update user set name ='test' , email ='test@tonyq.org'
where userid = 2;

線上測試範例：http://sqlfiddle.com/#!2/09022/1/0

編輯基本上算是蠻單純的，最常見的用法，大概就是透過 Id 去編輯，
最後附上一個只修改姓名的例子作為修改不同欄位設、的對照：

update user set name ='test'
where userid = 2;

線上測試範例：http://sqlfiddle.com/#!2/b9ae1/1/0

＠ Delete 刪除資料

刪除資料跟更新永法上，可以說是一模一樣，除了不需要指定欄位。

指令是
Delete from <資料表> <where 條件>

* 刪除全部資料

delete from user

線上操作範例：http://sqlfiddle.com/#!2/5bc47/1/0

* 刪除指定 userId 的資料

delete from user where userid = 2;

線上操作範例：http://sqlfiddle.com/#!2/d69de/1/0

今天的介紹還是屬於很基本的 SQL 指令介紹，
希望能夠借這篇文章幫助大家對於資料表的操作有多一分的瞭解。:)

複雜的函式(function)、多資料表查詢、鍵、索引等，
我們將留到明天再進行說明，那麼明天見囉。;)

網站系統規劃 - 從 Excel 到資料庫，談聰明的人腦到憨直的 SQL

網站系統規劃 - 再論資料管理

系列文

網站系統規劃實務共 27 篇

RSS系列文訂閱系列文

52 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1123 組

團體組數

52 組

累計文章數

23096 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js

IT邦幫忙

網站系統規劃實務系列 第 13 篇