MySQL-实验8-索引与视图

目录

第1关 建立数据表的同时并建立索引

第2关 在已有的数据表建立索引

第3关 删除索引

第4关 建立基于单表的视图，在视图中插入、删除和修改记录

第5关 根据多个数据表建立视图

第6关 根据视图建立视图

第7关 更新视图

第1关 建立数据表的同时并建立索引

任务描述

本关任务：建立数据表，同时建立索引 显示索引

相关知识

建立数据表的同时建立索引 命令格式：

1. CREATE TABLE table_name
2. ([col_name data_type]
3. [PRIMARY|UNIQUE][|INDEX|KEY]
4. [index_name] (index_col_name [length])
5. [ASC | DESC])

参数	说明
table_name	数据表的名称
primary	主索引
unique	唯一索引
index_name	索引名
index_col_name	索引列的名称

显示索引

1. SHOW
2. {INDEX|INDEXES|KEYS}
3. {FROM|IN} [db_name .]table_name
4. [[FROM|IN] db_name ]
5. [\G ]

任务要求

在sale数据库中，建立供应商数据表（gyh）

字段名	数据类型	说明
gysh	字符型4位	供应商号
gsmc	可变长字符型20位	公司名称
dh	可变长字符型11位	电话
dz	可变长字符型20位	地址
lxr	可变长字符型4位	联系人
sj	字符型11位	手机

同时根据供应商号字段建立主索引。

---

开始你的任务吧，祝你成功！

use sale;
 #代码开始
create table gyh
 (
     gysh char(4) primary key,
     gsmc varchar(20),
     dh varchar(11),
     dz varchar(20),
     lxr varchar(4),
     sj char(11)
 )
 #代码结束

第2关 在已有的数据表建立索引

数据结构

销售数据库有销售单明细(xsdmx)、商品(sp)数据表，表结构如下：

销售单明细(xsdmx)数据表

字符名	数据类型	说明
xsdh	varchar(12)	销售单编号
xh	smallint(6)	序号
sph	varchar(4)	商品号
sl	smallint(6)	数量
dj	decimal(5,1)	单价
je	decimal(10,1)	金额

商品(sp)数据表

字符名	数据类型	说明
sph	varchar(4)	商品号
spm	varchar(20)	商品名
dj	decimal(10,1)	单价
kc	int(11)	库存
pp	varchar(10)	品牌

相关知识

索引的创建和删除

创建索引

创建索引有两种方式，一种是在建表时创建，另一种是建表后创建：

• 普通索引：

1. 创表时创建普通索引：

   1. CREATE table mytable(
   2. id INT NOT NULL,
   3. username VARCHAR(16) NOT NULL,
   4. INDEX [indexName] (username)
   5. );

2. 建表后创建普通索引：

   1. create INDEX 索引名称 on 表名(字段名);
   2. #或者
   3. ALTER TABLE 表名 ADD INDEX 索引名称(字段名);

• 唯一索引：

  1. CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);
  2. #或者
  3. ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段名);

• 主键索引：主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT自增类型的，例如一般表的id字段。

  1. CREATE TABLE mytable (
  2. id int(11) NOT NULL AUTO_INCREMENT,
  3. PRIMARY KEY (id)
  4. );

• 组合索引：组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度）

  1. CREATE INDEX 索引名称 ON 表名(字段1,字段2，字段3);
  2. #或者
  3. ALTER TABLE 表名 ADD INDEX 索引名称(字段1，字段2，字段3);

删除索引

同样，删除索引也有两种方式。

1. #使用drop删除索引
2. drop index index_name on table_name ;
4. #使用alter删除索引
5. alter table table_name drop index index_name ;
6. alter table table_name drop primary key ; #删除主键索引

查询表中索引

查询索引 SQL：

1. show index from 表名;

查询结果部分字段解释：

字段名	说明
Table	创建索引的表
Non_unique	表示索引非唯一，1代表非唯一索引，0代表唯一索引，意思就是该索引是不是唯一索引
Key_name	索引名称
Seq_in_index	表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(只需要知道单列索引该值就为1，组合索引为别的)
Column_name	表示定义索引的列字段
Sub_part	表示索引的长度
Null	表示该字段是否能为空值
Index_type	表示索引类型

任务描述

任务： 第一题 在xsdmx数据表根据销售单编号xsdh和序号xh两个字段建立主索引xsdxh 第二题 在xsdmx数据表根据商品编号sph字段建立普通索引sphsy。 第三题 在商品sp数据表根据商品名spm字段建立唯一索引spmsy。

use sale
#代码开始

#第一题
alter table xsdmx add primary key(xsdh,xh);

#第二题
create index sphsy on xsdmx(sph);

alter table xsdmx add index sphsy(sph);

#第三题
create unique index spmsy on sp(spm);

alter table sp add unique index spmsy(spm);

#代码结束

第3关 删除索引

任务描述

本关任务：删除索引

任务要求

删除sp商品数据表的索引spmsy 删除索引的命令格式

1. DROP INDEX
2. index_name
3. ON table_name

---

开始你的任务吧，祝你成功！

use sale;
#代码开始
drop index spmsy on  sp;

alter table sp drop index spmsy;

#代码结束

第4关 建立基于单表的视图，在视图中插入、删除和修改记录

相关知识

为了完成本关任务，你需要掌握：

1. 视图的定义；

2. 创建视图；

3. 操作视图；

4. 删除视图。

视图的定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。通过视图，可以展现基表（用来创建视图的表）的部分数据；视图数据来自定义视图的查询表。

我们知道了视图的定义，那么，为什么要使用它呢？

因为视图有如下优点：

①. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集；

②. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或列，但是通过视图就可以简单的实现；

③. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列队视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

创建视图

1. CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
2. VIEW view_name [(column_list)]
3. AS select_statement
4. [WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：

• OR REPLACE：表示替换已有视图；

• ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；

• column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；

• select_statement：表示select语句；

• [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

操作视图

视图是逻辑表，也就是说视图不是真实的表，但操作视图和操作普通表的语法是一样的。

用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操作需要在创建视图时满足以下条件（满足以下条件的视图称为可更新视图）：

• from子句中只能引用有1个表（真实表或可更新视图）；

• 不能包含 with、distinct、group by、having、limit等子句；

• 不能使用复合查询，即不能使用union、intersect、except等集合操作；

• select子句的字段列表不能包含聚合、窗口函数、集合返回函数。

删除视图

若视图不再被需要，我们可以将其删除，且视图的删除并不影响源表中的数据。

删除视图的 SQL 如下：

1. DROP VIEW view_name;

数据表

销售数据库有工作人员(gzry)数据表，表结构如下：

字符名	数据类型	说明	默认值
gyh	int(3)	雇员号	主键
gyxm	varchar(10)	雇员姓名
csrq	date	出生日期
xl	enum("初中","高中","本科","研究生")	学历	本科
gz	decimal(5,0)	工资
bm	enum("销售部","办公室","仓库","采购部")	部门	销售部
dh	varchar(12)	电话

任务描述

本关任务： 第一题 建立视图ckyg，查询gzry数据表中部门bm为仓库的员工的所有字段的信息 第二题 在视图ckyg中，插入gyh雇员号为019，姓名gyxm为李盛，部门bm为仓库的数据。 第三题 在视图ckyg中，删除姓名为赵国庆的数据 第四题 在视图ckyg中，将王文武的电话改为13319660678

提示： 在数据来源于多表的视图中，不能插入和删除数据

开始你的任务吧，祝你成功！

use sale;
#代码开始
#题目1
create view ckyg 
as
select * from gzry where bm='仓库';

#题目2
insert into ckyg(gyh,gyxm,bm)values(019,'李盛','仓库');

#题目3
delete from ckyg where gyxm='赵国庆';

#题目4
update ckyg set dh='13319660678'where gyxm='王文武';
 
#代码结束

第5关 根据多个数据表建立视图

任务描述

本关任务： 根据工作人员gzry、销售单xsd、顾客gk数据表建立视图 为了完成本关任务，你需要掌握：

如何建立视图

相关知识

建立视图

视图是从数据库中一个或多个表中导出来的虚拟表。 包含一系列有名称的数据列和若干数据行。 建立视图的命令格式：

1. CREATE [OR REPLACE]
2. VIEW view_name [(column_list)]
3. AS SELECT_statement

view_name是视图的名称

表结构

工作人员(gzry)数据表

字符名	数据类型	说明	备注
gyh	int(3)	雇员号	主键
gyxm	varchar(10)	雇员姓名
csrq	date	出生日期
xl	enum("初中","高中","本科","研究生")	学历	默认：本科
gz	decimal(5,0)	工资
bm	enum("销售部","办公室","仓库","采购部")	部门	默认：销售部
dh	varchar(12)	电话

销售单(xsd)数据表

字符名	数据类型	说明	备注
xsdh	varchar(12)	销售单号	PK
hyh	varchar(4)	会员号
gyh	varchar(3)	雇员号
xsrq	date		销售日期
yfk	decimal(10,1)	预付款
sjfk	decimal(10,1)	实际付款

顾客(gk)数据表

字符名	数据类型	说明	备注
hyh	varchar(4)	会员号	PK
name	varchar(10)	会员姓名
sex	varchar(1)	性别	默认：男
tel	varchar(11)	电话
dept	varchar(20)	门店

任务要求

打开sale数据库 建立xsdxx视图，包含销售单号xsdh、雇员号gyh、雇员姓名gyxm、会员号hyh、会员姓名name、销售日期xsrq、实际付款sjfk字段。

---

开始你的任务吧，祝你成功！

use sale;
 #代码开始
 create  view xsdxx
 as
 select xsdh,gzry.gyh,gyxm,gk.hyh,name,xsrq,sjfk 
 from xsd inner join gzry using(gyh)
        inner join gk using(hyh)
 
 #代码结束

第6关 根据视图建立视图

任务描述

任务： 根据xsdxx视图建立视图

第一题： 根据xsdxx视图建立视图xsdhytj，统计每个会员的合计消费记录，显示会员号hyh，姓名name和实际付款sjfk的合计金额(命名为hjje) 按合计金额的降序排列

第二题： 根据xsdxx视图建立视图xsdgytj，统计每个雇员的合计销售记录，显示雇员号gyh，姓名xm和实际付款sjfk的合计金额(命名为hjje) 按合计金额的降序排列

use sale
#代码开始
#第一题
create view xsdhytj
as
select hyh,name,sum(sjfk) as hjje from xsdxx group by hyh order by hjje desc;

#第二题
create view xsdgytj
as
select gyh,gyxm,sum(sjfk) as hjje from xsdxx group by gyh order by hjje desc;

#代码结束

第7关 更新视图

任务描述

本关任务：在视图中修改数据

相关知识

在视图中，可以通过update命令更新数据 但是，如果是计算字段则无法更新。

任务要求

在视图xsdxx中，将工作人员（gyxm）王强的销售日期（xsrq）2015-6-3的会员名(name)刘海东的订单的实际付款(sjfk)设置为800 观察视图xsdgytj和xsdhytj的变化

---

开始你的任务吧，祝你成功！

use sale;
#代码开始
update xsdxx set sjfk=800 
where  gyxm='王强' and xsrq='2015-6-3' and name='刘海东';

#代码结束