一 索引和视图
(一)创建索引
1.在Workbench中,为学生表按姓名建一个‘Index_姓名’的非聚集索引;为课程表按课程名建一个‘Index_课程名’的唯一索引;为成绩表按成绩建一个‘Index_成绩’的非聚集索引;
create index INDEX_姓名
on student (sn);
create unique index INDEX_课程名
on course(cn);
create index INDEX_成绩
on sc(score);
2.使用SQL语句,为学生表创建一个聚集索引,索引名为‘Index_学号
create index Index_学号
on student (sno);
3.使用SQL语句,为学生表创建一个唯一索引,索引名为‘Index_姓名’
create unique index Index_姓名
on student(sn);
4.使用SQL语句,为学生表的学号建立一个唯一索引
create unique index Index_学号
on student(sno);
5.使用SQL,为学生表中的学号、姓名创建一个复合索引
create index Index_cn_cno
on student(sno,sn);
(二)创建视图
1.创建信息学院学生视图s_view;
create view s_view
as select *
from s
where dept='信息学院';
2. 创建学生选课情况视图s_sc_c_view,要求显示学号、姓名、课程名及成绩。
create view s_sc_c_view(sno,sn,cn,score)
as select student.sno,sn,cn,score
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno;
3.创建一个信息学院选修了‘c1’课程的学生视图,视图名选课学生,要求显示学号、姓名、性别,课程号、成绩;
create view 选课学生
as select student.sno,sn,sex,sc.cno,score
from student,sc,course
where student.dept='信息学院' and sc.cno='c1' and student.sno=sc.sno and sc.cno=course.cno;
4.创建一个信息学院选修了‘c1’课程的且成绩在80分以上的学生视图,视图名为选课学生1,要求在视图名为选课学生上建立;
create view 选课学生1
as select * from 选课学生
where score>80;
5. 创建一个学生出生年份的视图;
create view 出生年份
as select sn,2022-age
from student;
6.将学号、平均成绩定义一个视图,视图名为s_avg;
create view s_avg_1
as select sno,avg(score) as 平均分
from sc
group by sno;
(三)查询、更新视图
1.在s_view视图上列出年龄大于20的学生;
select *
from s_view
where age>20;
2.列出平均成绩大于80分的学生;
select *
from s_avg_1
where 平均分>80;
3.查询信息学院选修了‘c1’课程的学生清单
select *
from 选课学生
4.查询学生的出生年份;
select * from 出生年份
5.查询‘c1’成绩在80分以上信息学院的学生;
select sn,sno from 选课学生
where score>80 ;
6.插入记录(s10,韩义,男,19,计算机,信息学院)到s_view视图;
insert into s_view
values ('s10','韩义','男','19','计算机','信息学院');
7. 插入记录(s11,张三,男,19,计算机,工学院)到信息学院视图s_view中。
insert into s_view
values ('s11','张三','男','19','计算机','工学院');
8.使用s_view视图,将学号为是s10的学生年龄改为20岁;
update s_view
set age=20
where sno='s10';
9.删除s_view学生视图中的学号为‘s10’的记录;
delete from s_view
where sno='s10';
二 函数
1.从SC表查询所有选修课程的成绩情况,凡成绩为空的输出“未考”,小于60分的输出“不及格”,60~70分的输出“及格”,70~90分的输出“良好”,大于或等于90分的输出“优秀”。
select sno,cno,
case
when score is null then '未考'
when score <60 then '不及格'
when score >=60 and score<70 then '及格'
when score >=70 and score<90 then '良好'
when score >=90 then '优秀'
end
as score
from sc;
2.通过MySQL编程,完成用户自定义函数创建、调用、查看和删除等管理工作。
(1)创建一个函数cal_depts,函数的功能是,从学生表student中查询学院的数量。
delimiter $$
create function `cal_depts` ()
returns integer
reads sql data
comment "统计学院数量"
begin
declare N integer;
select count(distinct dept) into n from student;
return n;
end
(2)调用函数cal_depts,显示学院的数量;
select cal_depts();
(3)分别写出查看函数cal_depts的代码定义和状态特征的语句;
show create function cal_depts;
show function status like 'cal_depts';
(4)写出删除函数cal_depts的语句。
drop function cal_depts;
3.通过MySQL编程,完成用户自定义函数的创建、调用。
(1)创建一个函数count_number,任意向函数传递一个学号值,如果存在该同学,则能计算并返回该同学的选课门数,否则,返回0;
delimiter $$
create function count_number_1(_sno char(10))
returns int
begin
declare num int;
declare _count int;
select count(*) into _count from sc where sno=_sno;
if _count=0 then
set num=0;
else
set num=_count;
end if;
return num;
end$$
delimiter ;
(2)编写代码,调用函数count_number,显示某位同学的学号、姓名和选课门数。
select count_number(‘s1’);
4.通过MySQL编程,完成用户自定义函数的创建、调用。
(1)创建一个函数show_pass,任意向函数传递一个学号值,如果该同学已选课,则计算该学生的平均成绩,如果平均成绩大于或等于60分,则输出“pass!”信息,否则输出“fail!”;
delimiter $$
create function show_pass(_sno char(10))
returns varchar(10)
begin
declare _avg decimal(4,2);
declare _pass char(10);
select avg(score) into _avg from sc where sno=_sno;
set _pass=case when _avg>=60 then 'pass!'
else 'fail!'
end;
return _pass;
end$$
delimiter ;
(2)编写代码,调用函数show_pass,显示每位同学的学号、姓名和pass信息。
select show_pass(‘s1’);
三 存储过程
1. 创建存储过程pro_findname对学生姓名进行模糊查找,输入任一字输出姓名中含有该字的全部学生。
Delimiter $$
Create procedure pro_findname (_sn VARCHAR(45))
begin
select sn from student
where sn like (‘%_sn%’);
end $$;
2. 编写存储过程count_Credit,根据学号计算该学生所选课程的总学时。
DELIMITER $$
Create procedrue count_Credit (_sno CHAR(10))
begin
select sum(course.ct)
from sc,course
where sc.cno=course.cno and sc.sno=_sno;
END $$;
四 触发器
1. 创建级联删除触发器del_student_score,当删除student表中的学生时,也删除sc表中的对应学号的学生成绩记录。
DELIMITER $$
drop trigger if exists del_student_score$$
CREATE TRIGGER del_student_score BEFORE DELETE
ON student
FOR EACH ROW
BEGIN
delete from sc where sno=old.sno;
END$$
DELIMITER ;
2. 创建触发器,当修改sc表中的成绩时,要求触发器完成以下任务。
(1)当修改后的成绩小于60分时,按成绩等于60分设定。
(2)当修改后的成绩大于或等于90分时,按成绩等于90分设定。
DELIMITER $$
drop trigger if exists update_sc$$
CREATE TRIGGER update_sc
BEFORE UPDATE
ON sc
FOR EACH ROW
BEGIN
IF NEW.score < 60 THEN
SET NEW.score = 60;
ELSE
SET NEW.score = 90;
END IF;
END$$
DELIMITER ;
3. 创建事件,其任务是完成每月一次的成绩统计:计算每位学生的平均成绩和总分,结果放到表tj中(不用保留原数据
DELIMITER $$
drop EVENT if EXISTS tj_sc$$
CREATE EVENT tj_sc
ON SCHEDULE EVERY 1 MONTH
DO
begin
drop table if EXISTS tj;
create table tj SELECT sno, AVG(score), SUM(score) FROM sc GROUP BY sc.sno;
end$$
DELIMITER ;