版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認(rèn)領(lǐng)
文檔簡介
1、第六章,表,課程目標(biāo),創(chuàng)建表修改表重命名表刪除表,1. CREATE TABLE創(chuàng)建新表,使用CREATE TABLE可以創(chuàng)建一個新表,這一語句可以分為兩種寫法:一種用于直接創(chuàng)建新表;另一種用于參照其它表的定義創(chuàng)建一個新表。,1. CREATE TABLE創(chuàng)建新表,以下寫法用于直接創(chuàng)建新表:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名[(列定義1,列定義2,...,[
2、表級約束])][表選項][select表達(dá)式]其中列定義的基本寫法為:列名 列的類型,1. CREATE TABLE創(chuàng)建新表,以下代碼用于創(chuàng)建一個名為“student”的表,該表有兩列:ID:INT類型,用于存儲學(xué)生的編號;NAME:CHAR(8)類型,用于存儲學(xué)生的姓名。表的創(chuàng)建代碼如下:mysql> create table student -> ( -> ID int, -
3、> NAME char(8) -> );Query OK, 0 rows affected (0.09 sec),1. CREATE TABLE創(chuàng)建新表,MySQL將每一個表都單獨保存在一個“.frm”格式的文件中,該文件存儲在相應(yīng)的數(shù)據(jù)庫目錄中。表的存儲引擎也可能會創(chuàng)建其它文件,如對于MyISAM類型的表,存儲引擎會自動創(chuàng)建數(shù)據(jù)和索引文件。,1. CREATE TABLE創(chuàng)建新表,創(chuàng)建臨時表在創(chuàng)建表格時,可
4、以使用TEMPORARY關(guān)鍵詞使當(dāng)前建立的表為臨時表。臨時表只能在當(dāng)前連接中使用,當(dāng)連接關(guān)閉時,臨時表也被自動刪除。這意味著兩個不同的連接可以創(chuàng)建兩個名稱相同的臨時表,這兩個臨時表不會互相沖突,也不與原有的同名的非臨時表沖突。,1. CREATE TABLE創(chuàng)建新表,創(chuàng)建臨時表如果臨時表的名稱與數(shù)據(jù)庫中現(xiàn)有的非臨時表名稱相同,則非臨時表會被隱藏,直到該臨時表被刪除為止。創(chuàng)建臨時表要求當(dāng)前帳戶擁有CREATE TEMPORARY T
5、ABLES權(quán)限。,1. CREATE TABLE創(chuàng)建新表,避免創(chuàng)建已經(jīng)存在的表如果表已存在,則使用關(guān)鍵詞IF NOT EXISTS可以防止發(fā)生錯誤。注意,原有表的結(jié)構(gòu)與CREATE TABLE語句中表示的表的結(jié)構(gòu)是否相同,這一點沒有驗證。注釋:如果在CREATE TABLE...SELECT語句中使用IF NOT EXISTS,則不論表是否已存在,由SELECT部分選擇的記錄都會被插入。,1. CREATE TABLE創(chuàng)建新表,是
6、否允許列為空值在列定義中,可以加入以下選項以指明某一列是否允許出現(xiàn)空值:NULL:允許出現(xiàn)空值;NOT NULL:不允許出現(xiàn)空值。,1. CREATE TABLE創(chuàng)建新表,以下代碼在創(chuàng)建“student”表時,不允許學(xué)生的編號(ID)和姓名(NAME)為空,但允許住址(ADDR)為空。mysql> create table student -> ( -> ID int NOT NULL,
7、 -> NAME char(8) NOT NULL, -> ADDR char(60) NULL -> );Query OK, 0 rows affected (0.09 sec),1. CREATE TABLE創(chuàng)建新表,指定列的默認(rèn)值在列定義中,可以加入以下選項以指明某一列的默認(rèn)值:DEFAULT 默認(rèn)值,1. CREATE TABLE創(chuàng)建新表,默認(rèn)值必須為常數(shù),不能是函數(shù)或表達(dá)式,例如,一
8、個日期列的默認(rèn)值不能被設(shè)置為一個函數(shù),如NOW()或CURRENT_DATE。這里有一個特例,可以對TIMESTAMP列指定CURRENT_TIMESTAMP為默認(rèn)值。BLOB和TEXT類型的列不能指定默認(rèn)值。,1. CREATE TABLE創(chuàng)建新表,使列值自動增加如果一個列是整數(shù)類型,則可以使用“AUTO_INCREMENT”關(guān)鍵字指定該列的值自動增加。使用自動增加可以很方便的在表中的某一列上,為每一條記錄建立不同的列值,從而根
9、據(jù)該列的值可以唯一的確定表中的一條記錄。,1. CREATE TABLE創(chuàng)建新表,當(dāng)指定某一列為自動增加時,向該列插入一個NULL值(建議)或0時,該列會被自動設(shè)置為比上一次插入時更大的值,通常情況下依次增加1。也就是說,新增加的列值總是當(dāng)前表中該列的最大值。如果新增加的記錄是表中的第一條記錄,則該值為1。,1. CREATE TABLE創(chuàng)建新表,指定列的字符集如果某一列是字符列,則其定義可以包括一個CHARACTER SET屬性
10、,用來指定字符集和校對規(guī)則??梢詫HARACTER SET縮寫為CHARSET。,1. CREATE TABLE創(chuàng)建新表,為列添加注釋為了記錄表中某些列的作用,可以在建表時將這些列的用法添加列的注釋中,添加注釋使用COMMENT關(guān)鍵字??梢酝ㄟ^SHOW CREATE TABLE或SHOW FULL COLUMNS語句來查看列的注釋。,1. CREATE TABLE創(chuàng)建新表,設(shè)置主鍵主鍵就好比是表中每一條記錄的身份,一張表中不能
11、出現(xiàn)兩條主鍵相同的記錄,換言之,只要主鍵的值確定了,就唯一的確定了表中的一條記錄。被設(shè)置為主鍵的列同時會被強制設(shè)置為NOT NULL。一張表只能設(shè)置一個主鍵,主鍵可以是一個列,也可以是多個列的組合。,1. CREATE TABLE創(chuàng)建新表,設(shè)置主鍵將一個列設(shè)置為主鍵的方法是,在列的定義中使用“[PRIMARY] KEY”子句。以下代碼將“student”表的ID列設(shè)置為主鍵。Create table student(ID in
12、t KEY,NAME char(8)),1. CREATE TABLE創(chuàng)建新表,要將主鍵設(shè)置為多個列的組合,必須使用列級約束PRIMARY KEY子句。以下代碼演示了將表grade(成績)的列SID(學(xué)號),CID(課程號)設(shè)置為主鍵。create table grade(sid int,cid int,grade int,primary key (sid,cid)),1. CREATE TABLE創(chuàng)建新表,使列值
13、不重復(fù)在表中輸入數(shù)據(jù)時,有時需要某一列的內(nèi)容不含重復(fù)內(nèi)容,如書藉的出版號,此時可以使用UNIQUE [KEY]關(guān)鍵字來防止列值重復(fù)。如果在添加新行時插入的列值在表中已存在,則會出現(xiàn)錯誤。例外情況是,如果索引中的一個列允許包含NULL值,NULL值可以重復(fù)出現(xiàn)。,1. CREATE TABLE創(chuàng)建新表,使用索引可以使用如下方法在表中指定索引:INDEX 索引名 索引類型 (索引列1,索引列2,…)索引用于加速表的查詢速度,有
14、關(guān)索引的詳細(xì)信息可參考索引的管理。,1. CREATE TABLE創(chuàng)建新表,SERIAL實際使用中,經(jīng)常需要將一個列設(shè)置為“BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE”屬性,為便于進行這一操作,可以使用關(guān)鍵字“SERIAL”來替代這段很長的代碼。,1. CREATE TABLE創(chuàng)建新表,外鍵約束外鍵約束是指當(dāng)前表中的某些列與其它參考表中的某些列存在著參照關(guān)系,在當(dāng)前表中的列的值必
15、須已經(jīng)出現(xiàn)在參考表的對應(yīng)列中。在列定義和表級約束中都可以指定表的外鍵約束。,1. CREATE TABLE創(chuàng)建新表,外鍵定義服從下列情況: 所有tables必須是InnoDB型,它們不能是臨時表。 在引用表中,必須有一個索引,外鍵列以同樣的順序被列在其中作為第一列。這樣一個索引如果不存在,它必須在引用表里被自動創(chuàng)建。 在引用表中,必須有一個索引,被引用的列以同樣的順序被列在其中作為第一列。 不支持對外鍵列的索引前綴。這樣的后果
16、之一是BLOB和TEXT列不被包括在一個外鍵中,這是因為對這些列的索引必須總是包含一個前綴長度。 如果CONSTRAINT symbol被給出,它在數(shù)據(jù)庫里必須是唯一的。如果它沒有被給出,InnoDB自動創(chuàng)建這個名字。,1. CREATE TABLE創(chuàng)建新表,表選項表選項用于對表進行優(yōu)化,以下介紹表選項的含義。ENGINE和TYPEENGINE和TYPE選項用于為表指定存儲引擎,ENGINE是首選的選項名稱。ENGINE和TY
17、PE選項可參考MySQL備份與恢復(fù)中相關(guān)內(nèi)容。,1. CREATE TABLE創(chuàng)建新表,表選項如果被指定的存儲引擎無法利用,則MySQL使用MyISAM代替。例如,一個表定義包括ENGINE=BDB選項,但是MySQL服務(wù)器不支持BDB表,則表被創(chuàng)建為MyISAM表。這樣,如果在主機上有事務(wù)表,但在從屬機上創(chuàng)建的是非交互式表(以加快速度)時,可以進行復(fù)制設(shè)置。在MySQL 5.1中,如果沒有遵守存儲引擎規(guī)約,則會出現(xiàn)警告。,1.
18、CREATE TABLE創(chuàng)建新表,AUTO_INCREMENT 表的初始AUTO_INCREMENT值。在MySQL 5.1中,本選項只適用于MyISAM和MEMORY表。InnoDB也支持本選項。如果引擎不支持AUTO_INCREMENT表選項,則要設(shè)置引擎的第一個auto-increment值,需插入一個“假”行。該行的值比創(chuàng)建表后的值小一,然后刪除該假行。,1. CREATE TABLE創(chuàng)建新表,AVG_ROW_LENGTH
19、表中平均行長度的近似值。只需要對含尺寸可變的記錄的大型表進行此項設(shè)置。[DEFAULT] CHARACTER SET 用于為表指定一個默認(rèn)字符集。CHARSET是CHARACTER SET的同義詞。COLLATE 用于為表指定一個默認(rèn)整序。,1. CREATE TABLE創(chuàng)建新表,CHECKSUM如果希望MySQL隨時對所有行進行實時檢驗求和(也就是,表變更后,MySQL自動更新檢驗求和),則應(yīng)把此項設(shè)置為1。這樣做,表的
20、更新速度會略微慢些,但是更容易尋找到受損的表。CHECKSUM TABLE語句用于報告檢驗求和(僅限于MyISAM)。COMMENT 表的注釋,最長60個字符。,1. CREATE TABLE創(chuàng)建新表,CONNECTION FEDERATED表的連接字符串。( 注釋:較早版本的MySQL使用COMMENT選項用于連接字符串。MAX_ROWS 打算儲存在表中的行數(shù)目的最大值。這不是一個硬性限值,而更像一個指示語句,指示出表必須
21、能存儲至少這么多行。MIN_ROWS 打算存儲在表中的行數(shù)目的最小值。,1. CREATE TABLE創(chuàng)建新表,PACK_KEYS 如果希望索引更小,則把此選項設(shè)置為1。這樣做通常使更新速度變慢,同時閱讀速度加快。把選項設(shè)置為0可以取消所有的關(guān)鍵字壓縮。把此選項設(shè)置為DEFAULT時,存儲引擎只壓縮長的CHAR或VARCHAR列(僅限于MyISAM)。如果不使用PACK_KEYS,則默認(rèn)操作是只壓縮字符串,但不壓縮數(shù)字。如果使
22、用PACK_KEYS=1,則對數(shù)字也進行壓縮。,1. CREATE TABLE創(chuàng)建新表,PASSWORD 使用密碼對.frm文件加密。在標(biāo)準(zhǔn)MySQL版本中,本選項不起任何作用。DELAY_KEY_WRITE 如果想要延遲對關(guān)鍵字的更新,等到表關(guān)閉后再更新,則把此項設(shè)置為1(僅限于MyISAM)。,1. CREATE TABLE創(chuàng)建新表,ROW_FORMAT 定義各行應(yīng)如何儲存。當(dāng)前,此選項只適用于MyISAM表。對于靜態(tài)行
23、或長度可變行,此選項值可以為FIXED或DYNAMIC。myisampack用于把類型設(shè)置為COMPRESSED。在默認(rèn)情況下,InnoDB記錄以壓縮格式存儲(ROW_FORMAT=COMPACT)。通過指定ROW_FORMAT=REDUNDANT,仍然可以申請用于較早版本的MySQL中的非壓縮格式。,1. CREATE TABLE創(chuàng)建新表,RAID_TYPE 在MySQL 5.0中,RAID支持被刪除了。 UNION 當(dāng)想要
24、把一組相同的表當(dāng)作一個表使用時,采用UNION。UNION僅適用于MERGE表。對于映射到一個MERGE表上的表,必須擁有SELECT, UPDATE和DELETE權(quán)限。,1. CREATE TABLE創(chuàng)建新表,INSERT_METHOD 如果希望在MERGE表中插入數(shù)據(jù),必須用INSERT_METHOD指定應(yīng)插入行的表。INSERT_METHOD選項僅用于MERGE表。使用FIRST或LAST把行插入到第一個或最后一個表中;或者
25、使用NO,阻止插入行。DATA DIRECTORY, INDEX DIRECTORY 通過使用DATA DIRECTORY='directory'或INDEX DIRECTORY='directory',可以指定MyISAM存儲引擎放置表格數(shù)據(jù)文件和索引文件的位置。注意,目錄應(yīng)是通向目錄的完整路徑(不是相對路徑)。,1. CREATE TABLE創(chuàng)建新表,在一個表的基礎(chǔ)上創(chuàng)建表可以在CREATE
26、TABLE語句的末尾添加一個SELECT語句,在一個表的基礎(chǔ)上創(chuàng)建表。CREATE TABLE 新表名SELECT * FROM 老表名;MySQL會對SELECT中的所有項創(chuàng)建新列。,1. CREATE TABLE創(chuàng)建新表,參照其它表的定義創(chuàng)建一個新表以下寫法用于參照其它表的定義創(chuàng)建一個新表:CREATE [TEMPORARY] TABLE[IF NOT EXISTS] 表名LIKE 參照表的表名,2. ALTER T
27、ABLE修改表,ALTER [IGNORE] TABLE 表名修改項1 [, 修改項2] ... 其中,修改項寫法如下:ADD [COLUMN] 列定義 [FIRST | AFTER 列名] | ADD [COLUMN] (列定義1,列定義2) | ADD INDEX [索引名] [索引類型] (索引列名,...) | ADD [CONSTRAINT [約束名]] PRIMARY KEY [索引類型]
28、(索引列名,...) | ADD [CONSTRAINT [約束名]] UNIQUE [索引名] [索引類型] (索引列名,...),2. ALTER TABLE修改表,| ADD [FULLTEXT|SPATIAL] [索引名] (索引列名,...) | ADD [CONSTRAINT [約束名]] FOREIGN KEY [索引名] (索引列名,...) [外鍵約束定義]
29、 | ALTER [COLUMN] 列名 {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] 舊列名 列定義 [FIRST|AFTER 列名] | MODIFY [COLUMN] 列定義 [FIRST | AFTER 列名] | DROP [COLUMN] 列名 | DROP PRIMARY KEY | DROP INDEX 索引名,2.
30、ALTER TABLE修改表,| DROP FOREIGN KEY 外鍵約束名 | DISABLE KEYS | ENABLE KEYS | RENAME [TO] 新表名 | ORDER BY 列名 | CONVERT TO CHARACTER SET 字符集 [COLLATE 校對規(guī)則] | [DEFAULT] CHARACTER SET 字符集 [COLLATE 校對規(guī)則] | DISCARD TABL
31、ESPACE | IMPORT TABLESPACE | 表選項,2. ALTER TABLE修改表,ALTER TABLE用于更改原有表的結(jié)構(gòu)。例如,可以增加或刪減列,創(chuàng)建或取消索引,更改原有列的類型,或重新命名列或表。還可以更改表的評注和表的類型。允許進行的修改中,許多子句的語法與CREATE TABLE中的子句的語法相近。其中包括表選項修改,選項有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH
32、等??梢詤⒖肌癈REATE TABLE語法”。,2. ALTER TABLE修改表,IGNOREIGNORE是MySQL相對于標(biāo)準(zhǔn)SQL的擴展。如果在新表中有重復(fù)關(guān)鍵字,或者當(dāng)STRICT模式啟動后出現(xiàn)警告,則使用IGNORE控制ALTER TABLE的運行。如果沒有指定IGNORE,當(dāng)重復(fù)關(guān)鍵字錯誤發(fā)生時,復(fù)制操作被放棄,返回前一步驟。如果指定了IGNORE,則對于有重復(fù)關(guān)鍵字的行,只使用第一行,其它有沖突的行被刪除。并且,對錯誤值
33、進行修正,使之盡量接近正確值。,2. ALTER TABLE修改表,使用多個子句可以在一個ALTER TABLE語句里寫入多個ADD, ALTER, DROP和CHANGE子句,中間用逗號分開。這是MySQL相對于標(biāo)準(zhǔn)SQL的擴展。在標(biāo)準(zhǔn)SQL中,每個ALTER TABLE語句中每個子句只允許使用一次。例如,在一個語句中取消多個列:mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN
34、 d;,2. ALTER TABLE修改表,CHANGE和MODIFY可以使用“CHANGE 舊列名 新列定義”子句對列進行重命名。重命名時,需給定舊的和新的列名稱和列當(dāng)前的類型。例如:要把一個INTEGER列的名稱從a變更到b,需要如下操作:mysql> ALTER TABLE t1 CHANGE a b INTEGER;,2. ALTER TABLE修改表,FIRST和AFTER可以使用“FIRST 列名”或“AFTER
35、 列名”在一個表行中的某個特定位置添加列。默認(rèn)把列添加到最后。也可以在CHANGE或MODIFY語句中使用FIRST和AFTER。修改缺省值“ALTER ... SET DEFAULT”或“ALTER ... DROP DEFAULT”用于指定列的新默認(rèn)值,或刪除舊的默認(rèn)值。如果舊的默認(rèn)值被刪除同時列值為NULL,則新的默認(rèn)值為NULL。如果列值不能為NULL,MySQL會指定一個默認(rèn)值。,2. ALTER TABLE修改表,修改索
36、引DROP INDEX用于取消索引。這是MySQL相對于標(biāo)準(zhǔn)SQL的擴展。如果列從表中被取消了,則這些列也從相應(yīng)的索引中被取消。如果組成一個索引的所有列均被取消,則該索引也被取消。刪除列如果一個表只包含一列,則此列不能被取消。如果想要取消表,應(yīng)使用DROP TABLE。,2. ALTER TABLE修改表,DROP PRIMAY DEY刪除主鍵DROP PRIMAY DEY用于取消主索引。注釋:在MySQL較早的版本中,如果沒
37、有主索引,則DROP PRIMARY KEY會取消表中的第一個UNIQUE索引。在MySQL 5.1中不會出現(xiàn)這種情況。如果在MySQL 5.1中對沒有主鍵的表使用DROP PRIMARY KEY,則會出現(xiàn)錯誤信息。,2. ALTER TABLE修改表,添加唯一約束和主鍵約束如果向表中添加UNIQUE KEY或PRIMARY KEY,則UNIQUE KEY或PRIMARY KEY會被儲存在非唯一索引之前,這樣MySQL就可以盡早地檢查
38、出重復(fù)關(guān)鍵字。ORDER BYORDER BY用于在創(chuàng)建新表時,讓各行按一定的順序排列。注意,在插入和刪除后,表不會仍保持此順序。當(dāng)知道多數(shù)情況下會按照特定的順序查詢各行時,可以使用這個選項;在對表進行了大的改動后,通過使用此選項,可以提高查詢效率。在有些情況下,如果表按列排序,對于MySQL來說,排序可能會更簡單。,2. ALTER TABLE修改表,更改字符集如果想要把表默認(rèn)的字符集和所有字符列(CHAR, VARCHAR,
39、TEXT)改為新的字符集,應(yīng)使用如下語句:ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集;,3. RENAME TABLE重命名表,RENAME TABLE 表名TO 新表名[, 表名2 TO 新表名2] ...本語句用于對一個或多個表進行重命名,當(dāng)對表進行重命名時,其它線程不能讀取任何表。,3. RENAME TABLE重命名表,如果此語句用于對多個表進行重命名,則重命名操作從左至右進行。
40、如果想要交換兩個表的名稱,可以這樣做(假設(shè)不存在名稱為tmp_table的表):RENAME TABLE old_table TO tmp_table , new_table TO old_table , tmp_table TO new_table;,3. RENAME TABLE重命名表,只要兩個數(shù)據(jù)庫位于同一文件系統(tǒng)中,還可以對表進行重命名,把表從一個數(shù)據(jù)庫中移動到另一個數(shù)據(jù)庫中:RENAME TABLE current_db
41、.tbl_name TO other_db.tbl_name;,4. DROP TABLE刪除表,DROP [TEMPORARY] TABLE[IF EXISTS]表名1 [, 表名2] ... [RESTRICT | CASCADE]DROP TABLE用于刪除一個或多個表。必須有每個表的DROP權(quán)限。所有的表數(shù)據(jù)和表定義會被取消,所以使用本語句要小心!,4. DROP TABLE刪除表,TEMPORARY關(guān)鍵詞具有以下作用:
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 眾賞文庫僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
評論
0/150
提交評論