Open in app

Sign in

Write

Sign in

Mastodon

SQL table資料表的基本操作

Sean Yeh
Web Design Zone
Published in
16 min readOct 7, 2021

--

Chishang, Taitung, Taiwan, photo by Sean Yeh

上一篇文章裡我們談到SQL在資料庫層級的一些基本操作方式。其中包括了：show databases、create database <name>、use <db name>、select database()、drop database <name>也談到一部分SQL資料表層級的基本操作。其中包含：CREATE TABLE （建立資料表）、DESC （顯示資料表）、show tables （顯示資料表）、DROP TABLE （刪除資料表）等指令。

這一篇我們要繼續介紹SQL的其他操作方式。

SQL裡面的table操作

當我們建立資料庫後，下一步要思考的就是該如何使用這個建立好的資料庫？你應該會思考如何在資料庫裡面建立一個資料表？你應該會思考如何將數據資料插入這些特定的資料表中？你也可能會思考如何依照條件插入資料？如果數據已經存在資料表中，我們該如何查詢這些數據？除了第一個問題已經在上一篇說明，其他的部分將於這篇中解說。

INSERT

如何將數據資料插入資料表？在SQL裡面透過INSERT指令，可以讓我們將數據資料插入資料表裡面。我們可以一次插入一筆數據資料進入資料表，也可以一次插入多筆資料進入資料表。

INSERT一筆紀錄

如果要INSERT一筆紀錄，可以使用的語法是：

INSERT INTO table_name(col_name1, col_name2, ...) values (col_value1, col_value2, ...);

其中INSERT INTO是關鍵字，這關鍵字後面緊接著的是資料表名稱。資料表名稱後面有兩個括號，中間以values連接。

第一個括號裡面要放入這個資料表的欄位名稱，而第二個括號裡面，則是指定想要存入各個欄位的值。

其中要注意的是，第一個括號裡面的欄位順序與第二個括號裡面要插入的值之間，順序上必須相互對應，順序錯誤了會導致出入的內容發生錯誤。

透過這樣的方式，我們可以將一筆紀錄插入users資料表中：

insert into users(name,email,mobile)values ("ABC","1234","20");

INSERT多筆紀錄

除了一筆筆的將紀錄插入資料表外，也可以使用下面方式，將兩筆以上的紀錄插入資料表中。

insert into table_name(col_name1, col_name2, ...) values (col_value1, col_value2, ...),(col_value1, col_value2, ...),... ;

每一筆記錄之間以中括號包覆起來，括號與括號間加上逗點作為區分。

以上面user為例，可以透過這樣的語法將兩筆紀錄插入users資料表。

insert into users(name, email, mobile)values ("ABC","1234","20"),("DEF","5678","40");

SELECT

前面我們已經把數據資料插入了資料表。再來就會希望查看存在資料表中的數據資料。透過SELECT來查詢，可以把數據資料一條條的列出來。你可以利用SELECT指令，查詢資料表中的所有紀錄，同樣的也可以透過SELECT指令，查詢資料表中的符合特定條件的紀錄。

SELECT所有紀錄

SELECT資料表中所有紀錄的語法是：

SELECT * from table_name

語法很簡短，在from前面的「星號（*）」代表了所有的內容；from後面接的是資料表的名稱。使用這個語法，可以將table_name資料表裡面已經存在的所有數據資料一條條的列出來。

舉例來說，透過下面語法就可以把users資料表裡面的所有數據一條條的列出來：

SELECT * from users;

SELECT特定欄位紀錄

依照業務上的需要，若只需要資料表中的某欄位內容，可以使用下面語法把資料表中特定欄位的數據列出來：

SELECT col_name from table_name

一般來說，我們建立的資料表大多不會只有一個欄位。對於表中的眾多欄位，如果我們只關注其中的幾個欄位時，可以將上面的星號（ * ）替換為欄位的名稱（col_name），讓特定的一個或是數個欄位顯示出來。

# 只需要一個欄位

例如，我們只需要users資料表中的email欄位時，可以透過下面指令取得：

SELECT email from users

查詢結果只會顯示出email欄位的內容。

# 需要兩個欄位

又如，我們選擇users資料表中的name與email兩個欄位，可以在欄位名稱間用逗號區隔出來：

select name, email from users;

顯示的結果就會出現name與email兩欄位的內容，以此類推。

實際操作

說明完了INSER與SELECT大致的使用方式後，接下來可以實際操作看看。如下圖，在我們的環境中已經有一個users資料表。

如果沒有的話，可以透過下面指令建立資料表。

CREATE TABLE users(
	name  VARCHAR(20),
	email  VARCHAR(120),
	mobile VARCHAR(120)
);

使用 desc users; 指令可以看到，這個資料表的欄位共有3個，分別是name、email與mobile。

增加一筆

透過INSERT INTO指令來增加一筆紀錄。

insert into users(name, email, mobile)values ("ABC","1234","20");

使用SELECT指令查看結果，確認這筆紀錄是否已經存入資料表中。

select * from users;

如果正確插入資料的話，畫面上會顯示類似上圖的樣子。以上是一次插入一筆資料的方式。

增加兩筆

使用下面的方式一次插入兩筆資料：

insert into users(name, email, mobile)values ("ABC","1234","20"),("DEF","5678","40");

插入完畢後，透過SELECT指令查看結果，確定這兩筆資料是否已經存入資料表。

SELECT * from users;

剛才插入的資料已經在資料表裡面。不僅如此你會發現在目前的資料表裡面包含了第一次插入的那一筆資料。

所以可以看到資料表中已經累積了三筆資料。

查看一欄位內容

由於我們只需要了解 email欄位的狀況，此時可以使用SELECT指令查看特定欄位：

SELECT email from users;

執行結果只會顯示 email欄位的內容：

NULL 與 NOT NULL

到目前為止，我們提到透過INSERT指令可以將紀錄插入資料表。然而在插入資料的過程中，是否需要將每一個欄位都填滿？

在實務上，有時候我們希望使用者對於資料表中的某幾個欄位非填入資料不可；有些時候又容許使用者可以對某幾個資料欄位空白不填。

如何知道該欄位在輸入時是否必須填滿？

我們可以透過欄位表中Null的屬性來進行判斷。如果資料表裡面的Null記載的是『YES』的話，表示這個欄位可以為Null。換句話說，當我們新增資料時，該欄可以略過不填；反之則為必填欄位。

我們可以來看看先前建立的users資料表。你會發現資料表中不論是name、email或mobile等欄位，Null記載的都是『YES』，都可以保留空白。

Default

另外，我們可以從上面的users資料表看到，每個欄位的Default屬性都是『 NULL』。這表示在對users資料表插入紀錄時，空白不填的欄位會自動的插入NULL作為預設值。

我們可以試試看下面的指令：

insert into users(name, email) values("Julia","julia@abc.com");

這個指令指填入name與email兩個欄位，mobile欄位則是跳過沒賦予值。執行後使用 select * from users; 查詢，可以看到下面結果：

mobile欄位已經自動被『NULL』填滿。

設定是否必填

在建立資料表的時候，如何設定欄位是否必填？大家還記得前面建立的users資料表嗎？

CREATE TABLE users(
			name  VARCHAR(20),
			email  VARCHAR(120),
			mobile VARCHAR(120)
);

這時候請把時光倒轉至尚未建立users資料表的前一刻。其實，大家只要透過 drop table 指令就可以讓生命重新再來一遍，當然，時光倒轉後，以前曾經發生的事就不應該存在，因此資料表裡面的所有資料也會一並灰飛煙滅！

回到起初，若要建立一個users資料表，而資料表中的姓名（name）欄位需必填的話，該如何處理？

這時我們可以將『 NOT NULL 』加在姓名（name）欄位的後面，指令看起來像下面一樣：

CREATE TABLE users(
    name  VARCHAR(20)  NOT NULL,
    email  VARCHAR(120),
    mobile VARCHAR(120)
);

重新執行建立users資料表後，可以使用desc users查看結果：

很明顯的姓名（name）欄位的Null屬性已經從YES變成「NO」了。

因此我們可以使用這個方式控制資料表的各個欄位，若不希望某個欄位是空白，在建立資料表時，就可透過『 NOT NULL 』設定，該欄位成為必填欄。

DEFAULT 預設值限制

前面稍微提過了DEFAULT屬性，在使用INSERT插入資料時，若該欄位沒指定任何值的時候，會採用預設值。我們可以透過這個屬性來設定欄位的預設值。

在此仍舊以users資料表為例，這次要建一個有預設值的資料表，如下：

CREATE TABLE users(
    name  VARCHAR(20) DEFAULT "MyName",
    email  VARCHAR(120),
    mobile VARCHAR(120)
);

建立完畢後，使用desc users; 查看結果，可以發現name欄位在Default屬性的地方不像其他的欄位記載NULL而是「MyName」。這表示name欄位有了預設值「MyName」。

測試看看Default屬性的作用：使用 INSERT 指令插入一筆資料，指令如下，並且故意漏掉name欄位：

insert into users(email, mobile) values("julia@abc.com","12345678");

結果仍然可以將這筆插入。並且在name欄位的地方會自動填上了預設值：「MyName」。

Primary Key 主鍵

到目前為止，我們建立的資料表都是可以讓人隨便輸入任何紀錄，但是在大部分的實務上我們並不希望這種情況發生，為了讓資料表蒐集到的資料可以被使用或多或少會去設定限制。

在這些限制當中，除了前面提到過了NULL與NOT NULL、DEFAULT等，PRIMARY KEY（主鍵）也是其中的一種。它可以用來保證該欄位在資料表中的唯一性。欄位一旦設定了PRIMARY KEY之後，在資料表中，該欄位中內每一筆資料，都必需是獨一無二的。由於這種特性，我們時常將PRIMARY KEY用在id的欄位。

以users資料表為例說明。我們要在這次建立的users資料表中，增加一個id欄位，並在最後面加上PRIMARY KEY(id)：

CREATE TABLE users(
    id  INT,
    name  VARCHAR(20) DEFAULT "MyName",
    email  VARCHAR(120),
    mobile VARCHAR(120),
    PRIMARY KEY(id)
);

執行後產生的資料表會像這樣（透過 desc users; 查看）：

從上圖可以發現，id欄位多了一個KEY為「PRI」的屬性，並且id欄位的 NULL屬性為「NO」。這表示 id 欄位必須要有值，而且存在id欄位的值在users資料表裡面必須是唯一值、是獨一無二的值。

我們可以試著透過INSERT 指令添加一筆資料到users資料表中：

insert into users(id, name, email, mobile) values (1, "Julia", "julia@abc.com", "12345678");

基本上，執行上面的指令應該沒有什麼問題。我們可以得到下面的結果：資料表新增了一筆紀錄。

這時重點來了，如果我們重複的增加跟上面一樣的資料，會發生什麼事？

這樣子做的話，SQL就會出現錯誤訊息「Duplicate entry ‘1’ for key ‘users.PRIMARY’」。告訴我們這筆資料的id與資料表中的其他紀錄重複了。

透過這樣的方式，就可以確保資料表裡面的每一筆紀錄都是唯一的。尤其是在會員資料裡面，我們可以透過id的號碼來確保取出的資料是唯一的。

就算資料表中存在同名同性（兩筆資料的name一模一樣），也可以保證這兩筆資料不是指同一個人的兩份不同紀錄。

AUTO_INCREMENT

上面的例子裡面，我們需要一筆筆的手動賦予id值，並不是很方便，久了會搞不清楚該給的值是什麼，容易出錯。

實際上，我們可以採用下面方式增加資料。在id 欄位的方式，增加一個 AUTO_INCREMENT 屬性。

CREATE TABLE users(
    id  INT AUTO_INCREMENT,
    name  VARCHAR(20) DEFAULT "MyName",
    email  VARCHAR(120),
    mobile VARCHAR(120),
    PRIMARY KEY(id)
);

透過這個方式增加一個 AUTO_INCREMENT 的屬性後，可以看到在Extra的地方記載了「 AUTO_INCREMENT 」。這有什麼功能？

我們試著增加一筆紀錄：

insert into users(name, email, mobile) values ("Julia", "julia@abc.com", "12345678");

你會發現一個有趣的地方。在這種情形下不需要填id 欄位的值，也可以增加紀錄（如下圖）。

試著再增加第二筆紀錄 julia2。你會發現在 id欄位的地方，自動加一。填入數字2：

假設有個同名的資料（name為Julia），也沒有問題。因為兩者的id不一樣，一個是1，另一個是3。我們可以確信當取出id為1的資料，與取出id為3的資料，這兩筆不是指向同一個而是兩筆不同的資料紀錄。

採用上面的方式，可以讓id欄位自動增加id號碼（AUTO_INCREMENT），省去我們每次都要記得號碼已經排到第幾號的麻煩（這種瑣碎事就交由機器去處理吧）。這也是業界常用的方式。

UNIQUE

UNIQUE 屬性可以用來保證該欄位在資料表中的是唯一的。透過這個屬性可以限制資料表中的欄位，確保欄位中的資料不會重複。而且，一個資料表可設定多個 UNIQUE 欄位。

在users資料表裡面，其他欄位保持不變，可以試著設定mobile欄位為唯一值。也就是一個電話號碼只能註冊一次，確保每個會員的行動電話號碼都是唯一的。

CREATE TABLE users(
    id  INT AUTO_INCREMENT,
    name  VARCHAR(20) DEFAULT "MyName",
    email  VARCHAR(120),
    mobile VARCHAR(120) UNIQUE,
    PRIMARY KEY(id)
);

執行後，可以看到在Key的地方出現了「UNI」的標示。

與Primary Key 的區別

UNIQUE與Primary Key看起來似乎很類似，但Primary Key不可以接受NULL值，而UNIQUE欄位中可以接受NULL值。

換句話說，作為主鍵的Primary Key一定要賦值，而且值不可以重複。而UNIQUE欄位並不在乎是否有值，就算沒有值也沒關係，但是只要有值就必須是唯一值。易言之，我們不在乎會員是否有行動電話的號碼，但是如果會員有行動電話，號碼就不可以與其他人共用。

結語

以上是SQL中table的基本操作方式。包括如何使用INSERT將數據資料插入這些特定的資料表中？如何依照條件插入資料？對於已經存在資料表中的數據，如何使用SELECT查詢這些數據？對於SQL的各種操作方式，除了以上的說明外，還有很多，未來有機會再繼續介紹。

延伸閱讀

Web Development
Sql

--

--

Sean Yeh
Web Design Zone

Written by Sean Yeh

907 Followers

# Taipei, Internet Digital Advertising，透過寫作讓我們回想過去、理解現在並思考未來。並樂於分享，這才是最大贏家。

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams