实验5：索引和视图操作（2学时）

一、实验目的
1、理解索引的概念与类型；
2、掌握创建、更改、删除索引的方法；
3、掌握维护索引的方法；
4、理解视图的概念；
5、掌握创建、更改、删除视图的方法；
6、掌握使用视图来访问数据的方法；

二、实验环境
装有软件MySQL5.7或更高版本的PC电脑一台；

三、实验步骤及实验要求
按照下面的实验内容要求完成作业，将作业结果的每一步截图粘贴到word文档中即可。每一个实验都必须编写实验报告，要求如下：
1、实验报告用word编写；
2、word文件命名的格式统一要求：为以“杨健”同学19号为例，他所做的第4次实验的实验报告应该命令为：“DB实验4— 19号—杨健”（即格式是：实验序号—课内序号—姓名）；课内序号现在是一个人一门课一个号，同一个人在不同课序号会不同，回头我会将课程名单发到群里，自己查阅你自己在本门课中的序号。
3、实验报告用统一的封面，封面模板到时发给大家；
4、报告中截取每题主要步骤结果的截图、实验结果截图；
5、实验报告最后要加上实验总结，总结部分必须写出自己的切身体会；
6、实验报告如有雷同、抄袭现象，后果自负；
7、实验报告上交截止时间：上机后一周之内；
8、实验上交方式：由学委收集齐全后，统一交付老师：

四、实验内容
1、JobDB数据库中有登录用户信息【userlogin】表和个人信息【information】表，具体如表结构所示。
USERLOGIN 表的结构

information表的结构

请在上述2表上完成如下操作：

create table userlogin
(
   id         INT(4),
   `name`     VARCHAR(20),
   `Password` VARCHAR(20),
   info       TEXT,
   primary key (id) 
);
create table information
(
   id           INT(4),
   `name`       VARCHAR(20),
   sex          VARCHAR(4),
   birthday     DATE,
     address      VARCHAR(50),
     tel          VARCHAR(20),
     pic          BLOB,
   primary key (id)
);

1）在name字段创建名为index_name的索引；

CREATE INDEX index_name
ON userlogin(name);
CREATE INDEX index_name
ON information(name);

2）在birthday和address两个字段上创建名为index_bir的多列索引；

CREATE INDEX index_bir
ON information(birthday,address);

3）用 ALTER TABLE 语句创建名为index_id的惟一性索引；

ALTER TABLE userlogin 
ADD UNIQUE index_id(id);
ALTER TABLE information 
ADD UNIQUE index_id(id);

4）删除 userlogin 表上的index_ userlogin 索引；

SHOW INDEX FROM userlogin;
CREATE INDEX index_userlogin
ON userlogin(name);
DROP INDEX index_userlogin ON userlogin;

5）查看 userlogin 表的结构；

DESCRIBE userlogin;
DESC userlogin;

6）删除information 表上的index_name索引；

SHOW INDEX FROM information;
DROP INDEX index_name ON information;

7）查看information表的结构；

DESCRIBE information;
DESC information;

2、在JobDB数据库中，有聘任人员信息表【Work_lnfo】表，其表结构如下表所示：

create table Work_lnfo
(
   id           INT(4) unique key,
   `name`       VARCHAR(20) not null,
   sex          VARCHAR(4) not null,
   age          INT(4),
     address      VARCHAR(50),
     tel          VARCHAR(20),
   primary key (id)
);
-- 1.'张明','男',19,'北京市朝阳区','1234567'
-- 2.'李广','男',21,'北京市昌平区','2345678'
-- 3.'王丹','女',18,'湖南省永州市','3456789'
-- 4.'赵一枚','女',24,'浙江宁波市','4567890'
INSERT INTO Work_lnfo VALUE(1,'张明','男',19,'北京市朝阳区','1234567');
INSERT INTO Work_lnfo VALUE(2,'李广','男',21,'北京市昌平区','2345678');
INSERT INTO Work_lnfo VALUE(3,'王丹','女',18,'湖南省永州市','3456789');
INSERT INTO Work_lnfo VALUE(4,'赵一枚','女',24,'浙江宁波市','4567890');
SELECT * FROM Work_lnfo;

其中表中练习数据如下：
1.’张明’,’男’,19,’北京市朝阳区’,’1234567’
2.’李广’,’男’,21,’北京市昌平区’,’2345678’
3.’王丹’,’女’,18,’湖南省永州市’,’3456789’
4.’赵一枚’,’女’,24,’浙江宁波市’,’4567890’
按照下列要求进行操作：
1）创建视图info_view，显示年龄大于20岁的聘任人员id,name,sex,address信息。

CREATE VIEW info_view
AS 
SELECT  id,`name`,sex,address
FROM    Work_lnfo
WHERE   age > 20;
SELECT * FROM info_view;

2）查看视图info_view的基本结构和详细结构：（1）查看基本结构；（2）查看详细结构；

DESCRIBE info_view;
DESC info_view;
SHOW CREATE VIEW info_view;
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE 'info_view'

3）查看视图info_view的所有记录；

SELECT * FROM info_view;

4）修改视图info_view，满足年龄小于20岁的聘任人员id,name,sex,address信息；

SELECT * FROM info_view;
CREATE OR REPLACE VIEW info_view
AS
SELECT  id,`name`,sex,address
FROM    Work_lnfo
WHERE   age < 20;

5）更新视图，将id号为3的聘任员的性别，由“男“改为“女”；

UPDATE info_view SET sex = '男' WHERE id = 3;
SELECT * FROM info_view;

6）删除info_view视图；

DROP VIEW info_view;