【MySQL——视图查询练习】

视图基本语句

（一）创建视图

CREATE VIEW view1
AS
SELECT

（二）查看视图和表

SHOW TABLE STATUS;
DESC stu;
DESC view_1;
SHOW CREATE VIEW view_1;

（三）添加数据

INSERT view_1 VALUES(“刘雪梅”)

（四）修改视图

ALTER VIEW view_users
AS
SELECT uname,uPhone FROM users;

（五）删除视图

DROP VIEW IF EXISTS view_users;

（六）更新视图

UPDATE view_scar
set scNum=10
WHERE uName=“范丙全” AND gdname=“牛肉干”;

视图基本练习

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 birthday >= “1990-01-01”;

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

ALTER VIEW view1
AS SELECT stu.stuid,stuname,depname,couname,teachername
FROM course JOIN scores ON course.couid=scores.couid
JOIN stu ON scores.stuid=stu.stuid
JOIN dep ON stu.depid=dep.depid
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 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.
FROM dep
WHERE depid in (select depid from stu where rx_score > 500);*

9、创建视图VIEW4，使其显示每个系、每门课程的平均成绩，结果格式为：

CREATE VIEW view4
AS SELECT dep.depname 系名,couname AS 课程名,avg(score) AS 平均分
FROM stu JOIN dep ON stu.depid=dep.depid
JOIN scores ON stu.stuid=scores.stuid
JOIN course ON course.couid=scores.couid
GROUP BY dep.depname,couname;

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

SELECT 课程名,平均分
FROM view4
WHERE 系名 LIKE ‘%信息系%’;

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

法一：子查询
CREATE VIEW view5
AS SELECT stuname
FROM stu
WHERE stuid IN (select stuid from scores group by stuid having min(score) > 80);

法二：连接
CREATE VIEW view5
AS SELECT stu.stuname AS stuname
FROM stu JOIN scores ON stu.stuid = scores.stuid
GROUP BY scores.stuid
HAVING min( scores.score ) > 80;

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

法一
CREATE VIEW view6
AS SELECT round(sum(timestampdiff(year, birthday, curdate())) / (select count(sex) from stu where sex = ‘男’), 2)
FROM stu
WHERE sex = ‘男’
说明
SELECT curdate() 查询当前日期
SELECT timestampdiff(year,‘1989-01-07’,curdate()) 查询两个日期所差的年份 2022-1989=33
sum(timestampdiff(year, birthday, curdate()))所有人的年龄之和
SELECT count(sex)人数

法二
CREATE VIEW view66
AS SELECT avg(year(now())-year(birthday))
FROM stu
WHERE sex=‘男’;

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

CREATE VIEW view7
AS SELECT count(scores.score)/count(DISTINCT stu.stuid) AS 平均选课门数
FROM scores JOIN stu ON scores.stuid = stu.stuid

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

CREATE VIEW view8
AS SELECT TRUNCATE(count(*)/count(DISTINCT (dep.depid)),1)
FROM stu JOIN dep ON stu.depid=dep.depid;

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

CREATE VIEW view9
AS SELECT left(stuname,1) as 姓 ,count(*) as 学生人数
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) = “刘”;

欢迎指正！！！