解析SQLServer視圖、數(shù)據(jù)庫快照_MySQL

if exists(select * from sysobjects where name = 'newView') --如果存在刪除
 drop view newView
go
create view newView --創(chuàng)建視圖
as
select 學(xué)號=id,姓名=name,年齡=age from student
go
select * from newView

if exists(select * from sysobjects where name = 'newView') --如果存在刪除
 drop view newView
go
create view newView --創(chuàng)建視圖
as
select top 10 學(xué)號=id,姓名=name,年齡=age from student
order by id desc --如果使用order關(guān)鍵字 則必須指定top
go
select * from newView

alter view newView
as
select top 20 * from student
order by id desc --如果使用order關(guān)鍵字 則必須指定top
go

--剛剛修改的視圖用的是 * 查詢的是全部列
--此時(shí)修改基礎(chǔ)表架構(gòu)
alter table student add mail varchar(20)

--這時(shí)源表已經(jīng)更新架構(gòu)，但是視圖并沒有改變 需要調(diào)用系統(tǒng)存儲(chǔ)過程更新一下
EXEC sp_refreshview studentView
--這時(shí) 新的列才會(huì)被加進(jìn)來

--源表中數(shù)據(jù)
use webDB
go
select * from student

if exists(select * from sys.databases where name = 'webDB_DBSS')
drop database webDB_DBSS --存在則刪除
go
create database webDB_DBSS --創(chuàng)建數(shù)據(jù)庫快照
on
(
 name = webDB,
 filename='d:\webDB_DBSS.ss' --注意后綴.ss
) --如果有多個(gè)文件需要一一指出
as snapshot of webDB
go

--修改源數(shù)據(jù)庫數(shù)據(jù)
use webDB
go
update student set name='偉大' where id = 10 

--查詢源數(shù)據(jù)庫數(shù)據(jù)
select * from student where id = 10 

--查詢快照數(shù)據(jù)
use webDB_DBSS
go
select * from student where id = 10 

--修改student表架構(gòu)
alter table webDB.dbo.student add [address] varchar(20)
select * from webDB.dbo.student

--快照中依然沒有
select * from webDB_DBSS.dbo.student 

--刪除源數(shù)據(jù)庫student表
drop table webDB.dbo.newTable 

--查詢數(shù)據(jù)庫快照該表 依然存在
select * from webDB_DBSS.dbo.newTable

restore database webDB
from database_snapshot='webDB_DBSS'
go

select * from webDB.dbo.student --修改的值被改回

select * from webDb.dbo.newTable --被刪除的表 改回