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

/*--下面我们新建一个名为company的数据库，创建三个表priduct、project、tblsales，并在其中创建默认值、规则、触发器、存储过程，并利用用户、角色、权限等确保数据库安全。*/
J]!Jy7Mqy0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_priduct_project]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)51Testing软件测试网G8` gW.~I
ALTER TABLE [dbo].[priduct] DROP CONSTRAINT FK_priduct_project
1of;a`+Io0GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getavgpbiaodi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)51Testing软件测试网G$lh@
n8T/F
drop procedure [dbo].[getavgpbiaodi]51Testing软件测试网L+s4m mj/c:S9@K0k'q
GO

Hb9Gvg`NX mCA0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pinfo5000]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-j(E#\~rQ
G0drop procedure [dbo].[pinfo5000]
VN0A2}m V0GO51Testing软件测试网 a` sJ$x"f.Yc)ffb

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
+yIA8u8WPiB$^5V0drop procedure [dbo].[pro]
CPi l9_QNtqL4L0GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysconstraints]') and OBJECTPROPERTY(id, N'IsView') = 1)
PO&ou)`9M` S{0drop view [dbo].[sysconstraints]
|:\,rX'|Rt"m0GO51Testing软件测试网Jm}MD"EP

G!@E,s9On0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[priduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
I"c*^pdv*T0drop table [dbo].[priduct]51Testing软件测试网fpo+qc(d;Ld
GO

w2LR,M
Ury|6Xf0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
se$c%i(\o;}0drop table [dbo].[project]51Testing软件测试网;X-S7oq$L7F n(]
GO

`W G1q/A }xFV0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblsales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)51Testing软件测试网vOF9M.n6v1BY&l.m
drop table [dbo].[tblsales]51Testing软件测试网O{M`3pe
GO

fZS @JJ[0if exists (select * from dbo.systypes where name = N'tele')51Testing软件测试网eZe}fG%R
exec sp_droptype N'tele'
/W6q
]3`$L0GO51Testing软件测试网6m%yI;\$lI N8K

h"Y^8v&v}%|0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rule_position]') and OBJECTPROPERTY(id, N'IsRule') = 1)
s~i3} n4iE f6E-N0drop rule [dbo].[rule_position]
g#B |&m_Xj0GO

zB So)~5j0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@position]') and OBJECTPROPERTY(id, N'IsDefault') = 1)51Testing软件测试网-Y6jK/FPc%y6G
drop default [dbo].[@position]51Testing软件测试网W7p]!w5o|
GO

cS:o#c8pO6o0create default [@position] as '其它'51Testing软件测试网V4p&hJQ\5t1hd^z
GO51Testing软件测试网8{|'\5Lz ? JT*EG j
create rule [rule_position] as @postion in('项目经理','秘书','会计','职员','其它')51Testing软件测试网AGl(V2}&R-Z
GO
)n0ct5xh7\.dE0setuser51Testing软件测试网on.P$P@H
bRxE
GO51Testing软件测试网[)SNl6l
K

;Sx9_6FIsqK5tD0EXEC sp_addtype N'tele', N'smallint', N'not null'
$jS0d9qwU6I0GO51Testing软件测试网;YTcv({wTPBI5V

#`+i4}:Z/h+b9mq1o0setuser
i.Q&l9gt0K:g u)Kp0GO

3k\GC`L0CREATE TABLE [dbo].[priduct] (
E}#p
yaQo0 [产品ID] [int] NULL ,
8G:@'uC+f'^ax0 [产品名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL51Testing软件测试网-C;T*ur Q`@}
) ON [PRIMARY]51Testing软件测试网Z F
hR5]
GO51Testing软件测试网mGbD6R?+^

CREATE TABLE [dbo].[project] (
n$i]&pKtJk0 [项目编号] [int] NOT NULL ,
t[n,Ab5_ P0 [项目名称] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
l1j&~L,N4Dy\-D0 [开始日期] [datetime] NULL ,
%U"\M/mz,@5u{0 [预计日期] [int] NULL ,51Testing软件测试网&e|$L|^*c6\
 [客户编号] [int] NULL ,51Testing软件测试网I+V e+z6?l^$jJv
 [负责人编号] [int] NULL ,
"?5oE SB/@k+t0 [项目标的] [int] NULL51Testing软件测试网5R*gr%io;X
) ON [PRIMARY]51Testing软件测试网hR\Yj"\
GO

2Ze'H{3A)^9n{c0CREATE TABLE [dbo].[tblsales] (51Testing软件测试网
I:Z:o\ Q{/_
 [number] [int] NOT NULL ,
a:oz#[3T4G4W
T0 [name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
8p"_1[+Fk Wl0 [sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网 f4[_EKa
 [birthday] [datetime] NULL ,51Testing软件测试网*[\7Ri)A5r
 [salary] [money] NULL51Testing软件测试网'W lc${!K!PWOa
) ON [PRIMARY]
2G }
~e%T#M0GO

^!Nf G2K|0setuser51Testing软件测试网Xm.l7Z7|;X*a"fH
GO

EXEC sp_bindefault N'[dbo].[@position]', N'[project].[项目标的]'
,v(i9t v,h
Rx0GO51Testing软件测试网!r
w
WeU
Ht

~s_Q R0EXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[项目名称]'51Testing软件测试网.n0Y,y
]4W$k2sj)y
GO51Testing软件测试网 RsB3{@:J

x2pi s1tv7f}0setuser51Testing软件测试网6}+b/I6t6B'C^
GO

SET QUOTED_IDENTIFIER ON51Testing软件测试网1Y
TPR Si
GO

mh3a\A0SET ANSI_NULLS ON51Testing软件测试网1H"]C!KefV
GO51Testing软件测试网Z`6VR$lmP6c+O

4aA-CFp}0--/****** Encrypted object is not transferable, and script. can not be generated. ******/

\9t"`!h C0N0GO
3d"Q|2G[Y ZD)i0SET QUOTED_IDENTIFIER OFF
{%]0xd ce0GO51Testing软件测试网 wjY5pF2^E
SET ANSI_NULLS ON
+}@bz{Vd}0GO

^+[;sc6\$j|^,i0SET QUOTED_IDENTIFIER ON51Testing软件测试网K u*ve.h_8g
GO51Testing软件测试网F1U.PM(n e6w(w_L
SET ANSI_NULLS ON
-DLp9i4sj:U0GO

create procedure getavgpbiaodi
o.{'T `B}0@name varchar(10),@avgpbiaodi int output
8{6Ks*f%L5l0as51Testing软件测试网G(_{U]t7~`
declare @errorsave int51Testing软件测试网Y ^&[-gpfbG
set @errorsave=0
o+iT*}IJ*V t5HjjF0select @avgpbiaodi=avg(项目标的)51Testing软件测试网 w F*H;Bk+Hu
from project as p inner join pmanager as pm on p.负责人ID=pm.负责人ID51Testing软件测试网{J p2s[~`
where pm.姓名=@name
a-U9vZ6h@t)s0if(@@error<>0)
T2PP!bniz jf0 set @errorsave=@@error51Testing软件测试网ql3|i,?J$n{4^:y
 return @errorsave

b"t:u5k;i yo0GO

FR ^2NiW4p^w%}0SET QUOTED_IDENTIFIER OFF51Testing软件测试网 [+? QL{,cYP`S
GO51Testing软件测试网Gz(q`m(W%A
SET ANSI_NULLS ON51Testing软件测试网7xa,U3^_ _|
GO51Testing软件测试网ls$a9N$w

+H6B.Fq,V0SET QUOTED_IDENTIFIER ON51Testing软件测试网 C GJB n9Grp3H
GO
B.gc Tw Bf4U0SET ANSI_NULLS ON51Testing软件测试网lA7E4ay6~*ni3o
GO51Testing软件测试网#n8tjT,[,m"\Sc

k9~S4c!U0create procedure pinfo500051Testing软件测试网p*P[#CU5c-fqk.q
as select * from project where 项目标的>=500051Testing软件测试网:SJ{,r:W[+T li
order by 项目标的 desc51Testing软件测试网 \b&Ps%C3F6S1^

.Oh)x1B_e0GO51Testing软件测试网U6a:v(Y2zp3sA'a
SET QUOTED_IDENTIFIER OFF51Testing软件测试网fRBF``^g,d i
GO51Testing软件测试网? O{`/y0PM F.u@
SET ANSI_NULLS ON
X#q;P
q?+l/W,K0GO51Testing软件测试网wk6d3q9^QJ6T(Kg

"^
oK1RW$x/] x*]c0SET QUOTED_IDENTIFIER ON51Testing软件测试网[y d%Dec
V
GO51Testing软件测试网)q+tID2g b] J
SET ANSI_NULLS ON
;c+n0?m(S"P0GO51Testing软件测试网v i@+z8k0Bf4u

create procedure pro
iQ0y@[.l O ]0@n1 int,@n2 int,@n3 int,@avreage int output
%qG`,l'w
L0as select
m`d^$R`0@avreage=(@n1+@n2+@n3)/351Testing软件测试网I!O"jtD?v }sZ
declare @avgscore int51Testing软件测试网&N]]E|r
]-Q
exec pro1 1,2,3,@avgscore output51Testing软件测试网3}*{G*dm){
select 'The score is:',@avgscore51Testing软件测试网8UI~![!b#n_I@ N6x

v}"t(Ppu0GO51Testing软件测试网-}
S]&R|x
SET QUOTED_IDENTIFIER OFF
2o4i.O3pE(K,f6G0GO
S,C+G{0e4i?MX0SET ANSI_NULLS ON
/? n-QTA1d0GO