创办数据库、表、主外键、各种约束、存储过程、视图、索引、事务使用、触发器、创建登录账号、数据库用户 Sql语句示例

创建数据库、表、主外键、各种约束、存储过程、视图、索引、事务使用、触发器、创建登录账号、数据库用户 Sql语句示例

1. -----------创建数据库----------------??user?master??
2. go??if?exists?(select?*?from?sysdatabases?where?name='bankSystem')??
3. ????drop?database?bankSystem??go??
4. create?database?bankSystem??on?primary??
5. (??????name='bank_data',??
6. ????filename='D:\bank\bank_data.mdf',??????size=5,??
7. ????filegrowth=15%??)??
8. log?on??(??
9. ????name='bank_log',??????filename='D:\bank\bank_log.ldf',??
10. ????size=5,??????filegrowth=15%??
11. )??go??
12. ??----------------建表并添加约束----------------??
13. use?bankSystem??go??
14. ---------------------表userInfo--------------??if?exists?(select?*?from?sysObjects?where?name='userInfo')??
15. ????drop?table?userInfo??go??
16. create?table?userInfo??(??
17. ????customerID?int?identity(1,1)?not?null,??????customerName?varchar(30)?not?null,??
18. ????PID?varchar(18)?not?null,??????telephone?varchar(13)?not?null,??
19. ????address?varchar(50)??)??
20. go??alter?table?userInfo??
21. add?constraint?PK_customID?primary?key?(customerID)??alter?table?userInfo??
22. add?constraint?UQ_pid?unique?(PID)??alter?table?userInfo??
23. add?constraint?CK_pid?check?(pid?like?'4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'?or??????????????????????????????pid?like?'4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')??
24. ??alter?table?userInfo??????
25. add?constraint?CK_telephone?check?(telephone?like?'13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'?or??????????????????????????????????????telephone?like?'%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')??
26. ------------表cardInfo---------------??if?exists?(select?*?from?sysObjects?where?name='cardInfo')??
27. ????drop?table?cardInfo??go??
28. create?table?cardInfo??(??
29. ????cardID?varchar(18)?not?null,??????curType?varchar(10)?not?null,??
30. ????savingType?varchar(10)?not?null,??????openDate?datetime?not?null,??
31. ????openMoney?money?not?null,??????balance?money?not?null,??
32. ????pass?varchar(6)?not?null,??????IsReportLoss?bit?not?null,??
33. ????customerID?int?not?null??)??
34. go??alter?table?cardInfo??
35. add?constraint?PK_cardID?primary?key?(cardID)??alter?table?cardInfo??
36. add?constraint?CK_cardID?check?(cardID?like?'1010?3576?[0-9][0-9][0-9][0-9]?[0-9][0-9][0-9]')??alter?table?cardInfo??
37. add?constraint?DF_curType?default?('RMB')?for?curType??alter?table?cardInfo??
38. add?constraint?DF_openDate?default?(getDate())?for?openDate??alter?table?cardInfo??
39. add?constraint?CK_openMoney?check?(openMoney?>=1)??alter?table?cardInfo??
40. add?constraint?CK_balance?check?(balance>=1)??alter?table?cardInfo??
41. add?constraint?CK_pass?check?(pass?like?'[0-9][0-9][0-9][0-9][0-9][0-9]')??alter?table?cardInfo??
42. add?constraint?DF_pass?default?(888888)?for?pass??alter?table?cardInfo??
43. add?constraint?DF_IsReportLoss?default?(0)?for?IsReportLoss??alter?table?cardInfo??
44. add?constraint?FK_customerID?foreign?key?(customerID)?references?userInfo?(customerID)??alter?table?cardInfo??
45. add?constraint?CK_savingType?check?(savingType?like?'活期'?or?savingType?like?'定活两期'?or?savingType?like?'定期')?????
46. ---------------表transInfo-------------??if?exists?(select?*?from?sysObjects?where?name='transInfo')??
47. ????drop?table?transInfo??go??
48. create?table?transInfo??(??
49. ????transDate?datetime?not?null,??????cardID?varchar(18)?not?null,??
50. ????transType?varchar(4)?not?null,??????transMoney?money?not?null,??
51. ????remark?text??)??
52. go??alter?table?transInfo??
53. add?constraint?DF_transDate?default?(getDate())?for?transDate??alter?table?transInfo??
54. add?constraint?FK_cardID?foreign?key?(cardID)?references?cardInfo?(cardID)??alter?table?transInfo??
55. add?constraint?CK_transType?check?(transType?like?'存入'?or?transType?like?'支取')??alter?table?transInfo??
56. add?constraint?CK_transMoney?check?(transMoney?>0)??go??
57. ??--------------插入数据----------------??
58. insert?into?userInfo?values?('张三','420656789012345','010-67898978','北京海淀')??insert?into?userInfo?values?('李四','420645678912345678','0478-44443333',default)??
59. insert?into?cardInfo?values?('1010?3576?1212?113',default,'定期','2007-10-10?11:54:36.812',1,1,default,default,2)??insert?into?cardInfo?values?('1010?3576?1234?567',default,'活期','2007-10-10?11:58:45.352',1000,1000,default,default,1)??
60. ??insert?into?transInfo?(transDate,transType,cardID,transMoney)?values?('2007-10-24?11:56:36.812','支取','1010?3576?1234?567',900)??
61. update?cardInfo?set?balance=balance-900?where?cardID='1010?3576?1234?567'??insert?into?transInfo?(transDate,transType,cardID,transMoney)?values?('2007-10-24?11:56:54.245','存入','1010?3576?1212?113',5000)??
62. update?cardInfo?set?balance=balance+5000?where?cardID='1010?3576?1212?113'????
63. ---------------修改密码--------------??update?cardInfo?set?pass='123456'?where?cardID='1010?3576?1234?567'??
64. update?cardInfo?set?pass='123123'?where?cardID='1010?3576?1212?113'????
65. -------------是否挂失------------??update?cardInfo?set?IsReportLoss=1?where?cardID='1010?3576?1212?113'??
66. ??---------------统计银行资金流通余额和盈利结算--------------??
67. declare?@inMoney?money,@outMoney?money,@sumMoney?money,@rateEnd?money???select?@inMoney=sum(transMoney)?from?transInfo?where?transType='存入'??
68. select?@outMoney=sum(transMoney)?from?transInfo?where?transType='支取'??set?@sumMoney=@inMoney-@outMoney??
69. set?@rateEnd=@outMoney*0.008-@inMoney*0.003??print?'银行流余额总计为：'+convert(varchar(20),@sumMoney)+'RMB'??
70. print?'盈利结算结果为：'+convert(varchar(20),@rateEnd)+'RMB'????
71. ??---------------查询本周开户卡号----------------??
72. select?cardID?from?cardInfo?where?datepart(wk,openDate)=datepart(wk,getDate())????
73. ??----------------查询本月交易金额最高卡号------------??
74. select?*?from?userInfo?where?customerID?in???????????(select?customerID?from?cardInfo?where?cardID?in???
75. ????????????(select?cardID?from?cardInfo?where?datepart(wk,openDate)=datepart(wk,getDate())))????
76. ----------------本月交易金额最大的卡号为-----------------??select?cardID?from?transInfo?where?transMoney=(??
77. ????????select?max(transMoney)?from?transInfo?where?datepart(mm,transDate)=datepart(mm,getDate()))????
78. ---------------------挂失帐号客户信息----------------??select?customerName?as?客户姓名?telephone?as?电话?from?userInfo?where?customerID?in??
79. ????????(select?customerID?from?cardInfo?where?isreportloss?=?1)?????
80. -------------------------催款提醒业务-------------??select?customerName?as?客户姓名,telephone?as?电话,balance?as?卡上余额?from?userInfo?inner?join?cardInfo???
81. ????????on?userInfo.customerID=cardInfo.customerID?where?userInfo.customerID?in??????????(select?customerID?from?cardInfo?where?balance?<?200)??
82. ??------------创建索引--------------??
83. if?exists?(select?*?from?sysindexes?where?name='IX_cardid')??????drop?index?transInfo.IX_cardid??
84. go??create?nonclustered?index?IX_cardid???
85. ????on?transInfo?(cardID)??????with?fillfactor=70??
86. go????
87. select?*?from?transInfo?with(index=IX_cardid)?where?cardID?='1010?3576?1212?113'????
88. ------------创建视图-------------??if?exists?(select?*?from?sysobjects?where?name='view_userInfo')??
89. ????drop?view?view_userInfo??go??
90. create?view?view_userInfo??????as??
91. ????????select?客户编号=customerID,开户姓名=customerName,身份证号=PID,电话=telephone,地址=address?from?userinfo??go??
92. if?exists?(select?*?from?sysobjects?where?name='view_cardInfo')??????drop?view?view_cardInfo??
93. go??create?view?view_cardInfo??
94. ????as??????????select?卡号=cardID,货币=curType,存款类型=savingType,开户日期=openDate,??
95. ????????????????余额=balance,密码=pass,是否挂失=isreportloss,客户编号=customerID?from?cardInfo??go??
96. if?exists?(select?*?from?sysobjects?where?name='view_transInfo')??????drop?view?view_transInfo??
97. go??create?view?view_transInfo??
98. ????as??????????select?交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark??
99. ????????????????from?transInfo??go??
100. select?*?from?view_userInfo??select?*?from?view_cardInfo??
101. select?*?from?view_transInfo??go??
102. ??---------------创建存储过程----------------??
103. if?exists?(select?*?from?sysobjects?where?name='proc_getAndoutMoney')??????drop?proc?proc_getAndoutMoney??
104. go??create?proc?proc_getAndoutMoney??
105. ????@cardID?varchar(19),??????@money?money,??
106. ????@type?char(4),??????@inputpass?char(6)??
107. ????as??????????if(@type='支取')??
108. ????????????begin??????????????????begin?transaction??
109. ????????????????declare?@error?int??????????????????set?@error?=?0??
110. ????????????????if(@inputpass=(select?pass?from?cardInfo?where?cardID=@cardID))??????????????????????begin??
111. ????????????????????????update?cardInfo?set?balance=balance-@money?where?cardID=@cardID??????????????????????????set?@error=@error+@@error??
112. ????????????????????????insert?into?transInfo?values?(getDate(),@cardID,@type,@money,default)??????????????????????????set?@error=@error+@@error??
113. ????????????????????end??????????????????else??
114. ????????????????????print?'密码错误'??????????????????if?(@error<>0)??
115. ????????????????????rollback?transaction??????????????????else??
116. ????????????????????commit?transaction??????????????end??
117. ????????else??????????????begin??
118. ????????????????update?cardInfo?set?balance=balance+@money?where?cardID=@cardID??????????????????insert?into?transInfo??values?(getDate(),@cardID,@type,@money,default)???
119. ????????????end??go??
120. exec?proc_getAndoutMoney?'1010?3576?1212?113',500,'存入','000000'??exec?proc_getAndoutMoney?'1010?3576?1234?567',300,'支取','123446'??
121. select?*?from?cardInfo?where?cardID='1010?3576?1212?113'????
122. -----------随机产生卡号----------??use?bankSystem???
123. go??if?exists?(select?*?from?sysobjects?where?name='proc_randCardID')??
124. ????drop?proc?proc_randCardID??go??
125. create?proc?proc_randCardID??????@randCardID?varchar(18)?output??
126. ????as??????????declare?@rand?numeric(15,8)??
127. ????????declare?@tempStr?varchar(16)??????????select?@rand=rand((datepart(mm,getDate())*100000+datepart(ss,getDate())*1000+datepart(ms,getDate())))??
128. ????????set?@tempStr=convert(varchar(16),@rand)??????????set?@randCardID='1010?3576?'+subString(@tempStr,3,4)+'?'+subString(@tempStr,7,3)??
129. go??declare?@mycardID?varchar(18)??
130. exec?proc_randCardID?@mycardID?output??print?'随机产生卡号为：'+@mycardID???
131. ??-------------开户存储过程--------------??
132. set?nocount?on??if?exists?(select?*?from?sysobjects?where?name='proc_openAccount')??
133. ????drop?proc?proc_openAccount??go??
134. create?proc?proc_openAccount??????@customerName?varchar(8),??
135. ????@PID?varchar(18),??????@telephone?char(13),??
136. ????@openMoney?money,??????@savingType?char(8),??
137. ????@address?varchar(50)='?'??????as??
138. ????????declare?@cardID?varchar(18)??????????declare?@customerID?int??
139. ????????while(1=1)??????????????begin??
140. ????????????????exec?proc_randCardID?@cardID?output???????????????????if?not?exists?(select?*?from?cardInfo?where?cardID=@cardID)??
141. ????????????????????break??????????????????else??
142. ????????????????????continue??????????????end??
143. ????????if?(@openMoney<1)??????????????return??
144. ????????else??????????????begin??
145. ????????????????begin?transaction??????????????????????declare?@error?int??
146. ????????????????????set?@error=0??????????????????????insert?into?userInfo?values?(@customerName,@PID,@telephone,@address)??
147. ????????????????????set?@error=@error+@@error??????????????????????select?@customerID=customerID?from?userInfo?where?customerName=@customerName??
148. ????????????????????set?@error=@error+@@error??????????????????????insert?into?cardInfo?(cardID,savingType,openMoney,balance,customerID)??
149. ????????????????????????????values?(@cardID,@savingType,@openMoney,@openMoney,@customerID)??????????????????????set?@error=@error+@@error??
150. ????????????????if(@error<>0)??????????????????????rollback?transaction??
151. ????????????????else??????????????????????commit?transaction??
152. ????????????end??????????print?'尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@cardID+'?开户日期?'+??
153. ????????????????convert(varchar(30),getDate(),111)+'?开户金额?'+convert(varchar(10),@openMoney)??go??
154. exec?proc_openAccount?'王五','420656889012678','2222-63598978',1000,'活期','河南新乡'????
155. ??-----------------转账事务------------------??
156. if?exists?(select?*?from?sysobjects?where?name='proc_transfer')??????drop?proc?proc_transfer??
157. go??create?proc?proc_transfer??
158. ????@card1?char(18),??????@card2?char(18),??
159. ????@outMoney?money??????as??
160. ????????begin?transaction??????????????declare?@error?int??
161. ????????????set?@error=0??????????????update?cardInfo?set?balance=balance-@outMoney?where?cardID=@card1??
162. ????????????set?@error=@error+@@error??????????????insert?into?transInfo?values?(getDate(),@card1,'支取',@outMoney,default)??
163. ????????????set?@error=@error+@@error??????????????update?cardInfo?set?balance=balance+@outMoney?where?cardID=@card2??
164. ????????????set?@error=@error+@@error??????????????insert?into?transInfo?values?(getDate(),@card2,'存入',@outMoney,default)??
165. ????????????set?@error=@error+@@error??????????????if?(@error<>0)??
166. ????????????????rollback?transaction??????????????else??
167. ????????????????commit?transaction??go??
168. exec?proc_transfer?'1010?3576?1212?113','1010?3576?1234?567',2000??select?*?from?transInfo?where?cardID='1010?3576?1212?113'?????
169. select?*?from?transInfo?where?cardID='1010?3576?1234?567'???????????????????
170. -------------------创建登录帐号和数据库用户---------------------??exec?sp_addlogin?'sysAdmin','1234'??
171. exec?sp_grantdbaccess?'sysAdmin','sysAdminBankUser'??grant?insert,update,delete,select?on?userInfo,cardInfo,transInfo?to?sysAdminBankUser?
     

补上触发器：

1. --update触发器??set?nocount?on??
2. if?exists?(select?*?from?sysobjects?where?name?='trig_ppr_Update')??????drop?trigger?trig_ppr_Update??
3. go??create?trigger?trig_ppr_Update??
4. ????on?PlanPropertyRelation???????--with?encryption?--加密??
5. ????????for?update??????????????as??
6. ????????????????declare?@Plans_Id?int,@currentId?int,@childId?int,@childNewLevel?int??????????????????declare?@oldFatherId?int,@newFatherId?int??
7. ????????????????select?@oldFatherId=PPR_ProExtend_ID?from?deleted??????????????????select?@newFatherId=PPR_ProExtend_ID?from?inserted??
8. ????????????????if(@oldFatherId=@newFatherId)??????????????????????return;??
9. ????????????????else??????????????????begin?????????????????????
10. ????????????????????select?@Plans_Id=Plans_ID,@childId=Property_ID,@currentId=PlanPropertyRelation_ID?from?inserted??????????????????????delete?from?PropertyValuesRelation?where?PlanPropertyRelation_ID=@currentId??
11. ????????????????end??go??
12. ??--删除触发器，删除前触发??
13. set?nocount?on??if?exists?(select?*?from?sysobjects?where?name?='trig_ppr_BeforeDelete')??
14. ????drop?trigger?trig_ppr_BeforeDelete??go??
15. create?trigger?trig_ppr_BeforeDelete??????on?PlanPropertyRelation???
16. ????--with?encryption?--加密??????????INSTEAD?OF?delete??
17. ????????????as??????????????declare?@oldId?int,@error?int??
18. ????????????select?@oldId=PlanPropertyRelation_ID?from?deleted??????????????delete?from?PropertyValuesRelation?where?PlanPropertyRelation_ID=@oldId??
19. ????????????delete?from?PlanPropertyRelation?where?PlanPropertyRelation_ID=@oldId??go?
    
    

?