跳轉到內容

資料庫設計/SQL 結構化查詢語言

Add links
來自華夏公益教科書

結構化查詢語言 (SQL) 是一種資料庫語言，用於管理關係資料庫管理系統中儲存的資料。SQL 最初由 IBM 於 1970 年代初期開發（Date 1986）。最初的版本稱為SEQUEL（結構化英語查詢語言），旨在操作和檢索儲存在 IBM 的準關係資料庫管理系統 System R 中的資料。然後在 1970 年代後期，Relational Software Inc.（現為 Oracle Corporation）推出了第一個商業化的 SQL 實現版本 Oracle V2，適用於 VAX 計算機。

許多目前可用的關係型 DBMS，例如 Oracle Database、Microsoft SQL Server（如圖 15.1 所示）、MySQL、IBM DB2、IBM Informix 和 Microsoft Access，都使用 SQL。

圖 15.1. Microsoft SQL Server 的示例，由 A. Watt 提供。

在 DBMS 中，SQL 資料庫語言用於

  • 建立資料庫和表結構
  • 執行基本資料管理任務（新增、刪除和修改）
  • 執行復雜的查詢，將原始資料轉換為有用的資訊

本章將重點介紹使用 SQL 建立資料庫和表結構，主要使用 SQL 作為資料定義語言（DDL）。在第 16 章中，我們將使用 SQL 作為資料操縱語言（DML）在資料庫表中插入、刪除、選擇和更新資料。

建立資料庫

[編輯 | 編輯原始碼]

主要的 SQL DDL 語句是 CREATE DATABASE 和 CREATE/DROP/ALTER TABLE。SQL 語句 CREATE 用於建立資料庫和表結構。

示例：CREATE DATABASE SW

SQL 語句 CREATE  DATABASE SW 會建立一個名為 SW 的新資料庫。建立資料庫後，下一步是建立資料庫表。

CREATE TABLE 命令的通用格式是

CREATE TABLE <表名>

(

列名，資料型別，可選列約束，

列名，資料型別，可選列約束，

可選表約束

);

表名是資料庫表的名稱，例如 Employee。CREATE TABLE 中的每個欄位都有三個部分（見上文）

  1. 列名
  2. 資料型別
  3. 可選列約束

列名

[編輯 | 編輯原始碼]

列名在表中必須唯一。一些列名的例子是 FirstName 和 LastName。

資料型別

[編輯 | 編輯原始碼]

資料型別，如下所述，必須是系統資料型別或使用者定義資料型別。許多資料型別都有大小，例如 CHAR(35) 或 Numeric(8,2)。

Bit – 整數資料，值為 1 或 0

Int – 整數（整數）資料，從 -2^31 (-2,147,483,648) 到 2^31 – 1  (2,147,483,647)

Smallint – 整數資料，從 2^15 (-32,768) 到 2^15 – 1 (32,767)

Tinyint – 整數資料，從 0 到 255

Decimal – 固定精度和比例的數字資料，從 -10^38 -1 到 10^38

Numeric – decimal 的同義詞

Timestamp – 資料庫範圍內的唯一數字

Uniqueidentifier – 全域性唯一識別符號 (GUID)

Money – 貨幣資料值，從 -2^63 (-922,337,203,685,477.5808) 到 2^63 – 1 (+922,337,203,685,477.5807)，精度為貨幣單位的萬分之一

Smallmoney – 貨幣資料值，從 -214,748.3648 到 +214,748.3647，精度為貨幣單位的萬分之一

Float – 浮點精度數字資料，從 -1.79E + 308 到 1.79E + 308

Real – 浮點精度數字資料，從 -3.40E + 38 到 3.40E + 38

Datetime – 日期和時間資料，從 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度為三分之一秒，或 3.33 毫秒

Smalldatetime – 日期和時間資料，從 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度為一分鐘

Char – 固定長度的非 Unicode 字元資料，最大長度為 8,000 個字元

Varchar – 可變長度的非 Unicode 資料，最大長度為 8,000 個字元

Text – 可變長度的非 Unicode 資料，最大長度為 2^31 – 1 (2,147,483,647) 個字元

Binary – 固定長度的二進位制資料，最大長度為 8,000 個位元組

Varbinary – 可變長度的二進位制資料，最大長度為 8,000 個位元組

Image – 可變長度的二進位制資料，最大長度為 2^31 – 1 (2,147,483,647) 個位元組

可選列約束

[編輯 | 編輯原始碼]

可選列約束是 NULL、NOT NULL、UNIQUE、PRIMARY KEY 和 DEFAULT，用於初始化新記錄的值。列約束 NULL 表示允許空值，這意味著可以在沒有此列的值的情況下建立行。列約束 NOT NULL 表示建立新行時必須提供值。

為了說明，我們將使用 SQL 語句 CREATE TABLE EMPLOYEES 建立具有 16 個屬性或欄位的 employees 表。

USE SW

CREATE TABLE EMPLOYEES

(

EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,

DepartmentName            CHAR(30)             NOT NULL           DEFAULT “Human Resources”,

FirstName                         CHAR(25)             NOT NULL,

LastName                            CHAR(25)             NOT NULL,

Category                              CHAR(20)             NOT NULL,

HourlyRate                       CURRENCY          NOT NULL,

TimeCard                             LOGICAL              NOT NULL,

HourlySalaried                   CHAR(1)               NOT NULL,

EmpType                             CHAR(1)               NOT NULL,

Terminated                       LOGICAL              NOT NULL,

ExemptCode                     CHAR(2)               NOT NULL,

Supervisor                           LOGICAL              NOT NULL,

SupervisorName                CHAR(50)             NOT NULL,

BirthDate                             DATE                     NOT NULL,

CollegeDegree                     CHAR(5)               NOT NULL,

CONSTRAINT                     Employee_PK    PRIMARY KEY(EmployeeNo

);

第一個欄位是 EmployeeNo，欄位型別為 CHAR。對於此欄位，欄位長度為 10 個字元，使用者不能將此欄位留空 (NOT NULL)。

類似地，第二個欄位是 DepartmentName，欄位型別為 CHAR，長度為 30。在定義完所有表列後，使用由 CONSTRAINT 標識的表約束來建立主鍵

CONSTRAINT     EmployeePK      PRIMARY KEY(EmployeeNo)

我們將在本章稍後進一步討論約束屬性。

同樣，我們可以使用 CREATE TABLE SQL DDL 命令建立部門表、專案表和分配表，如下例所示。

USE SW

CREATE TABLE DEPARTMENT

(

DepartmentName Char(35) NOT NULL,

BudgetCode Char(30) NOT NULL,

OfficeNumber Char(15) NOT NULL,

Phone Char(15) NOT NULL,

CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)

);

在這個例子中，一個專案表被建立，包含七個欄位：ProjectID、ProjectName、Department、MaxHours、StartDate 和 EndDate。

USE SW

CREATE TABLE PROJECT

(

ProjectID Int NOT NULL IDENTITY (1000,100),

ProjectName Char(50) NOT NULL,

Department Char(35) NOT NULL,

MaxHours Numeric(8,2) NOT NULL DEFAULT 100,

StartDate DateTime NULL,

EndDate DateTime NULL,

CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID)

);

在最後一個例子中，一個分配表被建立，包含三個欄位：ProjectID、EmployeeNumber 和 HoursWorked。分配表用於記錄誰（EmployeeNumber）在哪個專案（ProjectID）上工作了多長時間（HoursWorked）。

USE SW

CREATE TABLE ASSIGNMENT

(

ProjectID Int NOT NULL,

EmployeeNumber Int NOT NULL,

HoursWorked Numeric(6,2) NULL,

);

表約束

[編輯 | 編輯原始碼]

表約束由 CONSTRAINT 關鍵字標識，可用於實現下面描述的各種約束。

IDENTITY 約束

[編輯 | 編輯原始碼]

我們可以使用可選的列約束 IDENTITY 為該列提供唯一遞增的值。標識列通常與 PRIMARY KEY 約束一起使用，作為表的唯一行識別符號。IDENTITY 屬性可以分配給具有 tinyint、smallint、int、decimal 或 numeric 資料型別的列。此約束

  • 生成序列號
  • 不強制實體完整性
  • 只有一列可以具有 IDENTITY 屬性
  • 必須定義為整數、數字或十進位制資料型別
  • 不能更新具有 IDENTITY 屬性的列
  • 不能包含 NULL 值
  • 不能將預設值和預設約束繫結到列

對於 IDENTITY[(seed, increment)]

  • Seed – 標識列的初始值
  • Increment – 要新增到最後一個增量列的值

我們將使用另一個數據庫示例，透過在 HOTEL 資料庫中建立 tblHotel 表，進一步說明 SQL DDL 語句。

CREATE TABLE tblHotel

(

HotelNo Int IDENTITY (1,1),

Name Char(50) NOT NULL,

Address Char(50) NULL,

City Char(25) NULL,

)

UNIQUE 約束

UNIQUE 約束防止將重複值輸入列中。

  • PK 和 UNIQUE 約束都用於強制實體完整性。
  • 可以在一個表上定義多個 UNIQUE 約束。
  • 當將 UNIQUE 約束新增到現有表時，將始終驗證現有資料。
  • UNIQUE 約束可以放在接受空值的列上。只有一行可以是 NULL。
  • UNIQUE 約束會在選定的列上自動建立一個唯一索引。

這是 UNIQUE 約束的通用語法

[CONSTRAINT constraint_name]

UNIQUE [CLUSTERED | NONCLUSTERED]

(col_name [, col_name2 […, col_name16]])

[ON segment_name]

這是一個使用 UNIQUE 約束的示例。

CREATE TABLE EMPLOYEES

(

EmployeeNo                      CHAR(10)             NOT NULL           UNIQUE,

)

FOREIGN KEY 約束

[編輯 | 編輯原始碼]

FOREIGN KEY (FK) 約束定義一個列或列的組合，其值與另一個表的 PRIMARY KEY (PK) 匹配。

  • 當關聯表中的 PK 值更新/更改時，FK 中的值會自動更新。
  • FK 約束必須引用另一個表的 PK 或 UNIQUE 約束。
  • FK 的列數必須與 PK 或 UNIQUE 約束的列數相同。
  • 如果使用 WITH NOCHECK 選項，則 FK 約束不會驗證表中的現有資料。
  • 不會在參與 FK 約束的列上建立索引。

這是 FOREIGN KEY 約束的通用語法

[CONSTRAINT constraint_name]

[FOREIGN KEY (col_name [, col_name2 […, col_name16]])]

REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]

在這個例子中，tblRoom 表中的 HotelNo 欄位是 tblHotel 表中 HotelNo 欄位的 FK，如前所示。

USE HOTEL

GO

CREATE TABLE tblRoom

(

HotelNo Int NOT NULL ,

RoomNo Int NOT NULL,

Type Char(50) NULL,

Price Money NULL,

PRIMARY KEY (HotelNo, RoomNo),

FOREIGN KEY (HotelNo) REFERENCES tblHotel

)

CHECK 約束

[編輯 | 編輯原始碼]

CHECK 約束限制可以輸入表的的值。

  • 它可以包含類似於 WHERE 子句的搜尋條件。
  • 它可以引用同一表中的列。
  • CHECK 約束的資料驗證規則必須評估為布林表示式。
  • 它可以為綁定了規則的列定義。

這是 CHECK 約束的通用語法

[CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

在這個例子中，Type 欄位被限制為只能包含 'Single'、'Double'、'Suite' 或 'Executive' 型別。

USE HOTEL

GO

CREATE TABLE tblRoom

(

HotelNo Int NOT NULL,

RoomNo Int NOT NULL,

Type Char(50) NULL,

Price Money NULL,

PRIMARY KEY (HotelNo, RoomNo),

FOREIGN KEY (HotelNo) REFERENCES tblHotel

CONSTRAINT Valid_Type

CHECK (Type IN ('Single', 'Double', 'Suite', 'Executive'))

)

在第二個例子中，員工入職日期應該在 2004 年 1 月 1 日之前，或者工資上限為 300000 美元。

GO

CREATE TABLE SALESREPS

(

Empl_num Int Not Null

CHECK (Empl_num BETWEEN 101 and 199),

Name Char (15),

Age Int CHECK (Age >= 21),

Quota Money CHECK (Quota >= 0.0),

HireDate DateTime,

CONSTRAINT QuotaCap CHECK ((HireDate < "01-01-2004") OR (Quota <=300000))

)

DEFAULT 約束

[編輯 | 編輯原始碼]

DEFAULT 約束用於提供一個值，如果使用者沒有提供值，則會自動新增到列中。

  • 一列只能有一個 DEFAULT。
  • DEFAULT 約束不能用於具有 timestamp 資料型別或標識屬性的列。
  • DEFAULT 約束在建立時會自動繫結到列。

DEFAULT 約束的通用語法是

[CONSTRAINT constraint_name]

DEFAULT {constant_expression | niladic-function | NULL}

[FOR col_name]

此示例將 city 欄位的預設值設定為 'Vancouver'。

USE HOTEL

ALTER TABLE tblHotel

Add CONSTRAINT df_city DEFAULT 'Vancouver' FOR City

使用者定義型別

[編輯 | 編輯原始碼]

使用者定義型別始終基於系統提供的資料型別。它們可以強制執行資料完整性，並且允許空值。

要在 SQL Server 中建立使用者定義的資料型別，請在您的資料庫中選擇“可程式設計性”下的型別。接下來，右鍵單擊並選擇“新建”->“使用者定義資料型別”，或執行 sp_addtype 系統儲存過程。然後，鍵入

sp_addtype ssn, 'varchar(11)', 'NOT NULL'

這將新增一個名為 SIN 的新使用者定義資料型別，其中包含九個字元。

在這個例子中，EmployeeSIN 欄位使用使用者定義的資料型別 SIN。

CREATE TABLE SINTable

(

EmployeeID INT Primary Key,

員工SIN    SIN，

約束 CheckSIN

檢查 (EmployeeSIN LIKE

‘ [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ‘)

)

更改表

[編輯 | 編輯原始碼]

可以使用 ALTER TABLE 語句新增和刪除約束。

  • ALTER TABLE 允許刪除列。
  • 新增約束時，將驗證所有現有資料是否存在違規情況。

在此示例中，我們使用 ALTER TABLE 語句將 IDENTITY 屬性應用於 ColumnName 欄位。

USE HOTEL

GO

ALTER TABLE  tblHotel

新增約束 unqName 唯一 (名稱)

使用 ALTER TABLE 語句新增具有 IDENTITY 屬性的列，例如 ALTER TABLE 表名。

新增

ColumnName         int    IDENTITY(種子，增量)

刪除表

[編輯 | 編輯原始碼]

DROP TABLE 將從資料庫中刪除一個表。請確保選擇了正確的資料庫。

DROP TABLE tblHotel

執行上述 SQL DROP TABLE 語句將從資料庫中刪除 tblHotel 表。

關鍵詞

[編輯 | 編輯原始碼]

DDL：資料定義語言的縮寫

DML：資料操作語言的縮寫

SEQUEL：結構化英語查詢語言的首字母縮寫詞；旨在操作和檢索儲存在 IBM 的準關係型資料庫管理系統 System R 中的資料

結構化查詢語言 (SQL)：一種為管理關係型資料庫管理系統中儲存的資料而設計的資料庫語言

練習

[編輯 | 編輯原始碼]
  1. 使用第 9 章練習的資訊，使用 Transact SQL 實現該模式（顯示每個表的 SQL 語句）。也實現約束。
  2. 在 SQL Server 中建立此處所示的表，並顯示你使用的語句。表：員工
屬性（欄位）名稱 資料宣告
EMP_NUM CHAR(3)
EMP_LNAME VARCHAR(15)
EMP_FNAME VARCHAR(15)
EMP_INITIAL CHAR(1)
EMP_HIREDATE 日期
JOB_CODE CHAR(3)

在建立了問題 2 中的表結構後，編寫 SQL 程式碼以輸入圖 15.1 中所示表的行。

圖 15.2。包含問題 4-10 資料的員工表，作者：A. Watt。

使用圖 15.2 回答以下問題

  1. 編寫 SQL 程式碼將人員編號為 107 的人的工作程式碼更改為 501。完成任務後，檢查結果，然後將工作程式碼重置為其原始值。
  2. 假設員工表中顯示的資料已輸入，請編寫 SQL 程式碼以列出工作程式碼為 502 的所有屬性。
  3. 編寫 SQL 程式碼以刪除名為 William Smithfield 的人的行，該人於 2004 年 6 月 22 日被僱用，其工作程式碼分類為 500。（提示：使用邏輯運算子包含此問題中給出的所有資訊。）
  4. 將屬性 EMP_PCT 和 PROJ_NUM 新增到員工表中。EMP_PCT 是要支付給每個員工的獎金百分比。
  5. 使用單個命令，編寫 SQL 程式碼，該程式碼將為所有工作分類 (JOB_CODE) 為 500 的員工輸入專案編號 (PROJ_NUM) = 18。
  6. 使用單個命令，編寫 SQL 程式碼，該程式碼將為所有工作分類 (JOB_CODE) 為 502 或更高的員工輸入專案編號 (PROJ_NUM) = 25。
  7. 編寫 SQL 程式碼，該程式碼將為所有在 1994 年 1 月 1 日之前被僱用且工作程式碼至少為 501 的員工將 PROJ_NUM 更改為 14。（你可以假設該表將在此問題之前恢復到其原始狀態。）

另請參見 附錄 C：帶解決方案的 SQL 實驗室

參考文獻

[編輯 | 編輯原始碼]
  • Date, C.J. 關係型資料庫精選著作。閱讀：馬薩諸塞州：Addison-Wesley 出版公司， 1986 年，第 269-311 頁。
  • BCOpenCampus：資料庫設計
檢索自 "https://wikibook.tw/w/index.php?title=Database_Design/SQL_Structured_Query_Language&oldid=3645571"
華夏公益教科書