-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path数据库和ADO.html
766 lines (762 loc) · 199 KB
/
数据库和ADO.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD><TITLE>数据库和ADO</TITLE>
<META content="text/html; charset=utf-8" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17785"></HEAD>
<BODY>
<OL>
<LI><A href="#4ecddead-b9b7-41a2-a227-f5eb1bb768b5" target=_blank><SPAN><SPAN style="BORDER-BOTTOM: #00c 1px dashed; BACKGROUND-COLOR: #ffff00; COLOR: #000000; CURSOR: hand" name="wizKMHighlighterSpan"></SPAN><SPAN><SPAN style="BORDER-BOTTOM: #00c 1px dashed; BACKGROUND-COLOR: #ffff00; COLOR: #000000; CURSOR: hand" name="wizKMHighlighterSpan"></SPAN><SPAN><SPAN style="BORDER-BOTTOM: #00c 1px dashed; BACKGROUND-COLOR: #ffff00; COLOR: #000000; CURSOR: hand" name="wizKMHighlighterSpan"></SPAN><SPAN><SPAN style="BORDER-BOTTOM: #00c 1px dashed; BACKGROUND-COLOR: #ffff00; COLOR: #000000; CURSOR: hand" name="wizKMHighlighterSpan"></SPAN>数据库语言</SPAN></SPAN></SPAN></SPAN></A>
<LI><A href="#930969eb-bb11-49f0-8a1b-9b48fb471981" target=_blank>数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程)</A>
<LI><A href="#32caf1d1-715d-4ae5-aa62-0467fefb9594" target=_blank>主键的概念</A>
<LI><A href="#baf0409b-64d9-461e-81c2-c027bdc14f15" target=_blank>如何创建主键</A>
<LI><A href="#7c68bc65-7678-4a4e-a50f-397edc33e663" target=_blank>如何创建外键</A>
<LI><A href="#a8bd5571-bca8-4df2-aae6-c465ab4f94f6" target=_blank>主外键关系的概念以及使用</A>
<LI><A href="#f23edd9a-643d-499e-b498-5074baf8543b" target=_blank>数据库的主要类型</A>
<LI><A href="#0cdbff89-a6b7-427b-9eb2-9cec759416e4" target=_blank>数据库的主要数据类型</A>
<LI><A href="#ab1cdf03-7a71-4912-aaea-b429f784b720" target=_blank>使用SQL语句来创建数据库和表</A>
<LI><A href="#e49aebd1-4ba2-4a3b-be8d-9710b06f43be" target=_blank>约束分类</A>
<LI><A href="#5b0af399-a3f4-499c-917d-8254e6080b29" target=_blank>top的使用</A>
<LI><A href="#976a26dd-750a-40c7-8aec-11b277c064a8" target=_blank>Distinct的使用(去除重复数)</A>
<LI><A href="#e2de275e-7cbf-4454-a039-52d37ba7b17a" target=_blank>聚合函数</A>
<LI><A href="#389f0c62-84e8-43d3-8db1-f2be25395b4a" target=_blank>聚合函数注意事项</A>
<LI><A href="#01ecc43c-7ba1-4811-86f8-3e7b1af7c343" target=_blank>between and 和 in 的使用</A>
<LI><A href="#eb1109a5-b9e6-4732-978a-a83d1f74214d" target=_blank>like, not like 通配符(%,_,[],^)</A>
<LI><A href="#41a43486-1ac4-4297-a5c5-c526eab70195" target=_blank>空值处理：null 是什么？</A>
<LI><A href="#db4f73e2-8f53-42b6-9299-b6371208ad39" target=_blank>排序(order by id asc / desc)(默认是哪一种排序？)</A>
<LI><A href="#37329ac3-6a5c-4a01-a547-b6a5fe86b471" target=_blank>分组(group by ),单条件分组，多条件分组（分组时，要注意的事情[位置，列]）</A>
<LI><A href="#9b2dfc8e-3df0-4e0d-9bfe-e5a9fa1d9d84" target=_blank>筛选(Having的使用)，它和where的区别</A>
<LI><A href="#87cbb6ae-077e-4f17-be12-0d92701cfb2e" target=_blank>类型转换(CAST，CONVERT)</A>
<LI><A href="#98311d44-e5e7-449b-a24a-873270d903c2" target=_blank>union,union all的使用</A>
<LI><A href="#4a48cfbe-6678-4c15-ac52-2fc00c145dc6" target=_blank>一次插入多条数据</A>
<LI><A href="#133dc11a-872f-4105-94a6-79cda4da6eaa" target=_blank>字符串函数</A>
<LI><A href="#213d020a-6b9a-42e1-9c3c-be043d73a13f" target=_blank>ADO.NET主要类</A>
<LI><A href="#64fb5bb6-5240-451d-a45a-0f4d78027694" target=_blank>数据库连接字符串</A>
<LI><A href="#674956c1-d477-4558-a9ab-9168b20304cf" target=_blank>SqlConnection类的State属性</A>
<LI><A href="#3ca1c6aa-e862-4929-97d6-979d4a46d1d4" target=_blank>SqlCommand类的方法：</A>
<LI><A href="#d2a4fc2c-bdff-4ccd-bdc9-fa981d15f063" target=_blank>StatementCompleted事件的触发</A>
<LI><A href="#bc51f327-375c-4eee-a164-b3e1bffac847" target=_blank>获得刚刚插入数据库的自增id</A>
<LI><A href="#c99e6a02-326a-4d8e-8568-9cdd79a5e763" target=_blank>Sql注入攻击(避免方式？)</A>
<LI><A href="#3f2505fc-8010-4b91-8b6f-6763cfd6562f" target=_blank>如何使用迭代生成树形菜单</A>
<LI><A href="#cbbbacaa-ce96-4d80-88e3-8b252f1df73a" target=_blank>单例模式(单例模式的创建)</A>
<LI><A href="#41b5aec3-66a6-4d9d-a6ab-f6c6ea3067b3" target=_blank>DataTable的使用</A>
<LI><A href="#a737038b-4b6b-4ee4-868d-6fa49349cd50" target=_blank>类SqlDataAdapter的使用(重点)</A>
<LI><A href="#6634b96c-4075-475a-b86e-2f581fcd518f" target=_blank>类SqlCommandBuilder的使用(注意：他必须操作的是有主键的数据库)</A>
<LI><A href="#f93511ec-b8b2-4511-9940-b752489b0678" target=_blank>提取查询语句，封装SqlHelper类(不仅要会，而且要理解思想)</A>
<LI><A href="#51ecbb35-4f72-4f58-a464-3e5a2b15c424" target=_blank>SQL中的Switch语句</A>
<LI><A href="#3343884e-c40c-43c2-88e6-ffa77317fec6" target=_blank>SQL中的子查询</A>
<LI><A href="#175d76f9-c9b1-44a0-9920-6ee521a7d4fc" target=_blank>SQL中的Exists用法</A>
<LI><A href="#682381ee-3f5a-4a54-8bab-177fb17c1585" target=_blank>SQL中的Any</A>
<LI><A href="#49db626d-691f-47f5-89ee-87d12467e245" target=_blank>SQL中的All</A>
<LI><A href="#5327355e-f1b5-4a39-91df-0f75a4846751" target=_blank>SQL2008 微软官方推荐分页方式</A>
<LI><A href="#8e295adb-8c4a-46f0-85f0-dda41f6530cb" target=_blank>SQL中表等值连接(内连接) inner join</A>
<LI><A href="#7d4766ad-a365-41a3-baf7-7b3b278c0807" target=_blank>SQL中表左连接(左外连接) left join</A>
<LI><A href="#178e35f6-f356-4e42-b3ac-692ed135588e" target=_blank>SQL中表右连接(右外连接) right join</A>
<LI><A href="#920ba0fe-468a-4dd7-8ab3-68e16fc516f6" target=_blank>SQL中表交叉连接(两张表的乘积)</A>
<LI><A href="#2f6dbd7f-0c9c-45a2-be17-de1206bc0074" target=_blank>SQL中表全连接 full join</A>
<LI><A href="#507e6547-860c-4ee8-9bfc-91e7420eb21f" target=_blank>SQL中变量</A>
<LI><A href="#8887f127-be17-4c1e-bd73-d34241bb802c" target=_blank>SQL中的事务</A>
<LI><A href="#956b8d68-ed50-4c86-ab7e-921931b1aa16" target=_blank>创建存储过程</A>
<LI><A href="#6ad4c4e3-a566-4a24-84fb-26049535979f" target=_blank>存储过程带输出参数</A>
<LI><A href="#03cf6ec8-8742-4b17-9a24-93da32f575a4" target=_blank>调用存储过程</A>
<LI><A href="#03641657-a6d0-46b9-a696-b891eeb32d6b" target=_blank>触发器定义</A>
<LI><A href="#81393cae-dd65-4c3f-b787-e7c411c28ad8" target=_blank>触发器类型:</A>
<LI><A href="#43f0e154-97e6-4f57-837b-1176ef7f7370" target=_blank>触发器触发条件:</A>
<LI><A href="#c84c3279-d0cc-4608-8332-7e106758318d" target=_blank>什么是索引</A>
<LI><A href="#505b3727-1c3e-4b5b-b2d2-b673dd260095" target=_blank>索引类型</A>
<LI><A href="#06a502fe-6601-46cb-8281-cc61329b1d36" target=_blank>什么是填充因子</A>
<LI><A href="#f3f2c165-b602-41d8-b3e9-0cb63af7c88b" target=_blank>什么是临时表 </A>
<LI><A href="#d31c1282-ea4c-4a74-b006-bbb97ebf803b" target=_blank>什么是局部临时表</A>
<LI><A href="#1e7d8c80-a8fa-4252-8bbf-527c67bdb207" target=_blank>什么是全局临时表</A>
<LI><A href="#4332b6c5-97e7-4fb0-9986-b29a3745eb28" target=_blank>什么是三层结构</A>
<LI><A href="#0f94e1de-ffea-4f7c-aec4-44547f5ad53c" target=_blank>三层结构的目的</A>
<LI><A href="#8ea2af5f-e764-4186-9433-1edd7040a66f" target=_blank>具体的三层是哪三层</A>
<LI><A href="#38bd17f2-53bd-4481-81b6-7d3d438f2341" target=_blank>三层之间的关系</A>
<LI><A href="#25c5b332-f65a-42d6-97da-7792c0dd3c04" target=_blank>三层结构的优缺点</A>
<LI><A href="#c32a2a1b-77df-44c9-970d-be78823c86d7" target=_blank>邮件发送方法</A>
<LI><A href="#b68b3c42-ca88-43c6-ba17-66b9f0dc5c9a" target=_blank>Excel导入导出</A>
<LI><A href="#9c640836-9b0d-49b3-b5b8-0242028ca220" target=_blank>MD5加密解密方法</A>
<LI><A href="#9ab437ba-fb20-459f-aa44-be33c5b6a772" target=_blank>读取数据库后，判断dataset里列的值是否为空</A>
<LI><A href="#6a838262-987a-4eca-8f0f-246cab4eff47" target=_blank>项目术语</A></LI></OL>
<UL style="MARGIN-RIGHT: 0px" dir=ltr>
<LI><A name=4ecddead-b9b7-41a2-a227-f5eb1bb768b5 target=_blank>数据库语言</A>
<UL>
<LI><FONT color=#0000ff>DML(数据操作语言)</FONT>
<UL>
<LI><FONT color=#0000ff>select</FONT>
<LI><FONT color=#0000ff>insert</FONT>
<LI><FONT color=#0000ff>delete</FONT>
<LI><FONT color=#0000ff>update</FONT></LI></UL>
<LI><FONT color=#0000ff>DDL（数据定义语言--建表建库等）</FONT>
<UL>
<LI><FONT color=#0000ff>creat</FONT>
<LI><FONT color=#0000ff>drop</FONT>
<LI><FONT color=#0000ff>alter</FONT></LI></UL>
<LI><FONT color=#0000ff>DCL（数据控制语言）</FONT>
<UL>
<LI><FONT color=#0000ff>grant</FONT>
<LI><FONT color=#0000ff>revoke</FONT></LI></UL></LI></UL>
<LI><A name=930969eb-bb11-49f0-8a1b-9b48fb471981 target=_blank>数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程)</A>
<UL>
<LI><FONT color=#0000ff>安装时有一个客户端管理软件和一个服务器。我们平常操作的是客户端软件，发送脚本到服务器DMSM（数据库服务器），服务器分析和解析并展示执行结果。</FONT></LI></UL>
<LI><A name=32caf1d1-715d-4ae5-aa62-0467fefb9594 target=_blank>主键的概念</A>
<UL>
<LI><FONT color=#0000ff>唯一的标识一行数据操作</FONT>
<LI><FONT color=#0000ff>可以作为其他表的外键来引用</FONT>
<LI><FONT color=#0000ff>业务主键：有意义（例：身份证号）</FONT>
<LI><FONT color=#0000ff>逻辑主键：唯一的意义就是标识一行</FONT></LI></UL>
<LI><A name=baf0409b-64d9-461e-81c2-c027bdc14f15 target=_blank>如何创建主键</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">ALTER<SPAN style="COLOR: windowtext"> </SPAN>TABLE<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[dbo]<SPAN style="COLOR: #808080">.</SPAN>[OperateType]</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">ADD<SPAN style="COLOR: windowtext"> </SPAN>CONSTRAINT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[PK_OperateType]</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">PRIMARY</SPAN> <SPAN style="COLOR: #0000ff">KEY</SPAN> <SPAN style="COLOR: #0000ff">CLUSTERED <SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">[Id]</SPAN></SPAN></SPAN> <SPAN style="COLOR: #0000ff">ASC<SPAN style="COLOR: #808080">);</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">GO</SPAN> </SPAN> </DIV></LI></UL>
<LI><A name=7c68bc65-7678-4a4e-a50f-397edc33e663 target=_blank>如何创建外键</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">ALTER<SPAN style="COLOR: windowtext"> </SPAN>TABLE<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[dbo]<SPAN style="COLOR: #808080">.</SPAN>[RoleAction]</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">ADD<SPAN style="COLOR: windowtext"> </SPAN>CONSTRAINT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[FK_RoleAction_Role]</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">FOREIGN</SPAN> <SPAN style="COLOR: #0000ff">KEY <SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">[Role_RoleId]</SPAN>)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">REFERENCES</SPAN> <SPAN style="COLOR: #008080">[dbo]<SPAN style="COLOR: #808080">.</SPAN>[RoleInfo]</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt"><SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">[RoleId]</SPAN>)</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">ON</SPAN> <SPAN style="COLOR: #0000ff">DELETE</SPAN> <SPAN style="COLOR: #0000ff">NO</SPAN> <SPAN style="COLOR: #0000ff">ACTION</SPAN> <SPAN style="COLOR: #0000ff">ON</SPAN> <SPAN style="COLOR: #0000ff">UPDATE</SPAN> <SPAN style="COLOR: #0000ff">NO</SPAN> <SPAN style="COLOR: #0000ff">ACTION<SPAN style="COLOR: #808080">;</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">GO</SPAN> </LI></UL>
<LI><A name=a8bd5571-bca8-4df2-aae6-c465ab4f94f6 target=_blank>主外键关系的概念以及使用</A>
<UL>
<LI><FONT color=#0000ff>作用</FONT>
<UL>
<LI><FONT color=#0000ff>可以优化查询效率</FONT>
<LI><FONT color=#0000ff>减少数据冗余</FONT>
<LI><FONT color=#0000ff>维护方便</FONT></LI></UL>
<LI><FONT color=#0000ff>两张聊存在依赖数据时，就可以使用主外键来解决，其中将依赖列作为主键的就叫作：主键表；另一个就叫作外键表，外键表的外键列数据取自主键表的主键</FONT>
<LI><FONT color=#0000ff>主外键约束</FONT>
<UL>
<LI><FONT color=#0000ff>为什么</FONT>
<UL>
<LI><FONT color=#0000ff>为了避免两张表的主外键数据出现不一致的情况，需要建立主外键约束关系</FONT></LI></UL>
<LI><FONT color=#0000ff>作用</FONT>
<UL>
<LI><FONT color=#0000ff>当两张表数据修改时出现主外键数据不一致，则报错，拒绝修改</FONT></LI></UL></LI></UL></LI></UL>
<LI><A name=f23edd9a-643d-499e-b498-5074baf8543b target=_blank>数据库的主要类型</A>
<UL>
<LI><FONT color=#0000ff>网状数据库、层次数据库和关系数据库</FONT></LI></UL>
<LI><A name=0cdbff89-a6b7-427b-9eb2-9cec759416e4 target=_blank>数据库的主要数据类型</A>
<UL>
<LI><FONT color=#0000ff>char类型：当储存的数据小于长度时，会自动用空格来补充</FONT>
<LI><FONT color=#0000ff>Nchar类型：使用Unicode编码，任意字符都占两个字节</FONT>
<LI><FONT color=#0000ff>varchar类型：当储存的数据小于长度时，不会自动用空格来表示</FONT>
<LI><FONT color=#0000ff>datetime类型：储存日期时间数据</FONT></LI></UL>
<LI><A name=ab1cdf03-7a71-4912-aaea-b429f784b720 target=_blank>使用SQL语句来创建数据库和表</A>
<UL>
<LI><FONT color=#0000ff>创建数据库</FONT>
<UL>
<LI><FONT color=#0000ff>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>database<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">MySchool</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">on</SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">(</SPAN></DIV>
<DIV><SPAN style="COLOR: #008000; FONT-SIZE: 10pt">--括号一定是圆括号</SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">name<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'MySchool_data'</SPAN>,<SPAN style="COLOR: #008000">--数据库名称</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">filename<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'d:\MySchool_data.mdf'</SPAN>,<SPAN style="COLOR: #008000">--物理文件名</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">size<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">5</SPAN></SPAN>mb<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008000">--初始大小</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">maxsize<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">10</SPAN></SPAN>mb<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008000">--最大大小</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">filegrowth<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">15</SPAN>%<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008000">--主文件增长率</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN></DIV>
<DIV><SPAN style="COLOR: #ff00ff; FONT-SIZE: 10pt">log<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #0000ff">on</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">(</SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">name<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'MySchool_log'</SPAN>,<SPAN style="COLOR: #008000">--日志文件名</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">filename<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'d:\MySchool_log.ldf'</SPAN>,<SPAN style="COLOR: #008000">--日志物理文件名</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">maxsize<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">4</SPAN></SPAN>mb<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008000">--最大大小</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">size<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">2</SPAN></SPAN>mb<SPAN style="COLOR: #808080">,</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">filegrowth<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">1</SPAN></SPAN>mb</SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">go</SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>创建表</FONT>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Student</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">(<SPAN style="COLOR: #008000">--创建学生信息表</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">sId</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #0000ff">identity<SPAN style="COLOR: #808080">(</SPAN></SPAN>1<SPAN style="COLOR: #808080">,</SPAN>1<SPAN style="COLOR: #808080">)</SPAN> <SPAN style="COLOR: #0000ff">primary</SPAN> <SPAN style="COLOR: #0000ff">key<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008000">--自动编号</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sClassId</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #808080">null,</SPAN> <SPAN style="COLOR: #008000">--班级外键</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sName</SPAN> <SPAN style="COLOR: #0000ff">nvarchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>50<SPAN style="COLOR: #808080">)</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #808080">null,</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sAge</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #808080">null,</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sNo</SPAN> <SPAN style="COLOR: #0000ff">numeric<SPAN style="COLOR: #808080">(</SPAN></SPAN>18<SPAN style="COLOR: #808080">,</SPAN>0<SPAN style="COLOR: #808080">),<SPAN style="COLOR: #008000">--身份证号，十八位数字，小数为</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sSex</SPAN> <SPAN style="COLOR: #0000ff">char<SPAN style="COLOR: #808080">(</SPAN></SPAN>2<SPAN style="COLOR: #808080">)</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #808080">null,</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">sEmail</SPAN> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>50<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN> </LI></UL></LI></UL>
<LI><A name=e49aebd1-4ba2-4a3b-be8d-9710b06f43be target=_blank>约束分类</A>
<UL>
<LI><FONT color=#0000ff>非空约束</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Employees</SPAN> </SPAN>alter<SPAN style="COLOR: windowtext"> </SPAN>column<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">EmpName</SPAN> </SPAN>varchar<SPAN style="COLOR: #808080">(<SPAN style="COLOR: windowtext">50</SPAN>)<SPAN style="COLOR: windowtext"> </SPAN>not<SPAN style="COLOR: windowtext"> </SPAN>null</SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>主键约束</FONT>
<UL>
<LI><FONT color=#0000ff>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Score</SPAN> </SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">add<SPAN style="COLOR: windowtext"> </SPAN>constraint<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">PK_Score</SPAN> </SPAN>primary<SPAN style="COLOR: windowtext"> </SPAN>key<SPAN style="COLOR: #808080">(</SPAN>sId<SPAN style="COLOR: #808080">)</SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>唯一约束</FONT>
<UL>
<LI><FONT color=#0000ff>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">add<SPAN style="COLOR: windowtext"> </SPAN>constraint<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UQ_student</SPAN> </SPAN>unique<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">sNo</SPAN>)</SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>默认约束</FONT>
<UL>
<LI><FONT color=#0000ff>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">add<SPAN style="COLOR: windowtext"> </SPAN>constraint<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">DF_student</SPAN> </SPAN>default<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #ff0000">'男'</SPAN>)<SPAN style="COLOR: windowtext"> </SPAN></SPAN>for<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">sSex</SPAN></SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>检测约束</FONT>
<UL>
<LI><FONT color=#0000ff>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">add<SPAN style="COLOR: windowtext"> </SPAN>constraint<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">CK_student</SPAN> </SPAN>check<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">sAge<SPAN style="COLOR: windowtext"> </SPAN></SPAN>>=<SPAN style="COLOR: windowtext">18 </SPAN>and<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">sAge</SPAN> </SPAN><=<SPAN style="COLOR: windowtext">100</SPAN>)</SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>外键约束</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">alter<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Employees</SPAN> </SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">add<SPAN style="COLOR: windowtext"> </SPAN>constraint<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">FK_DeptId_DepId</SPAN> </SPAN>foreign<SPAN style="COLOR: windowtext"> </SPAN>key<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">DeptId</SPAN>)<SPAN style="COLOR: windowtext"> </SPAN></SPAN>references<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Department<SPAN style="COLOR: #808080">(</SPAN>DepId<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></SPAN> </SPAN> </FONT></LI></UL></LI></UL>
<LI><A name=5b0af399-a3f4-499c-917d-8254e6080b29 target=_blank>top的使用</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> </SPAN>top<SPAN style="COLOR: windowtext"> 2 <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN></SPAN></SPAN></DIV>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt"><SPAN style="COLOR: windowtext"><SPAN style="COLOR: #008080"></SPAN></SPAN></SPAN><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> </SPAN>top<SPAN style="COLOR: windowtext"> 20 </SPAN>percent<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher<SPAN style="COLOR: #008000">--按百分比算行数，如果出现小数，则+1（类似天花板函数celling）</SPAN></SPAN></SPAN></SPAN> </DIV></LI></UL>
<LI><A name=976a26dd-750a-40c7-8aec-11b277c064a8 target=_blank>Distinct的使用(去除重复数)</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> </SPAN>distinct<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">age</SPAN>)<SPAN style="COLOR: windowtext"> </SPAN></SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> <SPAN style="COLOR: #008000">--求出不重复的age</SPAN></SPAN></SPAN></DIV>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt"><SPAN style="COLOR: windowtext"><SPAN style="COLOR: #008000"></SPAN></SPAN></SPAN><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> </SPAN>distinct<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">age<SPAN style="COLOR: #808080">,</SPAN>salary</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> <SPAN style="COLOR: #008000">--求出不重复的age和salary的组合（相当于将两个列的值加在一起的值不重复）</SPAN></SPAN></SPAN> </DIV></LI></UL>
<LI><A name=e2de275e-7cbf-4454-a039-52d37ba7b17a target=_blank>聚合函数</A>
<UL>
<LI><FONT color=#0000ff>MAX（最大数）</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">max<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">UserId</SPAN>),<SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">dbo<SPAN style="COLOR: #808080">.</SPAN>UserInfo</SPAN> </SPAN>GROUP<SPAN style="COLOR: windowtext"> </SPAN>BY<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>MIN（最小数）</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">min<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">UserId</SPAN>),<SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">dbo<SPAN style="COLOR: #808080">.</SPAN>UserInfo</SPAN> </SPAN>GROUP<SPAN style="COLOR: windowtext"> </SPAN>BY<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>AVG（平均数）</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">avg<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">UserId</SPAN>),<SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">dbo<SPAN style="COLOR: #808080">.</SPAN>UserInfo</SPAN> </SPAN>GROUP<SPAN style="COLOR: windowtext"> </SPAN>BY<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>SUM（和）</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">sum<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">UserId</SPAN>),<SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">dbo<SPAN style="COLOR: #808080">.</SPAN>UserInfo</SPAN> </SPAN>GROUP<SPAN style="COLOR: windowtext"> </SPAN>BY<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </LI></UL>
<LI>COUNT（计数）
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">COUNT<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">UserId</SPAN>),<SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">dbo<SPAN style="COLOR: #808080">.</SPAN>UserInfo</SPAN> </SPAN>GROUP<SPAN style="COLOR: windowtext"> </SPAN>BY<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserId</SPAN></SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=389f0c62-84e8-43d3-8db1-f2be25395b4a target=_blank>聚合函数注意事项</A>
<UL>
<LI><FONT color=#0000ff>聚合函数对NULL值不计算</FONT>
<LI><FONT color=#0000ff>聚合函数的结果集是单个的值，没办法与多个值的结果集集合</FONT>
<LI><FONT color=#0000ff>不能整除的情况</FONT>
<UL>
<LI><FONT color=#0000ff>该列是小数列：如果除出来是小数，则返回带小数的结果</FONT>
<LI><FONT color=#0000ff>该列是整数列：如果除出来是小数，也是直接当整数算，不会自动+1</FONT></LI></UL></LI></UL>
<LI><A name=01ecc43c-7ba1-4811-86f8-3e7b1af7c343 target=_blank>between and 和 in 的使用</A>
<UL>
<LI><FONT color=#0000ff>betwenn and</FONT>
<UL>
<LI><FONT color=#0000ff>求两个值的区间，推荐使用between and</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserInfo</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">ID</SPAN> <SPAN style="COLOR: #808080">between</SPAN> 40 <SPAN style="COLOR: #808080">and</SPAN> 50</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>in</FONT>
<UL>
<LI><FONT color=#0000ff>当使用子查询配合in关键字时，子查询的结果集必须只有一个列，而且列的类型必须和条件列类型一直</FONT>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserInfo</SPAN> </SPAN>Where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">ID</SPAN> <SPAN style="COLOR: #808080">in</SPAN></SPAN> <SPAN style="COLOR: #808080">(<SPAN style="COLOR: windowtext">40</SPAN>,<SPAN style="COLOR: windowtext">41</SPAN>)</SPAN></SPAN> </FONT>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">id</SPAN> <SPAN style="COLOR: #808080">in</SPAN></SPAN> <SPAN style="COLOR: #808080">(</SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">id</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Score</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">English<SPAN style="COLOR: #808080">></SPAN></SPAN>98 <SPAN style="COLOR: #808080">and</SPAN> <SPAN style="COLOR: #008080">Math<SPAN style="COLOR: #808080">></SPAN></SPAN> 98<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN> </FONT></LI></UL></LI></UL>
<LI><A name=eb1109a5-b9e6-4732-978a-a83d1f74214d target=_blank>like, not like 通配符(%,_,[],^)</A>
<UL>
<LI><FONT color=#0000ff>^只有MSSQLServer支持，其他DBMS用not like,且只能放在[]里用</FONT>
<LI><FONT color=#0000ff>not like 不取like的值</FONT>
<LI><FONT color=#0000ff>_ 代表单个任意字符；</FONT>
<LI><FONT color=#0000ff>% 代表任意长度任意字符</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">Select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">sName</SPAN> <SPAN style="COLOR: #808080">like</SPAN> <SPAN style="COLOR: #ff0000">'张%'</SPAN></SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>[] 代表取值范围内的单个字符</FONT>
<LI><FONT color=#0000ff>^ 取非符号，必须和[]连用</FONT></LI></UL>
<LI><A name=41a43486-1ac4-4297-a5c5-c526eab70195 target=_blank>空值处理：null 是什么？</A>
<UL>
<LI><FONT color=#0000ff>查询列中null的值用is关键字</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN></SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">name</SPAN> <SPAN style="COLOR: #808080">is</SPAN> <SPAN style="COLOR: #808080">NULL</SPAN></SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>isnull函数</FONT>
<UL>
<LI><FONT color=#0000ff>如果第一个参数为null，则使用第二个参数作为返回值，否则，返回第一个参数</FONT>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">ISNULL</SPAN></SPAN> <SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">Email<SPAN style="COLOR: windowtext"> </SPAN></SPAN>,<SPAN style="COLOR: #ff0000">'aaa'</SPAN>)</SPAN>as<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Email</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserInfo</SPAN></SPAN></SPAN> </FONT></LI></UL></LI></UL>
<LI><A name=db4f73e2-8f53-42b6-9299-b6371208ad39 target=_blank>排序(order by id asc / desc)(默认是哪一种排序？)</A>
<UL>
<LI><FONT color=#0000ff>order by 子句要放到where子句之后</FONT>
<LI><FONT color=#0000ff>asc--默认是升序</FONT>
<UL>
<LI><SPAN style="FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserInfo</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Sort<SPAN style="COLOR: #808080">=</SPAN></SPAN>0 </SPAN>order<SPAN style="COLOR: windowtext"> </SPAN>by<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">id</SPAN> </SPAN>asc</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>desc--降序</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">UserInfo</SPAN> </SPAN>order<SPAN style="COLOR: windowtext"> </SPAN>by<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> </SPAN>desc<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008080">ID<SPAN style="COLOR: windowtext"> </SPAN></SPAN></SPAN>desc</SPAN>
<LI><FONT color=#0000ff>多条件排序时，如上，当Phone中有相等的值，那么这些值按ID排序</FONT></LI></UL></LI></UL>
<LI><A name=37329ac3-6a5c-4a01-a547-b6a5fe86b471 target=_blank>分组(group by ),单条件分组，多条件分组（分组时，要注意的事情[位置，列]）</A>
<UL>
<LI><FONT color=#0000ff>根据性别统计男女的人数</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">count<SPAN style="COLOR: #808080">(*)</SPAN></SPAN> <SPAN style="COLOR: #008080">gender</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> </SPAN>group<SPAN style="COLOR: windowtext"> </SPAN>by<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">gender</SPAN></SPAN></SPAN>
<LI><FONT color=#0000ff>group by 子句必须放到where语句之后，group by 与 order by 都是对筛选后的数据进行处理，而where是用来筛选数据的</FONT></LI></UL>
<LI><FONT color=#0000ff>多条件分组</FONT>
<UL>
<LI><FONT color=#0000ff>分组条件，是age和gender列的组合，只有当age和gender一样的值时，才分为一组</FONT>
<LI><FONT color=#0000ff><SPAN style="FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">count<SPAN style="COLOR: #808080">(*),<SPAN style="COLOR: #008080">age</SPAN>,</SPAN></SPAN> <SPAN style="COLOR: #008080">gender</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN> </SPAN>group<SPAN style="COLOR: windowtext"> </SPAN>by<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">age<SPAN style="COLOR: #808080">,</SPAN>gender</SPAN></SPAN></SPAN></SPAN> </FONT></LI></UL>
<LI><FONT color=#0000ff>分组一般和聚合函数使用的</FONT>
<LI><FONT color=#0000ff>分组的结果是组信息，与表里的当行信息无关</FONT>
<LI><FONT color=#0000ff>一旦出现分组，前面要么是聚合函数，要么是分组的条件</FONT></LI></UL>
<LI><A name=9b2dfc8e-3df0-4e0d-9bfe-e5a9fa1d9d84 target=_blank>筛选(Having的使用)，它和where的区别</A>
<UL>
<LI><FONT color=#0000ff>having是group by的条件对分组后的数据进行筛选（与where类似，都是筛选，只不过having是用来筛选分组后的组）</FONT>
<LI><FONT color=#0000ff>在where中不能使用聚合函数，必须使用having，having要位于group by之后</FONT>
<LI><FONT color=#0000ff>having的使用几乎是与where一样的，也可以用in</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">sClassId<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #ff00ff">count</SPAN>(</SPAN>sName<SPAN style="COLOR: #808080">)</SPAN></SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN> </SPAN>group<SPAN style="COLOR: windowtext"> </SPAN>by<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">sClassId</SPAN> </SPAN>having<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">count<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">sName</SPAN>)></SPAN></SPAN>3</SPAN></SPAN> </LI></UL>
<LI>类型转换(CAST，CONVERT)
<UL>
<LI><FONT color=#0000ff>cast</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">cast<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">salary</SPAN></SPAN></SPAN> </SPAN>as<SPAN style="COLOR: windowtext"> </SPAN>int<SPAN style="COLOR: #808080">)<SPAN style="COLOR: windowtext"> </SPAN></SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>convert</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">convert<SPAN style="COLOR: #808080">(</SPAN></SPAN></SPAN>int<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008080">salary</SPAN>)<SPAN style="COLOR: windowtext"> </SPAN></SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">teacher</SPAN></SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=98311d44-e5e7-449b-a24a-873270d903c2 target=_blank>union,union all的使用</A>
<UL>
<LI><FONT color=#0000ff>是对两个集合操作的，两个集合必须具有相同的列数，列具有相同的数据类型（至少能隐式转换的），最终输出的集合的列名由第一个集合的列名来确定（可以用来连接多个结果）</FONT>
<LI><FONT color=#0000ff>要union的两个结果集的列数必须一样</FONT>
<LI><FONT color=#0000ff>要连接的两个列的类型鼻血一直（或者类型兼容，比如varchar和nchar）</FONT>
<LI><FONT color=#0000ff>union默认去除了结果集中重复数据</FONT>
<LI><FONT color=#0000ff>而union all 不会删除重复的数据；（因此，union all 的效率要比union的效果高一些）</FONT></LI></UL>
<LI><A name=4a48cfbe-6678-4c15-ac52-2fc00c145dc6 target=_blank>一次插入多条数据</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">insert<SPAN style="COLOR: windowtext"> </SPAN>into<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Score<SPAN style="COLOR: #808080">(</SPAN>studentId<SPAN style="COLOR: #808080">,</SPAN>english<SPAN style="COLOR: #808080">,</SPAN>math<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> 1<SPAN style="COLOR: #808080">,</SPAN>80<SPAN style="COLOR: #808080">,</SPAN>100 </SPAN>union</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> 1<SPAN style="COLOR: #808080">,</SPAN>80<SPAN style="COLOR: #808080">,</SPAN>100 </SPAN>union</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> 3<SPAN style="COLOR: #808080">,</SPAN>50<SPAN style="COLOR: #808080">,</SPAN>59 </SPAN>union<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">all</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> 4<SPAN style="COLOR: #808080">,</SPAN>66<SPAN style="COLOR: #808080">,</SPAN>89 </SPAN>union</SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> 5<SPAN style="COLOR: #808080">,</SPAN>59<SPAN style="COLOR: #808080">,</SPAN>100</SPAN></SPAN> </LI></UL>
<LI><A name=133dc11a-872f-4105-94a6-79cda4da6eaa target=_blank>字符串函数</A>
<UL>
<LI><FONT color=#0000ff>LEN()</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">len<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #ff0000">'字符串的长度'</SPAN>)</SPAN></SPAN></SPAN></SPAN>
<UL>
<LI><FONT color=#0000ff>结果是6</FONT></LI></UL>
<LI><FONT color=#0000ff>LEN返回的是去掉尾部空格后的字符数</FONT></LI></UL>
<LI><FONT color=#0000ff>Datalength()</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">datalength<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #ff0000">'字符串的长度'</SPAN>)</SPAN></SPAN></SPAN></SPAN> </FONT>
<UL>
<LI><FONT color=#0000ff>结果是12</FONT></LI></UL>
<LI><FONT color=#0000ff>返回的是包含尾部空格的字符串的字节数</FONT></LI></UL>
<LI><FONT color=#0000ff>LOWER()</FONT>
<UL>
<LI><FONT color=#0000ff><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff00ff">lower<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #ff0000">'JJJJJj'</SPAN>)</SPAN></SPAN></SPAN></SPAN> </FONT>
<LI><FONT color=#0000ff>转小写</FONT></LI></UL>
<LI><FONT color=#0000ff>UPPER()</FONT>
<UL>
<LI><FONT color=#0000ff>转大写</FONT></LI></UL>
<LI><FONT color=#0000ff>LTRIM()</FONT>
<UL>
<LI><FONT color=#0000ff>去除左边空格</FONT></LI></UL>
<LI><FONT color=#0000ff>RTRIM()</FONT>
<UL>
<LI><FONT color=#0000ff>去除右边空格</FONT></LI></UL>
<LI><FONT color=#0000ff>LEFT()</FONT>
<UL>
<LI><FONT color=#0000ff>从左边取指定长度字符串</FONT></LI></UL>
<LI><FONT color=#0000ff>RIGHT()</FONT>
<UL>
<LI><FONT color=#0000ff>从右边取指定长度字符串</FONT></LI></UL>
<LI><FONT color=#0000ff>SUBSTRING(string,start , length) </FONT>
<UL>
<LI><FONT color=#0000ff>从指定位置取指定长度字符串</FONT></LI></UL></LI></UL>
<LI><A name=213d020a-6b9a-42e1-9c3c-be043d73a13f target=_blank>ADO.NET主要类</A>
<UL>
<LI><FONT color=#0000ff>Connection</FONT>
<UL>
<LI><FONT color=#0000ff>主要是开启程序和数据库之间的连接，没有利用连接对象将数据库打开，是无法从数据库中取得数据的</FONT>
<LI><FONT color=#0000ff>Close和Dispose的区别</FONT>
<UL>
<LI><FONT color=#0000ff>Close以后还可以Open，Dispose以后则不能再用</FONT></LI></UL></LI></UL>
<LI><FONT color=#0000ff>Command</FONT>
<UL>
<LI><FONT color=#0000ff>主要可以用来对数据库发出一些指令，例如可以对数据库下达查询、新增、修改、删除数据等指令，以及调用存在数据库中的存储过程等，这个对象是架构在Connection对象上，也就是Command对象是通过连接到数据源</FONT></LI></UL>
<LI><FONT color=#0000ff>DataAdapter</FONT>
<UL>
<LI><FONT color=#0000ff>主要是在数据源以及DataSet之间执行数据传输的工作，它可以透过Command对象下达命令后，并将取得的数据放入DataSet对象中，这个对象是架构在Command对象上，并提供了许多配合DataSet使用的功能</FONT></LI></UL>
<LI><FONT color=#0000ff>DataSet</FONT>
<UL>
<LI><FONT color=#0000ff>这个对象可以视为一个暂存区（Cache），可以把从数据库中查询到的数据保存起来，试着可以将整个数据库显示出来，DataSet是放在内存中的，DataSet的能力不只是可以存储多个Table而已，还可以透过DataAdapter对象取得一些例如主键等的数据结构，并可以记录数据表间的关联。DataSet对象可以说是ADO.NET中重量级的对象，这个对象架构在DataAdapter对象上，本身不具有和数据源沟通的能力，也就是说我们是将DataAdapter对象当作DataSet对象及数据源间传输数据的桥梁，DataSet包含诺干DataTable,DataTable包含诺干DataRow</FONT></LI></UL>
<LI><FONT color=#0000ff>DataReader</FONT>
<UL>
<LI><FONT color=#0000ff>当我们只需要循序的读取数据而不需要其它操作时，可以使用 DataReader 对象。DataReader 对象只是一次一笔向下循序的读取数据源中的数据，这些数据是存在数据库服务器中的，而不是一次性加载到程序的内存中的，只能（通过游标）读取当前行的数据，而且这些数据是只读的，并不允许作其它的操作。因为 DataReader 在读取数据的时候限制了每次只读取一笔，而且只能只读，所以使用起来不但节省资源而且效率很好。使用DataReader 对象除了效率较好之外，因为不用把数据全部传回，故可以降低网络的负载。 ADO.NET 使用 Connection 对象来连接数据库，使用 Command 或 DataAdapter 对象来执行SQL 语句，并将执行的结果返回给 DataReader 或 DataAdapter ,然后再使用取得的DataReader 或 DataAdapter 对象操作数据结果。</FONT></LI></UL></LI></UL>
<LI><A name=64fb5bb6-5240-451d-a45a-0f4d78027694 target=_blank>数据库连接字符串</A>
<UL>
<LI><FONT color=#0000ff>server=.;database='dbName';uid=sa;pwd=123</FONT></LI></UL>
<LI><A name=674956c1-d477-4558-a9ab-9168b20304cf target=_blank>SqlConnection类的State属性</A>
<UL>
<LI><FONT color=#0000ff>是判断数据库连接状态的一组枚举值</FONT></LI></UL>
<LI><A name=3ca1c6aa-e862-4929-97d6-979d4a46d1d4 target=_blank>SqlCommand类的方法：</A>
<UL>
<LI><FONT color=#0000ff>ExecuteNonQuery()</FONT>
<UL>
<LI><FONT color=#0000ff>执行非查询语句（增、删、改），返回受影响函数</FONT></LI></UL>
<LI><FONT color=#0000ff>ExecuteScalar()</FONT>
<UL>
<LI><FONT color=#0000ff>返回查询结果集的首行首列，是一个Object类型。</FONT></LI></UL>
<LI><FONT color=#0000ff>ExecuteReader()</FONT>
<UL>
<LI><FONT color=#0000ff>返回一个游标指针，然后用read（）方法一行一行的读。性能很好。</FONT></LI></UL></LI></UL>
<LI><A name=d2a4fc2c-bdff-4ccd-bdc9-fa981d15f063 target=_blank>StatementCompleted事件的触发</A>
<UL>
<LI><FONT color=#0000ff>每条SQL语句执行完后会触发</FONT></LI></UL>
<LI><A name=bc51f327-375c-4eee-a164-b3e1bffac847 target=_blank>获得刚刚插入数据库的自增id</A>
<UL>
<LI><FONT color=#0000ff>方法一：string strSql="Insert into Student(name,age,cid) values ('{0}','{1}','{2}'); Select @@identity;" </FONT>
<LI><FONT color=#0000ff>方法二：string strSql="Insert into Student(name,age,cid) output inserted.id values('{0}','{1}','{2}') "</FONT></LI></UL>
<LI><A name=c99e6a02-326a-4d8e-8568-9cdd79a5e763 target=_blank>Sql注入攻击(避免方式？)</A>
<UL>
<LI><FONT color=#0000ff>参数化查询，SqlParameter</FONT></LI></UL>
<LI><A name=3f2505fc-8010-4b91-8b6f-6763cfd6562f target=_blank>如何使用迭代生成树形菜单</A>
<UL>
<LI><CODE>
<DIV><FONT color=#0000ff><B>void</B></FONT> BuildTree(TreeNode node, List<FONT color=#333399><</FONT>Category<FONT color=#333399>></FONT> list) <BR>{ <BR><FONT color=#008000> //获得父元素的ID</FONT> <BR><FONT color=#0000ff><B> int</B></FONT> pId <FONT color=#333399>=</FONT> Convert.ToInt32(node.Tag); <BR><FONT color=#0000ff><B> foreach</B></FONT> (Category model <FONT color=#0000ff><B>in</B></FONT> list) <BR> { <BR><FONT color=#008000> //找出集合中 父ID 和传入参数一致的</FONT><BR> <FONT color=#0000ff><B>if</B></FONT> (model.TParentId <FONT color=#333399>==</FONT> pId) <BR> { <BR><FONT color=#008000> //创建子节点</FONT><BR> TreeNode sonNode <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> TreeNode(model.TName); <BR><FONT color=#008000> //将子节点自己的ID存入 节点的 TAG中</FONT> <BR> sonNode.Tag <FONT color=#333399>=</FONT> model.TId;<BR> <FONT color=#008000>//将子节点 添加到父节点中</FONT><BR> node.Nodes.Add(sonNode); <BR><FONT color=#008000> //迭代 调用（看看子节点还有没有子节点）</FONT><BR> BuildTree(sonNode, list); <BR> }<BR> }<BR> } </DIV></CODE></LI></UL><CODE>
<LI></CODE><A name=cbbbacaa-ce96-4d80-88e3-8b252f1df73a target=_blank>单例模式(单例模式的创建)</A>
<UL>
<LI><FONT color=#0000ff>关闭要实现单例模式的类的构造函数（私有化构造函数）</FONT>
<LI><FONT color=#0000ff>在类中添加一个私有的类的静态变量</FONT>
<LI><FONT color=#0000ff>在类中添加一个共有的方法，返回当前的静态变量，在方法中判断静态变量是否为null，如果为null则先new在返回</FONT>
<LI><CODE>
<DIV><FONT color=#0000ff><B>public</B></FONT> Person GetSingleInstance() <BR> { <BR> <FONT color=#008000>//在多线程情况下，需要 加锁</FONT> <BR><FONT color=#008000> //这里：第一，直接锁当前的对象； 第二，定义一个线程 锁的标识volatile，然后锁住标识</FONT> <BR><FONT color=#0000ff><B> lock</B></FONT>(_mySinglePerson) <BR> { <BR><FONT color=#0000ff><B> if</B></FONT>(_mySinglePerson <FONT color=#333399>==</FONT> <FONT color=#0000ff><B>null</B></FONT> <FONT color=#333399>||</FONT> _mySinglePerson.IsDisPosed) <BR> { <BR> _mySinglePerson <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> Person(); <BR> } <BR> } <BR> <FONT color=#0000ff><B>return</B></FONT> _mySinglePerson; <BR> }</DIV></CODE></LI></UL>
<LI><A name=41b5aec3-66a6-4d9d-a6ab-f6c6ea3067b3 target=_blank>DataTable的使用</A>
<UL>
<LI><CODE>
<DIV>DataTable dt <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> DataTable（）；<BR><FONT color=#0000ff><B>foreach</B></FONT>（DataRow row <FONT color=#0000ff><B>in</B></FONT> dt.Rows）<BR>{<BR><FONT color=#0000ff><B>string</B></FONT> str <FONT color=#333399>=</FONT> row[<FONT color=#ff00bf>"columnName"</FONT>].ToString();<BR> ....<BR>}</DIV></CODE></LI></UL>
<LI><A name=a737038b-4b6b-4ee4-868d-6fa49349cd50 target=_blank>类SqlDataAdapter的使用(重点)</A>
<UL>
<LI><FONT color=#0000ff>Fill()方法</FONT>
<LI><FONT color=#0000ff>注意：SqlDataAdapter的内部还是使用了SqlDataRader去读取数据，只不过读取的过程微软帮我们封装了</FONT></LI></UL>
<LI><A name=6634b96c-4075-475a-b86e-2f581fcd518f target=_blank>类SqlCommandBuilder的使用(注意：他必须操作的是有主键的数据库)</A>
<UL>
<LI><FONT color=#0000ff>往里边传入一个DataAdapter对象，然后可以用相应方法直接执行对表的操作，使用相当简单，功能也很强大，主要是微软为我们做了一系列封装，简化了程序员的操作（但一般我们都不使用这种方式对数据表进行操作）</FONT>
<LI><CODE>
<DIV><FONT color=#008000>//SQL命令生成助手（适配器）</FONT> <BR>SqlCommandBuilder scb<FONT color=#333399>=</FONT><FONT color=#0000ff><B>new</B></FONT> SqlCommandBuilder(da); <BR><FONT color=#008000>//适配器在助手生成的SQL语句帮助下，成功的修改类数据库 da.Update(ds,"tempTable");</FONT> <BR> MessageBox.Show(<FONT color=#ff00bf>"修改成功"</FONT>)</CODE><CODE></DIV></CODE></LI></UL>
<LI><CODE>
<DIV></CODE><A name=f93511ec-b8b2-4511-9940-b752489b0678 target=_blank>提取查询语句，封装SqlHelper类(不仅要会，而且要理解思想)</A> </DIV>
<LI><A name=ed3125a5-3e3b-4360-8997-afde50ff5127 target=_blank></A>
<LI><A name=51ecbb35-4f72-4f58-a464-3e5a2b15c424 target=_blank>SQL中的Switch语句</A>
<UL>
<LI><FONT color=#0000ff>直接对列进行值的判断</FONT>
<UL>
<LI><FONT color=#0000ff>then的值必须是同一种类型（因为列的类型只能有一个）</FONT>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pId<SPAN style="COLOR: #808080">,</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">case<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeId</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> 1 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'a'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> 2 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'b'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">else</SPAN> <SPAN style="COLOR: #ff0000">'c'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end<SPAN style="COLOR: #808080">,</SPAN></SPAN></SPAN></DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">pName</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">PhoneNum</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>一般用来为列的取值范围</FONT>
<UL>
<LI><FONT color=#0000ff>case不一定非要和列在一起判断</FONT>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">studentId<SPAN style="COLOR: #808080">,</SPAN>成绩<SPAN style="COLOR: #808080">=(</SPAN></SPAN> </SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">case</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008080">english</SPAN> <SPAN style="COLOR: #808080">between</SPAN> 90 <SPAN style="COLOR: #808080">and</SPAN> 100 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'A'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008080">english</SPAN> <SPAN style="COLOR: #808080">between</SPAN> 80 <SPAN style="COLOR: #808080">and</SPAN> 89 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'B'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008080">english</SPAN> <SPAN style="COLOR: #808080">between</SPAN> 70 <SPAN style="COLOR: #808080">and</SPAN> 79 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'C'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008080">english</SPAN> <SPAN style="COLOR: #808080">between</SPAN> 60 <SPAN style="COLOR: #808080">and</SPAN> 69 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'D'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008080">english</SPAN> <SPAN style="COLOR: #808080"><</SPAN> 60 <SPAN style="COLOR: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">'E'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">else</SPAN> <SPAN style="COLOR: #ff0000">'缺考'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">score</SPAN></SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=3343884e-c40c-43c2-88e6-ffa77317fec6 target=_blank>SQL中的子查询</A>
<UL>
<LI><FONT color=#0000ff>在结果集的基础上，再次查询，一定要给结果集取别名，否则会报错</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from <SPAN style="COLOR: #808080">(</SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">bc<SPAN style="COLOR: #808080">)</SPAN></SPAN> </SPAN>as<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">temp</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=175d76f9-c9b1-44a0-9920-6ee521a7d4fc target=_blank>SQL中的Exists用法</A>
<UL>
<LI><FONT color=#0000ff>先执行的是主查询，然后再执行子查询，将匹配行的数据显示出来</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">tbl</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">exists(</SPAN></SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">phonType</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">tbl<SPAN style="COLOR: #808080">.</SPAN>pid<SPAN style="COLOR: #808080">=</SPAN>phonType<SPAN style="COLOR: #808080">.</SPAN>pid<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>判断结果集是否存在，但效率低</FONT>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">if<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">exists(</SPAN></SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">tbl</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">ptId<SPAN style="COLOR: #808080">=</SPAN></SPAN>1<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> 1</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">else</SPAN></DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> 2</SPAN> </LI></UL></LI></UL>
<LI><A name=682381ee-3f5a-4a54-8bab-177fb17c1585 target=_blank>SQL中的Any</A>
<UL>
<LI><FONT color=#0000ff>Any相当于条件是很多个or</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeID<SPAN style="COLOR: #808080">=any(</SPAN></SPAN></SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pType</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">b<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></SPAN>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeID<SPAN style="COLOR: #808080">=</SPAN></SPAN>1 <SPAN style="COLOR: #808080">or</SPAN> <SPAN style="COLOR: #008080">pTypeID<SPAN style="COLOR: #808080">=</SPAN></SPAN>2</SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=49db626d-691f-47f5-89ee-87d12467e245 target=_blank>SQL中的All</A>
<UL>
<LI><FONT color=#0000ff>All相当于条件是很多个and</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeId<SPAN style="COLOR: #808080">=all(</SPAN></SPAN></SPAN>select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeId</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">b<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></SPAN>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">pTypeId<SPAN style="COLOR: #808080">=</SPAN></SPAN>1 <SPAN style="COLOR: #808080">and</SPAN> <SPAN style="COLOR: #008080">pTypeId<SPAN style="COLOR: #808080">=</SPAN></SPAN>2</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>any、in、all匹配结果集时，结果集只能有一个列</FONT></LI></UL>
<LI><A name=5327355e-f1b5-4a39-91df-0f75a4846751 target=_blank>SQL2008 微软官方推荐分页方式</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: #808080">(</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #ff00ff">row_number<SPAN style="COLOR: #808080">()</SPAN></SPAN> <SPAN style="COLOR: #0000ff">over<SPAN style="COLOR: #808080">(</SPAN>order</SPAN> <SPAN style="COLOR: #0000ff">by</SPAN> <SPAN style="COLOR: #008080">ar_id<SPAN style="COLOR: #808080">)<SPAN style="COLOR: #0000ff">as</SPAN></SPAN></SPAN> <SPAN style="COLOR: #008080">num<SPAN style="COLOR: #808080">,*<SPAN style="COLOR: #0000ff">from</SPAN></SPAN></SPAN> <SPAN style="COLOR: #008080">area</SPAN></SPAN></DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)<SPAN style="COLOR: #0000ff">as<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">t</SPAN> </SPAN>where<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">runm</SPAN> </SPAN></SPAN>between<SPAN style="COLOR: windowtext"> 10 </SPAN>and<SPAN style="COLOR: windowtext"> 14</SPAN></SPAN> </LI></UL>
<LI><A name=8e295adb-8c4a-46f0-85f0-dda41f6530cb target=_blank>SQL中表等值连接(内连接) inner join</A>
<UL>
<LI><FONT color=#0000ff>任何一方都必须满足连接条件，如果有一方不满足连接条件就不显示</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> <SPAN style="COLOR: #808080">inner</SPAN> <SPAN style="COLOR: #808080">join</SPAN> <SPAN style="COLOR: #008080">PhoneType</SPAN> </SPAN>on<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">n<SPAN style="COLOR: #808080">.</SPAN>pTypeId<SPAN style="COLOR: #808080">=</SPAN>t<SPAN style="COLOR: #808080">.</SPAN>ptId</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=7d4766ad-a365-41a3-baf7-7b3b278c0807 target=_blank>SQL中表左连接(左外连接) left join</A>
<UL>
<LI><FONT color=#0000ff>保证左边的数据都有，根据左边数据匹配，显示Join左边表的所有记录，右侧表中符合条件的显示，不符合条件的就显示null</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Phone</SPAN> <SPAN style="COLOR: #008080">n</SPAN> <SPAN style="COLOR: #808080">left</SPAN> <SPAN style="COLOR: #808080">join</SPAN> <SPAN style="COLOR: #008080">PhoneType</SPAN> <SPAN style="COLOR: #008080">t</SPAN> </SPAN>on<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">n<SPAN style="COLOR: #808080">.</SPAN>pTypeId<SPAN style="COLOR: #808080">=</SPAN>t<SPAN style="COLOR: #808080">.</SPAN>ptId</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=178e35f6-f356-4e42-b3ac-692ed135588e target=_blank>SQL中表右连接(右外连接) right join</A>
<UL>
<LI><FONT color=#0000ff>保证右边的数据都有，根据右边数据匹配</FONT>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">PhoneNum</SPAN> <SPAN style="COLOR: #008080">n</SPAN> <SPAN style="COLOR: #808080">right</SPAN> <SPAN style="COLOR: #808080">join</SPAN> <SPAN style="COLOR: #008080">PhoneType</SPAN> <SPAN style="COLOR: #008080">t</SPAN> </SPAN>on<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">n<SPAN style="COLOR: #808080">.</SPAN>pTypeId</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #008080">t<SPAN style="COLOR: #808080">.</SPAN>ptId</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=920ba0fe-468a-4dd7-8ab3-68e16fc516f6 target=_blank>SQL中表交叉连接(两张表的乘积)</A>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">student</SPAN> <SPAN style="COLOR: #808080">cross</SPAN> <SPAN style="COLOR: #808080">join</SPAN> <SPAN style="COLOR: #008080">Score</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=2f6dbd7f-0c9c-45a2-be17-de1206bc0074 target=_blank>SQL中表全连接 full join</A>
<UL>
<LI><FONT color=#0000ff>左右两边的数据都进行匹配，相当于左连接和右连接相加和inner join刚好相反</FONT>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">PhoneNum</SPAN> <SPAN style="COLOR: #008080">n</SPAN> </SPAN>full<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">join</SPAN> <SPAN style="COLOR: #008080">PhoneType</SPAN> <SPAN style="COLOR: #008080">t</SPAN> </SPAN>on<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">n<SPAN style="COLOR: #808080">.</SPAN>pTypeId</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #008080">t<SPAN style="COLOR: #808080">.</SPAN>ptId</SPAN></SPAN></SPAN></DIV></LI></UL>
<LI><A name=507e6547-860c-4ee8-9bfc-91e7420eb21f target=_blank>SQL中变量</A>
<UL>
<LI><FONT color=#0000ff>声明变量</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">declare<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@age</SPAN> </SPAN>int</SPAN> </LI></UL>
<LI><FONT color=#0000ff>变量赋值</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">set<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@age<SPAN style="COLOR: #808080">=</SPAN></SPAN>3</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>声明并赋值</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@age<SPAN style="COLOR: #808080">=</SPAN></SPAN>3</SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>如果表数据出现多行，则将最后一行的列赋值给变量</FONT>
<UL>
<LI><SPAN style="FONT-SIZE: 10pt"><SPAN style="COLOR: #0000ff">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@age<SPAN style="COLOR: #808080">=</SPAN>age</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">Student</SPAN></SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>输出变量的值</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">print<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@age</SPAN></SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=8887f127-be17-4c1e-bd73-d34241bb802c target=_blank>SQL中的事务</A>
<UL>
<LI><FONT color=#0000ff>begin transaction 开始事务</FONT>
<LI><FONT color=#0000ff>rollback transaction 回滚事务</FONT>
<LI><FONT color=#0000ff>commit transaction 提交事务</FONT>
<LI><FONT color=#0000ff>银行事务转账例子</FONT>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">declare<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@err</SPAN> </SPAN>int</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">set<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@err</SPAN> <SPAN style="COLOR: #808080">=</SPAN> 0</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">begin<SPAN style="COLOR: windowtext"> </SPAN>transaction<SPAN style="COLOR: windowtext"> </SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">print</SPAN> <SPAN style="COLOR: #ff0000">'开始事务'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">update</SPAN> <SPAN style="COLOR: #008080">bank</SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">balance<SPAN style="COLOR: #808080">=</SPAN>balance<SPAN style="COLOR: #808080">-</SPAN></SPAN>1000 <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">cid<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'0001'</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@err<SPAN style="COLOR: #808080">=</SPAN>@err<SPAN style="COLOR: #808080">+<SPAN style="COLOR: #ff00ff">@@ERROR</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">update</SPAN> <SPAN style="COLOR: #008080">bank</SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">balance<SPAN style="COLOR: #808080">=</SPAN>balance</SPAN> <SPAN style="COLOR: #808080">+</SPAN> 1000 <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">cid<SPAN style="COLOR: #808080">=<SPAN style="COLOR: #ff0000">'0002'</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@err<SPAN style="COLOR: #808080">=</SPAN>@err<SPAN style="COLOR: #808080">+<SPAN style="COLOR: #ff00ff">@@ERROR</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">if<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">@err</SPAN>></SPAN></SPAN>0<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">rollback</SPAN> <SPAN style="COLOR: #0000ff">transaction</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">print</SPAN> <SPAN style="COLOR: #ff0000">'回滚事务'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">else</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">commit</SPAN> <SPAN style="COLOR: #0000ff">transaction</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">print</SPAN> <SPAN style="COLOR: #ff0000">'提交事务'</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN> </LI></UL></LI></UL>
<LI><A name=956b8d68-ed50-4c86-ab7e-921931b1aa16 target=_blank>创建存储过程</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>procedure<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_transferMoney</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">@intPerson<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>20<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">@outPerson<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>20<SPAN style="COLOR: #808080">)</SPAN> <SPAN style="COLOR: #ff0000">'123'</SPAN> <SPAN style="COLOR: #008000">--可以给默认值，当参数有默认值的时候，执行的时候可以不传该参数</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #008080; FONT-SIZE: 10pt">@abcPerson<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>20<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">as<SPAN style="COLOR: windowtext"> </SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@intPerson<SPAN style="COLOR: #808080">,</SPAN>@outPerson<SPAN style="COLOR: #808080">,</SPAN>@abcPerson</SPAN></SPAN></SPAN> </LI></UL>
<LI><A name=6ad4c4e3-a566-4a24-84fb-26049535979f target=_blank>存储过程带输出参数</A>
<UL>
<LI>
<DIV><SPAN style="COLOR: #008000; FONT-SIZE: 10pt">--登陆成功返回用户名不存在返回密码错误返回登陆超过次返回试登陆次数超过次返回</SPAN></DIV>
<DIV><SPAN style="COLOR: #008000; FONT-SIZE: 10pt">--drop proc login</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>proc<SPAN style="COLOR: windowtext"> </SPAN>Login</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">@result</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #0000ff">output<SPAN style="COLOR: #808080">,</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">@times</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #0000ff">output<SPAN style="COLOR: #808080">,</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">@name</SPAN> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>10<SPAN style="COLOR: #808080">),</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">@pwd</SPAN> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>10<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">as</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #008080">@times</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #008080">uTimes</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">[user]</SPAN> <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">uName<SPAN style="COLOR: #808080">=</SPAN>@name</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">if<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">@times</SPAN>>=</SPAN></SPAN>3<SPAN style="COLOR: #808080">)</SPAN> </SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@result</SPAN> <SPAN style="COLOR: #808080">=</SPAN> 4</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">return</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #808080">exists(<SPAN style="COLOR: #0000ff">select</SPAN></SPAN> <SPAN style="COLOR: #808080">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">[user]</SPAN> <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">uName<SPAN style="COLOR: #808080">=</SPAN>@name<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #808080">exists(<SPAN style="COLOR: #0000ff">select</SPAN></SPAN> <SPAN style="COLOR: #808080">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">[user]</SPAN> <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">uName<SPAN style="COLOR: #808080">=</SPAN>@name</SPAN> <SPAN style="COLOR: #808080">and</SPAN> <SPAN style="COLOR: #008080">uPwd<SPAN style="COLOR: #808080">=</SPAN>@pwd<SPAN style="COLOR: #808080">)</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@result</SPAN> <SPAN style="COLOR: #808080">=</SPAN> 1</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">else</SPAN> <SPAN style="COLOR: #008000">--密码错误返回</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@result</SPAN> <SPAN style="COLOR: #808080">=</SPAN> 3</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">update</SPAN> <SPAN style="COLOR: #008080">[user]</SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">uTimes<SPAN style="COLOR: #808080">=</SPAN></SPAN> <SPAN style="COLOR: #008080">uTimes<SPAN style="COLOR: #808080">+</SPAN></SPAN>1 <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">uName<SPAN style="COLOR: #808080">=</SPAN>@name</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #008080">@times</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #008080">uTimes</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">[user]</SPAN> <SPAN style="COLOR: #0000ff">where</SPAN> <SPAN style="COLOR: #008080">uName<SPAN style="COLOR: #808080">=</SPAN>@name</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">else</SPAN> <SPAN style="COLOR: #008000">--用户名不存在返回</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">begin</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008080">@result</SPAN> <SPAN style="COLOR: #808080">=</SPAN> 2</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">end</SPAN></SPAN></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">insert<SPAN style="COLOR: windowtext"> </SPAN>into<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[user]</SPAN> </SPAN>values<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #ff0000">'admin'</SPAN>,<SPAN style="COLOR: #ff0000">'000000'</SPAN>,<SPAN style="COLOR: windowtext">0</SPAN>)</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">update<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[user]</SPAN> </SPAN>set<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">uTimes<SPAN style="COLOR: #808080">=</SPAN></SPAN>0 </SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">select<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">*</SPAN> </SPAN>from<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">[user]</SPAN></SPAN></SPAN></DIV>
<DIV></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">declare<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">@times</SPAN> </SPAN>int<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008080">@r<SPAN style="COLOR: windowtext"> </SPAN></SPAN></SPAN>int</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">exec<SPAN style="COLOR: windowtext"> </SPAN>Login <SPAN style="COLOR: #008080">@r<SPAN style="COLOR: windowtext"> </SPAN></SPAN>output<SPAN style="COLOR: #808080">,<SPAN style="COLOR: #008080">@times<SPAN style="COLOR: windowtext"> </SPAN></SPAN></SPAN>output<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #808080">,<SPAN style="COLOR: #ff0000">'admin111'</SPAN>,<SPAN style="COLOR: #ff0000">'admin'</SPAN></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">print<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff0000">'times'<SPAN style="COLOR: #808080">+<SPAN style="COLOR: #ff00ff">cast</SPAN>(<SPAN style="COLOR: #008080">@times</SPAN></SPAN></SPAN> </SPAN>as<SPAN style="COLOR: windowtext"> </SPAN>varchar<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">print<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #ff0000">'result'<SPAN style="COLOR: #808080">+</SPAN></SPAN> <SPAN style="COLOR: #ff00ff">cast<SPAN style="COLOR: #808080">(<SPAN style="COLOR: #008080">@r</SPAN></SPAN></SPAN> </SPAN>as<SPAN style="COLOR: windowtext"> </SPAN>varchar<SPAN style="COLOR: #808080">)</SPAN></SPAN> </LI></UL>
<LI><A name=03cf6ec8-8742-4b17-9a24-93da32f575a4 target=_blank>调用存储过程</A>
<UL>
<LI><FONT color=#0000ff>无参数的存储过程调用</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">Exec<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade</SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>有参数的存储过程两种调用法</FONT>
<UL>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">EXEC<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade2</SPAN></SPAN> <SPAN style="COLOR: windowtext">60<SPAN style="COLOR: #808080">,</SPAN>55 <SPAN style="COLOR: #008000">---按次序</SPAN></SPAN></SPAN>
<LI><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">EXEC<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade2</SPAN></SPAN> <SPAN style="COLOR: #008080">@english<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">55</SPAN>,</SPAN>@math<SPAN style="COLOR: #808080">=<SPAN style="COLOR: windowtext">60 <SPAN style="COLOR: #008000">--参数名</SPAN></SPAN></SPAN></SPAN></SPAN> </LI></UL>
<LI><FONT color=#0000ff>参数名参数有默认值时</FONT>
<UL>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">EXEC<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade2</SPAN></SPAN> <SPAN style="COLOR: #008000">--都用默认值</SPAN></SPAN></DIV>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt"><SPAN style="COLOR: #008000"></SPAN></SPAN><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">EXEC<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade2</SPAN></SPAN> <SPAN style="COLOR: windowtext">1 <SPAN style="COLOR: #008000">--第一个用默认值</SPAN></SPAN></SPAN></DIV>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt"><SPAN style="COLOR: windowtext"><SPAN style="COLOR: #008000"></SPAN></SPAN></SPAN><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">EXEC<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">usp_upGrade2</SPAN></SPAN> <SPAN style="COLOR: windowtext">1<SPAN style="COLOR: #808080">,</SPAN>5 <SPAN style="COLOR: #008000">--不用默认值</SPAN></SPAN></SPAN> </DIV></LI></UL></LI></UL>
<LI><A name=03641657-a6d0-46b9-a696-b891eeb32d6b target=_blank>触发器定义</A>
<UL>
<LI><FONT color=#0000ff>触发器是一种特殊的存储过程</FONT>
<LI><FONT color=#0000ff>触发器不能传参，通过事件进行触发执行</FONT>
<LI>
<P style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt" class=p0><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"><FONT color=#0000ff>针对<FONT face="Times New Roman">tbL_abc</FONT><FONT face=宋体>表的新增之后的触发器</FONT></FONT></SPAN></P>
<UL>
<LI><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"><FONT face=宋体>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">Create<SPAN style="COLOR: windowtext"> </SPAN>Trigger<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">triggerName</SPAN> </SPAN>on<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">tbL_abc</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">after</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">insert<SPAN style="COLOR: windowtext"> </SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">as</SPAN></DIV>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">begin</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #808080">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #008080">inserted</SPAN> <SPAN style="COLOR: #008000">--保存了引发新增触发器的新增数据，只能在触发器中访问</SPAN></SPAN></DIV>
<P style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt" class=p0><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">end<SPAN style="COLOR: windowtext"> </SPAN></SPAN></FONT></SPAN></P><!--EndFragment--></LI></UL></LI></UL>
<LI>触发器类型
<UL>
<LI><FONT color=#0000ff>after和for是在执行操作后触发</FONT>
<LI><FONT color=#0000ff>instead of 是在操作之前触发（替换触发器），但不会执行原语句</FONT></LI></UL>
<LI>触发器触发条件
<UL>
<LI><FONT color=#0000ff>update</FONT>
<LI><FONT color=#0000ff>insert</FONT>
<LI><FONT color=#0000ff>delete</FONT></LI></UL>
<LI><A name=c84c3279-d0cc-4608-8332-7e106758318d target=_blank>什么是索引</A>
<UL>
<LI><FONT color=#0000ff>就是为某个表，某个列建立一个查找目录，如果没有目录，汉语字典就要一页一页的翻，有了目录直接翻目录，快速定位到查找位置</FONT></LI></UL>
<LI><A name=505b3727-1c3e-4b5b-b2d2-b673dd260095 target=_blank>索引类型</A>
<UL>
<LI><FONT color=#0000ff>聚集索引（拼音目录）</FONT>
<UL>
<LI><FONT color=#0000ff>数据的排列顺序，按照聚集索引排列（控制表的物理顺序）</FONT>
<LI><FONT color=#0000ff>每个表只能建立一个聚集索引</FONT></LI></UL>
<LI><FONT color=#0000ff>非聚集索引（偏旁部首目录）</FONT>
<UL>
<LI><FONT color=#0000ff>非聚集索引不会改变表的物理顺序</FONT>
<LI><FONT color=#0000ff>每个表可以建立多个非聚集索引</FONT></LI></UL></LI></UL>
<LI><A name=06a502fe-6601-46cb-8281-cc61329b1d36 target=_blank>什么是填充因子</A>
<UL>
<LI><FONT color=#0000ff>就是为每页索引设置预留空间，在将来加入新索引的时候，就只需要更新当前索引页，而不需要更新索引树</FONT>
<LI><FONT color=#0000ff>如每页索引1M大小，当填充因子设置为60%，在每页只存放60%的数据，剩下40%留给将来要加入的索引项使用</FONT></LI></UL>
<LI><A name=f3f2c165-b602-41d8-b3e9-0cb63af7c88b target=_blank>什么是临时表 </A>
<UL>
<LI><FONT color=#0000ff>是存在缓存中，而不是写在文件中</FONT>
<LI><FONT color=#0000ff>可以在系统数据库→tempdb中找到</FONT></LI></UL>
<LI><A name=d31c1282-ea4c-4a74-b006-bbb97ebf803b target=_blank>什么是局部临时表</A>
<UL>
<LI><FONT color=#0000ff>生命周期在当前会话，当前会话结束就销毁临时表</FONT>
<LI><FONT color=#0000ff>相当于C#的局部成员</FONT>
<LI><FONT color=#0000ff>创建时表名前加一个#号</FONT>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">#tempUsers</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">(</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">id</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #0000ff">identity<SPAN style="COLOR: #808080">(</SPAN></SPAN>1<SPAN style="COLOR: #808080">,</SPAN>1<SPAN style="COLOR: #808080">),</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">name</SPAN> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>20<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN> </LI></UL>
<LI><A name=1e7d8c80-a8fa-4252-8bbf-527c67bdb207 target=_blank>什么是全局临时表</A>
<UL>
<LI><FONT color=#0000ff>多个用户可以共享这个全局临时表</FONT>
<LI><FONT color=#0000ff>当所有会话都退出的时候，这个全局临时表才会被销毁</FONT>
<LI><FONT color=#0000ff>相当与C#的static 静态成员</FONT>
<LI><FONT color=#0000ff>创建时，表名前面加两个##号</FONT>
<LI>
<DIV><SPAN style="COLOR: #0000ff; FONT-SIZE: 10pt">create<SPAN style="COLOR: windowtext"> </SPAN>table<SPAN style="COLOR: windowtext"> <SPAN style="COLOR: #008080">##tempUsers</SPAN></SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">(</SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">id</SPAN> <SPAN style="COLOR: #0000ff">int</SPAN> <SPAN style="COLOR: #0000ff">identity<SPAN style="COLOR: #808080">(</SPAN></SPAN>1<SPAN style="COLOR: #808080">,</SPAN>1<SPAN style="COLOR: #808080">),</SPAN></SPAN></DIV>
<DIV><SPAN style="COLOR: windowtext; FONT-SIZE: 10pt"> <SPAN style="COLOR: #008080">name</SPAN> <SPAN style="COLOR: #0000ff">varchar<SPAN style="COLOR: #808080">(</SPAN></SPAN>20<SPAN style="COLOR: #808080">)</SPAN></SPAN></DIV><SPAN style="COLOR: #808080; FONT-SIZE: 10pt">)</SPAN> </LI></UL>
<LI><A name=4332b6c5-97e7-4fb0-9986-b29a3745eb28 target=_blank>什么是三层结构</A>
<UL>
<LI><FONT color=#0000ff>通常意义的三层是将整个业务应用分为</FONT>
<UL>
<LI><FONT color=#0000ff>表现层（UI）</FONT>
<LI><FONT color=#0000ff>业务逻辑层（BLL）</FONT>
<LI><FONT color=#0000ff>数据访问层（DAL）</FONT></LI></UL></LI></UL>
<LI><A name=0f94e1de-ffea-4f7c-aec4-44547f5ad53c target=_blank>三层结构的目的</A>
<UL>
<LI><FONT color=#0000ff>区分层次的目的即为“高内聚，低耦合”的思想</FONT></LI></UL>
<LI><A name=8ea2af5f-e764-4186-9433-1edd7040a66f target=_blank>具体的三层是哪三层</A>
<UL>
<LI><FONT color=#0000ff>表现层（UI）</FONT>
<UL>
<LI><FONT color=#0000ff>通俗讲就是展现给用户的界面，即用户在使用一个系统的时候的所见所得</FONT></LI></UL>
<LI><FONT color=#0000ff>业务逻辑层（BLL）</FONT>
<UL>
<LI><FONT color=#0000ff>针对具体问题的操作，也可以说是对数据层的操作，对数据业务逻辑处理</FONT></LI></UL>
<LI><FONT color=#0000ff>数据访问层（DAL）</FONT>
<UL>
<LI><FONT color=#0000ff>该层所做事务直接操作数据库，针对数据的添加、删除、修改、更新、查找等</FONT></LI></UL></LI></UL>
<LI><A name=38bd17f2-53bd-4481-81b6-7d3d438f2341 target=_blank>三层之间的关系</A>
<UL>
<LI><FONT color=#0000ff>是一种垂直的关系</FONT>
<LI><FONT color=#0000ff>三层结构是N层结构的一种，一般来说，层次之间是向下依赖，下层代码未确定其接口前，上层代码是无法开发的，下次代码接口的变化将使上层的代码一起变化</FONT></LI></UL>
<LI><A name=25c5b332-f65a-42d6-97da-7792c0dd3c04 target=_blank>三层结构的优缺点</A>
<UL>
<LI><FONT color=#0000ff>优点</FONT>
<UL>
<LI><FONT color=#0000ff>分工明确，条理清晰，易于调试，而且具有可扩展性</FONT></LI></UL>
<LI><FONT color=#0000ff>缺点</FONT>
<UL>
<LI><FONT color=#0000ff>增加成本</FONT></LI></UL></LI></UL>
<LI><A name=c32a2a1b-77df-44c9-970d-be78823c86d7 target=_blank>邮件发送方法</A>
<UL>
<LI>
<DIV><CODE>
<DIV><FONT color=#008000>//邮件发送方法</FONT><BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>void</B></FONT> Send() {<BR> <FONT color=#008000>//创建邮件对象</FONT><BR> MailMessage mailMessage <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> MailMessage();<BR> <FONT color=#008000>//设置邮件字符集编码</FONT><BR> mailMessage.SubjectEncoding <FONT color=#333399>=</FONT> Encoding.Default;<BR> mailMessage.BodyEncoding <FONT color=#333399>=</FONT> Encoding.Default;<BR> mailMessage.HeadersEncoding <FONT color=#333399>=</FONT> Encoding.Default;<BR> <FONT color=#008000>//设置邮件发件人</FONT><BR> mailMessage.From <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> MailAddress(<FONT color=#ff00bf><A href="mailto:[email protected]" target=_blank>[email protected]</A></FONT>);<BR> <FONT color=#008000>//设置邮件收件人</FONT><BR> mailMessage.To.Add(<FONT color=#0000ff><B>new</B></FONT> MailAddress(<FONT color=#ff00bf><A href="mailto:[email protected]" target=_blank>[email protected]</A></FONT>));<BR> <FONT color=#008000>//设置邮件主题</FONT><BR> mailMessage.Subject <FONT color=#333399>=</FONT> <FONT color=#ff00bf>"主题"</FONT>;<BR> <FONT color=#008000>//设置邮件正文</FONT><BR> mailMessage.Body <FONT color=#333399>=</FONT> <FONT color=#ff00bf>"你好呀!"</FONT>;<BR> <FONT color=#008000>//创建SMTP客户端</FONT><BR> <FONT color=#008000>//SMTP服务端地址</FONT><BR> SmtpClient smtpClient <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> SmtpClient(<FONT color=#ff00bf>"127.0.0.1"</FONT>);<BR> <FONT color=#008000>//SMTP客户端 通信凭证</FONT><BR> smtpClient.Credentials <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> NetworkCredential(<FONT color=#ff00bf>"[email protected]"</FONT>, <FONT color=#ff00bf>"123"</FONT>);<BR> <FONT color=#008000>//发送</FONT><BR> smtpClient.Send(mailMessage);<BR> }</DIV></CODE></DIV></LI></UL>
<LI><A name=b68b3c42-ca88-43c6-ba17-66b9f0dc5c9a target=_blank>Excel导入导出</A>
<UL>
<LI><FONT color=#0000ff>OLE Automation(需要安装Excel。要启动excel进程，不适合服务器)Microsoft Jet OleDb </FONT>
<DIV><FONT color=#0000ff>Open XML</FONT></DIV>
<DIV><FONT color=#0000ff>NPOI(不依赖Excel客户端，省资源，但只能处理.xls格式)</FONT></DIV>
<LI>
<DIV><FONT color=#0000ff>ExecelHelper 类帮助说明</FONT></DIV>
<UL>
<LI><FONT color=#0000ff>1.将对象集合导出到Excel + void ObjectCollectionsToExcel<T>(List<T> objectCollections, string path)选要创建实体类对象集合，选中一个路径，导出即可</FONT>
<LI><FONT color=#0000ff>2.将Excel数据导入为对象集合 +List<T> ExcelToObjectCollections<T>(string path)将Excel文档导入为ObjectCollection对象集合，是采用反射方式，去调用实体类 构造函数 ，以下为实体类写法</FONT>
<LI><FONT color=#0000ff>重点在public Person(IRow row)构造函数</FONT>
<LI><CODE>
<DIV><FONT color=#0000ff><B>using</B></FONT> NPOI.SS.UserModel;<BR><FONT color=#0000ff><B>namespace</B></FONT> MyLibrary.Test<BR><FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>class</B></FONT> Person<BR>{<BR> <FONT color=#0000ff><B>public</B></FONT> Person(IRow row) {<BR> <FONT color=#0000ff><B>this</B></FONT>.A <FONT color=#333399>=</FONT> row.GetCell(<FONT color=#6e00aa>0</FONT>).ToString();<BR> <FONT color=#0000ff><B>this</B></FONT>.B <FONT color=#333399>=</FONT> row.GetCell(<FONT color=#6e00aa>0</FONT>).ToString();<BR> <FONT color=#0000ff><B>this</B></FONT>.C <FONT color=#333399>=</FONT> row.GetCell(<FONT color=#6e00aa>0</FONT>).ToString();<BR> }<BR><BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>string</B></FONT> A { get; set; }<BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>string</B></FONT> B { get; set; }<BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>string</B></FONT> C { get; set; }<BR>}</DIV></CODE></LI></UL></LI></UL>
<LI><A name=9c640836-9b0d-49b3-b5b8-0242028ca220 target=_blank>MD5加密解密方法</A>
<UL>
<LI><FONT color=#0000ff>System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(strPwd,"MD5");</FONT>
<LI><CODE>
<DIV><FONT color=#008000>/// <summary></FONT> <BR><FONT color=#008000>/// 加密方法</FONT> <BR><FONT color=#008000>/// </summary></FONT> <BR><FONT color=#008000>/// <param name="encryptString">要加密的字符串</param></FONT> <BR><FONT color=#008000>/// <returns>加密后的字符串</returns></FONT><BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>static</B></FONT> <FONT color=#0000ff><B>string</B></FONT> EncryptString(<FONT color=#0000ff><B>string</B></FONT> encryptString) <BR> { <BR> TripleDESEncryptor target <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> TripleDESEncryptor(<FONT color=#ff00bf>"nihao"</FONT>);<BR> <FONT color=#0000ff><B>return</B></FONT> target.Encrypt(encryptString); <BR> }<BR><BR><FONT color=#008000>/// <summary></FONT> <BR><FONT color=#008000>/// 解密方法</FONT> <BR><FONT color=#008000>/// </summary></FONT> <BR><FONT color=#008000>/// <param name="deEncryptString">要解密的字符串</param></FONT> <BR><FONT color=#008000>/// <returns>解密后的字符串</returns></FONT><BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>static</B></FONT> <FONT color=#0000ff><B>string</B></FONT> DeEncryptString(<FONT color=#0000ff><B>string</B></FONT> deEncryptString) <BR> { <BR> TripleDESEncryptor target <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> TripleDESEncryptor(<FONT color=#ff00bf>"nihao"</FONT>); <BR><FONT color=#0000ff><B> return</B></FONT> target.Decrypt(deEncryptString); <BR> }<BR><BR> <FONT color=#0000ff><B>public</B></FONT> <FONT color=#0000ff><B>static</B></FONT> <FONT color=#0000ff><B>string</B></FONT> GetMd5(<FONT color=#0000ff><B>string</B></FONT> txt) <BR> { <BR> MD5Encryptor target <FONT color=#333399>=</FONT> <FONT color=#0000ff><B>new</B></FONT> MD5Encryptor(); <FONT color=#008000>// TODO: 初始化为适当的值</FONT> <BR><FONT color=#0000ff><B> return</B></FONT> target.GetMD5(txt); <BR> }</DIV></CODE></LI></UL>
<LI><A name=9ab437ba-fb20-459f-aa44-be33c5b6a772 target=_blank>读取数据库后，判断dataset里列的值是否为空</A>
<UL>
<LI><CODE>
<DIV>If(dataset.Table[<FONT color=#6e00aa>0</FONT>][<FONT color=#ff00bf>"columnName"</FONT>] <FONT color=#333399>!=</FONT> <FONT color=#0000ff><B>null</B></FONT>)<BR>{<BR>.......<BR>}</DIV></CODE>
<LI><CODE>
<DIV><FONT color=#0000ff><B>if</B></FONT> (dr.IsNull(<FONT color=#ff00bf>""</FONT>)) <BR>{<BR>}</DIV></CODE></LI></UL>
<LI><A name=6a838262-987a-4eca-8f0f-246cab4eff47 target=_blank>项目术语</A><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"></SPAN>
<UL>
<LI><FONT color=#0000ff><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">CRM</SPAN><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">--<FONT face=宋体>客户关系管理系统（</FONT><FONT face="Times New Roman">Customer Relation Management</FONT><FONT face=宋体>）</FONT></SPAN></FONT>
<LI><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"></SPAN><FONT color=#0000ff><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">OA</SPAN><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">--<FONT face=宋体>办公自动化 （</FONT><FONT face="Times New Roman">Office Automation</FONT><FONT face=宋体>）</FONT></SPAN></FONT>
<LI><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"></SPAN><FONT color=#0000ff><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">MIS</SPAN><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">--<FONT face=宋体>信息管理系统（</FONT><FONT face="Times New Roman">Management Information Systems</FONT><FONT face=宋体>）</FONT></SPAN></FONT>
<LI><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"></SPAN><FONT color=#0000ff><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">ERP</SPAN><SPAN style="FONT-FAMILY: '宋体'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'">--<FONT face=宋体>企业资源计划系统（</FONT><FONT face="Times New Roman">Enterprise Resource Planning</FONT><FONT face=宋体>）</FONT></SPAN><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: 10.5pt; mso-spacerun: 'yes'"><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></FONT></LI></UL><!--EndFragment--></LI></UL></BODY>
</HTML>