ALTER 改寫歷史想對資料表修正,而又不想刪除資料 !ALTER 相關使用項目- CHANGE:修改現存資料欄的名稱與型別。
- MODIFY:修改現存資料欄的型別與位置。
- ADD:新增一各欄位。
- DROP:移除某欄。
- RENAME TO:改變表名稱。
ALTER 與 關鍵字 FIRST、LAST、AFTER、BEFORE、SECOND、THIRD...原始表結構contacts_infoname | age | birthday | gender | city |
使用FIRSTALTER TABLE contacts_infoADD COLUMN address VARCHAR(50) FIRST;
contacts_infoaddress | name | age | birthday | gender | city |
使用LASTALTER TABLE contacts_infoADD COLUMN address VARCHAR(50) LAST;contacts_infoname | age | birthday | gender | city | address |
使用AFTERALTER TABLE contacts_infoADD COLUMN address VARCHAR(50)AFTER birthday;contacts_infoname | age | birthday | address | gender | city |
使用BEFOREALTER TABLE contacts_infoADD COLUMN address VARCHAR(50)BEFORE brithday;contacts_infoname | age | address | birthday | gender | city |
使用SECONDALTER TABLE contacts_infoADD COLUMN address VARCHAR(50) SECOND;contacts_infoname | address | age | birthday | gender | city |
更改資料表名稱ALTER TABLE contacts_infoRENAME TO contacts_lis;ALTER與CHANGE//原始範例表結構drinks_lisnumber | name | type | price |
ALTER TABLE drinks_listCHANGE COLUMN number drink_id INT NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY (drink_id);drinks_lisdrink_id | name | type | price |
- CHANGE 可以修改資料欄的"名稱"與"型別"。
- 把資料改成另種型別有可能會遺失資料。
- 欲改變的資料型別與原始型別不相容,則指令不會執行。
只改型別drinks_lisdrink_id | name | type | price |
ALTER TABLE drinks_listCHANGE COLUMN type VARCHAR(50);只改欄位名稱drinks_lisdrink_id | name | type | price |
ALTER TABLE drinks_listCHANGE COLUMN name drinkName;drinks_lisdrink_id | drinkName | type | price |
修改兩各欄位drinks_lisdrink_id | name | type | price |
ALTER TABLE drinks_listCHANGE COLUMN name drinkName VARCHAR(20),CHANGE COLUMN type drinkType VARCHAR(20);dirnks_lisdrink_id | drinkName | drinkType | price |
DROP COLUMN 刪除欄位dirnks_lisdrink_id | drinkName | drinkType | price | cost |
ALTER TABLE drinks_lisDROP COLUMN cost;dirnks_lisdrink_id | drinkName | drinkType | price |
一些便利的字串函式- RIGHT(欄位 , 字元數量) FROM tableName;
- SUBSTRING_INDEX(欄位, ',' , 1) FROM tableName;
example:SELECT RIGHT(location , 2) FROM my_contacts;- RIGHT,表示從右側開始
- 函數中第一各參數表,目標欄位。
- 函數中第二各參數表,右側開始選擇的字元數量。
example:SELECT SUBSTRING_INDEX(location, ',', 1)FROM my_contacts;這段敘述是說從location欄中,取回第一個豆號前所有的字串。- SUBSTRING_INDEX,找尋由第二參數中所給的符號開始,然後取出符號前面的所有東西。
- 第二各參數,要找尋的符號。
- 第三各參數,表該欄位中第幾各符號開始。
第四章 聰明的資料表具有唯一性資料的表//不具唯一性的資料表employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
表4-1
- 表4-1出現同名的古小三,這兩筆資料無法辨識,簡單的說該表沒有唯一性。
- 修正方式加入一各欄位使其每一筆資料都具有唯一性。
//修正成具有唯一性的資料表employees_infoemployeeID | name | age | gender | city |
00001 | 江小魚 | 20 | 男 | 台北 |
00002 | 林瀨瑤 | 20 | 女 | 東京 |
00003 | 古小三 | 22 | 女 | 台北 |
00004 | 古小三 | 22 | 女 | 台北 |
00005 | 塵在天 | 26 | 男 | 高雄 |
- 如此加上employeeID之後我們可以辨識出兩筆古小三是不同筆的資料。
而加入的這一個欄位,就是Primary Key。Primary Key的規則- 主鍵不可為NULL
- 插入新紀錄時,也必須指定主鍵值
- 必須簡潔
- 主鍵值不可修改
什麼是Primary Key? - 是資料表中的某各資料欄位,它可以獨一無二的分辨出每一筆紀錄。
第一階正規劃的步驟之一- 具有Primary Key的資料表,是資料表第一階正規化的步驟之一。
關於正規劃將於後面章節將會談論查詢已存在資料表當時建立的SQL敘述碼- SHOW CREATE TABLE yourTable;
建立一各不具Primary Key的資料表CREATE TABLE employees_A( name varchar(20) NOT NULL, age INT default NULL, gender char(4), city varchar(20))建立一各具有Primary Key的資料表CREATE TABLE employees_B
(
employeeID INT NOT NULL,
name varchar(20) NOT NULL,
age INT default NULL,
gender char(4),
city varchar(20),
PRIMARY KEY (employeeID)
)
- PRIMARY KEY (columnName),用於指定Primary Key。
建立一各具有具有Primary Key並且會自動增加的資料表
CREATE TABLE employees_C
(
employeeID INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
age INT default NULL,
gender char(4),
city varchar(20),
PRIMARY KEY (employeeID)
)
- AUTO_INCREMENT,讓employeeIDy自動遞增數值。
其實不用重新建立具有Primary Key的資料表,可以使用ALTER來修正
ALTER TABLE employees_A
ADD COLUMN employeeID INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (employeeID );
- FIRST要求將該欄位安至於最前面。
- ADD COLUMN,用於加入一各欄位。
- ADD PRIMARY KEY,設定主鍵。
第三章 DELETE 與 UPDATEDELETE 刪除紀錄※注意DELETE記得要加上WHERE使用,否則會刪掉整張表的資料。※DELETE用於刪除一列或多列,無法刪除單一欄。※使用DELETE必須小心,每次執行都有可能意外刪除掉必要的資料。※使用時定要確認WHERE所下的條件精確與否。※由於DELETE不精準,因此刪除前最好先SELECT確定刪除對象是否正確。example://原始資料表employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
//刪除name欄位為江小魚的資料。DELETE FROM employessWHERE name = '江小魚';employees_infoname | age | gender | city |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
example://原始資料表employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
//刪除年齡20歲且為女性的資料。DELETE FROM employessWHERE age = 20 AND gender = "女";employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
回憶一下INSERT,INSERT 與 DELETEexample://原始資料表employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
//插入一筆資料INSERT INTO employees_infoVALUES('吳英雄', 27, '男', '高雄' );employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
林瀨瑤 | 20 | 女 | 東京 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
吳英雄 | 27 | 男 | 高雄 |
//刪除前先用SELECT確定要刪除的資料是否正確。SELECT * FROM employees_infoWHERE name = '林瀨瑤' AND gender = '女';name | age | gender | city |
林瀨瑤 | 20 | 女 | 東京 |
//刪除資料DELETE FROM employees_infoWHERE name = '林瀨瑤' AND gender = '女';employees_infoname | age | gender | city |
江小魚 | 20 | 男 | 台北 |
古小三 | 22 | 女 | 台北 |
塵在天 | 26 | 男 | 高雄 |
吳英雄 | 27 | 男 | 高雄 |
閱讀階段心得:資料庫規劃在實做上其實會有Primary Key,來代表資料表中唯一的一筆資料,確保不重複,而在employess_info設計上通常會加上一各員工編號來做Primary Key,以便識別,關於PK後面在來敘述。閱讀到此,我想,像關於員工資料,在實際情境下,能夠刪除員工資料的人員,必定是具有管理權限的人事單位,而刪除員工資料在搜尋上因該是會以員工身分證號來找出員工編號,再以員工編號來執行刪除,若是有誤之處請指正之。UPDATE 改變資料※UPDATE可以修改已存在的資料。※UPDATE可以搭配WHERE使用。※UPDATE能夠只調整需要改變的欄位。example://原始資料表drinks_infoname | type | price |
可樂 | 汽水 | 25 |
芒果汁 | 果汁 | 40 |
黃色淺水艇 | 酒精 | 100 |
台灣啤酒 | 酒精 | 40 |
香蕉冰沙 | 冰沙 | 60 |
//SET用來指定將改調整的欄位與值。UPDATE drinks_infoSET price = 35WHERE name = '可樂' AND price = 25;drinks_infoname | type | price |
可樂 | 汽水 | 35 |
芒果汁 | 果汁 | 40 |
黃色淺水艇 | 酒精 | 100 |
台灣啤酒 | 酒精 | 40 |
香蕉冰沙 | 冰沙 | 60 |
※UPDATE若沒加上WHERE使用,則所有SET提到的欄位全部都會被修改。如上例,若沒加上WHERE則所有的price都會被改成35。example://SQL中可以對欄位套用基礎數學運算。//原始資料表 drinks_infoname | type | price |
可樂 | 汽水 | 35 |
芒果汁 | 果汁 | 40 |
黃色淺水艇 | 酒精 | 100 |
台灣啤酒 | 酒精 | 40 |
香蕉冰沙 | 冰沙 | 60 |
//修改所有酒精類飲料的價格,原本價格再加五元UPDATE drinks_info
SET price = price +5
WHERE name = '酒精' ;drinks_infoname | type | price |
可樂 | 汽水 | 35 |
芒果汁 | 果汁 | 40 |
黃色淺水艇 | 酒精 | 105 |
台灣啤酒 | 酒精 | 45 |
香蕉冰沙 | 冰沙 | 60 |