在SQL二〇〇五中使用索引视图来抓牢查询作用,

作者:新京葡娱乐场388官网

目录

总所周知,视图是很方便查询数据的,其易维护性,十分被大家看好,可是效率问题一直让人担忧
在SQL2005中,可以使用索引视图,达到两全其美的办法,索引视图就是具有唯一的聚集索引的视图,通过这样的视图查询数据库,效率将是普通视图的10倍以上
在这里 我就向大家介绍一下这个索引视图的建立和使用方法

基本的sql语句

  1. 创建数据库:CREATE DATABASE database-name
  2. 删除数据库:drop database dbname
  3. 创建表:create table tabname(字段属性)
  4. 删除表:drop table tabname
  5. 增加列:Alter table tabname add column col type
  6. 添加/删除主键:Alter table taname add/drop(删除) primary key(列名)
  7. 创建索引:create [unique] index idxname on tabname(col….)
  8. 创建视图:create view viewname as select statement
  9. 查询:select * from table1 where 范围
  10. 插入:insert into table1(field1,field2) values(value1,value2)
  11. 删除:delete from table where 范围
  12.  更新:update table1 set field1=value1 where 范围
  13.  模糊查找:select * from table1 where field1 like ’%value%’
  14. 排序:select * from table order by field1,field2 [desc]
  15. 总数统计:select count(*) from table
  16.  求和:select sum(field) as sumvalue from table
  17. 平均:select avg(field) as avgvalue from table
  18. 最大最小:select max/min(field) as value from table
  19.  查询范围值: select * from table1 where time between time1 and time2
  20. UNION 运算符:请转表的关联查询
  21. EXCEPT 运算符:请转表的关联查询
  22. INTERSECT 运算符:请转表的关联查询
  23. 外连接:请转表的关联查询
  24. 分组:select age,SUM(age) from BaseTable GROUP BY age
  25. 复制表(只复制表结构): select * into b from a where 1<>1
  26. 复制数据: insert into b(列名) select 列名 from b;
  • 1.架构
    • 1.1.创建架构并在架构中创建表
    • 1.2.删除架构
    • 1.3.修改表的架构
  • 2.视图
    • 2.1.新建视图
    • 2.2.使用视图修改数据
    • 2.3.删除视图
  • 3.索引
    • 3.1.聚集索引
    • 3.2.非聚集索引
    • 3.3.创建索引
    • 3.4.修改索引
    • 3.5.查看索引
    • 3.6.查看索引碎片
    • 3.7.查看统计信息

首先,建立两个表格 一个表格是品牌表 一个是产品表 我相信大家一定也做过类似的案例!

索引

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

1.架构

架构是一种独立于用户的逻辑分组,组中可以存储表,视图,存储过程等。假如表1在架构1中,表2在架构2中,用架构1的用户名登录时表2不可见。且未添加该架构的数据库不能被该架构的用户访问。

 

触发器

触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。

触发器分为after触发器,insert触发器, update触发器, delete触发器。

创建触发器语法:

create trigger tgr_name

on table_name

for  触发器类型

AS

数据库执行的语句

1.1.创建架构并在架构中创建表

执行如下语句

CREATE LOGIN hy WITH PASSWORD = '123456'
GO
--新建登录名
CREATE DATABASE schematest
GO
--新建数据库
USE schematest
GO
CREATE USER u_for_test FOR LOGIN hy
GO
CREATE SCHEMA dbo_Schema
go
--在schematest数据库下添加dbo_Schema
CREATE TABLE T1(id INT,NAME VARCHAR(20))
go

CREATE TABLE dbo_Schema.T2(Nid int,DD datetime)
go

GRANT SELECT ON SCHEMA :: dbo_Schema TO u_for_test;
--给u_for_test赋予SELECT权限
--重新使用hy登录即可。

用hy登录,打开未添加dbo_Schema架构的数据库,出现如下提示
新京葡娱乐场388官网 1
打开schematest数据库,展开表,dbo_Schema下的T2表可见,非dbo_Schema架构下的T1表不可见。
新京葡娱乐场388官网 2

  1. Create TABLE [dbo].[Brand](   
  2.  [ID] [int] IDENTITY(1,1) NOT NULL,   
  3.  [BrandName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Brand_BrandName]  DEFAULT (''),   
  4.  [Del] [bit] NOT NULL CONSTRAINT [DF_Brand_Del]  DEFAULT ((0)),   
  5.  CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED    
  6. (   
  7.  [ID] ASC  
  8. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
  9. ) ON [PRIMARY]    

触发器实例

在ado数据库创建BaseTable的插入触发器:

在向表BaseTable插入一条记录时,同时触发器会向Table表也插入一条数据作为记录触发器执行成功。

新京葡娱乐场388官网 3新京葡娱乐场388官网 4

ALTER TRIGGER [dbo].[tgr_name]

   ON  [dbo].[BaseTable]

   for INSERT

AS

BEGIN

    declare @name varchar(20),@id int;

    select @id = id, @name = name from inserted;

    set @name = @name   convert(varchar, @id);

    insert into [Ado].[dbo].[Table] values(@name, 18   @id);



END

View Code

1.2.删除架构

删除架构前必须删除或者移动该架构的所有对象,不然删除操作将会失败。如执行下列语句

DROP SCHEMA dbo_Schema
GO

结果如图所示
新京葡娱乐场388官网 5
此时要将T2表删除或者移动到其他架构才能成功删除dbo_Schema

Create TABLE [dbo].[Product](   

储存过程

存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。

储存过程的优点:存储过程允许标准组件式编程,存储过程能够实现较快的执行速度,存储过程减轻网络流量,存储过程可被作为一种安全机制来充分利用。

1.3.修改表的架构

如图所示,右键表名——设计——右侧属性栏中修改表的架构
新京葡娱乐场388官网 6
如图所示,当把T2表所引用的架构修改为dbo后,可继续删除架构dbo_Schema操作。就能成功删除dbo.Schema
新京葡娱乐场388官网 7

 [PID] [int] IDENTITY(1,1) NOT NULL,   

系统储存过程:

系统存储过程是系统创建的存储过程,系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程,有些也会在创建新数据库的时候自动创建在当前数据库。常用的系统储存过程有:

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助,查询数据库信息

储存过程的示例调用:

重命名表的名字:exec sp_rename 'tablename', tablename1;

2.视图

视图是数据库中原始数据的一种变换,是查看表数据的一种方式,视图是一种逻辑对象,是虚拟的表,是一串SELECT语句,并不是真实的表。

 [ProductName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Product_ProductName]  DEFAULT (''),   

用户自定义储存过程

创建储存过程的语法:

Create proc|procedure proc_name

(

   @参数名 类型,

   ….

)

As

Sql执行语句和一写判断处理

 

创建示例:

新京葡娱乐场388官网 8新京葡娱乐场388官网 9

USE [Ado]

GO

/****** Object:  StoredProcedure [dbo].[proc_BaseTable]    Script Date: 05/25/2016 17:23:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[proc_BaseTable](@name varchar(50),@age int)



AS

BEGIN

select * from BaseTable where name=@name

select * from BaseTable where age=@age return 10

select * from BaseTable where name=@name and age=@age 

END

View Code

2.1.新建视图

示例1:利用student表和class_student表的数据新建视图class_01,记录01班学生详细信息
Student表的数据如图所示
新京葡娱乐场388官网 10
Class_student表的数据如图所示
新京葡娱乐场388官网 11
执行下列语句新建视图class_01

CREATE VIEW class_01
AS
SELECT class_student.stu_no,class_id,stu_name,stu_sex,stu_age,stu_addr,stu_native_place,stu_birthday,stu_enter_score,stu_phone,stu_father_name,stu_mather_name
FROM class_student INNER JOIN student
ON class_student.stu_no=student.stu_no
WHERE class_id='01'

视图class_01的数据如图所示
新京葡娱乐场388官网 12

注:视图只是一个SELECT语句,数据根据基表的数据改变而自动改变。

 [ProductDes] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Product_ProductDes]  DEFAULT (''),   

视图

视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。

创建视图:create view view_BaseTable as select id,name from BaseTable

数据库文件有一个BaseTableView.Sql文件执行即是此示例。

2.2.使用视图修改数据

示例2:有course表数据,基于course表新建视图coursetest,列名为course_id,course_name,credits。
Course表数据如图所示
新京葡娱乐场388官网 13
执行下列语句新建coursetest视图

CREATE VIEW coursetest
AS
SELECT course.course_id,course_name,credits FROM course

Coursetest视图数据如图所示
新京葡娱乐场388官网 14
在coursetest视图中插入一行course_id为“0013”的数据

INSERT INTO coursetest(course_id,course_name,credits)
VALUES('0013','嵌入式系统开发','5')

Course表数据如图所示
新京葡娱乐场388官网 15
这行数据也被插入到course表中,在基于单张表的视图中可以通过增删改视图数据来更新基表数据,对基于多张表的视图不可更新。

 [BrandID] [int] NOT NULL CONSTRAINT [DF_Product_BrandID]  DEFAULT ((0)),   

约束

在此功能上我只在BaseTable表的age字段添加check约束使年龄不能超过50.

2.3.删除视图

DROP VIEW coursetest

 [hidden] [bit] NOT NULL CONSTRAINT [DF_Product_hidden]  DEFAULT ((0)),   

约束的属性介绍:

NOT NULL : 用于控制字段的内容一定不能为空(NULL)。

UNIQUE : 控件字段内容不能重复,一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复,但它在一个表只允许出现一个。

FOREIGN KEY: FOREIGN KEY 约束用于预防破坏表之间连接的动作,FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

DEFAULT: 用于设置新记录的默认值。

3.索引

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED    

示例:

NOT NULL :Create table MyTable 

               ( 

                   id varchar(32) not null, 

               name varchar (32) 
               ) 

UNIQUE:Create table MyTable 

               ( 

                   id varchar(32) not null UNIQUE, 

                   name varchar (32) 

               ) 

           Create table MyTable 

                ( 

                    id varchar(32) not null, 

                    name varchar (32), 

                    unique (id,.....) 

                 ) 

PRIMARY KEY :Create table MyTable 

               ( 

                   id varchar(32) not null PRIMARY KEY, 

                   name varchar (32) 

               ) 

Foreign Key :Create table MyTable 

               ( 

                            id nvarchar(32) not null primary key, 

                   name nvarchar(32), 

foreign key(id) references myTB(id) 

               ) 

Check  :Create table MyTable 

               ( 

                 id nvarchar(32) not null,

                   age int not null,

check (age>15 and age <30)

               ) 

Default:Create table MyTable 

               ( 

                 id int, 

name nvarchar(32) default 'celly'               

 ) 

3.1.聚集索引

聚集索引数据按照索引的顺序排序,查询速度比非聚集索引快。当插入数据时,按索引顺序对数据重新排序。打个比方,新华字典中按拼音查字就是聚集索引,找到了矮字就能按顺序查下去找到爱字。一个表只能有1个聚集索引
如果一个表在创建主键时没有聚集索引也没指定唯一非聚集索引,会对PRIMARY KEY字段自动创建聚集索引

(   

游标

游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。将批操作变成行操作,对结果集中得某行进行操作。

数据库文件BaseTableCsr.sql是游标示例。查询BaseTable表的id与name字段循环打印出来。

3.2.非聚集索引

非聚集索引不按照索引顺序排序,制定了表中数据的逻辑顺序,采用指针指向数据页的形式。一个表可以拥有多个非聚集索引。打个比方,新华字典中按笔画查字就是非聚集索引,笔画索引顺序和字的顺序不一致,依靠指针来指向数据页。

 [PID] ASC  

表的关联查询

3.3.创建索引

示例3:设置IndexDemo1表的id字段为PRIMARY KEY,看系统是否自动为该字段创建了聚集索引。执行下列语句

CREATE DATABASE IndexDemo
USE IndexDemo
CREATE TABLE IndexDemo1(
id INT NOT NULL,
A CHAR(10),
B VARCHAR(10),
CONSTRAINT PK_id PRIMARY KEY(id)
)

结果如图所示
新京葡娱乐场388官网 16
聚集索引以PRIMARY KEY的键名为索引名。
执行下列语句删除PRIMARY KEY

ALTER TABLE IndexDemo1
DROP CONSTRAINT PK_id

聚集索引PK_id也同时被删除了。
示例4:在示例3的IndexDemo1表中,插入几行数据,添加聚集索引,观察数据顺序,添加非聚集索引,观察数据顺序
IndexDemo1的数据如图所示(未添加索引)
新京葡娱乐场388官网 17
执行下列语句,为id列添加聚集索引

CREATE CLUSTERED INDEX clustered_index ON IndexDemo1(id)

添加聚集索引clustered_index后IndexDemo1表的数据如图所示
新京葡娱乐场388官网 18
可以发现,表中数据按照id列从小到大进行排序。
此时在表中插入一条数据

INSERT INTO IndexDemo1(id,A,B)VALUES('7','g','f')

新京葡娱乐场388官网,表中数据排序如图所示
新京葡娱乐场388官网 19
执行下列代码删除聚集索引clustered_index并对id列创建非聚集索引nonclustered_index

DROP INDEX IndexDemo1.clustered_index
GO--删除聚集索引clustered_index
CREATE NONCLUSTERED INDEX nonclustered_index ON IndexDemo1(id)
GO--创建非聚集索引nonclustered_index

表中的数据如图所示
新京葡娱乐场388官网 20
此时添加一条记录

INSERT INTO IndexDemo1(id,A,B)VALUES('8','g','f')

表中的数据如图所示
新京葡娱乐场388官网 21
在未创建聚集索引,创建了非聚集索引的表中新插入的数据是添加在末行的。

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]   

内连接:

只根据表查找符合添加的,不返回所有表数据。

示例代码:

select * from BaseTable join tabname on BaseTable.id=tabname.id

效果图

 新京葡娱乐场388官网 22

3.4.修改索引

当数据更改时,有必要重新生成索引,重新组织索引或者禁止索引。

  • 重新生成索引表示删除索引,并且重新创建索引。这样可以根据指定的填充度压缩页来删除碎片,回收磁盘空间,重新排序索引。
  • 重新组织索引对索引碎片的整理程度低于重新生成索引。
  • 禁止索引表示禁止用户访问索引。

示例5:对IndexDemo1表中的id列重新生成索引,重新组织索引和禁止索引。
执行下列语句

ALTER INDEX nonclustered_index ON IndexDemo1 REBUILD
--重新生成索引
ALTER INDEX nonclustered_index ON IndexDemo1 REORGANIZE
--重新组织索引
ALTER INDEX nonclustered_index ON IndexDemo1 DISABLE
--禁用索引

注:禁用索引后重新启用索引,只需重新生成索引就可以了。

) ON [PRIMARY]  

左连接查询:

以左表为主第二张表加入有符合条件的则显示数据,没有则第二张表全部为空

示例代码

select * from BaseTable left join tabname on BaseTable.id=tabname.id

效果图:

新京葡娱乐场388官网 23

3.5.查看索引

可以利用目录视图和系统函数查看索引。这样的函数有很多,不一一列举了。
新京葡娱乐场388官网 24

 

右链接查询:

以右表为主查找符合的数据,没有符合项左表显示为空

示例代码:

select * from BaseTable right join tabname on BaseTable.id=tabname.id

效果图:

 新京葡娱乐场388官网 25

3.6.查看索引碎片

右键索引名,在属性——碎片中查看碎片
新京葡娱乐场388官网 26

你可以使用语句在表中填充一些测试数据

完全外连接:

查找的表所有的数据合并成一张表进行显示

示例代码:

select * from BaseTable full join tabname on BaseTable.id=tabname.id

效果图(tabname表只有一条数据):

 新京葡娱乐场388官网 27

3.7.查看统计信息

在表下的统计信息中,右键点击要查看统计信息的索引名,点击详细信息
新京葡娱乐场388官网 28

表建立好以后 很明显是一对多的关系表 品牌对应产品 (其中Brand.ID = Product.BrandName) 按照以前的方法 可以这样获取数据

交叉连接:

没有where语句的交叉连接产生的是两张表行数的乘积数据,如果带where的结果与内连接的结果相同。

示例代码:

select * from BaseTable cross join tabname

效果图:

 新京葡娱乐场388官网 29

  1. 在Product表中也建立BrandName字段是速度最快的方法 但是缺点是造成数据冗余 不方便维护 例如修改品牌名的时候还要到产品表里做更新
  2. 第二个方法是列出产品的时候 依次按照产品表中的 BrandID 得到品牌表里的 BrandName字段 增加查询数据库次数
    这个时候就要应用到试图了 由于非索引视图每次都要到到两个表里去查询 速度很慢 所以现在我们开始建立索引视图
    这个例子建立索引视图的SQL代码如下

UNION运算:

分为UNION(检查重复)和UNION all(不检查重复)

检查重复示例代码:

select id from BaseTable union select id from  Tables

效果图:

 新京葡娱乐场388官网 30

不检查重复代码:

select id from BaseTable union all select id from  Tables

效果图:

 新京葡娱乐场388官网 31

 

EXCEPT运算:

从左查询中返回有右查询中没有找到的不重复项

示例代码:

select id from BaseTable except select id from tabname

效果图:

新京葡娱乐场388官网 32

SET ANSI_NULLS ON  

INTERSECT运算:

返回两个表都有的非重复项

示例代码:

select id From BaseTable intersect select id from tabname

效果图:

 新京葡娱乐场388官网 33

GO   

ADO 

SET QUOTED_IDENTIFIER ON  

连接字符串常用属性

1、    Data Source:数据源。计算机名称或者IP地址。

2、    Server:服务器。数据库所在计算机的名称

3、    Database:数据库名称。

4、    Initail Catalog:数据库的名称。

5、    User ID:用于连接数据库的用户名称。

6、    Password:用于连接数据库的用户密码。

7、    Pooling:标志是否使用数据库连接池(少客户访问启用可以提高性能)。

8、    Intergrated Security:系统集成安全验证。标志登录数据库时是否使用系统集成验证。

9、    Connection Timeout:连接超时的时间。系统再次尝试连接数据库时所经历的时间,单位为秒,默认值为15秒。

GO   

在程序中的事务操作

首先创建事物:SqlTransaction st = conn.BeginTransaction();

开始事物(即事物的赋值):cmd.Transaction = st;

提交事务:st.Commit();

回滚事务:st.Rollback();

假如要创建某个事物保存点可以使用:st.save();

根据事物操作可以做事物回滚的批量插入,修改操作,有一条不正确即可实现事物回滚,具体请看dome代码详细介绍。

  

调用储存过程

使用在数据库创建的储存过程在dome中通过ado进行调用,并得到返回的表。

示例代码:

 新京葡娱乐场388官网 34

 

注意点:在调用储存过程中,加入你的存储过程涉及到大量的操作,查询什么的,而你只需要返回其中的某个一条查询记录,需要在储存过程中找到需要返回的语句加上return ,这样在程序中调用只会返回本条语句结果。

Create VIEW [dbo].[ProductList] WITH SCHEMABINDING   

AS  

Select dbo.Brand.BrandName, dbo.Product.ProductName, dbo.Product.PID FROM dbo.Brand INNER JOIN dbo.Product ON dbo.Brand.ID = dbo.Product.BrandID where dbo.Product.Hidden = 0   

GO   

  

SET ANSI_NULLS OFF  

GO   

SET QUOTED_IDENTIFIER OFF  

GO   

 

建立索引视图之前 视图必须要和数据库绑定 所以我们使用使用WITH SCHEMABINDING 参数建立视图(注意当视图更改的时候 需要对该视图重新绑定操作)
下一步我们为这个视图建立一个唯一的聚集索引 取名为 PK_ProductID  添加字段PID为索引键列(注意当视图更改的时候 索引将消失)
如果你还经常使用Where筛选条件或者是排序的话 也可以再建立几个多个字段的非聚集索引
建立索引的代码如下:  

Create UNIQUE CLUSTERED INDEX [PK_ProductID] ON [dbo].[ProductList]    

(   

    [PID] ASC  

)WITH (SORT_IN_TEMPDB = OFF, Drop_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 

然后我们就来使用这个索引视图

select BrandName, ProductName    

from ProductList WITH (NOEXPAND) order by PID desc  

其中的NOEXPAND参数可以让你的查询速度如同在一个基础表上一般飞快! 当您的查询够复杂 数据量百万级 而你有建立了合理的索引就会体验到索引视图的飞快了

你可以通过如下语句来查看视图中索引的使用情况

Select (select name from sys.sysindexes where id = object_id and indid = index_id) as indexname, * FROM sys.dm_db_index_usage_stats where object_name(object_id)='ProductList'    

本文由www.204.net发布,转载请注明来源

关键词: www.204.net