【数据库基础】数据库的视图操作

一.实验目的

 1. 定义“MIS”专业学生基本情况视图V_MIS，并根据视图查询结果。

2．将student、salebill、goods表中学生学号、姓名、商品名、商品售价，销售数量定义为视图V_SSG，并根据视图查询结果 。

3. 定义一个带有年龄的学生视图V_AGE，并根据视图查询结果。

4. 定义一个按学院统计人数、平均年龄的视图V_COM，并查询结果。

5. 将各位同学购买商品种类数、消费总额定义为视图V_S_CONSUME，并查询结果。

6. 将各类别的商品总数、商品平均售价、最小库存量定义为视图V_GOODS，要求显示商品所属类别名，并查询结果。

7. 将各供应商提供的商品总数、商品库存总量、平均进价定义视图V_SU_GOOD，要求平均进价在10元以上。并查询结果。

8. 通过视图V_COM，查询平均年龄在19岁以上的学院。

9. 通过视图V_S_CONSUME，查询消费总额在200以上的学生信息。

10. 通过视图V_SU_GOOD，查询商品库存总量小于50的供应商名、供应商电话和地址。

11. 通过视图V_MIS，新增加一个学生记录：学号s09，姓名宋江，出生年2000，CS学院，MIS专业，微信wx009，并查询结果。

12. 通过视图V_MIS，新增加一个学生记录：学号s10，姓名周瑜，出生年1999，CS学院，IT专业，微信wx010，并查询结果。

13. 通过视图V_MIS,将s01学号的姓名改成“李欢欢”，并查询结果。

14. 通过视图V_MIS,将s02学号的姓名改成“徐慧慧”，并查询结果。

15. 通过视图V_SSG，修改“力神咖啡”的商品售价为10元，并查询结果。

16. 通过视图V_MIS，删除学号为s09的学生信息，并查询结果。

17. 通过视图V_MIS，删除学号为s10的学生信息，并查询结果。

二.实验代码

重新创库T2，依题意有:

 1. 定义“MIS”专业学生基本情况视图V_MIS，并根据视图查询结果。

CREATE VIEW v_mis
as
select sno,sname,birthyear,gender,college,major,weixin
from student
where major='mis'

2．将student、salebill、goods表中学生学号、姓名、商品名、商品售价，销售数量定义为视图V_SSG，并根据视图查询结果 。

create view v_ssg
as
select student.sno,sname,goodsname,saleprice,salebill.number
from salebill join student on student.sno=salebill.sno
join goods on goods.goodsno=salebill.goodsno

3. 定义一个带有年龄的学生视图V_AGE，并根据视图查询结果。

create view v_age
as
select sno,sname,birthyear,gender,college,major,weixin,YEAR(getdate())-birthyear as 年龄
from student

select YEAR(getdate())-birthyear as 年龄
from student

select * from v_age

4. 定义一个按学院统计人数、平均年龄的视图V_COM，并查询结果。

create view v_com
as
select college,COUNT(*) as 学院人数,AVG(year(getdate())-birthyear) as Aage 
from student
group by college

select * from  v_com
drop view v_com

5. 将各位同学购买商品种类数、消费总额定义为视图V_S_CONSUME，并查询结果。

create view v_s_consume
as
select goods.categoryno,sname,SUM(salebill.number * saleprice) as SaleSum
from salebill  join student  on student.sno=salebill.sno
join goods on goods.goodsno=salebill.goodsno
group by sname,goods.categoryno

select * from v_s_consume
drop view v_s_consume

6. 将各类别的商品总数、商品平均售价、最小库存量定义为视图V_GOODS，要求显示商品所属类别名，并查询结果。

create view v_goods
as
Select categoryname,sum(number) 商品总数,
avg(saleprice) 平均售价,min(number) 最小库存 from goods 
join category on goods.categoryno=category.categoryno
group by categoryname

select * from v_goods

7. 将各供应商提供的商品总数、商品库存总量、平均进价定义视图V_SU_GOOD，要求平均进价在10元以上。并查询结果。

create view v_su_good
as
select g.supplierno,s.number as 电话,sum(g.number)商品总数,AVG(saleprice) as 平均售价,address,g.number from goods g
join supplier s on g.supplierno=s.supplierno
group by g.supplierno,g.number,address,s.number
having AVG(saleprice)>10

select * from v_su_good
drop view v_su_good
select * from supplier
select * from goods

8. 通过视图V_COM，查询平均年龄在19岁以上的学院。

select * from v_com
where Aage>19

9. 通过视图V_S_CONSUME，查询消费总额在200以上的学生信息。

select * from v_s_consume
where SaleSum>200

10. 通过视图V_SU_GOOD，查询商品库存总量小于50的供应商名、供应商电话和地址。

select *from v_su_good 
where number<50

select * from v_su_good

11. 通过视图V_MIS，新增加一个学生记录：学号s09，姓名宋江，出生年2000，CS学院，MIS专业，微信wx009，并查询结果。

insert into v_mis 
values('s09','宋江','2000','男','CS','MIS','wx009')

12. 通过视图V_MIS，新增加一个学生记录：学号s10，姓名周瑜，出生年1999，CS学院，IT专业，微信wx010，并查询结果。

insert into v_mis 
values('s10','周瑜','1999','男','CS','IT','wx010')

update v_mis set major='IT'
where sno='s10'

13. 通过视图V_MIS,将s01学号的姓名改成“李欢欢”，并查询结果。

update v_mis set sname='李欢欢'
where sno='s01'

select * from v_mis
select * from student

14. 通过视图V_MIS,将s02学号的姓名改成“徐慧慧”，并查询结果。

update v_mis set sname='徐慧慧'
where sno='s02'
select * from v_mis
select * from student

15. 通过视图V_SSG，修改“力神咖啡”的商品售价为10元，并查询结果。

update v_ssg set saleprice='10'
where goodsname='力神咖啡'

select * from v_ssg

16. 通过视图V_MIS，删除学号为s09的学生信息，并查询结果。

delete v_mis
where sno='s09'
select * from v_mis

17. 通过视图V_MIS，删除学号为s10的学生信息，并查询结果。

delete v_mis
where sno='s10'
select * from v_mis

三.代码汇总

--1.定义“MIS”专业学生基本情况视图V_MIS，并根据视图查询结果。//
CREATE VIEW v_mis
as
select sno,sname,birthyear,gender,college,major,weixin
from student
where major='mis'

select * from v_mis
DROP VIEW v_mis

--2将student、salebill、goods表//
create view v_ssg
as
select student.sno,sname,goodsname,saleprice,salebill.number
from salebill join student on student.sno=salebill.sno
join goods on goods.goodsno=salebill.goodsno

DROP VIEW v_ssg

select * from v_ssg

--3//
create view v_age
as
select sno,sname,birthyear,gender,college,major,weixin,YEAR(getdate())-birthyear as 年龄
from student

select YEAR(getdate())-birthyear as 年龄
from student

select * from v_age

--4//
create view v_com
as
select college,COUNT(*) as 学院人数,AVG(year(getdate())-birthyear) as Aage 
from student
group by college

select * from  v_com
drop view v_com

--5//
create view v_s_consume
as
select goods.categoryno,sname,SUM(salebill.number * saleprice) as SaleSum
from salebill  join student  on student.sno=salebill.sno
join goods on goods.goodsno=salebill.goodsno
group by sname,goods.categoryno

select * from v_s_consume
drop view v_s_consume

--6//
create view v_goods
as
Select categoryname,sum(number) 商品总数,
avg(saleprice) 平均售价,min(number) 最小库存 from goods 
join category on goods.categoryno=category.categoryno
group by categoryname

select * from v_goods
--7//
create view v_su_good
as
select g.supplierno,s.number as 电话,sum(g.number)商品总数,AVG(saleprice) as 平均售价,address,g.number from goods g
join supplier s on g.supplierno=s.supplierno
group by g.supplierno,g.number,address,s.number
having AVG(saleprice)>10

select * from v_su_good
drop view v_su_good
select * from supplier
select * from goods
--8//
select * from v_com
where Aage>19

--9//
select * from v_s_consume
where SaleSum>200

--10//
select *from v_su_good 
where number<50

select * from v_su_good

--11//
insert into v_mis 
values('s09','宋江','2000','男','CS','MIS','wx009')

select * from v_mis

--12//
insert into v_mis 
values('s10','周瑜','1999','男','CS','IT','wx010')

update v_mis set major='IT'
where sno='s10'

select * from v_mis
select * from student

delete student
where sno='s10'
--13
update v_mis set sname='李欢欢'
where sno='s01'

select * from v_mis
select * from student

--14//
update v_mis set sname='徐慧慧'
where sno='s02'
select * from v_mis
select * from student

--15//
update v_ssg set saleprice='10'
where goodsname='力神咖啡'

select * from v_ssg

--16//
delete v_mis
where sno='s09'
select * from v_mis

--17//
delete v_mis
where sno='s10'
select * from v_mis