2011年3月11日 星期五

SQL閱讀整理五

ALTER 改寫歷史
想對資料表修正,而又不想刪除資料 !
  • 使用ALTER TABLE
ALTER 相關使用項目
  • CHANGE:修改現存資料欄的名稱型別
  • MODIFY修改現存資料欄的型別位置
  • ADD:新增一各欄位。
  • DROP:移除某欄。
  • RENAME TO:改變表名稱。
ALTER 與 關鍵字 FIRST、LAST、AFTER、BEFORE、SECOND、THIRD...

原始表結構
contacts_info
nameagebirthdaygendercity

使用FIRST
ALTER TABLE contacts_info
ADD COLUMN address VARCHAR(50) FIRST;

contacts_info
addressnameagebirthdaygendercity

使用LAST
ALTER TABLE contacts_info
ADD COLUMN address VARCHAR(50) LAST;
contacts_info
nameagebirthdaygendercityaddress

使用AFTER
ALTER TABLE contacts_info
ADD COLUMN address VARCHAR(50)
AFTER birthday;
contacts_info
nameagebirthdayaddressgendercity

使用BEFORE
ALTER TABLE contacts_info
ADD COLUMN address VARCHAR(50)
BEFORE brithday;
contacts_info
nameageaddressbirthdaygendercity

使用SECOND
ALTER TABLE contacts_info
ADD COLUMN address VARCHAR(50) SECOND;
contacts_info
nameaddressagebirthdaygendercity


更改資料表名稱
ALTER TABLE contacts_info
RENAME TO contacts_lis;


ALTER與CHANGE
//原始範例表結構
drinks_lis
numbernametypeprice

ALTER TABLE drinks_list
CHANGE COLUMN number drink_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (drink_id);

drinks_lis
drink_idnametypeprice

  • CHANGE 可以修改資料欄的"名稱"與"型別"。
  • 把資料改成另種型別有可能會遺失資料。
  • 欲改變的資料型別與原始型別不相容,則指令不會執行。


只改型別
drinks_lis
drink_idnametypeprice

ALTER TABLE drinks_list
CHANGE COLUMN type VARCHAR(50);


只改欄位名稱
drinks_lis
drink_idnametypeprice

ALTER TABLE drinks_list
CHANGE COLUMN name drinkName;

drinks_lis
drink_iddrinkNametypeprice


修改兩各欄位
drinks_lis
drink_idnametypeprice

ALTER TABLE drinks_list
CHANGE COLUMN name drinkName VARCHAR(20),
CHANGE COLUMN type drinkType VARCHAR(20);
dirnks_lis
drink_iddrinkNamedrinkTypeprice


DROP COLUMN 刪除欄位
dirnks_lis
drink_iddrinkNamedrinkTypepricecost

ALTER TABLE drinks_lis
DROP COLUMN cost;

dirnks_lis
drink_iddrinkNamedrinkTypeprice


一些便利的字串函式
  • 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,找尋由第二參數中所給的符號開始,然後取出符號前面的所有東西。
  • 第二各參數,要找尋的符號。
  • 第三各參數,表該欄位中第幾各符號開始。

SQL閱讀整理四

第四章 聰明的資料表
具有唯一性資料的表

//不具唯一性的資料表
employees_info
nameagegendercity
江小魚20台北
林瀨瑤20東京
古小三22台北
古小三22台北
塵在天26高雄
表4-1

  • 表4-1出現同名的古小三,這兩筆資料無法辨識,簡單的說該表沒有唯一性。
  • 修正方式加入一各欄位使其每一筆資料都具有唯一性。
//修正成具有唯一性的資料表
employees_info
employeeIDnameagegendercity
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,設定主鍵。

SQL閱讀整理三

第三章 DELETE 與 UPDATE
DELETE 刪除紀錄
※注意DELETE記得要加上WHERE使用,否則會刪掉整張表的資料。
※DELETE用於刪除一列或多列,無法刪除單一欄。
※使用DELETE必須小心,每次執行都有可能意外刪除掉必要的資料。
※使用時定要確認WHERE所下的條件精確與否。
※由於DELETE不精準,因此刪除前最好先SELECT確定刪除對象是否正確。
example:
//原始資料表
employees_info
nameagegendercity
江小魚20台北
林瀨瑤20東京
古小三22台北
塵在天26高雄

//刪除name欄位為江小魚的資料。
DELETE FROM employess
WHERE name = '江小魚';
employees_info
nameagegendercity
林瀨瑤20東京
古小三22台北
塵在天26高雄

example:
//原始資料表
employees_info
nameagegendercity
江小魚20台北
林瀨瑤20東京
古小三22台北
塵在天26高雄

//刪除年齡20歲且為女性的資料。
DELETE FROM employess
WHERE age = 20 AND gender = "女";
employees_info
nameagegendercity
江小魚20台北
古小三22台北
塵在天26高雄


回憶一下INSERT,INSERT 與 DELETE
example:
//原始資料表
employees_info
nameagegendercity
江小魚20台北
林瀨瑤20東京
古小三22台北
塵在天26高雄

//插入一筆資料
INSERT INTO employees_info
VALUES('吳英雄', 27, '男', '高雄' );
employees_info
nameagegendercity
江小魚20台北
林瀨瑤20東京
古小三22台北
塵在天26高雄
吳英雄27高雄

//刪除前先用SELECT確定要刪除的資料是否正確。
SELECT * FROM employees_info
WHERE name = '林瀨瑤' AND gender = '女';
nameagegendercity
林瀨瑤20東京

//刪除資料
DELETE FROM employees_info
WHERE name = '林瀨瑤' AND gender = '女';
employees_info
nameagegendercity
江小魚20台北
古小三22台北
塵在天26高雄
吳英雄27高雄

閱讀階段心得:
資料庫規劃在實做上其實會有Primary Key,來代表資料表中唯一的一筆資料,確保不重複,而在employess_info設計上通常會加上一各員工編號來做Primary Key,以便識別,關於PK後面在來敘述。

閱讀到此,我想,像關於員工資料,在實際情境下,能夠刪除員工資料的人員,必定是具有管理權限的人事單位,而刪除員工資料在搜尋上因該是會以員工身分證號來找出員工編號,再以員工編號來執行刪除,若是有誤之處請指正之。


UPDATE 改變資料
※UPDATE可以修改已存在的資料。
※UPDATE可以搭配WHERE使用。
※UPDATE能夠只調整需要改變的欄位。
example:
//原始資料表
drinks_info
nametypeprice
可樂汽水25
芒果汁果汁40
黃色淺水艇酒精100
台灣啤酒酒精40
香蕉冰沙冰沙60

//SET用來指定將改調整的欄位與值。
UPDATE drinks_info
SET price = 35
WHERE name = '可樂' AND price = 25;
drinks_info
nametypeprice
可樂汽水35
芒果汁果汁40
黃色淺水艇酒精100
台灣啤酒酒精40
香蕉冰沙冰沙60

※UPDATE若沒加上WHERE使用,則所有SET提到的欄位全部都會被修改。
如上例,若沒加上WHERE則所有的price都會被改成35。


example:
//SQL中可以對欄位套用基礎數學運算。
//原始資料表 
drinks_info
nametypeprice
可樂汽水35
芒果汁果汁40
黃色淺水艇酒精100
台灣啤酒酒精40
香蕉冰沙冰沙60

//修改所有酒精類飲料的價格,原本價格再加五元
UPDATE drinks_info
SET price = price +5
WHERE name = '酒精' ;

drinks_info
nametypeprice
可樂汽水35
芒果汁果汁40
黃色淺水艇酒精105
台灣啤酒酒精45
香蕉冰沙冰沙60