T-SQL语法参考手册.docx_c语言参考手册资源-CSDN文库

5星 · 超过95%的资源需积分: 40 31 浏览量 2019-12-11 07:45:43 上传评论收藏 17.18MB DOCX 举报

资源详情

资源评论

资源推荐

T-SQL 语法参考手册
目录
一、组成...................................................................................................................................................32
1、DML(数据操作语言 Data Manipulaon Language)........................................................................32
2、DCL(数据控制语言 Data Control Language)...................................................................................34
3、DDL(数据定义语言 Data Denion Language)...............................................................................34
4、变量说明、流程控制、功能函数..................................................................................................34
5、数据类型.........................................................................................................................................34
5.1、数据类型类别..........................................................................................................................35
5.2、数值数据类型..........................................................................................................................35
5.3、字符型数据类型......................................................................................................................36
5.4、日期数据类型..........................................................................................................................36
5.5、混合型数据类型......................................................................................................................37
5.6、附带有 VARDECIMAL 的 DECIMAL 存储类型...........................................................................50
5.7、类型转换..................................................................................................................................51
二、数据库...............................................................................................................................................98
1、数据库入门.....................................................................................................................................98
1.1、数据库概述..............................................................................................................................98
1.2、数据库基础概念....................................................................................................................102
1.3、数据库规范化设计................................................................................................................104
1.4、Microso+ SQL Server 数据库..................................................................................................104
2、创建数据库...................................................................................................................................151
模板.................................................................................................................................................151
CREATE DATABASE 语句详解..........................................................................................................158
3、删除数据库...................................................................................................................................174
模板.................................................................................................................................................174
DROP DATABASE 语句详解.............................................................................................................175
4、修改数据库属性...........................................................................................................................177
ALTER DATABASE 语句详解.............................................................................................................177
1

T-SQL 语法参考手册

5、元数据...........................................................................................................................................185

简介.................................................................................................................................................187

什么是动态在线目录?....................................................................................................................187

如何获得以上信息?........................................................................................................................189

系统视图.........................................................................................................................................189

元数据函数.....................................................................................................................................190

目录存储过程.................................................................................................................................196

我是如何逐渐使用的?....................................................................................................................197

比较有用的查询实例.....................................................................................................................197

在线目录总结.................................................................................................................................204

触发器元数据.................................................................................................................................204

那么如何找到触发器的数据?........................................................................................................204

但是当然一个触发器是首先是一个对象，因此一定在 sys.objects?..........................................208

触发器元数据总结.........................................................................................................................220

索引元数据.....................................................................................................................................220

索引元数据总结.............................................................................................................................233

6、数据仓库.......................................................................................................................................233

1、维（Dimension）......................................................................................................................233

2、Hierarchy 层次...........................................................................................................................234

3、立方体 Cube..............................................................................................................................237

4、数据模型与数据建模................................................................................................................238

5、数据模型的定义.......................................................................................................................238

6、事实表和维度表的分界线........................................................................................................239

7、钻取...........................................................................................................................................239

8、交叉分析...................................................................................................................................240

三、表.....................................................................................................................................................241

1、创建表...........................................................................................................................................241

模板.................................................................................................................................................241

2、删除表...........................................................................................................................................242

T-SQL 语法参考手册

模板.................................................................................................................................................242

四、约束.................................................................................................................................................243

1、添加主键(primary key)..................................................................................................................243

模板.................................................................................................................................................243

2、添加唯一性约束(uinque)..............................................................................................................243

3、添加默认约束(default('„„' ) for)....................................................................................................243

4、添加检查(check(„„))......................................................................................................................243

模板.................................................................................................................................................244

5、外键(foreign key(列名) references 主表名(列名))........................................................................244

6、删除约束.......................................................................................................................................244

7、修改字段类型（可以修改列的类型，是否为空）....................................................................244

8、添加字段.......................................................................................................................................244

模板.................................................................................................................................................244

9、删除字段.......................................................................................................................................245

模板.................................................................................................................................................245

五、通配符.............................................................................................................................................246

六、插入数据.........................................................................................................................................246

1、注意事项.......................................................................................................................................246

2、插入多行数据...............................................................................................................................247

(1)对象表存在.................................................................................................................................247

(2)对象表不存在.............................................................................................................................247

(3)插入新的标识列.........................................................................................................................247

(4)插入多行内容.............................................................................................................................248

3、大量插入数据（BULK INSERT 执行数据文件）..........................................................................248

语法.................................................................................................................................................255

参数.................................................................................................................................................256

兼容性.............................................................................................................................................266

数据类型.........................................................................................................................................266

一般备注.........................................................................................................................................269

T-SQL 语法参考手册

互操作性.........................................................................................................................................269

日志记录行为.................................................................................................................................269

限制.................................................................................................................................................270

性能注意事项.................................................................................................................................270

安全性.............................................................................................................................................270

示例.................................................................................................................................................272

4、SQLCMD/OSQL 导入 SQL...............................................................................................................277

七、更新数据行.....................................................................................................................................277

例子.....................................................................................................................................................277

更新指定的数据行数.........................................................................................................................277

顺序更新数据行.............................................................................................................................277

随机更新数据行.............................................................................................................................277

八、删除数据行.....................................................................................................................................277

1、语法...............................................................................................................................................278

2、删除指定的行...............................................................................................................................278

3、删除所有记录...............................................................................................................................278

九、查询.................................................................................................................................................278

1、SELECT 语句详解...........................................................................................................................282

语法.................................................................................................................................................282

SELECT 子句.....................................................................................................................................283

INTO 子句........................................................................................................................................285

FROM 子句......................................................................................................................................286

WHERE 子句....................................................................................................................................288

GROUP BY 子句...............................................................................................................................289

HAVING 子句...................................................................................................................................290

UNION 运算符.................................................................................................................................290

ORDER BY 子句................................................................................................................................291

COMPUTE 子句................................................................................................................................292

FOR 子句..........................................................................................................................................293

T-SQL 语法参考手册

OPTION 子句...................................................................................................................................294

注释.................................................................................................................................................296

选择标识列.....................................................................................................................................297

旧式外联接.....................................................................................................................................297

WHERE、GROUP BY 和 HAVING 子句的处理顺序.........................................................................297

查询的逻辑执行顺序.....................................................................................................................297

2、查询全部的行和列.......................................................................................................................300

3、查询部分行...................................................................................................................................300

LIKE 通配符......................................................................................................................................300

4、自定义命名查询结果中的列名....................................................................................................308

(1)使用 AS 来命名列.......................................................................................................................308

(2)合并两列数据,以规定格式输出查询结果.................................................................................308

(3)使用=来命名列...........................................................................................................................308

(4)查询某几列为空的行.................................................................................................................308

(5)显示结果中加入常量列.............................................................................................................309

(6)限制查询结果输出的行数.........................................................................................................309

(7)返回百分之多少行.....................................................................................................................309

(8)升序排列(默认)ASC....................................................................................................................309

(9)降序排列 DESC............................................................................................................................309

(10)按多列排序...............................................................................................................................309

5、使用 PIVOT 和 UNPIVOT...............................................................................................................309

语法.................................................................................................................................................310

注意.................................................................................................................................................310