目录
一、实验目的
二、实验内容
1。为什么要用视图
2。视图创建
3。应用案例
【案例1】创建成绩视图grade_view（难度：易）
【案例2】统计学生表男女生占比（难度：中）
【案例3】查询老师所授课成绩最高最低课程（难度：中）
【案例4】成绩表各门课各阶成绩人数统计（难度：高）
三、实验作业
1。统计每门课的最高分、最低分、平均成绩
2。查询男女生选课最多的人及各门课的平均成绩。
3。查询成绩最低分、最高分、平均最低分、平均最高分的课程及老师
四、实验总结
【实验3】视图使用
一、实验目的
① 理解视图的概念和作用。
② 使用视图保存应用查询，便于就近访问数据库。
③ 加深对复杂SELECT查询的理解和使用。
④ 学习程序访问视图数据。
二、实验内容
1。为什么要用视图
l重用SQL语句；
l隐藏复杂的SQL操作，按名访问视图；
l提高数据安全性；
l重构数据表示；
l提高数据逻辑独立性。
2。视图创建
命令格式：
CREATE VIEW 视图名
AS
SELECT 查询...
【注意】
① MySQL建立视图SELECT查询不允许嵌套子查询，否则会出错，如果有子查询，先把子查询建立视图，然后引用它；SQLITE3创建视图可以有子查询。
②MySQL-SELECT-FROM子句嵌套的子查询需要有个别名，SQLITE3可以有，也可以没有别名。
3。应用案例
【案例1】创建成绩视图grade_view（难度：易）
CREATE VIEW grade_view
AS
SELECT sno,sname,cname,score,tname
FROM s natural join sc natural join c natural join t;
-- 视图效果
--视图应用
此时如果要查询选了LI老师所授课的学生及课程，用如下查询实现：
引用视图，相当于SQL重用，查询语句简单多了。
SELECT * FROM grade_view WHERE tname='LI';
-- 查询结果
-- 程序访问视图
import sqlite3
import pymysql
import pandas as pd
def access_db_view(dblinkstring,user_view,fields,sql):
'''参数表
dblinkstring: 数据库连接字符串
user_view: 用户视图
fields：属性名表
sql: 访问视图语句'''
db = eval(dblinkstring)
c = db.cursor()
c.execute(sql)
results = list(c.fetchall())
data = pd.DataFrame(results,columns=fields)
c.close()
db.close()
return data
# 应用实例：成绩视图
#dblinkstring = "sqlite3.connect('D:\stumis\mydb.sqlite')"
dblinkstring = "pymysql.connect(user='root',passwd='root',db='mydb')"
user_view = 'grade_view'
fields = 'sno,sname,cname,score,tname'.split(',')
sql = f'select * from {user_view}'
data = access_db_view(dblinkstring,user_view,fields,sql)
print(f'# 访问Mydb视图：{user_view}\n{data}')
-- 执行结果
【案例2】统计学生表男女生占比（难度：中）
-- 显示总人数、男生数、女生数，男女生比率。
-- 视图效果
-- 分步实现
① 分组统计男女生人数
-- 参考代码
SELECT sex,count(sex) count_sex
FROM s
GROUP BY sex HAVING sex is not null
-- 查询效果
② 行变列
SELECT sum(case sex when '女' then count_sex else 0 end) as 'count_nv',
sum(case sex when '男' then count_sex else 0 end) as 'count_nan',
sum(case sex when '男' then count_sex else count_sex end) as 'total'
FROM (SELECT sex,count(sex) count_sex
FROM s GROUP BY sex HAVING sex is not null);
--查询效果
③ 查询合并
-- 参考代码（SQLITE3通过，MySQL视图不能嵌套子查询）
CREATE VIEW rate_of_sex
AS
SELECT total 总人数,count_nv 女生人数,count_nan 男生人数,
round(count_nv/cast(total as double),3) as 女生占比,
round(count_nan/cast(total as double),3) as 男生占比
FROM (SELECT sum(case sex when '女' then count_sex else 0 end) as 'count_nv',
sum(case sex when '男' then count_sex else 0 end) as 'count_nan',
sum(case sex when '男' then count_sex else count_sex end) as 'total'
FROM (SELECT sex,count(sex) count_sex FROM s GROUP BY sex HAVING sex is not null));
-- 视图效果
-- 程序访问视图
import sqlite3
db = sqlite3.connect('D:\stumis\mydb.sqlite')
c = db.cursor()
fields = ['总人数', '女生人数', '男生人数', '女生占比','男生占比']
sql = "select * from rate_of_sex"
print('访问sqlite3-Mydb视图：rate_of_sex')
c.execute(sql)
result = c.fetchone()
data = dict(zip(fields,result))
print(data)
c.close()
db.close()
-- 执行结果
【案例3】查询老师所授课成绩最高最低课程（难度：中）
-- 显示课号、课名、最低分、最高分、平均分、授课老师
1）问题描述
① 查询哪个老师所授课成绩最低，显示课号，课名，授课老师，课程最低分，课程平均分；
② 查询哪个老师所授课成绩最高，显示课号，课名，授课老师，课程最高分，课程平均分；
③ 把①②结果合并为一个查询。
2）视图效果
3）分步实现
① 课程最低成绩
-- 参考代码
SELECT DISTINCT CNO,SCORE MIN_SCORE
FROM SC
WHERE SCORE=(SELECT MIN(SCORE) FROM SC)
--查询结果
② 课程平均成绩
--参考代码
SELECT CNO,ROUND(AVG(SCORE),1) AVG_SCORE
FROM SC
GROUP BY CNO
--查询结果
③ 合并查询
-- 参考代码
SELECT CNO,CNAME,MIN_SCORE 最低分, AVG_SCORE 平均分,TNAME
FROM C NATURAL JOIN T NATURAL JOIN
(SELECT DISTINCT CNO,SCORE MIN_SCORE FROM SC WHERE SCORE=(SELECT MIN(SCORE) FROM SC))tb1
NATURAL JOIN
(SELECT CNO,ROUND(AVG(SCORE),1) AVG_SCORE FROM SC GROUP BY CNO)tb2
-- 查询结果
④ 查询哪个老师所授课成绩最高，显示课号，课名，授课老师，课程最高分，课程平均分；
SELECT CNO,CNAME,MAX_SCORE 最高分, AVG_SCORE 平均分,TNAME
FROM C NATURAL JOIN T NATURAL JOIN
(SELECT DISTINCT CNO,SCORE MAX_SCORE FROM SC WHERE SCORE=(SELECT MAX(SCORE) FROM SC))tb3
NATURAL JOIN
(SELECT CNO, ROUND(AVG(SCORE),1) AVG_SCORE FROM SC GROUP BY CNO)tb4
--查询结果
⑤ 把③④查询合并，创建视图（SQLITE3实现）
--参考代码
CREATE VIEW max_min_score
AS
-- ① 查询哪个老师所授课成绩最低，显示课号，课名，授课老师，课程最低分，课程平均分；
SELECT CNO,CNAME,MIN_SCORE 最低分, null 最高分, AVG_SCORE 平均分,TNAME
FROM C NATURAL JOIN T NATURAL JOIN
(SELECT DISTINCT CNO,SCORE MIN_SCORE FROM SC
WHERE SCORE=(SELECT MIN(SCORE) FROM SC))tb1
NATURAL JOIN
(SELECT CNO,ROUND(AVG(SCORE),1) AVG_SCORE FROM SC GROUP BY CNO)tb2
UNION ALL
-- ② 查询哪个老师所授课成绩最高，显示课号，课名，授课老师，课程最高分，课程平均分；
SELECT CNO,CNAME, null 最低分, MAX_SCORE 最高分, AVG_SCORE 平均分,TNAME
FROM C NATURAL JOIN T NATURAL JOIN
(SELECT DISTINCT CNO,SCORE MAX_SCORE FROM SC
WHERE SCORE=(SELECT MAX(SCORE) FROM SC))tb3
NATURAL JOIN
(SELECT CNO, ROUND(AVG(SCORE),1) AVG_SCORE FROM SC GROUP BY CNO)tb4
-- 查询结果
-- 程序访问视图参考代码
import sqlite3
import pandas as pd
db = sqlite3.connect('D:\stumis\mydb.sqlite')
c = db.cursor()
sql = 'select * from max_min_score'
title = ['cno','cname','min_score','max_score','avg_score','tname']
print('\n> 访问sqlite3视图：max_min_score')
while sql:
c.execute(sql)
results = c.fetchall()
df = pd.DataFrame(results,columns=title)
print(df)
sql = input('sqlite> 请输入SQL语句：')
c.close()
db.close()
-- 程序输出
【案例4】成绩表各门课各阶成绩人数统计（难度：高）
① 问题描述
统计成绩表SC中各门课各阶成绩人数：
0~60,60~69,70~79,80~89,90~100各阶成绩人数，要求每门课一行。
② 视图效果
③ 分步实现
-- 每门课统计各阶成绩（视图：jies1)
CREATE VIEW jies1
AS
select cno,'0~60' jies, count(1) nums from sc where score<60 group by cno
union
select cno,'60~70' jies, count(1) nums from sc where score>=60 and score<70 group by cno
union
select cno,'70~80' jies, count(1) nums from sc where score>=70 and score<80 group by cno
union
select cno,'80~90' jies, count(1) nums from sc where score>=80 and score<90 group by cno
union
select cno,'90~100' jies, count(1) nums from sc where score>=90 group by cno;
-- 执行结果：各门课各阶成绩人数
-- 统计各阶人数（视图：jies2）
-- 行变列，每阶成绩一列，每个cno一行，复用了jies1视图查询。
CREATE VIEW jies2
AS
SELECT cno,
sum(case jies when '0~60' then nums end) as JE, #'0~60',
sum(case jies when '60~70' then nums end) as JD, #'60~70',
sum(case jies when '70~80' then nums end) as JC, #'70~80',
sum(case jies when '80~90' then nums end) as JB, #'80~90',
sum(case jies when '90~100' then nums end) as JA #'90~100'
FROM jies1 GROUP BY cno order by cno;
-- 视图效果
-- 连接课程表C，完成数据整合（视图：jies3）
-- 重用视图jies2查询
CREATE VIEW jies3(cno,cname,je,jd,jc,jb,ja)
AS
SELECT cno,cname,
JE '0~60',JD '60~70',JC '70~80',JB '80~90',JA '90~100'
FROM jies2 NATURAL JOIN C
ORDER BY cno;
④ 完整SQL查询语句
--在MySQL实现需要分步创建视图，不能嵌套子查询，以下查询不能直接创建视图，在SQLITE3可以。
--以上可以看出，视图实现了SQL代码重用，不分布引用视图的话，SQL查询写起来很复杂。
SELECT cno,cname,
JE '0~60',JD '60~70',JC '70~80',JB '80~90',JA '90~100'
FROM (SELECE cno,
SUM(case jies when '0~60' then nums end) as JE,
SUM(case jies when '60~70' then nums end) as JD,
SUM(case jies when '70~80' then nums end) as JC,
SUM(case jies when '80~90' then nums end) as JB,
SUM(case jies when '90~100' then nums end) as JA
FROM (
SELECT cno,'0~60' jies, count(1) nums
FROM sc WHERE score<60 GROUP BY cno
UNION
SELECT cno,'60~70' jies, count(1) nums FROM sc WHERE score>=60 and score<70 GROUP BY cno
UNION
SELECT cno,'70~80' jies, count(1) nums FROM sc WHERE score>=70 and score<80 GROUP BY cno
UNION
SELECT cno,'80~90' jies, count(1) nums FROM sc WHERE score>=80 and score<90 GROUP BY cno
UNION
SELECT cno,'90~100' jies, count(1) nums FROM sc WHERE score>=90 group by cno)tb1 -- jies1
GROUP BY)tb2 -- jies2
NATURAL JOIN C ORDER BY cno;
--查询结果
5。程序访问视图
☐ 参考代码
import pymysql
import pandas as pd
db = pymysql.connect(user='root',passwd='root',db='mydb')
c = db.cursor()
sql = 'select * from jies3'
fields = ['cno','cname','0<60','60~70','70~80','80~90','90~100']
while sql:
c.execute(sql)
results = list(c.fetchall())
df = pd.DataFrame(results,columns=fields)
print(df)
sql = input('mysql> 请输入SQL语句：')
c.close()
db.close()
☐ 运行结果
三、实验作业
-- 使用教学数据库，建立实现下列查询的视图，使用MySQL或sqlite3数据库。
1。统计每门课的最高分、最低分、平均成绩
-- 显示cno,cname,min_score, max_score, avg_score。
-- 参考视图
-- 分步提示
① 查询每门课的最低分
② 查询每门课的最高分
③ 查询每门课的平均分
④ 用自然连接合并查询
2。查询男女生选课最多的人及各门课的平均成绩。
-- 参考视图
-- 分步提示
① 查询男生选课最多的门数；
② 查询男生选课门数最多的人及平均分；
③ 同理，查询女生选课门数最多的人及平均分；
④ 用UNION合并查询并建立视图，注意两者要属性一样。
3。查询成绩最低分、最高分、平均最低分、平均最高分的课程及授课老师，
要求显示：cno 课号,cname 课名,tname 教师,min_score 最低分,max_score 最高分,
min_avg_score 最低平均分,max_avg_score 最高平均分。
-- 参考视图
-- 分步提示
① 查询成绩最低的课程；
② 查询成绩最高的课程；
③ 查询平均分最低的课程；
④ 查询平均分最高的课程；
⑤ 用UNION合并查询并建立视图，注意属性要一样。
四、实验总结
从数据访问的角度看，视图起到了重构数据，简化查询及SQL重用的作用，极大方便了数据使用。
另外，对SQL-SELECT查询需要多做练习，多调试操作，切实掌握，实践出真知。
