MySQL，刷题之对视图操作，题+代码！！

视图

1.创建视图view_XS,包括家庭住址为‘北京’的学生信息。
2.创建view_CJ视图，包括上海所有学生的学号、其选修课程号及成绩。
3.创建视图view_sex，包括学生性别和其对应人数。
4.创建学生平均成绩视图view_avg，包括每个学生的学号、姓名和平均成绩。
5.通过视图view_CJ，查找所有成绩在90分以上的学生学号和课程号。
6.通过视图view_avg，查询“张三”的平均成绩。
7.向北京的学生视图view_XS中插入一个新的学生记录，学号为2018001，姓名为赵红平，性别为男，出生时间为1983-4-29,家庭地址北京。
8将北京的学生视图view_XS中学号为2018001 的学生姓名改为‘李娟’。
9.删除北京的学生视图view_XS中学号2018001的学生记录。
10.删除视图view_XS。

1
2
3
4
5
6
7
8
9
10
11

#1
create view view_XS as select * from students where address = "北京";
#2
create view view_CJ(sno,cno,grade)
	as select s.sno,c.cno,sc.grade from courses c,score sc,students s
	where (s.sno = sc.sno and c.cno = sc.cno) and c.ctype = '选修';
#3
create view view_sex(sex,sexnum)
	as select sex,count(sex) from students;
	GROUP BY sex;
#4
create view view_avg(sno,sname,grade_avg)
	as select students.sno,sname,avg(grade) from students
	join score on students.sno = score.sno
	GROUP BY score.sno;
#5
select * from view_cj where grade >= 90;
#6
select avg(grade) from view_avg where sname = '张三';
#7 xx
insert into view_xs(sno,sname,sex,sbirthday,saddress) VALUES('2018001','赵红平','男', '1983-04-29','北京');
#8
UPDATE view_xs set sname = '李娟' where sno='2018001';
#9
DELETE from view_xs where sno='2018001';
#10
DROP view view_xs;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27