第3章MySQL數(shù)據(jù)庫(kù)和表

第3章

MySQL數(shù)據(jù)庫(kù)和表MySQL數(shù)據(jù)庫(kù)3.13.2MySQL表表記錄的操作3.33.4MySQL數(shù)據(jù)庫(kù)信息顯示3.1MySQL數(shù)據(jù)庫(kù)3.1.1創(chuàng)建數(shù)據(jù)庫(kù)使用CREATEDATABASE或CREATESCHEMA命令可以創(chuàng)建數(shù)據(jù)庫(kù)。語(yǔ)法格式：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]數(shù)據(jù)庫(kù)名[

選項(xiàng)

...]選項(xiàng)：[DEFAULT]CHARACTERSET字符集|[DEFAULT]COLLATE校對(duì)規(guī)則名3.1.1創(chuàng)建數(shù)據(jù)庫(kù)【例3.1】創(chuàng)建學(xué)生成績(jī)數(shù)據(jù)庫(kù)xscj。mysql>createdatabasexscj如果已經(jīng)創(chuàng)建了數(shù)據(jù)庫(kù)，（例如mytest）重復(fù)創(chuàng)建時(shí)系統(tǒng)會(huì)提示數(shù)據(jù)庫(kù)已經(jīng)存在，不能再創(chuàng)建。系統(tǒng)顯示錯(cuò)誤信息。使用IFNOTEXISTS選項(xiàng)從句可不顯示錯(cuò)誤信息。創(chuàng)建了數(shù)據(jù)庫(kù)之后使用USE命令可指定當(dāng)前數(shù)據(jù)庫(kù)。語(yǔ)法格式：USE數(shù)據(jù)庫(kù)名;例如：指定當(dāng)前數(shù)據(jù)庫(kù)為學(xué)生成績(jī)數(shù)據(jù)庫(kù)（xscj）。mysql>usexscj3.1.2修改數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)創(chuàng)建后，如果需要修改數(shù)據(jù)庫(kù)的參數(shù)，可以使用ALTERDATABASE命令。語(yǔ)法格式：ALTER{DATABASE|SCHEMA}[數(shù)據(jù)庫(kù)名]

選項(xiàng)

...選項(xiàng)：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校對(duì)規(guī)則名【例3.2】修改學(xué)生成績(jī)數(shù)據(jù)庫(kù)（xscj）默認(rèn)字符集和校對(duì)規(guī)則。3.1.3刪除數(shù)據(jù)庫(kù)已經(jīng)創(chuàng)建的數(shù)據(jù)庫(kù)需要?jiǎng)h除，使用DROPDATABASE命令。語(yǔ)法格式：DROPDATABASE[IFEXISTS]數(shù)據(jù)庫(kù)名還可以使用IFEXISTS子句，避免刪除不存在的數(shù)據(jù)庫(kù)時(shí)出現(xiàn)MySQL錯(cuò)誤信息。3.2MySQL表3.2.1創(chuàng)建表1．全新創(chuàng)建從頭創(chuàng)建一個(gè)全新的表，使用CREATETABLE命令。語(yǔ)法格式：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[([列定義]...|[表索引定義])][表選項(xiàng)][select語(yǔ)句];說(shuō)明：TEMPORARY：該關(guān)鍵字表示用CREATE命令新建的表為臨時(shí)表。IFNOTEXISTS：在創(chuàng)建表前加上一個(gè)判斷，只有該表目前尚不存在時(shí)才執(zhí)行CREATETABLE操作。列定義：包括列名、數(shù)據(jù)類型，可能還有一個(gè)空值聲明和一個(gè)完整性約束。表索引項(xiàng)定義：主要定義表的索引、主鍵、外鍵等，具體定義將在第5章中討論。select語(yǔ)句：用于在一個(gè)已有表的基礎(chǔ)上創(chuàng)建表。3.2.1創(chuàng)建表（1）“列定義”格式如下：列名

type[NOTNULL|NULL][DEFAULT默認(rèn)值][AUTO_INCREMENT][UNIQUE[KEY]|[PRIMARY]KEY][COMMENT'string'][參照定義]其中：列名：必須符合標(biāo)識(shí)符規(guī)則，長(zhǎng)度不能超過(guò)64個(gè)字符，而且在表中要唯一。type：列的數(shù)據(jù)類型，有的數(shù)據(jù)類型需要指明長(zhǎng)度n，并用括號(hào)括起，MySQL支持的數(shù)據(jù)類型在附錄C中介紹。AUTO_INCREMENT：設(shè)置自增屬性，只有整型列才能設(shè)置此屬性。NOTNULL|NULL：指定該列是否允許為空。如果不指定，則默認(rèn)為NULL。DEFAULT默認(rèn)值：為列指定默認(rèn)值，默認(rèn)值必須為一個(gè)常數(shù)。UNIQUEKEY|PRIMARYKEY：PRIMARYKEY和UNIQUEKEY都表示字段中的值是唯一的。COMMENT'string'：對(duì)于列的描述，string是描述的內(nèi)容。參照定義：指定參照的表和列“type”定義如下。3.2.1創(chuàng)建表（2）“表選項(xiàng)”定義如下：{ENGINE|TYPE}=engine_name /*存儲(chǔ)引擎*/|AUTO_INCREMENT=value /*初始值*/|AVG_ROW_LENGTH=value /*表的平均行長(zhǎng)度*/|[DEFAULT]CHARACTERSET字符集名

[COLLATE校對(duì)規(guī)則名] /*默認(rèn)字符集和校對(duì)*/|CHECKSUM={0|1} /*設(shè)置1表示求校驗(yàn)和*/|COMMENT='string' /*注釋*/|CONNECTION='connect_string' /*連接字符串*/|MAX_ROWS=value /*行的最大數(shù)*/|MIN_ROWS=value /*列的最小數(shù)*/|PACK_KEYS={0|1|DEFAULT}|PASSWORD='string' /*對(duì).frm文件加密*/|DELAY_KEY_WRITE={0|1} /*對(duì)關(guān)鍵字的更新*/|ROW_FORMAT={DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} /*定義各行應(yīng)如何儲(chǔ)存*/|UNION=(表名[,表名]...) /*表示哪個(gè)表應(yīng)該合并*/|INSERT_METHOD={NO|FIRST|LAST} /*是否執(zhí)行INSERT語(yǔ)句*/|DATADIRECTORY='absolutepathtodirectory‘ /*數(shù)據(jù)文件的路徑*/|INDEXDIRECTORY='absolutepathtodirectory' /*索引的路徑*/3.2.1創(chuàng)建表【例3.3】在學(xué)生成績(jī)數(shù)據(jù)庫(kù)（xscj）中也創(chuàng)建一個(gè)學(xué)生情況表，表名xs。輸入以下命令：USExscjCREATETABLExs(

學(xué)號(hào)

char(6) notnullprimarykey,

姓名

char(8) notnull,

專業(yè)名

char(10) null,

性別

tinyint(1) notnulldefault1,

出生日期

date notnull,

總學(xué)分

tinyint(1) null,

照片

blob null,

備注

text null)engine=innodb;3.2.1創(chuàng)建表用showtables命令顯示xscj數(shù)據(jù)庫(kù)中產(chǎn)生了學(xué)生（xs）表，用describexs命令可以顯示xs表的結(jié)構(gòu)。如圖3.1所示。3.2.1創(chuàng)建表2．復(fù)制現(xiàn)成的表除了全新創(chuàng)建，用戶也可直接復(fù)制數(shù)據(jù)庫(kù)中已有表的結(jié)構(gòu)和數(shù)據(jù)，用這種方式構(gòu)建一個(gè)表，十分方便、快捷。語(yǔ)法格式：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[()LIKE已有表名

[]]|[AS(表達(dá)式)];【例3.4】在mytest數(shù)據(jù)庫(kù)中，用復(fù)制的方式創(chuàng)建一個(gè)名為user_copy1的表，表結(jié)構(gòu)直接取自u(píng)ser表；另再創(chuàng)建一個(gè)名為user_copy2的表，其結(jié)構(gòu)和內(nèi)容（數(shù)據(jù)）都取自u(píng)ser表。（1）創(chuàng)建user_copy1表：USEmytestCREATETABLEuser_copy1LIKEuser;（2）創(chuàng)建user_copy2表：CREATETABLEuser_copy2AS(select*fromuser);3.2.1創(chuàng)建表執(zhí)行過(guò)程及結(jié)果，如圖3.2所示。3.2.1創(chuàng)建表查詢user_copy1表中沒(méi)有記錄，而user_copy2表中包含user表中所有記錄。如圖3.3所示。3.2.2修改表1．修改表結(jié)構(gòu)ALTERTABLE用于更改原有表的結(jié)構(gòu)。例如，可以增加（刪減）列、創(chuàng)建（取消）索引、更改原有列的類型、重新命名列或表，還可以更改表的評(píng)注和表的類型。語(yǔ)法格式：ALTER[IGNORE]TABLE表名

選項(xiàng)

...選項(xiàng)=：ADD[COLUMN]列定義

[FIRST|AFTER列名

] /*添加列*/ |ALTER[COLUMN]列名{SETDEFAULTliteral|DROPDEFAULT} /*修改默認(rèn)值*/|CHANGE[COLUMN]列名

原列名[FIRST|AFTER列名] /*列名重定義*/|MODIFY[COLUMN]列定義

[FIRST|AFTER列名] /*修改列數(shù)據(jù)類型*/|DROP[COLUMN]列名

/*刪除列*/|RENAME[TO]新表名

/*重命名該表*/|ORDERBY列名

/*排序*/|CONVERTTOCHARACTERSET字符集名

[COLLATE校對(duì)規(guī)則名] /*將字符集轉(zhuǎn)換為二進(jìn)制*/|[DEFAULT]CHARACTERSET字符集名

[COLLATE校對(duì)規(guī)則名] /*修改默認(rèn)字符集*/|表選項(xiàng)|列或表中索引項(xiàng)的增、刪、改（詳細(xì)見(jiàn)索引一章）3.2.2修改表說(shuō)明：IGNORE：是MySQL相對(duì)于標(biāo)準(zhǔn)SQL的擴(kuò)展。ADD[COLUMN]子句：向表中增加新列。FIRST|AFTER列名：表示在某列的前或后添加，不指定則添加到最后。ALTER[COLUMN]子句：修改表中指定列的默認(rèn)值。CHANGE[COLUMN]子句：修改列的名稱。MODIFY[COLUMN]子句：修改指定列的數(shù)據(jù)類型。DROP子句：從表中刪除列或約束。RENAME子句：修改該表的表名。ORDERBY子句：用于在創(chuàng)建新表時(shí)，讓各行按一定的順序排列。表選項(xiàng)：修改表選項(xiàng)，具體定義與CREATETABLE語(yǔ)句中一樣。3.2.2修改表【例3.5】在xscj數(shù)據(jù)庫(kù)的xs表中，增加“獎(jiǎng)學(xué)金等級(jí)”一列，并將表中的“姓名”列刪除。userxscjaltertablexsadd獎(jiǎng)學(xué)金等級(jí)

tinyintnull,dropcolumn姓名;執(zhí)行后，xs表的結(jié)構(gòu)如圖3.4所示。3.2.2修改表為了在后面演示表記錄操作的方便，此處完成后要及時(shí)地將xs表改回原樣，語(yǔ)句如下：altertablexsadd姓名

char(8)notnullafter學(xué)號(hào),dropcolumn獎(jiǎng)學(xué)金等級(jí);3.2.2修改表2．更改表名除了上面的ALTERTABLE命令，還可以直接用RENAMETABLE語(yǔ)句來(lái)更改表的名字。語(yǔ)法格式：RENAMETABLE老表名

TO新表名

...【例3.6】將mytest數(shù)據(jù)庫(kù)usera表重命名為user1，user_copy2表重命名為user2。renametableuseratouser1,user_copy2touser2;3.2.3刪除表需要?jiǎng)h除一個(gè)表時(shí)可以使用DROPTABLE語(yǔ)句。語(yǔ)法格式：DROP[TEMPORARY]TABLE[IFEXISTS]表名

...這個(gè)命令將表的描述、表的完整性約束、索引及和表相關(guān)的權(quán)限等一并刪除?！纠?.7】刪除表uesra。droptableifexistsusera;3.2.4MySQL表結(jié)構(gòu)的特點(diǎn)1．空值（NULL）的概念空值通常表示未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。若一個(gè)列允許為空值，則向表中輸入記錄值時(shí)可不為該列給出具體值；而一個(gè)列若不允許為空值，則在輸入時(shí)必須給出該列的具體值。2．列的標(biāo)志（IDENTITY）屬性對(duì)任何表都可創(chuàng)建包含系統(tǒng)所生成序號(hào)值的一個(gè)標(biāo)志列，該序號(hào)值唯一標(biāo)志表中的一列，可以作為鍵值。每個(gè)表只能有一個(gè)列設(shè)置為標(biāo)志屬性，該列只能是decimal、int、numeric、smallint、bigint或

tinyint數(shù)據(jù)類型。定義標(biāo)志屬性時(shí)，可指定其種子（即起始）值、增量值，二者的默認(rèn)值均為

1。系統(tǒng)自動(dòng)更新標(biāo)志列值，標(biāo)志列值不允許空值。3.2.4MySQL表結(jié)構(gòu)的特點(diǎn)3．隱含地改變列類型（1）長(zhǎng)度小于4的varchar被改變?yōu)閏har。（2）如果在一個(gè)表中的任何列有可變長(zhǎng)度，結(jié)果使整個(gè)行是變長(zhǎng)的。因此，如果一張表包含任何變長(zhǎng)的列（varchar、text或Blob），所有大于3個(gè)字符的char列被改變?yōu)関archar列。這在任何方面都不影響用戶如何使用列。（3）timestamp的顯示尺寸必須是偶數(shù)且在2～14的范圍內(nèi)。如果指定0顯示尺寸或比14大，尺寸被強(qiáng)制為14。從1～13范圍內(nèi)的奇數(shù)值尺寸被強(qiáng)制為下一個(gè)更大的偶數(shù)。（4）不能在一個(gè)timestamp列里面存儲(chǔ)一個(gè)NULL，將它設(shè)為NULL默認(rèn)為當(dāng)前的日期和時(shí)間。3.3表記錄的操作3.3.1插入記錄1．插入新記錄向表中插入全新的記錄用INSERT語(yǔ)句，語(yǔ)法格式：INSERT[LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE][INTO]表名

[(列名,...)]VALUES({expr|DEFAULT},...),(...),...|SET列名={expr|DEFAULT},...[ONDUPLICATEKEYUPDATE列名=expr,...]3.3.1插入記錄說(shuō)明：列名：需要插入數(shù)據(jù)的列名。VALUES子句：包含各列需要插入的數(shù)據(jù)清單，數(shù)據(jù)的順序要與列的順序相對(duì)應(yīng)。LOW_PRIORITY：可以使用在INSERT、DELETE和UPDATE等操作中，當(dāng)原有客戶端正在讀取數(shù)據(jù)時(shí)，延遲操作的執(zhí)行，直到?jīng)]有其他客戶端從表中讀取為止。DELAYED：若使用此關(guān)鍵字，則服務(wù)器會(huì)把待插入的行放到一個(gè)緩沖器中，而發(fā)送INSERTDELAYED語(yǔ)句的客戶端會(huì)繼續(xù)運(yùn)行。HIGH_PRIORITY：可以使用在SELECT和INSERT操作中，使操作優(yōu)先執(zhí)行。IGNORE：使用此關(guān)鍵字，在執(zhí)行語(yǔ)句時(shí)出現(xiàn)的錯(cuò)誤就會(huì)被當(dāng)作警告處理。ONDUPLICATEKEYUPDATE…：使用此選項(xiàng)插入行后，若導(dǎo)致UNIQUEKEY或PRIMARYKEY出現(xiàn)重復(fù)值，則根據(jù)UPDATE后的語(yǔ)句修改舊行（使用此選項(xiàng)時(shí)DELAYED被忽略）。SET子句：SET子句用于給列指定值，使用SET子句時(shí)表名的后面省略列名。3.3.1插入記錄【例3.8】向?qū)W生成績(jī)數(shù)據(jù)庫(kù)（xscj）的表xs（表中列包括學(xué)號(hào)、姓名、專業(yè)名、性別、出生日期、總學(xué)分、照片、備注）中插入如下一行：081101，王林，計(jì)算機(jī)，1，1994-02-10，50，NULL，NULL使用下列語(yǔ)句：usexscjinsertintoxsvalues('081101','王林','計(jì)算機(jī)',1,'1994-02-10',50,null,null);3.3.1插入記錄若表xs中專業(yè)的默認(rèn)值為“計(jì)算機(jī)”，照片、備注默認(rèn)值為NULL，插入上例那行數(shù)據(jù)也可以使用如下命令：insertintoxs(學(xué)號(hào),姓名,性別,出生日期,總學(xué)分)values('081101','王林',1,'1994-02-10',50);也與下面這個(gè)命令的效果相同：insertintoxsvalues('081101','王林',default,1,'1994-02-10',50,null,null);當(dāng)然，也可以使用SET子句來(lái)實(shí)現(xiàn)：insertintoxsset學(xué)號(hào)='081101',姓名='王林',專業(yè)=default,性別=1,出生日期='1994-02-10',總學(xué)分=50;執(zhí)行結(jié)果如圖3.5所示。3.3.1插入記錄2.從已有表中插入新記錄使用INSERTINTO…SELECT…，可以快速地從一個(gè)或多個(gè)已有的表記錄向表中插入多個(gè)行。語(yǔ)法格式如下：INSERT[LOW_PRIORITY|HIGH_PRIORITY][IGNORE][INTO]表名

[(列名,...)]SELECT語(yǔ)句[ONDUPLICATEKEYUPDATE列名=expr,...]3.3.1插入記錄【例3.9】將mytest數(shù)據(jù)庫(kù)user表記錄插入到user1表中。usermytestinsertintouser1select*fromuser;命令執(zhí)行前后的效果如圖3.6所示。

3.3.1插入記錄2．替換舊記錄REPLACE語(yǔ)句可以在插入數(shù)據(jù)之前將與新記錄沖突的舊記錄刪除，從而使新記錄能夠替換舊記錄，正常插入。REPLACE語(yǔ)句格式與INSERT相同?！纠?.10】若上例中的記錄行已經(jīng)插入，其中學(xué)號(hào)為主鍵（PRIMARYKEY），現(xiàn)在想再插入下列一行記錄：081101，劉華，通信工程，1，1995-03-08，48，NULL，NULL若直接使用INSERT語(yǔ)句，會(huì)產(chǎn)生如下錯(cuò)誤：使用REPLACE語(yǔ)句，則可以成功插入：3.3.1插入記錄3．插入圖片MySQL還支持圖片的插入，圖片一般可以以路徑的形式來(lái)存儲(chǔ)，即插入圖片可以采用插入圖片的存儲(chǔ)路徑的方式。當(dāng)然也可以直接插入圖片本身，只要用LOAD_FILE函數(shù)即可。3.3.1插入記錄【例3.11】向xs表中插入一行記錄：081102，程明，計(jì)算機(jī)，1，1995-02-01，50，picture.jpg，NULL其中，照片路徑為D:\IMAGE\picture.jpg。使用如下語(yǔ)句：insertintoxsvalues('081102','程明','計(jì)算機(jī)',1,'1995-02-01',50,'D:\IMAGE\picture.jpg',null);也可使用這個(gè)語(yǔ)句直接存儲(chǔ)圖片本身：insertintoxsvalues('081102','程明','計(jì)算機(jī)',1,'1995-02-01',50,load_file('D:\IMAGE\picture.jpg'),null);執(zhí)行結(jié)果為：3.3.2修改記錄1．修改單個(gè)表語(yǔ)法格式：UPDATE[LOW_PRIORITY][IGNORE]表名SET列名1=expr1[,列名2=expr2...][WHERE條件][ORDERBY...][LIMITrow_count]3.3.2修改記錄【例3.12】將學(xué)生成績(jī)數(shù)據(jù)庫(kù)（xscj）的學(xué)生（xs）表中的所有學(xué)生的總學(xué)分都增加10。將姓名為“劉華”的同學(xué)的備注填寫(xiě)為“輔修計(jì)算機(jī)專業(yè)”，學(xué)號(hào)改為“081250”。updatexsset總學(xué)分

=總學(xué)分+10;updatexsset學(xué)號(hào)

='081250',備注

='輔修計(jì)算機(jī)專業(yè)'where姓名

='劉華';select學(xué)號(hào),姓名,總學(xué)分,備注

fromxs;執(zhí)行結(jié)果為：3.3.2修改記錄2．修改多個(gè)表語(yǔ)法格式：UPDATE[LOW_PRIORITY][IGNORE]表名,表名...SET列名1=expr1[,列名2=expr2...][WHERE條件]3.3.2修改記錄【例3.13】mytest數(shù)據(jù)庫(kù)表user和表user2中都有兩個(gè)字段idint(11)，passwordvarchar(10)，其中id為主鍵。當(dāng)表user中id值與user2中id值相同時(shí)，將表user中對(duì)應(yīng)的password值修改為“11111111”，將表user2中對(duì)應(yīng)的password值改為“22222222”。usermytestupdateuser,user2setuser.password='11111111',user2.password='22222222'whereuser.id=user2.id;修改后的結(jié)果如圖3.7所示。3.3.3刪除記錄1．刪除滿足條件的行使用DELETE語(yǔ)句刪除表中滿足條件的記錄行。從單個(gè)表中刪除，語(yǔ)法格式：DELETE[LOW_PRIORITY][QUICK][IGNORE]FROM表名[WHERE條件][ORDERBY...][LIMITrow_count]說(shuō)明：QUICK修飾符：可以加快部分種類的刪除操作的速度。FROM子句：用于說(shuō)明從何處刪除數(shù)據(jù)，表名為要?jiǎng)h除數(shù)據(jù)的表名。WHERE子句：指定的刪除記錄條件。ORDERBY子句：各行按照子句中指定的順序進(jìn)行刪除，此子句只在與LIMIT聯(lián)用時(shí)才起作用。LIMIT子句：用于告知服務(wù)器，在控制命令被返回到客戶端前，被刪除的行的最大值。3.3.3刪除記錄【例3.14】刪除mytest數(shù)據(jù)庫(kù)中user2表“周何駿”記錄刪除。usemytestdeletefrompersonwhereusername='周何駿';或者：deletefromxswhereid=2;3.3.3刪除記錄2．從多個(gè)表中刪除行刪除操作若要在多個(gè)表中進(jìn)行，其語(yǔ)法格式如下：DELETE[LOW_PRIORITY][QUICK][IGNORE]表名[.*][,表名[.*]...]FROMtable_references[WHEREwhere_definition]或：DELETE[LOW_PRIORITY][QUICK][IGNORE] FROM表名[.*][,表名[.*]...] USINGtable_references [WHEREwhere_definition]3.3.3刪除記錄【例3.15】刪除user1中id值等于user的id值的所有行和user2中id值等于user的id值的所有行，使用如下語(yǔ)句：DELETEuser1,user2FROMuser1,user2,userWHEREuser1.id=user.idANDuser2.id=user.id;命令執(zhí)行結(jié)果如圖3.8所示。3.3.3刪除記錄3．清除表數(shù)據(jù)使用TRUNCATETABLE語(yǔ)句將刪除指定表中的所有數(shù)據(jù)，因此也稱其為清除表數(shù)據(jù)語(yǔ)句，其語(yǔ)法格式如下：TRUNCATETABLE表名3.4MySQL數(shù)據(jù)庫(kù)信息顯示1．SHOW語(yǔ)句SHOWtables或SH