数据库原理及应用mysql版陈业斌实验四

🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客
🌅主页：猫咪-9527-CSDN博客 

“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”

目录

实验四索引与视图 

1.实验数据如下

student 表（学生表）

course 表（课程表）

teacher 表（教师表）

score 表（成绩表）

2. 插入数据

student 表中的数据

course 表中的数据

teacher 表中的数据

score 表中的数据

4-1 创建唯一索引 idx_snme 并按姓名升序排列

1.修改黎明为张伟（4.2删除索引后记得改回来）

2.不建立唯一索引，建立普通索引

3.删除一条姓名为黎明的数据（4.2记得添加回来）

4-2 删除索引 idx_snme

1.接4-1方法1 

 2.接4-1方法2

3.接4-2方法三 

4-3 创建联合索引 idx_sname_dept 在 sname 和 dept 字段上

4-4 在 course 表的 cname 字段上创建唯一索引 uk_cname

4-5 在 course 表的 cname 字段上创建前缀长度为4个字符的前缀索引pf_cname

4-6 在 course 表的 cname 字段上创建全文索引

4-7 创建计算机系学生的视图 student_cs

4-8 创建包含“学号”和“平均成绩”的视图 v_grade_avg

4-9 查询视图 v_grade_avg 中，平均成绩大于 90 分的学生信息

4-10 创建信息系学生的视图 v_information，并保证修改和插入操作时只有信息系的学生

4-11 修改视图 vinformation，使得修改和插入操作时只包含“学号”“姓名”“专业”字段

4-12 使用 INSERT 语句向视图 vinformation 中插入一条数据

实验四索引与视图 

实验目的：

通过实验进一步理解索引、视图的使用方法。

1.实验数据如下

student 表（学生表）

CREATE TABLE student (sno CHAR(5) PRIMARY KEY,snme VARCHAR(20) NOT NULL,        sdept VARCHAR(20) NOT NULL,       sclass CHAR(2) NOT NULL,          ssex CHAR(1),                     birthday DATE,                  totalcredit DECIMAL(4,1)          
);

course 表（课程表）

CREATE TABLE course (cno CHAR(3) PRIMARY KEY,cname VARCHAR(50),       ctime DECIMAL(3,0),          credit DECIMAL(3,1) 
);

teacher 表（教师表）

CREATE TABLE teacher (tno CHAR(6) PRIMARY KEY,          tname VARCHAR(20),               tsex CHAR(1),                    tdept VARCHAR(20)                 
);

score 表（成绩表）

CREATE TABLE score (sno CHAR(5),                      cno CHAR(3),                      tno CHAR(6),                     grade DECIMAL(5,1),              PRIMARY KEY (sno, cno, tno),      CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno),CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno)
);

2. 插入数据

student 表中的数据

INSERT INTO student VALUES('96001', '马小燕', '计算机', '01', '女', '2000/01/02', 0);
INSERT INTO student VALUES('96002', '黎明', '计算机', '01', '男', '2000/03/05', 0);
INSERT INTO student VALUES('96003', '刘东明', '数学', '01', '男', '2000/10/05', 0);
INSERT INTO student VALUES('96004', '赵志勇', '信息', '02', '男', '2000/08/08', 0);
INSERT INTO student VALUES('97001', '马蓉', '数学', '02', '女', '2001/03/04', 0);
INSERT INTO student VALUES('97002', '李成功', '计算机', '01', '男', '2001/09/10', 0);
INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0);
INSERT INTO student VALUES('97004', '李丽', '计算机', '02', '女', '2002/01/05', 0);
INSERT INTO student VALUES('96005', '司马志明', '计算机', '02', '男', '2001/11/23', 0);

course 表中的数据

INSERT INTO course VALUES('001', '数学分析', 64, 4);
INSERT INTO course VALUES('002', '普通物理', 64, 4);
INSERT INTO course VALUES('003', '微机原理', 56, 3.5);
INSERT INTO course VALUES('004', '数据结构', 64, 4);
INSERT INTO course VALUES('005', '操作系统', 56, 3.5);
INSERT INTO course VALUES('006', '数据库原理', 56, 3.5);
INSERT INTO course VALUES('007', '编译原理', 48, 3);
INSERT INTO course VALUES('008', '程序设计', 32, 2);

teacher 表中的数据

INSERT INTO teacher VALUES('052501', '王成刚', '男', '计算机');
INSERT INTO teacher VALUES('052502', '李正科', '男', '计算机');
INSERT INTO teacher VALUES('052503', '严敏', '女', '数学');
INSERT INTO teacher VALUES('052504', '赵高', '男', '数学');
INSERT INTO teacher VALUES('052505', '刘玉兰', '女', '计算机');
INSERT INTO teacher VALUES('052506', '王成刚', '男', '信息');
INSERT INTO teacher VALUES('052507', '马悦', '女', '计算机');

score 表中的数据

INSERT INTO score VALUES('96001', '001', '052503', 77.5);
INSERT INTO score VALUES('96001', '003', '052501', 89);
INSERT INTO score VALUES('96001', '004', '052502', 86);
INSERT INTO score VALUES('96001', '005', '052505', 82);
INSERT INTO score VALUES('96002', '001', '052504', 88);
INSERT INTO score VALUES('96002', '003', '052502', 92.5);
INSERT INTO score VALUES('96002', '006', '052507', 90);
INSERT INTO score VALUES('96005', '004', '052502', 92);
INSERT INTO score VALUES('96005', '005', '052505', 90);
INSERT INTO score VALUES('96005', '006', '052505', 89);
INSERT INTO score VALUES('96005', '007', '052507', 78);
INSERT INTO score VALUES('96003', '001', '052504', 69);
INSERT INTO score VALUES('97001', '001', '052504', 96);
INSERT INTO score VALUES('97001', '008', '052505', 95);
INSERT INTO score VALUES('96004', '001', '052503', 87);
INSERT INTO score VALUES('96003', '003', '052501', 91);
INSERT INTO score VALUES('97002', '003', '052502', 91);
INSERT INTO score VALUES('97002', '004', '052505', NULL);
INSERT INTO score VALUES('97002', '006', '052507', 92);
INSERT INTO score VALUES('97004', '005', '052502', 90);
INSERT INTO score VALUES('97004', '006', '052501', 85);

---

 注：把上面的实验数据添加上再开始实验。

4-1 创建唯一索引 idx_snme 并按姓名升序排列

数据有冲突，建议一下三种方法：（三种方法任选一种） 

1.修改黎明为张伟（4.2删除索引后记得改回来）

update student set snme='张伟'  where snme='黎明' and sdept='信息';

create unique index idx_snme on student(snme);

 

2.不建立唯一索引，建立普通索引

create index idx_snme on student(snme);

3.删除一条姓名为黎明的数据（4.2记得添加回来）

delete from student where snme='黎明' and sdept='信息';

create unique index idx_snme on student(snme);

4-2 删除索引 idx_snme

1.接4-1方法1 

drop index idx_snme on student;

update student set snme='黎明'  where snme='张伟' and sdept='信息';

 2.接4-1方法2

drop index idx_snme on student;

3.接4-2方法三 

drop index idx_snme on student;

INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0);

4-3 创建联合索引 idx_sname_dept 在 sname 和 dept 字段上

create index idx_snme_dept on student(snme,sdept);

4-4 在 course 表的 cname 字段上创建唯一索引 uk_cname

create unique index uk_cname on course(cname);

4-5 在 course 表的 cname 字段上创建前缀长度为4个字符的前缀索引pf_cname

create index pf_cname on course(cname(4));

4-6 在 course 表的 cname 字段上创建全文索引

create fulltext index ft_cname on course(cname);

4-7 创建计算机系学生的视图 student_cs

create view student_cs as select *from student where sdept='计算机';

4-8 创建包含“学号”和“平均成绩”的视图 v_grade_avg

create view v_garde_avg as select sno 学号,avg(grade)平均成绩 from score group by sno;

4-9 查询视图 v_grade_avg 中，平均成绩大于 90 分的学生信息

 select *from v_garde_avg where 平均成绩>90;

4-10 创建信息系学生的视图 v_information，并保证修改和插入操作时只有信息系的学生

create view v_information as select *from student where sdept='信息' 
with check option;

4-11 修改视图 vinformation，使得修改和插入操作时只包含“学号”“姓名”“专业”字段

当视图被创建后限制信息无法修改，只能删除视图后重新创建 

方法一： 

drop view v_information;

create view v_information as 
select sno 学号,snme 姓名, sdept 专业 from student where sdept='信息' 
with check option;

方法二：

create or replace view v_information as select sno,sname,sdept 
from student where sdept='信息' with check option;

4-12 使用 INSERT 语句向视图 vinformation 中插入一条数据

insert into student(sno,snme,sdept,sclass) values('98001','王立红','信息',02);