MySQL數(shù)據(jù)庫實(shí)用教程-數(shù)據(jù)定義

1第3章數(shù)據(jù)定義3.1數(shù)據(jù)定義語言3.2創(chuàng)建MySQL數(shù)據(jù)庫3.3創(chuàng)建MySQL表3.4數(shù)據(jù)完整性約束MySQL數(shù)據(jù)庫實(shí)用教程MySQL數(shù)據(jù)庫實(shí)用教程23.1數(shù)據(jù)定義語言數(shù)據(jù)定義語言用于對數(shù)據(jù)庫及數(shù)據(jù)庫地各種對象進(jìn)行創(chuàng)建,刪除,修改等操作。數(shù)據(jù)庫對象主要包括表,默認(rèn)約束,規(guī)則,視圖,觸發(fā)器,存儲過程等。數(shù)據(jù)定義語言地主要SQL語句如下。（1）CREATE語句,用于創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫對象。不同數(shù)據(jù)庫對象,其CREATE語句地語法形式不同。（2）ALTER語句,用于修改數(shù)據(jù)庫或數(shù)據(jù)庫對象。不同數(shù)據(jù)庫對象,其ALTER語句地語法形式不同。（3）DROP語句,用于刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象。不同數(shù)據(jù)庫對象地DROP語句地語法形式不同MySQL數(shù)據(jù)庫實(shí)用教程33.2.1MySQL數(shù)據(jù)庫地基本概念安裝MySQL時(shí),生成了系統(tǒng)使用地?cái)?shù)據(jù)庫,包括information_schema,mysql,performance_schema與sys等。MySQL把有關(guān)數(shù)據(jù)庫管理系統(tǒng)自身地管理信息都保存在這幾個(gè)數(shù)據(jù)庫,如果刪除了它們,MySQL將不能正常工作,操作時(shí)要十分小心?？梢允褂肧HOWDATABASES命令查看已有地?cái)?shù)據(jù)庫。例3.1使用SHOWDATABASES命令查看MySQL地已有數(shù)據(jù)庫。mysql>SHOWDATABASES;執(zhí)行結(jié)果如下。3.2創(chuàng)建MySQL數(shù)據(jù)庫+-------------------------+|Database|+-------------------------+|information_schema||mysql||performance_schema||sys|+--------------------------+MySQL數(shù)據(jù)庫實(shí)用教程43.2創(chuàng)建MySQL數(shù)據(jù)庫3.2.2創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫使用CREATEDATABASE語句。

語法格式如下。

CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name];

說明如下?！裾Z句"[]"為可選語法項(xiàng),"{}"為必選語法項(xiàng),"|"分隔括號或大括號地語法項(xiàng),只能選擇其一項(xiàng)?！馾b_name:數(shù)據(jù)庫名稱?！馡FNOTEXISTS:在創(chuàng)建數(shù)據(jù)庫前進(jìn)行判斷,只有該數(shù)據(jù)庫目前尚不存在時(shí)才執(zhí)行CREATEDATABASE操作。MySQL數(shù)據(jù)庫實(shí)用教程5●CHARACTERSET:指定數(shù)據(jù)庫字符集?！馛OLLATE:指定字符集地校對規(guī)則?！馜EFAULT:指定默認(rèn)值。例3.2創(chuàng)建名稱為teaching地教學(xué)數(shù)據(jù)庫,該數(shù)據(jù)庫是本書地重要數(shù)據(jù)庫。在MySQL命令行客戶端輸入如下SQL語句:

mysql>CREATEDATABASEteaching;

執(zhí)行結(jié)果如下。

QueryOK,1rowaffected(0.05sec)

查看已有數(shù)據(jù)庫

mysql>SHOWDATABASES;

3.2創(chuàng)建MySQL數(shù)據(jù)庫MySQL數(shù)據(jù)庫實(shí)用教程6顯示結(jié)果如下。

+--------------------------+|Database|+--------------------------+|information_schema||mysql||performance_schema||teaching||sys|+---------------------------+

可以看出,數(shù)據(jù)庫列表包含了剛創(chuàng)建地?cái)?shù)據(jù)庫teaching。3.2創(chuàng)建MySQL數(shù)據(jù)庫MySQL數(shù)據(jù)庫實(shí)用教程73.2.3選擇數(shù)據(jù)庫選擇數(shù)據(jù)庫使用USE語句,其語法格式如下。

USEdb_name;例3.3選擇教學(xué)數(shù)據(jù)庫teaching為當(dāng)前數(shù)據(jù)庫。

mysql>USEteaching;

執(zhí)行結(jié)果如下。Databasechanged3.2.4修改數(shù)據(jù)庫修改數(shù)據(jù)庫使用ALTERDATABASE語句,其語法格式如下。

ALTER{DATABASE|SCHEMA}[db_name][DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name;3.2創(chuàng)建MySQL數(shù)據(jù)庫MySQL數(shù)據(jù)庫實(shí)用教程8

說明如下。

數(shù)據(jù)庫名稱可以省略,表示修改當(dāng)前(默認(rèn))數(shù)據(jù)庫。選項(xiàng)CHARACTERSET與COLLATE與創(chuàng)建數(shù)據(jù)庫語句相同。

例3.4修改數(shù)據(jù)庫teaching地默認(rèn)字符集與校對規(guī)則。

mysql>ALTERDATABASEteaching->DEFAULTCHARACTERSETgb2312->DEFAULTCOLLATEgb2312_chinese_ci;

執(zhí)行結(jié)果:

QueryOK,1rowaffected(0.06sec)3.2創(chuàng)建MySQL數(shù)據(jù)庫

MySQL數(shù)據(jù)庫實(shí)用教程93.2.5刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫使用DROPDATABASE語句。

語法格式:

DROP{DATABASE|SCHEMA}[IFEXISTS]db_name

說明:●db_name:指定要?jiǎng)h除地?cái)?shù)據(jù)庫名稱。●DROPDATABASE或DROPSCHEMA:該命令會刪除指定地整個(gè)數(shù)據(jù)庫,數(shù)據(jù)庫所有地表與所有數(shù)據(jù)也將被永久刪除,并不給出任何提示確認(rèn)地信息。因此,刪除數(shù)據(jù)庫要特別小心?！馡FEXISTS:使用該子句,可避免刪除不存在地?cái)?shù)據(jù)庫時(shí)出現(xiàn)MySQL錯(cuò)誤信息。3.2創(chuàng)建MySQL數(shù)據(jù)庫MySQL數(shù)據(jù)庫實(shí)用教程10例3.5刪除數(shù)據(jù)庫teaching。

mysql>DROPDATABASEteaching;

執(zhí)行結(jié)果如下。

QueryOK,0rowsaffected(0.02sec)

查看現(xiàn)有數(shù)據(jù)庫

mysql>SHOWDATABASES;

可以看到,由于數(shù)據(jù)庫teaching被刪除,數(shù)據(jù)庫列表已沒有名稱為teaching地?cái)?shù)據(jù)庫了。顯示結(jié)果如下。

+--------------------------+|Database|+--------------------------+|information_schema||mysql||performance_schema||sys|+--------------------------+4rowsinset(0.00sec)3.2創(chuàng)建MySQL數(shù)據(jù)庫113.3創(chuàng)建MySQL表3.3.1表地基本概念1.表與表結(jié)構(gòu)在工作與生活,表是經(jīng)常使用地一種表示數(shù)據(jù)及其關(guān)系地形式。在教學(xué)數(shù)據(jù)庫teaching,教師表teacher如表3.1所示。表3.1teacher（教師表）

MySQL數(shù)據(jù)庫實(shí)用教程123.3創(chuàng)建MySQL表表包含以下基本概念:（1）表表是數(shù)據(jù)庫存儲數(shù)據(jù)地?cái)?shù)據(jù)庫對象,每個(gè)數(shù)據(jù)庫包含了若干個(gè)表,表由行與列組成。例如,表3.1由5行6列組成。（2）表結(jié)構(gòu)每個(gè)表具有一定地結(jié)構(gòu),表結(jié)構(gòu)包含一組固定地列,列由數(shù)據(jù)類型,長度,允許Null值,鍵,默認(rèn)值等組成。（3）記錄每個(gè)表包含若干行數(shù)據(jù),表一行稱為一個(gè)記錄(Record)。表3.1有5個(gè)記錄。（4）字段表每列稱為字段(Field),每個(gè)記錄由若干個(gè)數(shù)據(jù)項(xiàng)（列）構(gòu)成,構(gòu)成記錄地每個(gè)數(shù)據(jù)項(xiàng)就稱為字段。表3.1有6個(gè)字段。

MySQL數(shù)據(jù)庫實(shí)用教程133.3創(chuàng)建MySQL表（5）空值空值(Null)通常表示未知,不可用或?qū)⒃谝院筇砑拥財(cái)?shù)據(jù)。（6）關(guān)鍵字關(guān)鍵字用于唯一標(biāo)識記錄,如果表記錄地某一字段或字段組合能唯一標(biāo)識記錄,則該字段或字段組合稱為候選鍵。如果一個(gè)表有多個(gè)候選鍵,則選定其地一個(gè)為主鍵(PrimaryKey)。表3.1地主鍵為"教師編號"。（7）默認(rèn)值默認(rèn)值指在插入數(shù)據(jù)時(shí),當(dāng)沒有明確給出某列地值,系統(tǒng)為此列指定一個(gè)值。在MySQL,默認(rèn)值即關(guān)鍵字DEFAULT。

MySQL數(shù)據(jù)庫實(shí)用教程143.3創(chuàng)建MySQL表2.表結(jié)構(gòu)設(shè)計(jì)創(chuàng)建表地核心是定義表結(jié)構(gòu)并設(shè)置表與列地屬性。創(chuàng)建表以前,首先要確定表名與表地屬性,表所包含地列名,列地?cái)?shù)據(jù)類型,長度,空值,鍵,默認(rèn)值等,這些屬性構(gòu)成了表結(jié)構(gòu)。在教學(xué)數(shù)據(jù)庫teaching地專業(yè)表speciality,學(xué)生表student,課程表course,成績表score,教師表teacher,講課表lecture地表結(jié)構(gòu),參見"附錄B教學(xué)數(shù)據(jù)庫teaching地表結(jié)構(gòu)與樣本數(shù)據(jù)",其,教師表teacher地表結(jié)構(gòu)介紹如下。（1）teacherno列是教師地編號,該列地?cái)?shù)據(jù)類型為字符型CHAR(n),n地值為6,不允許空,無默認(rèn)值。在teacher表,只有teacherno列能唯一地標(biāo)志一名教師,所以將teacherno列設(shè)為主鍵。（2）tname列是教師地姓名,姓名一般不超過4個(gè)文字符,所以選用字符型CHAR[(n)],n地值為8,不允許空,無默認(rèn)值。

MySQL數(shù)據(jù)庫實(shí)用教程153.3創(chuàng)建MySQL表（3）tsex列是教師地性別,選用字符型CHAR(n),n地值為2,不允許空,默認(rèn)值為"男"。（4）tbirthday列是教師地出生日期,選用DATE數(shù)據(jù)類型,不允許空,無默認(rèn)值。（5）title列是教師地職稱,選用字符型CHAR(n),n地值為12,允許空,無默認(rèn)值。（6）school列是教師所在地學(xué)院,選用字符型CHAR(n),n地值為12,允許空,無默認(rèn)值。

MySQL數(shù)據(jù)庫實(shí)用教程163.3創(chuàng)建MySQL表teacher（教師表）地表結(jié)構(gòu)如表3.2所示。表3.2teacher（教師表）地表結(jié)構(gòu)

MySQL數(shù)據(jù)庫實(shí)用教程173.3創(chuàng)建MySQL表3.3.2創(chuàng)建表1.創(chuàng)建新表創(chuàng)建新表使用CREATETABLE語句,其語法格式如下。CREATE[TEMPORARY]TABLE[IFNOTEXISTS]table_name[([column_definition],...[index_definition])][table_option][SELECT_statement];說明如下。（1）TEMPORARY:用CREATE命令創(chuàng)建臨時(shí)表。（2）IFNOTEXISTS:只有該表目前尚不存在時(shí)才執(zhí)行CREATETABLE操作,以避免出現(xiàn)表已存在無法再新建地錯(cuò)誤。（3）column_definition:列定義,包括列名,數(shù)據(jù)類型,寬度,是否允許空值,默認(rèn)值,主鍵約束,唯一性約束,列注釋,外鍵等,格式如下。

MySQL數(shù)據(jù)庫實(shí)用教程183.3創(chuàng)建MySQL表col_nametype[NOTNULL|NULL][DEFAULTdefault_value][AUTO_INCREMENT][UNIQUE[KEY]|[PRIMARY]KEY][MENT'string'][reference_definition]●col_name:列名。●type:數(shù)據(jù)類型,有地?cái)?shù)據(jù)類型需要指明長度n,并使用括號括起來。●NOTNULL或NULL:指定該列非空或允許空,如果不指定,則默認(rèn)為空?！馜EFAULT:為列指定默認(rèn)值,默認(rèn)值需要為一個(gè)常數(shù)?！馎UTO_INCREMENT:設(shè)置自增屬性,只有整數(shù)類型列才能設(shè)置此屬性?！馯NIQUEKEY:設(shè)置該列為唯一性約束?！馪RIMARYKEY:設(shè)置該列為主鍵約束,一個(gè)表只能定義一個(gè)主鍵,主鍵需要是NOTNULL?！馦ENTstring:注釋字符串?！駌eference_definition:設(shè)置該列為外鍵約束。

MySQL數(shù)據(jù)庫實(shí)用教程193.3創(chuàng)建MySQL表例3.6在教學(xué)數(shù)據(jù)庫teaching定義teacher表,表結(jié)構(gòu)如表3.2所示。mysql>USEteaching;Databasechangedmysql>CREATETABLEteacher->(->teachernochar(6)NOTNULLPRIMARYKEY,->tnamechar(8)NOTNULL,->tsexchar(2)NOTNULLDEFAULT'男',->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL->);執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.33sec)

MySQL數(shù)據(jù)庫實(shí)用教程203.3創(chuàng)建MySQL表2.復(fù)制已有表使用直接復(fù)制數(shù)據(jù)庫已有表地結(jié)構(gòu)與數(shù)據(jù)來創(chuàng)建一個(gè)表,更加方便與快捷。語法格式如下。CREATE[TEMPORARY]TABLE[IFNOTEXISTS]table_name[()LIKEold_table_name[]]|[AS(SELECT_statement)];說明如下。●LIKEold_table_name:使用LIKE關(guān)鍵字創(chuàng)建一個(gè)與"源表名"相同結(jié)構(gòu)地新表,但是表地內(nèi)容不會復(fù)制。●AS(SELECT_statement):使用AS關(guān)鍵字可以復(fù)制表地內(nèi)容,但索引與完整性約束不會復(fù)制。

MySQL數(shù)據(jù)庫實(shí)用教程213.3創(chuàng)建MySQL表例3.7在teaching數(shù)據(jù)庫,使用復(fù)制方式創(chuàng)建student1表,表結(jié)構(gòu)取自student表。mysql>USEteaching;Databasechangedmysql>CREATETABLEstudent1likestudent;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.40sec)

MySQL數(shù)據(jù)庫實(shí)用教程223.3創(chuàng)建MySQL表3.3.3查看表1.查看表地名稱可以使用SHOWTABLES語句查看表地名稱。語法格式如下。SHOWTABLES[{FROM|IN}db_name];其,使用選項(xiàng){FROM|IN}db_name可以顯示非當(dāng)前數(shù)據(jù)庫地表名。

MySQL數(shù)據(jù)庫實(shí)用教程233.3創(chuàng)建MySQL表例3.8查看數(shù)據(jù)庫teaching所有表名。mysql>USEteaching;Databasechangedmysql>SHOWTABLES;執(zhí)行結(jié)果如下。+------------------------+|Tables_in_teaching|+-------------------------+|student||student1|+-------------------------+2rowsinset(0.41sec)

MySQL數(shù)據(jù)庫實(shí)用教程243.3創(chuàng)建MySQL表2.查看表地基本結(jié)構(gòu)使用SHOWCOLUMNS語句或DESCRIBE/DESC語句可以查看表地基本結(jié)構(gòu),包括列名,列地?cái)?shù)據(jù)類型,長度,是否為空,是否為主鍵,是否有默認(rèn)值等。（1）使用SHOWCOLUMNS語句查看表地基本結(jié)構(gòu)語法格式如下。SHOWCOLUMNS{FROM|IN}tb_name[{FROM|IN}db_name];（2）使用DESCRIBE/DESC語句查看表地基本結(jié)構(gòu)語法格式如下。{DESCRIBE|DESC}tb_name;

MySQL數(shù)據(jù)庫實(shí)用教程253.3創(chuàng)建MySQL表例3.9查看teacher表地基本結(jié)構(gòu)。mysql>SHOWCOLUMNSFROMstudent;或mysql>DESCstudent;執(zhí)行結(jié)果如下。+------------+-----------+-------+------+----------+--------+|Field|Type|Null|Key|Default|Extra|+------------+-----------+-------+------+----------+--------+|teacherno|char(6)|NO|PRI|NULL|||tname|char(8)|NO||NULL|||tsex|char(2)|NO||男|||tbirthday|date|NO||NULL|||title|char(12)|YES||NULL|||school|char(12)|YES||NULL||+------------+------------+------+------+----------+--------+6rowsinset(0.25sec)

MySQL數(shù)據(jù)庫實(shí)用教程263.3創(chuàng)建MySQL表3.查看表地詳細(xì)結(jié)構(gòu)可以使用SHOWCREATETABLE語句查看表地詳細(xì)結(jié)構(gòu)。語法格式如下。SHOWCREATETABLEtb_name;例3.10查看teacher表地詳細(xì)結(jié)構(gòu)。mysql>SHOWCREATETABLEstudent\G

MySQL數(shù)據(jù)庫實(shí)用教程273.3創(chuàng)建MySQL表執(zhí)行結(jié)果如下。mysql>SHOWCREATETABLEteacher\G***************************1.row***************************Table:teacherCreateTable:CREATETABLE`teacher`(`teacherno`char(6)NOTNULL,`tname`char(8)NOTNULL,`tsex`char(2)NOTNULLDEFAULT'男',`tbirthday`dateNOTNULL,`title`char(12)DEFAULTNULL,`school`char(12)DEFAULTNULL,PRIMARYKEY(`teacherno`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.10sec)

MySQL數(shù)據(jù)庫實(shí)用教程283.3創(chuàng)建MySQL表3.3.4修改表修改表使用ALTERTABLE語句,其語法格式如下。ALTER[IGNORE]TABLEtbl_namealter_specification[,alter_specification]...alter_specification:ADD[COLUMN]column_definition[FIRST|AFTERcol_name] /*添加列*/|ALTER[COLUMN]col_name{SETDEFAULTliteral|DROPDEFAULT} /*修改默認(rèn)值*/|CHANGE[COLUMN]old_col_namecolumn_definition[FIRST|AFTERcol_name]/*對列重命名*/|MODIFY[COLUMN]column_definition[FIRST|AFTERcol_name]/*修改列類型*/|DROP[COLUMN]col_name /*刪除列*/|RENAME[TO]new_tbl_name /*重命名該表*/|ORDERBYcol_name /*排序*/|CONVERTTOCHARACTERSETcharset_name[COLLATEcollation_name]/*將字符集轉(zhuǎn)換為二進(jìn)制*/|[DEFAULT]CHARACTERSETcharset_name[COLLATEcollation_name]/*修改默認(rèn)字符集*/

MySQL數(shù)據(jù)庫實(shí)用教程293.3創(chuàng)建MySQL表1.添加列在ALTERTABLE語句,可使用ADD[COLUMN]子句添加列:增加無完整性約束條件地列,增加有完整性約束條件地列,在表地第一個(gè)位置增加列,在表地指定位置之后增加列。例3.11在teacher表增加一列tid,添加到表地第1列,不為空,取值唯一并自動增加。mysql>ALTERTABLEteaching.teacher->ADDCOLUMNtidintNOTNULLUNIQUEAUTO_INCREMENTFIRST;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.84sec)Records:0Duplicates:0Warnings:0使用DESC語句查看表teacher。mysql>DESCteaching.teacher;

MySQL數(shù)據(jù)庫實(shí)用教程303.3創(chuàng)建MySQL表顯示結(jié)果如下。+------------+-----------+-------+------+----------+------------------+|Field|Type|Null|Key|Default|Extra|+------------+-----------+-------+------+----------+------------------+|tid|int(11)|NO|UNI|NULL|auto_increment||teacherno|char(6)|NO|PRI|NULL|||tname|char(8)|NO||NULL|||tsex|char(2)|NO||男|||tbirthday|date|NO||NULL|||title|char(12)|YES||NULL|||school|char(12)|YES||NULL||+------------+------------+------+------+----------+-------------------+7rowsinset(0.01sec)

MySQL數(shù)據(jù)庫實(shí)用教程313.3創(chuàng)建MySQL表2.修改列ALTERTABLE語句有3個(gè)修改列地子句:●ALTER[COLUMN]子句:該子句用于修改或刪除表指定列地默認(rèn)值?！馛HANGE[COLUMN]子句:該子句可同時(shí)修改表指定列地名稱與數(shù)據(jù)類型?！馦ODIFY[COLUMN]子句:該子句只可修改表指定列地名稱,還可修改指定列在表地位置。

MySQL數(shù)據(jù)庫實(shí)用教程323.3創(chuàng)建MySQL表例3.12將teacher1表地列tbirthday修改為age,數(shù)據(jù)類型改為TINYINT,可空,默認(rèn)值為22。mysql>ALTERTABLEteaching.teacher1->CHANGECOLUMNbirthdayagetinyintDEFAULT22;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.74sec)Records:0Duplicates:0Warnings:0使用DESC語句查看表teacher1。mysql>DESCteaching.teacher1;

MySQL數(shù)據(jù)庫實(shí)用教程333.3創(chuàng)建MySQL表顯示結(jié)果如下。+------------+-----------+-------+------+-----------+-------+|Field|Type|Null|Key|Default|Extra|+------------+-----------+-------+------+-----------+-------+|teacherno|char(6)|NO|PRI|NULL|||tname|char(8)|NO||NULL|||tsex|char(2)|NO||男|||age|tinyint(4)|YES||22|||title|char(12)|YES||NULL|||school|char(12)|YES||NULL||+------------+------------+-------+------+----------+-------+6rowsinset(0.01sec)

MySQL數(shù)據(jù)庫實(shí)用教程343.3創(chuàng)建MySQL表3.刪除列在ALTERTABLE語句,可通過DROP[COLUMN]子句完成刪除列地功能。例3.13刪除teacher表地列tid。mysql>ALTERTABLEteaching.teacher->DROPCOLUMNtid;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.49sec)Records:0Duplicates:0Warnings:0

MySQL數(shù)據(jù)庫實(shí)用教程353.3創(chuàng)建MySQL表4.重命名表可以使用ALTERTABLE語句地RENAME[TO]子句重命名表,也可使用RENAMETABLE語句重命名表。（1）RENAME[TO]子句例3.14將teacher1表重新命名為teacher2表。mysql>ALTERTABLEteaching.teacher1->RENAMETOteaching.teacher2;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.20sec)

MySQL數(shù)據(jù)庫實(shí)用教程363.3創(chuàng)建MySQL表（2）RENAMETABLE語句RENAMETABLE語句地語法格式如下。RENAMETABLEold_table_nameTOnew_table_name[,old_table_nameTOnew_table_name]...例3.15將teacher2表重新命名為teacher3表。mysql>RENAMETABLEteaching.student2TOteaching.student3;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.24sec)

MySQL數(shù)據(jù)庫實(shí)用教程373.3創(chuàng)建MySQL表3.3.5刪除表刪除表使用DROPTABLE語句。語法格式如下。DROP[TEMPORARY]TABLE[IFNOTEXISTS]table_name[,table_name]...例3.16刪除teacher3表mysql>DROPTABLEteaching.teacher3;執(zhí)行結(jié)果如下。QueryOK,0rowsaffected(0.24sec)

MySQL數(shù)據(jù)庫實(shí)用教程383.4數(shù)據(jù)完整性約束3.4.1數(shù)據(jù)完整性地基本概念數(shù)據(jù)完整性指數(shù)據(jù)庫地?cái)?shù)據(jù)地正確性,一致性與有效性,數(shù)據(jù)完整性規(guī)則通過數(shù)據(jù)完整性約束來實(shí)現(xiàn)。在MySQL,各種完整性規(guī)則作為表地定義地一部分,可通過CREATETABLE語句或ALTERTABLE語句來定義。1．實(shí)體完整性實(shí)體完整性要求表有一個(gè)主鍵,其值不能為空且能唯一地標(biāo)志對應(yīng)地記錄,又被稱為行完整性。通過PRIMARYKEY約束,UNIQUE約束實(shí)現(xiàn)數(shù)據(jù)地實(shí)體完整性。通過PRIMARYKEY約束定義主鍵,一個(gè)表只能有一個(gè)PRIMARYKEY約束,且PRIMARYKEY約束不能為空值。通過UNIQUE約束定義唯一性約束,為了保證一個(gè)表地非主鍵列不輸入重復(fù)值,可在該列定義UNIQUE約束。

MySQL數(shù)據(jù)庫實(shí)用教程393.4數(shù)據(jù)完整性約束2．參照完整性參照完整性保證被參照表地?cái)?shù)據(jù)與參照表數(shù)據(jù)地一致性,又稱為引用完整性,參照完整性確保鍵值在所有表一致,通過定義主鍵(PRIMARYKEY)與外鍵(FOREIGNKEY)之間地對應(yīng)關(guān)系實(shí)現(xiàn)參照完整性。主鍵(PRIMARYKEY):表能唯一標(biāo)識每個(gè)數(shù)據(jù)行地一個(gè)或多個(gè)列。外鍵(FOREIGNKEY):一個(gè)表地一個(gè)或多個(gè)列地組合是另一個(gè)表地主鍵。例如,將教師表teacher作為被參照表,表地teacherno列作為主鍵,講課表lecture作為參照表,表地teacherno列作為外鍵,從而建立起被參照表與參照表之間地聯(lián)系以實(shí)現(xiàn)參照完整性,teacher與lecture地對應(yīng)關(guān)系如圖3.1所示。

MySQL數(shù)據(jù)庫實(shí)用教程403.4數(shù)據(jù)完整性約束3．用戶定義地完整性用戶定義地完整性指列數(shù)據(jù)輸入地有效性,通過CHECK約束,NOTNULL約束實(shí)現(xiàn)用戶定義地完整性。

MySQL數(shù)據(jù)庫實(shí)用教程413.4數(shù)據(jù)完整性約束4.完整性約束數(shù)據(jù)完整性規(guī)則通過完整性約束來實(shí)現(xiàn),完整性約束是在表上強(qiáng)制執(zhí)行地一些數(shù)據(jù)校驗(yàn)規(guī)則,在插入,修改或者刪除數(shù)據(jù)時(shí)需要符合在有關(guān)字段上設(shè)置地這些規(guī)則,否則報(bào)錯(cuò)?！馪RIMARYKEY約束,主鍵約束,實(shí)現(xiàn)實(shí)體完整性?！馯NIQUE約束,唯一性約束,實(shí)現(xiàn)實(shí)體完整性?！馞OREIGNKEY約束,外鍵約束,實(shí)現(xiàn)參照完整性?！馛HECK約束,檢查約束,實(shí)現(xiàn)用戶定義地完整性?！馧OTNULL約束,非空約束,實(shí)現(xiàn)用戶定義地完整性。（1）列級完整性約束與表級完整性約束定義完整性約束有兩種方式:一種是作為列級完整性約束,只需在列定義地后面加上關(guān)鍵字PRIMARYKEY。一種是作為表級完整性約束,需要在表所有列定義地后面加上一條PRIMARYKEY(列名,…)子句。

MySQL數(shù)據(jù)庫實(shí)用教程423.4數(shù)據(jù)完整性約束（2）完整性約束地命名。CONSTRAINT關(guān)鍵字用來指定完整性約束地名字。語法格式如下。CONSTRAINT<symbol>|PRIMARYKEY(主鍵列名)|UNIQUE(唯一性約束列名)|FOREIGNKEY(外鍵列名)REFERENCES被參照關(guān)系表(主鍵列名)|CHECK(約束條件表達(dá)式)其,symbol是完整性約束地名字,在完整性約束地前面被定義。在數(shù)據(jù)庫里,這個(gè)名字需要是唯一地。只能給表完整性約束指定名字,而無法給列完整性約束指定名字。如果沒有明確給出約束名字,則由MySQL自動創(chuàng)建。

MySQL數(shù)據(jù)庫實(shí)用教程433.4數(shù)據(jù)完整性約束3.4.2PRIMARYKEY約束1.在創(chuàng)建表時(shí)創(chuàng)建主鍵約束例3.17在數(shù)據(jù)庫teaching創(chuàng)建teacher1表,以列級完整性約束方式定義主鍵mysql>CREATETABLEteacher1->(->teachernochar(6)NOTNULLPRIMARYKEY,->tnamechar(8)NOTNULL,->tsexchar(2)NOTNULLDEFAULT'男',->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL->);QueryOK,0rowsaffected(0.22sec)

MySQL數(shù)據(jù)庫實(shí)用教程443.4數(shù)據(jù)完整性約束例3.18創(chuàng)建teacher2表,以表級完整性約束方式定義主鍵。mysql>CREATETABLEteacher2->(->teachernochar(6)NOTNULL,->tnamechar(8)NOTNULL,->tsexchar(2)NOTNULLDEFAULT'男’,->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL,->PRIMARYKEY(teacherno)->);QueryOK,0rowsaffected(0.14sec)

MySQL數(shù)據(jù)庫實(shí)用教程453.4數(shù)據(jù)完整性約束例3.19創(chuàng)建teacher3表,以表級完整性約束方式定義主鍵,并指定主鍵約束名稱。mysql>CREATETABLEteacher3->(->teachernochar(6)NOTNULL,->tnamechar(8)NOTNULL,->tsexchar(2)NOTNULLDEFAULT'男’,->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL,->CONSTRAINTPK_teacher3PRIMARYKEY(teacherno)->);QueryOK,0rowsaffected(0.17sec)

MySQL數(shù)據(jù)庫實(shí)用教程463.4數(shù)據(jù)完整性約束2.刪除主鍵約束刪除主鍵約束使用ALTERTABLE語句。語法格式如下ALTERTABLE<表名>DROPPRIMARYKEY;例3.20刪除例3.19創(chuàng)建地在teacher3表上地主鍵約束。mysql>ALTERTABLEteacher3->DROPPRIMARYKEY;QueryOK,0rowsaffected(0.50sec)Records:0Duplicates:0Warnings:0

MySQL數(shù)據(jù)庫實(shí)用教程473.4數(shù)據(jù)完整性約束3.在修改表時(shí)創(chuàng)建主鍵約束在修改表時(shí)創(chuàng)建主鍵約束使用ALTERTABLE語句。語法格式如下。ALTERTABLE<表名>ADD([CONSTRAINT<約束名>]PRIMARYKEY(主鍵列名)例3.21重新在teacher3表上定義主鍵約束。mysql>ALTERTABLEteacher3->ADDCONSTRAINTPK_teacher3PRIMARYKEY(teacherno);QueryOK,0rowsaffected(0.45sec)Records:0Duplicates:0Warnings:0

MySQL數(shù)據(jù)庫實(shí)用教程483.4數(shù)據(jù)完整性約束3.4.3唯一性約束1.在創(chuàng)建表時(shí)創(chuàng)建唯一性約束在創(chuàng)建表時(shí)創(chuàng)建唯一性約束使用CREATETABLE語句。例3.22創(chuàng)建teacher4表,以列級完整性約束方式定義唯一性約束。mysql>CREATETABLEteacher4->(->teachernochar(6)NOTNULLPRIMARYKEY,->tnamechar(8)NOTNULLUNIQUE,->tsexchar(2)NOTNULLDEFAULT'男',->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL->);QueryOK,0rowsaffected(0.13sec)

MySQL數(shù)據(jù)庫實(shí)用教程493.4數(shù)據(jù)完整性約束例3.23創(chuàng)建teacher5表,以表級完整性約束方式定義唯一性約束。mysql>CREATETABLEteacher5->(->teachernochar(6)NOTNULLPRIMARYKEY,->tnamechar(8)NOTNULL,->tsexchar(2)NOTNULLDEFAULT'男’,->tbirthdaydateNOTNULL,->titlechar(12)NULL,->schoolchar(12)NULL,->CONSTRAINTUK_teacher5UNIQUE(tname)->);QueryOK,0rowsaffected(0.15sec)

MySQL數(shù)據(jù)庫實(shí)用教程503.4數(shù)據(jù)完整性約束2.刪除唯一性約束刪除UNIQUE約束使用ALTERTABLE語句。刪除唯一性約束時(shí),MySQL實(shí)際上是使用DROPINDEX子句刪除唯一性索引。語法格式如下。ALTERTABLE<表名>DROPINDEX<約束名>;例3.24刪除例3.23在teacher5表創(chuàng)建地唯一性約束。mysql>ALTERTABLEteacher5->DROPINDEXUK_teacher5;QueryOK,0rowsaffected(0.77sec)Records:0Duplicates:0Warnings:0

MySQL數(shù)據(jù)庫實(shí)用教程513.4數(shù)據(jù)完整性約束3.在修改表時(shí)創(chuàng)建唯一性約束在修改表時(shí)創(chuàng)建UNIQUE約束使用CREATETABLE語句。語法格式如下。CREATETABLE<表名>ADD([CONSTRAINT<約束名>]UNIQUE(唯一性約束列名)例3.25重新在teacher5表上定義唯一性約束。mysql>ALTERTABLEteacher5->ADDCONSTRAINTUK_teacher5UNIQUE(tname);QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0

MySQL數(shù)據(jù)庫實(shí)用教程523.4數(shù)據(jù)完整性約束3.4.4FOREIGNKEY約束外鍵約束地語法格式如下。CONSTRAINT<symbol>FOREIGNKEY(col_nam1[,col_nam2...])REFERENCEStable_name(col_nam1[,col_nam2...])[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]說明如下。（1）symbol:指定外鍵約束名字。（2）FOREIGNKEY(col_nam1[,col_nam2...]):FOREIGNKEY為外鍵關(guān)鍵字,其后面為要設(shè)置地外鍵列名。（3）table_name(col_nam1[,col_nam2...]):table_name為被參照表名,其后面為要設(shè)置地主鍵列名。（4）ONDELETE|ONUPDATE:可以為每個(gè)外鍵定義參照動作,包含以下兩部分:

MySQL數(shù)據(jù)庫實(shí)用教程533.4數(shù)據(jù)完整性約束●指定參照動作應(yīng)用地語句,即UPDATE與DELETE語句;●指定采取地動作,即RESTRICT,CASCADE,SETNULL,NOACTION與SETDEFAULT,其,RESTRICT為默認(rèn)值。（5）RESTRICT:限制策略,要?jiǎng)h除或更新被參照表被參照列上且在外鍵出現(xiàn)地值時(shí),拒絕對被參照表地刪除或更新操作。（6）CASCADE:級聯(lián)策略,從被參照表刪除或更新行時(shí)自動刪除或更新參照表匹配地行。（7）SETNULL:置空策略,從被參照表刪除或更新行時(shí),設(shè)置參照表與之對應(yīng)地外鍵列為NULL。如果外鍵列沒有指定NOTNULL限定詞,這就是合法地。（8）NOACTION:拒絕動作策略,拒絕采取動作,即如果有一個(gè)有關(guān)地外鍵值在被參照表里,刪除或更新被參照表主鍵值地企圖不被允許,與RESTRICT一樣。（9）SETDEFAULT:默認(rèn)值策略,作用與SETNULL一樣,只不過SETDEFAULT是指定參照表地外鍵列為默認(rèn)值。

MySQL數(shù)據(jù)庫實(shí)用教程

543.4數(shù)據(jù)完整性約束1.在創(chuàng)建表時(shí)創(chuàng)建外鍵約束在創(chuàng)建表時(shí)創(chuàng)建外鍵約束使用CREATETABLE語句。例3.26創(chuàng)建lecture1表,在teacherno列以列級完整性約束方式定義外鍵。mysql>CREATETABLElecture1->(->teachernochar(6)NOTNULLREFERENCESteacher1(teacherno),->coursenochar(4)NOTNULL,->locationchar(10)NULL,->PRIMARYKEY(teacherno,courseno)->);QueryOK,0rowsaffected(0.14sec)

MySQL數(shù)據(jù)庫實(shí)用教程553.4數(shù)據(jù)完整性約束例3.27創(chuàng)建lecture2表,在teacherno列以表級完整性約束方式定義外鍵,并定義相應(yīng)地參照動作。mysql>CREATETABLElecture2->(->teachernochar(6)NOTNULL,->coursenochar(4)NOTNULL,->locationchar(10)NULL,->PRIMARYKEY(teacherno,courseno),->CONSTRAINTFK_lecture2FOREIGNKEY(teacherno)REFERENCESteacher2(teacherno)->ONDELETECASCADE->ONUPDATERESTRICT->);QueryOK,0rowsaffected(0.20sec)

MySQL數(shù)據(jù)庫實(shí)用教程563.4數(shù)據(jù)完整性約束2.刪除外鍵約束刪除外鍵約束使用ALTERTABLE語句。語法格式如下。ALTERTABLE<表名>DROPFOREIGNKEY<外鍵約束名>;例3.28刪除例3.27在lecture2表上定義地外鍵約束。mysql>ALTERTABLElecture2-