第四章 数据库T-SQL语言基础

1、1审计署数据库应用技术 李春强北京信息科技大学信息管理学院2章节安排z 第1章 数据库基础概述z 第2章 SQL Server2008概述z 第3章 数据库的创建与管理z 第第4章章 TransactSQL语言基础语言基础z 第5章 表的创建与管理z 第6章 数据的查询与数据操作z 第7章 视图z 第8章 存储过程z 第9章 游标z 第10章 安全管理z 第11章 备份和恢复数据库z 第12章 数据导入导出z 第13章 Acces数据库及表的基本操作 z 第14章 查询对象 z 第15章 报表对象 3第 4 章 SQL 语言基础 z4.1 T-SQL基本概念基本概念z4.2 T-SQL数据类型

2、数据类型z4.3 T-SQL语法元素语法元素 z4.4 流程控制语句流程控制语句z4.5 常用内置函数常用内置函数4uSQLSQL是是Structured Query LanguageStructured Query Language（结（结构化查询语言）的缩写构化查询语言）的缩写uSQLSQL已经成为关系数据库的标准数据语言，已经成为关系数据库的标准数据语言，所以现在所有的关系数据库管理系统都所以现在所有的关系数据库管理系统都支持支持SQLSQL 4.1 基本概念5 1986年10月由美国ANSI 公布最早的SQL标准 1989年4月，ISO提出了具备完整性特征的SQL，称为SQL89 19

3、92年11月，ISO又公布了新的SQL标准，称为SQL92 1999年，ISO又公布了新的SQL标准，称为SQL3 SQL发展史6SQL 特点z是一种一体化的语言。z是一种高度非过程化的语言。z 简洁 z以多种方式使用7SQL 功能z数据定义功能（DDL）：用户定义、删除和修改数据模式z数据查询功能（DQL）：用于查询数据z数据操纵功能（DML）：用于增、删、改数据z数据控制功能（DCL）：用于控制数据访问权限8SQLSQL的简洁的简洁SQL功能功能命令动词命令动词数据查询数据查询SELECT数据定义数据定义CREATE、ALTER、DROP数据操纵数据操纵INSERT、UPDATE、DELE

4、TE数据控制数据控制GRANT、REVOKE、DENYSQLSQL的动词的动词94.2 SQL数据类型 z数值型 z字符串型z日期、时间型z货币类型101. 数值型z 定长（准确表达） y整数 Bigint: 8字节， Int：4字节 Smallint：2字节， Tinyint：1字节 Bit：1位，存储1或0y小数Numeric（p,q）或Decimal（p,q）， 其中：p为数字位长度，q：小数位长度。z 浮点（近似）数：y Float：8字节y Real：4字节112. 字符串型z普通编码字符串类型z统一字符编码字符串类型z二进制字符串类型12普通编码字符串类型zChar（n）:定长存储

5、，n8000 zVarchar（n）：不定长存储（按实际长度存储），长度最大不超过n , n8000 注：n 为字节个数zText：存储大于8000字节的文本13统一字符编码字符串类型znchar（n）:定长存储，n4000 znvarchar（n）：不定长存储，长度最大不超过n , n4000 注 N字符个数zntext：存储大于8000字节的文本z特点：每个字符占两个字节14二进制字符串类型zBinary(n)：固定长度，n 8000，占用n + 4个字节的存储空间zVarbinary(n) ：可变长度，n 8000 ，占用实际长度 + 4个字节的存储空间 注：n为二进制数据的字节数zim

6、age ：大容量、可变长二进制字符数据，可用于存储文件153. 日期时间型 zDatetime：8字节，年月日时分秒毫秒zSmallDateTime： 4字节，年月日时分（例：z日期、时间 的输入格式16例：将带秒数的字符串文字转换为smalldatetimeSELECT CAST(2007-05-08 12:35:29 AS smalldatetime),CAST(2007-05-08 12:35:30 AS smalldatetime),CAST(2007-05-08 12:59:59.998 AS smalldatetime)17例：将一个字符串分别转换为各种日期和时间数据类型时所产生的

7、结果SELECT CAST(2007-05-08 12:35:29.1234567 AS time(7) AS time ,CAST(2007-05-08 12:35:29.1234567 AS date) AS date,CAST(2007-05-08 12:35:29.123 AS smalldatetime) AS smalldatetime ,CAST(2007-05-08 12:35:29.123 AS datetime) AS datetime184. 货币类型z 表示正的或负的货币值z Money：精确到货币单位的千分之十。存储大小为 8 个字节。z Smallmoney：精确到

8、货币单位的千分之十。存储大小为 4 个字节。z 限制到小数点后 4 位。z 货币数据不需要用单引号 () 括起来。但在货币数值可以带有适当的货币符号。例如，若要指定 100 英镑，使用 100。 194.3 T-SQL的语法元素z语句批z标示符z脚本z注释y单行注释符：-y多行注释符：/* */z变量z全局变量20语句批z语句批批处理是包含一个或多个T-SQL语句的组，从应用程序一次性地发送到SQL Server进行执行。zSQL Server将批处理的语句编译为一个可执行单元，称为执行计划。z执行计划中的语句每次执行一条。z一个批以GO为结束标记。y GO不是T-SQL语句y 由sqlcmd

9、和osql实用工具以及 SQL Server Management Studio 代码编辑器识别的命令 21T-SQL语法元素使用约定约定约定 用于用于 大写T-SQL 关键字。|（竖线）分隔括号或大括号中的语法项。只能使用其中一项。 （方括号）可选语法项。不要键入方括号。 （大括号）必选语法项。不要键入大括号。,.n指示前面的项可以重复 n 次。各项之间以逗号分隔。 .n指示前面的项可以重复 n 次。每一项由空格分隔。 :=语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。22对象引用规范z 数据库包括表、视图和存储过程等对象，对数据库对象名的T-SQL

10、引用由四部分组成，具体格式如下： 服务器名称.数据库名称.构架名称. |数据库名称. 构架名称. |构架名称. 对象名z 其中：z 服务器名称指定链接服务器名称或远程服务器名称。z 当对象驻留在SQL Server数据库中时，数据库名称指定该SQL Server数据库的名称。当对象在链接服务器中时则指定OLE DB目录。z 架构是包含表、视图、存储过程等数据库对象的容器。23引用对象名的格式引用对象名的格式说明说明server.database.schema.object4个部分的名称server.database.object省略架构名称server.schema.object省略数据库名称

11、serverobject省略数据库和架构名称database.schema.object省略服务器名database.object省略服务器和架构名称schema.object省略服务器和数据库名称object省略服务器、数据库和架构名称24标识符z标识符是诸如表、视图、列、数据库和服务器等对象的名称。z对象标识符是在定义对象时创建的，标识符随后用于引用该对象。zSQL Server的标识符分为两类：常规标识符和分隔标识符。25常规标识符z常规标识符是指符合标识符格式规则的标识符，在T-SQL语句中使用常规标识符时不需要将其分割。例如：zSELECT * FROM BankT -查询BankT

12、表中的所有信息y标识符BankT为常规标识符。z常规标识符格式规则取决于数据库兼容级别。当兼容级别为 90 时，下列规则适用： y第一个字符必须是下列字符之一：x 英文字母az和AZ、来自其他语言的字母字符、下划线 （_）、at 符号 （） 或数字符号 （#）。 y后续字符可以包括：x英文字母az和AZ、来自其他语言的字母字符、十进制数字、at 符号 （）、美元符号 （$）、数字符号 （#）或下划线（_）。x标识符一定不能是 T-SQL 保留字。x不允许嵌入空格或其他特殊字符。26分隔标识符z分隔标识符包含在双引号（“”）或方括号（）内。z符合标识符格式规则的标识符可以分隔，也可以不分隔。但是

13、，不符合标识符格式规则的标识符必须进行分隔。例如：ySELECT * FROM My Table WHERE order=10-查询My Table表中order属性为10的所有信息y因为My和Table之间存在空格，不符合标识符格式规则，所以必须使用分隔标识符，否则系统会认为它们是两个标识符，从而报错。order也必须使用分隔标识符，因为order是SQL Server的保留字，用于order by子句。27脚本z脚本是存储在文件中的一系列T-SQL语句，该文件可以在SQL Server Management Studio的SQL编辑器中编写和运行。 28变量1种类（1）全局变量：变量名：系

14、统用于记录信息（2）局部变量：变量名：用户使用2. 声明局部变量: DECLARE 变量名 类型29变量3赋值: SET 变量名=值| 表达式 或 SELECT 变量名=值 | 表达式4显示变量的值 print 变量名 或 SELECT 变量名30DECLARE name nchar(3)-声明局部变量nameDECLARE grade numeric(3,1)-声明局部变量gradeSET name=王华 -为变量name赋值SET grade=90.5 -为变量grade赋值31DECLARE name nchar(3) -声明局部变量nameDECLARE grade numeric(3

15、,1) -声明局部变量gradeSET name=王华 -为变量name赋值SET grade=90.5 -为变量grade赋值PRINT name+的成绩为： -输出变量值PRINT grade32例：交换a，b两个字符型变量的值 DECLARE a char(3),b char(3) -声明a，b两个变量DECLARE c char(3) -在交换过程中使用到的中间变量cSET a=YES -为变量a赋值SET b=NO -b为变量赋值PRINT 交换前：a=+a+ b=+bSET c=a -交换a和b的值SET a=bSET b=cPRINT 交换后：a=+a+ b=+b33全局变量z

16、全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。z 全局变量通常存储一些SQL Server的配置设置值和效能统计数据。z 用户可在程序中用全局变量来测试系统的设定值或者T-SQL命令执行后的状态值。z 引用全局变量时，全局变量名必须以“”开头。z 不能定义与全局变量同名的局部变量。z 从SQL Server 7.0开始，全局变量就以系统函数的形式使用。例如：y通过全局变量ERROR的值获取系统的错误信息y通过全局变量SERVERNAME的值获取本地服务器名称y通过全局变量VERSION的值获取当前SQL Server的版本号34流程控

17、制语句流程控制语句功能说明功能说明BEGINEND定义语句块IF条件处理语句CASE选择表达式WHILE循环语句GOTO无条件跳转语句RETURN无条件退出语句WAITFOR延迟语句BREAK跳出循环语句CONTINUE重新开始循环4.4流程控制语句354.4.1 语句块格式： BEGIN 语句序列 END 364.4.2 分支语句格式： IF 条件表达式 语句块1 ELSE 语句块2 不成立不成立成立成立条件条件语句块语句块2语句块语句块1后续语句后续语句37IF MONTH(GETDATE()7 PRINT(上半年)ELSE PRINT(下半年)38IF MONTH(GETDATE()7

18、BEGIN PRINT(上半年) PRINT(GETDATE() ENDELSE BEGIN PRINT(下半年) PRINT(GETDATE () END 394.4.3 循环语句格式：WHILE 条件表达式 循环体语句块成立成立不成立不成立条件表达式条件表达式循环体语句块循环体语句块后续语句后续语句40DECLARE i intSET i=1WHILE i3000 THEN 高额贷款 WHEN amount BETWEEN 500 AND 3000 THEN 一般贷款 WHEN amount500 THEN 低额贷款ENDPRINT Level444.5常用的内置函数z聚合函数z日期和时间

19、函数z字符串函数z类型转换函数z其他函数类型451.聚合函数z聚合函数对一组值执行计算并返回单一的值。z聚合函数经常与SELECT语句的GROUP BY子句一同使用。z所有的聚合函数都为确定性函数。46z聚合函数：yAVG、COUNT、MAX、MIN、SUMz聚合函数只能在以下位置作为表达式使用ySELECT 语句的选择列表（子查询或外部查询）。yCOMPUTE 或 COMPUTE BY 子句。yHAVING 子句。47AVGAVGz功能：返回表达式的平均值（忽略任何空值）。z语法：AVG ( ALL | DISTINCT expression )z参数说明：y ALL：对所有的值进行聚合函数

20、运算。ALL 是默认值。y DISTINCT：指定 AVG 只在每个值的唯一实例上执行，而不管该值出现了多少次。y expression：是精确数值或近似数值数据类别（bit数据类型除外）的表达式。不允许使用聚合函数和子查询。z返回值类型：由表达式的计算结果类型确定。48例：查询例：查询AdventureWorksAdventureWorks数据库的数据库的Production.ProductProduction.Product表中所有产品的平均销售价格（表中所有产品的平均销售价格（ListPriceListPrice） USE AdventureWorks -指定 AdventureWork

21、s数据库为要使用的数据库GoSELECT AVG(ListPrice) -查询命令FROM Production.Product49COUNTz 功能：返回组中的项数。z 语法：COUNT ( ALL | DISTINCT expression | * )z 参数说明：yALL：对所有的值进行聚合函数运算。ALL 是默认值。yDISTINCT：指定 COUNT 返回唯一非空值的数量。y*：指定应该计算所有行以返回表中行的总数。COUNT(*) 不需要表达式参数，因为根据定义，该函数不使用有关任何特定列的信息。COUNT(*) 返回指定表中的行数而不删除副本。它对各行分别计数。包括包含空值的行。

22、yCOUNT(ALL|DISTINCT expression) 对组中的每一行都计算表达式并返回非空值的数量。z 返回值类型：int。50例：查询例：查询AdventureWorksAdventureWorks数据库的数据库的HumanReSources.EmployeeHumanReSources.Employee表的总行数表的总行数 USE AdventureWorksGoSELECT COUNT(*)FROM HumanReSources.EmployeeUSE AdventureWorksGoSELECT COUNT(EmployeeID)FROM HumanReSources.Emp

23、loyee51例：统计例：统计AdventureWorksAdventureWorks数据库的数据库的Production.ProductProduction.Product表中的产品总共有几种颜色（表中的产品总共有几种颜色（ColorColor） USE AdventureWorksGoSELECT Count(DISTINCT Color)FROM Production.Product52MAXMAXz功能：返回表达式的最大值（忽略任何空值）。对于字符列，MAX查找按排序序列排列的最大值。z语法：MAX( ALL | DISTINCT expression)z参数说明：同函数AVG的参数说

24、明。z返回值类型：与表达式类型相同。53例：查询例：查询AdventureWorksAdventureWorks数据库的数据库的Production.ProductProduction.Product表中所有产品的最高销售价格（表中所有产品的最高销售价格（ListPriceListPrice） USE AdventureWorksGoSELECT MAX(ListPrice)FROM Production.Product54MINMINz功能：返回表达式的最小值（忽略任何空值）。对于字符列，MIN查找按排序序列排列的最低值。z语法：MIN ( ALL | DISTINCT expression

25、)z参数说明：同函数AVG的参数说明。z返回值类型：与表达式类型相同。 55例：查询例：查询AdventureWorksAdventureWorks数据库的数据库的Production.ProductProduction.Product表中所有产品的最低销售价格（表中所有产品的最低销售价格（ListPriceListPrice） USE AdventureWorksGoSELECT MIN(ListPrice)FROM Production.Product56SUMSUMz功能：返回表达式中所有值的和或仅非重复值的和（忽略任何空值）。SUM 只能用于数字列。z语法：SUM ( ALL | DI

26、STINCT expression)z参数说明：同函数AVG的参数说明。z返回值类型：以最精确的表达式数据类型返回所有表达式值的和 57例：查询例：查询AdventureWorksAdventureWorks数据库的数据库的Production.ProductProduction.Product表中所有产品的销售价格（表中所有产品的销售价格（ListPriceListPrice）总和）总和USE AdventureWorksGoSELECT SUM(ListPrice)FROM Production.Product582.日期和时间函数z GETDATEz DATEADDz DATEDIFFz

27、 DATENAMEz DATEPART59GETDATEGETDATEz 功能：返回以SQL Server内部格式表示的当前日期和时间z 语法：GETDATE( )z 返回值类型：datetime例：查询当前的系统日期和时间例：查询当前的系统日期和时间 SELECT GETDATE()60DATEADDDATEADDz功能：返回指定日期加上一个时间间隔后的新值z语法：DATEADD (datepart , number, date )z参数说明：y detepart：指定要返回新值的日期的组成部分y number：是用于与datepart相加的值。如果指定了非整数值，则将舍弃该值的小数部分，不

28、进行舍入y date：用于返回datetime或smalldatetime值或日期格式的字符串z返回值类型：返回数据类型为 date 参数的数据类型，字符串文字除外。字符串文字的返回数据类型为 datetime 61例：计算例：计算20102010年年3 3月月2020日加上日加上1818天后的日期天后的日期SELECT DATEADD(day,18,2010/3/20)62DATEDIFFDATEDIFFz功能：返回跨两个指定日期的日期边界数和时间边界数z语法：DATEDIFF ( datepart , startdate , enddate )z参数说明：y datepart：指定要返回新

29、值的日期的组成部分y startdate：计算的开始日期，是返回datetime或smalldatetime值或日期格式字符串的表达式。y enddate：计算的结束日期，是返回datetime或smalldatetime值或日期格式字符串的表达式。z返回值类型：int 63计算2010年3月20日至2010年10月1日之间的天数SELECT DATEDIFF(day,2010/3/20,2010/10/1)64DATENAMEDATENAMEz功能：返回表示指定日期的指定日期部分的字符串z语法：DATENAME ( datepart , date )z参数说明：ydatepart：指定要返回

30、的日期部分ydate：是返回datetime或smalldatetime值或日期格式字符串的表达式z返回值类型：nvarchar例：查询当前系统日期中的年份例：查询当前系统日期中的年份 SELECT DATENAME(year,GETDATE()65DATEPARTDATEPARTz功能：返回表示指定日期的指定日期部分的整数。z语法：DATEPART ( datepart , date )z参数说明：ydatepart：指定要返回的日期部分ydate：是返回datetime或smalldatetime值或日期格式字符串的表达式z返回值类型：int例：查询当前系统日期中的月份例：查询当前系统日期

31、中的月份 SELECT DATEPART(month,GETDATE()663.字符串函数z字符串函数用于对字符和二进制字符串进行各种操作 z常用的字符串函数：y CHARINDEXy LEFTy RIGHTy LENy SUBSTRINGy LTRIMy RTRIMy REPLACE67CHARINDEXCHARINDEXz 功能：在字符串表达式expression2中搜索expression1出现时的字符位置并返回。如果在expression2内找不到expression1，则返回0。z 语法：CHARINDEX ( expression1 ,expression2 , start_loc

32、ation )z 参数说明：yexpression1：包含要查找的序列的字符表达式。yexpression2：要搜索的字符表达式。ystart_location：表示搜索起始位置的整数或 bigint 表达式。如果未指定 start_location，或者 start_location 为负数或 0，则将从 expression2 的开头开始搜索。z 返回值类型：如果 expression2 的数据类型为 varchar(max)、nvarchar(max) 或 varbinary(max)，则为 bigint，否则为 int68例：查询字符串“ghe”在字符串“abieghed”中 的起始位

33、置 SELECT CHARINDEX(ghe,abieghed)例：查询从字符串例：查询从字符串“aghebieghed”第第4个位个位置开始查找字符串置开始查找字符串“ghe”的起始位置的起始位置 SELECT CHARINDEX(ghe,aghebieghed,4) 69LEFTLEFTz功能：返回字符串中从左边开始指定个数的字符，字符串左边的空格也属于有效字符。z语法：LEFT ( character_expression , integer_expression )z参数说明：ycharacter_expression：字符或二进制数据表达式。yinteger_expression：正

34、整数，指定 character_expression 将返回的字符数。z返回值类型：当 character_expression 为非 Unicode 字符数据类型时，返回 varchar；当 character_expression 为 Unicode 字符数据类型时，返回 nvarchar。例：使用例：使用LEFT函数返回姓名为函数返回姓名为“张海洋张海洋”的人的姓的人的姓 SELECT LEFT(张海洋,1) 70RIGHTRIGHTz功能：返回字符串中从右边开始指定个数的字符，字符串右边的空格也属于有效字符z语法：RIGHT ( character_expression , integ

35、er_expression )z参数说明：ycharacter_expression：字符或二进制数据表达式yinteger_expression：正整数，指定 character_expression 将返回的字符数z返回值类型：当 character_expression 为非 Unicode 字符数据类型时，返回 varchar；当 character_expression 为 Unicode 字符数据类型时，返回 nvarchar 例：返回字符串例：返回字符串“中华人民共和国中华人民共和国”的最后的最后3个字符个字符 SELECT RIGHT(中华人民共和国,3)71LENLENz功能

36、：返回指定字符串表达式的字符数，其中不包含尾随空格z语法：LEN ( string_expression )z参数说明：y string_expression：要计算其字符数的字符串z返回值类型：如果 expression 的数据类型为 varchar(max)、nvarchar(max) 或 varbinary(max)，则为 bigint；否则为 int例：返回字符串例：返回字符串“中华人民共和国中华人民共和国”的字符数。的字符数。SELECT LEN(中华人民共和国)72SUBSTRINGSUBSTRINGz功能：返回字符、二进制字符串或文本字符串的一部分。z语法：SUBSTRING (

37、 value_expression ,start_expression , length_expression )z参数说明：yvalue_expression：字符或二进制字符串、列名称或包含列名称的字符串值表达式，不可使用包含聚合函数的表达式。ystart_expression：字符串的开始位置。ylength_expression：返回字符串的长度。z返回值类型：如果 expression 是受支持的字符数据类型，则返回字符数据。如果 expression 是支持的 binary 数据类型中的一种数据类型，则返回二进制数据。例：返回字符串例：返回字符串“北京信息科技大学北京信息科技大学”

38、的从第的从第5个字符开始长度个字符开始长度为为2的字符子串。的字符子串。SELECT SUBSTRING(北京信息科技大学,5,2)73例：假设银行代码的第2位代表银行名称（其中，1表示工商银行，2表示交通银行，3表示建设银行），请使用函数SUBSTRING确定银行代码为“B1210”的银行名称。 SELECT CASE SUBSTRING(B1210,2,1) WHEN 1 THEN 工商银行 WHEN 2 THEN 交通银行 WHEN 3 THEN 建设银行 END74LTRIMLTRIMz功能：返回删除了前导空格之后的字符表达式。z语法：LTRIM ( character_expression )z参数说明：y character_expression：字符数据或二进制数据的表达式。z返回值类型：varchar 或nvarch