Mysql入門基礎 資料庫建立篇

1.建立資料表---基礎（高手跳過）
正統方法：create [TEMPORARY] table 表名 [if not exists]
(建立的列項定義)
[表的選項]
[分區的選項];#正統的建立方式，具體的參數，請參考mysql手冊，在這裡不做詳細的解釋，只說一些比較特別的。
例： 複製代碼 代碼如下:create table user(id int unsigned not null auto_increment,
username char(15),
sex enum('M','F') default 'M',
userid varchar(20),
PRIMARY KEY(id,userid),
INDEX idx_user(userid),
)engine='innodb' charset=utf8;

複製資料庫結構： 複製代碼 代碼如下:create [temporary] table 表名 [if not exists] like 已存在的表名;//模仿已存在的表建立一個結構完全相同的表
mysql>create table vip_user like user;
mysql>create table vip_user select * from user where 0;//上例還可以這樣做,僅複製結構

複製並copy資料庫 複製代碼 代碼如下:create [temporary] table 新表名 select * from 舊錶名;#使用者可以人為的指定後續select組合成需要的語句。
create table dst_tbl(
id int not null auto_increment,
primary key(id)
) select a,b,c from src_tbl;

以此來建立各種各樣的符合使用者要求的資料庫，這裡只是做一個拋磚引玉。
2.查看修改的資料表結構 複製代碼 代碼如下:mysql> desc 資料表名;#查看資料表的結構
mysql> show create table 資料表名\G #查看資料表的構成語句,\G和;的意義相同，只不過\G是縱向顯示，這樣看的更清楚。
mysql> show table status like '資料表名'\G #查看資料表的狀態
mysql> show columns from 資料表名;#查看資料表的結構,同desc一樣，不過使用這條語句後面加like '欄位'可以只顯示指定欄位

3. 更改變據庫的結構
我們首先建立一個表
mysql> create table vip(id int null,username varchar(30));
修改表結構中的資料類型 複製代碼 代碼如下:mysql>alter table vip modify id smallint default 1;#更改資料記錄為1.
mysql>#alter table vip modify id smallint auto_increment;#大家執行一下這句話會提示錯誤，因為mysql中要求自動成長的列設為主鍵
mysql>alter table vip add primary(id);#設vip中的值的主鍵為id,執行這條語句後再執行上面的就沒問題了。
mysql>alter table vip modify username char(30) not null;#改變vip的username為char(30);
mysql>alter table vip modify username mediumtext first; #改變vip中的username,並將它設在最前面欄位，還有一個是after 欄位，是指放在某欄位後

對已有表新增欄位
mysql> alter table vip add sex enum('M','F') default 'M' not null after id;#新增sex欄位為enum類型，放在id的後面.

對已有欄位改名
mysql>alter table vip change sex usersex tinyint default 0 not null after username;改名欄位sex為usersex並改變類型和位置。
註：僅alter中modify,change很相似，但是modify不能改名只能改結構，但change即可以改名，也可以改類型。

刪除欄位
mysql>alter table vip drop usersex;#刪除 usersex欄位，警告，所有的該欄位資料都會丟失。
資料庫改名
mysql>alter table vip rename to vip_user;資料庫進行改名。
改變資料表引擎
mysql>alter table vip engine="MyISAM";

注意，修改資料結構是一個很危險的事，最好做好備份，以防不側。
還有部分的alter的語句和技巧我們將在以後涉及處一一到來.....
4. 關於資料約束
資料約束在mysql5中支援的越來越好了，但是現有的資料約束僅限於innodb,傳說中mysql5.2也會支援對資料約束的支援（期待..）
首先我們瞭解一下什麼是資料約束，因為我們平常建立表中都可能會有互相關聯的資訊，而資料約束是將兩個表進行關聯的一種紐帶。
例如：兩個表，一個usertype,一為userid,usertype中有一個關鍵字key為使用者類型編號，userid表中也有一 user_key對應著usertype表中的
1.首先我們要保證userid表中的所有值都在usertype中
2.其次我們要保證usertype中的值key進行改變userid表中的user_key值也會變化。
3.usertype中的值不能隨意刪除，除非userid表中不存在該usertype類型的值，如果要強制移除則會刪除userid中的所有 usertype的值。
如果沒有資料約束，我們每次insert/update可能要用數條語句才能保證資料的正確完整性，如果使用資料約束則只需要在定義的時候進行一下處理，而不用擔心太多。而且最重要的是使用資料約束能夠很好的保證資料，業務的完整性。
呵呵，說了這麼多，還沒有說資料約束的缺點：慢，使用資料約束要比不使用資料約束慢得多，而且使用者每次插入資料或更改資料，資料庫系統都會花一定的時間進行一定的檢查.但是隨著 mysql的日益成熟，這種速度會有著很大的改進。
就個人而言，我覺得非商務、即時系統對資料業務完整性要求較高的情況下使用資料約束還是很有必要的。其它情況下就仁者見仁智者見智了。

5.資料約束簡明解析
所以外鍵的定義必須必須滿足以下三種情況：
1.兩個表必須是innodb表類型
2.指定為外鍵的列必須進行索引
3.兩個表中關聯的外鍵類型必須相符。
我們先來看例子，在例子中進行學習：
mysql> create table parent(id int null,primary key(id)) engine=innodb; #建立一個主表
mysql> create table child(id int,parent_id int,
foreign key(parent_id)
references parent(id) on delete restrict on update cascade
) engine=innodb; #建立一個從表，並約外鍵關鍵字為parent_id,建立之間的關聯關係。
mysql> insert into parent values(1),(2),(3);#對主表插入資料
mysql> insert into child values(1,1),(1,2),(1,3);#對子表進行插入資料，對應不同的parent_id子child的id值均為1;
mysql> #insert into child values(1,1),(1,2),(1,3),(1,4); #看一下這會發生什嗎？報錯是吧？什麼原因？大家想想
說明：因為我們在建立語句的時候就約定了資料是進行外部索引鍵關聯的，而parent中不存在id值為4的主鍵，那麼子鍵當然更新不了了。
這時資料庫中的值為：
parent child
id id parent_id
1 1 1
2 1 2
3 1 3
我們繼續操作以體現外部索引鍵關聯表的作用
mysql> update parent set id=4 where id=1;#改變parent的值看一下child的反應
mysql> select * from parent;
mysql> select * from child;
這時資料庫中的值為：
parent child
id id parent_id
2 1 4
3 1 2
4 1 3
通過以上的例子大家可以清楚的看到使用者只是改變parent的值，而關聯的child值會自動改變。我們繼續
mysql> insert into child values(2,4),(3,4),(4,4);#為子表再添加一些其它的值。
mysql> #delete from parent where id=4; #大家執行這條語句看一下有什麼結果，錯誤吧？我們來分析一下提示錯誤
我們回顧一下我們建立外鍵的說細情況和關鍵語句：
foreign key(parent_id) #這句話的意思是指定對外關聯鍵為本表的parent_id;
references parent(id) on delete restrict on update cascade#這句話是則約束語句，references可以約束本資料庫的外鍵即parend_id與 parent資料表的id子鍵對應，並約束了on delete,on update時的操作，mysql共有以下幾種操作：
(1) restrict、no action 表示如果子表中有匹配的記錄,則不允許對父表對應候選索引鍵進行update/delete操作 ，大家現在明白了吧？為什麼我們執行delete from parent where id=4;時出現錯誤，因為子表(child)中還存在值.
(2) set null 在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但要注意子表的外鍵列不能為not null
(3) cascade 在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
大家可以根據自己的需要設定不同的動作，例如，我們要在父表刪除時，自動刪除子表的關聯值，則我們需進行設定：
references parent(id) on delete cascade on update cascade,實驗之前我們須要知道
1.已經定義設定好的外鍵是無法再行更改的，必須要刪除外鍵後再行建立（可能還有別的方法，哪位高手指點一下）
mysql> show create table child/G #得到 constraint（約束）的名字
說明：constraint是可以預設的，用於指定約束的名字，如果不指定則系統會自動為它取名，例如我們可以這樣：
constraint fk_child_key
foreign key(parent_id)
references parent(id) on delete restrict on update cascade;
這樣我們就指定了這個約束的整體名稱為fk_child_key,以後可以對這個進行操作了.
mysql> alter table child drop foreign key fk_child_key;#刪除約束
mysql> alter table child add foreign key ('parent_id)
references parent(id) on delete cascade
on update cascade;
mysql> show create table child/G #至此約束已更改，使用者可以查看一下更改情況

mysql>delete from parent where id=4;#我們再執行上面的那句，這時沒錯誤了吧？
mysql>select * from parent;
mysql>select * from child; #我們可以看到現在與parent_id為4的全部刪除。呵呵，以後都可以方便的使用了
這時資料庫中的值為：
parent child
id id parent_id
2 1 2
3 1 3
6.資料約束 的額外說明
如果定義了資料約束，則資料的插入或更改速度會很慢，特別是更改資料結構，插入資料時，效率低的可怕。
當客戶執行load data（載入資料，後續會介紹），alter table時建議使用以下命令，暫時關閉資料約束，等完成後再開啟，這樣速度至少可以提升20倍以上。
mysql> set foreign_key_checks=0;#關閉資料約束
mysql> load data infile '檔案絕對位址' into table 表名;#從文字檔中載入大量資料
mysql> set foreign_key_checks=1;#開啟資料約束