矿大数据库应用第2章课件

1、第2章 Transact-SQL语言基础李政伟 T-SQL语言简介 数据类型 系统数据类型和用户定义数据类型 变量、运算符、函数 局部变量和全局变量 运算符 函数 批处理和流程控制 批和脚本 流程控制第2章 Transact-SQL语言基础21970年6月，埃德加考特(Edgar Frank Codd)在Communications of ACM 上发表了大型共享数据库数据的关系模型一文。首次明确而清晰地为数据库系统提出了一种崭新的模型， 即关系模型。1970 年以后，考特继续致力于完善与发展关系理论。1972 年，他提出了关系代数和关系演算的概念， 定义了关系的并、交、投影、选择、连接等各种

2、基本运算，为SQL语言的形成和发展奠定了理论基础。1979年，SQL(Structure Query Language)在商业数据库中成功得到了应用。2.1 T-SQL语言简介31986年，ANSI正式发表了编号为X3.135-1986的SQL标准，并且在1987年获得了ISO组织的认可，被命名为ISO9075-1987。后来这个标准在1992、1999、2001、2003年等不断地得到了扩充和完善。从SQL语言的历史来看，T-SQL语言与SQL语言并不完全等同。不同的数据库供应商一方面采纳了SQL语言作为自己数据库的操作语言，另一方面又对SQL语言进行了不同程度的扩展。这种扩展的主要原因是不

3、同的数据库供应商为了达到特殊目的和实现新的功能，不得不对标准的SQL语言进行扩展，而这些扩展往往又是SQL标准的下一个版本的主要实践来源。2.1 T-SQL语言简介4T-SQL语句分类T-SQL语句相关例子数据操作语句SELECT、INSERT、DELETE、UPDATE数据定义语句CREATE TABLE、DROP TABLE、ALTER TABLECREATE VIEW、DROP VIEWCREATE INDEX、DROP INDEXCREATE PROCEDURE、ALTER PROCEDURE、DROP PROCEDURECREATE TRIGGER、ALTER TRIGGER、DRO

4、P TRIGGER数据控制语句GRANT、DENY、REVOKE附加的语言元素BEGIN TRANSACTION/COMMIT、ROLLBACK、SET TRANSACTIONDECLARE、OPEN、FETCH、CLOSE、EXECUTE5SELECT语句是SQL Server中最基本和最重要的语句之一。 SELECT语句是执行全部命令的基础。所谓查询就是对SQL Server发出一个数据请求，数据查询语句只是提出要“查询什么”和“从什么地方查询”，那“怎么查询”则由数据库管理系统DBMS来完成。SELECT语句的简单用法： SELECT FROM WHERE T-SQL语句数据查询语句78

5、注释程序代码中不执行的文本字符串，用于对代码进行说明或暂时禁用正在进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期、算法描述等。两种注释方式双连字符(-)正斜杠星号字符对(/*/)10【例2-2】演示使用注释 11 数据类型：数据所代表信息的类型，它关系到系统在内存或磁盘上开辟相应的存储空间。SQL Server 2008提供了33种数据类型，分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。需要使用数据类型的对象包括表中的列、视图中的列、定义的局部变量、存储过程中的参数、函数及存储过程的返回值等。2.2 数据类型12用于存储

6、十进制小数。 Real：取值范围为-3.40E+383.40E+38（4字节） 。 Float ( n ) ： n 为用于存储 尾数的位数， n1,53 ，默认值为 53。范围从-1.79E-308到1.79E+308，（4或8字节）。 如不指定float的长度，会被存储在8个字节中。当指定长度为124间数值时，则实际上定义了一个real数据类型。如果 1=n=24，则将 n 视为 24。如果 25=n=53，则将 n 视为 53。浮点数据类型(Real,float)14Decimal(p,s)和Numeric(p,s)：两者完全相同，可提供小数所需要的实际存储空间，但也有限制，可用2到17个

7、字节来存储从- 1038 +1 到 1038 - 1之间的数值。精度存储字节数1 - 9 510-19920-281329-38 17浮点数据类型(Decimal, numeric)注意：SQL SEVER没有无符号类型。15日期时间型 Date：用于存储日期，范围从0001年1月1日至9999年12月31日，占3字节。不含具体的时间。 Datetime：用于存储日期和时间。范围从1753年1月1日零时起到9999年12月31日23时59分59秒。 Smalldatetime：与datetime类似，但其日期时间范围较小，范围从1900年1月1日到2079年6月6日。 datetime2 (f

8、ractional seconds precision) ：与datetime类似，不同之处是datetime2秒的小数部分精度更高，存储范围更大：0001年1月1日至9999年12月31日，秒数可以精确到小数点后7位。17 datetimeoffset (fractional seconds precision) ：用于存储与日期和时区相关的日期时间数据。存储的日期时间数据，需要转化成为UTC（Coordinated Universal Time）值的时间，即需要根据时区关系进行换算。格式为YYYY-MM-DD hh:mm:ss.nnnnnnn +|_hh:mm。占用的存储空间因n的取值不同

9、而不同，在8至10个字节之间。 time (fractional second precision) ：专用于存储时间的数据类型。格式为hh:mm:ss.nnnnnnn，长度为35字节。 日期时间型18例2.1将字符串转换为各种 date 和 time 数据类型。SELECT CAST(2011-11-09 08:35:29. 1234567 +12:15 AS time(7) AS time ,CAST(2011-11-09 08:35:29. 1234567 +12:15 AS date) AS date ,CAST(2011-11-09 08:35:29.123 AS smalldate

10、time) AS smalldatetime ,CAST(2011-11-09 08:35:29.123 AS datetime) AS datetime ,CAST(2011-11-09 08:35:29. 1234567 +12:15 AS datetime2(7) AS datetime2 ,CAST(2011-11-09 08:35:29.1234567 +12:15 AS datetimeoffset(7) AS datetimeoffset;19包括money和smallmoney两种。money用于存储货币值，范围为 -263263，（8 字节）。smallmoney范围比mon

11、ey数据类型小，其存储范围为-2147483468到2147483467之间（4字节）。注意输入money或smallmoney类型数据时，(不是)必须在位置前加货币单位符号。money 和 smallmoney 类型精确到它们所代表的货币单位的万分之一。货币型20【例2-2】演示MONEY数据类型 21文本和图形数据类型 Text：长度可变的非 Unicode 数据，最大长度为 231-1。 Ntext：长度可变的 Unicode 数据 ，最大长度为 230 1。 Image：长度可变的二进制数据，从 0 到 231-1 (2,147,483,647) 个字节 。 在 Microsoft S

12、QL Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。 22程序用数据类型hierarchyid：新增的一种用于存储层次化结构型数据的数据类型。采用hierarchyid来存储，可以利用hierarchyid提供的函数，非常方便地实现数据的存储和节点搜索。geometry：用于存储平面几何对象（平面球）的数据类型，如点、多边形、曲线等11种几何度量中的一种。Geography：用于存储G

13、PS等全球定位类型的地理数据（椭圆球），以纬度和经度为度量来存储。XML：用于存放整个XML文档或者部分片段。Table：用于存储对表或者视图处理后的结果集的数据类型。这种数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。24程序用数据类型Cursor：一种变量或存储过程的输出参数使用的数据类型，也称游标。提供了一种逐行处理查询数据的功能。用cursor定义的变量只能用于定义游标和与游标有关的语句，不能在表设计时使用。sql_variant：允许存储多个不同类型数据值的数据类型，除了varchar(max)、nvarchar(max)、text、image、sql_v

14、ariant、sql_variant(max)、xml、ntext、rowversion等之外的数据类型都可以存储。 Uniqueidentifier：用于存储一个16字节长的二进制数据类型，它是SQL Server根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码（Globally Unique Identifier，简写为GUID）。具有更新订阅的合并复制和事务复制使用 uniqueidentifier 列来确保在表的多个副本中唯一地标识行。25利用系统存储过程创建语法：sp_addtype type_name , phystype(length)|(precis

15、ion,scale),null | not null | identitytype_name，用户定义的数据类型的名字；Phystype，基于的系统数据类型，可包括长度、精度、标度；null | not null | identity，用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。提醒：后续版本的 Microsoft SQL Server 将删除该功能。 用户定义数据类型27用户定义数据类型28右击“用户定义数据类型”，单击“删除”，再单击“确定” 。也可用系统存储过程来查看、重命名、删除用户定义的数据类型。 sp_help type_name查看用户自定义数据类型的

16、特征；sp_rename type_name给用户自定义数据类型重新命名；sp_droptype type_name删除一个已经定义且未被使用的用户自定义数据类型。删除用户定义数据类型2930 变量 局部变量和全局变量 常量 运算符 运算符的类别 运算符的优先级别 系统函数 数学函数、字符串函数 日期和时间函数 转换函数 系统函数 聚合函数 2.3 变量、运算符、函数31局部变量：由用户根据需要定义的变量，使用范围只局限于某一个语句或过程体内。主要用于保存临时数据或由存储过程返回的结果。作用域是其被声明时所在批处理。必须先用DECLARE语句定义后才可以使用。局部变量32定义语法形式： DEC

17、LARE local_variable AS data_type | = value | cursor_variable_name CURSOR ,.n 局部变量不能是 text，ntext 或 image 数据类型。声明局部变量之后，会将其设为NULL，如设定新值，须用SELECT或者SET命令。其语法形式为： SET local_variable = expression 或 SELECT local_variable = expression ,.n 其中，参数expression是任何有效的SQL Server表达式。局部变量33例2.4，声明一个长度为9个字符的变量city，并赋值“

18、Welcome to changsha”。DECLARE city CHAR(9)SELECT city = Welcome to changshaSELECT city建议：当执行简单变量赋值时，使用 SET语句；当基于查询进行变量赋值时，使用SELECT语句。局部变量定义与赋值34由SQL SERVER预先定义并负责维护，主要用于保存系统的某些参数值和性能统计数据，作用域覆盖所有程序。全局变量都以标记符“”开头。分类，一是与SQL Server连接有关的全局变量，如rowcount表示受最近一个语句影响的行数；二是关于系统内部信息有关的全局变量，如version表示SQL Server的版

19、本号。IDENTITY：返回最后一条Insert语句执行后的标识值。注意：局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。全局变量（系统函数）35例2.7返回当前用户进程的会话 ID、登录名和用户名。 SELECT SPID AS ID, SYSTEM_USER AS Login Name, USER AS User Name全局变量示例36常量常量：程序运行中值不变的量，也称为文字值或标量值 。常量格式取决于其所表示的值的数据类型。例，This is a book. 、May 1, 2006、98321等。对于字符常量或时间日期型常量，需用单引号引起来。分类：字

20、符串常量、二进制常量 、bit常量 、datetime常量 、integer常量、 decimal常量、 float 和 real 常量、 money常量、uniqueidentifier常量等。37主要有六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串连接运算符(参见P29)。运算符的优先级别 运算符38内置函数Microsoft SQL Server 2008系统提供了许多内置函数，这些函数可以完成许多特殊的操作，增强了系统的功能，提高了系统的易用性。 39数学函数40字符串函数41例2.8 先使用SUBSTRING函数从字符串中获取指定的子字符串，然后使用LOW

21、ER函数、UPPER函数对子字符串进行转换，且把UPPER函数嵌套在LOWER函数内或把LOWER函数嵌套在UPPER内。DECLARE string_lower_upper varchar(40)SET string_lower_upper = Information Model Creation Issues.SELECT LOWER(SUBSTRING(string_lower_upper, 1, 11) AS Lower, UPPER(SUBSTRING(string_lower_upper, 13, 5) AS Upper, LOWER(UPPER(SUBSTRING(string_

22、lower_upper, 19, 8) As LowerUpper, UPPER(LOWER(SUBSTRING(string_lower_upper, 28, 7) As UpperLower字符串函数42日期和时间函数 用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。 43转换函数 SQL Server能够自动处理某些数据类型的转换。例如，char和varchar、smallint和int，可以将自动转换（隐式转换）。但无法自动转换或者自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。 44data_type 作为目标的系统提供数据类型。l

23、ength nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。对于 CONVERT，如果未指定 length，则默认为 30 个字符。style 用于将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）的日期格式的样式；或用于将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）的字

24、符串格式的样式。如果 style 为 NULL，则返回的结果也为 NULL。转换函数45转换函数46转换函数47USE AdventureWorks;GOSELECT The list price is + CAST(ListPrice AS varchar(12) AS ListPriceFROM Production.ProductWHERE ListPrice BETWEEN 350.00 AND 400.00;GO例2.9，使用CAST连接非字符型非二进制表达式 48系统函数 用于返回系统、用户、数据库的信息。用户得到信息后，可以使用语句进行相关的操作。 49系统函数50聚合函数 针对

25、整个或者一个表列或几个表列进行数据汇总，常用来计算SELECT语句查询结果集的统计值。如求一个结果集的最大值、最小值、平均值和所有元素和等。 聚合函数AVG、COUNT、MIN、MAX和SUM是针对表列进行运算，并回单一的值，其中COUNT、MIN和MAX可用于各种数据类型，但AVG及SUM则仅适用于数值类型。 51程序是由批、注释、变量、改变执行顺序的控制语句、错误和消息的处理等成分组成的。 批（处理）批是作为一个逻辑单元的一组T-SQL语句。一个批中的所有语句被组合成一个执行计划。因此对所有语句一起进行语法分析且通过语法验证，否则将不执行任何一条语句。但如果运行时发生错误，那么任何发生在运

26、行时错误之前的语句仍然有效。除非是未提交事务的一部分。批处理和流程控制52GO：向 SQL Server 实用工具发出一批 Transact-SQL 语句结束的信号。 两个GO之间的T-SQL语句作为一个批。在一个批中可包含一条或多条T-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。使用批的限制（P35）脚本：一系列顺序提交的批。 批53/*例2.10 由三个批处理组成的脚本。*/USE studentGOCREATE VIEW teacher_info_viewASSELECT teacher_id, name, tech_title,

27、salary FROM teacher_infoGOSELECT * FROM teacher_info_viewGO脚本5455BEGINEND用来设定一程序块 IFELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。 CASE语句为多分支语句 WHILECONTINUEBREAK循环语句流程控制561. BEGINEND语句语法格式：BEGIN END用来设定一个程序块，将在BEGIN END 内的所有程序视为一个单元执行。经常在条件语句如IF ELSE 中使用，在BEGIN END 中可嵌套另外的BEGIN END 来定义另一程序块。57流程控制(示例2.11 )5

28、82. IFELSE语句语法形式： IF Boolean_expression sql_statement | statement_block ELSEsql_statement | statement_block ELSE子句是可选的。允许嵌套使用IFELSE语句，而且嵌套层数没有限制。59流程控制(示例2.12 )603. CASE语句作用：计算多个条件式，并将其中一个符合条件的结果表达式返回。两种使用形式简单式 CASE结构：将某个表达式与一组简单表达式进行比较以确定结果。搜索式CASE结构： 计算一组布尔表达式以确定结果。共同点：两种格式都支持可选的 ELSE 参数。 61流程控制(示

29、例2.13 )62流程控制(示例2.14 )例2.14 从stud_grade表中查询所有同学考试成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。SELECT stud_id,name,score=CASE WHEN grade IS NULL THEN 未考 WHEN grade=60 AND grade=70 AND grade=90 THEN 优秀 ENDFROM stud_grade634. WHILECONTINUEBREAK语句WHILE Boolean_expression sq

30、l_statement | statement_block BREAK sql_statement | statement_block CONTINUE sql_statement | statement_block 在设定的条件成立时会重复执行命令行或程序块。CONTINUE 可让程序跳过本次循环，其后的语句回到下次WHILE 循环的第一行命令。BREAK 命令则让程序完全跳出循环，结束执行WHILE 语句。64declare x int, y int, nums intselect x = 0, y=1set nums=0while y 100beginif(y%3=0)beginset x=x+y;set nums=nums+1endset y=y+1endprint str(x)+,+str(nums)例2.15 计算100内能被3整除的数的个数及总和65 WAITFOR语句用来暂时停止程序执行 GOTO语句用来改变程序执行的流程 RETURN语句用于结束当前程序(所在的批、存储过程和触发器)的执行，返回