| /*--下面我们新建一个名为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 |