课堂案例.txt

主要介绍了sql的基础教程

视图与索引案例

----创建一个简单的视图
use pubs
go

---创建视图
create view titles_view
as 
select title_id,title,type,price
from titles

----视图定义好后，就可以象普通表格一样使用
select * from titles_view
go

select title,type,price
from titles_view
go

视图并不存储数据，只是存储了一个查询语句
exec sp_helptext 'titles_view'
----------------------------------------------------------------------------------------------------------------------

-----创建一个视图同时给视图加密
create view authors_view With encryption 
as  
select au_id,au_lname,au_fname,contract
from authors
go

exec sp_helptext 'authors_view'
go
----------------------------------------------------------------------------------------------------------------------

----修改视图
alter view authors_view
as  
  select au_id,au_lname,au_fname,contract,city
  from authors
go

exec sp_helptext 'authors_view'
go
----------------------------------------------------------------------------------------------------------------------

-----修改视图并给视图加密
alter view authors_view With encryption
as  
  select au_id,au_lname,au_fname,contract
  from authors
go

exec sp_helptext 'authors_view'
go
----------------------------------------------------------------------------------------------------------------------

----[with check option]选项使用示例
create view titlepri_view
as
select title_id,title,type,price
from titles where price >10
go

exec sp_helptext 'titlepri_view'
go

select * from titlepri_view
go

update titlepri_view 
set price=1
where title_id='pc8888'
go


alter view titlepri_view
as
select title_id,title,type,price
from titles where price >10
with check option
go

exec sp_helptext 'titlepri_view'
go

select * from titlepri_view 
go

update titlepri_view 
set price=4
where title_id='PS1372'
go
----------------------------------------------------------------------------------------------------------------------

----删除视图
drop view titles_view
go
----------------------------------------------------------------------------------------------------------------------

----基于视图创建视图
select * from authors_view
go
create view tt_view
as 
select au_lname,au_fname
from authors_view
go

exec sp_helptext 'tt_view'
go
----------------------------------------------------------------------------------------------------------------------
-----如果一个视图是基于多表创建的，在更新视图时一次只能更新一张表中的字段的值
创建student表备用
create table student
(
 sid varchar(20) primary key,
 cname varchar(20),
)
go

---创建course表备用
create table course
(
 sid varchar(20) ,
 courseid int ,
 iscore numeric(5,2)
)
go

---在表中插入数据备用
insert into student values('A1001','john')
insert into student values('A1002','marry')
insert into student values('A1003','linda')
insert into course values('A1001',1001,62)
insert into course values('A1001',1005,72)
insert into course values('A1003',1005,96)

select * from student
select * from course

---创建视图，以student和course为基表
create view vwstudentscore
 As 
  select  a.sid,a.cname,b.courseid,b.iscore
  from student a inner join course b
  on a.sid=b.sid

更新视图，更新来自于基表student的name字段和来自于course表的iscore字段的值
Update vwstudentscore
set cname='martin',iscore=iscore+5
Where sid='A0001'
----------------------------------------------------------------------------------------------------------------------
---创建一个简单的索引
---创建索引
---创建一个表备用
create table students
(
 stu_id int,
 name varchar(20),
 sex char(2),
 age int
)
go

----在表中插入一些数据备用
insert into students values (1005,'张三','男',20)
insert into students values (1001,'李四','男',22)
insert into students values (1003,'王五','女',20)
insert into students values (1002,'赵六','男',19)
insert into students values (1004,'钱敏','女',23)
go

----查看一下在创建索引之前表中的数据
select * from students
go
----------------------------------------------------------------------------------------------------------------------

----创建一个非聚集索引
create index stuid_index on students (stu_id )
go

----查看一下在非聚集索引之后表中数据的变化
select * from students
go
----------------------------------------------------------------------------------------------------------------------

----创建一个聚集索引
create clustered index stuid_index on students (stu_id )
go

---查看一下在聚集索引之后表中数据的变化
select * from students
go
----------------------------------------------------------------------------------------------------------------------

----创建索引，并设置填充因子
use pubs
go

create index titles_idx
on titles (title)
with pad_index,fillfactor = 20
go
----------------------------------------------------------------------------------------------------------------------
----在查询中强制使用索引示例
use pubs
go

create index nclindx_ordnum
on sales(ord_num)
go

select * from sales (index =nclindx_ordnum)  
where ord_num = 'p3087a' 
go
----重建索引以优化索引结构
dbcc dbreindex (students, '', 70)
----------------------------------------------------------------------------------------------------------------------