MySQL系列（七）索引和视图

（1）索引的创建于管理
① 为student表的phone列上建立一个降序普通索引phone_idx，并输出student表中的记录，注意观察phone字段上的顺序；

create index  phone_idx on student(phone desc); select * from info1.student order by phone desc;

② 在course表的cname列上建立一个唯一性索引cname_idx，并输出course表中的记录，注意观察cname字段上的顺序;

create unique index cname_idx on course(cname);select * from info1.course;

③ 在score表的studentno和courseno列上建立一个复合索引stu_cour_idx，并输出score表中的记录，注意观察结果集的顺序；

create index stu_cour_idx on score(studentno,courseno);select * from info1.score;

④ 在teacher表上建立teacherno主键索引，建立tname和prof的复合索引；

alter table teacher add primary key(teacherno),add index mark(tname,prof);

⑤ 利用drop语句删除teacher表上mark索引；

drop index mark on teacher;

⑥ 利用alter table语句删除cname_idx索引；

alter table course drop index cname_idx;

（2）利用Mysql Workbench创建索引。
① 进入修改表student界面
② 打开index选项卡，在Index Name的文本框中输入索引名称un_phone，右侧的Index Column会自动显示表student中的所有列名，选择phone列。
③ 存储类型选择BTREE，选择索引类型unique，表示创建唯一性索引，其他参数采用默认值。
④ 设置完成后，单击Apply按钮，出现脚本对话框。
⑤ 单击Apply按钮，进入完成对话框，单击Finish按钮，即可完成在数据库teaching中student表上的唯一性索引un_phone的创建。
⑥ 也可以在此界面中实现索引的修改和删除。

（3）创建和管理视图。
① 在teacher表上创建一个简单的视图，视图名称为v_teacher；

create view v_teacher as select * from teacher;select * from v_teacher;

② 在student表、course表和score表上创建一个名为stu_score的视图。视图中保留19级女生的学号，姓名，性别，电话，课程名，期末成绩；

create view stu_score as select student.studentno,sname,sex,phone,cname,final from score join student on student.studentno = score.studentno join course on course.courseno = score.coursenowhere sex = '女' and left(student.studentno,2) = '19';select * from stu_score;

③ 创建视图v_teach，统计材料学院的教师中不是教授或副教授的教师号、教师名和专业；

create view v_teachas select teacherno,tname,major from teacherwhere prof not like '%教授' and department = '材料学院';select * from v_teach;

④ 查看视图定义的情况；

show create view stu_score;

⑤ 修改视图v_teach，统计材料学院的教师中的教授和副教授的教师号，教师名和专业，并在视图名后面指明视图列名称；

alter view v_teach(教师号,教师名,专业)as select teacherno,tname,major from teacherwhere prof like '%教授' and department = '材料学院';select * from v_teach;

⑥ 删除视图v_teach的命令；

drop view v_teach;

（4）利用视图修改数据
① 创建视图view_avg，查询每个学生的学号，姓名及平均分，并且按照平均分降序排序；

create view view_avgas select student.studentno,sname,avg(final)from score join student on score.studentno = student.studentno group by sname order by avg(final) desc;select * from  view_avg;

② 通过视图v_teacher，对基表teacher进行插入，更新和删除数据（插入、更新、删除的数据集自拟），观察teacher表上数据的变化情况；

insert into v_teacher (teacherno,tname,major,prof,department)values ('t07011','康哲彦','人文基础','讲师','文学与传媒学院'),('t07033','郭华荣','新闻学','教授','文学与传媒学院');SELECT * FROM info1.teacher;

③ 视图stu_score依赖于源表student、course和score三张表，包括studentno、sname、phone、cname、final五个字段，通过视图stu_score修改基本表student中的学号为19112100072的电话号码为‘15011112222’；

update stu_score set phone = '15011112222'where studentno ='19112100072';

④ 修改v_teacher的视图定义，添加with check option选项；

alter view v_teacheras select * from teacherwhere department = '计算机学院' with check option;

来源地址：https://blog.csdn.net/weixin_53850894/article/details/130621720