【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) = “刘”;

欢迎指正!!!

  • 5
    点赞
  • 9
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:书香水墨 设计师:CSDN官方博客 返回首页
评论

打赏作者

J7.777

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值