《数据库系统与应用技术》课件 第3章 MySQL数据定义功能

第3章MySQL数据定义功能主讲:何广赢时间:2026/01/01Contents目录3.1数据库MySQL简介·存储引擎·创建·查看·修改·删除3.2数据表表的基本概念·创建·查看·修改·删除·完整性约束3.3索引索引概述·分类·设计原则·查看·创建·删除3.4视图视图概述·创建·查看·修改·更新数据·删除3.5本章小结核心知识点回顾与学习建议3.1数据库013.1.1MySQL数据库简介Workbench工作界面安装并配置好MySQL8.0数据库后，使用自带的Workbench操作数据库。打开Workbench连接已配置好的远程数据库，工作界面包含以下几个核心模块：Navigator模块数据库对象导航和管理Information模块数据库信息和状态显示SQL语句文件编辑器编写和执行SQL语句OutPut模块执行结果和日志输出系统默认数据库打开左侧导航Schemas后，系统默认使用的数据库包括：information_schema存储数据库元数据信息mysql存储用户权限和系统信息performance_schema存储性能监控数据sakila示例数据库（电影租赁）sys简化性能监控视图world示例数据库（世界城市）数据定义语言DDL数据定义语言DDL，主要用于维护存储数据的结构，包括数据库、表、索引、视图等。DDL中包含几个核心指令：CCREATE创建数据库和数据库对象DDROP删除数据库、表、索引、视图AALTER修改数据库表的定义及属性3.1.2存储引擎概述存储引擎存储引擎决定了表在计算机中的存储方式。存储引擎定义了在数据库中存储数据、为存储的数据建立索引和更新、查询数据等技术的实现方法。在关系数据库中的数据是以表的形式存储的，因而存储引擎也可以称为表的类型。MySQL的核心就是存储引擎，MySQL提供了多种存储引擎，用户可以根据不同的需求选择不同的存储引擎查看存储引擎可以通过SHOWENGINES命令查看MySQL支持的所有存储引擎：SHOWENGINES;--查看MySQL支持的所有存储引擎MySQL支持的存储引擎IInnoDB默认存储引擎，支持事务MMyISAM高速查询，不支持事务MMEMORY内存存储，速度快以及CSV、ARCHIVE、BLACKHOLE、FEDERATED、PERFORMANCE_SCHEMA等存储引擎信息存储引擎在MySQL中可以从是否支持、解释说明、是否支持事务处理、所支持的分布式是否符合XA规范、是否支持保存点等方面进行描述3.1.2存储引擎对比分析特性InnoDBMyISAMMEMORY事务安全✓支持✗不支持✗不支持存储限制64TB256TB(理论)取决于max_heap_table_size锁机制行锁表锁表锁空间使用高低低内存使用高低高对外键的支持✓支持✗不支持✗不支持批量插入速度低高高数据可压缩✗不支持✓支持✗不支持InnoDB事务处理应用首选：要求较高的事务处理时选择InnoDB存储引擎MyISAM频繁查询场景：表会被频繁查询时选择MyISAM存储引擎MEMORY临时查询应用：用于查询的临时表可选择MEMORY存储引擎3.1.3创建数据库CREATEDATABASE语法--创建数据库的基本语法CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name[[DEFAULT]CHARACTERSETcharset][[DEFAULT]COLLATEcollation];1{}必选项大括号中用分隔符|隔开的内容为必选项，必选其中的一项2[]可选项中括号中的内容为可选项，可以根据需要选择使用3IFNOTEXISTS若数据库名已存在，带上此选项则不执行任何操作，避免报错4CHARACTERSET&COLLATE指定默认的字符集和校对规则，控制字符存储和比较方式MMS医疗信息管理数据库在本节中，将创建一个含有患者表P(Patients)、医生表D(Doctors)、预约表A(Appointments)等医疗管理数据库MMS。案例背景MMS数据库是本书的案例数据库，后续的相关操作都将基于此数据库进行演示。创建实例--例3.1：创建MMS数据库CREATEDATABASEIFNOTEXISTSMMSDEFAULTCHARACTERSETgb2312DEFAULTCOLLATEgb2312_chinese_ci;数据库名：MMS（医疗信息管理数据库）字符集：GB2312（支持中文）校对规则：gb2312_chinese_ci（中文不区分大小写）3.1.4查看与选择数据库使用SHOW语句查看数据库使用SHOW可查看当前MySQL中已存在的数据库，这是了解数据库服务器中已有数据库的基本操作。SHOWDATABASES;--查看已有数据库列表执行效果执行SHOWDATABASES命令后，MySQL会返回一个包含所有数据库名称的列表，包括系统数据库和用户创建的数据库。选择数据库当数据库创建完成以后，该数据库不会自动成为当前数据库，需要使用USE命令指定当前数据库。USEMMS;--将MMS作为当前数据库操作说明执行以上代码后，进入你刚才所建立的数据库MMS。后续的所有表操作都将在此数据库中进行。3.1.5修改数据库ALTERDATABASE语法--修改数据库的语法格式ALTER{DATABASE|SCHEMA}[db_name]MODIFYNAME=new_name[[DEFAULT]CHARACTERSETcharset][[DEFAULT]COLLATEcollation];1数据库名称可省略省略表示修改当前默认的数据库2MODIFYNAME修改数据库为新名称3字符集和校对规则与创建数据库语句相同的作用修改实例--例3.4：修改MMS数据库ALTERDATABASEMMSDEFAULTCHARACTERSETutf8DEFAULTCOLLATEutf8_general_ci;参数说明数据库MMS字符集utf8（支持更多字符）校对规则utf8_general_ci（不区分大小写）注意事项修改数据库不会影响到已有的表数据，但新创建的表将使用新的字符集和校对规则3.1.6删除数据库DROPDATABASE语法--删除数据库的语法格式DROP{DATABASE|SCHEMA}[IFEXISTS]db_name;当数据库不再使用时，可以删除数据库。删除数据库时，数据库中的所有表、数据、索引等都将被永久删除，此操作不可逆，请谨慎使用。IFEXISTS选项若数据库不存在，带上此选项则不执行任何操作，避免报错删除实例--例3.5：删除MMS数据库DROPDATABASEMMS;--查看现有数据库验证SHOWDATABASES;验证删除结果执行SHOWDATABASES命令后，从返回的数据库列表可知，MMS数据库已被成功删除，列表中已经没有名为MMS的数据库了。删除影响删除数据库会同时删除该数据库中的所有表、数据、索引、视图等对象，且无法恢复。3.2数据表的定义与完整性约束023.2.1表的基本概念在日常生活中，表是经常使用的一种表示数据及其关系的形式，表必须建立在某一数据库中，不能单独存在，它是数据库存放数据的对象。在关系数据库中，每一个关系都可使用一张二维表，表中的数据的组织成类似于Excel表格，由行、列、表头构成。使用表来存储和操作数据的逻辑结构，表是数据库中最重要的数据对象。1表结构每张表具有一定的结构，表结构包含一组固定的列，列由列名、数据类型、长度、允许空值、键、默认值等组成2记录每个表包含若干行数据，表中的一行成为一个记录（Record），记录是表中的完整数据单元3字段表中每个列称为字段（Field），每个记录由若干个数据项（列）构成，构成记录的每个数据项就称为字段4空值空值（NULL）表示未知、不可用或以后添加的数据，与空字符串或0不同5主键如果表中记录的某一列或列组合能唯一标识记录，则该列或列组合成为候选键。若表中有多个候选键，则选择其中一个为主键（PRIMARYKEY）6默认值表中插入数据时，当没有明确给出某列的值，系统为此列指定一个值。在MySQL中，默认值使用关键字DEFAULT完整性约束完整性约束条件包括实体完整性约束（PRIMARYKEY、UNIQUE等）、参照完整性约束（FOREIGNKEY）和用户定义完整性约束（NOTNULL、DEFAULT、CHECK约束等）列列级完整性约束直接定义在列上，直接跟在列定义之后，用空格分开，无需指定列名NOTNULL/NULLUNIQUEDEFAULTCHECKPRIMARYKEYFOREIGNKEY注意：NOTNULL/NULL和DEFAULT只能用于列级约束表表级完整性约束与列的定义相互独立，不包含在列定义中，必须指出要约束的列的名称PRIMARYKEYFOREIGNKEYUNIQUECHECK常用于对多个列一起进行约束3.2.1列级与表级完整性约束列级完整性约束列级完整性约束是直接定义在列上，直接跟在列定义之后，用空格分开，无需指定列名。列级约束主要有NOTNULL/NULL、UNIQUE、DEFAULT、CHECK、PRIMARYKEY、FOREIGNKEY等。--列级约束示例CREATETABLEexample(idINTPRIMARYKEY,--列级主键约束nameVARCHAR(50)NOTNULL,--列级非空约束emailVARCHAR(100)UNIQUE--列级唯一约束);只能用于列级的约束NOTNULL指定列值不能为空DEFAULT为列指定默认值特点简洁直观，直接在列后定义适用于单一列的约束定义不能约束多个列的组合表级完整性约束表级完整性约束与列的定义相互独立，不包含在列定义中，通常可以约束表中一个或任意多个列，与定义用逗号(,)分开，必须指出要约束的列的名称。常用于对多个列一起进行约束。--表级约束示例CREATETABLEexample(idINT,nameVARCHAR(50),CONSTRAINTpk_examplePRIMARYKEY(id)--表级主键约束);可用于表级的约束PRIMARYKEYFOREIGNKEYUNIQUECHECK表级约束的优势可以为约束命名，便于后续管理可以约束多个列的组合更清晰的约束定义方式3.2.2创建表-语法详解CREATETABLE基本语法--创建表的基本语法CREATE[TEMPORARY]TABLE[IFNOTEXISTS]table_name(column_definition1[column_level_constraints1],column_definition2[column_level_constraints2],...,column_definitionN[column_level_constraintsN],[table_level_constraints])[table_options];1指定数据库数据表属于数据库，在创建表之前应使用USE语句指定创建数据库2IFNOTEXISTS只有该表目前不存在时才执行CREATETABLE操作，避免表已存在无法再新建的错误3表名命名规则名称必须符合标识符的命名规则，不区分大小写，不能使用SQL语言中的关键字列定义格式--列定义格式column_namedata_type[NOTNULL|NULL][DEFAULTdefault_value][AUTO_INCREMENT][UNIQUE[KEY]|[PRIMARYKEY]][COMMENT'string'][reference_definition]NOTNULL|NULL：指定该列为非空或者允许为空DEFAULT：为列指定默认值AUTO_INCREMENT：设置自增属性，只有整型列才能设置UNIQUEKEY：设置该列为唯一性约束PRIMARYKEY：设置该列为主键约束table_options--表选项格式[ENGINE=engine_name][DEFAULTCHARSET=charset_name][COLLATE=collation_name]ENGINE：存储引擎CHARSET：默认字符集COLLATE：校对规则3.2.2创建表实例-Patients表Patients患者表结构--创建Patients表的SQL语句CREATETABLEPatients(PatientIDCHAR(4)PRIMARYKEY,PatientNameCHAR(8)NOTNULL,DateOfBirthDATE,GenderCHAR(1),PhoneNumberVARCHAR(15),AddressVARCHAR(50));PatientID-主键CHAR(4)类型，使用PRIMARYKEY定义为主键，唯一标识每位患者PatientName-非空CHAR(8)类型，使用NOTNULL定义为非空，确保每位患者都有姓名其他字段-无约束DateOfBirth、Gender、PhoneNumber、Address等字段允许为空表3.2数据结构列名数据类型约束条件PatientIDCHAR(4)主键PatientNameCHAR(8)非空DateOfBirthDATE无约束GenderCHAR(1)无约束PhoneNumberVARCHAR(15)无约束AddressVARCHAR(50)无约束设计要点主键选择：PatientID作为主键，确保每位患者唯一标识非空约束：PatientName使用NOTNULL，确保患者姓名必填灵活性：其他字段允许为空，提供数据录入的灵活性3.2.2创建表实例-Doctors表Doctors医生表结构--创建Doctors表的SQL语句CREATETABLEDoctors(DoctorIDCHAR(4)PRIMARYKEY,DoctorNameCHAR(8)NOTNULL,GenderCHAR(1)CHECK(Genderin('F','M')),SpecializationVARCHAR(100),WorkingYearsINT);CHECK约束详解GenderCHAR(1)CHECK(Genderin('F','M'))这行代码定义了一个检查约束，限制Gender列的取值只能是'F'（Female）或'M'（Male），确保性别数据的有效性。--CHECK约束语法CHECK(GenderIN('F','M'))DoctorID-主键CHAR(4)类型，主键约束DoctorName-非空CHAR(8)类型，非空约束表结构列名数据类型约束条件DoctorIDCHAR(4)主键DoctorNameCHAR(8)非空GenderCHAR(1)取值范围：['F','M']SpecializationVARCHAR(100)无约束WorkingYearsINT无约束数据完整性保障主键约束：DoctorID唯一标识每位医生非空约束：DoctorName确保医生姓名必填检查约束：Gender限制为'F'或'M'，保证数据有效性3.2.2复制已有表复制表语法使用直接复制数据库中已有表的结构和数据来快速创建一个表。这种方式可以快速创建与现有表结构相同的新表。--复制表的语法格式CREATETABLE[IFNOTEXISTS]table_name{[(]LIKEold_table_name[)]|AS(SELECT_statement)};LIKE方式使用LIKE关键字创建一个与"源表名"相同结构的新表，但是表的内容不会复制--只复制结构CREATETABLEnew_tableLIKEold_table;AS方式使用AS可以复制表的内容，但是索引和完整性约束不会复制--复制结构和数据CREATETABLEnew_tableASSELECT*FROMold_table;复制实例--例3.7：复制Patients表USEMMS;CREATETABLEPatients1ASSELECT*FROMPatients;操作说明首先使用USEMMS选择数据库，然后使用AS方式创建Patients1表，复制Patients表的所有数据两种方式对比LIKE方式复制表结构不复制数据保留索引和约束AS方式复制表结构和数据不复制索引和约束3.2.3查看表-查看表名与表结构查看表名使用SHOWTABLES可查看数据库中的所有表名，这是了解数据库中包含哪些表的基本操作。SHOWTABLES;--查看当前数据库中的所有表指定数据库也可以查看指定数据库中的表：SHOWTABLESFROMdb_name;查看表的基本结构使用SHOWCOLUMNS或DESCRIBE/DESC可以查看表的基本结构，包括列名、数据类型、长度、是否为空、是否是主键、是否有默认值等详细信息。SHOWCOLUMNSFROMtable_name;--查看指定表的列信息DESCRIBEtable_name;--简写形式：DESCtable_name;功能说明SHOWCOLUMNS和DESCRIBE功能完全相同，DESCRIBE更简洁，SHOWCOLUMNS更符合SQL标准3.2.3查看表示例例3.9：查看Patients表结构--使用SHOWCOLUMNS查看SHOWCOLUMNSFROMPatients;返回信息返回表中每个列的详细信息，包括Field（列名）、Type（数据类型）、Null（是否允许为空）、Key（键类型）、Default（默认值）、Extra（额外信息）查看表的创建语句--例3.10：查看完整创建语句SHOWCREATETABLEPatients;使用SHOWCREATETABLE可以查看创建表的完整SQL语句，包括所有约束、索引等定义信息示例输出--SHOWCOLUMNS输出示例+-------------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------------+-------------+------+-----+---------+-------+|PatientID|char(4)|NO|PRI|NULL|||PatientName|char(8)|NO||NULL|||DateOfBirth|date|YES||NULL|||Gender|char(1)|YES||NULL|||PhoneNumber|varchar(15)|YES||NULL|||Address|varchar(50)|YES||NULL||+-------------+-------------+------+-----+---------+-------+字段说明Field:列名Type:数据类型Null:是否允许为空(NO/YES)Key:键类型(PRI=主键)Default:默认值Extra:额外信息3.2.4修改表-语法详解ALTERTABLE基本语法由于应用环境和应用需求的变化，修改表是对数据库中已经存在的表做进一步的结构修改和调整。修改表使用ALTERTABLE语句。--ALTERTABLE基本语法ALTERTABLEtable_nameADD[COLUMN]col_nametype[constraints]|/*添加列*/CHANGECOLUMNold_colnew_coltype|/*列重命名*/ALTERCOLUMNcol_name{SETDEFAULTval|DROPDEFAULT}|/*修改默认值*/MODIFYCOLUMNcol_nametype|/*修改列类型*/DROPCOLUMNcol_name|/*删除列*/RENAMETOnew_table_name|/*重命名表*/CHARACTERSETcharset|/*修改字符集*/DROPCONSTRAINTconstraint_name;ADD方式用于增加新列和完整性约束DROP方式用于删除列和完整性约束修改列的子句ALTERCOLUMN修改或删除表中指定列的默认值CHANGECOLUMN同时修改表中指定列的名称和数据类型MODIFYCOLUMN修改表中指定列的数据类型和位置ALTERCOLUMN限制不能改变列名不能将含空值的列改为NOTNULL不能修改已创建索引的列若列中有数据，不能减少宽度或改变类型3.2.4修改表实例-修改表名与添加列修改表名--例3.11：将Patients表名改成Patients1ALTERTABLEPatientsRENAMETOPatients1;语法说明使用RENAMETO子句可以将表重命名。此外，还可以在MySQLWorkbench左侧管理窗格中，选择表右键中选择AlterTable，在打开的窗口中修改表名。--简写形式ALTERTABLEold_nameRENAMEnew_name;添加列--例3.12：在Patients表中添加列ALTERTABLEPatientsADDCOLUMNcommentvarchar(10);操作说明在MMS数据库的Patients表中最后位置增加一列comment，数据类型为varchar(10)。添加列后可以再使用DESC语句查看表的结构验证修改结果。--查看修改后的表结构DESCPatients;3.2.4修改表实例-修改列与删除列使用CHANGE修改列--例3.13：修改comment列ALTERTABLEPatientsCHANGECOLUMNcommentcommvarchar(20)DEFAULT'普通';修改内容列名从comment修改为comm数据类型从varchar(10)改为varchar(20)设置默认值为'普通'使用MODIFY修改列--使用MODIFY修改数据类型ALTERTABLEPatientsMODIFYCOLUMNcommvarchar(30);除了使用CHANGE可以修改列名和数据类型，可以使用MODIFY关键字只修改数据类型，保持列名不变。MODIFY不能修改列名，但可以修改列的位置。删除列--例3.14：删除comm列ALTERTABLEPatientsDROPCOLUMNcomm;注意事项删除列时，该列中的所有数据都将被永久删除，且此操作无法恢复，请谨慎操作。三种修改方式对比CHANGECOLUMN可修改列名和数据类型MODIFYCOLUMN只能修改数据类型和位置ALTERCOLUMN只能修改或删除默认值3.2.5删除表DROPTABLE语法--删除表的语法格式DROPTABLE[IFEXISTS]table_name[,table_name2,...];当数据表不需要的时候，可以将其删除。删除表时表的结构定义、数据、表的索引约束等都被删除，此操作不可逆，请谨慎操作。注意事项只能删除自己建立的表，不能删除其他用户所建的表。删除表会同时删除该表中的所有数据、索引、约束等对象。删除实例--例3.15：删除Patients1表DROPTABLEPatients1;执行结果执行DROPTABLE语句后，Patients1表及其所有数据和索引都被永久删除。可以使用SHOWTABLES命令验证表是否已被删除。IFEXISTS选项如果指定IFEXISTS选项，当要删除的表不存在时，MySQL不会报错，而是发出一个警告后继续执行。--安全的删除方式DROPTABLEIFEXISTStable_name;3.2.6数据的完整性约束概述数据完整性数据完整性是衡量数据库质量的标准之一，使用该机制防止无效的数据进入数据表。在MySQL中数据的完整性包括实体完整性约束、参照完整性约束和用户自定义约束等。重要性合理使用数据的完整性约束，可以提高数据库的可靠性、可维护性和可扩展性，保证数据的准确性和一致性。1实体完整性约束实体完整性要求表中有一个主键，其值不能为空且唯一标识对应的记录，又称行级完整性PRIMARYKEY主键约束UNIQUE唯一性约束2参照完整性约束参照完整性保证被参照表中的数据与参照表中数据的一致性，又称引用完整性FOREIGNKEY外键约束3用户自定义约束通过检查约束、非空约束来实现，限制输入值，保证数据的完整性CHECK检查约束NOTNULL非空约束3.2.6实体完整性约束-主键约束主键约束特点唯一性：一个表中只能有一个PRIMARYKEY约束非空性：主键列不能取空值（NOTNULL）自动创建索引：创建主键约束时系统会自动产生PRIMARYKEY索引唯一标识：不允许列存在重复值，唯一标识每条记录表级主键约束实例--例3.16：表级约束方式定义主键CREATETABLEPatients1(PatientIDchar(4),PatientNameCHAR(8),DateOfBirthDATE,GenderCHAR(1),PhoneNumberVARCHAR(15),AddressVARCHAR(50),CONSTRAINTPK_PatientIDPRIMARYKEY(PatientID));在表级上定义主键约束，并指定约束名为PK_PatientID，以便后续需要对完整性约束进行修改或删除时更加方便。列级主键约束--列级约束方式CREATETABLEPatients(PatientIDchar(4)PRIMARYKEY,...);直接在列定义后添加PRIMARYKEY关键字，简洁直观，适用于单列主键的情况。两种方式对比列级约束简洁直观适用于单列主键无法命名约束表级约束可以为约束命名支持多列组合主键便于后续管理3.2.6实体完整性约束-唯一性约束唯一性约束特点为保证一个表中非主键列不输入重复值，可在列定义UNIQUE约束，称为唯一性约束。创建时可以有多个UNIQUE约束，可以取空值，创建时系统自动产生UNIQUE索引，不允许存在重复值。与主键的相同点不允许重复值自动创建索引与主键的不同点可以有多个UNIQUE约束可以取空值不强制非空--例3.17：为PatientName创建唯一约束ALTERTABLEPatients1ADDCONSTRAINTUK_PatientNameUNIQUE(PatientName);ALTERTABLE创建唯一约束--基本语法ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]UNIQUE(column_name);说明为PatientName列创建唯一性约束，并指定唯一约束名UK_PatientName。运行后使用DESC语句可查看结果。删除唯一性约束在ALTERTABLE语句下删除唯一性约束时，MySQL实际上使用的是DROPINDEX子句删除唯一性约束。--删除唯一约束ALTERTABLEtable_nameDROPINDEXconstraint_name;3.2.6参照完整性约束-外键概念参照完整性参照完整性保证被参照表中的数据与参照表中数据的一致性，又称引用完整性。外键是一个表中的一列或多列的组合，它不是这个表的主键，但它对应另外一个表的主键。外键的主要作用外键主要保证数据引用的完整性，确保引用表中的外键值必须存在于被参照表的主键中，或者为NULL。相关术语参照表外键所在的表称为参照表（子表），包含指向其他表的外键列被参照表相关联的主键所在的表称为被参照表（父表），被外键引用的表参照完整性规则参照完整性规则是外键跟主键之间的引用规则，即外键的取值为空值，或等于被参照表中某一主键的值。规则说明外键可以为NULL，表示该行数据暂时没有关联外键如果有值，则必须是父表中存在的主键值保证引用关系的有效性定义外键应遵循的原则1被参照表必须已经存在或是当前正在创建的表2必须为被参照表定义主键或唯一性约束3主键不能有空值，但外键可以出现空值4外键对应列的数目必须和主键对应的数目相同5外键对应列的数据类型必须和主键对应的数据类型保持一致3.2.6参照完整性约束-外键创建与删除创建外键约束--表级外键约束语法[CONSTRAINTconstraint_name]FOREIGNKEY(column1[,column2,...])REFERENCESparent_table(column1[,column2,...])--例3.18：创建Appointments表及外键CREATETABLEAppointments(AppointmentIDINTAUTO_INCREMENTPRIMARYKEY,PatientIDchar(4),DoctorIDchar(4),ConditionsVARCHAR(50),AppointmentDateDATE,AppointmentTimeTIME,FOREIGNKEY(PatientID)REFERENCESPatients(PatientID),CONSTRAINTFK_DoctorIDFOREIGNKEY(DoctorID)REFERENCESDoctors(DoctorID));为Appointments表创建两个外键：PatientID引用Patients表的PatientID，DoctorID引用Doctors表的DoctorID。其中DoctorID外键指定了约束名FK_DoctorID。删除外键约束--删除外键约束语法ALTERTABLEtable_nameDROPFOREIGNKEYconstraint_name;--例3.19：删除FK_DoctorID外键ALTERTABLEAppointmentsDROPFOREIGNKEYFK_DoctorID;可以使用修改表的方式重新在Appointments表上定义外键约束(FK_DoctorID)。修改表时创建外键--修改表时添加外键ALTERTABLEtable_nameADD[CONSTRAINTname]FOREIGNKEY(col)REFERENCESparent_table(col);3.2.6用户自定义完整性约束-检查约束检查约束（CHECK）用户定义完整性约束是通过检查约束来实现，检查约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据的完整性。1使用CREATETABLE时创建--语法格式CHECK(constraint_equation)2使用ALTERTABLE时创建--语法格式ALTERTABLEtable_nameADD[CONSTRAINTname]CHECK(condition);3删除检查约束--语法格式ALTERTABLEtable_nameDROPCHECKconstraint_name;检查约束实例--例3.20：为Gender列设置检查约束ALTERTABLEPatientsADDCONSTRAINTCK_GenderCHECK(GenderIN('F','M'));约束说明为Patients表中Gender列设置检查约束，取值只有"F"或"M"，约束名指定为CK_Gender。这样当插入或更新数据时，如果Gender列的值不是'F'或'M'，操作将被拒绝。应用场景限制数值范围（如年龄>0）限制取值集合（如性别、状态）确保数据符合业务规则提高数据质量和可靠性3.2.6用户自定义完整性约束-非空约束非空约束（NOTNULL）非空约束是指列值不能为空。空值指"不存在"、"未知"、"无意义的值"，与空字符串或0不同。NOTNULL约束确保该列在插入或更新数据时必须有值。定义方式MySQL中可以使用CREATETABLE或者ALTERTABLE语句定义非空约束，定义列的时候直接在后面加上关键字NOTNULL作为限定词。创建时定义非空约束--例3.6：创建Patients表时定义非空CREATETABLEPatients(PatientIDCHAR(4)PRIMARYKEY,PatientNameCHAR(8)NOTNULL,...);在创建Patients表时，在PatientName列后添加了非空约束以确保该列不能为空值。Doctors表的非空约束--Doctors表创建语句CREATETABLEDoctors(DoctorIDCHAR(4)PRIMARYKEY,DoctorNameCHAR(8)NOTNULL,...);同样在Doctors表中，DoctorName列也使用了NOTNULL约束，确保医生姓名不能为空。应用场景必填字段（如姓名、用户名）关键业务信息防止数据不完整3.3索引033.3.1索引概述索引的概念在日常生活中我们会经常使用索引，例如图书的目录、词典的检索表等。借助索引，人们能够很快地找到需要的东西。索引是对表中一列或多列的值进行排序，建立索引表的一种数据结构，使用索引可以快速访问表中的特定记录。工作原理索引文件只有两个列：一个是排序后索引列，用于快速查找；另一个是该记录在数据表中的记录号，用于定位读取。索引的自动管理索引一旦创建，将由数据库自动管理和维护。比如，向数据表中插入、修改和删除数据时，数据库自动在索引中作出相应的修改。使用说明在编写SQL查询语句时，具有索引的表与普通没有索引的表无任何差别，索引只是提供了一种快速访问指定记录的方法。索引的作用1加快数据检索速度这是索引最重要的作用，通过索引可以快速定位到符合条件的数据2保证列值的唯一性唯一索引可以确保列中没有重复值3提高排序和分组速度在使用ORDERBY和GROUPBY时可以提高执行速度4查询优化依靠索引起作用查询优化器依靠索引来提高查询性能两种检索方式全表全表扫描，逐行检查索引使用索引快速定位3.3.2索引的分类-按用途分普通索引INDEX普通索引是MySQL中的基本索引类型。使用关键字KEY或INDEX定义，可以创建在任何数据类型中，而且它允许索引列有重复值和空值。特点：最基本的索引类型，没有任何限制唯一索引UNIQUE唯一索引与普通索引不同的是索引列值不能重复、唯一，可以是空值。特点：值唯一，可有空值主键索引PRIMARYKEY主键索引是一种特殊的唯一索引。创建表时定义主键后自动创建，一张表只能有一个主键索引，而且索引列值不能为空。特点：特殊的唯一索引，不能为空全文索引FULLTEXT全文索引是在定义索引的列上按照值的全文查找，允许索引列值重复和空值，但是它只能创建在CHAR、VARCHAR或TEXT等字符类型的列上。特点：用于全文搜索，支持文本字段空间索引SPATIAL空间索引是对空间数据类型GEOMETRY、POINT、LINESTRING、POLYGON等列上建立的索引，但是建立空间索引的列必须NOTNULL。特点：用于空间数据，列必须非空使用建议根据实际需求选择合适的索引类型。普通索引用于基本加速查询；唯一索引用于保证数据唯一性；主键索引自动创建；全文索引用于文本搜索；空间索引用于地理信息数据。3.3.2索引的分类-按索引个数与物理顺序按索引的个数分单列索引单列索引指在表中单一列上创建的索引。它可以是普通索引、唯一索引或者全文索引，一个表上可以建立多个单列索引，但一个单列索引只包含表中的一个列。特点：确保该索引只对应一个列即可多列索引多列索引指在表中多个列上创建的索引，也称为组合索引或复合索引。需要注意的是只有在查询条件中使用了这些列中的第一个列时，该索引才会被使用。特点：遵循最左前缀原则按物理顺序分聚簇索引聚簇索引是数据存储的物理顺序，保证索引值相近的记录行所存储的物理位置也相近。一张表只能有一个聚簇索引。特点：索引顺序与物理顺序相同非聚簇索引非聚簇索引顺序与数据表的物理顺序无关。非聚簇索引就是普通索引，仅对列创建相应的索引，不影响整个数据表的物理存储顺序。特点：索引顺序与物理顺序无关组合索引使用建议对于组合索引，查询条件中必须包含索引的第一个列（最左列），否则索引不会被使用。例如，在(col1,col2,col3)上创建的组合索引，只有在查询条件中包含col1时才会使用该索引。3.3.3索引设计原则索引设计的五项原则使用索引可以提高系统的性能，加快检索速度，但是也存在一定的缺点，比如增加存储空间、降低更新表中数据的速度等。因而在使用时应考虑以下原则：1限制表中索引的数量对经常查询的列应该建立索引，但要避免对不必要的列建立索引。过多的索引会占用大量存储空间，并降低更新操作的速度。2避免对经常更新的表建立过多的索引需要经常更新数据的表应该避免建立过多的索引，并且索引中的列要尽可能少，这样系统消耗在索引维护上的代价才小。3数据量小的表不建议使用索引对于数据量很小的表，全表扫描可能比使用索引更快，因为使用索引还需要额外的查找开销。4避免在重复率大的列上建索引在取值重复率较大的列上不要建立索引。例如"Patients表"中的"Gender"列，其取值基本只有"男"和"女"两个值，这样的列就无须建立索引。原因在这种低选择性的列上创建索引，索引的过滤效果很差，几乎无法提高查询效率5为排序分组连接查询的列建索引在关系数据中进行排序、分组和连接查询时，需要进行大量的查找比较运算，所以应该为频繁进行排序或分组的列和经常进行连接查询的列创建索引。适用场景•ORDERBY子句中频繁使用的列•GROUPBY子句中频繁使用的列•JOIN操作中连接条件的列3.3.4查看索引查看索引的三种方法在MySQL中使用SHOWCREATETABLE语句可以查看表的结构，同时也能查看表中存在哪些索引。使用EXPLAIN语句可以查看在执行SQL查询时索引的使用情况。使用SHOWINDEXFROM可以查看索引相关信息。--查看索引的语法SHOW{INDEX|INDEXS|KEYS}{FROM|IN}table_name[{FROM|IN}database_name];查看实例--例3.21：查看Patients表的索引SHOWINDEXFROMPatients;返回信息说明Table:表名Key_name:索引名Column_name:列名Collation:排序方式(A/D)Cardinality:基数(唯一值数量)Sub_part:前缀长度3.3.5创建索引-三种方式概述1CREATEINDEX使用CREATEINDEX语句在已经存在的表上创建索引，这是最常用的创建索引的方式。--基本语法CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column);适用于已有表语法清晰直观最常用的方式2ALTERTABLE使用ALTERTABLE语句在已经存在的表上创建索引，与CREATEINDEX作用完全相同。--基本语法ALTERTABLEtable_nameADD[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name(column);与CREATEINDEX等效可同时修改表结构便于批量操作3CREATETABLE在使用CREATETABLE语句创建表时同时创建索引，适合在创建表时就规划好索引的情况。--基本语法CREATETABLEtable_name(column1type,INDEXindex_name(column));创建表时一并创建便于统一管理适合初始设计3.3.5使用CREATEINDEX创建索引CREATEINDEX语法详解--CREATEINDEX基本语法CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name[length])[ASC|DESC][,...];UNIQUE|FULLTEXT|SPATIAL可选项，指定索引类型index_name索引名，在表中必须唯一table_name要建立索引的表名称column_name要创建索引的列名length可选项，使用列前多少个字符ASC|DESC可选项，升序或降序，默认ASC--例3.22：创建降序普通索引CREATEINDEXIDX_PatientIDONPatients(PatientIDDESC);执行说明在MMS数据库中Patients表的PatientID创建一个名为IDX_PatientID，按降序排序的普通索引。验证结果运行以上代码后并使用SHOWINDEXFROMPatients语句查看所创建的索引。图中第1行是创建主键约束时，系统默认创建的一个唯一索引，第2行的IDX_PatientID是用户创建的索引，collation列值为D表示降序排序。注意事项索引名在表中必须唯一可以指定升序(ASC)或降序(DESC)可以为前缀列创建索引适用于已有表3.3.5使用ALTERTABLE创建索引ALTERTABLE创建索引使用ALTERTABLE语句在已经存在的表上创建索引，这种方式创建索引与使用CREATEINDEX语句的作用完全一样。--ALTERTABLE创建索引语法ALTERTABLEtable_nameADD[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]index_name(column_name[length][ASC|DESC]);实例--例3.23：使用ALTERTABLE创建索引ALTERTABLEPatientsADDINDEXIDX_PatientID(PatientIDDESC);这种方式创建索引与使用CREATEINDEX语句的作用完全一样，可以根据个人习惯和具体场景选择使用哪种方式。两种方式对比CREATEINDEX语法简洁，专注于索引创建ALTERTABLE可同时修改表结构，功能更强结论：两种方式功能等效，根据个人习惯选择3.3.5使用CREATETABLE创建索引CREATETABLE创建索引语法--创建表时创建索引的语法[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[alias](col_name[(length)][ASC|DESC])UNIQUE可选参数，表示创建唯一索引FULLTEXT可选参数，表示创建全文索引SPATIAL可选参数，表示创建空间索引INDEX|KEY创建索引的关键词，二者选一alias可选参数，用于给索引另取名ASC|DESC可选参数，升序或降序排列--例3.24：创建Doctors1表及组合索引CREATETABLEDoctors1(DoctorIDCHAR(4),DoctorNameCHAR(8),GenderCHAR(1),SpecializationVARCHAR(100),WorkingYearsINT,PRIMARYKEY(DoctorID,DoctorName),INDEXIDX_GS(GenderASC,SpecializationDESC));组合索引实例在MMS数据库中，创建新表Doctor1表，主键为DoctorID、DoctorName，同时在Gender、Specialization列上创建普通组合索引。组合索引特点创建的组合索引IDX_GS排序时，先按照Gender升序排序；若Gender值相同，再按照Specialization降序排序。--查看创建的索引SHOWINDEXFROMDoctors1;使用建议适合在创建表时就规划好索引可以同时创建主键、唯一索引和普通索引便于统一管理表的所有索引3.3.6删除索引删除索引的必要性由于索引会占用一定的系统资源，因此，为了避免影响数据库性能，应该及时删除不再使用的索引。MySQL中有两种方法删除索引。占用存储空间每个索引都需要占用磁盘空间影响更新速度增删改数据时需维护索引使用DROPINDEX删除索引--例3.25：删除IDX_PatientID索引DROPINDEXIDX_PatientIDONPatients;语法：DROPINDEXindex_nameONtable_name;使用ALTERTABLE删除索引--例3.26：删除IDX_GS组合索引ALTERTABLEDoctors1DROPINDEXIDX_GS;语法：ALTERTABLEtable_nameDROPINDEXindex_name;注意事项删除索引是不可逆操作删除后需要重新创建才能使用主键索引不能直接用DROPINDEX删除定期清理不再使用的索引3.4视图043.4.1视图概述视图的概念视图是从一个或几个基表（或视图）中导出的表，是一种虚表。用于产生视图的表叫作该视图的基表。它可以来源于一个或多个基表的行或列构成的子集，也可以是基表的统计汇总，或是视图与基表的组合。核心特点由SELECT语句定义的视图实际上并不存储真实数据，而是存储了查询逻辑。数据库中仅存放了视图的定义，不存放视图对应的数据。通过视图看到的数据实际只是执行视图中定义的数据查询命令而查询出的存放在基表中的数据。数据同步当基表中的数据发生变化，视图中的数据也就会随之改变。视图的数据是实时从基表查询得到的，保证了数据的一致性。说明视图不存储数据，每次查询视图时都会执行定义视图的SELECT语句视图的来源可以来源于一个或多个基表可以是基表的统计汇总可以是视图与基表的组合可以是行或列的子集3.4.1视图的功能与优点视图的三大功能1筛选数据筛选出基表中的频繁操作的数据供用户简单地按视图名访问。用户无需关心复杂的查询语句，只需要查询视图即可获得所需数据。2安全保护只提供必要的数据视图，防止未经许可的用户访问敏感数据。通过视图可以控制用户只能看到其有权查看的数据列和行。3逻辑抽象将多个物理数据表抽象为一个逻辑数据表。用户可以通过一个视图查询多个表的数据，而不需要了解表之间的复杂关系。视图的四大优点简化用户的操作视图能够简化用户的操作，用户只需查询视图而无需编写复杂的SQL语句多角度看待数据视图使用户能从多种角度看待同一数据，不同用户可以有不同的视图实现逻辑独立性视图能够实现数据库的逻辑独立性，当基表结构变化时视图可以保持不变提供安全保护视图能够对机密数据提供安全保护，限制用户访问敏感信息应用场景为不同部门创建不同视图隐藏表之间的复杂关联简化报表查询保护敏感数据列3.4.2创建视图CREATEVIEW语法--CREATEVIEW基本语法CREATE[ORREPLACE]VIEWview_name[(column_list)]ASSELECTstatement[WITHCHECKOPTION];1ORREPLACE可选项，若存在同名的视图时，原视图将被新创建的视图覆盖。该子句使得新建视图命令同时也具备了修改视图的功能。2column_list可选项，指定视图查询结果的列名，如果没有此选项，视图查询结果的列名和SELECT子句中的列名一致。3WITHCHECKOPTION使用该子句后，在更新视图时对新插入或修改的数据进行检查，确保视图数据的更新要使得视图定义里的查询语句中的WHERE子句的结果为"真"。实例--例3.27：创建性别为F的患者视图USEMMS;CREATEVIEWp_f_viewASSELECT*FROMPatientsWHEREGender='F';在MMS数据库中创建一个基于Patients表的Gender值为"F"信息的视图，视图名为p_f_view。说明视图定义存储在数据库中视图不存储实际数据查询视图时执行SELECT语句3.4.2创建视图实例-自定义列名方式一：在CREATEVIEW后指定列名--指定列名列表CREATEVIEWp_info_view(患者号,姓名,性别,出生日期,联系方式)ASSELECTPatientID,PatientName,Gender,DateOfBirth,PhoneNumberFROMPatients;在CREATEVIEW后指定列名列表，将视图中的列名设置为中文名称，方便用户理解和使用。方式二：在SELECT中使用别名--使用AS指定别名CREATEVIEWp_info_viewASSELECTPatientIDAS患者号,PatientNameAS姓名,GenderAS性别,DateOfBirthAS出生日期,PhoneNumberAS联系方式FROMPatients;在SELECT语句中使用AS关键字为列指定别名，这种方式更加灵活，可以在别名中使用表达式或函数。3.4.3查看视图-四种方法查看视图的四种方法在MySQL中，视图实际上是被当作特殊表来处理的，所以查看视图的方法与查看表的方法类似。--方法1：SHOWTABLESSHOWTABLES;--方法2：DESCDESCview_name;--方法3：SHOWTABLESTATUSSHOWTABLESTATUSLIKE'view_name';--方法4：SHOWCREATEVIEWSHOWCREATEVIEWview_name;四种方法说明SHOWTABLES查看数据库中的所有表和视图名称DESC查看视图的列信息（列名、数据类型等）SHOWTABLESTATUS查看视图的详细信息（引擎、版本、行数等）SHOWCREATEVIEW查看视图的创建代码和字符集3.4.3查看视图实例查看视图列表--查看所有表和视图SHOWTABLES;执行SHOWTABLES命令后，返回结果中会包含表和视图的名称。在MySQL中，视图被视为特殊表，所以可以用SHOWTABLES命令查看。--查看p_info_view的列信息DESCp_info_view;查看视图创建代码--查看视图的创建语句SHOWCREATEVIEWp_f_view;使用SHOWCREATEVIEW可以查看视图的完整创建代码，包括SELECT语句、字符集等信息。查询视图数据--查询p_f_view中的数据SELECT*FROMp_f_view;查询视图就像查询普通表一样，MySQL会自动执行视图定义中的SELECT语句并返回结果。3.4.4修改视图修改视图的两种方式在MySQL中，可以通过两种方式来修改视图。第一种是使用CREATEORREPLACEVIEW，如果视图不存在则创建，如果存在则覆盖原有视图。第二种是使用ALTERVIEW语句。1CREATEORREPLACE--语法CREATEORREPLACEVIEWview_nameASSELECT...;若存在同名视图则覆盖，否则创建新视图2ALTERVIEW--语法ALTERVIEWview_nameASSELECT...;修改已存在的视图定义修改实例--修改p_info_view视图CREATEORREPLACEVIEWp_info_viewASSELECTPatientID,PatientName,Gender,DateOfBirth,PhoneNumber,AddressFROMPatientsWHEREGender='F';--或使用ALTERVIEWALTERVIEWp_info_viewASSELECTPatientID,PatientName,Gender,DateOfBirth,PhoneNumber,AddressFROMPatientsWHEREGender='F';以上两种方式都将p_info_view视图修改为只包含女性患者的信息，并增加了Address列。使用建议CREATEORREPLACE更常用，可同时用于创建和修改ALTERVIEW只能修改已存在的视图修改视图不会影响基表的数据3.4.5利用视图更新数据通过视图更新数据的原理对视图的更新操作（INSERT、UPDATE、DELETE）最终会转换成对基表数据的更新操作。当通过视图更新数据时，实际上是在更新定义视图的基表中的数据。INSERT-插入数据通过视图向基表中插入新行数据UPDATE-更新数据通过视图修改基表中已有行的数据DELETE-删除数据通过视图删除基表中的行数据实例--例3.28：通过视图插入数据INSERTINTOp_info_view(PatientID,PatientName,Gender,DateOfBirth,PhoneNumber,Address)VALUES('P008','陈丽萍','F','1990-05-15',,'北京市朝阳区');通过p_info_view视图给Patients表新增一位名叫'陈丽萍'的女患者。执行该语句后，数据实际上被插入到Patients基表中。数据同步视图更新→基表更新基表变化→视图数据变化视图不存储数据，实时查询3.4.5视图更新限制与WITHCHECKOPTION视图更新的限制并非所有的视图都可以更新。如果视图包含以下情况，则无法将更新命令准确转换成对基表的更新：包含GROUPBY分组统计使用了GROUPBY、HAVING、DISTINCT等子句包含聚合函数如COUNT、SUM、AVG、MAX、MIN等包含UNION、UNIONALL多个SELECT语句的并集操作包含子查询复杂的嵌套查询结构限制实例--创建统计不同性别人数的视图CREATEVIEWp_cnt_viewASSELECTGender,COUNT(*)AScntFROMPatientsGROUPBYGender;--尝试更新会失败UPDATEp_cnt_viewSETcnt=10WHEREGender='F';由于p_cnt_view包含GROUPBY和COUNT聚合函数，无法通过该视图更新数据，因为无法确定如何修改基表中的具体行。WITHCHECKOPTION--语法CREATEVIEW...ASSELECT...WHEREconditionWITHCHECKOPTION;确保更新时数据仍满足WHERE条件防止插入不满足条件的数据保证视图数据的一致性3.4.6删除视图DROPVIEW语法--删除视图的语法格式DROPVIEW[IFEXISTS]view_name[,view_name2,...];当视图不再使用时，可以将其删除。删除视图只是删除视图的定义，不会影响基表中的数据。IFEXISTS选项若视图不存在，带上此选项则不执行任何操作，避免报错删除实例--例3.29：删除p_f_view视图DROPVIEWp_f_view;执行DROPVIEW语句后，p_f_view视图的定义被删除。可以使用SHOWTABLES命令验证视图是否已被删除。注意事项删除视图只是删除定义，不影响基表数据删除基表后，建立在该表上的视图不会自动删除删除基表后视图无法再使用，需要手动删除3.5本章小结053.5本章核心知识点回顾数据库数据库是存储在一起集中管理的相关数据的集合。通过CREATEDATABASE创建数据库，使用SHOWDATABASES查看，USE选择数据库，ALTERDATABASE修改，DROPDATABASE删除。存储引擎：选择合适的存储引擎（InnoDB、MyISAM、MEMORY）对数据库性能至关重要数据表表必须建立在某一数据库中，不能单独存在，它是数据库存放数据的对象。通过CREATETABLE创建表，使用SHOWTABLES、DESC查看，ALTERTABLE修改，DROPTABLE删除。设计要点：合理设计表结构和完整性约束是保证数据质量的基础完整性约束数据完整性是衡量数据库质量的标准之一，包括实体完整性、参照完整性和用户自定义完整性三大类，通过PRIMARYKEY、UNIQUE、FOREIGNKEY、CHECK、NOTNULL等约束实现。重要性：完整性约束保证数据的准确性和一致性索引索引是一种特殊的数据对象，可以提高对表中数据的访问