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

g+bw ZP Bre0/*--下面我们新建一个名为company的数据库，创建三个表priduct、project、tblsales，并在其中创建默认值、规则、触发器、存储过程，并利用用户、角色、权限等确保数据库安全。*/51Testing软件测试网lvemk!BC
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_priduct_project]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)51Testing软件测试网
MtK;Q WiZ;j
ALTER TABLE [dbo].[priduct] DROP CONSTRAINT FK_priduct_project51Testing软件测试网1Y;ta} Hu
GO

o n4E,_]0_lR!w0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getavgpbiaodi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
R*`1@q a,ApY ~0drop procedure [dbo].[getavgpbiaodi]51Testing软件测试网@no v kl{/[
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pinfo5000]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)51Testing软件测试网\[x[D%YB
drop procedure [dbo].[pinfo5000]
k%s'A&th.x7s0GO51Testing软件测试网z6M)OH,|9K

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
*x5P]9cyU2T0drop procedure [dbo].[pro]
5}|#|bK1h0GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysconstraints]') and OBJECTPROPERTY(id, N'IsView') = 1)
J'?dCP
F0drop view [dbo].[sysconstraints]51Testing软件测试网*l,cY1vsmUN
GO51Testing软件测试网%A/}O @fpx.y0C

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[priduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)51Testing软件测试网 c6M-y
sbTPQ
drop table [dbo].[priduct]
v7~gO"u xE0GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
@+_"[IdC[0drop table [dbo].[project]
E T&]Vw\;k&@0GO51Testing软件测试网0m\yvX&P)D

xcvRv#^}I&f0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblsales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Y XpQ$n3HW0drop table [dbo].[tblsales]51Testing软件测试网*G(Ht&Wa
GO51Testing软件测试网V(mZ-vIW'u h9Y}'p

8DU'Km;\0if exists (select * from dbo.systypes where name = N'tele')51Testing软件测试网w l Mb_6s\&L
exec sp_droptype N'tele'51Testing软件测试网zMs#kxzy&O7c
GO51Testing软件测试网F j.rsB@

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rule_position]') and OBJECTPROPERTY(id, N'IsRule') = 1)51Testing软件测试网y o}C:A|"U:L8l
drop rule [dbo].[rule_position]
3~_~7o~J9T0GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@position]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
(y/vi-gWv/y&M0drop default [dbo].[@position]51Testing软件测试网9d|5[7^1T8mJ
GO

create default [@position] as '其它'51Testing软件测试网Y}"]0Z o&};R
GO
U;W)x&_ f
B(f7k[0create rule [rule_position] as @postion in('项目经理','秘书','会计','职员','其它')51Testing软件测试网)z(sBk#bms._#r G
GO
M:EP~AG$N0setuser
5q-E0D*tc"c0oq0GO

4b(jA#?Aw0EXEC sp_addtype N'tele', N'smallint', N'not null'51Testing软件测试网 N
aGVT;l
GO51Testing软件测试网FMT4@]

F&K}(@'@ [ j0setuser
&|-_"rg Q&U
eje)V0GO

O ?vG F0l0CREATE TABLE [dbo].[priduct] (51Testing软件测试网'Ls
n{t&`"h
 [产品ID] [int] NULL ,
jn`aHeyR#]0 [产品名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL
1M!V,k.W| kYh+p0) ON [PRIMARY]
T?6J9QlO6U!j]_0GO

c*{
[)OSP6cF0CREATE TABLE [dbo].[project] (
J]"s1f$?2c
{zw0 [项目编号] [int] NOT NULL ,
%RY(PBN9Q(tpf0 [项目名称] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网-N0a%bl @)O
 [开始日期] [datetime] NULL ,
Z%e!k
x-f0 [预计日期] [int] NULL ,

qP]*e1Yd1cc0 [客户编号] [int] NULL ,51Testing软件测试网2I f%A'j T)C?o,WX
 [负责人编号] [int] NULL ,
w,\)KJo,OI0_3}C0 [项目标的] [int] NULL51Testing软件测试网n-vU VF%o
) ON [PRIMARY]
7k){riX4P"P,R|0GO51Testing软件测试网/l&| u6VaG4n

p,|1SZ @
R3B0CREATE TABLE [dbo].[tblsales] (
P S5{9N?
U0 [number] [int] NOT NULL ,51Testing软件测试网cs#]yc
 [name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网 @^q }\;{{*OG
 [sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
f.fm$A^ |?%W0 [birthday] [datetime] NULL ,51Testing软件测试网 } ^,Z Ki
 [salary] [money] NULL
!k2R6|wvJ'y*sT.N0) ON [PRIMARY]51Testing软件测试网LG\CD;W7_)XF
GO

setuser
B)Im4YEfp0GO51Testing软件测试网!q$D'~1wPz

5s zJQ
vQ#F0EXEC sp_bindefault N'[dbo].[@position]', N'[project].[项目标的]'
~ek#BV;n2T2?0GO51Testing软件测试网i3K3{|2fk

EXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[项目名称]'51Testing软件测试网 L3CPtPg
GO

setuser51Testing软件测试网Xf\`?8H
GO51Testing软件测试网#}q/T"TxNy

2}M$\`%FvR.T;p#f0SET QUOTED_IDENTIFIER ON
@7mM.v d a$Z*gu.T0GO
&_,yO)L8v5E;f0SET ANSI_NULLS ON51Testing软件测试网ax{U)^\+u
GO51Testing软件测试网 [o:}A }/o&T8wQ%Q

Ur;U9D^&@]#f0--/****** Encrypted object is not transferable, and script. can not be generated. ******/

GO51Testing软件测试网.X;uoEO0dz
SET QUOTED_IDENTIFIER OFF51Testing软件测试网 x6[9X8RW"K;I
GO
*{zG?)x}:?*?+T0SET ANSI_NULLS ON51Testing软件测试网.];[#E'Q/G$J
M O ?
GO

SET QUOTED_IDENTIFIER ON
"hQ/})i)}G3Bw4e0GO

s"b-Bb{0SET ANSI_NULLS ON
/P-KmM+|\0GO

create procedure getavgpbiaodi
rLv3X/K0@name varchar(10),@avgpbiaodi int output
'qi.O-@A/]4W3nv+`0as51Testing软件测试网Kt+X2e;[Zy
declare @errorsave int51Testing软件测试网/L Yqz~0TD
set @errorsave=0
/n-h-|po;s s.g0select @avgpbiaodi=avg(项目标的)
L|4vjw6E:U#I1Mn0from project as p inner join pmanager as pm on p.负责人ID=pm.负责人ID
L?&PMVm"K3[0where pm.姓名=@name51Testing软件测试网.l/P6V_;NX2V(_K
if(@@error<>0)
V fs;X#D1z0 set @errorsave=@@error
3e.ao1D[*uB0 return @errorsave

"J_t*I{0GO51Testing软件测试网 rY'M$R4qv"FTWG
SET QUOTED_IDENTIFIER OFF51Testing软件测试网4B*q,ay
t*A8r-HI
GO
&T-X3t
N vRrL O&i0SET ANSI_NULLS ON
oO:J+VV0GO51Testing软件测试网6h{ Q uR

5S&d1H[!Msre0SET QUOTED_IDENTIFIER ON
Uzroh3t0GO51Testing软件测试网^u
huT$N
SET ANSI_NULLS ON51Testing软件测试网S[-d$Y-d ?(k0o4}
GO51Testing软件测试网#~ w5A;D O%eb`

4g ^dV,E0create procedure pinfo500051Testing软件测试网7q8` @V.o*a
as select * from project where 项目标的>=500051Testing软件测试网b+w(I"nkh cq2V
order by 项目标的 desc51Testing软件测试网(}u#z"F0tZ)a

GO
jY$zW.d F9|0SET QUOTED_IDENTIFIER OFF51Testing软件测试网*\.b;W&`7Y
GO51Testing软件测试网 Un#f7E}*W]-X)vSg
SET ANSI_NULLS ON
1X]x2CO$d
Eig0GO

SET QUOTED_IDENTIFIER ON51Testing软件测试网.]YMNu0{2DlU
GO51Testing软件测试网(v2U%E3gN;T
SET ANSI_NULLS ON
OLZ0rEj@/y0GO51Testing软件测试网:}2?2G T6vT3p

6zgg5E&p0create procedure pro
3k+N6T,h DW0@n1 int,@n2 int,@n3 int,@avreage int output51Testing软件测试网/_1hGjW+vNx8k/u
as select51Testing软件测试网:_,GC^w/tSd
@avreage=(@n1+@n2+@n3)/3
R1`0tNo%p)p1P0declare @avgscore int
%U?U-lS:`(o8?0exec pro1 1,2,3,@avgscore output51Testing软件测试网+ST0n$kz"Rp2K
select 'The score is:',@avgscore51Testing软件测试网,Oz2D w3a$^

uu-\4l,f Su0GO51Testing软件测试网4s/w0|K$z7fB/u
SET QUOTED_IDENTIFIER OFF
;Hjd#c%N0GO
Ec_!h
\0Ox3E
y1k0SET ANSI_NULLS ON51Testing软件测试网@9x!qG.tFF0c
GO