SQL应用开发范例宝典

第1章　SQL语言基础　1
1.1　变量与常量　2
实例001　定义和使用局部变量　2
实例002　定义和使用全局变量　4
实例003　数字常量的使用　6
实例004　使用字符串常量　8
实例005　使用日期和时间常量　9
1.2　数据类型　10
实例006　使用常见的数据类型　11
实例007　使用文本和图像数据类型　13
实例008　定义表数据类型　15
实例009　自定义数据类型　16
1.3　复合语句　18
实例010　定义复合语句(begin…end)　18
实例011　GO语句　20
1.4　条件判断语句　21
实例012　使用if语句进行条件判断　21
实例013　使用case语句进行条件选择判断　22
1.5　循环语句　25
实例014　使用while语句　25
实例015　在循环中使用break语句　26
实例016　在循环中使用continue语句　27
实例017　利用goto语句实现循环　29
1.6　临时表　30
实例018　定义和使用局部临时表　30
实例019　定义和使用全局临时表　31
实例020　使用select into语句生成临时表　33
1.7　常用命令　34
实例021　使用print命令输出文本　34
实例022　使用DBCC命令检查数据库　35
实例023　使用kill命令结束连接数据库中的进程　36
第２章　常规数据查询　39
2.1　查询的基本结构　40
实例024　SELECT语句的结构　40
2.2　列的查询　42
实例025　单列查询　42
实例026　多列查询　43
实例027　查询所有列　44
实例028　使用列别名　46
2.3　列的计算　47
实例029　简单列计算　47
实例030　复杂列计算　49
实例031　执行算术组合计算　50
实例032　连接列值　51
实例033　使用字符函数对列进行格式化查询　52
实例034　使用数字函数对列进行格式化查询　54
实例035　使用常量查询　56
2.4　WHERE条件查询　57
实例036　检索单行值　58
实例037　不匹配检索　59
2.5　区间查询　60
实例038　利用“”、“”号进行区间查询　61
实例039　利用BETWEEN进行区间查询　62
第３章　高级数据过滤　65
3.1　组合WHERE语句　66
实例040　使用AND运算符连接WHERE子句　66
实例041　利用OR运算符连接WHERE子句　67
实例042　有效利用组合顺序　68
实例043　对数值列进行四舍五入　70
实例044　使用行值表达式简化查询条件　72
实例045　怎样去掉小数点后多余的0　73
3.2　IN运算符　74
实例046　使用IN运算符简化条件判断　74
实例047　使用IN运算符查询4∶1、4∶2、4∶3赢得比赛的信息　75
3.3　NOT运算符　76
实例048　使用NOT运算符　76
实例049　运算符的使用　78
3.4　行查询　79
实例050　使用UNIQUE以没有重复行为条件查询数据　79
实例051　限制返回行数(top rownum limit)　80
实例052　从表中随机返回记录　81
实例053　使用DISTINCT去除重复信息　82
实例054　返回第8行到第10行的数据　84
实例055　查找具体行数据　85
实例056　查询时显示行号的方法　86
实例057　延时执行查询　88
实例058　使用ReadText读取文本类型数据　89
实例059　预览员工工资提高10%的金额　91
实例060　确定哪些行是彼此互换的　92
实例061　强制使用非簇索引查询数据　93
实例062　使用iif函数进行条件判断　94
3.5　类型转换　95
实例063　使用convert函数进行类型转换　95
实例064　使用cast函数进行类型转换　97
实例065　格式化金额　98
实例066　将字符串转换为日期　99
实例067　将日期转换为字符串　100
3.6　进制转换　101
实例068　二进制转换十进制　101
实例069　八进制转换十进制　102
实例070　十六进制转换十进制　103
3.7　匹配查询　105
实例071　使用like运算符　106
实例072　使用“%”匹配字符串　107
实例073　使用“_”匹配任意字符　108
实例074　使用“_”查询6个字的数据　109
实例075　使用“[ ]”匹配某一范围的字符　111
实例076　使用“[^]”匹配非某一范围字符　112
实例077　匹配特殊字符　113
实例078　在模式查询中使用转义字符(escape)　114
实例079　在多个字段中进行单个字符的匹配查询　116
实例080　匹配发音　117
3.8　空值判断　118
实例081　查找空值　118
实例082　查找非空值　119
实例083　使用case语句将空值转换为实际值　121
实例084　使用coalesce函数将空值转换为实际值　122
实例085　使用nullif函数比较两个表达式　123
第４章　字符串查询　125
4.1　字符串查询　126
实例086　去除尾部空格　126
实例087　截取字符串的部分信息　127
实例088　使用“+”连接字符串　128
实例089　使用stuff函数插入字符串　129
实例090　将字符串以列的形式显示　130
实例091　在字符串中包含引号　131
实例092　获取字符在字符串中出现的次数　132
实例093　字符串大小写转换　133
实例094　去掉字符串中的“*”　135
4.2　截取字符串　135
实例095　从字符串中删除元音字符　135
实例096　从字符串中分离字符和数字　136
实例097　去除字符串中非字母和数字的字符　138
4.3　格式化字符串　139
实例098　将电话号码中的“-”去掉，但保留分机号前的“-”　139
实例099　提取电话号码中的区号和号码　140
实例100　格式化字符串为日期格式　141
4.4　替换字符串　143
实例101　将字符串中的指定字符替换为“*”　143
实例102　用“…”代替过长的字符串显示　143
4.5　比较字符串　145
实例103　比较两个字符串是否相同　145
实例104　比较两个字符串的相似性　146
实例105　大小写敏感查询　147
第5章　日期查询　149
5.1　格式化日期　150
实例106　把长日期格式数据转化为短日期格式数据　150
实例107　显示长日期格式数据中的小时、分、秒　151
实例108　将日期格式中的“.”转化为“-”　153
5.2　时间段查询　154
实例109　使用Between and查询某一时间段内的数据　154
实例110　使用关系运算符查询某一时间段的数据　155
实例111　使用in运算符查询某几个时间的数据　157
实例112　查询某一月份的数据　158
实例113　查询今天是周几　159
实例114　查询两个日期之间的天数　161
实例115　计算两个日期之间的月份数　162
实例116　计算两个日期之间的分、秒、小时　164
实例117　计算两个日期之间的工作日　165
实例118　返回当前记录与下一条记录之间相差的天数　166
5.3　获取日期　167
实例119　将日期中的年月日以列的形式显示　167
实例120　查询当前月份　169
实例121　查询3个月后的日期　170
实例122　获取某月的第一天　172
实例123　获取本周的第一天　173
实例124　获取当前季度的第一天　174
实例125　获取上个月的最后一天　175
实例126　获取去年最后一天　176
实例127　获取本年的最后一天　177
第6章　数据排序　179
6.1　数据排序　180
实例128　升序排序　180
实例129　降序排序　181
实例130　别名排序　182
6.2　字符串排序　183
实例131　按字符串中的某个子串排序　183
实例132　按字母数字混合数据排序　184
6.3　汉字排序　185
实例133　按姓氏笔画排序　186
实例134　按音序排序　187
6.4　按多个列排序　188
实例135　多列排序　188
实例136　重复值排序　189
实例137　排序前3名数据　190
实例138　排序后1名数据　191
实例139　查询结果排序　193
实例140　在统计结果中排序　194
6.5　按列序号排序　195
实例141　按列的编号排序　195
实例142　动态排序　196
第7章　聚合函数与分组统计　199
7.1　聚合函数　200
实例143　通过SUM函数对销售金额进行汇总　200
实例144　通过DISTINCT函数求不重复班级名称　201
实例145　通过AVG函数求某班学生的平均成绩　202
实例146　通过MIN函数求销售额最低值　204
实例147　利用MIN函数在子查询中查找最小值　205
实例148　通过MAX函数求销售额最高值　207
实例149　利用MAX函数在子查询中查询最大值　209
实例150　利用COUNT函数求日销售数量　210
实例151　利用COUNT函数求日销售额大于某值的商品数　212
实例152　利用COUNT函数求部门名称列非NULL值的个数　214
实例153　查询包含最大值和最小值的记录　215
实例154　查询不包含最大值和最小值的平均值　216
实例155　计算商品销售额所占的百分比　217
实例156　查询重复记录　219
7.2　分组统计　220
实例157　GROUP BY子句创建简单分组　220
实例158　GROUP BY子句创建多列组合行　222
实例159　在分组查询中使用ALL关键字　223
实例160　对统计结果进行排序　224
实例161　在分组查询中使用HAVING子句　226
实例162　在分组查询中使用CUBE运算符　227
实例163　在分组查询中使用ROLLUP 　229
实例164　HAVING子句与WHERE子句的联合使用　231
实例165　多表分组统计　233
实例166　使用COMPUTE子句在同一结果集中显示明细和汇总　234
实例167　使用COMPUTE BY子句显示多级分类汇总　237
实例168　使用COALESCE函数将NULL转换成0后，计算员工的平均工资　239
实例169　生成累计和　240
实例170　生成累计差　241
第8章　使用子查询　243
8.1　简单子查询　244
实例171　在SELECT列表中的子查询　244
实例172　为子查询命名　245
实例173　多列子查询　246
实例174　在多表查询中使用子查询　248
8.2　单行子查询　249
实例175　比较子查询　249
实例176　在子查询中使用聚合函数　250
实例177　使用子查询更新数据　251
实例178　使用子查询删除数据　252
8.3　多行子查询　253
实例179　使用IN查询实现交集运算　253
实例180　使用NOT IN子查询实现差集运算　255
实例181　在嵌套中使用EXISTS谓词　256
实例182　在嵌套中使用NOT EXISTS谓词　259
实例183　使用比较谓词ALL引入子查询　260
实例184　使用比较谓词ANY引入子查询　261
实例185　不使用NOT IN查询主表中有而从表中没有的数据　263
8.4　相关子查询　264
实例186　使用IN引入相关子查询　264
实例187　嵌套子查询　265
实例188　在HAVING子句中使用相关子查询　267
实例189　比较运算符引入相关子查询　268
实例190　实现树查询　269
第9章　多表查询　271
9.1　多表联接　272
实例191　笛卡尔乘积查询　272
实例192　在多表联接中设置联接条件　273
实例193　使用表别名　274
实例194　在多表联接中返回某个表的所有列名　275
9.2　内联接　276
实例195　等值联接　276
实例196　不等联接　278
实例197　自然联接　279
实例198　多表内联接　280
9.3　外联接　281
实例199　左外联接　281
实例200　右外联接　282
实例201　完全联接　284
实例202　使用外联接进行多表联合查询　285
9.4　其他联接　286
实例203　自联接　286
实例204　静态交叉表　287
实例205　动态交叉表　289
实例206　在外联接中使用and运算符　290
9.5　组合查询　292
实例207　使用UNION运算符组合两个表的数据　292
实例208　一对多联合查询　293
实例209　对组合查询结果排序　295
实例210　使用UNION ALL保留重复行　296
实例211　使用UNION运算符消除重复的行　298
实例212　将数据表中符合指定条件的记录优先显示　299
实例213　使用INTERSECT函数进行交集运算　300
实例214　使用MINUS进行差集运算　301
9.6　使用联接和联接条件　302
实例215　在内联接中子查询使用聚合函数　302
实例216　利用联接条件查找两表中的共同行　303
实例217　查询一个表中存在的而其他两个表中不存在的数据　304
实例218　数据库之间数据的复制　305
实例219　从一个表中查询另一个表中没有的数据　306
实例220　根据另一表中的条件查询数据　307
实例221　利用递归生成连续数　308
第10章　高级查询　311
10.1　交叉表查询　312
实例222　交叉表　312
实例223　反转交叉表　313
实例224　行列转换　316
实例225　创建分隔列表　317
实例226　查询结果分页显示　318
实例227　在结果集中隔一行显示数据　320
10.2　分布式查询　321
实例228　建立链接　322
实例229　查看链接　323
实例230　删除链接　324
实例231　建立映射　325
实例232　查看登录映射信息　326
实例233　简单分布式查询　327
实例234　使用OpenDatasource函数进行分布式查询　329
实例235　使用OpenQuery函数进行分布式查询　331
实例236　使用OpenRowSet进行分布式查询　333
实例237　创建关联到电子表格的链接　335
实例238　创建关联Access数据库的链接　336
第11章　插入数据　339
11.1　插入单行数据　340
实例239　整行插入数据　340
实例240　特定字段数据插入　341
实例241　NULL值的插入　342
实例242　唯一值的插入　343
实例243　通过视图插入行　344
实例244　插入默认值　345
实例245　插入日期数据　346
实例246　创建数据表之后插入数据　347
11.2　插入多行记录　349
实例247　通过VALUES关键字插入多行数据　349
实例248　使用UNION ALL语句批量插入数据　350
实例249　使用INSERT SELECT语句批量插入数据　352
11.3　将查询结果插入数据表　353
实例250　从一个表中向另一个表中复制行　353
实例251　复制表结构　354
实例252　将查询结果中的数据插入到多表中　356
第12章　更新和删除数据　359
12.1　更新表中的数据　360
实例253　UPDATE语句更新列值　360
实例254　利用子查询更新多行的值　361
实例255　依据外表值更新数据　362
实例256　分步更新表　364
实例257　使用CASE表达式更新列值　366
实例258　只更新存在于其他表中的数据　368
实例259　利用Oracle数据库中的关键字merge合并记录　369
12.2　删除表中的数据　371
实例260　删除单条数据　371
实例261　删除多行数据　372
实例262　删除所有数据　373
实例263　删除部分重复的行　374
实例264　删除完全重复的行　376
实例265　使用TRUNCATE TABLE语句彻底删除数据　377
实例266　使用子查询删除数据　379
实例267　删除从其他表中引用的记录　380
实例268　删除违反参照完整性的记录　381
12.3　通过视图更新表　382
实例269　通过视图更新表数据　382
实例270　通过视图删除表数据　383
第13章　创建、操纵数据库和表　385
13.1　创建、删除数据库　386
实例271　创建包含多个数据文件和日志文件的数据库　386
实例272　创建带有多个文件组的数据库　389
实例273　修改数据库　390
实例274　向数据库中添加数据文件　392
实例275　向数据库中添加日志文件　393
实例276　向数据库中添加文件组　394
实例277　从数据库中删除文件　395
实例278　从数据库中删除文件组　396
实例279　删除数据库　397
13.2　表的创建　398
实例280　创建基本表　398
实例281　定义列的非空约束　399
实例282　在表中定义默认值约束　401
实例283　使用关键字作为表的列名　402
实例284　为联合主键设置外键　403
13.3　表的修改　405
实例285　为表增加列　405
实例286　删除表中列　407
实例287　修改列的结构　408
实例288　为指定列设定默认值　409
实例289　为现有表添加主键　410
实例290　为现有表添加外键　411
实例291　为现有表关系添加级联更新、级联删除　412
13.4　表的重命名和删除　413
实例292　重命名表　413
实例293　删除表　414
第14章　使用视图　417
14.1　创建和使用视图　418
实例294　使用视图过滤不想要的数据　418
实例295　使用视图与计算数据　419
实例296　使用视图重新格式化检索出的数据　421
实例297　使用视图简化复杂的连接　422
实例298　创建基于视图的视图　423
14.2　修改视图　424
实例299　修改视图　424
实例300　通过视图添加数据　426
实例301　通过视图修改数据　428
14.3　删除视图　429
实例302　删除视图　429
实例303　通过视图删除表数据　430
第15章　使用存储过程和函数　433
15.1　创建存储过程　434
实例304　使用CREATE PROCED URE语句创建存储过程　434
实例305　使用return语句从存储过程中返回值　436
实例306　创建带参数的存储过程　437
实例307　在存储过程中使用事务　439
实例308　创建带返回参数的存储过程　440
实例309　创建并添加扩展存储过程　442
实例310　创建加密存储过程　443
15.2　管理存储过程　444
实例311　执行存储过程　444
实例312　存储过程的重新编译　446
实例313　执行具有回传参数的存储过程　448
实例314　查看存储过程代码　449
实例315　修改存储过程　452
实例316　重新命名存储过程　454
实例317　删除存储过程　455
实例318　使用存储过程调用Com组件(sp_OACreate)　456
实例319　重命名数据库中列的名称　458
实例320　监控存储过程(sp_monitor)　459
实例321　自动执行存储过程　461
15.3　使用函数　462
实例322　创建标量函数　462
实例323　创建内嵌表值函数　463
实例324　删除函数　466
第16章　使用游标　469
实例325　声明游标　470
实例326　定义游标变量　473
实例327　打开游标　474
实例328　读取游标中数据　475
实例329　关闭游标　477
实例330　释放游标　478
实例331　获取游标行数　479
实例332　检测FETCH操作的状态　481
实例333　游标FOR循环　482
实例334　游标的嵌套　484
实例335　基于游标定位修改数据　486
实例336　基于游标定位删除数据　488
实例337　在存储过程中使用游标　489
实例338　参数化游标　491
实例339　利用sp_cursor_list查看服务及游标属性　492
实例340　利用sp_describe_cursor查看游标的全局特性　494
第17章　使用触发器　497
17.1　创建触发器　498
实例341　创建INSERT触发器　498
实例342　创建UPDATE触发器　501
实例343　创建DELETE触发器　502
实例344　创建具有触发条件的触发器　503
实例345　创建INSTEAD OF触发器　505
实例346　触发器的嵌套　506
实例347　创建递归触发器　508
17.2　修改触发器　510
实例348　修改触发器　510
实例349　重命名触发器　513
17.3　删除触发器　514
实例350　删除触发器　514
第18章　事务处理　517
18.1　事务概述　518
实例351　设置事务属性　518
18.2　事务应用　519
实例352　提交事务　520
实例353　回滚事务　521
实例354　保存事务　523
18.3　事务并发控制　525
实例355　表级锁定　525
实例356　设置事务隔离层并发控制　526
实例357　死锁的产生及预防　528
第19章　安全性控制　531
19.1　用户管理　532
实例358　使用sp_addlogin创建用户　532
实例359　使用sp_password修改用户密码 　533
实例360　使用sp_helplogins查看用户登录信息　534
19.2　角色管理　535
实例361　使用sp_addrole创建数据库角色　535
实例362　使用sp_droprole删除数据库角色　536
实例363　使用GRANT语句授予角色权限　537
实例364　使用REVOKE语句取消角色创建数据表的权限　539
19.3　权限管理　540
实例365　使用GRANT语句授予角色WSERT和SELECT的权限　540
实例366　使用REVOKE语句取消角色对指定数据表的操作权限　542
实例367　SELECT权限控制　543
实例368　INSERT权限控制　544
实例369　UPDATE权限控制　545
实例370　DELETE权限控制　547
实例371　附加数据库给新用户已存在用户权限　548
第20章　SQL高级特性　551
20.1　数据完整性　552
实例372　创建并使用主键约束　552
实例373　创建并使用外键约束　554
实例374　唯一约束　556
实例375　检查约束　558
实例376　使用newid函数为uniqueidentifer类型的数据生成唯一标识　560
实例377　使用默认约束　561
实例378　删除约束　563
实例379　使用identity属性生成唯一标识　564
20.2　规则　566
实例380　创建规则　566
实例381　将规则与数据类型或列绑定在一起　567
实例382　解除规则　569
实例383　删除规则　570
实例384　创建默认值(Create Default)　571
实例385　绑定默认值(sp_bindefault)　572
实例386　解除默认值绑定(sp_unbingdefault)　573
20.3　索引　575
实例387　创建索引　575
实例388　创建简单的非簇索引　576
实例389　多字段非簇索引的创建　578
实例390　使用UNIQUE关键字创建唯一索引　579
实例391　使用CLUSTERDE关键字创建簇索引　580
实例392　索引的销毁　581
实例393　使用DBCC SHOWCONTIG 语句对索引进行维护　583
实例394　使用DBCC INDEXDEFRAG语句对索引进行维护　584
实例395　重构索引　585
实例396　创建虚拟列索引　587
实例397　创建选择索引　588
实例398　创建全文索引　589
实例399　检索词(Contains谓语)　590
实例400　模糊查询　593
20.4　优化查询　594
实例401　避免使用or运算符　594
实例402　避免使用not运算符　596
实例403　避免使用distince语句　597
实例404　简化联接条件　598
实例405　在组合查询时使用All语句　600
第21章　数据库对象查询　603
21.1　SQL Server数据库对象查询　604
实例406　获取自定义存储过程、函数和视图文本　604
实例407　显示有关触发器信息　605
实例408　列出数据库的所有表　606
实例409　列出数据表中数据列的定义　607
实例410　获取表中不允许为空的字段　608
实例411　获取表中的索引列　609
实例412　获取表中的约束　610
实例413　查看硬盘分区　611
实例414　判断两个表是否相等　612
实例415　结束事件探查器进程　613
实例416　修改数据库名称　614
实例417　获取当前数据库中的所有用户表　615
实例418　获取某一个表的所有字段　616
实例419　查看与某一个表相关的视图、存储过程和函数　616
实例420　查看当前数据库中所有存储过程　617
实例421　查询用户创建的所有数据库　618
实例422　获取数据库大小　619
实例423　跨越服务器查询数据　620
实例424　查询多个部分列　621
21.2　Oracle数据库对象查询　622
实例425　获取数据字典视图　622
实例426　查看表空间的名称及大小　623
实例427　查看表空间物理文件的名称及大小　624
实例428　查看控制文件　625
实例429　查看日志文件　626
实例430　查看数据库对象　626
实例431　查看数据库的版本　627
第22章　数据库安全与维护　629
22.1　收缩数据库　630
实例432　压缩数据库　630
实例433　收缩数据和日志　631
22.2　备份数据库　632
实例434　备份数据库　632
实例435　备份事务日志　633
实例436　检查备份集　636
实例437　定时备份数据库　637
实例438　如何备份文件和文件组　638
22.3　恢复数据库　640
实例439　简单恢复数据库　640
实例440　恢复文件和文件组　641
实例441　应用事务日志备份　643
实例442　恢复到即时点 　644
实例443　灾难性恢复数据库　645
22.4　其他操作　646
实例444　对数据库中数据进行加密　646
实例445　附加数据库　647
实例446　分离数据库　648
22.5　导入/导出数据　649
实例447　将SQL Server数据库中数据导出到数据文件　649
实例448　将Oracle数据库中数据导出到数据文本文件　651
实例449　从SQL Server数据库中将数据导出到Excel数据表中　652
实例450　从Excel中导入数据到SQL Server中　653
实例451　将SQL Server数据导出到Access中　655
实例452　从Access导出数据到SQL Server中　656
实例453　从SQL Server里查询Access数据　657
实例454　将从SQL Server中查询到的数据导成一个Excel文件　658
实例455　将Oracle数据库的数据导入到Excel中　659
22.6　SQL注入式攻击　661
实例456　防止SQL注入式攻击　661
实例457　从源程序预防SQL注入式攻击　662
第23章　嵌入式SQL　665
23.1　定义嵌入式SQL语句　666
实例458　在高级语言中定义嵌入式SQL　666
实例459　在C语言中使用SQL通信区　667
实例460　嵌入式SQL中的主变量　669
实例461　在嵌入式SQL中使用游标　671
23.2　编译并运行嵌入式SQL语句　673
实例462　预编译嵌入式SQL语句　673
实例463　执行嵌入式SQL语句　675
附录　SQL范例宝典实例适用范围　677