管理数据库对象------索引与视图

一、 索引

1. 作用：加快查询速度
2. 创建
   1） 创建表同时创建索引
   create table student1
   (sno char(4),
   sname char(20),
   index ix_sno(sno asc));
   2） 在已经存在的表上创建索引
   create index ix_sno on student(sno desc);
3. 查看 show index from student;
4. 删除
   drop index ix_sno on student1;
   二、 视图
5. 定义：从一个或多个基本表或视图中导出的虚表。
6. 作用：

1. 简化对数据的操作。
2. 自定义数据。
3. 数据集中显示。
4. 导入和导出数据。
5. 合并分割数据。
6. 安全机制。

3. 创建（修改）
   Create(alter) view 名字
   As
   Select语句
   举例
   创建视图v1，其内容包括学生学号、姓名、课程号、课程名、成绩；
   create view v1
   as
   select student.sno,sname,course.cno,cname,grade
   from student join sc on student.sno = sc.sno
   join course on sc.cno = course.cno;

4. 查看
   查看结构：desc boy;
   查看内容：select语句

5. 使用
   select *
   from v1
   where sname = ‘刘晨’;
   对视图的增删改是有限制的
   出错示例：
   insert into v1
   values(‘2222’,‘h’,‘c01’,‘java’,80);

create view v3
as
select sno,avg(grade)
from sc
group by sno;

update v3
set avg(grade) = 80
where sno = ‘0001’;

6. 删除
   drop view v1;

视图和索引练习：

1. 创建视图view1，其内容包括20岁以上所有学生学号、姓名和年龄；
   create view v1
   as
   select sno,sname,sage
   from student
   where sage > 20;
2. 创建视图view2，其内容包括20岁以上所有学生学号、姓名和年龄，并使用with check option；
   create view v2
   as
   select sno,sname,sage
   from student
   where sage > 20
   with check option;
3. 创建视图view3，其内容包括学生学号、姓名、课程号和成绩；
   create view v3
   as
   select student.sno,sname,cno,grade
   from student join sc
   on student.sno = sc.sno;
4. 创建视图view4，其内容包括每门课程的平均成绩；
   create view v4
   as
   select cno,avg(grade)
   from sc
   group by cno;
5. 为view1添加记录：学号11，姓名tom，年龄：18；
   mysql> insert into v1
   -> values(‘11’,‘tom’,18);
   Query OK, 1 row affected (0.03 sec)
6. 查看view1的内容；
   select * from v1;
7. 为view2添加记录：学号12，姓名jerry，年龄：19；结果如何？
   mysql> insert into v2
   -> values(‘12’,‘jerry’,19);
   ERROR 1369 (HY000): CHECK OPTION failed ‘xkdb.v2’
8. 为view3添加内容：学号：13，姓名：mary、课程号：c01，成绩：85；结果如何？
   mysql> insert into v3
   -> values(‘13’,‘mary’,‘c01’,85);
   ERROR 1394 (HY000): Can not insert into join view ‘xkdb.v3’ without fields list
9. 为view4添加内容：平均成绩：85，结果如何？
   mysql> insert into v4
   -> values(‘c03’,85);
   ERROR 1471 (HY000): The target table v4 of the INSERT is not insertable-into
10. 为学生表的学号列创建唯一的升序索引ix_sno；
    create unique index ix_sno on student(sno asc);
11. 删除学生表的索引ix_sno;
    drop index ix_sno on student;