五、视图和索引

一，视图
1>,新建表
create table index1
(
STID int not null,
name1 varchar(10),
score int
)

2>，向表中插入数据
declare @a int
set @a=1
while (@a<10)
begin
insert into index1 values(@a,'clm',100-@a)
set @a=@a+1
end

一、简单视图
1,新建视图
create view view1 as select * from index1
2，从视图中查询
select * from view1

3，在表中插入一条新记录
insert into index1 values(23,'slc',77)

4，从视中查询，所得结果已经改变，说明视图每次执行都是重新在表中检索记录
select * from view1

5,在视图中插入一条新记录，成功（多表视图不成立，为什么？）
insert into view1 values(23,'slc',77）

6,从表中查询，结果改变，说明可以通过更改视图来更改表的记录
select * from index1

7，从视图中删除记录，结论同上
delete from view1 where score=99

select * from index1

8,从视图中更新记录，结果同一
update view1 set name1='wyl'where score=98

9,新建视图可以 order by ，但必须和top n一起使用，为什么？
create view view4 as select top 3 * from index1 order by stid desc
select * from index1
9，显示前三条的视图
create view view2 as select top 3 * from index1

select * from view2
10,又一次说明视图实时更新
insert into view1 values(23,'slc',99)

select * from view2

11，创建视图不能和INTO连用
create view view5 as select  * into index1 from index1

12,删除视图
drop view view4

二、高级视图
1, 使用 WITH ENCRYPTION
下例使用 WITH ENCRYPTION 选项并显示计算列、重命名列以及多列。

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'accounts')
   DROP VIEW accounts
GO
CREATE VIEW accounts (title, advance, amt_due)
WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $5
GO

2. 使用 WITH CHECK OPTION
下例显示名为 CAonly 的视图，该视图使得只对加利福尼亚州的作者应用数据修改。

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'CAonly')
   DROP VIEW CAonly
GO
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTION
GO

3.在视图中使用内置函数
下例显示包含内置函数的视图定义。使用函数时，必须在 CREATE VIEW 语句中为派生列指定列名。

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'categories')
   DROP VIEW categories
GO
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO

4 在视图中使用 @@ROWCOUNT 函数
下例使用 @@ROWCOUNT 函数作为视图定义的一部分。

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'myview')
   DROP VIEW myview
GO
CREATE VIEW myview
AS
   SELECT au_lname, au_fname, @@ROWCOUNT AS bar
   FROM authors
   WHERE state = 'UT'
GO
SELECT *
FROM myview

5. 使用分区数据
下例使用名为 SUPPLY1、SUPPLY2、SUPPLY3 和 SUPPLY4 的表，这些表对应于位于不同国家的四个办事处的供应商表。

--create the tables and insert the values
CREATE TABLE SUPPLY1 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY2 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY3 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY4 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
   supplier CHAR(50)
   )
INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp')
INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd')
INSERT SUPPLY2 VALUES ('231', 'FarEast')
INSERT SUPPLY2 VALUES ('280', 'NZ')
INSERT SUPPLY3 VALUES ('321', 'EuroGroup')
INSERT SUPPLY3 VALUES ('442', 'UKArchip')
INSERT SUPPLY4 VALUES ('475', 'India')
INSERT SUPPLY4 VALUES ('521', 'Afrique')

--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
   UNION ALL
SELECT *
FROM SUPPLY2
   UNION ALL
SELECT *
FROM SUPPLY3
   UNION ALL
SELECT *
FROM SUPPLY4
二，索引
1,新建表
create table index1
(
STID int not null,
name1 varchar(10),
score int
)

2，向表中插入数据
declare @a int
set @a=1
while (@a<10)
begin
insert into index1 values(@a,'clm',100-@a)
set @a=@a+1
end

3,在表的score列上建立索引
create index indexscore on index1 (score)

4，查看表的索引信息
sp_helpindex index1

5,在表的STID列上建立索引
create index indextstid on index1 (STID)

6,带索引查询
SELECT * FROM INDEX1(index=indextstid)

7，创建聚集索引
//注：主键和聚集索引无关，但在创建主键时，如果没有聚集索引会在此字段上创建。
//对表中的记录进行重排
create clustered index indexscore on index1(score)

8，一个表只能创建一个聚集索引
//默认创建的索引按升序，此处为降序
create clustered index indexname on index1 (name1 desc)

9，组合索引，此处只有SCORE字段为降序

create  index indexname4 on index1 (name1,score desc)

10,一个字段上可以建立多个索引。

create  index indexname5 on index1 (name1,score desc)

11，唯一索引，此字段不再允许有重复值

create  unique index indexname6 on index1 (name1,score desc)

insert into index1 values(9,'clm',91)

12，添充因子
create index bb on index1(score)
WITH PAD_INDEX,FILLFACTOR = 100

13，全文索引

contains谓词
//等价于LIKE谓词，但比LIKE有更强的查询能力

select [Description]
from Categories
where contains([Description],' "bean curd" ')
or
select [Description]
from Categories
where  [Description] like ' %bean curd% ')

freetext谓词
//p95
select [Description]
from Categories
where freetext([Description],' sweetest candy bread and dry meat ')

在全文检索中使用变量

declare @aa varchar(30)
set @aa='fish'
select [Description]
from Categories
where freetext([Description],@aa)