MySQL数据库对象

一、实验目的

(1)掌握索引和视图的基本概念和功能

(2)掌握利用 MySQL语句创建、维护索引的方法

(3)掌握利用 MySQL语句创建、修改视图的方法

(4)掌握通过视图插入、修改、删除基本表中数据的方法。

二、实验内容

1、创建学生成绩（xscj）数据库，在数据库中创建student、course、score、teacher四张数据表。

（1）具体结构如下  ：

表1 student表

字段名	数据类型	主键	非空	唯一	自增	说明
s_no	bigint(11)	是	是	是	是	学生学号
s_name	varchar(50)	否	是	否	否	学生姓名
s_sex	char(2)	否	否	否	否	性别
s_bir	date	否	否	否	否	出生日期
phone	varchar(13)	否	否	是	否	电话
email	varchar(50)	否	否	是	否	电子邮件

表2 score表

字段名	数据类型	主键	非空	唯一	自增	说明
s_no	bigint(11)	是	是	否	否	学生学号
c_no	char(13)	是	是	否	否	课程编号
daily	float	否	否	否	否	平时成绩
final	float	否	否	否	否	结业成绩

表3 course表

字段名	数据类型	主键	非空	唯一	自增	说明
c_no	char(13)	是	是	否	否	课程编号
c_name	varchar(50)	否	否	否	否	课程名称
t_no	char(10)	是	是	否	否	教师编号
hour	float	否	否	否	否	学分
week	int(2)	否	否	否	否	教学周
semester	int(1)	否	否	否	否	开课学期

表4 teacher表

字段名	数据类型	主键	非空	唯一	自增	说明
t_no	char(10)	是	是	是	否	教师号
t_name	varchar(10)	否	是	否	否	教师姓名
major	char(10)	否	否	否	否	专业
prof	char(6)	否	否	否	否	职称
department	char(10)	否	否	否	否	院系部门

（2）在对应的表中，插入如下数据：

表1  student 表记录

s_no	s_name	s_sex	s_bir	phone	email
18122221320	张凯	男	2001/5/6	13245678547	36451@qq.com
18122221321	赵峰	男	1999/5/4	13945678548	ABTHE@SOHU.COM
18122221322	王伟	男	2003/8/9	13245678549	45SSEEE@163.COM
18122221323	李成	男	2001/9/12	13245678550	78962@qq.com
18122221324	赵晓月	女	2002/12/4	13545678551	3232459@qq.com
18122221325	王俊鑫	男	2003/4/7	13845678552	8989456@qq.com
18137221506	王枫	男	2001/10/5	13645678553	WSWS4875@SOHU.COM
18137221507	李浩	男	2000/11/23	13888128558	46SSEEE@163.COM
18137221508	孙琳	女	2002/5/1	13522078555	124578@qq.com
19112100070	陈晨	男	2003/4/8	13243271256	457822SW@SOHU.COM
19112100071	吴司	男	1999/12/4	13244673357	JEUD@163.COM
19112100072	李珊	女	1998/12/25	13955089958	78451@qq.com
19112100073	钱峰	男	2000/4/1	13945322359	BBBTIH@SOHU.COM
19112100074	张雯	女	2000/12/3	13545612360	SSSMOU@163.COM

表2  score表记录

s_no	c_no	daily	final
18122221320	c08123	85	95
18122221320	a01564	88	93
18122221320	C06108	92	90
18122221321	c08123	85	94
18122221321	C06108	70	82
18122221322	c08123	60	64
18122221322	a01327	53	50
18122221322	a01564	64	50
18122221323	a01564	88	84
18122221323	C05103	85	86
18122221324	c08123	77	81
18122221324	C05103	95	86
18122221325	C05103	88	90

表3   course 表记录

c_no	c_name	t_no	hour	week	semester
c08123	数据库技术	t07019	4	16	4
c08123	数据库技术	t03117	4	16	4
C06108	数据结构	t07019	4	16	3
C06108	数据结构	t01247	4	16	3
C05103	计算机原理	t01247	4	16	2
C05103	计算机原理	t03117	4	16	2
a01327	高等数学	t00458	4	16	2
a01327	高等数学	t00578	4	16	2
a01564	马克思原理	t04410	2	12	1
a01564	马克思原理	t04115	2	12	1

表4  teacher 表记录

t_no	t_name	major	prof	department
t01247	程瑞	软件工程	副教授	计算机学院
t07019	刘泽	软件工程	讲师	软件学院
t04213	汪凌灵	网络技术	副教授	计算机学院
t04115	刘珊	哲学	讲师	人文学院
t00458	李泽峰	数学	助教	基础课程学院
t00578	张伦	数学	讲师	基础课程学院
t02145	王乐	英语	助教	基础课程学院
t04410	王伟	哲学	副教授	人文学院
t03117	孙艳	软件工程	讲师	软件学院

2、视图与索引设计。根据下面的需求，利用选择视图与索引相关理论进行数据库表的视图与索引设计。

1.为student表的phone列上建立一个降序普通索引phone_idx。

源代码：

create index phone_index on student(phone asc);

结果截图：

 

2.在score表的s_no和c_no列上建立一个复合索引stu_cour_idx。

源代码：

mysql> create index stu_cour_idx on score(S_no,C_no);

结果截图：

 

3.在course表的 c_name, t_no列上建立一个唯一性索引 cname_idx。

源代码：

mysql> create unique index cname_idx on course(C_name,T_no);

结果截图：

 

4.在 teacher表上建立t_name和prof的复合索引mark。

源代码：

mysql> create index mark on teacher(T_name,prof);

结果截图：

 

5.删除teacher表的mark索引。

源代码：

mysql> alter table teacher drop index mark;

结果截图：

 

6.利用alter table语句删除course表的cname_idx索引。

源代码：

mysql> alter table course drop index cname_idx;

结果截图：

 

7.在teacher表上创建一个简单的视图v_teacher，显示teacher表的所有信息。

源代码：mysql> create view v_teacher as select*from teacher;

结果截图：

 

8.在student表和score表上创建一个名为stu_score的视图。视图中保留18级的女生的学号、姓名、电话、课程号和结业成绩。

源代码：

CREATE view stu_score

as select student.S_no,S_name,phone,C_name,final

from score join student on student.S_no=score.S_no

join course on course.C_no=score.C_no

where S_sex='女' and left(student.S_no,2)='18';

结果截图：

9.创建视图v_teach，统计软件学院的教师中不是教授或副教授的教师号、教师名和专业。

源代码：create view v_teach

as select T_no,T_name,major

from teacher

where prof not like'%教授' and Department='软件学院';

结果截图：

 

10.查看视图stu_score定义情况。

源代码：

show create view stu_score;

结果截图：

 

11.修改视图v_teach，统计软件学院的教师中的教授或副教授的教师号、教师名和专业，并在视图名后指明视图列名称。

源代码：

alter view v_teach(教师号,教师名,专业)

as select T_no,T_name,major

from teacher

where prof like'%教授' and Department='软件学院';

结果截图：

 

12.删除视图v_teach。

源代码：

drop view if exists v_teach;

结果截图：

 

13.创建视图view_avg，统计各门课程的平均结业成绩，并按课程号升序排列。

源代码：

create view view_avg

as select C_name,avg(final)

from score join course on score.C_no=course.C_no

group by C_name asc;

结果截图：

14.通过视图v_teacher，插入一条纪录（‘t07027’，‘谢天’，‘教育学’,‘副教授’，‘计算机学院’）

源代码：

insert into v_teacher(T_no,T_name,major,prof,Department)

values('t07027','谢天','教育学','副教授','计算机学院');

结果截图：

 

15.通过视图v_teacher，修改‘t07019’的职称为副教授。

源代码：

update v_teacher

set prof='副教授'where T_no='t07019';

结果截图：

 

16.通过视图v_teacher，删除教师号为‘t07027’的纪录。

源代码：delete from v_teacher where T_no='t07027';

结果截图：

17.视图stu_score依赖于表student和表score，通过视图stu_score修改基本表student中的学号为18122221324的电话号码为‘888888’。

源代码：

delete from v_teacher where T_no='t07027';

update stu_score set phone='888888'

where S_no='18122221324';

结果截图：