/*一、创建索引*/
/*1、为学生成绩表在建立学号和课程编号上的唯一索引
	唯一索引指的是此处的每一个索引值只对应唯一的数据记录
*/
create unique index stu_sco_index
on v_student_score(student_id,course_id);

/*2、运行结果可通过执行系统的存储过程查看：Sp_helpindex 表名*/
/*Sql Server运行代码*/
Sp_helpindex v_student_score;

/*Mysql运行代码*/
show index
from v_student_score;

/*3、为教材选用表在数量上建立聚集索引聚簇索引指索引项的顺序与表中记录的物理顺序一致*/
/*Sql server运行代码*/
/*①默认情况下,主键是聚集索引。先删除系统默认创建的索引PK__v_textbook_selec__2A4B4B5E*/	
alter table v_textbook_selection
	drop constraint PK__v_textbook_selec__2A4B4B5E;
/*②添加主键，将表中规定主键修改为非聚集类型*/
alter table v_textbook_selection
	add constraint pk_course_textbook
	primary key nonclustered(course_id, textbook_id) ;
/*③在数量列上创建此聚集索引*/	
create clustered index textbook_selection_index
on v_textbook_selection(textbook_selection_number);
/*④查看索引,查看到一条是sql server默认给主键设置的索引，另一条便是第③步创建的聚集索引*/
Sp_helpindex v_textbook_selection;

/*Mysql运行代码*/
/*①为Mysql建立一个普通索引*/
create index textbook_selection_index 
on v_textbook_selection(textbook_selection_number);
/*②查看索引*/
show index 
from v_textbook_selection;

/*4、删除教材选用表在数量上的索引*/
/*Sql Server运行代码*/
drop index textbook_selection_index 
on v_textbook_selection;

Sp_helpindex v_textbook_selection;

/*Mysql运行代码*/
alter table v_textbook_selection
	drop index textbook_selection_index;

show index
from v_textbook_selection;

/*二、更新和删除数据*/
/*1、更新学生成绩表，将选择数据库的同学成绩都加上2分*/
/*①先查看学生成绩表中的信息*/
select * from v_student_score;
/*②因为设置的check语句中规定了分数为0到100之间的浮点数，故不能直接加2，否则update会与check约束发生冲突*/
update v_student_score
set grade = grade + 2
where grade <= 98;
/*③再次查看学生成绩表中的数据，发现已更新*/
select * 
from v_student_score;

/*2、更新教材表中《教材十一》的出版时间为今天*/
/*①先查看教材表中的信息*/
select * 
from v_textbook;
/*②修改教材十一的出版时间为今天*/
/*Sql server运行代码*/
update v_textbook
set publish_date = getdate()
where textbook_name = '教材十一';

/*Mysql运行代码*/
update v_textbook
set publish_date = curdate()
where textbook_name = '教材十一';
/*③再次查看教材表中的信息*/
select * from v_textbook;

/*3、删除没有被选中的教材的信息*/
/*Sql Server代码*/
delete 
from v_textbook
where textbook_id not in(
	select v_textbook.textbook_id
	from v_textbook_selection, v_textbook
	where v_textbook_selection.textbook_id = v_textbook.textbook_id
);

/*Mysql代码*/
/*①先创建一个临时表*/
create table temp as(
	select v_textbook.textbook_id
	from v_textbook_selection, v_textbook
	where v_textbook_selection.textbook_id = v_textbook.textbook_id 
);
/*②在v_textbook中执行删除操作*/
delete 
from v_textbook
where textbook_id not in(
	select *
	from temp
);
/*③删除临时表*/
drop table temp;

/*4、根据学生选课人数修改教材选用的数量，数量为学生人数+2*/
/*Sql Server执行代码*/
update v_textbook_selection
set v_textbook_selection.textbook_selection_number = t.number + 2
from v_textbook_selection, (
	select v_student_score.course_id, count(v_student_score.course_id) as number
	from v_student_score
	group by(v_student_score.course_id)	
	)
	as t
where t.course_id = v_textbook_selection.course_id;

select * 
from v_textbook_selection;
	 
/*Mysql执行代码*/
update v_textbook_selection
inner join (
	select v_student_score.course_id, count(v_student_score.course_id) as number
	from v_student_score
	group by(v_student_score.course_id)
)
as t 
on t.course_id = v_textbook_selection.course_id
set v_textbook_selection.textbook_selection_number = t.number + 2;

select * 
from v_textbook_selection;

/*三、修改表结构、约束*/
/*1、在学生表中增加一个班号列和住址列*/
alter table v_student
	add student_class varchar(20);
alter table v_student
	add student_address varchar(50);
select *
from v_student;

/*2、为教材表的出版社属性值加非空约束*/
/*Sql Server运行代码*/
alter table v_textbook
	alter column press varchar(50) not null;

/*Mysql运行代码*/
alter table v_textbook
	modify press varchar(50) not null;

/*3、如果课室表中容纳人数为整型，修改为字符类型，如果为字符类型，修改为整型*/
/*①首先*查询表结构，查询结果为int类型*/
/*Sql Server运行代码*/
Sp_help v_classroom;

/*Mysql运行代码*/
desc v_classroom;
/*②修改类型*/
/*Sql Server运行代码*/
alter table v_classroom
	alter column classroom_capacity varchar(20);

/*Mysql运行代码*/
alter table v_classroom
	modify classroom_capacity varchar(20);

/*4、修改成绩取值范围的约束，改为0-150之间*/
/*Sql Server运行代码*/
alter table v_student_score
	drop constraint CK__v_student__grade__08EA5793;  
alter table v_student_score
	add constraint CK_grade
	check(grade <= 150 and grade >=0);

/*Mysql运行代码*/
/*需要用到触发器*/

/*四、创建视图*/
/*1、创建一个包含学生信息、课程信息，以及选课信息的视图，
	包括以下属性：学号，姓名，性别，年龄，系别，课程号，课程名，成绩
*/
create view STUDENT_INFO
as
select v_student.student_id, student_name, student_sex, 2016 - year(student_birthday) as student_age, department_name, v_student_score.course_id, course_name, grade
from v_student, v_department, v_student_score, v_course
where v_student.student_id = v_student_score.student_id
	and v_student.department_id = v_department.department_id
	and v_student_score.course_id = v_course.course_id
with check option;

/*2、查询视图的全部元组*/
select * 
from STUDENT_INFO;

/*3、创建一院系学生平均成绩视图S_AVG，包括院系名称和平均成绩*/
/*Sql Server运行代码*/
create view S_AVG
as
select v_department.department_name, department_avg_grade 
from v_department, (
	select v_student.department_id, avg(grade) as department_avg_grade
	from v_department, v_student, v_student_score
	where v_student.student_id = v_student_score.student_id
	and v_student.department_id = v_department.department_id
	group by (v_student.department_id)
) t
where t.department_id = v_department.department_id;

/*Mysql运行代码*/
create view S_AVG
as
select v_department.department_name, avg(grade) as department_avg_grade
from v_department, v_student, v_student_score
where v_student.student_id = v_student_score.student_id
and v_student.department_id = v_department.department_id
group by (v_student.department_id);

/*4、查看视图的全部元组*/
select * 
from S_AVG;

/*5、修改视图定义，增加一个当前年份属性，并给出修改前后的视图执行结果截图*/
/*Sql Server运行代码*/
alter view S_AVG
AS
select v_department.department_name, department_avg_grade, 
datepart(yyyy, getdate()) as current_year
from v_department, (
	select v_student.department_id, avg(grade) as department_avg_grade
	from v_department, v_student, v_student_score
	where v_student.student_id = v_student_score.student_id
	and v_student.department_id = v_department.department_id
	group by (v_student.department_id)
) t
where t.department_id = v_department.department_id;

/*Mysql运行代码*/
alter view S_AVG
AS
select v_department.department_name, avg(grade) as department_avg_grade, 
date_format(curdate(), '%Y') as current_year
from v_department, v_student, v_student_score
where v_student.student_id = v_student_score.student_id
and v_student.department_id = v_department.department_id
group by v_student.department_id;

select * 
from S_AVG;