MSSQL之十九 视图

create table 出版社信息表
(
  pub_id int not null primary key,
  pub_name char(10),
  city char(10)
)

create table 图书信息表
(
  book_id int not null primary key,
  book_name char(16),
  price money,
  pub_id int
    constraint FK_books与publish
                                  foreign key
                                         references 出版社信息表 (pub_id)
)

drop table 出版社信息表
drop table 图书信息表

insert into 图书信息表 values(1,'C 语言程序设计',62,1)
insert into 图书信息表 values(2,'VB 程序设计',46,1)
insert into 图书信息表 values(3,'Java 程序设计',54,3)
insert into 图书信息表 values(4,'C++ 程序设计',36,4)
insert into 图书信息表 values(5,'汇编语言',39,3)
insert into 图书信息表 values(6,'T-SQL 语言',64,4)

insert into 出版社信息表 values(1,'科技出版社','福建')
insert into 出版社信息表 values(2,'前进出版社','上海')
insert into 出版社信息表 values(3,'希望出版社','河南')
insert into 出版社信息表 values(4,'胜利出版社','广州')

-----------------------------------------------

select * from  图书信息表
select * from 出版社信息表


-------------------------------------------------
create view view_infor1 (编号cc,名称aa,城市bb)  --查询结果起别名
as 
select top 3 pub_id as 编号,pub_name as 名称,city as 城市 from 出版社信息表 
--where city='福建'
order by pub_id desc

drop view view_infor1
---------------------------------------------
select * from view_infor1  -- 执行创建的视图

----------------------------------------------


---------------------------------------------------------
drop view view_infor
---------------------------------------------------------
--视图的查询
create view view_infor --(book_id,book_name,price)
as 
select sum(price) [as]
from 图书信息表
--COMPUTE sum(price)

drop view view_infor
select * from view_infor
----------------------------------------------------------
select * from view_infor
where price > 40
order by price desc

------上下等价-------------------------
select book_id,book_name,price from 图书信息表
where price > 40
order by price desc
---------------------------------------

create view view_infor(book_name,sum_price)
as 
select book_name,sum(price)
from 图书信息表
group by book_name

--------------------------------------------
select * from view_infor

--===================================================
--===================================================
select * from 图书信息表

create view view_infor(book_id,book_name,price,pub_id)
as 
select book_id,book_name,price,pub_id
from 图书信息表

select * from view_infor
drop view view_infor

--视图的更新
update view_infor
set price=price+100
where book_id=1

--==========================================================
--视图的插入

alter view view_infor (book_id,book_name,price,外间)
as 
select book_id,book_name,price,pub_id
from 图书信息表


insert into view_infor 
values(7,'.NET 程序设计',100,3)

--=============================================
select * from 图书信息表

create table 产品销售
(
  [产品 ID]     smallint primary key not null,
  产品名称    varchar(40) not null,
  售价      money not null,
  [制造商 ID]  smallint not null
)

create table 制造商
(
  [制造商 ID] smallint primary key not null,
  制造商名称 varchar(20),
  联络人 char(16),
  地址  varchar(40),
  电话  char(16),
  传真  char(16)
)

drop table 制造商
drop table 产品销售

insert into 产品销售 values(1,'电脑',1200,1)
insert into 产品销售 values(2,'复印机',2400,1)
insert into 产品销售 values(3,'显示器',1660,2)
insert into 产品销售 values(4,'数码相机',3450,2)
insert into 产品销售 values(5,'打印机',1340,4)
insert into 产品销售 values(6,'扫描仪',2600,4)

insert into 制造商 values(1,'新兴科技公司','王先生','北京市大兴区林校路8号','010-12345678','010-987654321')
insert into 制造商 values(2,'亨通科技公司','张先生','北京市海淀区北大路12号','010-23456791','010-69874565')
insert into 制造商 values(3,'新天地电子有限公司','付先生','北京市东城区文附路34号','010-45698715','010-32698745')
insert into 制造商 values(4,'王牌计算机公司','陶先生','北京市朝阳区国际路8号','010-56987456','010-45987456')
insert into 制造商 values(5,'胜利科技有限公司','李先生','天津市南开区安鞍山西道9号','022-12369856','022-45987456')
insert into 制造商 values(6,'超音速计算机公司','赵先生','天津市红桥区西园路6号','022-56987455','022-78459564')

select * from 产品销售
select * from 制造商 

select * 
from 产品销售,制造商 
where 产品销售.[制造商 ID] = 制造商.[制造商 ID]
--group by  产品名称,制造商名称
--with cube
order by 制造商名称
compute sum(售价) by 制造商名称

---视图中oder by 子句必须与 top 联合使用-----------------------------------------创建视图--------------------------------------------------------
--如果创建视图时　查询有 cumpute...by　则视图不能创建
create view 产品查询 
as
select top 2 产品名称,制造商名称
from 产品销售,制造商 
where 产品销售.[制造商 ID] = 制造商.[制造商 ID]
--group by  产品名称,制造商名称
--with cube
--order by 制造商名称
--compute sum(售价) by 制造商名称
go
--=============================================
select * from 产品查询

select *
from 产品销售 



drop view 产品查询
select * from 产品查询 
--===================================================
update 产品查询
set 售价=售价 + 200
where 产品名称='电脑'

--insert into 产品查询 values(
--====================================================
drop view 产品查询   -- 删除视图
-------------------------------------视图查询-------------------------------------------------------
select 产品名称 as 贫民 from 产品查询



/*
  视图优点：
    1.增加可读性。
    2.数据安全及保密。
    3.降低查询的复杂度。
    4.方便程序维护。
*/


/* 创建视图语句中，不可使用select into / order by / compute /compute by.

create view MyView
as
select  mycol1,mycol2 from mytable order by mycol2   -- 错误写法

select * from MyView order by mycol2    -- 正确写法
*/
----------------------------------------------------------------------------指定视图字段别名--------------------
alter view 产品查询 (品名,老板,大老板)   -- 指定字段别名
with encryption  --对视图加密
as
select 产品名称,制造商名称,联络人
from 产品销售,制造商 
where 产品销售.[制造商 ID1] = 制造商.[制造商 ID]
--compute 售价
--order by 制造商名称
go


select * from 产品查询



order by 定价 desc

drop view 产品查询
-------------------------------------------------------------------------------视图加密---------------------------

create view 产品查询_1 
with encryption
as
select 产品名称,售价,制造商名称 
from 产品销售,制造商 
where 产品销售.[制造商 ID] = 制造商.[制造商 ID]

select * from 产品查询_1


-----------------------------------修改视图-----------------------------

alter view 产品查询_1 (品名,定价,长牌)
as
select 产品名称,售价,制造商名称 
from 产品销售,制造商 
where 产品销售.[制造商 ID] = 制造商.[制造商 ID]



drop view 产品查询_1   -- 删除视图

--在视图中用order by

select * from sysobjects order by name


create view v_test
as
--select * from sysobjects order by name
select top 100 percent * from sysobjects order by name

select * from v_test