首頁 / CompScience / Database / MySQL / MySQL 5第十五次學科練習

MySQL 5第十五次學科練習

這是第十五次ㄚ琪自我練習的結果,咦,上一次不是最後一次嗎?沒啦,有關我的認證經驗你可以看大型資料庫管理系統-專業級(MySQL 5.0)測驗認證受挫,這裡我就不多講了,只專注在如何幫大家精進你的MySQL能力。

這一次的測試50題,答對46題得92分,錯4題扣4分,總計得分88分。

2013-08-21_111648

 

這是MySQL 5實力養成暨評量裡的1-17.『下列關於關聯式資料庫的敘述,何者不正確?』

答案:(C) 表格內的資料不可以重複

這一題看來只要有中文閱讀力加上一般的資料庫基礎就可以答題,看來是我們閱讀能力有問題

2013-08-21_111541

這是MySQL 5實力養成暨評量裡的3-75.『SQL指令中,使用DROP指令無法刪除下列何者?』

答案:(A) 資料目錄

這一題本應該是簡單的,但是粗心還是錯了,也可能是不熟ALTER TABLE的敘述吧,我們重新複習一下,手冊可以看MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.6 ALTER TABLE Syntax,中文的部份看MySQL 5.1参考手册 :: 13. SQL语句语法::13.1. 数据定义语句::13.1.2. ALTER TABLE语法

轉譯繁體中文於下:

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal |  DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

ALTER TABLE用於更改原有資料表的結構。例如,您可以增加或刪除欄位,建立或取消索引,更改原有欄位的型態,或重新命名欄位或資料表。您還可以更改資料表的評注和資料表的型態。

允許進行的變更中,許多子句的語法與CREATE TABLE中的子句的語法相近。其中包括table_options修改,選項有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH等。請見13.1.5節,「CREATE TABLE語法」

儲存引擎不支援有些操作,如果進行這些操作,會出現警告。使用SHOW WARNINGS可以顯示出這些警告。請參見13.5.4.22節,「SHOW WARNINGS語法」

如果您使用ALTER TABLE更改欄位規約,但是DESCRIBE tbl_name提示您欄位規約並沒有改變,則可能是因為MySQL忽略了您所做的更改。忽略更改的原因見13.1.5.1節,「沉寂的列規格變更」。例如,如果您試圖把VARCHAR欄位更改為CHAR欄位,此時,如果資料表包含其它長度可變的欄位,則MySQL仍會使用VARCHAR。

ALTER TABLE運行時會對原資料表進行臨時複製,在副本上進行更改,然後刪除原資料表,再對新資料表進行重命名。在執行ALTER TABLE時,其它用戶可以閱讀原資料表,但是對資料表的更新和修改的操作將被延遲,直到新資料表生成為止。新資料表生成後,這些更新和修改訊息會自動轉移到新資料表上。

注意,如果您在執行ALTER TABLE時使用除了RENAME以外的選項,則MySQL會建立一個臨時資料表。即使資料並不需要進行複製(例如當您更改欄位的名稱時),MySQL也會這麼操作。對於MyISAM資料表,您可以通過把myisam_sort_buffer_size系統變數設置到一個較高的值,來加快重新建立索引(該操作是變更過程中速度最慢的一部分)的速度。

·         要使用ALTER TABLE,您需要獲得資料表的ALTER, INSERT和CREATE權限。

·         IGNORE是MySQL相對於標準SQL的延伸。如果在新資料表中有重複關鍵字,或者當STRICT模式啟動後出現警告,則使用IGNORE控制ALTER TABLE的運行。如果沒有指定IGNORE,當重複關鍵字錯誤發生時,複製操作被放棄,返回前一步驟。如果指定了IGNORE,則對於有重複關鍵字的行,只使用第一行,其它有衝突的行被刪除。並且,對錯誤值進行修正,使之盡量接近正確值。

·         您可以在一個ALTER TABLE語句裡寫入多個ADD, ALTER, DROP和CHANGE子句,中間用逗號分開。這是MySQL相對於標準SQL的延伸。在標準SQL中,每個ALTER TABLE語句中每個子句只允許使用一次。例如,在一個語句中取消多個欄位:

·                >  t2 DROP COLUMN c, DROP COLUMN d;

·         CHANGE col_name, DROP col_name和DROP INDEX是MySQL相對於標準SQL的延伸。

·         MODIFY是Oracle對ALTER TABLE的延伸。

·         COLUMN只是自選項目,可以忽略。

·         如果您使用ALTER TABLE tbl_name RENAME TO new_tbl_name並且沒有其它選項,則MySQL只對與table tbl_name相對應的檔案進行重命名。不需要建立一個臨時資料表。(您也可以使用RENAME TABLE語句對資料表進行重命名。請參見13.1.9節,「RENAME TABLE語法」。)

·         column_definition子句使用與CREATE TABLE中的ADD和CHANGE子句相同的語法。注意,此語法包括欄位名稱,而不只是欄位型態。請參見13.1.5節,「CREATE TABLE語法」

·         您可以使用CHANGE old_col_name column_definition子句對欄位進行重命名。重命名時,需給定舊的和新的欄位名稱和欄位當前的型態。例如:要把一個INTEGER列的名稱從a變更到b,您需要如下操作:

·                mysql> ALTER TABLE t1 CHANGE a b INTEGER;

如果您想要更改欄位的型態而不是名稱, CHANGE語法仍然要求舊的和新的欄位名稱,即使舊的和新的欄位名稱是一樣的。例如:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

您也可以使用MODIFY來改變欄位的型態,此時不需要重命名:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

·         如果您使用CHANGE或MODITY縮短欄位長時,欄位中存在有索引,並且縮短後的欄位長小於索引長度,則MySQL會自動縮短索引的長度。

·         當您使用CHANGE或MODIFY更改欄位的型態時,MySQL會盡量把原有的欄位值轉化為新的型態。

·         您可以使用FIRST或AFTER col_name在一個資料表行中的某個特定位置新增欄位。預設把欄位新增到最後。您也可以在CHANGE或MODIFY語句中使用FIRST和AFTER。

·         AFTER COLUMN用於指定欄位的新預設值,或刪除舊的預設值。如果舊的預設值被刪除同時欄位值為NULL,則新的預設值為NULL。如果欄位值不能為NULL,MySQL會指定一個預設值,請參見13.1.5節,「CREATE TABLE語法」

·         DROP INDEX用於取消索引。這是MySQL相對於標準SQL的延伸。請參見13.1.7節,「DROP INDEX語法」

·         如果欄位從資料表中被取消了,則這些欄位也從相應的索引中被取消。如果組成一個索引的所有欄位均被取消,則該索引也被取消。

·         如果一個資料表只包含一欄位,則此欄位不能被取消。如果您想要取消資料表,應使用DROP TABLE。

·         DROP PRIMAY DEY用於取消主索引。註釋:在MySQL較早的版本中,如果沒有主索引,則DROP PRIMARY KEY會取消資料表中的第一個UNIQUE索引。在MySQL 5.1中不會出現這種情況。如果在MySQL 5.1中對沒有主鍵的資料表使用DROP PRIMARY KEY,則會出現錯誤訊息。

如果您向資料表中新增UNIQUE KEY或PRIMARY KEY,則UNIQUE KEY或PRIMARY KEY會被儲存在非唯一索引之前,這樣MySQL就可以盡早地檢查出重複關鍵字。

·         ORDER BY用於在建立新資料表時,讓各行按一定的順序排列。注意,在插入和刪除後,資料表不會仍保持此順序。當您知道多數情況下您會按照特定的順序查詢各行時,可以使用這個選項;在對資料表進行了大的改動後,通過使用此選項,您可以提高查詢效率。在有些情況下,如果資料表按欄位排序,對於MySQL來說,排序可能會更簡單。

·         如果您對一個MyISAM資料表使用ALTER TABLE,則所有非唯一索引會被建立到一個單獨的批裡(和REPAIR TABLE相同)。當您有許多索引時,這樣做可以使ALTER TABLE的速度更快。

這項功能可以明確激活。ALTER TABLE…DISABLE KEYS讓MySQL停止更新MyISAM資料表中的非唯一索引。然後使用ALTER TABLE … ENABLE KEYS重新建立丟失的索引。進行此操作時,MySQL採用一種特殊的算法,比一個接一個地插入關鍵字要快很多。因此,在進行成批插入操作前先使關鍵字禁用可以大大地加快速度。使用ALTER TABLE … DISABLE KEYS除了需要獲得以前提到的權限以外,還需要獲得INDEX權限。

·         Innodb儲存引擎支援FOREIGN KEY和REFERENCES子句。Innodb儲存引擎執行ADD [CONSTRAINT [symbol]] FOREIGN KEY (…) REFERENCES … (…)。請參見15.2.6.4節,「FOREIGN KEY約束」。對於其它儲存引擎,這些子句會被分析,但是會被忽略。對於所有的儲存引擎,CHECK子句會被分析,但是會被忽略。請參見13.1.5節,「CREATE TABLE語法」。接受這些子句但又忽略子句的原因是為了提高相容性,以便更容易地從其它SQL伺服器中導入代碼,並運行應用程式,建立帶參考資料的資料表。請參見1.8.5節,「MySQL與標準SQL的差別」

·         InnoDB支援使用ALTER TABLE來取消外部鍵:

·                ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

要瞭解更多訊息,請參見15.2.6.4節,「FOREIGN KEY約束」

·         ALTER TABLE忽略DATA DIRECTORY和INDEX DIRECTORY資料表選項。

·         如果您想要把資料表預設的字元編碼和所有字元欄位(CHAR, VARCHAR, TEXT)改為新的字元編碼,應使用如下語句:

·                ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

警告:前面的操作轉換了字元編碼之間的欄位型態。如果您有一欄位使用一種字元編碼(如latin1),但是儲存的值實際上使用了其它的字元編碼(如utf8),這種情況不是您想要的。此時,您必須對這樣的欄位進行以下操作。

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

這種方法能夠實現此功能的原因是,當您轉換到BLOB欄位或從BLOB欄位轉換過來時,並沒有發生轉換。

如果您指定CONVERT TO CHARACTER SET為二進制,則TEXT欄位被轉換到相應的二進制字串型態(BINARY, VARBINARY, BLOB)。這意味著這些欄位將不再有字元編碼,接下來的CONVERT TO操作也將不適用於這些欄位。

要僅僅改變一個資料表的預設字元編碼,應使用此語句:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

詞語DEFAULT為自選項。如果您在向資料表中新增一個新欄位時(例如,使用ALTER TABLE…ADD column)沒有指定字元編碼,則此時使用的字元編碼為預設字元編碼。

警告:ALTER TABLE…DEFAULT CHARACTER SET和ALTER TABLE…CHARACTER SET是等價的,只用於更改預設的資料表字元編碼。

·         如果InnoDB資料表在建立時,使用了.ibd檔案中的自己的資料表空間,則這樣的檔案可以被刪除和導入。使用此語句刪除.ibd檔案:

·                ALTER TABLE tbl_name DISCARD TABLESPACE;

此語句用於刪除當前的.ibd檔案,所以應首先確認您有一個備份。如果在資料表空間被刪除後嘗試打開資料表格,則會出現錯誤。

要把備份的.ibd檔案還原到資料表中,需把此檔案複製到資料庫目錄中,然後書寫此語句:

ALTER TABLE tbl_name IMPORT TABLESPACE;

15.2.6.6節,「使用按資料表的資料表空間」

·         使用mysql_info() C API函數,您可以瞭解有多少記錄已被複製,以及(當使用IGNORE時)有多少記錄由於重複關鍵字的原因已被刪除。請參見25.2.3.34節,「mysql_info()」

·         ALTER TABLE也可以用於對帶分區的資料表進行重新分區,功能包括新增、取消、合併和拆分各分區,還可以用於進行分區維護。

對帶分區的資料表使用partition_options子句和ALTER TABLE可以對資料表進行重新分區,使用時依據partition_options定義的分區方法。本子句以PARTITION BY為開頭,然後使用與用於CREATE TABLE的partition_options子句一樣的語法和規則(要瞭解詳細訊息,請參見13.1.5節,「CREATE TABLE語法」)。註釋:MySQL 5.1伺服器目前接受此語法,但是不實際執行;等MySQL 5.1開發出來後,將執行此語法。

用於ALTER TABLE ADD PARTITION的partition_definition子句支援用於CREATE TABLE語句的partition_definition子句的同樣名稱的選項。(要瞭解語法和介紹,請參見13.1.5節,「CREATE TABLE語法」。)例如,假設您有一個按照以下方式建立的帶分區的資料表:

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

您可以在資料表中增加一個新的分區p3,該分區用於儲存小於2002的值。新增方法如下:

ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);

註釋:您不能使用ALTER TABLE向一個沒有進行分區的資料表新增分區。

DROP PARTITION用於取消一個或多個RANGE或LIST分區。此命令不能用於HASH或KEY 分區;用於這兩個分區時,應使用COALESCE PARTITION(見後)。如果被取消的分區其名稱欄位於partition_names清單中,則儲存在此分區中的資料也被取消。例如,如果以前已定義的資料表t1,您可以採用如下方法取消名稱為p0和p1的分區:

ALTER TABLE DROP PARTITION p0, p1;

ADD PARTITION和DROP PARTITION目前不支援IF [NOT] EXISTS。也不可能對一個分區或一個已分區的資料表進行重命名。如果您希望對一個分區進行重命名,您必須取消分區,再重新建立;如果您希望對一個已分區的資料表進行重新命名,您必須取消所有分區,然後對資料表進行重命名,再新增被取消的分區。

COALESCE PARTITION可以用於使用HASH或KEY進行分區的資料表,以便使用number來減少分區的數目。例如,假設您使用下列方法建立了資料表t2:

CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH(YEAR(started))
PARTITIONS (6);

您可以使用以下命令,把t2使用的分區的數目由6個減少到4個:

ALTER TABLE t2 COALESCE PARTITION 2;

包含在最後一個number分區中的資料將被合併到其餘的分區中。在此情況下,分區4和分區5將被合併到前4個分區中(編號為0、1、2和3的分區)。

如果要更改部分分區,但不更改所有的分區,您可以使用REORGANIZE PARTITION。這個命令有多種使用方法:

o        把多個分區合併為一個分區。通過把多個分區的名稱列入partition_names清單,並為partition_definition提供一個單一的定義,可以實現這個功能。

o        把一個原有的分區拆分為多個分區。通過為partition_names命名一個分區,並提供多個partition_definitions,可以實現這個功能。

o        更改使用VALUES LESS THAN定義的分區子集的範圍或更改使用VALUES IN定義的分區子集的值清單。

註釋:對於沒有明確命名的分區,MySQL會自動提供預設名稱p0, p1, p2等。

要瞭解有關ALTER TALBE…REORANIZE PARTITION命令的詳細訊息,請參見18.3節,「分區管理」

·         多個附加子句用於提供分區維護和修補功能。這些功能與用於非分區資料表的功能類似。這些功能由CHECK TABLE和REPAIR TABLE等命令(這些命令不支援用於分區資料表)執行。這些子句包括ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION和REPAIR PARTITION.每個選項均為一個partition_names子句,包括一個或多個分區名稱。需要更改的資料表中必須已存在這些分區。多個分區名稱用逗號分隔。要瞭解更多訊息,或要瞭解舉例說明,請參見18.3.3節,「分區維護」

以下例子展示了ALTER TABLE的使用。首先展示資料表t1。資料表t1採用如下方法建立:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

把資料表t1重新命名為t2:

mysql> ALTER TABLE t1 RENAME t2;

把欄位a從INTERGER更改為TINYINT NOT NULL(名稱保持不變),並把欄位b從CHAR(10)更改為CHAR(20),同時把欄位b重新命名為欄位c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

新增一個新的TIMESTAMP欄位,名稱為d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

在欄位d和欄位a中新增索引:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);

刪除欄位c:

mysql> ALTER TABLE t2 DROP COLUMN c;

新增一個新的AUTO_INCREMENT整數欄位,名稱為c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     ADD PRIMARY KEY (c);

注意我們為c編製了索引(作為PRIMARY KEY),因為AUTO_INCREMENT欄位必須編製索引。同時我們定義c為NOT NULL,因為主鍵欄位不能為NULL。

當您新增一個AUTO_INCREMENT欄位時,欄位值被自動地按序號填入。對於MyISAM資料表,您可以在ALTER TABLE之前執行SET INSERT_ID=value來設置第一個序號,也可以使用AUTO_INCREMENT=value資料表選項來設置。請參見13.5.3節,「SET語法」

如果值大於AUTO_INCREMENT欄位中的最大值,則您可以使用用於InnoDB資料表的ALTER TALBE…AUTO_INCREMENT=value資料表選項,來為新行設置序號。如果值小於欄位中當前的最大值,不會出現錯誤訊息,當前的序列值也不改變。

使用MyISAM資料表時,如果您不更改AUTO_INCREMENT欄位,則序列號不受影響。如果您取消一個AUTO_INCREMENT欄位,然後新增另一個AUTO_INCREMENT欄位,則序號重新排列,從1開始。

A.7.1節,「與ALTER TABLE有關的問題」

2013-08-21_111606

這是MySQL 5實力養成暨評量裡的3-47.『SQL語法中,哪一個符號代表單一長度的任意字元?』

答案:(C) _

也是粗心題,因為看成是任意長度。

馬上成為工作達人的Fans

About ㄚ琪

工作達人Fun Taiwan的創辦者及總編,可以在這裡更認識他。

發表迴響

你的電子郵件位址並不會被公開。 Required fields are marked *

*

Scroll To Top

 好吃、好玩、旅遊、試吃、試用、邀稿、SEO

歡迎洽詢:flylinux@gmail.com

➨ 國外美食旅遊  日本東京自由行  美國猶他加州自由行  韓國釜山首爾慶州自由行 │ 泰國馬來西亞新加坡自由行  澳門廣東香港自由行日本九州奈良京阪自由行 │澳洲墨爾本坎培拉雪梨自由行 │中國北京自由行 

紅屋頂加級大阪難波飯店 (Red Roof Plus Osaka Namba) 日本大阪紅屋頂加級大阪難波飯店 (Red Roof Plus Osaka Namba)激推