一、视图和索引练习
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