《MySQL数据原理与应用》 课件 第7、8章 视图与索引、数据处理之增删改VIP

视图与索引第7章通过本课程的学习，您将应该：了解数据库的相关概念熟悉数据库工具的使用熟悉SQL语言的规则与规范熟练掌握MySQL数据库管理方法熟练掌握MySQL表结构管理方法熟练掌握表记录的检索方法熟练掌握视图与索引的使用方法熟练掌握数据处理之增删改熟练掌握存储过程与游标的使用方法熟练掌握存储函数的应用熟练掌握触发器的应用熟练掌握事务的基本特性和应用场景熟练掌握数据库安全及管理熟练掌握MySQL数据库备份和恢复操作了解利用MySQL+Java开发一个数据库学习系统并部署课程目标项目部署成功本讲教学目标熟练创建视图和索引及增删改查操作。熟练使用数据库图形化管理工具进行视图和索引的实际操作。通过本讲的学习，您将可以：1.OPTION2.OPTION视图的应用目录Content视图01索引02课业任务03常见错误及解决方案04视图017.1.1创建视图视图是存储在数据库中的查询的语句，它的存在主要出于两个原因：首先是便捷原因，它将复杂的查询简单化，更利于用户理解和使用；另一个原因是安全原因，视图可以隐藏一些数据，例如，它可以用视图显示学生信息表中的姓名、年龄、专业，而不显示联系电话和身份证号码等信息。视图是一个虚拟表，是从数据库中的一个或多个表中导出来的表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。对所引用的基础表来说，视图的作用类似于筛选，定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过视图修改数据时的限制也很少。7.1.1创建视图创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一个表中，也可以建立在多个表中。1.查看创建视图的权限创建视图需要具有CREATEVIEW的权限，同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息，查询语法如下。SELECTSelect_priv,Create_view_priv

FROMmysql.user

WHEREuser='用户名';

（1）Selete_priv表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。（2）Create_view_priv表示用户是否具有CREATEVIEW权限。（3）mysql.user表示MySQL自带的数据库mysql下的user表。（4）用户名表示为当前使用MySQL数据库的用户名称。说明：7.1.1创建视图【案例7.1】查询MySQL中root用户是否具有创建视图的权限。登录MySQL终端后利用查询权限语法查询登录所用的root用户创建视图的权限，执行的SQL语句如下。SELECTSelect_priv,Create_view_priv

FROMmysql.user

WHEREuser='root';“root”为当前登录的用户名称。说明：执行上述SQL语句，在查询结果中查看当前权限，右如图所示。由运行结果可以得知：结果中字段“Select_priv”和“Create_view_priv”的值都为Y，表示root用户具有SELECT和CREATEVIEW，即查看和创建视图的权限。7.1.1创建视图2.创建视图在MySQL中，使用CREATEVIEW语句创建视图，其语法格式如下：CREATE[ALGORITHM] VIEW视图名[(属性清单)] ASSELECT语句;（1）ALGORITHM：可选参数，表示视图选择的算法。（2）视图名：表示要创建的视图名称。（3）属性清单：可选参数，指定视图中各个属性的名词，默认情况下与SELECT语句中查询的属性相同。（4）SELECT语句：一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。说明：7.1.1创建视图【案例7.2】创建视图查找学生表的全部信息，命名为view_stu。登录MySQL终端后在db_study数据库中，执行如下SQL语句。CREATEVIEWview_stu

ASSELECT*FROMtb_student;执行上述SQL语句，创建一个名称为“view_stu”的视图，如下图所示，视图创建成功。7.1.1创建视图创建视图时需要注意以下几点：（1）运行创建视图的语句需要用户具有创建视图的权限，若加了[orreplace]参数，还需要用户具有删除视图的权限。（2）在使用SELECT语句时不能包含FROM子句中的子查询、不能引用系统或用户变量以及不能引用预处理语句参数。（3）在存储子程序内，定义不能引用子程序参数或者局部变量。（4）在定义中引用的表或视图必须存在。但是，创建视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECKTABLE语句。（5）在定义中不能引用temporary表，不能创建temporary视图。（6）在视图定义中命名的表必须已存在。（7）不能将触发程序与视图关联在一起。（8）在视图定义中允许使用ORDERBY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDERBY的语句，它将被忽略。7.1.2查看视图1.使用DESCRIBE语句查看视图使用DESCRIBE语句时，可以将DESCRIBE缩写成DESC，DESC语句如下。DESC视图名;查看视图是指查看已存在的视图，查看视图必须要有SHOWVIEW权限。查看视图的方法主要包括使用DESCRIBE语句、SHOWTABLESTATUS语句、SHOWCREATEVIEW语句等。7.1.2查看视图【案例7.3】使用DESC语句查询【案例7.2】创建的视图view_stu的结构。登录MySQL终端后在db_study数据库中，执行如下SQL语句。DESCview_stu;执行上述SQL语句，查询视图中的结构如下图所示，能够了解视图中各个字段的简单信息。7.1.2查看视图2.使用SHOWTABLESTATUS语句查看视图在MySQL中如果需要查看视图信息，可以使用SHOWTABLESTATUS语句，其语法格式如下。在MySQL的命令行窗口中，语句结束符可以为“;”、“\g”或者“\G”。其中，“;”和“\g”的作用是一样的。都是按照表格的形式显示结果，而“\G”则会把原来的列按照行显示。说明：SHOWTABLESTATUSLIKE'视图名';

【案例7.4】查看视图view_stu的信息。登录MySQL终端后在db_study数据库中，执行如下SQL语句。SHOWTABLESTATUSLIKE'view_stu'\G7.1.2查看视图执行上述SQL语句，查看视图view_stu的结构。视图结构如左下图所示。以同样的方式查询学生表，得到如右下图所示，学生表的信息。对比查看结果可以观察出，视图中存储引擎、数据长度等信息都显示为NULL，而学生表是具有具体参数的，说明了视图为虚拟表，与普通数据表有所区别。

7.1.2查看视图3.使用SHOWCREATEVIEW语句查询视图在MySQL中我们还可以通过SHOWCREATEVIEW语句查看视图的详细定义，其语法如下。SHOWCREATEVIEW视图名;【案例7.5】查看视图view_stu的详细定义。登录MySQL终端后在db_study数据库中，执行如下SQL语句。SHOWCREATEVIEWview_stu\G执行上述SQL语句，查看视图的详细定义，其定义如右图所示。7.1.3修改视图修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间的一致。MySQL中可以通过CREATEORREPLACEVIEW语句和ALTERVIEW语句来修改视图。7.1.3修改视图1.使用CREATEORREPLACEVIEW语句修改视图CREATEORREPLACEVIEW语句在MySQL的使用中可以非常灵活，在视图已经存在的情况下，对视图进行修改；若视图不存在时，则可以创建视图。其语法如下。CREATEORREPLACEVIEW视图[(属性清单)]ASSELECT语句;【案例7.6】将视图view_stu的字段修改为name、gender、height。登录MySQL终端后在db_study数据库中，执行如下SQL语句。CREATEORREPLACEVIEWview_stu(name,gender,height)ASSELECTstudent_name,student_gender,student_heightFROMtb_student;7.1.3修改视图执行上述SQL语句，“view_stu(name,gender,height)”为指定的所需要修改的视图及其字段，分别对应SELECT子句中的三个字段。如下图所示，视图修改成功。使用DESC语句重新查询view_stu视图，执行SQL语句“DESCview_stu;”，执行结果如下图所示。从左图可以得知，视图修改后只剩下三个字段，表示修改成功。7.1.3修改视图2.使用ALTERVIEW语句修改视图ALTERVIEW语句改变了视图的定义，包括索引视图，但不影响所依赖的存储过程或触发器。该语句与CREATEVIEW语句有着同样的限制，如果删除并重建了一个视图，就必须重新为它分配权限。【案例7.7】修改视图view_stu以学生表为基本表。登录MySQL终端后在db_study数据库中，执行如下SQL语句。ALTERVIEWview_stuASSELECT*FROMtb_student;ALTERVIEW语句语法如下。ALTERVIEW视图名ASSELECT语句;（1）视图名为指定修改的视图名称。（2）SELECT语句为重新定义视图内容。说明：7.1.3修改视图执行上述SQL语句，指定视图“view_stu”，内容修改SELECT子句中查询学生表所有信息。如下图所示，语句执行成功。修改成功后，执行SQL语句“SELECT*FROMview_stu;”，如下图所示查看视图。同理可知，视图的定义也被修改。7.1.4更新视图1.更新视图的方法对于插入、更新和删除操作，其语法格式与对表格数据增删改相似，以更新为例。更新视图其语法格式如下。（1）视图名为指定更新的视图名称。（2）字段1对应值1，字段2对应值2，以此类推。更新规范与数据表的数据更新一致。说明：UPDATE视图名

SET字段1=值1,字段2=值2...[WHERE条件表达式];

因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，实际上都是在基本表中执行更新操作。更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。但是，更新视图时，只能更新权限范围内的数据，超出了范围就不能更新。7.1.4更新视图【案例7.8】修改视图view_stu中姓黄的同学身高为0。登录MySQL终端后在db_study数据库中，执行如下SQL语句。UPDATEview_stu

SETstudent_height=0WHEREstudent_name

LIKE'黄%';执行上述SQL语句，指定更新“view_stu”，将字段“student_height”赋值为0，指定条件范围为黄姓的同学。如下图所示，视图更新成功。7.1.4更新视图修改成功后，执行SQL语句“SELECTstudent_name,student_height

FROMview_stu

WHEREstudent_name

LIKE‘黄%’;”和“SELECTstudent_name,student_height

FROMtb_student

WHEREstudent_name

LIKE‘黄%’;”查看更新后的视图数据和基本表数据，如下图所示查看两表数据。由此可见，对视图的修改实际上是实现在基本表中的。相对地，对视图的增加和删除可以参考第8章

数据处理之增删改。7.1.4更新视图（1）视图中包含COUNT()、SUM()、MAX()和MIN()等函数时。例如：虽然可以在视图中更新数据，但是一般情况下，最好将视图作为查询数据的虚拟表，而不是通过视图更新数据。因为在进行更新操作时很容易由于考虑不全面而导致操作失败。注意：以下这类情况是不能更新视图的。CREATEVIEW视图名(字段1,字段2)ASSELECT字段3,COUNT(字段4)FROM数据表;

（2）视图中包含UNION、UNIONALL、DISTINCT、GROUPBY和HAVING等关键字。例如：CREATEVIEW视图名(字段1,字段2)ASSELECT子句

GROUPBY字段3;

（3）常量视图。例如：CREATEVIEW视图名(字段1,字段2)ASSELECT常量字符

AS别名;

7.1.4更新视图（4）视图中的SELECT中包含子查询。例如：（5）由不可更新的视图导出的视图。例如：CREATEVIEW视图名1ASSELECT*FROM视图名2;（6）创建视图时，ALGORITHM为TEMPTABLE类型。例如：（7）视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。例如，表中包含的name字段没有默认值，但是视图中不包括该字段，那么这个视图是不能更新的。因为在更新视图时，这个没有默认值的记录将没有值插入，也没有NULL值插入。CREATEVIEW视图名(字段)ASSELECT子句(SELECT子查询);

CREATEVIEWALGORITHM=TEMPTABLE视图名ASSELECT*FROM数据表;总结：在视图的数据和基本表的数据不同时，无法更新或者创建视图。7.1.5删除视图“IFEXISTS”表示执行操作前先检测是否存在满足该语句的对象，存在即可继续执行，不存在则停止操作。说明：DROPVIEWIFEXISTS视图名；删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROPVIEW语句来删除视图。但是，用户必须拥有DROP权限。DROPVIEW其语法格式如下。7.1.5删除视图【案例7.9】删除视图view_stu。登录MySQL终端后在db_study数据库中，执行如下SQL语句。DROPVIEWIFEXISTSview_stu;执行上述SQL语句，如下图所示，视图删除成功。操作成功后，执行“SHOWCREATEVIEWview_stu;”SQL语句，如下所示，视图不存在表示删除成功。索引027.2索引索引是一种将数据库中单列或者多列的值进行排序的结构。应用索引，可以大幅度提高查询的速度，还可以降低服务器的负载。用户查询数据时，系统可以不必遍历数据表中的所有记录，而是查询索引列。一般形式的数据查询是通过遍历数据后，寻找数据库中的匹配记录而实现的。与一般形式的查询相比，索引就像一本书的目录。通过目录可以直接查询到书的某节。大大缩短了查询时间，提高了查找速度。所以，使用索引可以有效地提高数据库系统的整体性能。应用MySQL数据库时，用户在查询数据时并非总需要应用索引来优化查询。诚然，使用索引可以提高检索数据的速度。但是，创建和维护索引是需要耗费时间的，并且所耗费的时间与数据量的大小成正比；另外，索引需要占用物理空间，会给数据的维护造成很多麻烦。整体来说，索引可以提高查询的速度，但是会影响用户操作数据库时的插入操作。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。所以，用户可以将索引删除后插入数据，当数据插入操作完成后，再重新创建索引。7.2索引MySQL的索引包括普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引等。普通索引普通索引即无任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。唯一索引使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊的唯一索引。全文索引使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，查询带有文章回复内容的字段，可以应用全文索引方式。需要注意的是，在默认情况下，应用全文索引时大小写不敏感。如果索引的列使用二进制排序，可以执行大小写敏感的全文索引。7.2索引单列索引单列索引是对应一个字段的索引，它包括前3种索引方式。应用该索引的条件是保证该索引值对应一个字段。多列索引多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。空间索引使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。7.2.1创建索引1.在建立数据表时创建索引在创建数据表时可以创建索引，这种方式较直接、方便且易用。在建立数据表时创建索引的基本语法结构如下。CREATETABLE表名(字段1数据类型[约束条件],...,[UNIQUE|FULLTEXT|SPATIAL]INDEX[别名](字段1[(长度)],...[ASC|DESC]));创建索引是指在某个表的至少一列中建立索引，以提高表的访问速度和数据库性能。本节通过几种不同的方式创建索引，包括在建立数据库时创建索引、在已建立的数据表中创建索引和修改数据表结构添加索引。7.2.1创建索引说明：（1）UNIQUE：可选项，表明索引为唯一索引。（2）FULLTEXT：可选项，表明索引为全文索引。（3）SPATIAL：可选项，表明索引为空间索引。（4）长度：可选项，指索引长度，必须是字符串类型才可以使用。（5）ASC/DESC：可选项，表示升序/降序排序。7.2.1创建索引【案例7.10】在db_study数据库中创建普通索引。登录MySQL终端后在db_study数据库中，执行如下SQL语句。CREATETABLEtest(aaINTNOTNULL,bb

VARCHAR(30)NULL,INDEX(aa));执行上述SQL语句，创建数据表test，其中有两个字段分别为“aa”和“bb”，INDEX(aa)意为对字段aa创建索引。如下图所示，索引创建成功。7.2.1创建索引其他类型的索引即在普通索引的基础上加上约束条件或改变字段数量即可。要注意的是，全文索引只能作用在CHAR、VARCHAR、TEXT类型的字段上；单列索引则不需要约束参数，仅需指定单列字段名；多列索引则是指定多个字段名。说明：操作成功后，使用SHOWINDEX语句查看索引，执行SQL语句“SHOWINDEXFROMtest\G”，如下图所示，含有一个索引。7.2.1创建索引2.在已建立的数据表中创建索引在MySQL中，不但可以在创建数据表时创建索引，还可以在已创建的数据表创建索引。方式1：直接创建索引直接创建索引基本语法格式如下。CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(字段名

[(长度)],...[ASC|DESC]);

同个字段可以有多个索引。与建立数据表时创建索引相同，在已建立的数据表中创建索引同样包含6种索引方式。说明：【案例7.11】在test数据表中创建普通索引。登录MySQL终端后在db_study数据库中，执行如下SQL语句。CREATEINDEXiiONtest(aa);7.2.1创建索引操作成功后，使用SHOWINDEX语句查看索引，执行SQL语句“SHOWINDEXFROMtest;”，如下图所示，含有两个索引。执行上述SQL语句，指定test数据表中的字段aa创建名称为“ii”的索引。如下图所示，索引创建成功。7.2.1创建索引说明：以下分别列出建立其他类型索引命令。CREATEUNIQUEINDEX索引名ON数据表名(字段名);--------------------唯一索引CREATEFULLTEXTINDEX索引名ON数据表名(字段名);------------------全文索引CREATEINDEX索引名ON数据表名(字段名(长度));---------------------单列索引CREATEINDEX索引名ON数据表名(字段名1,字段名2...);--------------多列索引CREATESPATIALINDEX索引名

ON数据表名(字段名);-------------------空间索引

7.2.1创建索引方式2：修改数据表结构添加索引修改已经存在表上的索引，可以通过ALTERTABLE语句为数据表添加索引，其基本格式如下。【案例7.12】在test数据表中添加普通索引。登录MySQL终端后在db_study数据库中，执行如下SQL语句。ALTERTABLEtestADDINDEXbb(aa);ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名

[(长度)],...[ASC|DESC]);

7.2.1创建索引操作成功后，使用SHOWINDEX语句查看索引，执行SQL语句“SHOWINDEXFROMtest;”，如下图所示，含3个索引。执行上述SQL语句，修改test数据表，对其字段aa添加名称为“bb”的索引。如下图所示，索引添加成功。7.2.1创建索引说明：以下分别列出添加其他类型索引命令。ALTERTABLE表名ADDUNIQUEINDEX索引名(字段名);------------------唯一索引ALTERTABLE表名ADDFULLTEXTINDEX索引名(字段名);----------------全文索引ALTERTABLE表名ADDINDEX索引名(字段名(长度));-------------------单列索引ALTERTABLE表名ADDINDEX索引名(字段名1,字段名2,...);-----------多列索引ALTERTABLE表名

ADDSPATIALINDEX索引名(字段名);-----------------空间索引

7.2.2删除索引在MySQL中，创建索引后，如果用户不再需要该索引，则可以删除指定表的索引。因为这些已经建立但不常使用的索引，一方面会占用系统资源，另一方面可能导致更新速度下降，这极大地影响了数据表的性能。删除索引可以通过DROP语句来实现。其基本语法格式如下。【案例7.13】删除test数据表中的索引。登录MySQL终端后在db_study数据库中，执行如下SQL语句。DROPINDEXaaONtest;DROPINDEX索引名

ON数据表名;

7.2.2删除索引操作成功后，使用SHOWCREATETABLE语句查看索引，执行SQL语句“SHOWCREATETABLEtest;”，如下图所示，剩余2个索引。执行上述SQL语句，删除数据表test中的索引aa。如下图所示，索引删除成功。课业任务037.3课业任务课业任务7-1创建视图求计算机系的人数课业任务7-2创建视图求每位同学的成绩课业任务7-3创建唯一索引课业任务7-4使用Workbench工具创建索引课业任务7-5使用Navicat工具创建视图王小明想利用MySQL+Java开发一个数据库学习系统，在熟悉了MySQL数据库表记录的检索知识后，需熟悉MySQL数据库视图和索引的实际操作，熟练掌握该操作将为后续开发数据库学习系统打下良好的基础，现通过5个课业任务来完成。具体请见实验6常见错误及解决方案047.4.1错误7-1创建视图失败7.4.2错误7-2创建唯一索引失败具体请见实验67.4常见错误及解决方案选择题填空题判断题操作题（1）视图的作用有（

）。A.简单性B.安全性C.逻辑数据独立性D.以上都是（2）下列选项中，不是索引的类型的是（

）。A.唯一索引B.普通索引C.空间索引D.时间索引DD选择题填空题判断题操作题（3）下列选项中，不是创建索引时的可选项的是（

）。A.UNIONB.FULLTEXTC.SPATIALD.INDEX（4）在MySQL中，删除索引的关键字是（

）。A.DROPB.DELETEC.DESCRIBED.以上说法都不正确AA选择题填空题判断题操作题（5）创建视图最基本需要（

）两个权限。A.查看和删除B.创建和删除C.创建和查看D.以上都不正确C（2）请写出查询的类型，分别是（）。（3）根据查询的类型写出对应类型的关键字（）。（4）相等连接和自然连接属于（）。（5）WHERE和HAVING哪个关键字后可以使用聚合函数（）。选择题填空题判断题操作题（1）请写出视图的作用，分别是（）。答案：（1）1.它将复杂的查询简单化，更利于用户理解和使用；2.视图可以隐藏一些数据，保护数据库的安全性。3.降低数据冗余：视图可以将多个表中的数据进行联接，并以虚拟表的形式呈现。这样可以避免在数据库中存储冗余的数据，提高了存储空间的利用率。（2）请写出索引的类型，分别是（）。答案：（2）索引包括普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引等。（2）请写出查询的类型，分别是（）。（3）根据查询的类型写出对应类型的关键字（）。（4）相等连接和自然连接属于（）。（5）WHERE和HAVING哪个关键字后可以使用聚合函数（）。选择题填空题判断题操作题（3）根据索引的类型写出索引不同类型对应的可选项或关键字（）。答案：（3）主键索引：可选项或关键字为：PRIMARYKEY。唯一索引：可选项或关键字为：UNIQUE。3.聚簇索引：可选项或关键字为：CLUSTERED。4.非聚簇索引：可选项或关键字为：NONCLUSTERED。（4）删除视图时应该使用哪个关键字（）。答案:(4)删除视图时应该使用关键字DROP选择题填空题判断题操作题（5）请至少默写出创建索引两个语法（）。答案：（5）使用CREATEINDEX语句创建索引，语法为：CREATEINDEXindex_nameONtable_name(column_name)；

使用ALTERTABLE语句创建索引，语法为：ALTERTABLEtable_nameADDINDEXindex_name(column_name)。判断题填空题选择题操作题（1）创建主键时自动创建唯一索引（

）（2）创建视图时不可以使用SELECT子查询（

）（3）创建视图时可以不使用别名（

）（4）视图的功能是优化查询。（

）（5）索引能够保证数据安全性。（

）√√×

××操作题选择题填空题判断题（1）使用MySQL终端创建视图求工商管理学院的人数。CREATEVIEWbusiness_management

ASSELECTCOUNT(*)AScountFROMstudentsWHERE学院='工商管理学院';（2）使用MySQL终端创建视图求工商管理学院同学的分数。CREATEVIEWbusiness_management_scores

ASSELECT学号,成绩FROMgradesWHERE学院='工商管理学院';操作题选择题填空题判断题（3）使用MySQL终端创建一个全文索引。ALTERTABLE表名ADDFULLTEXT索引名(列名);（4）使用MySQL终端删除索引。ALTERTABLE表名DROPINDEX索引名;操作题选择题填空题判断题（5）使用MySQLWorkbench图形化工具创建学生表视图。1.打开MySQLWorkbench并连接到MySQL服务器。2.在左侧的"管理导航器"中，展开数据库并选择要创建视图的数据库。3.在"对象信息"选项卡中，选择"视图"选项卡。4.点击右键并选择"创建视图"。5.在"创建视图"对话框中，输入视图的名称。6.在"定义视图"文本框中，输入创建视图的SQL语句。例如：```SELECT*FROMstudents;```7.点击"应用"按钮并关闭对话框。视图将被创建并显示在"视图"选项卡下。操作题选择题填空题判断题（6）使用Navicat

Premiun

16图形化工具创建一个普通索引。1.打开NavicatPremium16并连接到MySQL服务器。2.在左侧的"连接器"窗格中，展开数据库并选择要创建索引的表。3.在右侧的"数据库对象"窗格中，选择要创建索引的表。4.在"属性"选项卡中，展开"索引"部分。5.点击"+"按钮以添加一个新索引。6.在"索引名称"字段中输入索引的名称。7.在"索引类型"下拉菜单中选择合适的索引类型（如BTREE或HASH）。8.在"列"列表中选择要包含在索引中的列。9.点击"应用"按钮以创建索引。本讲拓展InnoDB的索引模型：在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

mysql>createtableT(

idintprimarykey,

kintnotnull,

namevarchar(16),

index(k))engine=InnoDB;感谢观看数据处理之增删改第8章通过本课程的学习，您将应该：了解数据库的相关概念熟悉数据库工具的使用熟悉SQL语言的规则与规范熟练掌握MySQL数据库管理方法熟练掌握MySQL表结构管理方法熟练掌握表记录的检索方法熟练掌握视图与索引的使用方法熟练掌握数据处理之增删改熟练掌握存储过程与游标的使用方法熟练掌握存储函数的应用熟练掌握触发器的应用熟练掌握事务的基本特性和应用场景熟练掌握数据库安全及管理熟练掌握MySQL数据库备份和恢复操作了解利用MySQL+Java开发一个数据库学习系统并部署课程目标项目部署成功本讲教学目标熟练使用INSERT、UPDATE和DELETE语句对数据库的数据进行增删改操作。熟练使用不同的MySQL工具对数据表中的数据进行增、删、改操作。通过完成课业任务学会分析问题、解决问题，提高动手操作能力。通过本讲的学习，您将可以：1.OPTION2.OPTIONOPTION3.tb_course表的数据修改目录Content插入数据01更新数据02删除数据03课业任务04常见错误及解决方案05插入数据018.1.1通过INSERT...VALUES语句插入数据在建立一个空的数据表后，首先需要考虑如何向数据表中添加数据，该操作可以使用INSERT语句来完成。在MySQL中，INSERT语句有3种语法格式，分别是INSERT...VALUES、INSERT...SET和INSERT...SELECT语句。使用INSERT...VALUES语句插入数据，是INSERT语句最常用的语法格式，格式如下：（1）INTO关键字在MySQL中可以省略。（2）如需指定字段输入，字段1对应插入的值1，字段2对应插入的值2，以此类推。（3）插入值的数据类型和对应开的数据类型一定要匹配，如果类型不同，将无法插入。说明：INSERT[INTO]数据表

[(字段1,字段2...)]VALUES(值1,值2...)

使用INSERT...VALUES语句插入数据有两种情况，第一种情况为表的所有字段均插入数据，第二种情况为表的指定字段插入数据。8.1.1通过INSERT...VALUES语句插入数据插入数据前执行“SHOWDATABASES;”SQL语句查看数据库；执行“USEdb_study;”SQL语句使用“db_study”数据库；执行“SHOWTABLES;”SQL语句查看数据库中的数据表，并执行“SELECT*FROMtb_department;”查询语句查询表中记录，此时是tb_department数据表插入数据前的数据，执行效果如右图所示。8.1.1通过INSERT...VALUES语句插入数据【案例8.1】向系部表中的所有字段插入数据。从右图得知，系部表中有四个字段，利用INSERT...VALUES语句分别插入四个字段的值成为一行新记录，执行的SQL语句如下。INSERTINTOtb_department

VALUES('X09','环境科学与工程学院','87471239','工程楼’);

或INSERTINTOtb_department

(department_id,department_name,department_phone,department_address)VALUES('X09','环境科学与工程学院','87471239','工程楼');

8.1.1通过INSERT...VALUES语句插入数据执行上述SQL语句，运行结果如下图所示，数据添加成功。8.1.1通过INSERT...VALUES语句插入数据VALUES字句后面的值需要用引号括起来，原因是对应字段的数值类型是VARCHAR，如果是INT类型可以省略引号输入。说明：执行上述SQL语句后，重新执行“SELECT*FROMtb_department;”SQL语句查询系部表记录，查询结果如下图所示，已经成功添加了一条记录。8.1.1通过INSERT...VALUES语句插入数据登录MySQL终端后在db_study数据库中，利用INSERT...VALUES语句分别为字段“department_id”和“department_name”插入值成为一行新记录，执行的SQL语句如下。执行上述SQL语句，运行结果如下图所示，数据添加成功。INSERTINTOtb_department(department_id,department_name)VALUES('X10','医学院');

【案例8.2】向系部表中指定字段插入数据8.1.1通过INSERT...VALUES语句插入数据（1）一定需要注意插入数据的过程中，对应字段插入对应的值。（2）插入的过程中字段顺序不一定需要按照数据表所示的字段顺序。（3）未被插入数据的字段一定是允许为空的，未插入的字段将显示默认值。说明：执行上述SQL语句后，重新执行“SELECT*FROMtb_department;”SQL语句，查询系部表新记录，查询结果如下图所示，已经成功添加了一条新记录，该记录只有两个字段有数据，另外两个字段的值为默认值NULL。8.1.2通过INSERT...SET语句插入数据在MySQL中除了INSERT...VALUES语句可以对指定字段插入数据以外，还能利用INSERT...SET语句往数据表中插入数据，其基本语法格式如下：INSERTINTO数据表

SET字段1=值1,字段2=值2......

【案例8.3】向系部表中指定字段插入数据。登录MySQL终端后在db_study数据库中，需要利用INSERT...SET语句分别为字段“department_id”和“department_name”插入值成为一行新记录，执行的SQL语句如下。INSERTINTOtb_department

SETdepartment_id='X10',department_name='医学院';

8.1.2通过INSERT...SET语句插入数据执行上述SQL语句，运行结果如下图所示，数据添加成功。执行上述SQL语句后，重新执行“SELECT*FROMtb_department;”SQL语句查询系部表新记录，查询结果如下图所示，已经成功插入一条编号为“X10”，部门名称为“医学院”的新记录。8.1.2通过INSERT...SET语句插入数据【案例8.4】向系部表插入两行数据。登录MySQL终端后在db_study数据库中，利用INSERT...SET和INSERT...VALUES语句分别向系部表插入两行新记录，执行的SQL语句如下。INSERTINTOtb_department

SETdepartment_id='X09',department_name='环境科学与工程学院',department_phone='87471239',department_address='工程楼';INSERTINTOtb_department

SETdepartment_id='X10',department_name='医学院',department_phone='87471240',department_address='生物综合楼';

或者INSERTINTOtb_department

VALUES('X09','环境科学与工程学院','87471239','工程楼'),('X10','医学院','87471240','生物综合楼');

8.1.2通过INSERT...SET语句插入数据执行上述SQL语句，运行结果如下图所示，数据添加成功。8.1.2通过INSERT...SET语句插入数据执行上述SQL语句后，重新执行SQL语句“SELECT*FROMtb_department;”查询系部表新记录，查询结果如下图所示，已经成功插入了两行记录。8.1.3通过INSERT...SELECT语句插入查询结果在MySQL中，支持将查询结果插入指定的数据表中，这可以通过INSERT...SELECT语句来实现，其语法格式如下：（1）[INTO]数据表，用于指定被操作的数据表，其中，[INTO]为可选项，可以省略。（2）[(字段1,字段2...)]为可选项，当不指定该选项时，表示要向表中所有列插入数据，否则表示向数据表的指定列插入数据。（3）SELECT子句用于快速地从一个或者多个表中获取数据，并将这些数据作为行数据插入目标数据表中。需要注意的是，SELECT子句返回的结果集中的字段数、字段类型必须与目标数据表完全一致。说明：INSERT[INTO]数据表

[(字段1,字段2...)]SELECT...

8.1.3通过INSERT...SELECT语句插入查询结果登录MySQL终端后在db_study数据库中，执行SQL语句“SHOWTABLES;”查看数据库中的数据表，并执行结构查看语句“DESCtb_department;”查看系部表的结构，系部表结构如下图所示。【案例8.5】将系部表中的所有数据插入到测试表中。8.1.3通过INSERT...SELECT语句插入查询结果从查询结果中得知系部表的字段名称以及数据类型等结构，利用SQL语句创建一个与系部表结构相同的新的数据表，并将其命名为“tb_test”，执行的SQL语句如下。执行上述SQL语句，运行结果如下图所示，数据表创建成功。CREATETABLEtb_test

LIKEtb_department;数据表创建成功后，重新执行“DESCtb_test;”SQL语句查看测试表结构，操作结果如右图所示，测试表结构与系部表结构相同。8.1.3通过INSERT...SELECT语句插入查询结果确认tb_test和tb_department数据表结构相同后，利用INSERT...SELECT语句向测试表插入在系部表中的所有记录，执行的SQL语句如下。执行上述SQL语句，运行结果如下图所示，数据添加成功。INSERTINTOtb_test

(department_id,department_name,department_phone,department_address)SELECT*FROMtb_department;8.1.3通过INSERT...SELECT语句插入查询结果执行上述SQL语句后，重新执行SQL语句“SELECT*FROMtb_test;”查询测试表中的记录，查询结果如下图所示，系部表的数据已经成功插入到了tb_test数据表中。8.1.3通过INSERT...SELECT语句插入查询结果由以上案例得知：使用INSERT…VALUES语句可以向表中插入一行数据，也可以插入多行数据；使用INSERT…SET语句可以指定插入行中每列的值，也可以指定部分列的值；使用INSERT…SELECT语句可以向表中插入其他表的记录。

在实际数据库开发中推荐使用完整的对应字段插入对应值的INSERT...VALUES语句。因为在数据库维护和更新过程中，存在修改字段顺序或者修改字段数值类型的情况，如果是使用按字段顺序插入的INSERT语句会对数据库的维护增加难度，约束了数据库的拓展性。在插入多条记录时，INSERT...VALUES语句的批量插入在运行效率上会远大于其他语句。更新数据028.2更新数据在MySQL中使用UPDATE语句可以更新表中的记录，其语法格式如下：UPDATE[IGNORE]数据表SET字段1=值1[,字段2=值2...][WHERE条件表达式][ORDERBY...][LIMIT行数]（1）[IGNORE]在MySQL中，通过UPDATE语句更新表中多行数据时，如果出现错误，那么整个UPDATE语句操作都会被取消，错误发生前更新的所有行将被恢复到它们原来的值。因此，为了在发生错误时也要继续进行更新，可以在UPDATE语句中使用IGNORE关键字。（2）SET子句为必选项，用于指定表中要修改的字段名及其字段值。其中的值可以是表达式，也可以是该字段所对应的默认值。如果要指定默认值，可使用关键字DEFAULT。（3）WHERE子句为可选项，用于限定表中要修改的行，如果不指定该字句，那么UPDATE语句会更新表中的所有行。（5）ORDERBY子句为可选项，用于限定表中的行被修改的次序。（6）LIMIT子句为可选项，用于限定被修改的行数。说明：8.2更新数据执行更新操作前tb_department数据表中的数据如右图所示。【案例8.6】更新系部表中的内容。8.2更新数据执行上述SQL语句，运行结果如下图所示，数据更新成功。UPDATEtb_departmentSETdepartment_name='马克思主义学院',department_address='思政楼'WHEREdepartment_id='X10';利用UPDATE语句将系部表中的第十行记录进行修改，执行的SQL语句如下。8.2更新数据执行上述SQL语句后，重新执行“SELECT*FROMtb_department;”SQL语句查询系部表中的记录，查询结果如下图所示，第十行记录中字段department_name的值“医学院”修改为“马克思主义学院”。说明：（1）通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行。（2）更新时一定要保证WHERE子句的正确性，一旦WHERE子句出错，将会破坏所有改变的数据。删除数据038.3.1通过DELETE语句删除数据在数据库中，有些数据已经失去意义或者发生错误，此时需要将它们删除。在MySQL中，可以使用DELETE或者TRUNCATETABLE语句删除表中的一行或者多行数据。DELETE[QUICK][IGNORE]FROM数据表[WHERE条件表达式][ORDERBY...][LIMIT行数]通过DELETE语句删除数据的基本语法格式如下。8.3.1通过DELETE语句删除数据说明：（1）[QUICK]为可选项，用于加快部分种类的删除操作速度。（2）[IGNORE]在MySQL中，通过DELETE语句删除表中多行数据时，如果出现错误，那么整个DELETE语句操作都会被取消，错误发生前更新的所有行将被恢复到它们原来的值。因此，为了在发生错误时也要继续进行删除，可以在DELETE语句中使用IGNORE关键字。（3）WHERE子句为可选项，用于限定表中要删除的行，如果不指定该字句，那么DELETE语句会删除表中的所有行。（5）ORDERBY子句为可选项，用于限定表中的行被删除的次序。（6）LIMIT子句为可选项，用于限定被删除的行数。8.3.1通过DELETE语句删除数据登录MySQL终端后在db_study数据库中，执行SQL语句“SELECT*FROMtb_department;”查询执行删除操作前tb_departmen数据表的数据如下图所示。【案例8.7】删除系部表中的一行记录。8.3.1通过DELETE语句删除数据利用DELETE语句删除系部表中的第十行记录，执行的SQL语句如下。DELETEFROMtb_departmentWHEREdepartment_id='X10';执行上述SQL语句，运行结果如右图所示，数据删除成功。8.3.1通过DELETE语句删除数据执行上述SQL语句后，重新执行“SELECT*FROMtb_department;”SQL语句查询系部表中的记录，查询结果如图8.22所示，第十行记录已经删除成功。在实际应用中，执行删除的条件一般应该为数据的id，而不是具体某个字段值，这样可以避免一些错误发生。说明：8.3.2通过TRUNCATETABLE语句删除数据如果要删除表中所有行，可以通过TRUNCATETABLE语句实现，其基本语法格式如下。TRUNCATE[TABLE]数据表名说明：数据表名表示删除的数据表名称，也可以使用“数据库名.数据表名”来指定该数据表隶属于哪个数据库。TEXTTEXTTEXT8.3.2通过TRUNCATETABLE语句删除数据登录MySQL终端后，使用TRUNCATETABLE语句清空系部表数据，执行的SQL语句如下。TRUNCATETABLEdb_study.tb_department;执行上述SQL语句后，重新执行“USEdb_study;”“SELECT*FROMtb_department;”SQL语句查询系部表中的记录，查询结果如右图所示，系部表已经没有任何数据。【案例8.8】清空系部表数据。执行上述SQL语句，运行结果如下图所示，数据删除成功。8.3.2通过TRUNCATETABLE语句删除数据（1）使用TRUNCATETABLE语句后，表中的AUTO_INCREMENT计数器将被重新设置为该列的初始值。（2）对于参与了索引和视图的表，不能使用TRUNCATETABLE语句来删除数据，而应该使用DELETE语句。（3）TRUNCATETABLE操作比DELETE操作使用的系统和事务日志资源少。DELETE语句每删除一行都会在事务日志中添加一行记录，而TRUNCATETABLE语句是通过释放存储表数据所用的数据页来删除数据的，因此只在事务日志中记录页的释放。DELETE语句和TRUNCATETABLE语句的区别如下：8.3.3DELETE、TRUNCATE和DROP的区别1.表和索引所占空间当表被TRUNCATE语句删除后，该表和索引所占用的空间会恢复到初始大小；DELETE语句操作不会减少表或索引所占用的空间；DROP语句将表所占用的空间全释放掉。2.应用范围（1）TRUNCATE只能对数据表有效。（2）DELETE可以对数据表和视图有效。3.执行速度DROP>TRUNCATE>DELETE。课业任务048.4课业任务课业任务8-1往课程表插入一行记录课业任务8-2往课程表同时插入多行记录课业任务8-3更新课程表中的字段内容课业任务8-6使用Navicat工具更新课程表中的内容课业任务8-4删除课程表中插入的多行记录课业任务8-5使用Workbench工具删除课程表中的记录王小明想利用MySQL+Java开发一个数据库学习系统，在熟悉了MySQL视图与索引知识后，需要熟悉对数据表的数据进行增、删、改操作，并能够灵活地使用这些操作对数据进行管理，现通过6个课业任务来完成。具体请见实验7常见错误及解决方案058.5.1错误8-1向课程表插入一行数据失败8.5.2错误8-2TRUNCATE语句清空表格失败具体请见实验78.5常见错误及解决方案选择题填空题判断题操作题（1）下列哪个关键字是插入数据（）A.INSERTB.UPDATEC.DELETED.TRUNCATE（2）下列哪个选项的可选项关键字使用正确（）。A.INSERTINTOB.UPDATEFROMC.DELETETABLED.DROPRROMAA选择题填空题判断题操作题（3）下列哪个选项不是删除表格数据的关键字（）。A.DROPB.DELETEC.TRUNCATED.以上都不是（4）删除数据操作失败的原因可能是（）。A.外键B.数值类型C.索引D.以上说法都正确AA选择题填空题判断题操作题（5）需要一条语句插入多条记录时我们需要用什么符号隔开（）。A.，B.：C.；D.以上都不正确A选择题填空题判断题操作题（1）数据表的几大处理方式，分别是（）。答案：（1）1.创建（Create）：用于创建新的数据表。2.查询（Select）：用于检索数据表中的数据。3.更新（Update）：用于修改数据表中的数据。4.删除（Delete）：用于删除数据表中的数据。5.修改（Alter）：用于修改已存在的数据表结构。6.截断（Truncate）：用于快速删除数据表中的所有数据。7.重建（Rebuild）：用于重建数据表或其索引以优化性能。（2）请写出查询的类型，分别是（）。（3）根据查询的类型写出对应