SQL 数据库 创建视图

--创建视图
exec sp_help sysobjects
use [4S_Auto] 
go
exec sp_help Customer_Car
if exists(select * from sysobjects where name='v_car')
drop view v_car
go
create view v_car
as
select Car_ID,Car_Type,Car_Displacement,Car_Mil,Car_BuyTime
from Customer_Car 
--视图创建成功
select * from sysobjects
where name='v_car'

wap.bjqfhy.cn
exec sp_help sysobjects
exec sp_help Customer_Car
--访问视图
select * from v_car


if exists(select * from sysobjects where name='v_car_2')
drop view v_car_2
go
create view v_car_2
as
select Car_ID,Customer_ID
from Customer_Car 
--访问视图2
select * from v_car_2 


exec sp_helpconstraint Customer_Car
exec sp_help Customer_Info
exec sp_helpconstraint Customer_Info


insert into Customer_Info(Name,Sex,Identity_ID,Phone,Office_Phone,Email)
values('a','女','8349382948239','345332','353','324532432');
select * from Customer_Info 


exec sp_help Customer_Car
insert into Customer_Car(Car_ID,Customer_ID,Car_Type,Car_Displacement,Car_BuyTime,Car_Mil)
values('a9',9,'good',453,'2012.3.4 12:30',45);


--修改视图，基表数据变化
update v_car set Car_Type='best' where Car_ID='a9'


select * from Customer_Car 

wap.bjqfhy.cn
wap.sxcsgm.cn

--添加视图信息，基表数据变化
insert into v_car(Car_ID,Car_Type,Car_Displacement,Car_Mil,Car_BuyTime)values('a10','good',343,45,'2012.4.5 13:00')
--有错误
ms.zjgxfsl.com
dz.zjgxfsl.com
--插入10万条记录
go
alter proc p_insert_rows
as
set nocount on
set IMPLICIT_TRANSACTIONS ON
declare @i int,@cnt int,@d datetime
select @d=getdate(),@i=1,@cnt=100000
while(@i<=@cnt)
begin
insert into tstuTest
values(@i,left(convert(varchar(40),newid()),10),@i%2,convert(varchar(40),newid()),rand()*100,@d-@i%1000)
set @i=@i+1
if(@i%100=0)
commit tran
end
set IMPLICIT_TRANSACTIONS off
commit tran
go


truncate table tstuTest
exec p_insert_rows


use [4S_Auto] 
go
create table tstuTest
(
[a]int not null,
[b]varchar not null,
[c]int not null,
[d]varchar not null,
[e]int not null,
[f]int not null
)
alter table tstuTest alter column f datetime not null
alter table tstuTest alter column b varchar(40)not null
alter table tstuTest alter column d varchar(40)not null
exec sp_help tstuTest
4g.cdyy028.com
m.hrbmjj.cn
m.sxcsgm.cn


select COUNT(*)
from tstuTest 
ya.zjgxfsl.com
zg.zjgxfsl.com
select *
from tstuTest 
where a between 3456 and 20000