SqlServer 基础知识大整理(强烈推荐之四)

 

--索引是对数据库表中一个或多个列的值进行排序的结构
--索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针
--sysindexes 系统表存在于每个数据库当中，表中的 indid 字段表示索引ID，
--当它 =1时为聚集索引,>1时为非聚集索引，=0时说明没有索引，=255时 具有 text 或 image 数据的表条目

--当给表创建主键时，自动在该列上创建聚集的唯一索引，索引名称与主键名称相同。

drop table emp

create table emp(eid int constraint pk primary key,ename varchar(10))

select *from sysindexes where name='pk'  --name的值：如果有索引则用索引名称，没有索引则用表的名字

--当给表添加唯一约束时，自动在该列上创建非聚集的唯一索引   

create table emp(eid int not null constraint uq unique,ename varchar(10))

select *from sysindexes where id=object_id('emp')

sp_helpindex 'emp'    --查询指定表的索引创建在哪些列上

--只有在表上创建主键或唯一约束才可以影响索引
--适合创建索引的列：1。该列频繁使用进行搜索 2。该列用于对数据进行排序
--不适合创建索引的列：1。列中仅有几个不同的值（低基数列）

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]  --创建索引的语法，[]中为可选参数，默认为非聚集。
 INDEX index_name
     ON table_name ( column_name [ ,column_name ]... )

create table emp(eid int not null,ename varchar(10))

create index in_name on emp(ename)  --该语句创建了非聚集非唯一的索引
      --聚集索引不一定唯一，但如果在 CLUSTERED 前加上 unique 约会则为唯一了

--FREETEXT是个谓词，用于搜索含有基于字符的数据类型的列，其中的值符合在搜索条件中所指定文本的含义，
--但不符合表达方式。使用 FREETEXT 时，全文查询引擎内部将 freetext_string 拆分为若干个搜索词，
--并赋予每个词以不同的加权，然后查找匹配。

--语法
FREETEXT ( { column | * } , 'freetext_string' )

--1。全文索引存放在全文目录中
--2。当全文索引的表内容变化的时候，全文目录必须手动更新！（选完全填充）

--示例
--使用 FREETEXT 搜索包含指定字符值的单词
--下例搜索产品描述中含有与 bread、candy、dry 和 meat 相关的词语的所有产品类别，如 breads、candies、dried 和 meats 等。

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )
GO

--CONTAINS一个谓词，用于搜索包含基于字符的数据类型的列，该列与单个词和短语，
--以及与另一个词一定范围之内的近似词精确或模糊（不太精确的）匹配或者加权匹配。

--语法
CONTAINS ( { column | * } , '< contains_search_condition >')
   
--示例
--使用带有 <simple_term> 的 CONTAINS
--下面的示例查找包含词"bottles"且价格为 $15.00 的所有产品。

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
   AND CONTAINS(QuantityPerUnit, 'bottles')
GO

--视图：是一张虚拟表，其内容由查询定义。同真实的表一样，试图包含一系列带有名称的列与行数据。
--但是视图并不在数据库中以存储的数据值集的形式存在。行与列数据来自由定义视图的查询所用的表，
--并且在引用视图时动态生成。
--视图中不保存数据，但是在符合一定条件的前提下可以通过视图对基表的数据进行更新，录入，删除，查询。

create table dept(did int primary key,dname varchar(10))

insert into dept select 1001,'财务部'
union select 1002,'行政部'
union select 1003,'人事部'
union select 1004,'市场部'

drop table dept

drop view a

create view a   --创建视图的语法中必须有 AS 关键字
as    
select *from dept  --* 表示所有的列

--A.当视图中包含了基表所有的非空列的时候:
--1。通过视图向基表进行数据录入

insert into a values(1005,'组织部')

select *from dept

--2。更新数据

update a set dname='accp' where did=1005

--3。删除数据

delete from a where did=1005

--B.当创建的视图没有全部包含基表所有的非空列的时候
--1。录入数据将失败

alter view a    --修改视图的语法
as
select dname from dept

insert into a values('组织部')  --这条语句将不能执行！

--2。删除数据（当指定视图中部分不包含的列的时候，删除失败；当指定视图中包含的列的时候，删除成功）

delete from a where dname='行政部'   

--3。更新数据（同上述要求）

update a set dname='accp' where dname='市场部'

--4。查询数据(同上要求)

select * from a

alter view a   --修改视图
as
select dname from dept
where did<1003   --指定视图中只包含did<1003的部门

select *from a

--A.当创建的视图中带有where条件的时候（视图中不包含基表中所有的非空列的情况）
--录入，删除，更新，查询数据的时候不能指定视图中不存在但是基表中存在的列

--B.当视图中包含基表所有非空列时，如带有where条件

alter view a
as
select *from dept  --用*表示包含了基表中所有非空列
where did<1003

--1。录入数据（在创建视图时所带的where条件对视图录入数据不起作用，数据会录入到基表中的）

insert into a values(1005,'accp')

--可以在创建视图时加上 with check option 使where在录入数据时也起作用

alter view a
as
select *from dept
where did<1003
with check option

--2。删除数据与更新数据时where条件起作用

delete from a where did=1006

update a set dname='bccp' where did=1006

select *from dept

--创建多表连接视图

create table emp
(eid int primary key,ename varchar(10),sal money default 3456,dno int references dept(did))

insert into emp values(1,'rose',default,1001)
insert into emp values(2,'jack',3,1002)
insert into emp values(3,'tom',default,1003)
insert into emp values(4,'mike',1234,1004)

drop table emp

select *from emp

create view a
as
select *from emp,dept
where emp.dno=dept.did

select *from a

--在多表视图中录入数据，当修改会影响到多个基表时，录入数据会失败。

insert into a values(5,'ekin',999,1005,1005,'bccp')

--修改不影响多个基表时，录入数据成功

insert into a(did,dname) values(1005,'cccp')

--删除，更新数据道理同上

--查询数据不受限制。

--游标语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

 

--全局变量 @@FETCH_STATUS 返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标状态
--它的值 =0 时，语句成功； =-1 时，语句失败或此行不在结果集中；=-2 时被提取的行不存在。

--SCROLL关键字：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。
--如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。
--如果指定 SCROLL，则不能也指定 FAST_FORWARD。

--select_statement是定义游标结果集的标准 SELECT 语句。
--在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

--游标过程举例

declare cr scroll cursor  --游标的声明
for
select *from emp   
open cr     --打开游标
fetch last from cr   --游标的推进
close cr    --关闭游标
deallocate cr    --释放游标

select *from emp where 1>2
select @@error    --用 @@error 判断sql语句是否正确执行

select @@rowcount   --受上一sql语句影响的函数。它的值若为-则表示不返回任何行

declare employee_cursor cursor
for
select lastname,firstname
from employees for update of lastname
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
update employees set firstname='jack' where 1>2
end
close employee_cursor
deallocate employee_cursor

--以上程序为游标应用的更新。从上往下的第四行表示只能更新lastname.
--(实际此例并不是真正通过游标进行更新的！)

begin tran
declare employee_cursor cursor
for
select lastname,firstname
from employees for update of lastname
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
update employees set firstname='jack'
where current of employee_cursor  --通过这个where来实现真正的游标更新
end
close employee_cursor
deallocate employee_cursor
rollback

declare employee_cursor cursor
for
select lastname,firstname
from employees
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
delete from employees    --通过游标删除数据
where current of employee_cursor
end
close employee_cursor
deallocate employee_cursor

select *from employees

-----------------------------------------------------------------------------------------------------

--存储过程

create proc p1    --这程序本意是让@a1与@a2的值进行交换，
@a int,@b int    --但结果并非如此，因为向过程传递参数时用的是值传递。
as
begin
declare @temp int
set @temp=@a
set @a=@b
set @b=@temp
end

declare @a1 int,@a2 int
set @a1=1
set @a2=2
EXEC p1 @a1,@a2
print @a1
print @a2

alter proc p1    --修改一个过程
@a int output,@b int output  --用output可以实现按地址传递
as
begin
declare @temp int
set @temp=@a
set @a=@b
set @b=@temp
end

declare @a1 int,@a2 int
set @a1=1
set @a2=2
EXEC p1 @a1 output,@a2 output  --向过程传递参数时也要用output
print @a1
print @a2

alter proc p1
@a int,@b int    --一个过程的返回值不能通过参数列表看出来
as
begin
if(@a>@b) return 0   --用return表示返回值，该返回值描述一种状态
return -1
end

declare @res int   --变量@res用来保存返回值
EXEC @res=p1 1,3
print @res

alter proc p1    --结合表的存储过程
as
begin
declare @tab table(eid int)
insert into @tab select eid from emp
select * from @tab
end

exec p1

alter proc p1
@a int=90,@b int   --存储过程参数有默认值的情况
as
begin
declare @c int
select @c=@a+@b
print @c
end         --参数有默认值时传递参数有以下两种方式
    
exec p1 default,10   --1。使用 fefault 传递 

exec p1 @b=10    --2。使用定义时的参数名称传递

------------------------------------------------------------------------------------------------------------]

--触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。

create trigger tr   --tr为触发器名称
on emp for insert   --insert 为指定的触发动作，其他动作还有：delete,update，多个动作之间用逗号分隔
as
begin     --begin 与 and 之间为触发器语句主题，其内部可以用事务控制语句
print '不能录入数据！'
rollback
end     --同一个动作可以定义多个触发器，但其执行的次序是随机的

insert into emp select 1001,'rose',2005 --用这条语句录入数据时触发器只触发一次 ，insert触发器触发几次要看有多少insert语句
 union select 1002,'jack',2006

--逻辑表 inserted介绍
     
--录入数据时，数据首先会被存放在inserted逻辑表中，接着等待用户回退或提交的命令，
--如果用户回退，则清空该表数据；如果用户提交，则将该表数据永远写入数据库

--逻辑表结构与触发器结构完全相同，存在于内存中。逻辑表主要用来做数据的审计。

select *into audit_emp from emp where 1>2   --audit_emp表为用来审计的表
alter table audit_emp add descr varchar(20) default '录入数据'

alter trigger tr
on emp for insert
as
begin
print '录入数据'
insert into audit_emp(eid,ename,sal) select *from inserted 
end

--逻辑表 deleted 介绍

--结构和触发器表的结构完全相同，当删除数据的时候该数据首先会被放在deleted逻辑表中，接着等待用户提交或回退命令，
--如果提交则清空该表数据，如果用户回退，则把该表数据放回原处，以保证了数据的一致性

--删除动作不涉及 inserted，录入动作也不涉及 deleted 。

--update 动作发生的时候，历史数据首先会被放在deleted表中，新数据会被放在inserted表中，接着等待用户提交或回退命令。
--如果用户提交，则新数据从inserted表中录入，并同时清空deleted表；如果回退，则将的deleted表中数据恢复，同时清空inserted表。