MySQL——视图查询

1、创建一个视图VIEW1，用于查询所有1990年以后出生的学生的学号、姓名、性别、入学成绩、以及他们所选修的课程号、课程名、任课教师姓名等信息

CREATE view view1
as
SELECT stu.stuid,stuname,sex,rx_score,course.couid,couname,teachername
from stu join scores on stu.stuid=scores.stuid
join course on scores.couid=course.couid
where stu.birthday >=‘1990-01-01’;

2、 要求将视图VIEW1修改为用于查询所有男生的学生的学号、姓名、系名以及他们所选修的课程名、任课教师姓名等信息

ALTER view view1
as
SELECT stu.stuid,stuname,depname,couname,teachername
from dep join stu on dep.depid=stu.depid
join scores on stu.stuid=scores.stuid
join course on scores.couid=course.couid
where sex=‘男’;

3、创建一个视图VIEW2，用于查询电话号码以‘189’开头的并且班级中包含‘软件’字符的学生姓名和班级、电话号码

CREATE view view2
as
select stuname,class,phone
from stu
where phone like “189%” and class like ‘%软件%’;

4、向VIEW2插入一条记录（周舟，18软件2班，18998879999）

INSERT into view2(stuname,class,phone)VALUES(‘周舟’,‘18软件2班’,‘18998879999’);

5、在VIEW2中将刚刚插入的记录的电话号码修改为18911111111

update view2
set phone=18911111111
where stuname=‘周舟’;

6、在VIEW2中将刚刚插入的记录删除

DELETE from view2
where stuname=‘周舟’;

7、删除视图VIEW2

drop view view2;

8、创建视图VIEW3，要求查询出学生的最高入学成绩高于500的系部信息

CREATE VIEW view3
as
select dep.depid,depname,director,dep.phone
from stu join dep on stu.depid=dep.depid
where rx_score>500;

9、创建视图VIEW4，使其显示每个系、每门课程的平均成绩

CREATE VIEW view4
as
select dep.depname as 系名,couname as 课程名,avg(score) as 平均分
from dep join stu on dep.depid=stu.depid
join scores on stu.stuid=scores.stuid
join course on scores.couid=course.couid
GROUP BY dep.depname ,course.couname;

10、通过VIEW4查看信息系每门课程的平均成绩

select 课程名,平均分
from view4
where 系名 like ‘%信息系%’;

11、创建视图VIEW5，使其显示所选修的每门课程都大于80分的学生姓名

create view view5
as
select stu.stuname
from stu join scores on stu.stuid=scores.stuid
GROUP BY scores.score
HAVING min(scores.score)>80;

12、创建视图VIEW6，要求查询出全体男生的平均年龄，要求将结果保留到小数点后2位。（提示：设置小数位数的函数是round(x,y),表示将x保留小数点后y位）

CREATE view view6
as
select avg(year(now())-year(birthday))
from stu
where sex=‘男’;

13、创建视图VIEW7，要求统计出所有学生的平均选课门数，将结果保存到小数点1位

CREATE view view7
AS
select truncate(count(*) / count(DISTINCT(stu.stuid)),1) as 平均选课门数
from scores right join stu on scores.stuid = stu.stuid
join course on course.couid = scores.couid;

14、创建视图VIEW8，要求查询所有系的平均学生人数，将结果保存到小数点后1位

create view view8 as
select round(count(stuid) / (select count(depid) from dep), 1)
from stu
where depid is not null;

15、创建视图VIEW9，要求查询出每个姓氏的学生人数。（提示：left(s,n)表示从字符串s中提取出前n个字符）

create view view9 as
select left(stuname, 1), count(left(stuname, 1))
from stu
group by left(stuname, 1);

16、创建视图VIEW10，要求查询出姓氏人数最多的前2个姓及其人数

CREATE view view10
as
select left(stuname,1) ,count(*)
from stu
GROUP BY left(stuname,1) limit 0,2;

17、创建视图VIEW11，要求查询出名字为3个字且第2字是“小”的学生人数

CREATE view view11
AS
select count(*) as 学生人数
from stu
where char_length(stuname) = 3 and right(substring(stuname,1,2),1) = “小”;

18、创建视图VIEW12，请查询出姓刘的学生人数占全班总人数的百分比,要求保留两位小数

create view view12
AS
select truncate(count(*) / 10 ,2)
from stu
where left(mid(stuname,1),1) = “刘”;