MySQL数据库应用与开发技术 课件 第05章-视图与索引

MySQL数据库应用与开发技术第5章视图与索引本单元教学目标了解数据库的数据库视图的概念认识数据表索引理解索引的实现原理掌握数据视图创建语法掌握索引的创建语法能够熟练为数据表添加索引提升检索速度3数据视图视图以物理数据为基础，对已经存在的数据通过一定的运算规则获得新的数据集合视图构建在数据表的基础上通过对关系表数据检索来形成新的数据集合视图中的数据视图不同于数据库的物理表数据库环境中没有专门的视图数据存储单元关系表数据改变时会反映在对应的视图上视图的权限视图提供查询、检索操作，即支持读操作有特殊权限的视图可以进行写操作4视图构建可以构建在一个或多个关系数据表上为视图提供数据的关系表叫基表可以构建在一个或多个视图上可以构建在视图与数据表混合体上5视图构建语法视图创建语法：CREATEVIEW视图名(列名1,列名2,列名3,…)AS子查询CREATEVIEWfirst_view(aa,bb,cc)ASSELECTa,b,cFROMabc创建语法分析：CREATEVIEW为创建视图的关键字CREATEVIE后面为视图的名称视图名称后面为视图的列，需用小括号括起来列名后面跟关键字ASAS关键字后面为数据的检索子查询，即视图的数据来源6视图检索-更新-删除语法视图数据检索语法SELECT列名1,列名2,列名3FROM视图名SELECTaa,bb,ccFROMfirst_view可以用星号“*”表示检索所有数据列SELECT*FROMfirst_view视图删除语法：DROPVIEW视图名DROPVIEWfirst_view视图修改语法：ALTERVIEW视图名(列名1,列名2,列名3,…)AS子查询ALTERVIEWfirst_view(ee,ff,gg)ASSELECTe,f,gFROMabc7案例应用（视图构建）在my_view库中存在如下数据表Sale、Shop、User表结构如图所示创建如图中所示的相关视图US_View视图Shop_View视图SS_View视图检索出相关视图的数据8关系表与视图组成结构关系

9数据库环境SQL脚本CREATEDATABASEIFNOTEXISTSmy_view;USEmy_view;DROPTABLEIFEXISTSsale;CREATETABLEsale(

sidvarchar(10)NOTNULL,

uidvarchar(10)DEFAULTNULL,cidvarchar(10)DEFAULTNULL,

stime

datetimeDEFAULTNULL,

smoneyfloatDEFAULTNULL,spaychar(1)DEFAULTNULL,PRIMARYKEY(sid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOsale(sid,uid,cid,stime,smoney,spay)VALUES('S201','U1001','C01','2019-03-0612:24:30',318,'Y'),('S202','U1002','C02','2019-04-0815:28:30',215,'Y'),('S203','U1003','C03','2019-07-1216:30:20',156,'Y');DROPTABLEIFEXISTSshop;CREATETABLEshop(cidvarchar(10)NOTNULL,

cnamevarchar(10)DEFAULTNULL,

ctypevarchar(20)DEFAULTNULL,

csuppliervarchar(45)DEFAULTNULL,amountsmallint(6)DEFAULTNULL,

salervarchar(45)DEFAULTNULL,managervarchar(45)DEFAULTNULL,addressvarchar(45)DEFAULTNULL,PRIMARYKEY(cid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOshop(cid,cname,ctype,csupplier,amount,saler,manager,address)VALUES('C01','大米','食品','食之源',100,'张新靓','孙福平','永和路45号'),('C02','钢笔','文具','大学堂',80,'林佳佳','孙福平','永和路45号'),('C03','衬衣','服装','金利来',75,'刘惠方','孙福平','永和路45号');DROPTABLEIFEXISTSuser;CREATETABLEuser(

uidvarchar(10)NOTNULL,

unamevarchar(45)DEFAULTNULL,

uagesmallint(6)DEFAULTNULL,

usexchar(1)DEFAULTNULL,

uaddressvarchar(50)DEFAULTNULL,PRIMARYKEY(uid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOuser(uid,uname,uage,usex,uaddress)VALUES('U1001','李小青',25,'女','建国路33号'),('U1002','孙民辉',30,'男','建设大道15号'),('U1003','林青芳',28,'女','前进路08号');案例应用-视图构建（1）创建US_View视图创建语句：CREATEVIEWUS_View(sid,uid,uaddress,smoney,spay)ASSELECTs.sid,u.uid,u.uaddress,s.smoney,s.spayFROMUseru,SalesWHEREs.uid=u.uid;案例应用-视图构建（2）创建Shop_View视图创建语句：CREATEVIEWShop_View(cid,cname,ctype,saler,amount)ASSELECTcid,cname,ctype,saler,amountFROMShop;案例应用-视图构建（3）创建SS_View视图创建语句：CREATEVIEWSS_View(sid,cid,cname,stime,saler)ASSELECTs.sid,sv.cid,ame,s.stime,sv.salerFROMSales,Shop_View

svWHEREs.cid=sv.cid;13数据索引定义：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。14索引优点第一，可以大大加快数据的检索速度这也是创建索引的最主要的原因。第二，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第三，在使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间。第四，在查询的过程中，使用优化隐藏器提高系统的性能。15索引不足第一，创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加第二，索引需要占物理空间每一个索引还要占一定的物理空间第三，对表中的数据进行写操作时数据表上会重建相关索引降低了数据写操作的速度16索引设计原则经常与其他表进行连接的表，在连接字段上应该建立索引经常出现在Where子句中的字段，特别是大表的字段，应该建立索引索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引频繁进行写操作的表，不要建立太多的索引删除无用的索引，避免对造成负面影响17创建单列索引语法格式：ALTERTABLE+表名

+ADDINDEX+

索引名+(索引列)为myorder表的goods_id字段添加名为goods_id_index的单列索引ALTERTABLEmyorderADDINDEXgoods_id_index(goods_id)

18创建混合索引语法格式：ALTERTABLE+表名+ADDINDEX+

索引名

+(列1,列2,列3)为myorder表的price、order_time字段添加名为ordertime_price_index的混合索引

ALTERTABLEmyorderADDINDEXordertime_price_index(price,order_time)

19查询索引语法格式：SHOWINDEXFROM+

表名查询为上面myorder表创建的索引信息SHOWINDEXFROMmyorder20删除索引语法格式(二选一)DROPINDEX+

索引名+ON+

表名ALTERTABLE+表名

+DROPINDEX+索引名删除myorder表上的ordertime_price_index索引DROPINDEXordertime_price_indexONmyorderALTERTA