实验4：索引、视图创建与管理操作实验

一、实验目的：

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

二、验证性实验

在job数据库中有登录用户信息：userlogin表和个人信息：information 表。具体如表结构所示。

create database job;
use job;
create table userlogin(
	id int(4) not null unique primary key auto_increment,
	name varchar(20) not null,
	password varchar(20) not null,
	info text
);
create table information(
	id int(4) not null primary key unique auto_increment,
	name varchar(20) not null,
	sex varchar(4) not null,
	birthday date,
	address varchar(50),
	tel varchar(20),
	pic blob
);
desc userlogin;
desc information;

请在上述2表上完成如下操作：
1、在name字段创建名为index_name的索引

create index index_name on information (name(10));

2、创建名为index_bir的多列索引

 create index index_bir on information (birthday,address);

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

alter table information add index index_id(id ASC);

4、删除 userlogin 表上的index_ userlogin 索引

 create index index_userlogin on userlogin(id(4));
drop index index_userlogin on userlogin;

5、查看 userlogin 表的结构的代码如下：

 show create table userlogin \G

6、删除information 表上的index_name索引

 drop index index_name on information;

7、查看information 表的结构的语句如下：

 show create table information \G

在job数据库中，有聘任人员信息表：Work_lnfo表，其表结构如下表所示：

其中表中练习数据如下：
1,‘张明’,‘男’,19,‘北京市朝阳区’,‘1234567’
2,‘李广’,‘男’,21,‘北京市昌平区’,‘2345678’
3,‘王丹’,‘女’,18,‘湖南省永州市’,‘3456789’
4,‘赵一枚’,‘女’,24,‘浙江宁波市’,‘4567890’

create table work_info(
	id int(4) primary key not null unique,
	name varchar(20) not null,
	sex varchar(4) not null,
	age int(4),
	address varchar(50),
	tel varchar(20)
);
desc work_info;

insert into work_info(id,name,sex,age,address,tel)
values
(1,'张明','男',19,'北京市朝阳区','1234567'),
(2,'李广','男',21,'北京市昌平区','2345678'),
(3,'王丹','女',18,'湖南省永州市','3456789'),
(4,'赵一枚','女',24,'浙江省宁波市','4567890');

//机房：
insert into work_info(id,name,sex,age,address,tel)
values
(1,'ZM','MAN',19,'BJSCYQ','1234567'),
(2,'LG','MAN',21,'BJSCPQ','2345678'),
(3,'WD','NMAN',18,'HNSYZS','3456789'),
(4,'ZYM','NMAN',24,'ZJSNBS','4567890');
select * from work_info;

按照下列要求进行操作：
1．创建视图info_view，显示年龄大于20岁的聘任人员id,name,sex,address信息。

create algorithm=merge view info_view(id,name,sex,address) as 
select id,name,sex,address from work_info where age>20 with local check option;

2．查看视图info_view的基本结构和详细结构
查看基本结构的代码如下：

desc info_view;

查看详细结构的代码如下：

show create view info_view \G

3．查看视图info_view的所有记录

 select * from info_view;

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

alter algorithm=merge view info_view(id,name,sex,address) as 
select id,name,sex,address from work_info where age<20 with local check option;

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

update info_view set sex='女' where id=3;
//机房
update info_view set sex='NOMA' where id=3;

6．删除info_view视图

drop view info_view;

三、设计性试验

在数据库job下创建worklnfo表。创建表的同时在id字段上创建名为index_id的唯一性索引，而且以降序的格式排列。Worklnfo表内容如表所示。

create table workinfo(
	id int(4) not null unique primary key auto_increment,
	name varchar(20) not null,
	type varchar(10),
	address varchar(50),
	wages year,
	contents tinytext,
	extra text,
	unique index index_id(id desc)
);

请完成如下操作：
1、使用 CREATE INDEX 语句为name字段创建长度为10的索引index_name。

create index index_name on workinfo(name(10));

2、使用 ALTER TABLE 语句在type和address上创建名为index_t的索引。

alter table workinfo add index index_t(type,address);

3、使用 ALTER TABLE 语句将workInfo表的存储引擎更改为MyISAM类型。

alter table workinfo ENGINE=MYISAM;

4、使用 ALTER TABLE 语句在extra字段上创建名为index_ext的全文索引。

alter table workinfo add fulltext index index_ext(extra);

5、使用 DROP 语句删除workInfo表的惟一性索引index_id。

drop index index_id on workinfo;

在学生管理系统中，有学生信息表studentinfo表，其表结构如下：

create table studentinfo(
	number int(4) not null primary key unique,
	name varchar(20) not null,
	major varchar(20),
	age int(4)
);

请完成如下操作：

1. 使用CREATEVIEW语句来创建视图college_view，显示studentinfo表中的number,name,age,major，并将字段名显示为：

student_num,student_name,student_age,department。
create algorithm=undefined view
college_view(student_num,student_name,student_age,department) as
select number,name,age,major from studentinfo
with local check option;

2. 执行SHOW CREATE VIEW语句来查看视图的详细结构。

show create view college_view \G

3. 更新视图。向视图中插入如下3条记录：
   0901,‘张三’,20,‘外语’
   0902,‘李四’,22,‘计算机’
   0903,‘王五’,19,‘计算机’

insert into college_view(student_num,student_name,student_age,department)
values
(0901,'张三',20,'外语'),
(0902,'李四',22,'计算机'),
(0903,'王五',19,'计算机');

4. 修改视图，使视图中只显示专业为“计算机”的信息。

create or replace algorithm=undefined view
college_view(student_num,student_name,student_age,department) as
select number,name,age,major
from studentinfo where major='计算机'
with local check option;

5. 删除视图。

drop view college_view;

四、观察与思考

1. 数据库中索引被破坏后会产生什么结果？
   答：不能使用delete操作。
2. 视图上能创建索引吗？
   答：不能，视图是select语句的结果集，是放在内存中的数据索引是创建在存储空间的对象上，占用存储空间。
3. MySQL中组合索引创建的原则是什么？
   答：1.创建查询频率高的字段索引；
   2.索引的数目不宜太多；
   3.选择唯一性索引；
   4.对排序、分组、联合查询频率高的字段创建索引；
   5.尽量使用前缀来索引；
   6.尽量使用数据量少的索引。
4. 主键约束和唯一约束是否会默认创建唯一索引？
   答：会，主键约束的索引键在定义上不允许为NULL，而唯一约束的索引键在定义上允许为NULL。
5. 通过视图中插入的数据能进入到基本表中去吗？
   答：不能，视图被看成是虚拟表,它并不代表任何的物理数据,只是用来查看数据的视窗数据库中只储存视图的定义，并不是以一组数据的形式储存在数据库中。
6. with check option能起什么作用？
   答：强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。
7. 修改基本表的数据会自动反映到相应的视图中去吗？
   答：视图是一个虚拟表，其内容由查询定义。视图中的数据是由一张或多张表中的数据组成的。如果你改动了基本表，你的视图来源于这个基本表，那视图给你呈现的结果也会随之发生变化。
8. 哪些视图中的数据不可以增删改操作
   答：视图定义信息、隐藏视图定义