MySQL数据库基础实例教程（第3版）课件 第三章 数据定义

第3章数据定义项目知识要点知识能力目标学时任务1创建与管理数据库1创建数据库2管理数据库任务2创建与管理数据库表1创建数据库表

2管理数据库表任务3数据完整性约1主键约束2替代键约束3参照完整性约束4CHECK完整性约束【典型工作任务】学会创建与管理数据库和表【技能要求】

1掌握创建与管理数据库的方法2掌握创建与管理数据库表的方法3掌握建立数据完整性约束的方法【知识要求】1数据库和数据库表的结构2数据完整性约束3数据库及表操作的SQL语句

6项目实训LibraryDB数据定义4项目知识要点与目标E-R图设计实例【例1.1】网络图书销售系统处理会员图书销售。简化的业务处理过程为：网络销售的图书信息包括：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片等；用户需要购买图书必须先注册为会员，提供身份证号，会员姓名，密码，性别，联系电话，注册时间等信息；系统根据会员的购买订单形成销售信息，包括订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清。

请画出网络图书销售数据库E-R图,并将其转化为关系模型网络图书销售数据库E-R图网络图书销售数据库E-R图关系模型：Bookstore（数据库）01OPTION02OPTION03OPTIONbook(图书编号,图书类别,书名,作者,出版社,出版时间,单价,数量,折扣,封面图片)，如下表。

members(用户号,姓名,性别,密码,联系电话,注册时间)。sell(订单号,用户号,图书编号,订购册数,订购单价,订购时间,是否发货,是否收货,是否结清)。book表网络图书销售数据库E-R图members表sell表MySQL数据库模型在数据库服务器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。数据库文件：数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。数据库只能由得到授权的用户访问，这样保证了数据库的安全。成绩数据库xscj网络图书库bookstore雇员管理库YGGL……….库MySQL服务器数据库和表数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最基本的数据对象，用以存放数据库的数据。必须首先创建数据库，然后才能创建数据库的数据对象。MySQL可以采用两种方式来创建、操作数据库和数据对象（1）命令行方式（2）图形界面工具方式数据库数据表视图存储过程DELIMITER$$CREATEPROCEDUREDELETE_STUDENT(INXHCHAR(6))BEGIN

创建数据库MySQL安装成功后，系统会自动创建如information_schema、MySQL这样的系统数据库，MySQL数据库的系统信息都存储在这些系统数据库中。如果删除了这些数据库，MySQL将不能正常工作。对于用户的数据，需要创建新的数据库来存放。语法格式：创建数据库CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名

[[DEFAULT]CHARACTERSET字符集名

|[DEFAULT]COLLATE校对规则名]说明：语句中“[]”内为可选项。{|}表示二选一。MySQL命令解释器对大小写不敏感，“CREATE”和“create”是同一含义。汉字为变量，输入命令前，一定要用具体的实意词替代。如“数据库名”用“Petstore”替代。命令行方式创建数据库在创建数据库命令中，如果“{}”二选一中选DATABASE，“[]”可选项全部不选，其结构形式如下：CREATEDATABASE数据库名;数据库名表示被创建数据库名，数据库名必须符合以下规则：数据库名必须唯一；名称内不能含有“/”及“.”等非法字符；最大不能超过64字节。【例3.1】创建一个名为Bookstore的数据库。CREATEDATABASEBookstore;显示数据库命令显示数据库SHOWDATABASES显示服务器中已建立的数据库，使用SHOWDATABASES命令。语法格式：此命令没有用户变量，执行“SHOWDATABASES；”。注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。打开数据库USE数据库名因为MySQL服务器中有多个数据库，使用USE命令可指定当前数据库。语法格式：说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATEDATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。【例】将数据库test1设置为当前数据库。

Usetest1;MySQL中的字符集和校对规则字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。若某字符集有四个字母：‘A’、‘B’、‘a’、‘b’，每个字母赋予一个数值：‘A’=0，‘B’=1，‘a’=2，‘b’=3。字母‘A’是一个符号，数字0是‘A’的编码，字母和它们的编码组合在一起构成一个字符集。假设我们希望比较两个字符串的值：‘A’和‘a’。比较的最简单的方法是查找编码：‘A’为0，‘a’为2。若我们制定的比较规则是：“按编码值的大小进行比较”，因为0小于2，我们可以说‘A’小于‘a’。“按编码值的大小进行比较”就是这个字符集的一个校对规则。MySQL字符集和校对规则应用MySQL支持30多种字符集的70多种校对规则。在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串。可用SHOWCHARACTERSET命令查看。两个不同的字符集不能有相同的校对规则。每个字符集有一个默认校对规则。【例3.2】创建一个名为Petstore的数据库，采用字符集gb2312和校对规则gb2312_chinese_ci。CREATEDATABASEPetstore

DEFAULTCHARACTERSETgb2312COLLATEgb2312_chinese_ci;修改数据库数据库创建后，如果需要修改数据库的参数，可以使用ALTERDATABASE命令。【例3.3】修改数据库Petstore的默认字符集为latin1，校对规则为latin1_swedish_ci。ALTERDATABASEPetstoreDEFAULTCHARACTERSETlatin1DEFAULTCOLLATElatin1_swedish_ci;ALTER{DATABASE|SCHEMA}[数据库名][[DEFAULT]CHARACTERSET字符集名

|[DEFAULT]COLLATE校对规则名]语法格式：删除数据库已经创建的数据库需要删除，使用DROPDATABASE命令。数据库test2命令如下：DROPDATABASEtest2;DROPDATABASE[IFEXISTS]数据库名语法格式：数据库表结构分析图书目录表图书编号类别书名出版时间单价数量TP.2525计算机PHP高级语言2022-06-2033.253TP.2462计算机计算机应用基础2022-10-1945.0045TP.2463计算机计算机网络技术2021-10-1625.5031book1表结构分析字段名字段类型宽度是否为空默认值说明图书编号char10x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40x不超过40个字符出版时间datex单价Float5,2√元为单位，精确到分数量int

√MySQL数据类型MySQL常用数据类型：字符串类型数值类型时间类型大数据类型Char(n)intdateblobVarchar(n)Float(M,D)timetextdatetimeCREATETABLE命令为数据库创建数据表，可以使用SQL命令CREATETABLE来完成。此语句的完整语法是相当复杂的，因为存在很多的可选子句创建表使用CREATETABLE命令。语法格式：CREATETABLE[IFNOTEXISTS]表名（列名数据类型[NOTNULL|NULL][DEFAULT列默认值]…）

ENGINE=存储引擎CREATETABLE命令的基本格式：CREATETABLE表名

（列名1数据类型1[NOTNULL|NULL],

列名2数据类型2[NOTNULL|NULL],…..）命令行方式创建数据库表命令行方式创建数据库表book1字段名字段类型宽度是否为空默认值说明图书编号char10x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40x不超过40个字符出版时间datex单价Float5,2√元为单位，精确到分数量int

√CREATETABLEbook(

图书编号char(10)NOTNULL,

类别varchar(8)NOTNULLDEFAULT'计算机',

书名varchar(40)NOTNULL,

出版时间dateNOTNULL,

单价float(5,2)NULL,

数量int);修改表ALTERTABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：ALTERTABLE表名

ADD[COLUMN]列定义[FIRST|AFTER列名]

/*添加列*/ |ALTER[COLUMN]列名{SETDEFAULT默认值|DROPDEFAULT} /*修改默认值*/|CHANGE[COLUMN]旧列名列定义

/*对列重命名*/

[FIRST|AFTER列名] |MODIFY[COLUMN]列定义[FIRST|AFTER列名] /*修改列类型*/|DROP[COLUMN]列名 /*删除列*/|RENAME[TO]新表名 /*重命名该表*/修改表结构

ALTERTABLE用于更改原有表的结构。可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表完整性约束和表的类型等。增加列：ALTERTABLE表名ADD列定义删除列：ALTERTABLE表名DROP列名更改列名：ALTERTABLE表名CHANGE旧列名列定义更改列类型：ALTERTABLE表名MODIFY列定义修改表结构增加列：ALTERTABLE表名ADD列定义[FIRST|AFTER列名]【例】在表book中增加一列“浏览次数”。ALTERTABLEbookADD浏览次数intNULL;删除列：ALTERTABLE表名DROP列名【扩展】在表book中增加一列“浏览次数”，位置在单价列的后面。ALTERTABLEbookADD浏览次数intNULLafter单价;【例】删除book表中“浏览次数”列。ALTERTABLEbookDROP浏览次数;【扩展】在表book中增加“浏览次数”列，删除“封面图片”列。ALTERTABLEbook

ADD浏览次数intNULL,

DROP封面图片;修改表结构更改列名：ALTERTABLE表名

CHANGE旧列名

列定义【例】将book表中“出版时间”列改为“出版日期”列。ALTERTABLEbook

CHANGE出版时间出版日期datenotnull;更改列类型：ALTERTABLE表名

MODIFY列定义【例】将book表中“出版日期”列类型改为日期时间类型。ALTERTABLEbookMODIFY出版日期datetimenotnull;复制表语法格式：CREATETABLE[IFNOTEXISTS]新表名

[LIKE参照表名]|[AS(select语句)] 使用LIKE关键字创建一个与参照表相同结构的新表，但内容不会复制，因此创建的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。【例】假设数据库BookStore中有一个表Book，创建Book表的一个名为book_copy1的拷贝。

CREATETABLEbook_copy1LIKEBook;【例】创建表Book的一个名为book_copy2的拷贝，并且复制其内容。

CREATETABLEbook_copy2AS(SELECT*FROMBook);删除表需要删除一个表时可以使用DROPTABLE语句。这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。语法格式：DROPTABLE[IFEXISTS]表名1[,表名2]...【例】删除表test。

DROPTABLEIFEXISTStest;显示数据表信息（1）显示数据表文件名语法格式：

SHOWTABLES【例3.11】显示Bookstore数据库建立的数据表文件:USEBookstoreSHOWTABLES;（2）显示数据表结构语法格式：

{DESCRIBE|DESC}表名[列名|通配符]DESC是DESCRIBE的简写，二者用法相同。【例3.12】用DESCRIBE语句查看Book表的列的信息。

USEBookstoreDESCRIBEBook;【例3.13】查看Book表图书编号列的信息。

USEBookstoreDESCBook图书编号;数据完整性输入的类型是否正确？——年龄必须是数字输入的格式是否正确？——Email必须包含@符号是否在允许的范围内？——性别只能是”男”或者”女”是否存在重复输入？——员工信息输入了两次是否符合其他特定要求？——信誉值大于5的客户才能够加入客户表……列值要求（约束）整行要求（约束）数据完整性约束数据完整性：数据类型准确无误数据取值符合规定的范围多个数据表之间的数据不存在冲突实体完整性域完整性引用（参照）完整性用户定义的完整性实体完整性学号姓名地址……0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃江南新乡0010016赵可以河南亲乡×0010014雷铜江西南昌约束方法：唯一约束、主键约束域完整性学号姓名地址……0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃江南新乡0010016赵可以河南亲乡×8700000000李亮湖北江门约束方法：限制数据类型、检查约束、默认值、非空约束参照完整性学号姓名地址……0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃江南新乡0010016赵可以河南亲乡×数学001002198约束方法：外键约束科目学号分数……数学001001288数学001001374语文001001467语文001001581数学001001698自定义完整性用户编号用户姓名会员证……AV0012孙悟空AV378289AV0013猪悟能AV378290AV0014沙悟净AV378291CV0015玄痛AV372133CV0016乔峰AV121322×约束方法：存储过程、存储函数、触发器账号姓名信用……00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-10约束方法：存储过程、存储函数、触发器约束与完整性之间的关系完整性类型约束类型描述约束对象列完整性Default当使用INSERT语句插入数据时，若已定义默认值的列没有提供指定值，则将该默认值插入记录中列Check指定某一列可接受的值实体完整性PrimaryKey每行记录的惟一标识符，确保用户不能输入重复值，并自动创建索引，提高性能，该列不允许使用空值行Unique在列集内强制执行值的惟一性，防止出现重复值，表中不允许有两行的同一列包含相同的非空值参照完整性ForeignKey定义一列或几列，其值于本表或其他表的主键或UNIQUE列相匹配表与表之间主键约束主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARYKEY约束来创建主键，而且PRIMARYKEY约束中的列不能取空值。由于PRIMARYKEY约束能确保数据的唯一，所以经常用来定义标志列。如果PRIMARYKEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARYKEY约束定义中所有列的组合值必须唯一。可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARYKEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARYKEY（列名，…）语句。主键约束（1）列的完整性约束：【例】创建表book_copy，将“书名”定义为主键。CREATETABLEbookk_copy(

图书编号char(6)NOTNULL,

书名char(20)NOTNULLPRIMARYKEY,

出版日期date

);（2）表的完整性约束：【例】创建course表来记录每门课程的学生的“学号”“姓名”“课程号”“学分”“毕业日期”。其中“学号”“课程号”“毕业日期”构成复合主键。CREATETABLEcourse(学号 varchar(6) NOTNULL,

姓名 varchar(8) NOTNULL,

毕业日期

date NOTNULL,

课程号 varchar(3),

学分 tinyint,PRIMARYKEY(学号,课程号,毕业日期));注意：当表中的主键为复合主键时，只能定义为表的完整性约束。主键约束原则原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。（1）每个表只能定义一个主键。（2）关系模型理论要求必须为每个表定义一个主键。（3）唯一性原则：表中的两个不同的行在主键上不能具有相同的值。（4）最小化规则：如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，这个复合主键是不正确的，这条规则称为最小化规则。MySQL自动地为主键创建一个索引。通常，这个索引名为PRIMARY。然而，可以重新给这个索引起名。替代键约束在关系模型中，替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。【例】在表book_copy1中将图书编号作为主键，书名列定义为一个替代键。CREATETABLEbook_copy1(

图书编号varchar(20)NOTNULLprimarykey,

书名varchar(20)NOTNULLUNIQUE,

出版日期dateNULL);ALTERTABLE语句添加约束使用ALTERTABLE语句修改表，其中也包括向表中添加约束。语法格式如下：2.使用ALTERTABLE语句ALTERTABLE表名

ADDPRIMARYKEY(列名,...) /*添加主键*/|ADDUNIQUE[索引名](列名,...) /*添加替代键约束*/|DROPPRIMARYKEY /*删除主键*/|DROPINDEX索引名 /*删除索引*/ALTERTABLE举例ALTERTABLEBook ADDPRIMARYKEY(图书编号),ADDUNIQUEu_idx(书名);【例】假设Book表中主键未设定，为Book表建立以图书编号为主键约束,以书名为唯一性约束。这个例子中，既包括主键约束,也包括唯一性约束，说明MySQL可以同时创建多个约束。记住，使用PRIMARYKEY的列，必须是一个具有NOTNULL属性的列。如果想要查看表中创建的约束的情况，可以使用SHOWINDEXFROM语句，例如：SHOWINDEXFROMbook;【例】删除Book表上的主键和替代键约束。ALTERTABLEBook DROPPRIMARYKEY,DROPINDEXu_idx;替代键和主键的区别在MySQL中替代键和主键的区别主要有以下几点。02主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOTNULL声明。03一般创建PRIMARYKEY约束时，系统会自动产生PRIMARYKEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。01一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。参照完整性约束方法：外键约束河南新乡0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012…地址姓名学号980010021数学×科目学号分数…数学001001288数学001001374语文001001267语文001001381数学001001698主表（父表）参照表（子表）参照完整性约束举例1、创建表的同时创建外键语法格式：

2、对已有表创建外键语法格式：CREATETABLE表名(列名,...）|[外键定义]ALTERTABLE子表表名ADD[外键定义][外键定义]语法格式：FOREIGNKEY(外键)REFERENCES父表表名[(列名[(长度)][ASC|DESC],...)][ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]参照完整性约束说明Xs表（父表）学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Cj表（子表）RESTRICT：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。如要当要删除xs表中081102记录时，因为cj中还有081102记录，拒绝对xs表的删除操作。CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行。如从xs表更新081102学号为801102时自动更新cj表中学号为081102行为801102。参照完整性约束说明Xs表（父表）学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Cj表（子表）SETNULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。当从xs表删除081103行时，设置cj表中081103项为NULL。NOACTION：NOACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样。SETDEFAULT：作用和SETNULL一样，只不过SETDEFAULT是指定子表中的外键列为默认值。如果没有指定动作，两个参照动作就会默认地使用RESTRICT。参照完整性约束举例【例】创建book_ref表，所有的book_ref表中图书编号都必须出现在Book表中，假设已经使用图书编号列作为Book表主键。CREATETABLEbook_ref(图书编号varchar(20)NULL,

书名varchar(20)NOTNULL,

出版日期dateNULL,

PRIMARYKEY(书名),

FOREIGNKEY(图书编号)

REFERENCESBook(图书编号)

ONDELETERESTRICT

ONUPDATERESTRICT)ENGINE=INNODB;参照完整性约束举例【例】创建带有参照动作CASCADE的book_ref1表。CREATETABLEbook_ref1(

图书编号

varchar(20)NULL,

书名varchar(20)NOTNULL,

出版日期dateNULL,PRIMARYKEY(书名),FOREIGNKEY(图书编号)REFERENCESBook(图书编号)ONUPDATECASCADE)ENGINE=INNODB;这个参照动作的作用是在主表更新时，子表产生连锁更新动作，有些人称它为“级联”操作。举例说，如果book表中有一个图书编号为“TP.2525”的值修改为“TP.2525-1”，则book_ref1表中的“图书编号”列中的值“TP.2525”相应地改为“