【SQL】超全！以练代学，练完这篇，搞定MySQL语句！（进阶篇）

一 索引和视图

（一）创建索引

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 ;