SQL Server数据库程序设计知识总结

/*--下面我们新建一个名为company的数据库，创建三个表priduct、project、tblsales，并在其中创建默认值、规则、触发器、存储过程，并利用用户、角色、权限等确保数据库安全。*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_priduct_project]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[priduct] DROP CONSTRAINT FK_priduct_project
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getavgpbiaodi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getavgpbiaodi]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pinfo5000]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pinfo5000]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pro]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysconstraints]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[sysconstraints]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[priduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[priduct]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[project]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblsales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblsales]
GO

if exists (select * from dbo.systypes where name = N'tele')
exec sp_droptype N'tele'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rule_position]') and OBJECTPROPERTY(id, N'IsRule') = 1)
drop rule [dbo].[rule_position]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@position]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
drop default [dbo].[@position]
GO

create default [@position] as '其它'
GO
create rule [rule_position] as @postion in('项目经理','秘书','会计','职员','其它')
GO
setuser
GO

EXEC sp_addtype N'tele', N'smallint', N'not null'
GO

setuser
GO

CREATE TABLE [dbo].[priduct] (
 [产品ID] [int] NULL ,
 [产品名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[project] (
 [项目编号] [int] NOT NULL ,
 [项目名称] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [开始日期] [datetime] NULL ,
 [预计日期] [int] NULL ,
 [客户编号] [int] NULL ,
 [负责人编号] [int] NULL ,
 [项目标的] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblsales] (
 [number] [int] NOT NULL ,
 [name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [birthday] [datetime] NULL ,
 [salary] [money] NULL 
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[@position]', N'[project].[项目标的]'
GO

EXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[项目名称]'
GO

setuser
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--/****** Encrypted object is not transferable, and script can not be generated. ******/

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure getavgpbiaodi
@name varchar(10),@avgpbiaodi int output
as 
declare @errorsave int
set @errorsave=0
select @avgpbiaodi=avg(项目标的)
from project as p inner join pmanager as pm on p.负责人ID=pm.负责人ID
where pm.姓名=@name
if(@@error<>0)
 set @errorsave=@@error
 return @errorsave

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure pinfo5000
as select * from project where 项目标的>=5000
order by 项目标的 desc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure pro
@n1 int,@n2 int,@n3 int,@avreage int output
as select
@avreage=(@n1+@n2+@n3)/3
declare @avgscore int
exec pro1 1,2,3,@avgscore output
select 'The score is:',@avgscore

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO