本文介紹如何使用 CREATE DATABASE 語句創建資料庫、 CREATE TABLE 語句創建表、ALTER TABLE 語句更新表、DROP TABLE 語句刪除表。 一、表的創建 本節要點 表通過 CREATE TABLE 語句創建而成。 表和列的命名要使用有意義的文字。 指定列的數據類 ...
目錄
本文介紹如何使用 CREATE DATABASE
語句創建資料庫、 CREATE TABLE
語句創建表、ALTER TABLE
語句更新表、DROP TABLE
語句刪除表。
一、表的創建
本節要點
表通過
CREATE TABLE
語句創建而成。表和列的命名要使用有意義的文字。
指定列的數據類型(整數型、字元型和日期型等)。
可以在表中設置約束(主鍵約束和
NOT NULL
約束等)。
1.1 表的內容的創建
我們將從 SQL SELECT WHERE 語句如何指定一個或多個查詢條件 開始學習針對錶的查詢,以及數據變更等 SQL 語句。本節將會創建學習這些 SQL 語句所需的資料庫和表。
表 2 是 資料庫和 SQL 是什麼關係 舉例時使用的商品表。
表 2 商品表
商品編號 | 商品名稱 | 商品種類 | 銷售單價 | 進貨單價 | 登記日期 |
---|---|---|---|---|---|
0001 | T 恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
0002 | 打孔器 | 辦公用品 | 500 | 320 | 2009-09-11 |
0003 | 運動 T 恤 | 衣服 | 4000 | 2800 | |
0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2009-09-20 |
0005 | 高壓鍋 | 廚房用具 | 6800 | 5000 | 2009-01-15 |
0006 | 叉子 | 廚房用具 | 500 | 2009-09-20 | |
0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2008-04-28 |
0008 | 圓珠筆 | 辦公用品 | 100 | 2009-11-11 |
該表是某家小商店銷售商品的一覽表。
商品的數量不多,不過我們可以把它想象成大量數據中的一部分(畢竟這隻是為了學習 SQL 而創建的表)。
像 0003
號商品的登記日期以及 0006
號商品的進貨單價這樣的空白內容,我們可以認為是由於店主疏忽而忘記輸入了。
大家可以看到表 2 由 6 列 8 行所組成。最上面一行是數據的項目名,真正的數據是從第 2 行開始的。
備忘
接下來,我們會逐步學習創建資料庫和表所使用的 SQL 語句的書寫方式。
還沒有準備好學習環境(PostgreSQL)的讀者,請按照 如何在 Windows 10 中安裝 PostgreSQL 和連接設置 的內容進行準備。
1.2 資料庫的創建(CREATE DATABASE 語句)
前面提到,在創建表之前,一定要先創建用來存儲表的資料庫。運行 CREATE DATABASE
語句就可以在 RDBMS 上創建資料庫了。CREATE DATABASE
語句的語法如下所示 [1]。
語法 1 創建資料庫的 CREATE DATABASE 語句
CREATE DATABASE <資料庫名稱>;
這裡我們將資料庫命名為 shop
,然後執行代碼清單 1 中的 SQL 語句 [2]。
代碼清單 1 創建資料庫 shop
的 CREATE DATABASE
語句
CREATE DATABASE shop;
此外,資料庫名稱、表名以及列名都要使用半形字元(英文字母、數字、符號),具體內容隨後會進行介紹。
1.3 表的創建(CREATE TABLE 語句)
創建好資料庫之後,接下來我們使用 CREATE TABLE
語句在其中創建表。CREATE TABLE
語句的語法如下所示 [3]。
語法 2 創建表的 CREATE TABLE 語句
CREATE TABLE <表名>
(<列名1> <數據類型> <該列所需約束>,
<列名2> <數據類型> <該列所需約束>,
<列名3> <數據類型> <該列所需約束>,
<列名4> <數據類型> <該列所需約束>,
.
.
.
<該表的約束1>, <該表的約束2>,……);
該語法清楚地描述了我們要創建一個包含 <列名 1>
、<列名 2>
、……的名稱為 <表名>
的表,非常容易理解。每一列的數據類型(後述)是必須要指定的,還要為需要的列設置約束(後述)。
約束可以在定義列的時候進行設置,也可以在語句的末尾進行設置 [4]。
在資料庫中創建表 2 中的商品表(Product
表)的 CREATE TABLE
語句,如代碼清單 2 所示。
代碼清單 2 創建 Product 表的 CREATE TABLE 語句
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
備忘
創建這些表的 SQL 語句可以從 https://cdn.developerastrid.com/file/sql/602-CreateTable.zip 下載。
格式為
\CreateTable\<RDBMS 名>
文件夾下的 CreateTable<表名>.sql 文件中。例如在 PostgreSQL 中創建
Product
表所使用的 SQL 語句,就保存在本文示例代碼\CreateTable\PostgreSQL
文件夾下的 CreateTableProduct.sql 文件中。CreateTableProduct.sql 文件包含了創建
Product
表時用到的 SQL 語句(代碼清單 2),以及向Product
表中插入數據的 SQL 語句(代碼清單 6)。這樣就可以在創建表的同時向表中預先插入數據了。
1.4 命名規則
我們只能使用半形英文字母、數字、下劃線(_
)作為資料庫、表和列的名稱。例如,不能將 product_id
寫成 product-id
,因為標準 SQL 並不允許使用連字元作為列名等名稱。$
、#
、?
這樣的符號同樣不能作為名稱使用。
儘管有些 RDBMS 允許使用上述符號作為列的名稱,但這也僅限於在該 RDBMS 中使用,並不能保證在其他 RDBMS 中也能使用。
雖然大家可能會覺得限制有點太多了,但還是請遵守規則使用半形英文字母、數字和下劃線(_
)吧。
法則 9
資料庫名稱、表名和列名等可以使用以下三種字元。
半形英文字母
半形數字
下劃線(
_
)
此外,名稱必須以半形英文字母開頭。以符號開頭的名稱並不多見,但有時會碰到類似 1product
或者 2009_sales
這樣以數字開頭的名稱。
雖然可以理解,但這在標準 SQL 中是被禁止的。請大家使用 product1
或者 sales_2009
這樣符合規則的名稱。
法則 10
名稱必須以半形英文字母作為開頭。
最後還有一點,在同一個資料庫中不能創建兩個相同名稱的表,在同一個表中也不能創建兩個名稱相同的列。如果出現這樣的情況,RDBMS 會返回錯誤信息。
法則 11
名稱不能重覆。
接下來我們根據上述規則,使用代碼清單 2 中的 CREATE TABLE
語句來創建表 2 中的商品表。表名為 Product
,表中的列名如表 3 所示。
表 3 商品表和 Product 表列名的對應關係
商品表中的列名 | Product 表定義的列名 |
---|---|
商品編號 | product_id |
商品名稱 | product_name |
商品種類 | product_type |
銷售單價 | sale_price |
進貨單價 | purchase_price |
登記日期 | regist_date |
1.5 數據類型的指定
Product
表所包含的列,定義在 CREATE TABLE Product()
的括弧中。
列名右邊的 INTEGER
或者 CHAR
等關鍵字,是用來聲明該列的數據類型的,所有的列都必須指定數據類型。
數據類型表示數據的種類,包括數字型、字元型和日期型等。每一列都不能存儲與該列數據類型不符的數據。
聲明為整數型的列中不能存儲 'abc'
這樣的字元串,聲明為字元型的列中也不能存儲 1234
這樣的數字。
數據類型的種類很多,各個 RDBMS 之間也存在很大差異。根據業務需要實際創建資料庫時,一定要根據不同的 RDBMS 選用最恰當的數據類型。
在學習 SQL 的時候,使用最基本的數據類型就足夠了。下麵我們就來介紹四種基本的數據類型。
-
INTEGER
型用來指定存儲整數的列的數據類型(數字型),不能存儲小數。
-
CHAR
型CHAR
是 CHARACTER(字元)的縮寫,是用來指定存儲字元串的列的數據類型(字元型)。可以像
CHAR(10)
或者CHAR(200)
這樣,在括弧中指定該列可以存儲的字元串的長度(最大長度)。字元串超出最大長度的部分是無法輸入到該列中的。
RDBMS 不同,長度單位也不一樣,既存在使用字元個數的情況,也存在使用位元組長度 [5] 的情況。
字元串以定長字元串的形式存儲在被指定為
CHAR
型的列中。所謂定長字元串,就是當列中存儲的字元串長度達不到最大長度的時候,使用半形空格進行補足。
例如,我們向
CHAR(8)
類型的列中輸入'abc'
的時候,會以 'abc '
(abc 後面有 5 個半形空格)的形式保存起來。另外,雖然之前我們說過 SQL 不區分英文字母的大小寫,但是表中存儲的字元串卻是區分大小寫的。也就是說,
'ABC'
和'abc'
代表了兩個不同意義的字元串。 -
VARCHAR
型同
CHAR
類型一樣,VARCHAR
型也是用來指定存儲字元串的列的數據類型(字元串類型),也可以通過括弧內的數字來指定字元串的長度(最大長度)。但該類型的列是以可變長字元串的形式來保存字元串的 [6]。
定長字元串在字元數未達到最大長度時會用半形空格補足,但可變長字元串不同,即使字元數未達到最大長度,也不會用半形空格補足。
例如,我們向
VARCHAR(8)
類型的列中輸入字元串'abc'
的時候,保存的就是字元串'abc'
。該類型的列中存儲的字元串也和
CHAR
類型一樣,是區分大小寫的。特定的 SQL
Oracle 中使用
VARCHAR2
型(Oracle 中也有VARCHAR
這種數據類型,但並不推薦使用)。 -
DATE
型用來指定存儲日期(年月日)的列的數據類型(日期型)。
特定的 SQL
除了年月日之外,Oracle 中使用的
DATE
型還包含時分秒,但在本文中我們只學習日期部分。
1.6 約束的設置
約束是除了數據類型之外,對列中存儲的數據進行限制或者追加條件的功能。Product
表中設置了兩種約束。
Product
表的 product_id
列、product_name
列和 product_type
列的定義如下所示。
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
數據類型的右側設置了 NOT NULL
約束。NULL
是代表空白(無記錄)的關鍵字 [7]。
在 NULL
之前加上了表示否定的 NOT
,就是給該列設置了不能輸入空白,也就是必須輸入數據的約束(如果什麼都不輸入就會出錯)。
這樣一來,Product
表的 product_id
(商品編號)列、product_name
(商品名稱)列和 product_type
(商品種類)列就都成了必須輸入的項目。
另外,在創建 Product
表的 CREATE TABLE
語句的後面,還有下麵這樣的記述。
PRIMARY KEY (product_id)
這是用來給 product_id
列設置主鍵約束的。所謂鍵,就是在指定特定數據時使用的列的組合。鍵種類多樣,主鍵(primary key)就是可以特定一行數據的列 [8]。
也就是說,如果把 product_id
列指定為主鍵,就可以通過該列取出特定的商品數據了。
反之,如果向 product_id
列中輸入了重覆數據,就無法取出唯一的特定數據了(因為無法確定唯一的一行數據)。這樣就可以為某一列設置主鍵約束了。
二、表的刪除和更新
本節要點
使用
DROP TABLE
語句來刪除表。使用
ALTER TABLE
語句向表中添加列或者從表中刪除列。
2.1 表的刪除(DROP TABLE 語句)
此前介紹的都是關於 Product
表的內容的創建,下麵我們就來介紹一下刪除表的方法。刪除表的 SQL 語句非常簡單,只需要一行 DROP TABLE
語句即可。
語法 3 刪除表時使用的 DROP TABLE 語句
DROP TABLE <表名>;
如果想要刪除 Product
表,只需要像代碼清單 3 那樣書寫 SQL 語句即可 [9]。
代碼清單 3 刪除 Product 表
DROP TABLE Product;
DROP
在英語中是“丟掉”“捨棄”的意思。需要特別註意的是,刪除的表是無法恢復的 [10]。即使是被誤刪的表,也無法恢復,只能重新創建,然後重新插入數據。
如果不小心刪除了重要的業務表,那就太悲劇了。特別是存儲了大量數據的表,恢復起來費時費力,請大家務必註意!
法則 12
刪除了的表是無法恢復的。
在執行
DROP TABLE
語句之前請務必仔細確認。
2.2 表定義的更新(ALTER TABLE 語句)
有時好不容易把表創建出來之後才發現少了幾列,其實這時無需把表刪除再重新創建,只需使用變更表定義的 ALTER TABLE
語句就可以了。
ALTER
在英語中就是“改變”的意思。下麵就給大家介紹該語句通常的使用方法。
首先是添加列時使用的語法。
語法 4 添加列的 ALTER TABLE 語句
ALTER TABLE <表名> ADD COLUMN <列的定義>;
特定的 SQL
Oracle 和 SQL Server 中不用寫
COLUMN
。ALTER TABLE <表名> ADD <列名> ;
另外,在 Oracle 中同時添加多列的時候,可以像下麵這樣使用括弧。
ALTER TABLE <表名> ADD (<列名>,<列名>,……);
例如,我們可以使用代碼清單 4 中的語句在 Product
表中添加這樣一列,product_name_pinyin
(商品名稱(拼音)),該列可以存儲 100 位的可變長字元串。
代碼清單 4 添加一列可以存儲 100 位的可變長字元串的 product_name_pinyin
列
DB2 PostgreSQL MySQL
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
反之,刪除表中某列使用的語法如下所示。
語法 5 刪除列的 ALTER TABLE 語句
ALTER TABLE <表名> DROP COLUMN <列名>;
特定的 SQL
Oracle 中不用寫
COLUMN
。ALTER TABLE <表名> DROP <列名> ;
另外,在 Oracle 中同時刪除多列的時候,可以像下麵這樣使用括弧來實現。
ALTER TABLE <表名> DROP (<列名>,<列名>,……);
例如,我們可以使用代碼清單 5 中的語句來刪除之前添加的 product_name_pinyin
列。
代碼清單 5 刪除 product_name_pinyin 列
SQL Server DB2 PostgreSQL MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;
Oracle
ALTER TABLE Product DROP (product_name_pinyin);
ALTER TABLE
語句和 DROP TABLE
語句一樣,執行之後無法恢復。誤添的列可以通過 ALTER TABLE
語句刪除,或者將表全部刪除之後重新再創建。
法則 13
表定義變更之後無法恢復。
在執行
ALTER TABLE
語句之前請務必仔細確認。
2.3 向 Product 表中插入數據
最後讓我們來嘗試一下向表中插入數據。從 SQL SELECT WHERE 語句如何指定一個或多個查詢條件 開始,大家將會使用插入到 Product
表中的數據,來學習如何編寫操作數據的 SQL 語句。
向 Product
表中插入數據的 SQL 語句如代碼清單 6 所示。
代碼清單 6 向 Product 表中插入數據的 SQL 語句
SQL Server PostgreSQL
-- DML :插入數據
BEGIN TRANSACTION;------------------①
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '辦公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '運動T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '廚房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高壓鍋', '廚房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '廚房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '廚房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圓珠筆', '辦公用品', 100, NULL,'2009-11-11');
COMMIT;
特定的 SQL
DBMS 不同,代碼清單 6 中的 DML 語句也略有不同。
在 MySQL 中運行時,需要把 ① 中的
BEGIN TRANSACTION;
改寫成START TRANSACTION;
在 Oracle 和 DB2 中運行時,無需使用 ① 中的
BEGIN TRANSACTION;
(請予以刪除)。
使用插入行的指令語句 INSERT
,就可以把表 2 中的數據都插入到表中了。
開頭的 BEGIN TRANSACTION
語句是開始插入行的指令語句,結尾的 COMMIT
語句是確定插入行的指令語句。
這些指令語句將會在 什麼是 SQL 事務 詳細介紹,大家不必急於記住這些語句。
專欄
表的修改
本節將名為
Product
的表作為例子進行了講解,估計會有些讀者在匆忙中把表名誤寫成了Poduct
,創建出了名稱錯誤的表,這可怎麼辦呢?如果還沒有向表中插入數據,那麼只需要把表刪除,再重新創建一個名稱正確的表就可以了。
可是如果在發現表名錯誤之前就已經向表中插入了大量數據,再這樣做就麻煩了。畢竟插入大量的數據既費時又費力。
抑或起初決定好的表名,之後又覺得不好想換掉,這種情況也很麻煩。
其實很多資料庫都提供了可以修改表名的指令(
RENAME
)來解決這樣的問題。例如,如果想把
Poduct
表的名稱變為Product
,可以使用代碼清單 A 中的指令。代碼清單 A 變更表名
Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
DB2
RENAME TABLE Poduct TO Product;
SQL Server
sp_rename 'Poduct', 'Product';
MySQL
RENAME TABLE Poduct to Product;
通常在
RENAME
之後按照<變更前的名稱>
、<變更後的名稱>
的順序來指定表的名稱。各個資料庫的語法都不盡相同,是因為標準 SQL 並沒有
RENAME
,於是各個資料庫便使用了各自慣用的語法。如上所述,在創建了錯誤的表名,或者想要保存表的備份時,使用這些語句非常方便。
但美中不足的是,由於各個資料庫的語法不同,很難一下子想出恰當的指令。這時大家就可以來參考本專欄。
原文鏈接:https://www.developerastrid.com/sql/sql-create-alter-drop-table/
(完)
這裡我們僅指定了使用該語法所需的最少項目,實際開發資料庫時還需要指定各種其他項目。 ↩︎
如何在 Windows 10 中安裝 PostgreSQL 和連接設置 中介紹了在 PostgreSQL 中運行 SQL 語句的方法。執行了 如何在 Windows 10 中安裝 PostgreSQL 和連接設置 內容的讀者應該已經創建好了名為 shop 的資料庫。接下來請繼續完成創建表的工作。 ↩︎
這裡我們僅指定了使用該語法所需的最少項目,實際開發資料庫時還需要指定各種其他項目。 ↩︎
但是
NOT NULL
約束只能以列為單位進行設置。 ↩︎位元組是電腦內部的數據單位。一個字元通常需要 1 到 3 個位元組來表示(根據字元的種類和表現方式有所不同)。 ↩︎
VARCHAR
中的 VAR 是 VARING(可變的)的縮寫。 ↩︎NULL
這個詞是無或空的意思,NULL
是使用 SQL 時的常見關鍵字,請大家牢記。 ↩︎特定一行數據,也可以說是唯一確定一行數據。 ↩︎
隨後還需使用
Product
表來學習相關知識,請不要刪除Product
表。如果已經刪除,請重新創建Product
表。 ↩︎其實很多 RDBMS 都預留了恢復的功能,但還是請大家認為是無法恢復的。 ↩︎