《mysql数据库实用教程教学课件作者郑明秋项目5视图、索引和事务的使用》由会员分享，可在线阅读，更多相关《mysql数据库实用教程教学课件作者郑明秋项目5视图、索引和事务的使用（39页珍藏版）》请在金锄头文库上搜索。
1、项目5,视图、索引和事务的使用,1,使学生了解视图的基本概念、作用和特点，掌握创建、修改和删除视图的方法，灵活运用视图简化表和简化数据的查询。 掌握索引的分类，根据数据的特点创建各类索引，以加快检索的速度。 了解事务的特点，事务的提交与回滚。,学习目标,2,5.1.1 视图的概述 视图作为一种数据库对象，为用户提供了可以检索数据表中数据的方式。用户通过视图来浏览数据表中感兴趣的部分或全部数据，而数据的物理存储仍然在表中。 视图是使用查询语句基于一个或多个基本表构建出来的虚表。对视图的一切操作最终都转换为对基本表的操作。,5.1 视图,3,5.1.1 视图的概述 视图的有以下特性： 1.简单性。
2、 2.安全性。 3.逻辑数据独立性。,5.1 视图,4,5.1.2 视图的创建与管理 视图不是实体的数据表，但却可以把相关联的表的数据汇集到一个“表”中，你可以一次查出所需数据，并且操作方便，就像从一个表中查出数据一样。 1.创建视图的语法格式:,5.1 视图,5,5.1.2 视图的创建与管理 【例51】在empMIS数据库中创建一个名为v_employee的视图，该视图显示employees表中员工编号是3开头的员工信息，包含eno、ename、ejob三列数据。,5.1 视图,6,5.1.2 视图的创建与管理 创建视图注意事项： 运行创建视图的语句需要用户具有创建视图（create vie
3、w）的权限，若加了or replace时，还需要用户具有删除视图（drop view）的权限； select语句不能包含from子句中的子查询； select语句不能引用系统或用户变量； select语句不能引用预处理语句参数； 在存储子程序内，定义不能引用子程序参数或局部变量； 在定义中引用的表或视图必须存在； 在定义中不能引用临时表，不能创建临时视图； 在视图定义中命名的表必须已存在； 不能将触发程序与视图关联在一起； 在视图定义中允许使用order by子句，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的子句，它将被忽略。,5.1 视图,7,5.1.2 视图的创
4、建与管理 2.查看视图 在MySQL中，show tables不仅可以用于查看当前数据库中存在的所有数据表，同时也可以查看到当前数据库中存在所有视图。,5.1 视图,8,5.1.2 视图的创建与管理 2.查看视图 仅仅使用show tables语句，在输出结果中，我们根本无法区分到底哪些是视图哪些才是真实的数据表（当然，视图的命名我们可以统一约定以“v_”开头）。 我们需要使用命令show full tables，该命令可以列出额外的table_type列，如果对应输出行上该列的值为“view”，则表示这是一个视图。,5.1 视图,9,5.1.2 视图的创建与管理 2.查看视图 可以使用如下命
5、令查看创建该视图的详细语句： show create view 视图名;,5.1 视图,10,5.1.2 视图的创建与管理 3.修改视图 修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MySQL中通过create or replace view语句或alter语句来修改视图。 MySQL中使用create or replace view语句修改视图，语法如下： create or replace algorithm = undefined | merge | temptable view 数据库名.视图名 (字段列表) as se
6、lect查询语句 with cascaded | local check option,5.1 视图,11,5.1.2 视图的创建与管理 3.修改视图 【例5.2】修改视图v_employee，显示所有员工信息。 create or replace view v_employee as select * from employees;,5.1 视图,12,5.1.2 视图的创建与管理 3.修改视图 alter语句是MySQL提供的另外一种修改视图的方法，语法如下： alter algorithm = undefined | merge | temptable view view_name (c
7、olumn_list) as select_statement with cascaded | local check option,5.1 视图,13,5.1.2 视图的创建与管理 3.修改视图 【例5.3】使用alter语句修改视图v_employee，视图显示employees表中员工编号是3开头的员工信息，包含eno、ename三列数据。,5.1 视图,14,5.1.2 视图的创建与管理 4.删除视图 语法格式： drop view if exists view_name , view_name2. 例如删除视图v_employee： drop view v_employee;,5.1
8、 视图,15,5.1.2 视图的创建与管理 注意，视图可以和基本表一样被查询，但是利用视图进行数据增、删、改操作，会受到一定的限制。以下类型视图不可以使用语句修改基本表数据。 （1）由两个以上的基本表导出的视图。 （2）视图的字段来自字段表达式函数。 （3）视图定义中有嵌套查询。 （4）在一个不允许更新的视图上定义的视图。,5.1 视图,16,索引是加速表内容访问的主要手段，特别对涉及多个表的连接查询更是如此，本节中，将介绍索引的特点，以及创建和删除索引的语法。,5.2 索引,17,5.2.1 索引简介 所有的MySQL列类型能被索引。在相关的列上的使用索引是改进select操作性能的最好方法
9、。 一个表最多可有16个索引。最大索引长度是256个字节，这可以在编译MySQL时被改变。 对于char和varchar列，可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于blob和text列，必须索引列的前缀，而不能索引列的全部。 MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在char和varchar列上，也可以使用列的前缀作为一个索引的部分）。,5.2 索引,18,5.2.1 索引简介 1.索引的分类 index索引：通常意义的索引，某些情况下key是它的一个同义词。索引的列可以包括重复的值。 unique索引：唯一索引，保证了列不包含重复的值，对于多
10、列唯一索引，它保证值的组合不重复。 primary key索引：也unique索引非常类似。事实上，primary key索引仅是一个具有primary名称的unique索引。这表示一个表只能包含一个primary key。 fulltext索引：全文索引，全文索引的索引类型为fulltext。全文索引只能在varchar或text类型的列上创建，并且只能在MyISAM表中创建。,5.2 索引,19,5.2.1 索引简介 1.索引的分类 查看数据表中现有索引的语法是： show index from 数据表名； 【例5.4】查看departments表中现有索引情况。 mysqlshow in
11、dex from departments;,5.2 索引,20,5.2.1 索引简介 2.索引的作用 所有的MySQL索引（primary key、unique和index）在B树中存储。字符串是自动地压缩前缀和结尾空间。索引用于以下几种情况： 快速找出匹配一个where子句的行。 在多个表的查询时，执行连接时加快了与其他表中的行匹配的行的搜索。 对特定的索引列找出max()或min()值。 如果排序或分组在一个可用索引的最左面前缀上进行，排序或分组一个表。如果所有键值部分跟随desc，键以倒序被读取。 在一些情况中，一个查询能被优化来检索值，不用访问数据文件。如果对某些表的所有使用的列是数字
12、型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。,5.2 索引,21,5.2.2 索引的创建 索引的创建有三种方法，分别是创建表时直接添加索引，使用create index在现有表中添加索引，使用alter table语句创建索引。,5.2 索引,22,5.2.2 索引的创建 1.创建表时直接创建索引 语法格式：create table 表名 ( 数据列定义列表; index 索引名 (字段列表), key 索引名 (字段列表), unique 索引名 (字段列表), primary key 索引名 (字段列表), ),5.2 索引,23,5.2.2 索引的创建 2.使用c
13、reate index语句在现有表上创建索引 create unique|fulltext|spatial index 索引名 on 表名(字段名长度asc | desc),5.2 索引,24,5.2.2 索引的创建 2.使用create index语句在现有表上创建索引 【例5.5】在empmis数据库的员工表姓名列前2个字符上创建一个升序索引。 create index ind_emp on employees(ename(2) desc);,5.2 索引,25,5.2.2 索引的创建 2.使用create index语句在现有表上创建索引 【例5.6】在empMIS数据库的员工表的员工编
14、号和员工直属领导编号上建立一个复合索引。 create index ind_ejob on employees(eno,emgr);,5.2 索引,26,5.2.2 索引的创建 3.用alter table语句创建索引 为了给现有的表增加一个索引，可使用alter table来创建普通索引、unique索引或 primary key索引。 语法格式： alter table 表名 add index|unique|primary key 索引名称 (数据列列表) 同样，也可以用alter table语句删除列的索引： alter table 表名 drop index 索引名 alter ta
15、ble 表名 drop primary key,5.2 索引,27,5.2.2 索引的创建 3.用alter table语句创建索引 【例5.7】连接stuMIS数据库，为department表departname添加一个唯一索引，以加速表的检索速度。alter table语句创建索引,5.2 索引,28,5.2.3 设计原则和注意事项 1.搜索的索引列，不一定是所要选择的列 2.使用惟一索引 3.使用短索引 4.利用最左前缀 5.不要过度索引 6.考虑在列上进行的比较类型,5.2 索引,29,5.3.1 事务的基本概念 事务是由一系列的SQL语句组成的一个数据库操作序列，而这些操作是一个不可
16、分割的逻辑工作单元。如果事务成功执行，那么该事务中所有的更新操作都会成功执行、并将执行结果提交到数据库中，成为数据库永久的组成部分。若事务中任何一条SQL语句执行操作失败，则事务所有操作均被撤销。 事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，数据库能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。,5.3 事务,30,5.3.1 事务的基本概念 事务通常是以begin transaction开始，以commit或rollback结束。commit表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。 rollback表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有已完成的操作全部撤消，回滚到事务开始的状态。,5.3 事务,31,5.3.1 事务的基本概念 1.事务必须具备4个原则，即所谓的AC
