sql學習筆記（14）------------資料表，建立資料表的sql語句

sql學習筆記（14）------------資料表，建立資料表的sql語句

有關資料表的操作

資料庫基本操作

用 MySQL，目前(版本 3.23)你可以在三種基本資料庫表格式間選擇。當你建立一張表時，你可以告訴 MySQL它應該對於表使用哪個表類型。MySQL將總是建立一個.frm 檔案儲存表和列定義。視表類型而定，索引和資料將在其他檔案中儲存。

你能用 ALTER TABLE語句在不同類型的表之間變換。

MyISAM

MyISAM是預設表格類型，它是基於ISAM代碼並且有很多有用的擴充。索引儲存在一個有.MYI（MYindex）副檔名的檔案並且資料存放區在有.MYD（MYData）副檔名的檔案中。你能用 myisamchk 公用程式檢查/修複 MyISAM表。

 ISAM

你也可以使用放棄的 ISAM。這將在不久消失，因為 MyISAM是同一個東西的更好實現。ISAM使用一個 B-tree 索引，這個索引儲存在一個有.ISM副檔名的檔案中並且資料存放區在有.ISD 副檔名的檔案中，你可用 isamchk 公用程式檢查/修複 ISAM表。ISAM表不是
跨 OS/平台二進位可移植的。


 HEAP

HEAP 表格使用一個雜湊（hashed）索引並且儲存在記憶體中。這使他們更快，但是如果 MySQL崩潰，你將失去所有儲存的資料。HEAP 作為暫存資料表很可用！

 用 SHOW/ DESCRIBE 語句顯示資料表的資訊

句法：
SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STA TUS [FROM db_name] [LIKE wild]
{DESCRIBE | DESC} tbl_name {col_name | wild}

你能使用 db_name.tbl_name 作為 tbl_name FROM db_name 句法的另一種選擇。

SHOW TABLES列出在一個給定的資料庫中的表。 你也可以用mysqlshow db_name命令得到這張表。

注意：如果一個使用者沒有一個表的任何許可權，表將不在 SHOW TABLES 或 mysqlshow db_name 中的輸出中顯示。

SHOW COLUMNS 列出在一個給定表中的列。 如果列類型不同於你期望的是基於CREA TE TABLE語句的那樣，注意，MySQL有時改變列類型。

DESCRIBE 語句提供了類似 SHOW COLUMNS 的資訊。DESCRIBE 提供關於一張表的列的資訊。col_name 可以是一個列名字或包含 SQL 的“%”和“_”萬用字元的一個字串。這個語句為了與 Oracle 相容而提供的。SHOW TABLE STA TUS（在版本 3.23 引入)運行類似 SHOW STA TUS，但是提供每個表的更多資訊。你也可以使用 mysqlshow --status db_name 命令得到這張表。

SHOW FIELDS是SHOW COLUMNS一個同義字， SHOW KEYS是SHOW INDEX一個同義字。

你也可以用 mysqlshow db_name tbl_name 或 mysqlshow -k db_name tbl_name 列出一張表的列或索引。

SHOW INDEX 以非常相似於 ODBC的 SQLStatistics 調用的格式返回索引資訊。

  使用 mysqlshow  工具得到資訊
下面簡單介紹一下 mysqlshow 公用程式的用法，在得到資料庫和表的資訊上，使用起來非常方便。

 

得到已有資料庫的列表：
shell> mysqlshow

列出某資料庫 db_name 中已有的表：
shell> mysqlshow db_name

列出某資料庫表 db_name.tbl_name 的結構資訊：
shell>mysqlshow db_name tbl_name

列出一張表的索引：
shell> mysqlshow –k db_name tbl_name

  用 CREATE TABLE  語句建立資料表

用 CREA TE TABLE 語句建立表。此語句的完整文法是相當複雜的，因為存在那麼多
的可選子句， 但在實際中此語句的應用相當簡單。
有意思的是，大多數複雜東西都是一些子句，這些子句 MySQL 在分析後扔掉。

1 、 CREATE TABLE  語句的基本文法

CREA TE TABLE tbl_name(create_definition,...) [TYPE =table_type]
create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT][PRIMARY KEY]

在 MySQL3.22 或以後版本中， 表名可以被指定為 db_name.tbl_name， 不管有沒有當前的資料庫都可以。
例如，建立一個訪問者留言表：

shell> mysql –u root –p
mysql> create database mytest;
mysql> CREATE TABLE guestbook
-> (
-> visitor VARCHAR(40),
-> comments TEXT,
-> entrydate DATETIME
->);

如果一切正常，祝賀你，你已經建立了你的第一個表！

你所建立的表名為 guestbook， 你可以使用這個表來儲存來字你網站訪問者的資訊。 你是用 REEA TE TABLE 語句建立的這個表，這個語句有兩部分：第一部份指定表的名子；第二部份是括在括弧中的各欄位的名稱和屬性，相互之間用逗號隔開。表 guestbook 有三個欄位：visitor,comments 和 entrydate。visitor 欄位儲存訪問者的名
字， comments 欄位儲存訪問者對你網站的意見，entrydate 欄位儲存訪問者訪問你網站的日
期和時間。

注意每個欄位名後面都跟有一個專門的運算式。例如，欄位名 comments 後面跟有運算式 TEXT。這個運算式指定了欄位的資料類型。資料類型決定了一個欄位可以儲存什麼樣的資料。因為欄位comments 包含文本資訊，其資料類型定義為文本型。

2 、如何指定表的類型

你也可以在建立表時指定表的類型，如果不指定表的類型，在 3.22 及以前版本中預設為 ISAM表，在 3.23 版本中預設為 MyISAM表。你應該盡量使用 MyISAM表。指定表的類型經常用於建立一個 HEAP 表：

mysql> CREA TE TABLE fast(id int,articles TEXT) TYPE=HEAP;

3 、隱含的列說明的改變

在某些情況下，MySQL隱含地改變在一個 CREA TE TABLE語句給出的一個列說明。

（這也可能在 ALTER TABLE。 ）

長度小於 4 的 VARCHAR 被改變為 CHAR。

如果在一個表中的任何列有可變長度，結果是整個行是變長的。因此, 如果一張表包含任何變長的列(VARCHAR、TEXT 或 BLOB)，所有大於 3 個字元的 CHAR列被改變為 VARCHAR列。 這在任何方面都不影響你如何使用列； 在 MySQL中，VARCHAR只是儲存字元的一個不同方法。 MySQL實施這種改變， 是因為它節省空間的並且使表操作更快捷。

TIMESTAMP 的顯示尺寸必須是偶數且在 2 ～ 14 的範圍內。如果你指定 0 顯示尺寸或比 14 大，尺寸被強製為 14。從 1～13 範圍內的奇數值尺寸被強製為下一個更大的偶數。

你不能在一個TIMESTAMP 列裡面儲存一個文字 NULL；將它設為 NULL將設定為當前的日期和時間。因為 TIMESTAMP 列表現就是這樣，NULL 和 NOT NULL屬性不以一般的方式運用並且如果你指定他們，將被忽略。DESCRIBE tbl_name總是報告該 TIMESTAMP 列可能賦予了 NULL值。

如果你想要知道 MySQL 是否使用了除你指定的以外的一種列類型，在建立或改變你的表之後，發出一個DESCRIBE tbl_name 語句即可。

 利用 SELECT  的結果建立表
關聯式資料庫的一個重要概念是，任何資料都表示為行和列組成的表，而每條 SELECT

語句的結果也都是一個行和列組成的表。在許多情況下，來自 SELECT 的“表”僅是一個隨著您的工作在顯示屏上滾動的行和列的映像。在 MySQL 3.23 以前，如果想將SELECT 的結果儲存在一個表中以便以後的查詢使用，必須進行特殊的安排：
1) 運行 DESCRIBE 或 SHOW COLUMNS 查詢以確定想從中擷取資訊的表中的列類型。
2) 建立一個表，明確地指定剛才查看到的列的名稱和類型。
3) 在建立了該表後，發布一條 INSERT ... SELECT 查詢，檢索出結果並將它們插入所建立的表中。

在 MySQL 3.23 中，全都作了改動。CREA TE TABLE ... SELECT 語句消除了這些浪費時間的東西，使得能利用 SELECT 查詢的結果直接得出一個新表。只需一步就可以完成知道或指定所檢索的列的資料類型。這使得很容易建立一個完全用所喜歡的資料填充的表，並且為進一步查詢作了準備。

 如果你在 CREA TE 語句後指定一個 SELECT，MySQL 將為在 SELECT 中所有的單元創鍵新欄位。例如：

mysql> CREA TE TABLE test
-> (a int not null auto_increment,primary key (a), key(b))
-> SELECT b,c from test2;

這將建立一個有 3 個列(a，b，c)的表，其中 b，c 列的資料來自表 test2。注意如果在拷貝資料進表時發生任何錯誤，表將自動被刪除。可以通過選擇一個表的全部內容（無 WHERE 子句）來拷貝一個表，或利用一個總是失敗的 WHERE 子句來建立一個空表，如：

mysql> CREA TE TABLE test SELECT * from test2;
mysql> CREA TE TABLE test SELECT * from test2 where 0;

如果希望利用 LOAD DA TA 將一個資料檔案裝入原來的檔案中，而不敢肯定是否具有指定的正確資料格式時，建立空拷貝很有用。您並不希望在第一次未得到正確的選項時以原來表中畸形的記錄而告終。利用原表的空拷貝允許對特定的列和行分隔字元用 LOAD DATA 的選項進行實驗，直到對輸入資料的解釋滿意時為止。在滿意之後，就可以將資料
裝入原表了。
可結合使用 CREA TE TEMPORARY TABLE 與 SELECT 來建立一個暫存資料表作為它自身的拷貝，如：
這允許修改 my_tbl 的內容而不影響原來的內容。在希望實驗對某些修改表內容的查詢，而又不想更改原表內容時，這樣做很有用。為了使用利用原表名的預先編寫的指令碼，不 需要 為引用 不同 的表而 編輯這 些腳 本；只 需在 指令碼的 起始 處增加 CREA TE EMPORARY TABLE 語句即可。 相應的指令碼將建立一個臨時拷貝， 並對此拷貝進行操作，當指令碼結束時伺服器會自動刪除這個拷貝。
要建立一個作為自身的空拷貝的表，可以與 CREATE TEMPORARY ... SELECT 一起使用 WHERE 0 子句

但建立空表時有幾點要注意。在建立一個通過選擇資料填充的表時，其列名來自所選擇的列名。如果某個列作為運算式的結果計算，則該列的“名稱”為運算式的文本。運算式不是合法的列名，可在 mysql 中運行下列查詢瞭解這一點：
為了正常工作，可為該列提供一個合法的別稱：如果選擇了來自不同表的具有相同名稱的列，將會出現一定的困難。假定表 t1 和 t2兩者都具有列 c，而您希望建立一個來自兩個表中行的所有組合的表。那麼可以提供別名
指定新表中惟一性的列名，如：通過選擇資料進行填充來建立一個表並會自動拷貝原表的索引。

 用 ALTER TABLE 語句修改表的結構

有時你可能需要改變一下現有表的結構，那麼 Alter Table 語句將是你的合適選擇。

增加列
alter table tbl_name add col_name type
例如,給表增加一列weight
mysql>alter table pet add weight int;

刪除列
alter table tbl_name drop col_name
例如，刪除列weight：
mysql>alter table pet drop weight;

改變列
alter table tbl_name modify col_name type
例如，改變weight 的類型：
mysql> alter table pet modify weight samllint;
另一種方法是：
alter table tbl_name change old_col_name col_name type
例如：
mysql> alter table pet change weight weight samllint;

給列更名
mysql>alter table pet change weight wei;

給表更名
alter table tbl_name rename new_tbl
例如，把 pet 表更名為 animal
mysql>alter table pet rename animal;

改變表的類型
另外，可以為列增加或刪除索引等屬性

 用 DROP TABLE  語句刪除資料表


DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE刪除一個或多個資料庫表。所有表中的資料和表定義均被刪除，故小心使用這個命令！
在 MySQL 3.22 或以後版本，你可以使用關鍵詞 IF EXISTS 類避免不存在表的一個錯誤發生。
例如：
mysql>USE mytest;
mysql>DROP TABLE guestbook;
或者，也可以同時指定資料庫和表：
mysql>DROP TABLE mytest. guestbook;

 總結
本節講述了有關表的大部分操作，現在將所述內容總結如下：

MySQL的表的三種類型

如何建立表、刪除表

如何改變表的結構、名字

如何使用 mysqlshow 公用程式