Welcome to sql50’s documentation!¶
SQL Server 介绍¶
基本功能¶
SQL Server是由Microsoft开发和推广的以客户/服务器（c/s）模式访问、使用Transact-SQL语言的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。Microsoft SQL Server近年来不断更新版本，1996年，Microsoft 推出了SQL Server 6.5版本；1998年，SQL Server 7.0版本和用户见面；SQL Server 2000是Microsoft公司于2000年推出，目前最新版本是2019年份推出的SQL SERVER 2019。
提供的主要功能：
- 支持存储过程、触发器、函数和视图
- 本机支持关系数据、XML、FILESTREAM 和空间数据，可存储所有类型的业务数据
- 除与 SQL Server Reporting Services 中的 Microsoft 2007 Office System 集成外，还改进了性能、可用性、可视化
- 通过利用现有的 T-SQL 技术、ADO.NET 实体框架和 LINQ 简化开发工作
- 与 Visual Studio 和 Visual Web Developer 紧密集成
SQL Server 2019 更是使用统一的数据平台实现业务转型，附带 Apache Spark 和 Hadoop Distributed File System（HDFS），可实现所有数据的智能化。
以db-engines全球数据库排名去看，SQL Server 常年占据全球前三名。
版本信息¶
企业版 Enterprise、开发版 Developer、快速版 Express。
Express这个版本是最为基本的版本，是学习、开发桌面、Web 及小型服务器应用程序的理想选择。
企业版和开发版本相同不过应用场景不同。
开发版和快速版免费。
安装说明¶
下载¶
2008版本下载地址：https://www.microsoft.com/zh-cn/download/details.aspx?id=30438
选择这个版本的话会发现下载页又有好多不同的安装包下载
sqlserver2008files
Express (SQLEXPR)Express 版本仅包含 SQL Server 数据库引擎。它最适合需要接受远程连接或以远程方式进行管理的情况。
Express with Advanced Services (SQLEXPRADV)此包包含 SQL Server Express 的所有组件，包括 SQL Server 2012 Management Studio SP1的完整版本。此包的下载大小大于“带有工具”的版本，因为它还同时包含“全文搜索”和 Reporting Services。
Express with Tools (SQLEXPRWT)此包包含将 SQL Server 作为数据库服务器进行安装和配置所需的全部内容。
SQL Server Management Studio Express (SQLManagementStudio)此版本不包含数据库，只包含用于管理 SQL Server 实例的工具（包括 LocalDB、SQL Express、SQL Azure、SQL Server Management Studio 完整版本等）。如果只需要管理工具，则可使用此版本。
简单来说，只需要数据库，选择SQLEXPR，只需要数据库管理工具，选择SQLManagementStudio，数据库和管理工具都要，那么选择SQLEXPRWT，一般我们选择SQLEXPRWT。
2019版本下载地址：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
2019版本的下载要简单的多，因为对上述文件的选择是在安装过程中分步选择安装的，按安装过程中的提示操作即可。
下载参考说明 ： https://blog.csdn.net/wqy_zyc/article/details/80240104
连接到数据库¶
可以使用下列工具来访问和更改 SQL Server 实例（数据库）中的数据：
- SQL Server Management Studio
- sqlcmd 实用工具
- bcp 实用工具
- sqlps 实用工具
SQL Server Management Studio 提供了两种方法来访问和更改数据：
- 从**“文件”菜单或工具栏中的“新建查询”和“数据库引擎查询”**按钮，打开数据库引擎查询窗口。在数据库引擎查询窗口中，可以交互方式编写 Transact-SQL 和 Xquery 语句来查询数据库和更改数据。可以将这些语句另存为脚本文件，并可以在以后使用 sqlcmd 实用工具运行这些脚本文件。数据库引擎查询编辑器支持动态 F1 帮助、自动完成、代码大纲显示、Transact-SQL 调试器、IntelliSense 以及其他生产效率辅助工具。
- 在对象资源管理器中，可以右键单击表或视图，然后选择可用来选择或编辑行的菜单项。
sqlcmd 实用工具是一个 Microsoft Win32 命令提示实用工具，可用于：
- 以交互方式即席运行 Transact-SQL 和 XQuery 语句。
- 运行 Transact-SQL 和 XQuery 脚本文件。
bcp 实用工具可以用于将大量的行插入 SQL Server 表中。该实用工具不需要您具有 Transact-SQL 知识；但是，您必须清楚要向其中复制新行的表的结构以及表中的行可以使用的数据类型。
sqlps 实用工具是一个 Microsoft C# 命令提示实用工具，可用于：
- 以交互方式即席运行 PowerShell 命令。
- 运行 PowerShell 脚本文件。
可以使用 SQL Server Management Studio 和 sqlps 实用工具同时连接和管理 SQL Server 的多个实例。sqlcmd 和 bcp 实用工具一次仅允许与一个 SQL Server 实例连接。一般不是使用程序去访问/查询数据库，我们会选择使用SQL Server Management Studio，因为它是有图形界面的。
以SQL Server Management Studio连接Sql Server 数据库为例
sqlserver_management_studio_connection
帮助信息¶
- 微软官方在github会有sqlserver示例
- 微软官方的sqlserver问答论坛
在线SQL 平台¶
当然了，很多时候，我们可能只是想学习SQL，提升SQL查询的技能，不希望在本地安装软件（服务），那么在线型的SQL平台就特别得符合我们的需求。
SQLZOO¶
SQLZOO包括了 SQL 学习的教程和参考资料，支持 SQL Sever、Oracle、MySQL、DB2、PostgreSQL等多个 SQL 搜索引擎，现在支持英日中(繁体)，建议还是用英文来看。
sqlzoo
SQLBolt¶
SQLBolt 是一个适合小白学习 SQL 的网站，这里由浅及深的介绍了 SQL 的知识，每一个章节是一组相关的 SQL 知识点，且配备着相应的练习。这个网站的优点是教程是交互型的。仅支持英文。
image-20200123214825320
XUESQL¶
XUESQL相当于 SQLBolt 的中文版，包含手册、在线练习，B站还有讲解 SQL 的视频可以食用。优点是查询结果秒级响应（测试了前几个页面），纯中文。当然了，讲解视频非常入门级，完全针对小白型的（这个是优点还是缺点还真不好定论）。
xuesql
SQL Fiddle¶
SQL Fiddle 支持 MySQL、SQL Server、SQLite、Oracle 等主流的 SQL 引擎，在这里可以选择练习的数据库以及版本号。相比于前面推荐的网站还提供教程，这个网站相当于提供环境。
image-20200123215753887
w3resource¶
w3resource 看上去是一个综合性教程网站（和w3cschool类似），网站是纯英文的，提供了mysql, Oracle, PostgreSQL, SQLite, MongoDB, Redis 等的教程，进入教程之后在教程结尾有练习题，练习题是提供运行环境以及正确答案的！
w3resource
sql学习网SQL¶
SQL(Structured Query Language，结构化查询语言)是访问和操作关系数据库的标准语言，是高级的非过程化编程语言。只要是关系数据库，都可以使用 SQL 进行访问和控制。
SQL发展历史¶
- 在1970年代初，由IBM公司San Jose,California研究实验室的埃德加·科德发表将数据组成表格的应用原则（Codd’s Relational Algebra）。1974年，同一实验室的D.D.Chamberlin和R.F. Boyce对Codd’s Relational Algebra在研制关系数据库管理系统System R中，研制出一套规范语言-SEQUEL(Structured English QUEry Language)，并在1976年11月的IBM Journal of R&D上公布新版本的SQL（叫SEQUEL/2）。1980年改名为SQL。
- 1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2 和SQL/DS数据库系统中也实现了SQL。
- 1986年10月，美国ANSI采用SQL作为关系数据库管理系统的标准语言（ANSI X3. 135-1986），后为国际标准化组织（ISO）采纳为国际标准。
- 1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89， 该标准替代ANSI X3.135-1986版本。该标准为下列组织所采纳：
- 国际标准化组织（ISO），为ISO 9075-1989报告“Database Language SQL With Integrity Enhancement”
- 美国联邦政府，发布在The Federal Information Processing Standard Publication(FIPS PUB)127
- 目前，所有主要的关系数据库管理系统支持某些形式的SQL， 大部分数据库打算遵守ANSI SQL89标准。
SQL特点¶
- SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体
- 面向集合的语言
- 非过程语言
- 类似自然语言，简洁易用
- 自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。
SQL基本语句¶
- DQL（data query language），数据查询语言；也就是 SELECT 语句，用于查询数据库中的数据和信息
- DML（data manipulation language），数据操作语言；用于对表中的数据进行增加（INSERT）、修改（UPDATE）、删除（DELETE）以及合并（MERGE）操作
- DDL（data definition language），数据定义语言；主要用于定义数据库中的对象（例如表或索引），包括创建对象（CREATE）、修改对象（ALTER）和删除对象（DROP）等
- TCL（transaction control language），事务控制语言；用于管理数据库的事务，主要包括启动一个事务（BEGIN TRANSACTION）、提交事务（COMMIT）、回退事务（ROLLBACK）和事务保存点（SAVEPOINT）
- DCL（data control language），数据控制语言；用于控制数据的访问权限，主要有授权（GRANT）和撤销（REVOKE）。
NoSQL¶
随着互联网的发展和大数据的兴起，出现了各种各样的非关系（NoSQL）数据库。NoSQL 代表 Not only SQL，表明它是针对传统关系数据库的补充和升级，而不是为了替代关系数据库。
NoSQL 数据库主要用于解决关系数据库在某些特定场景下的局限性，比如海量存储和水平扩展；但同时也会为此牺牲某些关系数据库的特性，例如对事务强一致性的支持和标准 SQL 接口。因此，这类数据库主要用于对一致性要求不是非常严格的互联网业务。常见的 NoSQL 数据库可以分为以下几类：
- 文档数据库，例如 MongoDB（MongoDB 4.0 增加了多文档事务的特性）
- 键值存储，例如 Redis
- 全文搜索引擎，例如 Elasticsearch
- 宽列存储数据库，例如 Cassandra
- 图形数据库，例如 Neo4J。
另一方面，关系数据库也在积极拥抱变化，添加了许多非关系模型（XML 和 JSON）支持。以最流行的开源关系数据库 MySQL 为例，最新的 MySQL 8.0 版本增加了 JSON 文档存储的支持，并且推出了一个新的概念：NoSQL + SQL = MySQL。
Oracle、SQL Server 以及 PostgreSQL 同样也进行了类似的扩展，可以支持原生的 XML 和 JSON 数据，并且提供了许多标准的 SQL 接口。
NewSQL¶
为了同时获得关系数据库对于事务的支持和标准的 SQL 接口，以及非关系数据库的高度扩展性和高性能。如今市场上已经出现了一类新型关系型数据库系统：NewSQL 数据库。
比较有代表性的 NewSQL 数据库包括 Google Spanner、VoltDB、PostgreSQL-XL 以及国产的 TiDB。这类新型数据库是数据库领域最新的发展方向。
Transact-SQL¶
Transact-SQL （又称 T-SQL），是在 Microsoft SQL Server 和 Sybase SQL Server 上的 ANSI SQL 实现，与 Oracle 的 PL/SQL 性质相近（不只是实现 ANSI SQL，也为自身数据库系统的特性提供实现支持），在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。
Transact-SQL 元素¶
| Transact-SQL 元素 | 说明 |
|---|---|
| 标识符 | 表、视图、列、数据库和服务器等对象的名称。 |
| 数据类型 | 定义数据对象（如列、变量和参数）所包含的数据的类型。大多数 Transact-SQL 语句并不显式引用数据类型，但它们的结果受语句中所引用对象的数据类型之间的交互操作影响。 |
| 常量 | 代表特定数据类型的符号。 |
| 函数 | 语法元素，可以接受零个、一个或多个输入值，并返回一个标量值或表格形式的一组值。示例包括将多个值相加的 SUM 函数、确定两个日期之间相差多少个时间单位的 DATEDIFF 函数、获取 Microsoft SQL Server 实例名称的 @@SERVERNAME 函数或在远程服务器上执行 Transact-SQL 语句并检索结果集的 OPENQUERY 函数。 |
| 表达式 | SQL Server 可以解析为单个值的语法单位。表达式的示例包括常量、返回单值的函数、列或变量的引用。 |
| 表达式中的运算符 | 与一个或多个简单表达式一起使用，构造一个更为复杂的表达式。例如，表达式 PriceColumn * 1.1 中的乘号 (*) 使价格提高百分之十。 | | 注释 | 插入到 Transact-SQL 语句或脚本中、用于解释语句作用的文本段。SQL Server 不执行注释。 | | 保留关键字 | 保留下来供 SQL Server 使用的词，不应用作数据库中的对象名。 | |
Transact-SQL标识符¶
Microsoft SQL Server 中的所有内容都可以有标识符。服务器、数据库和数据库对象（例如表、视图、列、索引、触发器、过程、约束及规则等）都可以有标识符。大多数对象要求有标识符，但对有些对象（例如约束），标识符是可选的。
标识符的种类¶
有两类标识符：
- 常规标识符 符合标识符的格式规则。在 Transact-SQL 语句中使用常规标识符时不用将其分隔开。
SELECT *
FROM TableX
WHERE KeyCol = 1024
- 分隔标识符 包含在双引号 (“) 或者方括号 ([ ]) 内。不分隔符合标识符格式规则的标识符。例如：
SELECT * FROM [TableX] --用不用分隔符都可以
WHERE [KeyCol] = 1024 --用不用分隔符都可以
在 Transact-SQL 语句中，必须对不符合所有标识符规则的标识符进行分隔。例如：
SELECT * FROM [My Table] --My Table之间包含空格，因此必须包含分隔标识符
WHERE [order] = 10 --关键字必须包含分隔符
常规标识符和分隔标识符包含的字符数必须在 1 到 128 之间。对于本地临时表，标识符最多可以有 116 个字符。
常规标识符规则¶
常规标识符格式规则取决于数据库兼容级别。该级别可以使用 ALTER DATABASE 设置。当兼容级别为 100 时，下列规则适用：
- 第一个字符必须是下列字符之一：
Unicode 标准 3.2 所定义的字母。Unicode 中定义的字母包括拉丁字符 a-z 和 A-Z，以及来自其他语言的字母字符。
下划线 (_)、at 符号 (@) 或数字符号 (#)。
在 SQL Server 中，某些位于标识符开头位置的符号具有特殊意义。以 at 符号开头的常规标识符始终表示局部变量或参数，并且不能用作任何其他类型的对象的名称。以一个数字符号开头的标识符表示临时表或过程。以两个数字符号 (##) 开头的标识符表示全局临时对象。虽然数字符号或两个数字符号字符可用作其他类型对象名的开头，但是不建议这样做。
某些 Transact-SQL 函数的名称以两个 at 符号 (@@) 开头。为了避免与这些函数混淆，不应使用以 @@ 开头的名称。
- 后续字符可以包括：
- 如 Unicode 标准 3.2 中所定义的字母。
- 基本拉丁字符或其他国家/地区字符中的十进制数字。
- at 符号、美元符号 ($)、数字符号或下划线。
- 标识符一定不能是 Transact-SQL 保留字。SQL Server 可以保留大写形式和小写形式的保留字。
- 不允许嵌入空格或其他特殊字符。
- 不允许使用增补字符。
在 Transact-SQL 语句中使用标识符时，不符合这些规则的标识符必须由双引号或括号分隔。
Transact-SQL 数据类型¶
包含数据的对象都有一个相关联的数据类型，它定义对象所能包含的数据种类，例如字符、整数或二进制。下列对象具有数据类型：
- 表和视图中的列。
- 存储过程中的参数。
- 变量。
- 返回一个或多个特定数据类型数据值的 Transact-SQL 函数。
- 具有返回代码（始终为 integer 数据类型）的存储过程。
为对象分配数据类型时可以为对象定义四个属性：
- 对象包含的数据种类。
- 所存储值的长度或大小。
- 数值的精度（仅适用于数字数据类型）。
- 数值的小数位数（仅适用于数字数据类型）。
二进制数据¶
binary 和 varbinary 数据类型存储位串。尽管字符数据是根据 SQL Server 代码页进行解释的，但 binary 和 varbinary 数据仅是位流。
- binary [ ( n ) ] 长度为 n 字节的固定长度二进制数据，其中 n 是从 1 到 8,000 的值。存储大小为 n 字节。
- varbinary [ ( n | max) ] 可变长度二进制数据。n 可以是从 1 到 8000 之间的值。max 指示最大存储大小为 2^31-1 字节。存储大小为所输入数据的实际长度 + 2 个字节。所输入数据的长度可以是 0 字节。varbinary 的 ANSI SQL 同义词为 binary varying。
二进制常量以 0x（一个零和小写字母 x）开始，后跟位模式的十六进制表示形式。例如，0x2A 表示十六进制值 2A，它等于十进制值 42 或单字节位模式 00101010。
存储十六进制值 [如安全标识号 (SID)、GUID（使用 uniqueidentifier 数据类型）或可以用十六进制方式存储的复杂数字时，使用二进制数据。
字符串¶
char 和 varchar 数据类型存储由以下字符组成的数据：
- 大写字符或小写字符。例如，a、b 和 C。
- 数字。例如，1、2 和 3。
- 特殊字符。例如，at 符号 (@)、“与”符号 (&) 和感叹号 (!)。
使用方式：
- char [ ( n ) ] 固定长度，非 Unicode 字符串数据。n 定义字符串长度，取值范围为 1 至 8,000。存储大小为 n 字节。当排序规则代码页使用双字节字符时，存储大小仍然为 n 个字节。根据字符串的不同，n 个字节的存储大小可能小于为 n 指定的值。char 的 ISO 同义词为 character。
- varchar [ ( n | max ) ] 可变长度，非 Unicode 字符串数据。n 定义字符串长度，取值范围为 1 至 8,000。max 指示最大存储大小是 2^31-1 个字节 (2 GB)。存储大小为输入的实际数据长度 + 2 个字节。varchar 的 ISO 同义词为 char varying 或 character varying。
varchar 数据可以有两种形式。varchar 数据的最大字符长度可以是指定的。例如，varchar(6) 指示此数据类型最多存储六位字符；它也可以是 varchar(max), 形式的，即此数据类型可存储的最大字符数可达 2^31。
每个 char 和 varchar 数据值都具有排序规则。排序规则定义属性，如用于表示每个字符的位模式、比较规则以及是否区分大小写或重音。每个数据库有默认排序规则。当定义列或指定常量时，除非使用 COLLATE 子句指派特定的排序规则，否则将为它们指派数据库的默认排序规则。当组合或比较两个具有不同排序规则的 char 或 varchar 值时，根据排序规则的优先规则来确定操作所使用的排序规则。
字符常量必须包括在单引号 (‘) 或双引号 (“) 中。建议用单引号括住字符常量。
Unicode 字符串¶
Unicode 规格为全球商业领域中广泛使用的大部分字符定义了一个单一编码方案。所有的计算机都用单一的 Unicode 规格将 Unicode 数据中的位模式一致地转换成字符。这保证了同一个位模式在所有的计算机上总是转换成同一个字符。数据可以随意地从一个数据库或计算机传送到另一个数据库或计算机，而不用担心接收系统是否会错误地转换位模式。
每个 Microsoft SQL Server 排序规则都有一个代码页，该代码页定义表示 char、varchar 和 text 值中每个字符的位模式。可为个别的列和字符常量分配不同的代码页。
Unicode 规格通过采用两个字节编码每个字符使这个问题迎刃而解。转换最通用商业语言的单一规格具有足够多的 2 字节的模式 (65536)。因为所有的 Unicode 系统均一致地采用同样的位模式来表示所有的字符，所以当从一个系统转到另一个系统时，将不会存在未正确转换字符的问题。通过在整个系统中使用 Unicode 数据类型，可尽量减少字符转换问题。
在 SQL Server 中，下列数据类型支持 Unicode 数据：
- nchar
- nvarchar
- ntext
字符串数据类型（nchar 长度固定或 nvarchar 长度可变）和 Unicode 数据使用 UNICODE UCS-2 字符集。
- nchar [ ( n ) ] 固定长度，Unicode 字符串数据。n 定义字符串长度，取值范围为 1 至 4,000。存储大小为 n 字节的两倍。当排序规则代码页使用双字节字符时，存储大小仍然为 n 个字节。根据字符串的不同，n 个字节的存储大小可能小于为 n 指定的值。nchar 的 ISO 同义词为 national char 和 national character。
- nvarchar [ ( n | max ) ] 可变长度，Unicode 字符串数据。n 定义字符串长度，取值范围为 1 至 4,000。max 指示最大存储大小是 2^31-1 个字节 (2 GB)。存储大小（以字节为单位）是所输入数据实际长度的两倍 + 2 个字节。nvarchar 的 ISO 同义词为 national char varying 和 national character varying。
除下列情况外，nchar、nvarchar 和 ntext 的使用分别与 char、varchar 和 text 的使用相同：
- Unicode 支持更大范围的字符。
- 存储 Unicode 字符需要更大的空间。
- nchar 列的最大大小为 4,000 个字符，与 char 和 varchar 不同，它们为 8,000 个字符。
- 使用最大说明符，nvarchar 列的最大大小为 2^31-1 字节。
- Unicode 常量以 N 开头指定：N’A Unicode string’。
- 所有 Unicode 数据使用由 Unicode 标准定义的字符集。用于 Unicode 列的 Unicode 排序规则以下列属性为基础：区分大小写、区分重音、区分假名、区分全半角和二进制。
Text和Image¶
Microsoft SQL Server 将超过 8,000 个字节的字符串和大于 8,000 个字节的二进制数据分别存储为名为 text 和 image 的特殊数据类型。超过 4,000 个字符的 Unicode 字符串存储为 ntext 数据类型。
例如，您需要将一个大型客户信息文本文件 (.txt) 导入 SQL Server 数据库。应将这些数据作为一个数据块存储起来，而不是集成到数据表的多个列中。为此，可以创建一个 text 数据类型的列。但是，如果必须存储公司徽标，它们当前存储为标记图像文件格式 (TIFF) 图像 (.tif) 且每个图像的大小为 10 KB，则可以创建一个 image 数据类型的列。
整数¶
| 数据类型 | 范围 | 存储 |
|---|---|---|
| bigint | -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807) | 8 字节 |
| int | -2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647) | 4 字节 |
| smallint | -2^15 (-32,768) 到 2^15-1 (32,767) | 2 字节 |
| tinyint | 0 到 255 | 1 字节 |
在数据类型优先次序表中，bigint 介于 smallmoney 和 int 之间。
尽管 SQL Server 有时会将 tinyint 或 smallint 值提升为 int 数据类型，但不会自动将 tinyint、smallint 或 int 值提升为 bigint 数据类型。除非明确说明，否则那些接受 int 表达式作为其参数的函数、语句和系统存储过程都不会改变，从而不会支持将 bigint 表达式隐式转换为这些参数，只有当参数表达式为 bigint 数据类型时，函数才返回 bigint。
decimal、numeric、float和real¶
精度是数字中的数字个数。小数位数是数中小数点右边的数字个数。例如，数 123.45 的精度是 5，小数位数是 2。
decimal 数据类型最多可以存储 38 个数字，所有这些数字均可位于小数点后面。decimal 数据类型存储精确的数字表示形式，存储值没有近似值。
定义 decimal 列、变量和参数的两种属性为：
- p
指定精度或对象能够支持的数字个数。
- s
指定可以放在小数点右边的小数位数或数字个数。
p 和 s 必须遵守规则：0 <= s <= p <= 38。
- decimal[ (p[ ,s] )] 和 numeric[ (p[ ,s] )] 固定精度和小数位数。使用最大精度时，有效值从 - 10^38 +1 到 10^38 - 1。decimal 的 ISO 同义词为 dec 和 dec(p, s)。numeric 在功能上等价于 decimal。
- p（精度） 最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从 1 到最大精度 38 之间的值。默认精度为 18。
- s （小数位数） 小数点右边可以存储的十进制数字的最大位数。小数位数必须是从 0 到 p 之间的值。仅在指定精度后才可以指定小数位数。默认的小数位数为 0；因此，0 <= s <= p。最大存储大小基于精度而变化。
| 精度 | 存储字节数 |
|---|---|
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
在 SQL Server 中，numeric 和 decimal 数据类型的默认最大精度为 38。在 SQL Server 早期版本中，默认最大精度为 28。numeric 的功能等同于 decimal 数据类型。
float 和 real 数据类型被称为近似数据类型。float 和 real 的使用遵循有关近似数值数据类型的 IEEE 754 规范。
| 数据类型 | 范围 | 存储 |
|---|---|---|
| float | -1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308 | 取决于 n 的值 |
| real | -3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38 | 4 字节 |
其中 n 为用于存储 float 数值尾数的位数（以科学记数法表示）。 如果 1**<=n<=**24，则将 n 视为 24。 如果 25**<=n<=**53，则将 n 视为 53。n的默认值为53。real 的 ISO 同义词为 float(24)。
| n的值 | 精度 | 存储 |
|---|---|---|
| 1-24 | 7位数 | 4 字节 |
| 25-53 | 15位数 | 8 字节 |
近似数值数据类型并不存储为许多数字指定的精确值，它们只储存这些值的最近似值。 在很多应用程序中，指定值与存储的近似值之间的微小差异并不明显。但有时这些差异也较明显。
在 WHERE 子句搜索条件（特别是 = 和 <> 运算符）中，应避免使用 float 列或 real 列。float 列和 real 列最好只限于 > 比较或 < 比较。
IEEE 754 规范提供四种舍入模式：舍入到最近、向上舍入、向下舍入以及舍入到零。Microsoft SQL Server 使用向上舍入。所有的数值都必须精确到确定的精度，但会产生微小的浮点值差异。因为浮点数字的二进制表示法可以采用很多合法舍入规则中的任意一条，因此我们不可能可靠地量化浮点值。
货币数据¶
Microsoft SQL Server 使用以下两种数据类型存储货币数据或货币值：money 和 smallmoney。这些数据类型可以使用下列任意一种货币符号。
代表 货币或货币值 的数据类型。
| 数据类型 | 范围 | 存储 |
|---|---|---|
| money | -922,337,203,685,477.5808 到 922,337,203,685,477.5807 | 8 字节 |
| smallmoney | -214,748.3648 到 214,748.3647 | 4 字节 |
money 和 smallmoney 数据类型精确到它们所代表的货币单位的万分之一。
日期和时间数据¶
下表列出了 Transact-SQL 的日期和时间数据类型。
| 数据类型 | 格式 | 范围 | 精确度 | 存储使用字节 |
|---|---|---|---|---|
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 到 23:59:59.9999999 | 100 纳秒 | 3 到 5 |
| date | YYYY-MM-DD | 0001-01-01 到 9999-12-31 | 1 天 | 3 |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 到 2079-06-06 | 1 分钟 | 4 |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 到 9999-12-31 | 0.00333 秒 | 8 |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 | 100 纳秒 | 6 到 8 |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示） | 100 纳秒 | 8 到 10 |
所有日期和时间数据类型都支持关系运算符（<、<=、>、>=、<>）、比较运算符（=、<、<=、>、>=、<>、!<、!>）以及逻辑运算符和布尔谓词（IS NULL、IS NOT NULL、IN、BETWEEN、EXISTS、NOT EXISTS 和 LIKE）。
数据类型转换¶
可以按以下方案转换数据类型：
- 当一个对象的数据移到另一个对象，或两个对象之间的数据进行比较或组合时，数据可能需要从一个对象的数据类型转换为另一个对象的数据类型。
- 将 Transact-SQL 结果列、返回代码或输出参数中的数据移到某个程序变量中时，必须将这些数据从 SQL Server 系统数据类型转换成该变量的数据类型。
可以隐式或显式转换数据类型：
- 隐式转换对用户不可见。
SQL Server 会自动将数据从一种数据类型转换为另一种数据类型。例如，将 smallint 与 int 进行比较时，在比较之前 smallint 会被隐式转换为 int。请注意，查询优化器可能生成一个查询计划来在任意时间执行此转换。
- 显式转换使用 CAST 或 CONVERT 函数。
如果希望 Transact-SQL 程序代码符合 ISO 标准，请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的样式功能，请使用 CONVERT 而不要使用 CAST。
uniqueidentifier¶
uniqueidentifier 数据类型可存储 16 字节的二进制值，其作用与全局唯一标识符 (GUID) 一样。GUID 是唯一的二进制数；世界上的任何两台计算机都不会生成重复的 GUID 值。GUID 主要用于在拥有多个节点、多台计算机的网络中，分配必须具有唯一性的标识符。
uniqueidentifier 列的 GUID 值通常通过下列方式之一获取：
- 在 Transact-SQL 语句、批处理或脚本中调用 NEWID 函数。
- 在应用程序代码中，调用返回 GUID 的应用程序 API 函数或方法。
Transact-SQL NEWID 函数以及应用程序 API 函数和方法用它们的网卡的标识号加上 CPU 时钟的唯一编号来生成新的 uniqueidentifier 值。每个网卡都有唯一的标识号。NEWID 返回的 uniqueidentifier 值是通过使用服务器上的网卡而生成的。应用程序 API 函数和方法返回的 uniqueidentifier 值是通过使用客户端中的网卡而生成的。
uniqueidentifier 值通常不定义为常量。您可以按下列方式指定 uniqueidentifier 常量：
- 字符串格式： ‘6F9619FF-8B86-D011-B42D-00C04FC964FF’
- 二进制格式： 0xff19966f868b11d0b42d00c04fc964ff
uniqueidentifier 数据类型具有下列缺点：
- 值长且难懂。这使用户难以正确键入它们，并且更难记住。
- 这些值是随机的，而且它们不支持任何使其对用户更有意义的模式。
- 也没有任何方式可以决定生成 uniqueidentifier 值的顺序。它们不适用于那些依赖递增的键值的现有应用程序。
- 当 uniqueidentifier 为 16 字节时，其数据类型比其他数据类型（例如 4 字节的整数）大。这意味着使用 uniqueidentifier 键生成索引的速度相对慢于使用 int 键生成索引的速度。
XML数据¶
可以创建 xml 数据类型的变量和列。xml 数据类型有自己的 XML 数据类型方法。
| XML方法 | 说明 |
|---|---|
| query() 方法（xml 数据类型） | 说明如何使用 query() 方法查询 XML 实例。 |
| value() 方法（xml 数据类型） | 说明如何使用 value() 方法从 XML 实例中检索 SQL 类型的值。 |
| exist() 方法（xml 数据类型） | 说明如何使用 exist() 方法确定查询是否返回非空结果。 |
| modify() 方法（xml 数据类型） | 说明如何使用 modify() 方法指定 XML Data Modification Language (XML DML) 语句以执行更新。 |
| nodes() 方法（xml 数据类型） | 说明如何使用 nodes() 方法将 XML 拆分到多行中，从而将 XML 文档的组成部分传播到行集中。 |
| 在 XML 数据内部绑定关系数据 | 说明如何在 XML 中绑定非 XML 数据。 |
| xml 数据类型方法的使用准则 | 说明使用 xml 数据类型方法的指导原则。 |
可以对 xml 数据类型的列和变量中存储的 XML 数据指定 XQuery 语言。
timestamp和rowversion¶
每个数据库都有一个计数器，当对数据库中包含 rowversion 列的表执行插入或更新操作时，该计数器值就会增加。此计数器是数据库行版本。这可以跟踪数据库内的相对时间，而不是时钟相关联的实际时间。一个表只能有一个 rowversion 列。
每次修改或插入包含 rowversion 列的行时，就会在 rowversion 列中插入经过增量的数据库行版本值。这一属性使 rowversion 列不适合作为键使用，尤其是不能作为主键使用。对行的任何更新都会更改行版本值，从而更改键值。如果该列属于主键，那么旧的键值将无效，进而引用该旧值的外键也将不再有效。如果该表在动态游标中引用，则所有更新均会更改游标中行的位置。如果该列属于索引键，则对数据行的所有更新还将导致索引更新。
timestamp 的数据类型为 rowversion 数据类型的同义词，并具有数据类型同义词的行为。在 DDL 语句，请尽量使用 rowversion 而不是 timestamp。
cursor¶
cursor是变量或存储过程 OUTPUT 参数的一种数据类型，这些参数包含对游标的引用。使用 cursor数据类型创建的变量可以为空。
有些操作可以引用那些带有 cursor 数据类型的变量和参数，这些操作包括：
- DECLARE @local_variable 和 SET @local_variable 语句。
- OPEN、FETCH、CLOSE 及 DEALLOCATE 游标语句。
- 存储过程输出参数。
- CURSOR_STATUS 函数。
- sp_cursor_list、sp_describe_cursor、sp_describe_cursor_tables 以及 sp_describe_cursor_columns 系统存储过程。
table¶
table 是一种特殊的数据类型，用于存储结果集以进行后续处理。主要用于临时存储一组作为表值函数的结果集返回的行。可将函数和变量声明为 table 类型。table 变量可用于函数、存储过程和批处理中。
sql_variant¶
sql_variant用于存储 SQL Server 支持的各种数据类型的值。sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。sql_variant 使这些数据库对象能够支持其他数据类型的值。
最大长度可以是 8016 个字节。这包括基类型信息和基类型值。实际基类型值的最大长度是 8,000 个字节。
Transact-SQL 函数¶
SQL Server 提供了可用于执行特定操作的内置函数。具体内置函数
| 函数类别 | 说明 |
|---|---|
| 聚合函数 | 执行的操作是将多个值合并为一个值。如 COUNT、SUM、MIN 和 MAX。 |
| 分析函数 | 分析函数基于一组行计算聚合值。 但是，与聚合函数不同， 分析函数可能针对每个组返回多行。 可以使用分析函数来计算移动平均线、运行总计、百分比 或一个组内的前 N 个结果。 |
| 配置函数 | 是一种标量函数，可返回有关配置设置的信息。 |
| 加密函数 (Transact-SQL) | 支持加密、解密、数字签名和数字签名验证。 |
| 游标函数 | 返回有关游标状态的信息。 |
| 日期和时间函数 | 可以更改日期和时间的值。 |
| 数学函数 | 执行三角、几何和其他数字运算。 |
| 元数据函数 | 返回数据库和数据库对象的属性信息。 |
| 排名函数 | 是一种非确定性函数，可以返回分区中每一行的排名值。 |
| 行集函数 (Transact-SQL) | 返回可在 Transact-SQL 语句中表引用所在位置使用的行集。 |
| 安全函数 | 返回有关用户和角色的信息。 |
| 字符串函数 | 可更改 char、varchar、nchar、nvarchar、binary 和 varbinary 的值。 |
| 系统函数 | 对系统级的各种选项和对象进行操作或报告。 |
| 系统统计函数 (Transact-SQL) | 返回有关 SQL Server 性能的信息。 |
| 文本和图像函数 | 可更改 text 和 image 的值。 |
系统函数¶
-- 返回工作站标识号,是连接到 SQL Server的客户端计算机上的应用程序的进程 ID (PID)
SELECT HOST_ID();
-- 返回工作站名
SELECT HOST_NAME();
-- 创建 uniqueidentifier 类型的唯一值
SELECT NEWID();
-- 确定表达式是否为有效的数值类型;ISNUMERIC ( expression )
SELECT distinct
ISNUMERIC(sid),ISNUMERIC(ssex)
from Student
-- 使用指定的替换值替换 NULL。
-- ISNULL ( check_expression , replacement_value )
CAST 和 CONVERT¶
CAST 和 CONVERT函数是将一种数据类型的表达式转换为另一种数据类型的表达式。
-- CAST()语法:
CAST ( expression AS data_type [ ( length ) ] )
-- CONVERT()语法:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
- expression 任何有效的 表达式 。
- data_type 目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。
- length 指定目标数据类型长度的可选整数。默认值为 30。
- style 指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的。有关详细信息，请参阅“备注”部分。
cast() 主要用于数据类型之间的转换，而convert() 则将特定格式（style）的数据类型（expression）转为其他数据类型。
安全函数¶
对管理安全性有用的函数
-- 当前用户的名称, 两者等价
SELECT CURRENT_USER;
SELECT USER_NAME();
-- 数据库指定用户的标识号， 用户名缺省则表示当前用户
SELECT USER_ID ( [ 'user' ] );
SELECT USER_ID();
-- 数据库指定标识号的用户名
SELECT USER_NAME([ id ] );
SELECT USER_NAME();
-- 当前数据库中当前上下文的用户名
SELECT SESSION_USER;
-- 用户的登录标识号 SUSER_ID ( [ 'login' ] ) login为登录名
SELECT SUSER_ID('sa');
SELECT SUSER_ID(USER_NAME());
-- 根据用户登录标识号返回用户的登录标识名SUSER_NAME ( [ server_user_id ] )
SELECT SUSER_NAME(1);
-- 指定登录名的安全标识号 (SID)
SELECT SUSER_SID('sa');
-- 与安全标识号 (SID) 关联的登录名
SELECT SUSER_SNAME(0x01);
-- 判断当前账户是否可以访问指定的数据库
SELECT HAS_DBACCESS ('database_name');
-- 判断当前用户是否为指定Microsoft Windows组或SQL Server数据库角色的成员
SELECT IS_MEMBER ( { 'group' | 'role' } );
元数据函数¶
返回有关数据库和数据库对象的信息
-- 与架构 ID 关联的架构名称 SCHEMA_NAME ([ schema_id ])
SELECT SCHEMA_NAME();
-- 与架构名称关联的架构ID SCHEMA_ID ([ schema_name ])
SELECT SCHEMA_ID();
-- 数据库标识 (ID)号, DB_ID ( [ 'database_name' ] )
SELECT DB_ID();
-- 数据库名称 DB_NAME ( [ database_id ] )
SELECT DB_NAME();
-- 指定表中指定列的定义长度（以字节为单位）
COL_LENGTH ( 'table' , 'column' )
OVER子句（窗口函数）¶
OVER子句确定在应用关联的窗口函数之前，行集的分区和排序。 窗口函数， 也可以被称为OLAP函数或分析函数。
窗口函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。窗口函数计算从窗口派生的结果集中各行的值。
可以在单个查询中将多个排名或聚合窗口函数与单个 FROM 子句一起使用。 窗口函数是整个SQL语句最后被执行的部分，这意味着窗口函数是在SQL查询的结果集上进行的， 因此不会受到Group By， Having，Where子句的影响。
-- 语法
-- 排名函数
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause>
[ <ROW or RANGE clause> ]
)
-- 聚合函数
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
[ORDER BY order_by_expression ]
[ <ROW or RANGE clause> ]
)
- PARTITION BY 将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
- value_expression 指定对相应 FROM 子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM 子句可用的列。value_expression 不能引用选择列表中的表达式或别名。value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。
- <ORDER BY 子句>指定按其执行窗口函数计算的逻辑顺序。
- order_by_expression 指定用于进行排序的列或表达式。 order_by_expression 只能引用可供 FROM 子句使用的列 。 不能将整数指定为表示列名或别名。
- ROWS | RANGE 适用于：SQL Server 2012 (11.x) 及更高版本。 通过指定分区中的起点和终点，进一步限制分区中的行数。 这是通过按照逻辑关联或物理关联对当前行指定某一范围的行实现的。 物理关联通过使用 ROWS 子句实现。
窗口函数（over子句）优势
- 类似Group By的聚合
- 非顺序的访问数据
- 可对窗口函数使用分析函数、聚合函数和排名函数
- 简化了SQL代码（消除Join）
- 消除中间表
窗口函数（over子句）使用场景
- 经典top N问题
比如：找出每个科目排名前N的学生
- 经典排名问题
业务需求“在每组内排名”，比如：每个班级（科目）按成绩来排名
- 在每个组里比较的问题
聚合函数¶
聚合函数 对一组值执行计算，并返回单个值。 除了COUNT 以外，聚合函数都会 忽略空值 。 聚合函数经常与 SELECT 语句的GROUP BY 子句一起使用。
OVER子句 可以跟在除CHECKSUM 以外的所有聚合函数的后面。
-- AVG ( [ ALL | DISTINCT ] expression ) 平均值
-- MIN ( [ ALL | DISTINCT ] expression ) 最小值
-- MAX ( [ ALL | DISTINCT ] expression ) 最大值
-- SUM ( [ ALL | DISTINCT ] expression ) 和
-- VAR ( [ ALL | DISTINCT ] expression ) 方差
-- VARP ( [ ALL | DISTINCT ] expression ) 总体方差
-- STDEV ( [ ALL | DISTINCT ] expression ) 标准差
-- STDEVP ( [ ALL | DISTINCT ] expression ) 总体标准差
-- COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 项数
-- COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) 项数
-- 聚合函数+窗口函数
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER (
[ PARTITION BY value_expression , ... [ n ] ]
[ORDER BY order_by_expression ]
)
--SQL SERVER 2012 及以上版本
select
s.sid,
sc.cid,
s.sname,
s.ssex,
sc.score,
AVG(sc.score) over(partition BY s.ssex) as [不同性别平均分],
AVG(sc.score) over(partition BY sc.cid) as [不同课程平均分],
SUM(sc.score) over(partition BY sc.sid) as [个人总分],
SUM(sc.score) over(partition BY sc.sid order by sc.cid) as [个人累计总分]
from Student s
left join Score sc
on sc.sid = s.sid
order by s.sid, sc.cid
--SQL SERVER 2008 及以前版本
--自连接，利用课程ID来作为累计合计的标志位
with myquery (sid,cid,sname,ssex,score,[不同性别平均分],[不同课程平均分],[个人总分]) as
(select
s.sid,
sc.cid,
s.sname,
s.ssex,
sc.score,
AVG(sc.score) over(partition BY s.ssex) as [不同性别平均分],
AVG(sc.score) over(partition BY sc.cid) as [不同课程平均分],
SUM(sc.score) over(partition BY sc.sid) as [个人总分]
from Student s
left join Score sc
on sc.sid = s.sid
)
select
q1.*,
sum(q2.score) as [个人累计得分]
from myquery as q1
inner join myquery as q2
on q1.sid = q2.sid
and q1.cid >= q2.cid
group by q1.sid,q1.cid,q1.sname,q1.ssex,q1.score,q1.[不同性别平均分],q1.[不同课程平均分],q1.[个人总分]
order by q1.sid, q1.cid
GROUPING()¶
指示是否聚合 GROUP BY 列表中的指定列表达式。在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT 列表、HAVING 和 ORDER BY 子句中。
-- 语法
GROUPING ( <column_expression> )
GROUPING_ID()¶
计算分组级别的函数。仅当指定了 GROUP BY时， GROUPING_ID 才能在 SELECT 列表、HAVING 或 ORDER BY 子句中使用。
-- 语法
GROUPING_ID ( <column_expression>[ ,...n ] )
排名函数¶
排名函数 为分区中的每一行返回一个排名值。根据所用函数的不同，某些行可能与其他行接收到相同的值。排名函数具有不确定性。
-- 排名可能间断（同值同排名）
RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
-- 排名中没有任何间断 （同值同排名）
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
-- 将有序分区中的行分发到指定数目(integer_expression)的组中。
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
-- 结果集分区内行的序列号，每个分区的第一行从 1 开始
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
SELECT p.FirstName, p.LastName
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
数学函数¶
算术函数（例如 ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS 和 SIGN）返回与输入值具有相同数据类型的值。三角函数和其他函数（包括 EXP、LOG、LOG10、SQUARE 和 SQRT）将输入值转换为 float 并返回 float 值。
除 RAND 以外的所有 数学函数 都为确定性函数。 这意味着在每次使用特定的输入值集调用这些函数时，它们都将返回相同的结果。仅当指定种子参数时 RAND 才是确定性函数。
--和角度、弧度相关的数学函数
--π的值
SELECT PI();
--RADIANS(numeric_expression) 返回角度值相应的弧度值
SELECT RADIANS(180.0);
--DEGREES(numeric_expression) 返回弧度值相应的角度值
SELECT DEGREES(PI());
--ACOS (float_expression) 反余弦, 取值范围从-1 到 1
SELECT ACOS(0.0);
--ASIN (float_expression) 反正弦, 取值范围从-1 到 1
SELECT ASIN(0.0);
--ATAN (float_expression) 反正切
SELECT ATAN(0.0);
--ATN2 (float_expression,float_expression) 两个向量间的反正切值
SELECT ATN2(0.0, 1.0);
--COS (float_expression) 余弦
SELECT COS(0.0);
--SIN (float_expression) 正弦
SELECT SIN(0.0);
--COT (float_expression) 余切
SELECT COT(1.0);
--TAN (float_expression) 正切
SELECT TAN(PI()/2);
--常用的一些数据函数
--SELECT ABS(numeric_expression) 绝对值
SELECT ABS(-1);
--CEILING (numeric_expression)大于或等于指定数值表达式的最小整数
SELECT CEILING(2.3);
--FLOOR (numeric_expression)小于或等于指定数值表达式的最大整数
SELECT FLOOR(2.3);
--ROUND(numeric_expression , length [ ,function ]) 舍入
--length 必须是 tinyint、smallint 或 int 类型的表达式。如果 length 为正数，则将 numeric_expression 舍入到 length 指定的小数位数。如果 length 为负数，则将 numeric_expression 小数点左边部分舍入到 length 指定的长度。
SELECT ROUND(123.4567,2);
SELECT ROUND(123.4567,-2);
--SIGN(numeric_expression)返回指定表达式的正号 (+1)、零 (0) 或负号 (-1)
SELECT SIGN(2);
SELECT SIGN(0);
SELECT SIGN(-2);
--RAND([ seed ]) 0到1（不包括 0 和 1）之间的伪随机 float 值
SELECT RAND(100);
SELECT RAND();
--和指数、对数、幂指相关的数学函数
--EXP(float_expression) e的指数值
--指数为1，返回e的值
SELECT EXP(1.0);
--LOG(float_expression) 以e为底的对数值
SELECT LOG(2.718);
--LOG10(float_expression) 以10为底的对数值
SELECT LOG10(100);
--POWER(float_expression,y) float_expression的y幂次的值
SELECT POWER(100,0.5);
--SQRT(float_expression) 平方根
SELECT SQRT(100);
--SQUARE(float_expression) 平方
SELECT SQUARE(10);
字符串函数¶
所有内置字符串函数都是具有确定性的函数。 字符串函数 对字符串输入值执行操作，并返回字符串或数值。
--ASCII(character_expression) 返回最左侧字符的ASCII码值，仅第一个字符
--返回A的ASCII码值65
SELECT ASCII('ABCD');
--UNICODE('ncharacter_expression') 返回unicode字符串中第一个字符的unicode数值
SELECT UNICODE(N'ABCD');
--CHAR(integer_expression) 将ASCII码转换为字符，0至255间整数，否则返回NULL
SELECT CHAR(65);
SELECT CHAR(256);
--CHARINDEX(expression1,expression2[,start_location])
--expression2中搜索expression1 并返回其起始位置（如果找到）。搜索的起始位置为 start_location。
SELECT CHARINDEX('WANG','FIREWANG',1);
--SOUNDEX(character_expression)一个由四个字符组成的代码 (SOUNDEX)，用于评估两个字符串的相似性。
SELECT SOUNDEX('WANG');
SELECT SOUNDEX ('FIREWANG');
--DIFFERENCE(character_expression,character_expression)
--两个字符表达式的 SOUNDEX值 的差异。返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同
SELECT DIFFERENCE('WANG','FIREWANG')
--LEFT(character_expression,integer_expression)字符串从左边开始指定个数的字符
SELECT LEFT('FIREWANG',4);
--RIGHT(character_expression,integer_expression)字符串从右边开始指定个数的字符
SELECT RIGHT('FIREWANG',4);
--SUBSTRING (value_expression ,start_expression ,length_expression )
--返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分。
SELECT SUBSTRING('FIREWANG',1,4);
SELECT SUBSTRING('FIREWANG',5,4);
--LEN ( string_expression )字符串长度，不含尾随空格
SELECT LEN('FIRE');
SELECT LEN('FIRE ');
-- LOWER(character_expression) 全部转换为小写字符
SELECT LOWER('FIREWANG');
-- UPPER(character_expression) 全部转换为大写字符
SELECT UPPER('firewang');
--LTRIM(character_expression)删除前导空格
SELECT LTRIM(' FIRE');
--RTRIM(character_expression)删除尾随空格
SELECT RTRIM('FIRE ');
--NCHAR(integer_expression) unicode值对应的unicode字符，0-65535
SELECT NCHAR(100);
SELECT NCHAR(256);
--PATINDEX('%pattern%',expression ) 在字符或者文本数据中搜索指定模式,
--返回指定表达式中某模式第一次出现的起始位置；否则返回0
SELECT PATINDEX('%FIRE%','FIREWANG');
--QUOTENAME ( 'character_string' [ , 'quote_character' ] )
--返回带有分隔符的 Unicode 字符串，分隔符的加入可使输入的字符串成为有效的 SQL Server 分隔标识符。
--用作分隔符的单字符字符串。可以是单引号 (')、左方括号或右方括号 ([], 默认值) 或者英文双引号 (")。
SELECT QUOTENAME('fire[]wang','""');
SELECT QUOTENAME('fire[]wang','''');
SELECT QUOTENAME('fire[]wang','[]');
SELECT QUOTENAME('fire[]wang')
--REPLACE(完整字符串, 要被替换的字符串 , 用于替换的字符串) 替换字符串
SELECT REPLACE('FIREWANG','FIRE','UPUP');
--REPLICATE(string_expression ,integer_expression) 重复指定次数字符串
SELECT REPLICATE('FIREWANG',2);
--REVERSE ( string_expression ) 逆转字符串
SELECT REVERSE('FIREWANG');
--SPACE ( integer_expression ) 返回重复指定次数的空格
SELECT 'FIRE'+SPACE(2)+'WANG';
--STR(float_expression [ , length [ ,decimal ] ])
--将数字数据转换为字符串。
--length 总长度。它包括小数点、符号、数字以及空格。默认值为 10。
--decimal 小数点右边的小数位数。decimal 必须小于等于 16。
SELECT STR(123.456);
SELECT STR(123.456,5);
SELECT STR(123.456,6,1);
--STUFF(character_expression,start,length,character_expression)
--STUFF函数将字符串插入另一字符串。它在第一个字符串中从开始位置start删除指定长度length的字符；然后将第二个字符串插入第一个字符串的开始位置。
SELECT STUFF('FIREWANG',2,3,'1234567'); --F1234567WANG;
日期和时间函数¶
日期和时间数据类型及函数 的信息和示例
日期和时间数据类型¶
下表列出了 Transact-SQL 的日期和时间数据类型。
| 数据类型 | 格式 | 范围 | 精确度 | 存储大小（字节） | 用户定义的秒的小数精度 | 时区偏移量 |
|---|---|---|---|---|---|---|
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 到 23:59:59.9999999 | 100 纳秒 | 3 到 5 | Y | N |
| date | YYYY-MM-DD | 0001-01-01 到 9999-12-31 | 1 天 | 3 | N | N |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 到 2079-06-06 | 1 分钟 | 4 | N | N |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 到 9999-12-31 | 0.00333 秒 | 8 | N | N |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 | 100 纳秒 | 6 到 8 | Y | N |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示） | 100 纳秒 | 8 到 10 | Y | Y |
-- 示例各个日期、时间数据类型
SELECT
CAST('2020-02-02 12:13:14.1234567' AS time(7)) AS 'time',
CAST('2020-02-02 12:13:14.1234567' AS date) AS 'date',
CAST('2020-02-02 12:13:14.123' AS smalldatetime) AS 'smalldatetime',
CAST('2020-02-02 12:13:14.123' AS datetime) AS 'datetime',
CAST('2020-02-02 12:13:14.1234567' AS datetime2(7)) AS 'datetime2',
CAST('2020-02-02 12:13:14.1234567' AS datetimeoffset(7)) AS 'datetimeoffset';
系统日期和时间值¶
所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。
精度较高 的系统日期和时间函数
SQL Server 2008 R2 使用 GetSystemTimeAsFileTime() Windows API 来获取日期和时间值。精确程度取决于运行 SQL Server 实例的计算机硬件和 Windows 版本。此 API 的精度固定为 100 纳秒。可通过使用 GetSystemTimeAdjustment() Windows API 来确定该精确度。
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| SYSDATETIME | SYSDATETIME () | 时区偏移量未包含在内。 | datetime2(7) | N |
| SYSDATETIMEOFFSET | SYSDATETIMEOFFSET ( ) | 时区偏移量包含在内。 | datetimeoffset(7) | N |
| SYSUTCDATETIME | SYSUTCDATETIME ( ) | 日期和时间作为 UTC 时间（通用协调时间）返回。 | datetime2(7) | N |
SELECT
SYSDATETIME() AS 'SYSDATETIME',
SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET',
SYSUTCDATETIME() AS 'SYSUTCDATETIME';
精度较低 的系统日期和时间函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 | datetime | N |
| GETDATE | GETDATE ( ) | 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 | datetime | N |
| GETUTCDATE | GETUTCDATE ( ) | 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 | datetime | N |
SELECT
CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP',
GETDATE() AS 'DATE',
GETUTCDATE() AS 'UTCDATE';
日期和时间部分值¶
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| DATENAME | DATENAME ( datepart , date ) | 返回表示指定日期的指定 datepart 的字符串。 | nvarchar | N |
| DATEPART | DATEPART ( datepart , date ) | 返回表示指定 date 的指定 datepart 的整数。 | int | N |
| DAY | DAY ( date ) | 返回表示指定 date 的“日”部分的整数。 | int | Y |
| MONTH | MONTH ( date ) | 返回表示指定 date 的“月”部分的整数。 | int | Y |
| YEAR | YEAR ( date ) | 返回表示指定 date 的“年”部分的整数。 | int | Y |
DATENAME() 和DATEPART() 的 datepart参数完全一样，并且datepart的全写和缩写完全等价，DATENAME()和DATEPART()仅在部分datepart下输出值的不同（当然两种输出值的数据类型是完全不一样的）
| datepart参数 | 缩写 | DATENAME返回值 | DATEPART返回值 |
|---|---|---|---|
| year | yy,yyyy | 2020 | 2020 |
| quarter | qq,q | 1 | 1 |
| month | mm, m | February | 2 |
| dayofyear | dy,y | 33 | 33 |
| day | dd,d | 2 | 2 |
| week | wk,ww | 6 | 6 |
| weekday | dw | Sunday | 1 |
| hour | hh | 12 | 12 |
| minute | mi, n | 13 | 13 |
| second | ss, s | 14 | 14 |
| millisecond | ms | 123 | 123 |
| microsecond | mcs | 123456 | 123456 |
| nanosecond | ns | 123456700 | 123456700 |
| TZoffset | tz | +08:00 | 480 |
| ISO_WEEK | isowk,isoww | 5 | 5 |
SELECT '2020-02-02 12:13:14.1234567';
SELECT
DATENAME(year, '2020-02-02 12:13:14.1234567') as 'year',
DATENAME(quarter, '2020-02-02 12:13:14.1234567') as 'quarter',
DATENAME(month, '2020-02-02 12:13:14.1234567') as 'month',
DATENAME(dayofyear, '2020-02-02 12:13:14.1234567') as 'dayofyear',
DATENAME(day, '2020-02-02 12:13:14.1234567') as 'day',
DATENAME(week, '2020-02-02 12:13:14.1234567') as 'week',
DATENAME(weekday, '2020-02-02 12:13:14.1234567') as 'weekday',
DATENAME(hour, '2020-02-02 12:13:14.1234567') as 'hour',
DATENAME(minute, '2020-02-02 12:13:14.1234567') as 'minute',
DATENAME(second, '2020-02-02 12:13:14.1234567') as 'second',
DATENAME(millisecond, '2020-02-02 12:13:14.1234567') as 'millisecond',
DATENAME(microsecond, '2020-02-02 12:13:14.1234567') as 'mocrosecond',
DATENAME(nanosecond, '2020-02-02 12:13:14.1234567') as 'nanosecond',
--返回偏移量
DATENAME(TZoffset, '2020-02-02 12:13:14.1234567 +8:00') as 'TZoffset',
DATENAME(ISO_WEEK, '2020-02-02 12:13:14.1234567') as 'ISO_WEEK';
SELECT
DATEPART(yy, '2020-02-02 12:13:14.1234567') as 'year',
DATEPART(q, '2020-02-02 12:13:14.1234567') as 'quarter',
DATEPART(mm, '2020-02-02 12:13:14.1234567') as 'month',
DATEPART(dy, '2020-02-02 12:13:14.1234567') as 'dayofyear',
DATEPART(dd, '2020-02-02 12:13:14.1234567') as 'day',
DATEPART(wk, '2020-02-02 12:13:14.1234567') as 'week',
DATEPART(dw, '2020-02-02 12:13:14.1234567') as 'weekday',
DATEPART(hh, '2020-02-02 12:13:14.1234567') as 'hour',
DATEPART(mi, '2020-02-02 12:13:14.1234567') as 'minute',
DATEPART(ss, '2020-02-02 12:13:14.1234567') as 'second',
DATEPART(ms, '2020-02-02 12:13:14.1234567') as 'millisecond',
DATEPART(mcs, '2020-02-02 12:13:14.1234567') as 'mocrosecond',
DATEPART(ns, '2020-02-02 12:13:14.1234567') as 'nanosecond',
--返回偏移的分钟数
DATEPART(tz, '2020-02-02 12:13:14.1234567 +8:00') as 'TZoffset',
DATEPART(isowk, '2020-02-02 12:13:14.1234567') as 'ISO_WEEK';
对于DATEPART()，当 datepart 为 week (wk, ww) 或 weekday (dw) 时，返回值取决于使用 SETDATEFIRST 设置的值。
任何年份的 1 月 1 日都用来定义 weekdatepart 的起始数字，例如：DATEPART (wk, ‘Jan 1, xxxx’) = 1，其中 xxxx 为任意年份。
下表列出了针对每个不同的 SET DATEFIRST 参数，“2007-04-21”的 week 和 weekdaydatepart 返回值。1 月 1 日在 2007 年是星期日。4 月 21 日在 2007 年是星期六。SET DATEFIRST 7, Sunday 是美国英语的默认值。
此时相当于星期天被指示为一周的第一天，因此星期六为最后一天，返回值为7。
| SET DATEFIRST参数 | 返回的week | 返回的weekday |
|---|---|---|
| 1 | 16 | 6 |
| 2 | 17 | 5 |
| 3 | 17 | 4 |
| 4 | 17 | 3 |
| 5 | 17 | 2 |
| 6 | 17 | 1 |
| 7 | 16 | 7 |
对于DATEPART() 的 ISO_WEEK ，遵循ISO 8601， 包括 ISO 周-日期系统，即周的编号系统。
每周都与该周内星期四所在的年份关联 。例如，2004 年的第一周 (2004W01) 是指从 2003 年 12 月 29 日（星期一）到 2004 年 1 月 4 日（星期日）。一年中最大的周编号可能是 52 或 53。此样式的编号通常用于欧洲国家/地区，其他地方很少使用。
不同的国家/地区的编号系统可能不符合 ISO 标准。现在至少可能存在六种编号系统，如下表所示：
| 每周的第一天 | 一年的第一周包含 | 分配两次的周 | 使用的国家/地区 |
|---|---|---|---|
| 星期日 | 1 月 1 日，第一个星期六，其中有 1–7 天属于此年 | Y | 美国 |
| 星期一 | 1 月 1 日，第一个星期日，其中有 1–7 天属于此年 | Y | 大多数欧洲国家和英国 |
| 星期一 | 1 月 4 日，第一个星期四，其中有 4-7 天属于此年 | N | ISO 8601，挪威和瑞典 |
| 星期一 | 1 月 7 日，第一个星期一，7 天均属于此年 | N | |
| 星期三 | 1 月 1 日，第一个星期二，其中有 1–7 天属于此年 | Y | |
| 星期六 | 1 月 1 日，第一个星期五，其中有 1–7 天属于此年 | Y |
很容易发现，DAY(), MONTH(), YEAR() 的实现在 DATEPART() 中都已经实现了。
日期和时间差¶
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| DATEDIFF | DATEDIFF ( datepart , startdate , enddate ) | 返回两个指定日期之间所跨的日期或时间 datepart 边界的数目。 | int | Y |
datepart参数与 DATEPART() 中除 TZoffset 和 ISO_WEEK 外完全一致，可以认为 DATEDIFF(datepart , startdate , enddate) 就是 DATEPART(datepart , startdate) 与 DATEPART(datepart , enddate) 的差值
修改日期和时间值¶
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| DATEADD | DATEADD (datepart , number , date ) | 通过将一个时间间隔与指定 date 的指定 datepart 相加，返回一个新的 datetime 值。 | date 参数的数据类型。 | Y |
| SWITCHOFFSET | SWITCHOFFSET (DATETIMEOFFSET , time_zone) | 更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。 | datetimeoffset 具有的小数精度的DATETIMEOFFSET | Y |
| TODATETIMEOFFSET | TODATETIMEOFFSET (expression , time_zone) | 将 datetime2 值转换为 datetimeoffset 值。datetime2 值被解释为指定 time_zone 的本地时间。 | 具有 datetime 参数的小数精度的 datetimeoffset | Y |
DATEADD() 的datepart参数与 DATEPART() 中除 TZoffset 和 ISO_WEEK 外完全一致。 特别的是， 参数中的 number只能是整数，即int值，如果是浮点数，那么会自动被转换为 int值。
SWITCHOFFSET (DATETIMEOFFSET , time_zone) 中 time_zone 是一个格式为 [+|-]TZH:TZM 的字符串，或是一个表示时区偏移量的带符号的整数（分钟数）。time_zone的范围为 +14 到 -13 ，或者是同样长度的分钟数。
SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567','+08:00');
SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567','-08:00');
SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567',60);
TODATETIMEOFFSET(expression , time_zone) 和 SWITCHOFFSET(DATETIMEOFFSET , time_zone) 用法类似，只不过需要 expression参数为返回值为datetime2数据类型的表达式。
设置或获取会话格式¶
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| [@@DATEFIRST](https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms187766(v%3dsql.105)) | @@DATEFIRST | 返回对会话进行 SET DATEFIRST 操作所得结果的当前值。 | tinyint | N |
| SET DATEFIRST | SET DATEFIRST { number | @number_var } | 将一周的第一天设置为从 1 到 7 的一个数字。 | 不适用 | 不适用 |
| SET DATEFORMAT | SET DATEFORMAT { format | @format_var } | 设置用于输入 datetime 或 smalldatetime 数据的日期各部分（月/日/年）的顺序。 | 不适用 | 不适用 |
| [@@LANGUAGE](https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms177557(v%3dsql.105)) | @@LANGUAGE | 返回当前使用的语言的名称。@@LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。 | 不适用 | 不适用 |
| SET LANGUAGE | SET LANGUAGE { [ N ] ‘language’ | @language_var } | 设置会话和系统消息的语言环境。SET LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。 | 不适用 | 不适用 |
| sp_helplanguage | sp_helplanguage [ [ @language = ] ‘language’ ] | 返回有关所有支持语言日期格式的信息。sp_helplanguage 不是日期或时间存储过程。但是，语言设置会影响日期函数的输出。 | 不适用 | 不适用 |
验证日期和时间值¶
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
| ISDATE | ISDATE ( expression ) | 确定 datetime 或 smalldatetime 输入表达式是否为有效的日期或时间值。 | int ， 1或者0 | 只有与 CONVERT 函数一起使用，同时指定了 CONVERT 样式参数且样式不等于 0、100、9 或 109 时，ISDATE 才是确定的。 |
expression: 字符串或者可以转换为字符串表达式。
| ISDATE 表达式 | ISDATE 返回值 |
|---|---|
| date、smalldatetime、datetime | 1 |
| NULL | 0 |
| 除字符串、Unicode 字符串或日期和时间以外的任何数据类型 | 0 |
| text、ntext 或 image 数据类型的值 | 0 |
| 秒精度小数位数超过 3 的任何值（.0000 到 .0000000…n） | 0 |
| 有效日期和无效值混在一起的任何值，例如 1995-10-1a | 0 |
SELECT ISDATE('12:13:14.1234567') AS 'time';
SELECT ISDATE('2020-02-02') AS 'date';
SELECT ISDATE('2020-02-02 12:13:14') AS 'smalldatetime';
SELECT ISDATE('2020-02-02 12:13:14.123') AS 'datetime';
SELECT ISDATE('2020-02-02 12:13:14.1234567') AS 'datetime2';
SELECT ISDATE('2020-02-02 12:13:14.1234567 +8:00') AS 'datetimeoffset';
日期和时间相关主题¶
| 主题 | 说明 |
|---|---|
| 使用日期和时间数据 | 提供通用于日期和时间数据类型及函数的信息和示例（包括日期和时间类型之间的相互转换）。 |
| CAST 和 CONVERT | 提供有关在日期和时间值与字符串文字及其他日期和时间格式之间进行相互转换的信息。 |
| 编写国际化 Transact-SQL 语句 | 提供使用 Transact-SQL 语句的数据库和数据库应用程序在不同语言之间的可移植性准则，或支持多种语言的数据库和数据库应用程序的可移植性准则。 |
| ODBC 标量函数 ) | 提供有关可在 Transact-SQL 语句中使用的 ODBC 标量函数的信息。这包括 ODBC 日期和时间函数。 |
| 分布式查询的数据类型映射 | 提供有关以下方面的信息：日期和时间数据类型对具有不同版本的 SQL Server 或不同访问接口的服务器之间的分布式查询有何影响。 |
利用cast()和convert() 转换日期和时间数据类型。
-- CAST()语法，数据类型之间相互转换:
CAST ( expression AS data_type [ ( length ) ] )
-- CONVERT()语法， 将指定style的数据类型值转化为另一数据类型:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式（回历）的日期格式。
| 不带世纪数位 (yy) (1) | 带世纪数位 (yyyy) | 标准 | 输入/输出 (3) |
|---|---|---|---|
| 0 或 100 (1,2) | 默认 | mon dd yyyy hh:miAM（或 PM） | |
| 1 | 101 | 美国 | mm/dd/yyyy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | 英国/法国 | dd/mm/yyyy |
| 4 | 104 | 德国 | dd.mm.yy |
| 5 | 105 | 意大利 | dd-mm-yy |
| 6 | 106(1) | dd mon yy | |
| 7 | 107(1) | mon dd, yy | |
| 8 | 108 | hh:mi:ss | |
| 9 或 109（1、2） | 默认设置 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM（或 PM） | |
| 10 | 110 | 美国 | mm-dd-yy |
| 11 | 111 | 日本 | yy/mm/dd |
| 12 | 112 | ISO | yymmdd yyyymmdd |
| 13 或 113 (1,2) | 欧洲默认设置 + 毫秒 | dd mon yyyy hh:mi:ss:mmm(24h) | |
| 14 | 114 | hh:mi:ss:mmm(24h) | |
| 20 或 120 (2) | ODBC 规范 | yyyy-mm-dd hh:mi:ss(24h) | |
| 21 或 121 (2) | ODBC 规范（带毫秒） | yyyy-mm-dd hh:mi:ss.mmm(24h) | |
| 126 (4) | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm（无空格） | |
| 127(6, 7) | 带时区 Z 的 ISO8601。 | yyyy-mm-ddThh:mi:ss.mmmZ（无空格） | |
| 130 (1,2) | 回历 (5) | dd mon yyyy hh:mi:ss:mmmAM | |
| 131 (2) | 回历 (5) | dd/mm/yy hh:mi:ss:mmmAM |
--style指定的是源数据的格式
--新数据的格式由数据类型来决定
SELECT CONVERT(date, 'Jan 22 2020 12:13:14', 100);
SELECT CONVERT(datetime, '01/22/2020', 101);
SELECT CONVERT(datetime2, '2020.01.22', 102);
SELECT CONVERT(smalldatetime, '22/01/2020', 103);
SELECT CONVERT(date, '22/01/2020', 104);
SELECT CONVERT(date, '22-01-2020', 105);
SELECT CONVERT(date, '22 Jan 2020', 106);
SELECT CONVERT(date, 'Jan 22,2020', 107);
SELECT CONVERT(time(5), '12:13:14', 108);
SELECT CONVERT(smalldatetime, 'Jan 22 2020 12:13:14.123', 109);
SELECT CONVERT(date, '01-22-2020', 110);
SELECT CONVERT(date, '2020/01/22', 111);
SELECT CONVERT(date, '20200122', 112);
SELECT CONVERT(date, '22 Jan 2020 12:13:14.123', 113);
SELECT CONVERT(time(7), '12:13:14.123', 114);
SELECT CONVERT(datetime, '2020-01-22 12:13:14', 120);
SELECT CONVERT(datetime, '2020-01-22 12:13:14.123', 121);
SELECT CONVERT(smalldatetime, '2020-01-22T12:13:14.123', 126);
SELECT CONVERT(smalldatetime, '2020-01-22T12:13:14.123', 127);
Transact-SQL 表达式¶
表达式是标识符、值和运算符的组合，SQL Server 可以对其求值以获取结果。访问或更改数据时，可在多个不同的位置使用数据。例如，可以将表达式用作要在查询中检索的数据的一部分，也可以用作查找满足一组条件的数据时的搜索条件。
表达式可以是下列任何一种：
- 常量
- 函数
- 列名
- 变量
- 子查询
- CASE、NULLIF 或 COALESCE
还可以用运算符对这些实体进行组合以生成表达式。
Transact-SQL 运算符¶
| 算术运算符 | 含义 |
|---|---|
| +（加） | 加,也可以将一个以天为单位的数字加到日期中 |
| -（减） | 减,也可以从日期中减去一个以天为单位的数字 |
| *（乘） | 乘 |
| / (Divide) | 除 |
| %（取模） | 返回一个除法运算的整数余数. |
| 逻辑运算符 | 含义 |
|---|---|
| ALL | 如果一组的比较都为TRUE,那么就为TRUE. |
| AND | 如果两个布尔表达式都为TRUE,那么就为TRUE. |
| ANY | 如果一组的比较中任何一个为TRUE,那么就为TRUE. |
| BETWEEN | 如果操作数在某个范围之内,那么就为TRUE. |
| EXISTS | 如果子查询包含一些行,那么就为TRUE. |
| IN | 如果操作数等于表达式列表中的一个,那么就为TRUE. |
| LIKE | 如果操作数与一种模式相匹配,那么就为TRUE. |
| NOT | 对任何其他布尔运算符的值取反. |
| OR | 如果两个布尔表达式中的一个为TRUE,那么就为TRUE. |
| SOME | 如果在一组比较中,有些为TRUE,那么就为 TRUE. |
| 比较运算符 | 含义 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| >= | 大于或等于 |
| < | 小于 |
| <= | 小于或等于 |
| != | 不等于 |
| !< | 不小于 |
| !> | 不大于 |
Transact-SQL 注释¶
注释是程序代码中不执行的文本字符串（也称为备注）。注释可用于对代码进行说明或暂时禁用正在进行诊断的部分 Transact-SQL 语句和批。使用注释对代码进行说明，便于将来对程序代码进行维护。
SQL Server 支持两种类型的注释字符：
- –（双连字符）。这些注释字符可与要执行的代码处在同一行，也可另起一行。从双连字符开始到行尾的内容均为注释。对于多行注释，必须在每个注释行的前面使用双连字符。
- \(/* ... */\) （正斜杠-星号字符对）。这些注释字符可与要执行的代码处在同一行，也可另起一行，甚至可以在可执行代码内部。
Transact-SQL 保留关键字¶
Microsoft SQL Server 将保留关键字用于定义、操作和访问数据库。保留关键字是 SQL Server 使用的 Transact-SQL 语言语法的一部分，用于分析和理解 Transact-SQL 语句和批处理。尽管在 Transact-SQL 脚本中使用 SQL Server 保留关键字作为标识符和对象名在语法上是可行的，但规定只能使用分隔标识符。
下表列出了 SQL Server 保留关键字。
| 关键字 | 关键字 | 关键字 |
|---|---|---|
| ADD | EXISTS | PRECISION |
| ALL | EXIT | PRIMARY |
| ALTER | EXTERNAL | |
| AND | FETCH | PROC |
| ANY | FILE | PROCEDURE |
| AS | FILLFACTOR | PUBLIC |
| ASC | FOR | RAISERROR |
| AUTHORIZATION | FOREIGN | READ |
| BACKUP | FREETEXT | READTEXT |
| BEGIN | FREETEXTTABLE | RECONFIGURE |
| BETWEEN | FROM | REFERENCES |
| BREAK | FULL | REPLICATION |
| BROWSE | FUNCTION | RESTORE |
| BULK | GOTO | RESTRICT |
| BY | GRANT | RETURN |
| CASCADE | GROUP | REVERT |
| CASE | HAVING | REVOKE |
| CHECK | HOLDLOCK | RIGHT |
| CHECKPOINT | IDENTITY | ROLLBACK |
| CLOSE | IDENTITY_INSERT | ROWCOUNT |
| CLUSTERED | IDENTITYCOL | ROWGUIDCOL |
| COALESCE | IF | RULE |
| COLLATE | IN | SAVE |
| COLUMN | INDEX | SCHEMA |
| COMMIT | INNER | SECURITYAUDIT |
| COMPUTE | INSERT | SELECT |
| CONSTRAINT | INTERSECT | SESSION_USER |
| CONTAINS | INTO | SET |
| CONTAINSTABLE | IS | SETUSER |
| CONTINUE | JOIN | SHUTDOWN |
| CONVERT | KEY | SOME |
| CREATE | KILL | STATISTICS |
| CROSS | LEFT | SYSTEM_USER |
| CURRENT | LIKE | TABLE |
| CURRENT_DATE | LINENO | TABLESAMPLE |
| CURRENT_TIME | LOAD | TEXTSIZE |
| CURRENT_TIMESTAMP | MERGE | THEN |
| CURRENT_USER | NATIONAL | TO |
| CURSOR | NOCHECK | TOP |
| DATABASE | NONCLUSTERED | TRAN |
| DBCC | NOT | TRANSACTION |
| DEALLOCATE | NULL | TRIGGER |
| DECLARE | NULLIF | TRUNCATE |
| DEFAULT | OF | TSEQUAL |
| DELETE | OFF | UNION |
| DENY | OFFSETS | UNIQUE |
| DESC | ON | UNPIVOT |
| DISK | OPEN | UPDATE |
| DISTINCT | OPENDATASOURCE | UPDATETEXT |
| DISTRIBUTED | OPENQUERY | USE |
| DOUBLE | OPENROWSET | USER |
| DROP | OPENXML | VALUES |
| DUMP | OPTION | VARYING |
| ELSE | OR | VIEW |
| END | ORDER | WAITFOR |
| ERRLVL | OUTER | WHEN |
| ESCAPE | OVER | WHERE |
| EXCEPT | PERCENT | WHILE |
| EXEC | PIVOT | WITH |
| EXECUTE | PLAN | WRITETEXT |
Transact-SQL 语法约定¶
| 约定 | 使用场景 |
|---|---|
| 大写 | Transact-SQL 关键字。 |
| 斜体 | 用户提供的 Transact-SQL 语法的参数。 |
| 粗体 | 数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本。 |
| 下划线 | 指示当语句中省略了包含带下划线的值的子句时应用的默认值。 |
| |（竖线） | 分隔括号或大括号中的语法项。只能使用其中一项。 |
| [ ]（方括号） | 可选语法项。不要键入方括号。 |
| { }（大括号） | 必选语法项。不要键入大括号。 |
| [,…n] | 指示前面的项可以重复 n 次。各项之间以逗号分隔。 |
| […n] | 指示前面的项可以重复 n 次。每一项由空格分隔。 |
| ; | Transact-SQL 语句终止符。 |
| ::= | 语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。可使用语法块的每个位置由括在尖括号内的标签指示：。集是表达式的集合，例如 ；列表是集的集合，例如 。 |
除非另外指定，否则，所有对数据库对象名的 Transact-SQL 引用将是由四部分组成的名称，格式如下：
server_name.[database_name]**.**[schema_name]**.**object_name
database_name.[schema_name]**.**object_name
schema_name.object_name
object_name
- server_name 指定链接的服务器名称或远程服务器名称。
- database_name 如果对象驻留在 SQL Server 的本地实例中，则指定 SQL Server 数据库的名称。如果对象在链接服务器中，则 database_name 将指定 OLE DB 目录。
- schema_name 如果对象在 SQL Server 数据库中，则指定包含对象的架构的名称。如果对象在链接服务器中，则 schema_name 将指定 OLE DB 架构名称。
- object_name 对象的名称。
Select查询¶
查询基础知识¶
查询是对存储在 SQL Server 中的数据的一种请求。可以使用下列几种形式发出查询：
- MS Query 或 Microsoft Access 用户可使用图形用户界面 (GUI) 从一个或多个 SQL Server 表中选择想要查看的数据。
- 使用 SQL Server Management Studio 或 osql 实用工具的用户可发出 SELECT 语句。
- 客户端或基于中间层的应用程序（如 Microsoft Visual Basic 应用程序）可将 SQL Server 表中的数据映射到绑定控件（如网格）。
尽管查询使用多种方式与用户交互，但它们都完成相同的任务：它们为用户提供 SELECT 语句的结果集。即使用户从不指定 SELECT 语句，与使用图形化工具（如 Visual Studio Query Designer）所经常遇到的情况一样，客户端软件可将每个用户查询转换成发送到 SQL Server 的 SELECT 语句。
SELECT 语句从 SQL Server 中检索出数据，然后以一个或多个结果集的形式将其返回给用户。结果集是对来自 SELECT 语句的数据的表格排列。与 SQL 表相同，结果集由行和列组成。
大多数 SELECT 语句都描述结果集的四个主要属性：
- 结果集中的列的数量和属性。对于每个结果集列来说，必须定义下列属性：
- 列的数据类型。
- 列的大小以及数值列的精度和小数位数。
- 返回到列中的数据值的源。
- 从中检索结果集数据的表，以及这些表之间的所有逻辑关系。（From）
- 为了符合 SELECT 语句的要求，源表中的行所必须达到的条件。不符合条件的行会被忽略。(Where)
- 结果集的行的排列顺序。(Order by)
例如下列 SELECT 语句查找单价超过 $40 的产品的产品 ID、名称以及标价
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > $40
ORDER BY ListPrice ASC
在 SELECT 关键字之后所列出的列名（ProductID、Name 和 ListPrice）形成选择列表。此列表指定结果集有三列，并且每一列都具有 Product 表中相关列的名称、数据类型和大小。因为 FROM 子句仅指定了一个基表，所以 SELECT 语句中的所有列名都引用该表中的列。
FROM 子句仅列出 Product 这一个表，该表用来检索数据。
WHERE 子句指定出条件：在 Product 表中，只有 ListPrice 列中的值大于 $40，该值所在的行才符合 SELECT 语句的要求。
ORDER BY 子句指定结果集将基于 ListPrice 列中的值按照升序进行排序 (ASC)。
SELECT语句组成¶
Select的主要子句可归纳如下:
SELECT
[ ALL | DISTINCT ]
[TOP (expression) [PERCENT] [ WITH TIES ] ]
select_list
INTO new_table_name
FROM table_list
[ WHERE search_conditions ]
[ GROUP BY group_by_list ]
[ HAVING search_conditions ]
[ ORDER BY order_list [ ASC | DESC ] ]
select_list 描述结果集的列。它是一个逗号分隔的表达式列表。每个表达式同时定义格式（数据类型和大小）和结果集列的数据来源。通常,每个选择列表表达式都是对数据所在的源表或视图中的列的引用，但也可能是对任何其他表达式（例如，常量或 Transact-SQL 函数）的引用。在选择列表中使用 ***** 表达式可指定返回源表的所有列。
INTO new_table_name 指定使用结果集来创建新表。new_table_name 指定新表的名称。
FROM table_list 包含从中检索到结果集数据的表的列表。这些来源可以是：
- 运行 SQL Server 的本地服务器中的基表。
- 本地 SQL Server 实例中的视图。SQL Server 在内部将一个视图引用按照组成该视图的基表解析为多个引用。
- 链接表。它们是 OLE DB 数据源中的表，称之为“分布式查询”。通过将 OLE DB 数据源链接为链接服务器，或在 OPENROWSET 或 OPENQUERY 函数中引用数据源，可以从 SQL Server 访问 OLE DB 数据源。
FROM 子句还可以包含联接规范。这些联接规范定义了 SQL Server 在从一个表导航到另一个表时使用的特定路径。
FROM 子句还用在 DELETE 和 UPDATE 语句中以定义要修改的表。
WHERE search_conditions WHERE 子句是一个筛选，只有符合条件的行才向结果集提供数据。
WHERE 子句还用在 DELETE 和 UPDATE 语句中以定义目标表中要修改的行。
GROUP BY group_by_list GROUP BY 子句根据 group_by_list 列中的值将结果集分成组。
HAVING search_conditions HAVING 子句是应用于结果集的附加筛选。从逻辑上讲，HAVING 子句是从应用了任何 FROM、WHERE 或 GROUP BY 子句的 SELECT 语句而生成的中间结果集中筛选行。尽管 HAVING 子句前并不是必须要有 GROUP BY 子句，但 HAVING 子句通常与 GROUP BY 子句一起使用。
ORDER BY order_list[ ASC | DESC ] ORDER BY 子句定义了结果集中行的排序顺序。order_list 指定组成排序列表的结果集列。关键字 ASC 和 DESC 用于指定排序行的排列顺序是升序还是降序。
ORDER BY 之所以重要，是因为关系理论规定除非已经指定 ORDER BY，否则不能假设结果集中的行带有任何序列。如果结果集行的顺序对于 SELECT 语句来说很重要，那么在该语句中就必须使用 ORDER BY 子句。
SELECT 语句的逻辑处理顺序¶
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE 或 WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
选择列表¶
结果集列的以下特性由选择列表中的下列表达式定义：
- 结果集列与定义该列的表达式的数据类型、大小、精度以及小数位数相同。
- 结果集列的名称与定义该列的表达式的名称相关联。可选的 AS 关键字可用于更改名称，或者在表达式没有名称时为其分配名称。这样做可以增加可读性。
- 结果集列的数据值通过对结果集的每一行相应的表达式求值而得出。
选择列表还可以包含下列控制结果集最终格式的关键字：
-
- DISTINCT 关键字可从 SELECT 语句的结果中消除重复的行。如果没有指定 DISTINCT，将返回所有行，包括重复的行。
- 空值将被认为是相互重复的内容。不论遇到多少个空值，结果中只返回一个 NULL。
-
- TOP ( expression ) [ PERCENT ] [ WITH TIES ] ; expression 是指定返回行数的数值表达式，如果指定了 PERCENT，则是指返回的结果集行的百分比（由 expression 指定）
TOP (120) /*返回120行*/ TOP (15) PERCENT /* 返回前15%的行结果 */. TOP(@n) /* 返回变量n指定数量的行结果，比如：DECLARE @n AS BIGINT; SET @n = 2 */.
选择列表中的项包括下列内容：
一个简单表达式，例如：对函数、变量、常量或者表或视图中的列的引用。
一个标量子查询。该 SELECT 语句将每个结果集行计算为单个值。
一个复杂表达式，通过对一个或多个简单表达式使用运算符而生成。这使结果集中得以包含基表中不存在，但是根据基表中存储的值计算得到的值。这些结果集列被称为派生列。
表达式可以包含 $ROWGUID 关键字。它解析为对表中具有 ROWGUIDCOL 属性的列的引用。
对数值列或常量使用算术运算符或函数进行的计算和运算
SELECT ProductID, ROUND( (ListPrice * .9), 2) AS DiscountPrice FROM Production.Product WHERE ProductID = 748;
数据类型转换（cast）
SELECT ( CAST(ProductID AS VARCHAR(10)) + ': ' + Name ) AS ProductIDName FROM Production.Product;
CASE 表达式
SELECT ProductID, Name, CASE Class WHEN 'H' THEN ROUND( (ListPrice * .6), 2) WHEN 'L' THEN ROUND( (ListPrice * .7), 2) WHEN 'M' THEN ROUND( (ListPrice * .8), 2) ELSE ROUND( (ListPrice * .9), 2) END AS DiscountPrice FROM Production.Product;
子查询
SELECT Prd.ProductID, Prd.Name, ( SELECT SUM(OD.UnitPrice * OD.OrderQty) FROM AdventureWorks2008R2.Sales.SalesOrderDetail AS OD WHERE OD.ProductID = Prd.ProductID ) AS SumOfSales FROM Production.Product AS Prd ORDER BY Prd.ProductID;
* 符号。如果没有使用限定符指定，星号 (*) 将被解析为对 FROM 子句中指定的所有表或视图中的所有列的引用。
变量赋值的格式为：*@*local_variable = 表达式。SET *@*local_variable 语句也可用于变量赋值。
FROM子句¶
在每一个要从表或视图中检索数据的 SELCET 语句中，都需要使用 FROM 子句。使用 FROM 子句可以：
- 列出选择列表和 WHERE 子句中所引用的列所在的表和视图。可以使用 AS 子句为表和视图的名称指定别名。
- 联接类型。这些类型由 ON 子句中指定的联接条件限定。
FROM 子句是用逗号分隔的表名、视图名和 JOIN 子句的列表。
Transact-SQL 具有扩展功能，支持在 FROM 子句中指定除表或视图之外的其他对象。这些对象返回结果集，也就是 OLE DB 术语中所说的行集，该结果集构成了虚拟表。然后 SELECT 语句就像操作表一样操作这些结果集。
FROM 子句可以指定
一个或多个表或视图
两个或多个表或视图之间的联接（join）
一个或多个派生表，这些派生表是 FROM 子句中的 SELECT 语句，由别名或用户指定的名称引用。FROM 子句中 SELECT 语句的结果集构成了外层 SELECT 语句所用的表。
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID INNER JOIN (SELECT AddressID, City FROM Person.Address) AS d ON bea.AddressID = d.AddressID ORDER BY p.LastName, p.FirstName ;
使用 APPLY 运算符根据左侧输入表中的每一行计算右侧的输入（通常是表值函数），并将所有这些计算的结果合并起来。(数据库兼容级别必须至少为 90)
使用 PIVOT 和 UNPIVOT 运算符来改造输入表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。(数据库的兼容级别需要90以上 )
用 sp_addlinkedserver 定义的链接服务器中的一个或多个表或视图。链接服务器可以是任何 OLE DB 数据源。
OPENROWSET 或 OPENQUERY 函数返回的 OLE DB 行集。
不需要 FROM 子句的 SELECT 语句是那些不从数据库内的任何表中选择数据的 SELECT 语句。这些 SELECT 语句只从局部变量或不对列进行操作的 Transact-SQL 函数中选择数据:
SELECT SYSDATETIME();
SELECT @MyIntVariable;
SELECT @@VERSION;
PIVOT和UNPIVOT¶
PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列（即行转列），来轮替表值表达式。 PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。 与 PIVOT 执行的操作相反，UNPIVOT 将表值表达式的列轮换为行（即列转行）。
但是需要注意得是，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 执行聚合，并将多个可能的行合并为输出中的一行。 UNPIVOT 不重现原始表值表达式的结果，因为行已被合并。
-- PIVOT 语法
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257] )
) AS pvt
ORDER BY pvt.VendorID;
/* pivot result
VendorID Emp1 Emp2 Emp3 Emp4
1492 2 5 4 4
1494 2 5 4 5
1496 2 4 4 5
1498 2 5 4 4
1500 3 4 4 5
*/
-- 查看每个人的年龄，性别，三门课成绩
select
sid,sname,sage,ssex,[语文],[数学],[英语]
from
(
select a.sid,a.sname,a.sage,a.ssex,c.cname,b.score
from Student a
left join Score b
on a.sid=b.sid
left join Course c
on b.cid = c.cid
) source_table
pivot(
sum(score) for
cname in (
[语文],[数学],[英语]
)
) t
student_pivot
将上述结果新建表 Student_pivot
-- unpivot 语法
SELECT [columns not unpivoted],
[unpivot_column],
[value_column],
FROM
(<source query>)
AS <alias for the source data>
UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) )
AS <alias for unpivot>
Where:
--[columns not unpivoted]: 没有被转换的列名。
--[unpivot_column]: 转换的各列所汇总到的单列的名称。
--[value_column]: 转换的各列数据所汇总到的单列的名称。
--<source query>: 源数据。
--<alias for the source data>: 为源数据转换后的表确定一个别名。
--<column_list>: 被转换的列的各列的名称。
--<alias for unpivot>: 转换操作的整个过程的别名。
select
sid,
sname,
sage,
ssex,
subject,
score
from
(select * from Student_pivot) as sp
UNPIVOT(
score for subject in ([语文],[数学],[英语])
) as t
unpivot
特别注意那些成绩为空的行记录都没有出现！
WHERE和HAVING筛选结果¶
SELECT 语句中的 WHERE 和 HAVING 子句可以控制用于生成结果集的源表中的行。WHERE 和 HAVING 是筛选器。这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集。我们称满足搜索条件的行包含在结果集中。
HAVING 子句通常与 GROUP BY 子句一起使用来筛选聚合值的结果。但是，也可以不使用 GROUP BY 而单独指定 HAVING。HAVING 子句指定在 WHERE 子句筛选之后应用的其他筛选器。这些筛选器可应用于选择列表中使用的聚合函数。
理解应用 WHERE、GROUP BY 和 HAVING 子句的正确顺序对编写高效的查询代码会有所帮助：
- WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
- GROUP BY 子句用来分组 WHERE 子句的输出。
- HAVING 子句用来从分组的结果中筛选行。
WHERE 和 HAVING 子句中的搜索条件或限定条件可以包括：
比较运算符，例如：=、< >、< 和 >
SELECT ProductID, Name FROM Production.Product WHERE Class = 'H' ORDER BY ProductID;
范围（BETWEEN 和 NOT BETWEEN）
-- 100到500之间 SELECT ProductID, Name FROM Production.Product WHERE ListPrice BETWEEN 100 and 500 ORDER BY ListPrice;
列表（IN 和 NOT IN）
-- 如果不适用IN，就需要用多个or -- 使用IN更简洁 SELECT ProductID, Name FROM Production.Product WHERE Color IN ('Multi', 'Silver') ORDER BY ProductID;
模式匹配（LIKE 和 NOT LIKE）
SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Ch%' ORDER BY ProductID;
like通配符使用通配符时应着重考虑对性能的影响。如果表达式以通配符开头，则无法使用索引。（正如在电话簿中进行查找一样，如果所给的名称是“%mith”，而不是“Smith”，那么您将不知道需从电话簿的何处开始搜索。）如果通配符位于表达式内部或位于表达式末尾，则可以使用索引。
Null 值（IS NULL 和 IS NOT NULL）
SELECT s.Name FROM Sales.Customer c JOIN Sales.Store s ON c.CustomerID = s.CustomerID WHERE c.CustomerID IS NOT NULL ORDER BY s.Name;
比较 null 值时请谨慎从事。例如，指定 = NULL 与指定 IS NULL 是不同的。
所有记录（=ALL、>ALL、<= ALL、ANY）
-- 从其中已发货的产品量大于任何已发货的 H 类产品量的 SalesOrderDetail 表中检索订单和产品 ID SELECT OrdD1.SalesOrderID, OrdD1.ProductID FROM Sales.SalesOrderDetail OrdD1 WHERE OrdD1.OrderQty > ALL (SELECT OrdD2.OrderQty FROM Sales.SalesOrderDetail OrdD2 JOIN Production.Product Prd ON OrdD2.ProductID = Prd.ProductID WHERE Prd.Class = 'H');
条件的组合（AND、OR、NOT）
SELECT ProductID, Name FROM Production.Product WHERE ListPrice < 500 OR (Class = 'L' AND ProductLine = 'S');
搜索通配符字符¶
当可以搜索通配符字符。有两种方法可指定平常用作通配符的字符：
使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，若要搜索字符串中所有的字符串 5%，请使用：
sql_like将通配符放在方括号 ([ ]) 中。若要搜索连字符 (-) 而不是使用它指定搜索范围，请将连字符作为方括号内的第一个字符：
SELECT ColumnA FROM your_table WHERE ColumnA LIKE '9[-]5';
通配符转义
ORDER BY 排序¶
ORDER BY 子句按一列或多列（最多 8,060 个字节）对查询结果进行排序。
从 SQL Server 2005 开始，SQL Server 允许在 FROM 子句中指定对 SELECT 列表中未指定的表中的列进行排序。
ORDER BY 子句中引用的列名必须明确地对应于 SELECT 列表中的列或 FROM 子句中的表中的列。如果列名已在 SELECT 列表中有了别名，则 ORDER BY 子句中只能使用别名。同样，如果表名已在 FROM 子句中有了别名，则 ORDER BY 子句中只能使用别名来限定它们的列。
排序可以是升序的 (ASC)，也可以是降序的 (DESC)。默认为 ASC。
如果 ORDER BY 子句中指定了多个列，则排序是嵌套的。
无法对数据类型为 text、ntext、image 或 xml 的列使用 ORDER BY。
ORDER BY 子句的准确结果取决于被排序的列的排序规则。对于 char、varchar、nchar 和 nvarchar 列，可以指定 ORDER BY 操作按照表或视图中定义的列的排序规则之外的排序规则执行。可以指定 Windows 排序规则名称或 SQL 排序规则名称。
-- 使用 Traditional_Spanish 排序规则 SELECT LastName FROM Person.Person ORDER BY LastName COLLATE Traditional_Spanish_ci_ai ASC;
子查询 subquery¶
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。
子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。
有三种基本的子查询。它们是：
- 在通过 IN 或由 ANY 或 ALL 修改的比较运算符引入的列表上操作。 WHERE expression [NOT] IN (subquery)
- 通过未修改的比较运算符引入且必须返回单个值。 WHERE expression comparison_operator [ANY | ALL] (subquery)
- 通过 EXISTS 引入的存在测试。 WHERE [NOT] EXISTS (subquery)
许多包含子查询的 Transact-SQL 语句都可以改用联接表示。其他问题只能通过子查询提出。
在 Transact-SQL 中，包含子查询的语句和语义上等效的不包含子查询的语句（即联接的方式）在性能上通常没有差别。但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。所以在这些情况下，联接方式会产生更好的效果。
子查询的 SELECT 查询总是使用圆括号括起来。它不能包含 COMPUTE 或 FOR BROWSE 子句，如果同时指定了 TOP 子句，则只能包含 ORDER BY 子句。
子查询受下列限制的制约：
- 通过比较运算符引入的子查询选择列表只能包括一个表达式或列名称（对 SELECT * 执行的 EXISTS 或对列表执行的 IN 子查询除外）。
- 如果外部查询的 WHERE 子句包括列名称，它必须与子查询选择列表中的列是联接兼容的。
- ntext、text 和 image 数据类型不能用在子查询的选择列表中。
- 由于必须返回单个值，所以由未修改的比较运算符（即后面未跟关键字 ANY 或 ALL 的运算符）引入的子查询不能包含 GROUP BY 和 HAVING 子句。
- 包含 GROUP BY 的子查询不能使用 DISTINCT 关键字。
- 不能指定 COMPUTE 和 INTO 子句。
- 只有指定了 TOP 时才能指定 ORDER BY。
- 不能更新使用子查询创建的视图。
- 按照惯例，由 EXISTS 引入的子查询的选择列表有一个星号 (*)，而不是单个列名。因为由 EXISTS 引入的子查询创建了存在测试并返回 TRUE 或 FALSE 而非数据，所以其规则与标准选择列表的规则相同。
联接 join¶
通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。
联接条件可通过以下方式定义两个表在查询中的关联方式：
- 指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。
- 指定用于比较各列的值的逻辑运算符（例如 = 或 <>）。
可以在 FROM 或 WHERE 子句中指定内部联接；而只能在 FROM 子句中指定外部联接。联接条件与 WHERE 和 HAVING 搜索条件相结合，用于控制从 FROM 子句所引用的基表中选定的行。
比如下列联接因为是内部联接，因此也可以改写为在WHERE条件中指定联接。
-- FROM中指定联接（首选）
SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv
JOIN Purchasing.Vendor AS v
ON (pv.BusinessEntityID = v.BusinessEntityID)
WHERE StandardPrice > 10
AND Name LIKE N'F%';
-- WHERE中指定联接
SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
WHERE pv.VendorID = v.VendorID
AND StandardPrice > 10
AND Name LIKE N'F%';
在 FROM 子句中指定联接条件有助于将这些联接条件与 WHERE 子句中可能指定的其他任何搜索条件分开，建议用这种方法来指定联接。简化的 ISO FROM 子句联接语法如下：
FROM first_table
join_type
second_table
[ON (join_condition)]
join_type 指定要执行的联接类型
内部联接（典型的联接运算，使用类似于 = 或 <> 的比较运算符）。内部联接包括同等联接和自然联接。
外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。
在 FROM 子句中可以用下列某一组关键字来指定外部联接：
LEFT JOIN 或 LEFT OUTER JOIN。
左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行，则在关联的结果集行中，来自右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN
右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN
完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时，另一个表的选择列表列将包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
交叉联接
交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。
join_condition 定义用于对每一对联接行进行求值的谓词（比较运算符或关系运算符）。
当 SQL Server 处理联接时，查询引擎会从多种可行的方法中选择最有效的方法来处理联接。由于各种联接的实际执行过程会采用多种不同的优化，因此无法可靠地预测。
UNION运算符¶
UNION 运算符可以将两个或多个 SELECT 语句的结果组合成一个结果集。
UNION 的结果集列名与 UNION 运算符中第一个 SELECT 语句的结果集中的列名相同。另一个 SELECT 语句的结果集列名将被忽略。
默认情况下，UNION 运算符将从结果集中删除重复的行。如果使用 ALL （即UNION ALL）关键字，那么结果中将包含所有行而不删除重复的行。
使用 UNION 运算符时需遵循下列准则：
在用 UNION 运算符组合的语句中，所有选择列表中的表达式（如列名称、算术表达式、聚合函数等）数目必须相同。
用 UNION 组合的结果集中的对应列或各个查询中所使用的任何部分列都必须具有相同的数据类型，并且可以在两种数据类型之间进行隐式数据转换，或者可以提供显式转换。例如，datetime 数据类型的列和 binary 数据类型的列之间的 UNION 运算符将不执行运算，直到进行了显式转换。但是，money 数据类型的列和 int 数据类型的列之间的 UNION 运算符将执行运算，因为它们可以进行隐式转换。
用 UNION 运算符组合的各语句中对应结果集列的顺序必须相同，因为 UNION 运算符按照各个查询中给定的顺序一对一地比较各列。
表中通过 UNION 运算所得到的列名称是从 UNION 语句中的第一个单独查询得到的。若要用新名称引用结果集中的某列（例如在 ORDER BY 子句中），必须按第一个 SELECT 语句中的方式引用该列
SELECT city AS Cities FROM stores_west UNION SELECT city FROM stores_east ORDER BY city
EXCEPT和INTERSECT半联接¶
使用 EXCEPT 和 INTERSECT 运算符可以比较两个或更多 SELECT 语句的结果并返回非重复值。
- EXCEPT 运算符返回由 EXCEPT 运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。（左边结果与 左右两边结果的交集的差集 A-A∩B）
- INTERSECT 返回由 INTERSECT 运算符左侧和右侧的查询都返回的所有非重复值。（两个查询结果的交集然后去重后的结果，A∩B）
使用 EXCEPT 或 INTERSECT 比较的结果集必须具有相同的结构。它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。
INTERSECT 运算符优先于 EXCEPT。
公用表表达式 WITH¶
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
CTE 可用于：
- 创建递归查询。
- 在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
- 启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
- 在同一语句中多次引用生成的表。
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。
可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。
CTE 由表示 CTE 的表达式名称、可选列列表和定义 CTE 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。
CTE 的基本语法结构如下：
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
-- 运行 CTE 的语句
SELECT <column_list>
FROM expression_name;
-- 定义 CTE 查询别名和列名称
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- 定义CTE查询的结果集
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- 使用CTE查询的结果进行进一步的查询
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
SQL笔试50题¶
表结构¶
-- 创建数据库
create database school;
use school;
-- 建表
-- 学生表：学生编号,学生姓名, 出生年月,学生性别
create table Student(sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
-- 课程表：课程编号, 课程名称, 教师编号
create table Course(cid varchar(10),cname nvarchar(10),tid varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
-- 教师表：教师编号,教师姓名
create table Teacher(tid varchar(10),tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
-- 成绩表：学生编号,课程编号,分数
create table Score(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
create table Student_pivot (
sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10), "语文" int, "数学" int,"英语" int);
insert into Student_pivot values('01' , N'赵雷' , '1990-01-01' , N'男', 80, 90, 99);
insert into Student_pivot values('02' , N'钱电' , '1990-12-21' , N'男', 70, 60, 80);
insert into Student_pivot values('03' , N'孙风' , '1990-05-20' , N'男', 80, 80, 80);
insert into Student_pivot values('04' , N'李云' , '1990-08-06' , N'男', 50, 30, 20);
insert into Student_pivot values('05' , N'周梅' , '1991-12-01' , N'女', 76, 87, null);
insert into Student_pivot values('06' , N'吴兰' , '1992-03-01' , N'女', 31, null, 34);
insert into Student_pivot values('07' , N'郑竹' , '1989-07-01' , N'女', null, 89, 98);
insert into Student_pivot values('08' , N'王菊' , '1990-01-20' , N'女', null,null,null);
table_scheme
-- 查看每个人的年龄，性别，三门课成绩
select
sid,sname,sage,ssex,[语文],[数学],[英语]
from
(
select a.sid,a.sname,a.sage,a.ssex,c.cname,b.score
from Student a
left join Score b
on a.sid=b.sid
left join Course c
on b.cid = c.cid
) source_table
pivot(
sum(score) for
cname in (
[语文],[数学],[英语]
)
) t
all_info
50题¶
- 1.查询“01”课程比“02”课程成绩高的所有学生的学号
select * from
(select * from Score where Score.cid = '01') s1,
(select * from Score where Score.cid = '02') s2
where
s1.sid = s2.sid and
s1.score > s2.score
sql50_1
- 2.查询平均成绩大于60分的同学的学号和平均成绩
SELECT sid,AVG( score ) as mean_score
FROM Score
GROUP BY sid
HAVING AVG( score ) > 60;
sql50_2
- 3.查询所有同学的学号、姓名、选课数、总成绩
SELECT a.sid,a.sname,
count(b.cid) as '选课数',
sum(b.score) as '总成绩'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
order by a.sid
sql50_3
- 4.查询姓“李”的老师的个数；
SELECT
count(1)
FROM Teacher
where tname like N'李%' --建表时字段设置为了Unicode,因此查询也需要加上N
- 5.查询没学过“张三”老师课的同学的学号、姓名；
-- 子查询将张三老师课程的学生id找出来
SELECT
sid, sname
FROM Student
where
sid not in (
select s.sid
from Score s, Course c, Teacher t
where s.cid = c.cid
and c.tid=t.tid
and t.tname=N'张三')
sql50_5
- 6.查询学过“张三”老师所教的课的同学的学号、姓名；
select s.sid, st.sname
from Score s, Course c, Teacher t ,Student st
where s.cid = c.cid
and c.tid=t.tid
and t.tname=N'张三'
and s.sid = st.sid
sql50_6
- 7.查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
select * from
Student
where sid in
(
select s1.sid from
(select * from Score where Score.cid = '01') s1,
(select * from Score where Score.cid = '02') s2
where
s1.sid = s2.sid)
使用 INTERSECT简化SQL
select * from
Student
where sid in
(
select sid from Score where Score.cid = '01'
INTERSECT
select sid from Score where Score.cid = '02'
)
sql50_7
- 8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
-- 和第一题，第七题相似
select sid,sname from
Student where sid in
(
select s1.sid from
(select sid,score from Score where cid = '01') s1,
(select sid,score from Score where cid = '02') s2
where
s1.sid = s2.sid and
s1.score < s2.score)
sql50_8
- 9.查询所有课程成绩小于60分的同学的学号、姓名；
SELECT t.sid, s.sname
FROM
(SELECT DISTINCT sid
FROM Score
GROUP BY sid
HAVING MAX(score) < 60) t
LEFT JOIN Student s
ON t.sid = s.sid
sql50_9
- 10.查询没有学全所有课的同学的学号、姓名
-- 利用第三题的选课数
SELECT a.sid,a.sname,
count(b.cid) as '选课数'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
having count(b.cid) <> (select count(distinct cid) from Course)
order by a.sid
sql50_10
- 11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
select distinct st.sid,st.sname from
Score s, Student st
where st.sid = s.sid
and s.cid in
(select s.cid from
Score s, Student st
where st.sid = s.sid
and st.sid = '01')
and st.sid <> '01'
order by st.sid
sql50_11
- 12.查询和”01”号的同学学习的课程完全相同的其他同学的学号和姓名
-- 此题和11题类似，在11题基础上加上课程数量的限制即可
select st.sid,st.sname from
Score s, Student st
where st.sid = s.sid
group by st.sid, st.sname
having count(s.cid) =
(select count(s.cid) from
Score s, Student st
where st.sid = s.sid
and st.sid = '01')
and st.sid <> '01'
order by st.sid
sql50_12
- 13.把“Score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩
-- update题
- 14.查询没学过”张三”老师讲授的任一门课程的学生姓名
-- 和第六题一样
SELECT
sid, sname
FROM Student
where
sid not in (
select s.sid
from Score s, Course c, Teacher t
where s.cid = c.cid
and c.tid=t.tid
and t.tname=N'张三')
- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
s.sid, s.sname,AVG(sc.score) as mean_score
FROM Student s, Score sc
where
s.sid = sc.sid
and sc.score < 60
group by s.sid, s.sname
having count(sc.cid) >1
sql50_15
- 16.检索”01”课程分数小于60，按分数降序排列的学生信息
SELECT
s.*, sc.score
FROM Student s, Score sc
where
s.sid = sc.sid
and sc.cid = '01'
and sc.score < 60
order by sc.score desc
sql50_16
- 17.按平均成绩从高到低显示所有学生的平均成绩
SELECT
s.sid,s.sname, AVG(sc.score) as mean_score
FROM Student s, Score sc
where
s.sid = sc.sid
group by s.sid,s.sname
order by AVG(sc.score) desc
sql50_17
- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
select
s.cid,
c.cname,
max(s.score) as max_score,
min(s.score) as min_score,
AVG(s.score) as mean_score,
AVG (case when s.score >= 60 then 1.0 else 0.0 end ) as passrate
from Score s, Course c
where s.cid = c.cid
group by s.cid,c.cname
sql50_18
- 19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 就是第十八题的排序
select
s.cid,
c.cname,
AVG(s.score) as mean_score,
AVG (case when s.score >= 60 then 1.0 else 0.0 end ) as passrate
from Score s, Course c
where s.cid = c.cid
group by s.cid,c.cname
order by AVG(s.score) asc, AVG (case when s.score > 60 then 1.0 else 0.0 end ) desc
sql50_19
- 20.查询学生的总成绩并进行排名
-- 使用rank()进行排名
select
s.sid,
s.sname,
sum(sc.score) as total_score,
rank() over(order by sum(sc.score) desc) as score_rank
from Student s, Score sc
where s.sid = sc.sid
group by s.sid, s.sname
order by sum(sc.score) desc
sql50_20
- 21.查询不同老师所教不同课程平均分从高到低显示
select
c.cname,
t.tname,
AVG(s.score) as mean_score
from Course c,Score s, Teacher t
where c.tid = t.tid
and c.cid = s.cid
group by c.cname,t.tname
order by AVG(s.score) desc
sql50_21
- 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- row_number() over(partition by 分组字段 order by 排序字段 排序方式) as 别名
select * from (
select
sc.sid,
s.sname,
s.ssex,
s.sage,
c.cname,
sc.score,
ROW_NUMBER() over(partition BY sc.cid order by score desc) as myrank
from Score sc,Student s,Course c
where sc.sid = s.sid
and sc.cid = c.cid) t
where t.myrank in (2,3)
sql50_22
- 23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
-- 有点琐碎，不知道有没有简便方法
select
c.cid,
c.cname,
SUM(case when sc.score >= 85 and sc.score <= 100 then 1.0 else 0.0 end ) as '[100-85]',
SUM(case when sc.score >= 85 and sc.score <= 100 then 1.0 else 0.0 end ) / count(sc.sid) as '[100-85]百分比',
SUM(case when sc.score >= 70 and sc.score < 85 then 1.0 else 0.0 end ) as '[85-70]',
SUM(case when sc.score >= 70 and sc.score < 85 then 1.0 else 0.0 end )/ count(sc.sid) as '[85-70]百分比',
SUM(case when sc.score >= 60 and sc.score < 70 then 1.0 else 0.0 end ) as '[70-60]',
SUM(case when sc.score >= 60 and sc.score < 70 then 1.0 else 0.0 end )/ count(sc.sid) as '[70-60]百分比',
SUM(case when sc.score >= 0 and sc.score < 60 then 1.0 else 0.0 end ) as '[60-0]',
SUM(case when sc.score >= 0 and sc.score < 60 then 1.0 else 0.0 end ) / count(sc.sid) as '[60-0]百分比'
from Score sc,Course c
where c.cid =sc.cid
group by c.cid,c.cname
sql50_23
- 24.查询学生平均成绩及其名次
--这题和第二十题是一样的
select
s.sid,
s.sname,
AVG(sc.score) as mean_score,
rank() over(order by AVG(sc.score) desc) as score_rank
from Student s, Score sc
where s.sid = sc.sid
group by s.sid, s.sname
order by AVG(sc.score) desc
sql50_24
- 25.查询各科成绩前三名的记录
-- 和第二十二题一样
-- row_number() over(partition by 分组字段 order by 排序字段 排序方式) as 别名
select * from (
select
sc.sid,
s.sname,
s.ssex,
s.sage,
c.cname,
sc.score,
ROW_NUMBER() over(partition BY sc.cid order by score desc) as myrank
from Score sc,Student s,Course c
where sc.sid = s.sid
and sc.cid = c.cid) t
where t.myrank <4
sql50_25
- 26.查询每门课程被选修的学生数
-- 此题只使用Score单表也可以
select
c.cname,
count(s.sid) as '选课人数'
from Score s, Course c
where s.cid = c.cid
group by c.cname
sql50_26
- 27.查询出只选修了一门课程的全部学生的学号和姓名
-- 此题可以在第三题基础上增加限制
-- 没有这样的学生。
SELECT a.sid,a.sname,
count(b.cid) as '选课数'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
having count(b.cid) = 1
- 28.查询男生、女生人数
SELECT
ssex,
count(sid) as '人数'
FROM Student
GROUP BY ssex
- 29.查询名字中含有”风”字的学生信息
SELECT
sid,
sname,
sage,
ssex
FROM Student
WHERE sname like N'%风%' --编码原因加了N，视实际情况而定
sql50_29
- 30.查询同名同性学生名单，并统计同名人数
-- 根据姓名和性别分组即可
SELECT
sname,
ssex,
count(sid)
FROM Student
GROUP BY sname,ssex
sql50_30
- 31.查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT
*
FROM Student
WHERE year(sage) = 1990
sql50_31
- 32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
-- 同第十九题
select
s.cid,
c.cname,
AVG(s.score) as mean_score
from Score s, Course c
where s.cid = c.cid
group by s.cid,c.cname
order by AVG(s.score) asc, s.cid desc
sql50_32
- 33.查询不及格的课程，并按课程号从大到小排列
select
sc.cid,
s.sname,
c.cname,
sc.score
from Score sc, Course c, Student s
where sc.cid = c.cid
and sc.sid = s.sid
and sc.score < 60
order by sc.cid desc
sql50_33
- 34.查询课程编号为”01”且课程成绩在60分以上的学生的学号和姓名
select
s.sid,
s.sname,
sc.score
from Score sc, Course c, Student s
where sc.cid = c.cid
and sc.sid = s.sid
and sc.cid = '01'
and sc.score > 60
sql50_34
- 35.查询所有学生的课程及分数情况
--查看每个人的年龄，性别，三门课成绩
--就是在开头使用的用于便捷判断结果的 all_info
--利用了pivot来行转列
select
sid,sname,sage,ssex,[语文],[数学],[英语]
from
(
select a.sid,a.sname,a.sage,a.ssex,c.cname,b.score
from Student a
left join Score b
on a.sid=b.sid
left join Course c
on b.cid = c.cid
) source_table
pivot(
sum(score) for
cname in (
[语文],[数学],[英语]
)
) t
sql50_35
- 36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select
s.sname,
c.cname,
sc.score
from Score sc, Course c, Student s
where sc.cid = c.cid
and sc.sid = s.sid
and sc.score > 70
sql50_36
- 37.查询课程名称为”数学”，且分数低于60的学生姓名和分数
select
s.sname,
sc.score
from Score sc, Course c, Student s
where sc.cid = c.cid
and sc.sid = s.sid
and sc.score < 60
and c.cname = N'数学'
sql50_37
- 38.查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
--和第三十四题是一样的，混进来的题目？
select
s.sid,
s.sname,
sc.score
from Score sc, Course c, Student s
where sc.cid = c.cid
and sc.sid = s.sid
and sc.cid = '03'
and sc.score > 80
sql50_38
- 39.求每门课程的学生人数
--混进来的题目？
select
cid,
count(sid)
from Score
group by cid
- 40.查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
--利用 top
select
top 1 s.sid, s.sname, sc.score
from Score sc, Course c, Teacher t, Student s
where sc.cid = c.cid
and c.tid=t.tid
and sc.sid = s.sid
and t.tname=N'张三'
sql50_40
- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--同表级联查询
select
distinct
s1.sid,
s1.cid,
s1.score
from Score s1, Score s2
where s1.sid = s2.sid
and s1.score = s2.score
and s1.cid != s2.cid
sql50_41
- 42.查询每门功课成绩最好的前两名
--同第二十二题和第二十五题
--row_number() over(partition by 分组字段 order by 排序字段 排序方式) as 别名
select * from (
select
sc.sid,
s.sname,
s.ssex,
s.sage,
c.cname,
sc.score,
ROW_NUMBER() over(partition BY sc.cid order by score desc) as myrank
from Score sc,Student s,Course c
where sc.sid = s.sid
and sc.cid = c.cid) t
where t.myrank <3
sql50_42
- 43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
cid,
count(sid) as '选修人数'
from Score
group by cid
having count(sid) > 5
order by count(sid) desc, cid asc
sql50_34
- 44.检索至少选修两门课程的学生学号
select
sid,
count(cid) as '选修课程数'
from Score
group by sid
having count(cid) >= 2
sql50_44
- 45.查询选修了全部课程的学生信息
--同第十题（条件相反）
SELECT a.sid,a.sname,
count(b.cid) as '选课数'
FROM Student a
left join Score b
on a.sid = b.sid
group by a.sid,a.sname
having count(b.cid) = (select count(distinct cid) from Course)
order by a.sid
sql50_45
- 46.查询各学生的年龄
--利用SYSDATETIME()/getdate() 获取当前时间
SELECT SYSDATETIME();
SELECT
sid,
sname,
year(SYSDATETIME()) - year(sage) AS '年龄'
FROM Student
sql50_46
- 47.查询本周过生日的学生
select getdate();
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0); -- 本周周一
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 7) ; -- 下周周一
SELECT
*
FROM Student
where DATEADD(year, year(getdate())-year(sage), sage) between
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
and DATEADD(wk, DATEDIFF(wk,0,getdate()), 7)
sql50_47
- 48.查询下周过生日的学生
--同第四十七题
select getdate();
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0); -- 本周周一
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 7) ; -- 下周周一
SELECT
*
FROM Student
where DATEADD(year, year(getdate())-year(sage), sage) between
DATEADD(wk, DATEDIFF(wk,0,getdate()), 7)
and DATEADD(wk, DATEDIFF(wk,0,getdate()), 14)
- 49.查询本月过生日的学生
--利用getdate() 获取当前时间, month()获得月份
SELECT getdate();
select
sid,
sname,
sage,
ssex
from Student
where month(sage) = month(getdate())
sql50_49
- 50.查询下月过生日的学生
--同第四十九题
SELECT getdate();
select
sid,
sname,
sage,
ssex
from Student
where month(sage) = month(getdate())+1
附加题¶
获得连续的日期¶
利用while循环¶
-- 创建临时表#DateTime存储日期
CREATE TABLE #DateTime
(
DayTime DATE
);
-- 连续获取天
DECLARE @StartTime DATE = '2019-03-08', --设置开始时间
@EndTime DATE = '2019-03-18' --设置结束时间
-- 循环获取日期插入临时表
WHILE @StartTime <= @EndTime
BEGIN
INSERT INTO #DateTime (DayTime)
VALUES (@StartTime);
--修改dateadd参数实现连续年、月、日等
SET @StartTime = DATEADD(DAY, 1, @StartTime);
END;
SELECT DayTime FROM #DateTime;
-- 删除临时表
DROP TABLE #DateTime;
利用CTE¶
DECLARE @StartTime DATE = '2018-12-08', -- 开始时间
@EndTime DATE = '2019-03-18' -- 结束时间
;
-- 获取连续天
WITH CteDateDay AS
(
SELECT @StartTime DayTime
UNION ALL
SELECT DATEADD(DAY,1,DayTime) DayTime FROM CteDateDay
WHERE DayTime<@EndTime
)
SELECT DayTime FROM CteDateDay
OPTION (MAXRECURSION 0);
-- 获取连续月
WITH CteDateMonth AS
(
SELECT CONVERT(VARCHAR(7),@StartTime,120) MonthTime
UNION ALL
SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,1,CAST(MonthTime+'-01' AS DATE)),120) DayTime FROM CteDateMonth
WHERE MonthTime<CONVERT(VARCHAR(7),@EndTime,120)
)
SELECT MonthTime FROM CteDateMonth
OPTION (MAXRECURSION 0);
-- 获取连续年
WITH CteDateYear AS
(
SELECT DATEPART(YEAR,@StartTime) YearTime
UNION ALL
SELECT YearTime+1 DayTime FROM CteDateYear
WHERE YearTime<DATEPART(YEAR,@EndTime)
)
SELECT YearTime FROM CteDateYear
OPTION (MAXRECURSION 0)
SQLite介绍¶
SQLite3 是一种轻量型、进程内的关系型数据库， 它实现了一个自包含的、无服务器的、零配置的事务性SQL数据库引擎。 SQLite 直接访问其存储文件。 SQLite 是在世界上最广泛部署的 SQL 数据库引擎。 在DB-Engines上的全球排名年年位居全球第十名。
SQLite于2000年8月17日发布1.0版本，已在近20年时间里发布了305个版本。 最新版本 为2020年1月27号发布的3.31.1版本。
SQLite是遵守ACID[原子性、一致性、隔离性和持久性]的关系数据库管理系统，它包含在一个相对小的C程序库中。 与许多其它数据库管理系统不同，SQLite不是一个客户端/服务器结构的数据库引擎，而是被集成在用户程序中。
SQLite实现了大多数SQL标准。它使用动态的、弱类型的SQL语法。 它作为嵌入式数据库，是应用程序，如网页浏览器，在本地/客户端存储数据的常见选择。 它可能是最广泛部署的数据库引擎，因为它正在被一些流行的浏览器、操作系统、嵌入式系统所使用。 同时，它有许多程序设计语言的语言绑定。 SQLite是D. Richard Hipp创建的公有领域项目，免费使用，但是项目代码闭源。目前由国际团队（https://www.sqlite.org/crew.html）全职开发。
SQLite优点¶
- 不需要一个单独的服务器进程或操作的系统（无服务器的）。
- SQLite不需要配置，这意味着不需要安装或管理。
- 一个完整的SQLite数据库是存储在一个单一的跨平台的磁盘文件。
- SQLite是非常小的，是轻量级的（不到1MiB）。
- SQLite是自给自足的，这意味着不需要任何外部的依赖。
- SQLite事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
- SQLite支持 SQL92（SQL2）标准的大多数查询语言的功能。
- SQLite使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
- SQLite在 UNIX（Linux, Mac OS-X, Android,iOS,*BSD,Solaris）,Windows（Win32, WinCE,WinRT）,VxWorks中运行。
- 同样内容的文件从SQLite中读取比从文件中读取一般要 快35%
SQLite的安装¶
Windows Platform 安装¶
下载 Precompiled Binaries for Windows目录下的 sqlite-dll-win64-x64-3310100.zip 和 sqlite-tools-win32-x86-3310100.zip。根据所选择的下载版本，序列号会有所不同。
创建文件夹 D:mysqlite（可选，自由定义），并在此文件夹下解压上面两个压缩文件， 将得到 sqlite3.def、sqlite3.dll、sqlite3.exe、sqldiff.exe、sqlite3_analyzer.exe 文件。
| 文件 | 用途 |
|---|---|
| sqlite3.exe | 运行主程序 |
| sqlite3.dll | win dll支持文件 |
| sqldiff.exe | 分析两个数据库间差异 |
| sqlite3_analyzer.exe | 分析数据库使用状态 |
添加 D:sqlite3到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果：
D:\>sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
SQLite GUI¶
推荐使用Navicat, 有轻量化要求的话则使用 SQLite Expert
SQLite Database Browser¶
SQLite Database browser 是一个免费、开源的SQLite 数据库的轻量级 GUI 客户端。
SQLite Administrator¶
SQLite Administrator 是一个用来管理 SQLite 数据库文件的图形化工具，可进行创建、设计和管理操作。 提供代码编辑器具有自动完成和语法着色，支持中文。
SQLite Studio¶
SQLiteStudio 是一个开源、跨平台的 SQLite 数据库的管理工具， 支持导出数据格式：CSV、HTML、XML、SQL、PDF、JSON、dBase等。 可同时打开多个数据库文件，支持查看和编辑二进制字段。
SQLite基础信息¶
SQLite语法¶
SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等， 所有的语句以分号（;）结束。
注释是以两个连续的 “-” 字符（ASCII 0x2d）开始， 并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。
也可以使用 C 风格的注释，以 “/*” 开始，并扩展至下一个 “*/” 字符对或直到输入结束，以先到者为准。 SQLite的注释可以跨越多行。
其他的DML, DDL, DQL语法在下文进行讲解。
SQLite点命令¶
| .archive … | Manage SQL archives |
| .auth ON|OFF | Show authorizer callbacks |
| .backup ?DB? FILE | 备份 DB 数据库（默认是 “main”）到 FILE 文件。 |
| .bail on|off | 发生错误后停止。默认为 OFF。 |
| .binary on|off | 是否输出二进制结果。默认为 OFF。 |
| .cd DIRECTORY | 切换工作目录到DIRECTORY |
| .changes on|off | 显示执行的SQL影响的行的数量 |
| .check GLOB | Fail if output since .testcase does not match |
| .clone NEWDB | 将当前数据库的数据复制到数据库NEWDB |
| .databases | 列出数据库的名称及其所依附的文件。 |
| .dbconfig ?op? ?val? | List or change sqlite3_db_config() options |
| .dbinfo ?DB? | 显示数据库状态信息 |
| .dump ?TABLE? … | 以 SQL 文本格式转储数据库。 |
| .echo ON|OFF | 开启或关闭 echo 命令。 |
| .eqp on|off|full|… | Enable or disable automatic EXPLAIN QUERY PLAN |
| .excel | Display the output of next command in spreadsheet |
| .exit ?CODE? | 退出 SQLite 提示符。 |
| .expert | EXPERIMENTAL. Suggest indexes for queries |
| .explain ?on|off|auto? | 开启或关闭适合于 EXPLAIN 的输出模式。默认：auto |
| .filectrl CMD … | Run various sqlite3_file_control() operations |
| .fullschema ?–indent? | Show schema and the content of sqlite_stat tables |
| .headers on|off | 开启或关闭头部显示。 |
| .help ?-all? ?PATTERN? | 显示PATTERN的帮助消息。 |
| .import FILE TABLE | 导入来自 FILE 文件的数据到 TABLE 表中。 |
| .imposter INDEX TABLE | Create imposter table TABLE on index INDEX |
| .indexes ?TABLE? | 显示所有索引的名称。 |
| .limit ?LIMIT? ?VAL? | Display or change the value of an SQLITE_LIMIT |
| .lint OPTIONS | Report potential schema issues. |
| .load FILE ?ENTRY? | 加载一个扩展库。 |
| .log FILE|off | 开启或关闭日志。FILE文件可以是 stderr（标准错误）/stdout（标准输出）。 |
| .mode MODE ?TABLE? |
|
| .nullvalue STRING | 在 NULL 值的地方输出 STRING 字符串。 |
| .once (-e|-x|FILE) | Output for the next SQL command only to FILE |
| .open ?OPTIONS? ?FILE? | Close existing database and reopen FILE |
| .output ?FILE? | 发送输出到 FILE 文件或者 stdout |
| .parameter CMD … | Manage SQL parameter bindings |
| .print STRING… | 逐字地输出 STRING 字符串。 |
| .progress N | Invoke progress handler after every N opcodes |
| .prompt MAIN CONTINUE | 替换标准提示符。 |
| .quit | 退出 |
| .read FILE | 读取FILE |
| .recover | 尽可能恢复数据库数据 |
| .restore ?DB? FILE | Restore content of DB (default “main”) from FILE |
| .save FILE | 将内存内数据库写入FILE |
| .scanstats on|off | Turn sqlite3_stmt_scanstatus() metrics on or off |
| .schema ?PATTERN? | Show the CREATE statements matching PATTERN |
| .selftest ?OPTIONS? | Run tests defined in the SELFTEST table |
| .separator COL ?ROW? | 改变列、行的分隔符 |
| .sha3sum … | 对数据库内容计算SHA3的hash值 |
| .shell CMD ARGS … | Run CMD ARGS… in a system shell |
| .show | 显示各种设置的当前值。 |
| .stats ?on|off? | 显示统计，开启或关闭统计。 |
| .system CMD ARGS … | Run CMD ARGS… in a system shell |
| .tables ?TABLE? | 列出匹配 LIKE 模式的表的名称。 |
| .testcase NAME | Begin redirecting output to ‘testcase-out.txt’ |
| .testctrl CMD … | Run various sqlite3_test_control() operations |
| .timeout MS | 尝试打开锁定的表 MS 毫秒。 |
| .timer on|off | 开启或关闭 CPU 定时器（SQL timer）。 |
| .trace ?OPTIONS? | Output each SQL statement as it is run |
| .vfsinfo ?AUX? | Information about the top-level VFS |
| .vfslist | List all available VFSes |
| .vfsname ?AUX? | Print the name of the VFS stack |
| .width NUM1 NUM2 … | 为 “column” 模式设置列宽度。 |
SQLite创建数据库¶
$sqlite3 [DatabaseName].db
SQLite创建表
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
columnN datatype,
)；
创建完之后。便可以使用.tables查看相关的表，使用..schema tablename查看表具体信息。使用DROP TABLE database_name.table_name即可删除表，一旦删除表中信息将无法找回了。
SQLite的增、删、改、查和之前学过的MySQL、Oracle语法一样，均是采用标准SQL格式, 简单如下图所示：
更新
查询read 系统级信息 所有表名 表内列名 表schema
删除 delete
Python操作SQLite¶
Python 2.5.x以上版本内置了SQLite3，使用时直接import sqlite3即可，而SQLite3 模块是由 Gerhard Haring 编写的。
python操作流程大概分为以下五步 通过sqlite3.open()创建与数据库文件的连接对象connection； 通过connection.cursor()创建光标对象cursor； 通过cursor.execute()执行SQL语句(CURD)； 通过connection.commit()提交当前的事务，或者通过cursor.fetchall()获得查询结果； 通过connection.close()关闭与数据库文件的连接。 建立连接：
import sqlite3 conn = sqlite3.connect(‘testDB.db’)
“testDB.db”是前面所创建的数据库，当没有此数据库时也会自动创建一个。连接到数据库以后，按照上边的步骤就需要创建光标对象cursor 。 cursor = conn.cursor()
接下来就可以使用cursor.execute()直接执行SQL语句了。
建立数据库表：
cursor.execute(‘create table student(id int PRIMARY KEY,name text,age int)’)
插入数据库表语句 cursor.execute(“insert into student(id,name,age) values (1,’zhangsan’,22)”) cursor.execute(“insert into student values (2,’lisi’,24)”) cursor.execute(“insert into student values (?,?,?)”,(3,”wangwu”,25)) conn.commit() –插入完之后提交
经过提交后使用以下语句查询 cursor.execute(‘select * from student’) cursor.fetchall()
SQLite3更新语句 cursor.execute(“update student set id=0 where age =22 ”) >>> cursor.execute(“update student set id=1 where name =’lisi’”) >>> conn.commit() >>> cursor.execute(“select * from student”) >>> cursor.fetchall() [(0, u’zhangsan’, 22), (1, u’lisi’, 24), (3, u’wangwu’, 25)]
SQLite3删除语句
cursor.execute(“delete from student where age = ? “,(25, ));
当使用词语去删除时报参数错误，Python认为传递的字符串是一个元组，导致参数过多报错，传递一个参数时括号里一定要加逗号，不然Python会认为是数字，会报不支持的参数类型错误。而使用标准的语法删除时是没有问题的cursor.execute(“delete from student where id = 0 “)；建议使用此语法删除。
关闭连接 >>> cursor.close() >>> conn.close()
连接对象 conn
游标对象 cursor
异常处理 https://docs.python.org/3/library/sqlite3.html#exceptions
更新python内sqlite python内置sqlite3升级(是对SQLite的升级) 从官网下载相应安装包 替换 sqlite3.dll ， sqlite3.exe
默认安装下升级 替换路径下.dll 文件 C:Python35DLLs
anaconda下升级 C:ProgramDataAnaconda3Librarybin
python使用sqlite3模块集成了SQLite数据库 import sqlite3 print(sqlite3.version_info) #显示python sqlite3模块的版本信息 print(sqlite3.sqlite_version) #显示SQLite3 数据库的版本信息
SQLite C/C++ API¶
https://sqlite.org/cintro.html
SQLite重点对外提供2个对象8个方法。 sqlite3 → The database connection object. Created by sqlite3_open() and destroyed by sqlite3_close().
sqlite3_stmt → The prepared statement object. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().
sqlite3_open() → Open a connection to a new or existing SQLite database. The constructor for sqlite3.
sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the database. The constructor for sqlite3_stmt.
sqlite3_bind() → Store application data into parameters of the original SQL.
sqlite3_step() → Advance an sqlite3_stmt to the next result row or to completion.
sqlite3_column() → Column values in the current result row for an sqlite3_stmt.
sqlite3_finalize() → Destructor for sqlite3_stmt.
sqlite3_close() → Destructor for sqlite3.
sqlite3_exec() → A wrapper function that does sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and sqlite3_finalize() for a string of one or more SQL statements.
SQLite参考资源¶
SQLite数据库参考内容
https://www.sqlite.org/docs.html
http://www.runoob.com/sqlite/sqlite-python.html
https://www.cnblogs.com/webapplee/p/3767450.html
python内置模块sqlite3