DDL DDL是DBMS的核心組件,是SQL的重要組成部分. DDL的正確性和穩定性是整個SQL髮型的重要基礎. DDL的基礎語法及設計工具 DDL的英文是Data Definition Language,也就是數據定義語言.定義了資料庫的結構和數據表的結構.常用的功能急救室增刪改,對應的命令分別是 ...
DDL
DDL是DBMS的核心組件,是SQL的重要組成部分. DDL的正確性和穩定性是整個SQL髮型的重要基礎.
DDL的基礎語法及設計工具
DDL的英文是Data Definition Language,也就是數據定義語言.定義了資料庫的結構和數據表的結構.常用的功能急救室增刪改,對應的命令分別是CREATE、DROP和ALTER.
- 對資料庫進行定義
CREATE DATABASE nba; // 創建名為nba的資料庫
DROP DATABASE nba; // 刪除名為nba的資料庫- 對數據表進行定義
CREATE TABLE table_name; // 創建表,table_name指表名創建表的結構呢? 舉個實際的例子, 我們創建一個球員表, 表名為player, 裡面有兩個欄位, 一個是player_id, 它是int類型,另一個是player_name欄位是varchar(255)類型, 兩個欄位都不能為空, 並且player_id是遞增的.
接下來創建表的語句這麼就是:
CREATE TABLE player(
player_id int(11) NOT NULL AUTO_INCREMENT,
player_name varchar(255) NOT NULL
);註意的是每個欄位定義的語句最後使用 , 作為結束符, 最後一個欄位的定義結束之後沒有逗號的 , 並且語句最後是以 ; 結尾的. 數據類型中int(11)代表整數類型, 顯示長度是11位, 括弧中的參數11代表的是最大有效顯示長度, 與類型包含的數值大小無關. varchar(255)代表的是最大長度為255的可變字元串類型. NOT NULL表名整個欄位不能為空值,是一種數據約束. AUTO_INCREMENT代表主鍵自動增長.(一般情況下使用可視化工具類創建和操作資料庫和資料庫表,比如Navicat)
接下來針對player表,設計下麵欄位:
其中player_id是數據表player的主鍵, 且自動增長, 也就是player_id會從1開始, 然後每次加一, 不必為它賦值. player_id、team_id、player_name這三個欄位均不為空, height欄位可以為空.
使用Navicat工具創建表並導出的SQL文件如下所示:
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`(
`player_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`team_name` varchar(255) CHARACTER SET utf8 collate utf8_general_ci NOT NULL ,
`height` float(3,2) NULL DEFAULT0.00,
PRIMARY KEY(`player_id`) USING BTREE,
UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;可以看到整個SQL文件中的DDL處理, 首先刪除player表(如果資料庫中存在該表的話), 然後再創建player表, 裡面的欄位名和表名都使用了反引號,這是為了避免名稱與MYSQL保留欄位相同,對資料庫表和欄位名都加上反引號.
其中player_name欄位的字元集是utf8, 排序規則是utf8_general_ci, 代表對大小寫不敏感, 如果設置為utf8_bin, 表示對大小寫敏感.
因為player_id設置為了主鍵, 所以在DDL中使用PRIMARY KEY進行規定,同時索引方法採用BTREE.
對player_name欄位進行索引, 在設置索引時, 可以設置UNIQUE INDEX(唯一索引), 也可以設置為其它索引方式, 比如NORMAL INDEX(普通索引), 這裡我們採用UNIQUE INDEX. 唯一索引和普通索引的區別在於對欄位進行了唯一性約束. 在索引方式上, 可以選擇BTREE和HASH, 這裡採用BTREE方法進行索引.
整個數據表的存儲規則採用InnoDB, 是MYSQL5.5之後的預設存儲引擎, 將字元集設置為utf8, 排序規則設置為utf8_general_ci, 行格式為Dynamic, 就可以定義數據表的最後約定了:
ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;修改表結構
創建完表之後, 可以對錶結構進行修改, 使用DDL命令來完成.
- 添加欄位
ALTER TABLE player ADD (age int(11));- 修改欄位名, 將age欄位改成player_age
ALTER TABLE player RENAME COLUMN age to player_age;- 修改欄位的數據類型
ALTER TABLE player MODIFT (player_age float(3,1));- 刪除欄位, 刪除剛纔添加的player_age欄位
ALTER TABLE player DROP CLOUMN player_age;數據表的常見約束
在創建數據表的時候, 會對欄位進行約束, 約束的目的在於保證RDBMS裡面的數據的準確性和一致性.
- 主鍵約束
主鍵起的作用是唯一標識一條記錄, 不能重覆, 不能為空, 即UNIQUE + NOT NULL. 一個數據表的主鍵只能有一個. 但是主鍵可以是一個欄位, 也可以是多個欄位符合組成. 上面的player_id就是主鍵.
- 外鍵約束
外鍵起的作用是確保表與表之間引用的完整性. 一個表中的外鍵對應了另外一張表中的主鍵. 外鍵可以重覆並且可以為空. 比如player_id是player表的主鍵,如果想設置一個球員比分表player_score, 可以再player_score中設置player_id為外鍵,關聯到player表.
- 唯一約束
唯一約束就是表明欄位在表中的數值唯一, 主要是對除主鍵以外的其他欄位(主鍵自帶數值唯一BUFF). 上面對player_name進行了唯一性約束,也就是說球員的姓名不能相同. 註意的是唯一性約束和普通索引(NORMAL INDEX)之間的區別: 唯一性約束相當於創建了一個約束和普通索引, 目的是保證欄位的正確性, 而普通索引只是提升數據檢索的速度, 並不對欄位的唯一性進行約束.
- NOT NULL約束
對欄位定義了NOT NULL, 表明欄位不能為空, 必須有取值.
- DEFAULT
表明欄位的預設值, 如果在插入數據的時候該欄位沒有取值, 就設置為預設值.
- CHECK約束
用來檢查特定欄位取值範圍的有效性, CHECK約束的結果不能為FALSE.
設計數據表的原則
"三少一多的原則":
- 數據表的個數越少越好
RDBMS的核心在於對實體和聯繫的定義, 也就是E-R圖(Entity Relation Diagram), 數據表越少, 說明實體和聯繫設計得越簡潔, 即方便理解有方便操作.
- 數據表中的欄位個數越少越好
欄位個數越多, 數據冗餘的可能性越大. 設置欄位個數少的前提是各個欄位相互獨立, 而不是某個欄位的取值可以由其它欄位計算出來. 當然欄位個數少是相對的, 通常會在數據冗餘和檢索效率中進行平衡.
- 數據表中聯合主鍵的欄位個數越少越好
設置主鍵是為了確定唯一性, 當一個欄位無法確定唯一性, 就需要採用聯合主鍵的方式. 聯合主鍵中的欄位越多, 占用的所以索引空間越大, 會加大理解難度, 會增加運行時間和索引空間.
- 使用主鍵和外鍵越多越好
資料庫的設計實際上就是定義各種表, 一級各種欄位間的關係, 關係越多, 證明實體之間的冗餘度越低, 利用度越高, 這樣做的好處在於不僅保證數據表之間的獨立性, 還能提升相互之間的關聯使用率. (不過在我現在的公司, 基本上沒有使用外鍵, 不知道是因為影響效率還是什麼, 外鍵的意義起不到作用)
作者的意思是大型項目中後期，可以採用業務層來實現，取消外鍵提高效率。不過在SQL學習之初，包括在系統最初設計的時候，還是建議你採用規範的資料庫設計，也就是採用外鍵來對數據表進行約束。因為這樣可以建立一個強一致性，可靠性高的資料庫結構，也不需要在業務層來實現過多的檢查。當然在項目後期，業務量增大的情況下，你需要更多考慮到資料庫性能問題，可以取消外鍵的約束，轉移到業務層來實現。而且在大型互聯網項目中，考慮到分庫分表的情況，也會降低外鍵的使用。
建議是 不過在SQL學習，以及項目早期，還是建議你使用外鍵。在項目後期，你可以分析有哪些外鍵造成了過多的性能消耗。一般遵循2/8原則，會有20%的外鍵造成80%的資源效率，你可以只把這20%的外鍵進行開放，採用業務層邏輯來進行實現，當然你需要保證業務層的實現沒有錯誤。不同階段，考慮的問題不同。當用戶和業務量增大的時候，對於大型互聯網應用，也會通過減少外鍵的使用，來減低死鎖發生的概率，提高併發處理能力。
