MySQL数据库基础PPT（高职）完整全套教学课件

MySQL数据库基础第1章数据库基础知识第2章数据设计第3章数据库及数据表的基本操作第4章数据更新第5章数据查询第6章数据视图第7章索引第8章数据库编程第9章数据安全第10章综合案例

第1章

数据库基础知识1.1基本概念1.2关系数据库1.3MySQL的安装与配置011.1.1数据与数据库1.1基本概念数据（Data）是对客观事物的符号记录，是按照一定规则排列组合的物理符号，可以是数字、文字、图像等。在计算机中，数据是指经过数字化后能被计算机处理的符号记录。数据是描述和管理信息的有效载体。数据库（Database）可以理解为存储数据的地方。在计算机中，数据库是指存储在计算机内，有组织、可共享的数据集合。在数据库中保存的是以一定方式存储在一起的相互关联的数据整体。数据库既保存了数据，也保存了数据与数据之间的相互关系。通过数据库对数据进行存储，可以方便地对数据进行查询、更新、添加和删除等操作。例如，将一个班的学生信息进行存储，可以使用如表1-1所示的结构1.1.2数据库系统1.1基本概念数据库系统（DatabaseSystem，DBS）通常由硬件、数据库、管理软件、用户4个部分组成，如图11所示。1.1.3数据库管理系统1.1基本概念数据库管理系统（DatabaseManagementSystem，DBMS）是位于用户与操作系统之间的一种数据管理软件。使用数据库管理系统可以创建、使用和维护数据库。数据库管理系统建立在操作系统的基础上，对数据库进行统一的管理和控制，以保证数据库的安全和完整性。数据库管理系统的主要功能包括以下几点。（1）数据定义。数据库管理系统提供了数据定义语言（DataDefinitionLanguage,DDL），用于定义数据库的结构、完整性约束条件和触发机制等。（2）数据操纵。数据操纵语言（DataManipulationLanguage,DML）可以实现对数据的操作，包括对数据库中数据的查询、添加、修改、删除等。（3）数据库的建立与维护。数据库管理系统提供了一些管理维护程序，可以实现对数据库初始数据的输入，对工作日志进行记录，对数据库性能进行监视，重新组织数据库，修改和更新数据库及数据恢复等。（4）数据库控制。数据库管理系统还提供了一系列运行控制程序，可以实现对运行中的数据库进行管理和控制。对数据库的管理和控制主要体现为以下几点。1.1.4结构化查询语言1.1基本概念在与数据库进行交互时通常需要一种语言来完成对数据库的操作。目前，被广泛使用的关系数据库标准语言就是结构化查询语言（StructuredQueryLanguage，SQL）。使用SQL可以实现对关系数据库中数据的查询、插入、更新、删除等，也可以对关系数据库进行管理。SQL是一种高级的非过程化编程语言，允许用户在高层数据结构上工作，既不要求用户指定数据的存放方法，也不需要知道数据的具体存放方式，只需要指出“做什么”即可。由于SQL简单易学，且功能丰富，因此它已被定为关系数据库语言的国际标准。SQL中的语句主要分为以下4类。（1）数据定义。数据定义语言主要负责定义数据库对象及数据结构。例如，对数据库对象的创建、修改和删除。常使用的数据定义语句包括CREATE、ALTER、DROP等。例如，创建数据库cjgl可以使用语句：CREATEDATABASEcjgl。（2）数据操纵。数据操纵语言主要负责对数据库中的数据进行访问，包括数据的插入、修改、删除和查询等。常用的数据操纵语句包括：INSERT、UPDATE、DELETE、SELECT等。例如，查询student表的数据可以使用语句：SELECT*fromstudent。（3）数据控制。数据控制语言（DataControlLanguage，DCL）主要负责对数据的访问权限进行控制，可以实现用户对数据表、预存程序和函数等的使用权限的管理。（4）事务处理。数据库中的事务指的是一组不可分割的操作，要么都执行，要么都不执行。事务处理语句负责对事务中的操作进行控制。1.2.1关系数据模型1.2关系数据库数据模型（DataModel）是对现实世界数据的模拟和抽象，是数据库的核心与基础，提供了信息表示和操作手段的形式架构。数据模型的主要作用是确定系统中数据的定义和格式，使人更容易理解，以便在数据库系统上得以更好的实现。数据模型通常由数据结构、数据操作和完整性约束3个部分组成。（1）数据结构：是对数据静态特征的描述，包括数据的类型、内容、性质和数据之间的相互关系等。（2）数据操作：是对数据库对象动态特征的描述，主要包括操作类型和操作方式。（3）完整性约束：是一组完整性规则的集合，主要用于描述数据之间的语法、数据之间的相互关系和制约关系等，并规定了数例如，班级和学生之间的关系可以通过图1-2表示。1.2.1关系数据模型1.2关系数据库一个班级可以有多个学生，一个学生只能属于一个班级。将班级与学生的这种关系转换为关系表，可以通过班级编号进行关联，如表1-2和表1-3所示。1.2.2关系数据库管理系统1.2关系数据库针对关系数据模型设计的数据库管理系统称为关系数据库管理系统（RelationalDatabaseManagementSystem，RDBMS）。常见的关系数据库管理系统包括SQLServer、DB2、Oracle和MySQL等。1.SQLServerSQLServer是Microsoft公司推出的关系数据库管理系统。2.DB2DB2是IBM公司开发的关系数据库管理系统，主要运行在UNIX、Linux等服务器操作系统上。3.OracleOracle是甲骨文公司开发的关系数据库管理系统。MySQLMySQL由瑞典的MySQLAB公司开发，目前是Oracle公司旗下产品。1.2.3关系的完整性约束1.2关系数据库1.实体完整性实体完整性也称为表中行的完整性，指的是能唯一标示一个实体且属性不能为空值，也不能有重复的主码。在关系数据模型中，将能标示一条记录的属性或属性组称为候选码，一般选中其中的一个作为该关系的主码。包含在任何一个候选码中的属性都称为主属性。不包含在任何候选码中的属性称为非主属性。实体完整性规则是指关系中的主属性不能为空值，也不能重复。2.参照完整性参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。在关系数据模型中，实体与实体之间的关系可以通过引用对应的实体关系模式的主码来表示。在上面的关系中，学生关系中的“所属班级编号”对应于班级关系中的“班级编号”，而“班级编号”是班级关系的主码，则“所属班级编号”是学生关系的外码。参照完整性约束规则为：若属性F是基本关系R的外码，且与基本关系S的主码K相对应，则R中某条记录的F值必须与S中某条记录的主码值相同，或者取空值。3.用户定义完整性用户定义完整性是根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。这一约束机制一般不应由应用程序提供，而应由关系数据模型提供定义并检验，用户定义完整性主要包括字段有效性约束和记录有效性约束。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置MySQL服务器的安装包可以到MySQL官网上下载。下载MySQL安装包文件的具体步骤如下。（1）在浏览器地址栏中输入：/downloads/，按“Enter”键进入下载页面，如图1-3所示。（2）单击超链接“MySQLInstallerforWindows”，跳转到MySQL版本选择页面。单击“LookingforthelatestGAversion?”，在“SelectVersion”下拉列表框中选择“5.7.29”，在“SelectOperatingSystem”下拉列表框中选择“MicrosoftWindows”，如图1-4所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（3）单击“Windows（x86,32-bit）,MSIInstaller”右边的“Download”按钮，跳转到用户注册页面，单击页面下方的“Nothanks,juststartmydownload.”超链接，开始下载，如图1-5所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置MySQL安装包文件下载成功后，便可以安装MySQL服务器。安装MySQL的具体步骤如下。（1）双击下载的mysql-installer-community-.msi文件，进入安装向导。首先需要选择一种安装类型。安装类型包括5种：“DeveloperDefault”（开发者默认）、“Serveronly”（仅服务器）、“Clientonly”（仅客户端）、“Full”（完全）、“Custom”（自定义），如图1-6所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（2）选择“DeveloperDefault”单选按钮，然后单击“Next”按钮，进入“PathConflicts”对话框，如图1-7所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（3）在“InstallDirectory”文本框中设置MySQL服务器的安装位置，在“DataDirectory”文本框中设置数据的存放位置，然后单击“Next”按钮，进入“CheckRequirements”对话框。在该对话框中检查系统是否具备安装MySQL所必需的插件，如果不具备，单击“Execute”按钮，在线安装所需的插件，如图1-8所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（4）插件安装完成以后，单击“Next”按钮，进入“Installation”对话框，如图1-9所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（5）单击“Execute”按钮，开始安装MySQL的各项组件。安装完成后单击“Next”按钮，进入“ProductConfiguration”对话框，如图1-10所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（6）单击“Next”按钮，进入“HighAvailability”对话框，如图1-11所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（7）保持默认设置，单击“Next”按钮，进入“TypeandNetworking”对话框。在该对话框中配置服务器类型和网络选项。服务器类型包括：DevelopmentComputer（开发者类型）、ServerComputer（服务器类型）、DedicatedComputer（致力于MySQL服务类型）3种。网络选项包括连接方式和端口的设置，如图1-12所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（8）选择默认的服务器类型“DevelopmentComputer”和默认的网络连接方式“TCP/IP”，并保持默认端口“3306”。单击“Next”按钮，进入“AccountsandRoles”对话框。在该对话框中可以设置默认用户root的登录密码，也可以添加新的用户，如图1-13所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（9）在“MySQLRootPassword”和“RepeatPassword”文本框中输入相同的密码，然后单击“Next”按钮，进入“WindowsService”对话框。在该对话框中，配置MySQL服务器的名称，是否开机自启动等，如图1-14所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（10）保持默认设置，单击“Next”按钮，进入“ApplyConfiguration”对话框，如图1-15所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（11）单击“Execute”按钮，完成MySQL服务器的配置，如图1-16所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（12）单击“Finish”按钮，然后在弹出的对话框中单击“Next”按钮，进入“MySQLRouterConfiguration”对话框，如图1-17所示1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（13）保持默认设置，单击“Finish”按钮，然后在弹出的对话框中单击“Next”按钮，进入“ConnectToServer”对话框。在“Password”文本框中输入密码，单击“Check”按钮，测试连接，如图1-18所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（14）单击“Next”按钮，进入“ApplyConfiguration”对话框，单击“Execute”按钮，应用配置，如图1-19所示。1.3.1MySQL服务器的安装与配置1.3MySQL的安装与配置（15）单击“Finish”按钮，进入“InstallationComplete”对话框，完成安装。在该对话框中，单击“Finish”按钮，完成整个安装过程，如图1-20所示。1.3.2MySQL基本操作1.3MySQL的安装与配置1.通过系统服务操作MySQL服务器在Windows的系统服务中可以启动、暂停、停止和重新启动MySQL服务器，具体方法如下。执行“开始”|“控制面板”|“管理工具”|“服务”命令，打开Windows服务管理器窗口。在该窗口中选择名称为“MySQL57”的服务，可以查看该项服务的状态。在窗口左边单击相应的选项，可实现对MySQL服务器的操作。也可以在该项上单击鼠标右键，在弹出的快捷菜单中选择相应的命令，实现对MySQL服务的启动、暂停、停止和重新启动等操作，如图1-21所示。1.3.2MySQL基本操作1.3MySQL的安装与配置2.通过命令提示符操作MySQL服务器通过在命令提示符窗口中输入命令，也可以实现对MySQL服务器的操作，具体方法如下。单击“开始”菜单，在弹出的输入框中输入“cmd”命令，按“Enter”键，打开命令提示符窗口。在该窗口中输入以下命令。3.netstartmysql57按“Enter”键，即可启动MySQL服务器，如图1-22所示。1.3.3CommandLineClient1.3MySQL的安装与配置MySQL服务器启动后，就可以通过MySQL的CommandLineClient程序来操作MySQL数据了，具体步骤如下。（1）执行“开始”|“所有程序”|“MySQL”|“MySQLServer5.7”|“MySQL5.7CommandLineClient”命令，打开MySQL客户端命令行窗口，如图1-23所示。1.3.3CommandLineClient1.3MySQL的安装与配置（2）在该窗口中输入root用户的登录密码（该密码为安装过程中配置的密码），登录到MySQL服务器，如图1-24所示。1.3.3CommandLineClient1.3MySQL的安装与配置（3）输入操作的SQL语句，即可实现对数据库的各项操作，如图1-25所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置MySQL本身没有提供非常方便的图形化界面管理工具，操作都通过在客户端命令行窗口中进行，需要对操作命令非常熟悉，对于初学者来说增加了学习难度。在学习过程中，可以使用第三方提供的图形化界面管理工具。常用的图形化界面管理工具包括使用Navicat操作MySQL数据库的基本步骤如下。（1）打开Navicat，如图1-26所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置（2）单击“连接”按钮，在弹出的“新建连接”对话框中输入“连接名”“主机名或IP地址”“端口”“用户名”和“密码”等信息，单击“连接测试”按钮。若弹出“连接成功”对话框，则表明连接信息正确，如图1-27所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置（3）单击该对话框中的“确定”按钮，再单击“新建连接”对话框中的“确定”按钮，返回Navicat窗口。在窗口左侧显示出刚才建立的连接。双击该连接，即可查看当前MySQL服务器中已有的数据库，如图1-28所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置（4）在连接名称“test”上单击鼠标右键，在弹出的快捷菜单中选择“新建数据库”命令，弹出“新建数据库”对话框。在该对话框中设置“数据库名”“字符集”和“排序规则”信息，如图1-29所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置（5）单击“确定”按钮，返回Navicat窗口。在“test”连接下可以看到新建的数据库“db_test”。双击“db_test”，打开该数据库。在表上单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，打开表设计窗口。在该窗口中设置表结构信息，如图1-30所示。1.3.4MySQL图形化界面管理工具1.3MySQL的安装与配置（6）单击“保存”按钮，弹出“表名”对话框。在该对话框中输入表名信息，单击“确定”按钮，返回Navicat窗口。在窗口中双击“db_test”下的“表”，即可查看刚才创建的数据表“tb_test”。双击“tb_test”，打开表，在该表中输入基本数据，如图1-31所示。（7）在Navicat窗口中对数据库和数据表进行其他操作。由于图形化界面操作相对比较简单，此处不再详细介绍，读者可以在学习的过程中自行研究。MySQL数据库基础

第2章

数据设计2.1数据库设计概述2.2概念结构设计2.3逻辑结构设计2.4物理结构设计2.5数据库实施与维护022.1.1认识数据库设计2.1数据库设计概述数据库设计分为6个步骤，包括需求分析（了解和分析用户需求，包括数据和数据的特点、存储方式等）、概念结构设计（抽象出来的现实世界模型）、逻辑结构设计（设计实现）、物理结构设计（存储结构和存取方法）、数据库实施、运行与维护。2.1.2数据库设计的基本步骤数据库设计的6个步骤，如图2-1所示。2.2.1概念结构设计概述2.2认识数据库设计概念结构设计也称概念模型设计。概念模型是将现实世界的事物反映到人的大脑中的一种事物抽象，这种抽象既不依赖计算机系统，也不与具体的DBMS相关，是现实世界到机器世界的中间层次。通过对概念模型的设计最后转换为计算机所支持的DBMS数据模型。1.常用的表示概念模型的方法E-R模型也称为ER实体联系图（E-R图），是表示概念模型的工具，包括实体、联系、属性，其中用矩形表示现实世界的实体，用菱形表示实体间的联系，用椭圆表示实体和联系的属性。ER模型的表示方法如表2-1所示。2.2认识数据库设计2.概念结构设计的基本概念（1）实体：客观存在并能相互区分的事物，可以是具体的人、事、物，如一个学生、一门课程、一个系；也可以是抽象的概念或联系，如一个年度学期、学生的一次选课等。（2）属性：实体所具有的某一特性，用若干属性来刻画某一实体，如学生实体可以由学生编号、姓名、性别、出生日期、民族、家庭住址、联系电话、入学时间、身份证编码等属性组成。（3）主码：能够对实体进行唯一标识的属性集，可以是单属性，也可以是多个属性。例如，学生实体的主码是学号，班级实体的主码是班级号。主码不能为空，并且不能取重复值。（4）域：属性的取值范围。例如，身份证的域为18位，学生性别的域为（男，女）。（5）联系：实体与实体之间，以及实体内部之间通过联系来关联。两个实体间的联系可以分为以下3类。2.2认识数据库设计①一对一联系（1∶1）。如果对于实体集A中的每一个实体，实体集B中有且只有一个实体与之联系；反之亦然，则实体集A与实体集B具有一对一联系，记为1∶1。例如，“学生”实体与“身份证”实体之间属于一对一联系，一个学生只能有一个身份证号，一个身份证只能属于一个学生，如图2-2所示。②一对多联系（1∶n）。如果对于实体集A中的每一个实体，实体集B中有n个实体与之联系（n≥0）；对于实体集B中的每一个实体，实体集A中至多有一个实体与之联系，则实体集A与实体集B具有一对多联系，记为1∶n。例如，“班级”实体与“学生”实体之间属于一对多联系，一个班级有多个学生，一个学生只能在一个班级，如图2-3所示。③多对多联系（m∶n）。如果对于实体集A中的每一个实体，实体集B中有n个实体与之联系（n≥0）；对于实体集B中的每一个实体，实体集A中有m个实体与之联系（m≥0），则实体集A与实体集B具有多对多联系，记为m∶n。例如，“学生”实体与“课程”实体之间属于多对多联系，一个学生可以选修n个课程，一个课程也可以被m个学生选修，如图2-4所示。2.2.2数据抽象与局部视图设计2.2认识数据库设计数据抽象是指通过概念模型对现实世界进行抽象，对人、事、物及概念进行人为处理，从而抽取出认为重要的共同特征，并对这些特征进行概念描述。例如，本书以学生成绩管理系统为例，通过需求分析，获取数据和属性各自的特点；进行数据抽象，建立抽象的概念数据模型，涉及的实体有系别、班级、学生、课程、年度学期及身份证；这些实体对应的局部E-R图分别如图2-5至图2-10所示，图中带下划线的属性表示该实体的主码属性。2.2.3全局概念模式设计2.2认识数据库设计一般情况下先设计局部E-R图，如2.2.2节中给出的学生成绩管理系统的局部实体E-R图，而实体之间可以通过联系进行连接，故可以将局部E-R图综合成全局E-R图，即全局概念模式，如图2-11所示。在全局E-R图中，系别和班级之间是一对多的联系，班级和学生之间是一对多的联系，身份证和学生之间是一对一的联系，学生和年度学期之间是多对多的联系，学生和课程之间是多对多的联系。在合并局部E-R图时需要注意，联系本身也可以有属性，因此在合并的E-R图中给出了。2.3.1逻辑结构设计概述2.3逻辑结构设计逻辑结构设计旨在将概念结构设计建立的E-R图转换成计算机系统支持的某一具体的DBMS（数据库管理系统）中的数据模型。数据模型分为层次模型、网状模型、关系模型，其中关系模型是目前比较常用的一种数据模型。将E-R图转换为关系数据模型，即将E-R图中的实体及实体间的联系用二维表表示。关系模型的表现形式如表2-2所示。2.3.1逻辑结构设计概述2.3逻辑结构设计综上可知，通过概念模型转换成关系模型，关系模型的表现形式是二维表，最后在关系数据库中得以实现，在概念模型、关系模型、关系数据库中，相关的术语之间的对应关系如表2-3所示。2.3.2E-R模型向关系模型的转换2.3逻辑结构设计将信息世界转换成机器世界的逻辑模型，需要从信息世界抽象出来的ER模型向关系模型转换。ER模型向关系模型转换需要通过一定的规则，完成实体和实体间的联系向关系模式的转换，转换规则分为以下两种。1.独立实体到关系模型的转换2.实体和实体之间的联系到关系模式的转换实体和实体之间的联系分为3种，对应的转换规则也分为3种。（1）一对一联系（1∶1）到关系模式的转换（2）一对多联系（1∶n）到关系模式的转换（3）多对多联系（m∶n）到关系模式的转换2.3逻辑结构设计图2-12所示为学生成绩管理系统的局部E-R图，它是一个三元关系图。将其转换为关系模型为选修（学期编号，学生编号，课程编号，成绩，备注）。当多个属性构成关系的主码时，可以增加一个新的属性作为关系的主码，减少主码的数量。在关系选修中，加入属性“成绩编号”作为主码，即选修（成绩编号，学期编号，学生编号，课程编号，成绩，备注）。2.3逻辑结构设计学生成绩管理系统的关系模型，以及完整性约束还可以用表格直观地描述，如表2-4至表2-10所示。2.3逻辑结构设计2.3逻辑结构设计2.3逻辑结构设计2.3.3数据模型优化2.3逻辑结构设计E-R模型转换为关系模型后，还应适当地修改、调整数据库逻辑结构，合理地设计关系模型，提高数据库应用系统的性能，对数据模型进行优化。不好的数据库设计可能存在插入异常、删除异常、修改异常及数据冗余等问题。通过对关系模型进行规范化设计，即关系模型的规范化理论可以判断设计的关系模型是否合理。下面依次对操作异常、函数依赖和关系范式进行介绍。表2-11为班级信息表的表结构和数据，在该表中假设将班级信息和系部信息放在一张表中。2.3.3数据模型优化2.3逻辑结构设计1.操作异常及数据冗余（1）插入异常。向数据库中插入新的数据时插入不成功会导致异常，如果某实体随着另一个实体的存在而存在，缺少某个实体时无法表示这个实体，这个表就存在插入异常。例如，在表中插入一行数据（null,null,X009,学前教育系，null）,即某个系刚刚成立，暂时还未分配班级，就无法将系的信息存入数据库，因为主键不能为空。（2）删除异常。如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，则表中存在删除异常。例如，删除第二行数据（该班级学生毕业了），系部的信息就有可能会丢失。（3）修改异常。如果更改表对应的某个实体实例的单独属性时，需要将多行更新，则这个表存在更新异常。例如，若要将“会计班级”的系部换到其他系，需要涉及“会计班级”中的系部信息数据都要更改，如有遗漏，则造成数据不一致。（4）数据冗余。如果相同的数据在多个地方存在，或者表中的某列可以由其他列计算得到，则存在数据冗余。例如，“计算机系”的班级比较多时，系部名称重复量增多，造成存储空间浪费。2.3逻辑结构设计2.函数依赖（1）函数依赖的概念。（2）平凡函数依赖与非平凡函数依赖。（3）完全函数依赖和部分函数依赖。（4）传递函数依赖。2.3逻辑结构设计4.关系范式（1）第一范式。关系模式R的属性不可再分，即数据库表中的所有字段都是单一属性，不可再分。表2-12符合第一范式，而表2-13不符合第一范式，设计不合理。2.3逻辑结构设计（2）第二范式。第二范式是指若R∈1NF,且R中除主码外属性完全依赖主码，数据库表中不存在除主码以外的其他列对主码的部分函数依赖，即存在联合主码中的某一个主码决定非主码列的情况。表2-14不符合第二范式。解决方法是将表2-14分解成两个表，也就是两个关系模式，如表2-15和表2-16所示。当操作学生表时，课程表的数据不会受到影响。2.3逻辑结构设计（3）第三范式。若R∈2NF，关系模式R中不存在非主属性对任意候选关键字的传递函数依赖，则称为符合第三范式，即若关系R中有属性A、B、C，存在A确定B,B确定C,那么A通过B确定C,称为C传递依赖于A。例如，在表211的班级信息表中，以班级编号作为主码，（班级编号）→（系别编号）→（系部名称），即非关键字段“系部名称”传递依赖于关键字段“班级编号”，属于不符合第三范式的表。同时存在着插入异常、删除异常、修改异常和数据冗余问题。解决方法是将表进行拆分，拆分后的表为班级表和系部表，如表2-17和表2-18所示。当操作班级表时，系别表的数据就不会受到影响。2.3逻辑结构设计（4）BC范式。BC范式又叫Boyce.Codd范式，由Boyce和Codd提出，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖，即一个关系模式是复合关键字，则复合关键字之间也不能存在函数依赖关系，如表2-19所示。在表2-19中，可以以学生编号，课程编号作为该关系的唯一标识，也可以以身份证号，课程编号作为该关系的唯一标识，有两种组合关键字的选择，在这两种组合关键字中存在以下关系：（学生编号）P（身份证号），（身份证号）P（学生编号），即学生编号可确定身份证号，身份证号可确定学生编号，因此不符合BC范式定义的需求，并且存在操作异常和数据冗余。2.3逻辑结构设计解决方法是将表进行拆分，拆分后的表为学生表和成绩表，如表2-20或表2-21所示。当操作成绩表时，学生表的数据不会受到影响。2.4.1物理结构设计概述2.4物理结构设计数据库设计最后要在物理设备上进行存储。对上述设计的逻辑结构模型选取最适合应用要求的物理结构的过程，称为数据库的物理结构设计。数据库的物理结构设计主要包括数据库在物理结构上的存储结构与存取方法。2.4.2存取结构与存取方法设计1.存储结构设计2.存取方法设计（1）索引方法。（2）聚簇方法。①聚簇的用途、②聚簇的适用范围、③聚簇的局限性、④聚簇索引的适用条件。2.4.1物理结构设计概述2.5数据库实施与维护1.数据库的创建2.数据的载入3.数据库测试（1）功能测试是指为了测试应用程序的各种功能，运行应用程序，对数据库执行各种操作。（2）性能测试是指通过测量系统的性能指标，分析数据库设计是否符合设计目标。如果不符合，需要返回物理结构设计阶段，做适当的调整和修改；若仍不符合，需要返回逻辑结构设计阶段调整数据模型，这样可能会导致数据重新入库，此时要注意以下两点。4.运行与维护数据库测试结果达到设计目标后，数据库开发工作就基本完成，数据库正式投入运行，同时意味着维护工作的开始，数据库设计工作并未结束。由于应用环境的变化带来的物理存储的变化，需要长期进行数据库的维护，包括数据库设计的评价、调整、修改等工作。在数据库运行中，维护工作主要由数据库管理员（DBA）来完成，包括数据库的转储和恢复、数据库的安全性和完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。这里就不再详细介绍了。MySQL数据库基础

第3章

数据库及数据表的基本操作3.1数据库的基本操作3.2MySQL存储引擎3.3数据类型3.4数据表的基本操作033.1.1创建数据库3.1数据库的基本操作创建数据库，是指在计算机磁盘上划分出一块区域用于数据的存储和管理。在MySQL中，使用SQL语句创建数据库的基本语法如下：创建一个名为“cjgl”的数据库，运行效果如图3-1所示。3.1数据库的基本操作创建一个名为“cjgl”的数据库，并指定字符集为UTF8，运行效果如图3-2所示。如果创建数据库时，数据库已经存在了，且没有进行数据库是否已存在的判断，则会报错，如图3-3所示。如果创建的数据库已经存在，但创建数据库时进行了数据库是否已存在的判断，则不在创建数据库，并给出警告，如图3-4所示。3.1.2管理数据库3.1数据库的基本操作1.数据库查看显示当前服务器中，用户权限范围内的所有数据库名称，运行效果如图3-5所示显示出数据库名以s开头的数据库信息，运行效果如图3-6所示。3.1数据库的基本操作2.数据库选择第1行代码，选择world数据库作为当前数据库；第2行代码，对当前数据库进行操作，查看数据表信息。运行效果如图3-7所示。第1行代码，选择cjgl数据库作为当前数据库。运行效果如图3-8所示。3.1数据库的基本操作3.数据库修改修改数据库cjgl的默认字符集为gbk，修改字符集的默认校对规则为gbk_chinese_ci。运行效果如图3-9所示。删除数据库cjgl，由于删除之前，该数据库是存在的，所以能正常执行删除。运行效果如图3-10所示。在cjgl数据库被删除后，再使用DROPDATABASEcjgl语句对其进行删除，则产生异常，如图3-11所示。如果在删除之前进行了数据库是否存在的判断，则不会产生错误，如图3-12所示。3.2.1查看MySQL存储引擎3.2MySQL存储引擎1.查看全部存储引擎运行效果如图3-13所示。3.2MySQL存储引擎SHOWENGINES语句以分号（；）结束，也可以以“\\g”或“\\G”结束。使用“\\g”与“；”作用是相同的，使用“\\G”可以以更美观的方式显示结果。使用“\\G”结束，基本格式如下：SHOWENGINES\\G运行效果如图3-14所示。3.2.1查看MySQL存储引擎3.2MySQL存储引擎1.查看默认存储引擎运行效果如图3-15所示。3.2.2InnoDB存储引擎3.2MySQL存储引擎MySQL从3.23.34a开始包含InnoDB存储引擎，从5.5开始将InnoDB作为默认的存储引擎。3.2.3MyISAM存储引擎MyISAM存储引擎曾是MySQL中的默认存储引擎。3.2.4MEMORY存储引擎擎MEMORY存储引擎最大的特点是响应速度快。3.2.5ARCHIVE存储引擎ARCHIVE存储引擎不支持事务，主要用于对数据进行归档。3.2.6存储引擎的选择3.2MySQL存储引擎一个数据库中的表，可以采用不同的存储引擎。实际应用中，应该根据需求灵活设置每个表的存储引擎。为每个表设置合适的存储引擎，以提高整个数据库的性能。每种存储引擎都有其各自的优缺点，充分对比各种存储引擎的特点，才能在实际应用中做出恰当的选择。从事务安全、存储限制、空间使用、内存使用、插入数据速度和外键支持等几个方面，对常用的InnoDB、MyISAM、MEMORY3种存储引擎进行详细对比，如表3-1所示。3.2.7存储引擎的设置3.2MySQL存储引擎运行代码，并打开数据库文件目录查看表文件，如图3-17所示。3.2.7存储引擎的设置3.2MySQL存储引擎运行代码，并打开数据库文件目录查看表文件，如图3-18所示。从图3-18可以看出，teacher表只对应扩展名为“.frm”的一个文件3.3.1数字类型3.3数据类型MySQL中的数字类型分为整数类型和小数类型。整数类型又包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等。不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。根据数据类型占用的字节数，可以求出每一种数据类型的取值范围。例如，TINYINT占用1个字节（8bit）来存储，那么TINYINT无符号数的最大值为28-1，即255；TINYINT有符号数的最大值为27-1，即127。其他整数类型的取值范围计算方法相同。不同整数类型占用空间大小及取值范围，如表3-2所示。3.3数据类型小数类型可以分为浮点数类型和定点数类型。浮点数类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）。定点数类型只有一种，就是DECIMAL。不同小数类型占用空间大小及取值范围，如表3-3所示。3.3.2字符串类型3.3数据类型1.普通文本字符串类型关于CHAR和VARCHAR类型的介绍，如表3-4所示。CHAR和VARCHAR类型的区别如表3-5所示。3.3数据类型2.可变类型TEXT和BLOB类型，根据其存储空间和数据长度的不同又各分为4种，如表3-6所示。2.可变类型ENUM和SET类型的介绍如表3-7所示。3.3.3日期类型3.3数据类型日期类型的介绍如表3-8所示。3.4.1创建数据表3.4数据表的基本操作创建数据表是指在已有的数据库中建立一张新的数据表。其中，第1行代码，选择数据库，表示接下来的操作是在数据库cjgl中进行的；第2行代码，创建student数据表，在创建前先判断是否已存在同名的数据表，避免创建同名数据表时出现错误；第3~第8行代码，分别创建数据表中的各个字段。在创建字段时，需要定义字段的类型，类型名后面的括号内给出的是该字段所占的长度。根据实际情况，合理定义各个字段的长度，有利于更好地利用计算机空间。PRIMARYKEY表示该字段为数据表的主键，其值不能为空且是唯一的，NOTNULL表明该字段不能为空。运行上述代码，结果如图3-19所示。3.4数据表的基本操作1.查看数据表结构数据表定义后，可以通过DESCRIBE和SHOWCREATETABLE等语句对其结构进行查看，包括字段名、字段类型和完整性约束条件等。运行代码，结果如图3-20所示。3.4.2管理数据表3.4数据表的基本操作除了使用DESCRIBE语句查看表结构外，还可以使用SHOWCREATETABLE语句查看表的详细结构，包括表的各字段名称、字段类型、完整性约束和默认的存储引擎、字符编码等详细信息。运行代码，结果如图3-21所示。3.4数据表的基本操作2.修改数据表在MySQL中对已经定义的表可以通过ALTERTABLE语句对其修改。修改表包括对表的名称、字段的名称、字段类型等的修改，也可以对字段进行增加和删除、修改字段的顺序，还可以修改默认的存储引擎和外键约束等。对表的修改，不会影响表中已有的数据。3.删除数据表在MySQL中可以使用DROPTABLE语句同时删除一个或多个数据表。数据表被删除后，表的结构和表中的数据都会被删除。在删除表时需要特别注意。MySQL数据库基础

第4章

数据更新4.1插入数据4.2数据修改4.3数据删除044.1插入数据1.向表中所有字段插入数据向表中所有字段插入数据的两种方式：①指定表中所有字段，并给出对应字段的值；②不指定字段给出所有字段的值。（1）指定表中所有字段向课程表tb_course中插入一行数据（1，'高等数学'，'必修'，5，130，'测试数据'）。首先查看tb_course的表结构，代码如下：DESCcjgl.tb_course;运行结果如图4-1所示。4.1.1插入数据之INSERT…VALUES语句4.1插入数据（2）不指定字段向课程表（tb_course）中插入一行数据（2,'MySQL数据库基础','必修',5,130,'测试数据'），采用INSERT…VALUES语句，不指定字段给出所有字段的值，对应的SQL语句代码如下：INSERTINTOtb_courseVALUES（2,'MySQL数据库基础'，'必修'，5，130，'测试数据'）;运行效果如图4-4所示。4.1插入数据2.向表中部分字段插入数据向表中部分字段插入数据，即通过INSERT…VALUES语句，插入给定的部分字段的数据。【例4-2】向课程表（tb_course）中插入一行数据（'大学英语'，'必修'）。对应的SQL语句代码如下：INSERTINTOtb_course（name,nature）VALUES（'大学英语'，'必修'）;运行效果如图4-6所示。4.1.1插入数据之INSERT…VALUES语句4.1插入数据通过查询语句，查看数据的插入结果，使用以下的SQL语句：SELECT*FROMtb_course;运行结果如图4-7所示，id为3的记录为新插入的数据。4.1插入数据3.向表中插入多条数据通过INSERT…VALUES语句向表中还可以一次插入多条数据。【例43】向课程表（tb_course）中插入多条数据，对应的SQL语句代码如下：INSERTINTOtb_course（id,name,nature,credits,hours,remarks）VALUES（4，'建筑学基础'，'必修'，3，100，'测试数据'），（5，'文言文赏析'，'必修'，6，300，'测试数据'），（6，'Java编程基础'，'必修'，3，120，'测试数据'）;运行效果如图4-8所示。4.1插入数据通过查询语句，查看数据的插入结果，使用以下的SQL语句：SELECT*FROMtb_course;运行效果如图4-9所示，id为4、5、6的记录为新插入的数据。4.1插入数据INSERT…SET语句也可以向表中插入数据，该语句通过直接给表中的某些字段赋值来完成指定数据的插入，其他未赋值的字段的值为默认值。4.1.2插入数据之INSERT…SET语句在MySQL中，可通过INSERT…SELECT语句，向指定表中插入其他表中的数据，即将一张表的查询结果插入指定的数据表中，INSERT…SELECT语句的基本语法如下：INSERT［INTO］<数据表名1>［（字段名，…）］SELECT字段名1，字段名2，…FROM<数据表名2>［ONDUPLICATEKEYUPDATE字段名=表达式,…］4.1.3插入数据之INSERT…SELECT语4.2数据修改在插入数据时可能会遇到将错误的数据插入数据库的情况，也有可能在后期的工作中要对数据进行更新，在此就需要涉及对表中的数据进行修改。在MySQL语句中，通过UPDATE语句来执行修改的操作，该语句即可以修改字段所有的值，也可以修改指定的值。（1）修改所有的值。（2）修改指定的值。在修改数据时，一般需要对修改的字段的数据加上限定条件，只修改满足条件的记录的该字段，此时要用到WHERE字句。4.3数据删除（1）删除指定记录的数据（2）删除所有数据4.3.1删除数据之DELETE语句删除数据库中所有的行，还可以使用TRUNCATETABLE语句。4.3.2删除数据之TRUNCATETABLE语句MySQL数据库基础

第5章

数据查询5.1MySQL数据基本查询5.2MySQL查询条件选择5.3MySQL多表查询5.4行浏览查询055.1MySQL数据基本查询在数据库的查询中，SELECT*FROMTABLE是查询的核心要点语句。可以根据需要，有选择地使用条件。在MySQL中，使用SELECT语句查询数据库的基本语法如下：SELECT［*|<字段列名>］［FROM<表1>,<表2>…］［WHERE<表达式>］［GROUPBY<groupbydefinition>］［HAVING<expression>［{<operator><expression>}…］［ORDERBY<orderbydefinition>］［LIMIT［<offset>,］<rowcount>］;5.1.1SELECT语法格式5.1MySQL数据基本查询在数据库的查询中，我们可以根据需要，有选择地查询输出相应的字段。当选择字段查询时，各字段之间要用逗号隔开；当需要查询所有的字段时，可以使用“*”表示。从表tb_student中查询字段名分别为id，name，national和telephone的所有数据，运行效果如图5-1所示。5.1.2查询选择字段5.1MySQL数据基本查询查询表tb_student中所有字段的所有数据，运行效果如图5-2所示。5.1MySQL数据基本查询若希望查询结果展示时字段名使用自己想要的名字，可以在字段名后面使用as子句，运行效果如图5-3所示。5.1MySQL数据基本查询当电话号码显示没有的时候，替换为“无”。当电话号码大于等于10000000000并且小，替换为“移动”。当电话号码大于等且小，替换为“联通”。当电话号码大于等且小于19999999999时，替换为“电信”。当电话号码不在相应的范围内时，替换为“未知”。并把telephone字段显示为别名“运营商”。效果如图5-4所示。5.1MySQL数据基本查询当希望对查询数据进行再次加工计算处理时，可以在相应字段进行运算，如将考试成绩从百分制换算成120分制，再进行展示。运行效果如图5-5所示。如果仅对表中部分字段进行查询，则输出的结果可能会出现重复的行，如例51中，如果选择的字段没有id，则会查询得到两条重复的数据。可以使用DISTINCT或DISTINCTROW消除图5-5结果中的重复行。运行效果如图5-6所示。5.1MySQL数据基本查询在数据库的查询中，FROM子句表示从哪个表中查询数据。既可以从当前数据库中的表查询相关数据，也可以在当前数据库查询其他库中表的数据，还可以根据表的个数进行多表查询。使用当前数据库，并进行库中表内的数据查询。在这种情况下，首先使用USE语句让使用的库作为当前数据库，其次使用FROM子句指定相应表的查询。运行效果如图5-7所示。5.13查询数据来源5.1MySQL数据基本查询使用当前库，查询其他库中的表时，指定表名前面要加上查询表所在的数据库名字。在示例中，首先使用USE表示使用WORLD数据库；其次查询test数据库中的tb_term表。这时在表tb_term的前面加上其所在的库名test，我们可以正常查询并得到相应的数据；如果去掉库名，则会提示库中不存在相应的表。运行效果如图5-8所示。5.1MySQL数据基本查询当多个表中存在字段重名的时候，进行查询就会出现语法错误，提示Column'字段名'infieldlistisambiguous，意思是：重复的这个字段名存在于多个表中，需要使用哪个表的此字段是模糊不清的。此时我们取tb_termremarks这个字段。运行效果如图5-9所示。5.1MySQL数据基本查询使用数据库查询时，有时，并不需要返回数据本身，而仅仅需要对数据进行统计或计算总结，那么就需要用聚合函数进行查询。聚合函数的作用是对一组数值进行统计或计算，并返回确定的单一数值。所有聚合函数都具有确定性，任何时候用一组确定的数值调用它们时，都返回相同的值。表5-1列出了一些常用的聚合函数。5.1.4使用聚合函数5.1MySQL数据基本查询使用数据库查询时，有时，并不需要返回数据本身，而仅仅需要对数据进行统计或计算总结，那么就需要用聚合函数进行查询。聚合函数的作用是对一组数值进行统计或计算，并返回确定的单一数值。所有聚合函数都具有确定性，任何时候用一组确定的数值调用它们时，都返回相同的值。表5-1列出了一些常用的聚合函数。5.1.4使用聚合函数5.2MySQL查询条件选择常用的操作符包括：比较操作符、逻辑操作符、连接操作符、求反操作符、算术操作符。1.比较操作符比较操作符用于比较语句中的值。当两个表达式的值均不为空时，比较操作符有运算结果，为TRUE（真）或FALSE（假）。2.逻辑操作符逻辑操作符用于对SQL关键字而不是符号进行比较。3.连接操作符如果我们在查询时，需要利用多个条件来缩小数据范围，则可使用连接操作符。连接操作符可以让我们在一个SQL语句中，使用多个操作符进行组合比较，从而得到想要的结果。4.求反操作符在前文所提到的所有逻辑操作符中，我们都可以颠倒它们的条件要求。5.算术操作符算术操作符用于在SQL语句中执行算术功能。5.2.1WHERE子句5.2MySQL查询条件选择数据分组是按照逻辑次序把具有重复数值的字段进行合并。例如，一个数据库包含关于雇员的信息，有些雇员住在同一个城市。如果我们需要知道每个指定城市的雇员信息，就可以根据不同的城市对雇员进行分组。数据分组是通过SELECT语句使用GROUPBY子句来实现的。5.2.2分组GROUPBY我们需要让输出结果以某种方式进行排序时，可以使用ORDERBY子句，它能够以用户指定的列表格式对查询结果进行排序。ORDERBY子句的默认次序是升序，也就是说，如果对输出的结果为字符的结果进行排序，就是A—Z的次序；反之，降序就是Z—A的次序显示字符结果。对于数值而言，升序是1—9，降序是9—1。5.2.3排序ORDERBY5.2MySQL查询条件选择LIMIT子句的作用是限制查询结果的条数。其语法格式:LIMIT［位置偏移量,］行数；其中，中括号里面的参数是可选参数。位置偏移量是指MySQL查询分析器要从哪一行开始显示，索引值从0开始，即第一条记录的位置偏移量是0，第二条记录的位置偏移量是1,依此类推……第二个参数为“行数”，即指示返回的记录条数。5.2.4行限制LIMIT5.3MySQL多表查询子查询也被称为嵌套查询，是位于另一个查询的WHERE子句里的查询，它返回的数据通常在主查询里作为一个条件，从而进一步限制数据库返回的数据。子查询可用于SELECT，INSERT，UPDATE和DELETE语句。5.3.1子查询要从不同的表中查询数据，必须在FROM子句中指定多个表，这时要用到连接，将不同的数据组合到一个表中叫作表的连接。将各个表用逗号隔开，就指定了一个全连接。FROM子句产生的中间结果是一个新表。5.3.2连接查询使用UNION语句，可以把来自多个SELECT语句的结果组合到一个结果集中。UNION操作符组合多个语句结果时，不包含重复的记录。如果某行的输出存在一个查询结果中，那么其他查询结果同一行的记录就不会再输出显示。5.3.3联合查询5.4行浏览查询使用HANDLER语句时，首先使用HANDLER表名OPEN语句打开一个表，再使用HANDLER表名READ语句浏览已打开表的数据行，最后使用HANDLER表名CLOSE关闭表。5.4.1HANDLER神秘的非SQL标准语法HANDLER命令，使用如此方便，而且可以降低优化器对SQL语句的解析与优化开销，从而提升查询性能。那么，为什么这么好的东西未被广泛推广呢？我们把它与SELECT进行对比，就会发现以下不同点：（1）HANDLER每次返回一行，SELECT一次返回所有行。（2）HANDLER每次返回索引中的所有字段，SELECT可以返回需要的具体字段。（3）HANDLER涉及分析较少，比SELECT较快。当指定具体表中特定字段数值查询时，HANDLER语句涉及的分析句柄和索引比SELECT相对少得多，因此速度较快。（4）HANDLER语句完成后，必须使用CLOSE语句关闭表。打5.4.2说明MySQL数据库基础

第6章

数据视图6.1视图的基本概念6.2视图的操作6.3对性能的影响066.1视图的基本概念当创建一个视图时，实际上是在数据库里执行了一个SELECT语句，它定义了这个视图。这个SELECT语句可能只包含表里的字段名称，也可能包含各种函数和运算来操作或汇总给用户显示的数据。6.1.1什么是视图（1）简化数据访问。（2）一种安全形式。（3）维护摘要数据。6.1.2视图的作用6.2视图的操作视图是通过CREATEVIEW语句创建的。为了创建视图我们可以从一个表创建视图；可以从多个表创建视图；可以从视图创建视图；也可以通过视图创建表。通过单表创建视图。创建的视图为表的全部字段和数据。运行结果如图6-1所示。6.2.1创建视图6.2视图的操作视图的查询基本和表的查询（SELECT）相同。【例6-7】SELECTvv.Stu_id,vvname,Count（*）FROMvv1_tb_scorestudentASvvGROUPBYvv.name;通过视图查询数据。通过分组查询视图vv1_tb_scorestudent统计数据。运行结果如图6-7所示。6.2.2查询视图6.2视图的操作在一定条件下，视图的底层数据可以进行更新，主要表现在以下几个方面。（1）视图不包含结合。（2）视图不包含GROUPBY子句。（3）视图不包含UNION语句。（4）视图不包含对伪字码ROWNUM的任何引用。（5）视图不包含任何组函数。（6）不能使用DISTINCT子句。（7）WHERE子句包含的嵌套表的表达式不能与FROM引用同一个表。（8）视图可以执行INSERT、UPDATE和DELETE等语句。6.2.3更新视图6.2视图的操作DROPVIEW命令用于从数据库里删除视图。它有两个选项：RESTRICT和CASCADE。如果使用了RESTRICT选项进行删除操作，而其他视图在约束里有所引用，删除操作就会出错；如果使用CASCADE选项，而且其他视图或被约束引用了，DROPVIEW也会成功，而且底层的视图或约束也会被删除。删除视图v1_tb_course。运行结果如图6-9所示。6.2.4删除视图6.3对性能的影响（1）为用户集中数据，简化用户的数据查询和处理。有时所需的数据分布在多张表中，定义视图可以将他们集中起来，从而方便查询和处理。（2）屏蔽数据库的复杂性。用户不必去了解复杂的数据库表的结构，并且数据库的表更改也不会影响用户对数据库的使用。（3）简化用户权限的管理。只需授权用户使用视图的权限，而不必指定只能使用表的特定列或特定权限。（4）便于数据共享。各用户不必都定义和存储自己所需的数据，这样数据只存储一次，共享同一数据库资源即可。（5）可以重新组织数据以便输出到其他应用程序中。6.3.1视图的优点6.3对性能的影响视图中隐藏的复杂逻辑，会导致系统需要查询底层来分析并组合数据；在进行性能调整的时候，视图需要和其他SQL语句一样被调整。如果构成视图的查询没有经过事先设计，那么视图本身就会对性能产生影响。有些用户将查询分解为各种视图构成的多个单元，以便简化复杂的查询。这种方法在简化复杂逻辑方面看起来是个好办法，但却会降低性能。因为，搜索引擎需要分析每一次的视图来确定为了完成搜索需要进行哪些工作。嵌套的层数越多，搜索引擎为了获得执行计划而需要进行的分析工作就越多。实际上，大多数的搜索引擎无法确保获得一个完美的执行计划，而只能保证一个耗时最短的计划。因此，最好的办法就是尽量减少代码的嵌套层数，测试并调整创建视图所用到的语句。6.3.2视图的不足MySQL数据库基础

第7章

索引7.1索引简介7.2创建索引7.3删除索引077.1索引简介在MySQL中,索引是十分重要的数据库对象。通过索引的创建,可以大大提MySQL的查询速度，同时可降低服务器的负载。在使用时,索引主要是根据表中建立索引单列或多列的值与原表中记录行之间一一对应关系的有序表。索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。例如，根据我们前面创建的学生成绩管理系统,如果要查找学号为“3”的学生的学习成绩，这时只需要创建学生信息表中学号列的索引,迅速在学号索引列查找学号为“3”的值，即可返回该行对应的数据。在查询过程中，一般过程的数据查询，需要遍历数据表中的所有数据。建立索引后，通过索引可以实现优化查询，进一步提高查询速度。但是使用时也需要注意，过多地使用索引也会造成滥用。因为创建索引时会降低更新表的速度和占用一定的物理空间，如对表进行INSERT、UPDATE和DELETE。在更新表时，MySQL除了要保存数据，还要保存索引文件，建立的索引还会占用磁盘空间的索引文件，进而给数据的操作和维护带来一定影响。7.1.1索引简介7.1索引简介1.普通索引普通索引是最基本的索引,没有任何限制,可以在任何数据类型中创建。创建普通索引时，只需添加关键字INDEX即可。创建时只需要根据表的某一字段进行创建，创建好普通索引后，通过创建的该索引字段即可进行查询。2.唯一索引创建唯一索引时，索引列的所有值都必须是唯一的，即只能出现一次。创建时使用关键字UNIQUE进行设置。3.全文索引全文索引可以使用FULLTEXT参数进行设置。只能在VARCHAR、CHAR、TEXT类型的列上创建,主要针对数据量较大的字符串类型字段进行查询，可以提高查询速度和效率。跟普通索引稍有不同，全文索引一般以词为基础，全文搜索可能存在文字、字母和数字等，对于一些大小写敏感的字母，可以使用二进制排序后再执行全文索引。4.空间索引空间索引主要是针对空间数据类型做的索引，主要使用SPATIAL参数进行设置。7.1.2索引分类7.2创建索引CREATEINDEX语句用于在已有表上创建索引，一个表可以创建多个索引，一个索引可以包含多个列，中间需用逗号隔开。但该语句不能创建主键。【例7-1】在tb_student表中的id字段上建立一个名为stu_id的普通索引。CREATEINDEXstu_idontb_student（id）;输入上述命令后，即可查看刚才创建的stu_id的普通索引，运行结果如图7-1所示。7.2.1通过CREATEINDEX语句7.2创建索引通过ALTERTABLE语句可以修改已有数据表结构及向表中添加索引，语法格式如下：ALTERTABLE表名ADD［索引类型］INDEX<索引名>（<列名>［<长度>］［ASC|DESC］）【例7-3】在表tb_department中的name字段上，使用altertable语句创建名为index_name的全文索引,具体代码如下。ALTERTABLEtb_departmentaddfulltextindex_name（name）;创建的索引信息，运行如图7-3所示。7.2.2使用ALTERTABLE语句7.2创建索引在创建表时创建索引。【例7-4】在cjgl数据库中创建成绩表tb_score，并在该表的score列上创建普通索引。usecjglCREATETABLEtb_score（idbigint（10）PRIMARYKEYNOTNULLAUTO_INCREMENT,scoreint（4）DEFAULTNULL,stu_idbigint（10）DEFAULTNULL,course_idbigint（10）DEFAULTNULL,term_idbigint（10）DEFAULTNULL,remarksvarchar（50）DEFAULTNULL,index（score））;创建索引信息如图7-4所示。7.2.3在创建表时创建索引7.3删除索引索引创建完后，如果是用户不需要或不常用的索引，可以通过DROPINDEX语句进行删除。删除索引后，将会减少系统资源的占用，从而提高更新速度。语法格式如下：DROPINDEX索引名ON表名【例7-5】删除例7-3中在表tb_department中的name字段上创建的名为index_name的全文索引,具体代码如下：DROPINDEXindex_nameONtb_department;删除后效果如图7-5所示。MySQL数据库基础

第8章

数据库编程8.1存储过程的基本操作8.2MySQL存储函数8.3触发器088.1存储过程的基本操作存储过程是一个完成特定功能的SQL语句的集合，是根据业务功能需要而保存的一条或多条SQL语句的集合。存储过程通常有以下优点：（1）可重用性。存储过程被创建后，可通过名字在程序中被多次调用，而不必重新编写该存储过程的SQL语句。后续可以对存储过程封装的SQL语句进行修改，而不会影响调用它的应用程序。（2）扩展SQL语句的编程性。存储过程可以采用流程控制语句编写，有很强的逻辑灵活性，因此可完成复杂的判断和较复杂的运算。（3）降低网络负载。存储过程是在服务器端运行，且执行速度较快。当客户端机调用该存储过程时，网络只需要传送该调用语句即可，减少了网络之间的数据传输，从而降低了网络负载。（4）提高安全性。存储过程可以完成所有针对数据库的操作，并且开发人员可以通过编程的方式控制访问数据库信息的权限。8.1存储过程的基本操作存储过程的参数详解：（1）IN：修饰输入参数，用于传入参数值给存储过程。（2）OUT：修饰输出参数，用于接收存储过程返回的参数值。（3）INOUT：修饰输入/输出参数，可以传入值，也可以接收存储过程的返回值。（4）<参数名>：可选项，命名规范同存储过程名称。（5）<类型>：参数的数据类型。（即MySQL数据类型）8.1.1存储过程基本操作8.1存储过程的基本操作1.创建无参数存储过程根据语法提示，创建一个简单的存储过程，名称为pro_test，功能是完成查询学生表tb_student的信息。2.有参数存储过程在实际开发应用中，存储过程基本都需要参数。参数的使用可以使存储过程更加灵活和有用。在MySQL中参数有3种模式：IN，OUT或INOUT。3.查看存储过程在实际的应用过程中，我们会想要了解在数据库下创建了哪些存储过程，因此我们需要在MySQL中查看存储过程的语法。4.修改存储过程MySQL修改存储过程的语法。5.删除存储过程当我们在实际应用中不再需要一些存储过程时，我们可以通过命令删除掉。8.1.1存储过程基本操作8.1存储过程的基本操作1.MySQL声明变量编辑复杂的存储过程，我们首先要清楚MySQL变量的定义与赋值。在MySQL中有两种定义变量的方式，分别为DECLARE命令和SET命令加“@”符号。2.存储过程的条件语句MySQL的存储过程提供了两种条件判断语句，一种是IF…ELSE…，另一种是CASE，下面我们来详细讲解这两种语法的用法。3.存储过程的循环语句在MySQL存储过程中支持以下3种循环结构：1）WHILE…DO…ENDWHILE结构；2）REPEAT…UNTIL…ENDREPEAT结构；3）LOOP…LEAVE…ITERATE…ENDLOOP结构。8.1.2存储过程高级应用8.2MySQL存储函数MySQL存储函数（自定义函数），函数的功能一般用于接收参数，完成计算，返回结果。因此，我们可以将使用频率较高的计算逻辑写成一个函数。存储函数与存储过程一样，也是由SQL语句和过程式语句组成的代码片段。8.2.1存储函数8.2MySQL存储函数存储函数和存储过程本质上是一样的，都是一组SQL语句的集合，但在使用场景上还是有区别的，存储过程一般用于业务逻辑比较复杂的情况，而存储函数的应用功能往往更有针对性，而且MySQL提供非常多的内置存