数据库学习笔记（SQL Server）

 1. 数据库还原

EXEC sp_attach_db 'Assert',
 'D:/Program Files/Microsoft SQL Server/MSSQL/Data/Asset_data.mdf',
 'D:/Program Files/Microsoft SQL Server/MSSQL/Data/asset_log.ldf'

 

2.创建存储过程

Drop procedure hello_test
GO

CREATE PROCEDURE hello_test  AS
Select 'Hello word!'
--Select * from Inventor
return 0
GO

 

说明：Drop删除一个存储过程，且更改该过程的属性；为了保留原属性，提供

 

Alter PROCEDURE hello_test  AS
Select 'Hello word!'
--Select * from Inventor
return 0
GO 

 

3. 更改一条存储过程，为了防止已经存在的情况

if exists(select * from sysobjects
where id=object_id('hello_tset')
and OBJECTPROPERTY(id,'IsProcedure')=1)     -------判断语句

Drop procedure hello_test  .---------Drop或Alter
GO

 

CREATE PROCEDURE hello_test  AS
Select 'Hello word!'
--Select * from Inventor
return 0
GO

 

4.含有参数的存储过程

CREATE PROCEDURE aa
@a int         ---一个参数    
 AS
select 'aa'
-- from
GO

 

CREATE PROCEDURE aa
@a int,        ---多个参数 

@b int    
 AS
select 'aa'
-- from

 

5.存储过程输出值方式1：select

 

CREATE procedure prGetEqId
 @Make varchar(50),
 @Model varchar(50)
as
 select EquipmentId
 from Equipment
 where Make = @Make
 and Model = @Model
GO

运行方式：EXEC prGetEqId 'ToShiBa','Portege 7020CT'

输出值方式2：输出参数

CREATE procedure prGetEqId_2
 @Make varchar(50),
 @Model varchar(50),
 @EqId int output   ------输出参数
as
 select @EqId = EquipmentId  ----输出参数赋值
 from Equipment
 where Make = @Make
 and Model = @Model
GO

运行方式：

Declare @intEqId int
EXEC prGetEqId_2 'ToShiBa','Portege 7020CT',@intEqId output

select @intEqId 'XX'   --XX为输出列名

 

输出值方式3：返回值（只能整形数据才可以返回）

CREATE procedure prGetEqId_3
 @Make varchar(50),
 @Model varchar(50)
as

declare @intEqId int ---声明变量

select @intEqId  = EquipmentId
 from Equipment
 where Make = @Make
 and Model = @Model

return @intEqId  --- 返回值
GO

运行方式：

Declare @intEqId int
EXEC @intEqId = prGetEqId_3 'ToShiBa','Portege 7020CT'

select @intEqId 'XX'

 

6.存储过程参数带默认值

Create procedure prGetEqId_4
 @Make varchar(50) = '%',  ------带默认值
 @Model varchar(50) = '%'
as
 select *
 from Equipment
 where Make Like @Make
 and Model Like @Model

GO

运行方式：

EXEC prGetEqId_4 'T%','Portege%'    --参数有值

EXEC prGetEqId_4 'T%'   ----第一个参数有值，第二个默认

EXEC prGetEqId_4   ----两个参数都忽略

EXEC prGetEqId_4 @Model = 'T%' --忽略第一个，Model 需要与列名相同

 

7.光标的使用方法

CREATE Procedure prBcpOutTables
--loop through tables and export them to text fiels
  @debug int = 0
As

declare @chvTable varchar(128),
  @chvCommand varchar(255)

DECLARE @curTables CURSOR    ----声明光标

-- get all tables from current database
SET @curTables = CURSOR FOR   -----光标绑定
 select name
 from sysobjects
 where xType = 'U'

OPEN @curTables   ----打开光标

-- get first table
FETCH NEXT FROM @curTables   ----取值
INTO @chvTable

-- if we succefully read the current record
WHILE (@@FETCH_STATUS = 0)   -----如果正确取值
BEGIN

 -- assemble DOS command for exporting table
 Set @chvCommand = 'bcp "Asset..[' + @chvTable + ']" out C:/sql7/backup/' + @chvTable + '.txt -c -q -Sdejan -Usa -Pdejan'                                ----设置一条批处理命令
 -- during test just display command
 if @debug <> 0
  select @chvCommand chvCommand
 
 -- in production execute DOS command and export table
 if @debug = 0
  execute xp_cmdshell @chvCommand, NO_OUTPUT   ---运行命令
  
 FETCH NEXT FROM @curTables     ----取下一个值
 INTO @chvTable

END

CLOSE @curTables   -----关闭光标
DEALLOCATE @curTables-----销毁光标

return 0
GO

 

8.增加新列

Alter table PartTest
ADD Cost money NULL

GO

 

9.运行一条命令

Exec ('select * from PartTest')