第04章T-SQL程序设计基础.ppt

数据共享数据独立最小冗余数据安全数据完整灵活性和可恢复性,数据库技术及网络应用,章节目录之上,4.1SQL语言和T-SQL语言,4.2T-SQL的标识符,4.3T-SQL的数据类型,4.4T-SQL的运算符及表达式,4.5T-SQL中的变量,第4章T-SQL程序设计基础,4.6T-SQL中的语句,4.7T-SQL函数,小结,习题4,4.1.1SQL语言简介,4.1.2T-SQL语言简介,4.1.3T-SQL语句的功能及语法规定,4.2.1标识符类型,4.2.2标识符命名规则,4.2.3特殊意义的标识符,4.3.1整型数据类型,4.3.2货币数据类型,4.3.3精确小数数据类型,4.3.4近似小数数据类型,4.3.5日期和时间数据类型,4.3.6字符串数据类型,4.3.7Unicode字符串数据类型,4.3.8二进制字符串数据类型,章节目录之中,4.1SQL语言和T-SQL语言,4.2T-SQL的标识符,4.3T-SQL的数据类型,4.4T-SQL的运算符及表达式,4.5T-SQL中的变量,第4章T-SQL程序设计基础,4.6T-SQL中的语句,4.7T-SQL函数,小结,习题4,4.4.1算术运算符及表达式,4.4.2赋值运算符及表达式,4.4.3字符串运算符及表达式,4.4.4关系运算符及表达式,4.4.5逻辑运算符及表达式,4.4.6位运算符及表达式,4.5.1局部变量,4.5.2实例：局部变量的应用,4.5.3全局变量,4.5.4实例：全局变量的应用,4.6.1注释语句,4.6.2批处理及结束标志go,4.6.3BeginEnd语句块,4.6.4条件语句,4.4.7各种运算符的优先级别,4.6.5循环语句,4.6.6reture语句,章节目录之下,4.1SQL语言和T-SQL语言,4.2T-SQL的标识符,4.3T-SQL的数据类型,4.4T-SQL的运算符及表达式,4.5T-SQL中的变量,第4章T-SQL程序设计基础,4.6T-SQL中的语句,4.7T-SQL函数,小结,习题4,4.7.1数学函数,4.7.2字符串函数,4.7.3日期和时间函数,4.7.4系统函数,4.7.5类型转换函数,4.7.6用户自定义函数,本章提要,第4章T-SQL程序设计基础,从本章开始，介绍T-SQL程序设计基础.包括T-SQL语言的基本概念、T-SQL的标识符、数据类型、运算符、变量、表达式、语句和函数。重点讲解T-SQL语句、变量、函数的使用，为使用T-SQL代码操纵数据库和表以及进行程序设计打下坚实的基础。,4.1SQL语言和T-SQL语言,4.3T-SQL的数据类型,4.4T-SQL的运算符及表达式,4.7T-SQL函数,小结,习题4,4.2T-SQL的标识符,4.5T-SQL中的变量,4.6T-SQL中的语句,4.1SQL语言和T-SQL语言,4.1.1SQL语言简介,第四章T-SQL程序设计基础,数据库需要一套指令集，能够识别指令、执行对应的操作并为程序提供数据。目前，标准的指令集就是SQL。,SQL语言不同于C、C#这样的程序设计语言，它只是数据库能识别的指令。但是在程序中，可以组织SQL语句发送给数据库，数据库再执行相应的操作。例如，在VB.net程序中要得到SQLServer数据库表中的记录，可以在VB.net程序中编写SQL查询语句，然后发送到数据库中，数据库根据查询的SQL语句进行查询，再把查询的结果返回给VB.net程序。,4.1.2T-SQL语言简介,T-SQL语言主要由以下几部分组成：,第四章T-SQL程序设计基础,（1）数据定义语言（DDL）用来建立数据库和数据库对象，命令常以CREATE开头；（2）数据操纵语言（DML）用来对数据库中的数据进行增、删、改、查询等作；（3）数据控制语言（DCL）用来进行数据库的访问权限控制。除此之外，T-SQL还包括变量说明、内嵌函数及其他命令。,第四章T-SQL程序设计基础,4.1.3T-SQL语句的功能及语法规定,T-SQL语句主要用于处理数据库中的数据，如声明变量、控制程序流程、定义或调用自定义函数或存储过程等。,1T-SQL语句的功能T-SQL语句主要完成以下功能：（1）创建数据库及数据库对象（2）对数据的操纵（3）控制程序流程（4）定义变量（5）控制事务处理,第四章T-SQL程序设计基础,2T-SQL语句的语法对T-SQL语句的语法格式主要有以下规定：,（1）语句中的字母大小写均可；（2）关键字有特殊用途，定义变量名时不得使用关键字；（3）语句中的日期型常量和字符型常量必须用单引号括起来；（4）语句中的标点符号必须用英文标点，即半角符号；（5）一条语句可以分行写，一行也可以写多条语句，语句末尾不写任何标点。,第四章T-SQL程序设计基础,4.2T-SQL的标识符,为了提供完善的数据库管理机制，T-SQL对数据库对象制定了严格的命名规则。,4.2.1标识符类型,SQLServer中的所有对象包括数据库以及数据库对象，例如，表名、表中的列名、视图、索引、存储过程、函数、触发器等都应该有一个标识符。对于大多数对象来说，标识符是不可缺少的。标识符是在创建对象时定义的，为用户在引用对象时使用。T-SQL规定了以下两类标识符：,第四章T-SQL程序设计基础,1、规则标识符规则标识符是严格按标识符命名规则定义的标识符，不需要任何定界符号括起来。例如：在上述创建索引的T-SQL语句中，索引名“aaa”不需要添加任何定界符号，因为“aaa”是规则标识符。当然，若添加定界符也是可以的。,2、定界标识符定界标识符是使用定界符号或“”将标识符括起来的标识符。使用了定界标识符，既可以遵守标识符的命名规则，也可以不遵守标识符命名规则。例如：以下两条语句中的索引名必须加定界符或“”，因为符号“：”和“”都是规则标识符中不允许使用的符号。,4.2.2标识符命名规则,第四章T-SQL程序设计基础,1标识符必须以字母、_(下划线)、或#开头；2标识符可以是字母、数字、_、#或$的组合；3标识符不允许使用保留字；4标识符不允许使用除_、#或$之外的其他符号，例如：不能使用空格符号；5标识符大小写等价。,4.2.3特殊意义的标识符,某些以特殊符号打头的标识符在T-SQL中具有特殊意义。例如：以#开头的标识符表示临时表；以开头的标识符表示局部变量；以开头的标识符表示全局变量；,提示：全局变量由系统提供，用户不要定义以开头的标识符。,第四章T-SQL程序设计基础,4.3T-SQL的数据类型,使用SQLServer管理器或使用T-SQL语句创建数据库表时，为表中的列选择数据类型是至关重要的。另外，在设计存储过程时，需要建立参数和定义局部变量，也要求定义变量的数据类型。,在定义变量的数据类型时，有些数据类型可以选择宽度，有些数值型数据，可以指明数值精度和小数位数。SQLServer2005提供了7类共29种系统数据类型，同时还允许用户自定义新的数据类型。本节将介绍SQLServer2005使用的主要数据类型。,4.3.1整型数据类型,第四章T-SQL程序设计基础,1bit,2tinyint3smallint4int5bigint,第四章T-SQL程序设计基础,4.3.2货币数据类型,货币数据类型专门用于处理货币，由整数部分和小数部分组成。SQLServer提供了两种货币类型的数据。,1smallmoneysmallmoney型数据最多可以包含4位小数，也就是能精确到货币的万分之一。若数据的小数位超过4位，会自动进行四舍五入。smallmoney型数据在内存中占4个字节，范围是-214748.3648到+214748.3647。2moneymoney型数据比smallmoney型数据表示的范围大，小数点后面的位数也是4位，即能精确到货币的万分之一。若数据的小数位超过4位，会自动进行四舍五入。money型数据在内存中占8个字节，范围是-922337203685477.5808到+922337203685477.5807。,4.3.3精确小数数据类型,第四章T-SQL程序设计基础,精确十进制小数数据类型包含numeric和decimal两种类型，它们在使用方法上基本相同。使用numeric和numeric数据类型能进行数据的精度设置，因此数据能精确存储。,1numericnumeric是可以存储带小数的数值数据类型，精度和小数位可以在所允许范围内自行设置。一旦设定精度和小数位，将对符合所设定范围的数据能精确存储。Numeric型数据最大精度可设置到38位，取值范围是-1038+1038-1。默认精度18位，小数位是0位。,2decimaldecimal数据类型和numeric数据类型的用法相同。只是decimal作为保留字可以缩写为dec。,第四章T-SQL程序设计基础,numeric和decimal型数据在内存中占用的字节数是随着所设定的精度不同而不同。,精度是指整数和小数部分的总位数，不包括小数点本身。在输入的数据中，若小数部分超过规定的精度可以四舍五入，但前提条件是四舍五入之后的数据不能越界，即不能超出所设计的数据范围，超出范围的数据不能输入到计算机里。,第四章T-SQL程序设计基础,图4-1执行代码和提示信息,第四章T-SQL程序设计基础,图4-2插入行的结果,提示：decimal可简写为dec,4.3.4近似小数数据类型,近似十进制小数数据类型是指浮点数据类型，包含real和float两种类型。浮点数据类型能够存储范围更大的数字，但有时不能精确存储。这是因为浮点型数据的精度不能自行设置，未超出数据范围，但超出了精度范围，数据能够存储，但有误差。,第四章T-SQL程序设计基础,1realreal型数据在内存中占用个字节，精度达到位。可表示的数据范围是1.1810-383.401038。如果某个数据的有效数字超过7位，超出部分会自动进行四舍五入。2floatfloat型数据在内存中占用8个字节，精度达到15位。可表示的数据范围是2.2310-3081.7910308。如果某个数据的有效数字超过15位，超出部分会自动进行四舍五入。,4.3.5日期和时间数据类型,第四章T-SQL程序设计基础,日期和时间数据类型可存储日期和时间的组合数据。SQLServer提供了专门处理日期和时间类型数据的函数。日期和时间数据类型分为datetime和smalldateti-me两类。,1datetimedatetime型数据在内存中占8个字节，其中前4个字节存储日期，后4个字节存储时间。默认显示格式“年-月-日小时:分种:秒”，存储范围是1753-1-10:00:00到9999-12-3123:59:59。输入时省略日期，则默认为当前日期；若省略时间，默认为0:00:00。2Smalldatetime用smalldatetime数据类型存储日期和时间要比datetime数据类型范围小。Small-datetime型数据在内存中占4个字节，其中前2个字节存储日期，后2个字节存储时间。默认显示格式“年-月-日小时:分种:秒”，存储范围是1900-1-10:00:00到2079-6-623:59:59。输入时省略日期，则默认为当前日期；若省略时间，默认为0:00:00。输入日期时，年、月、日之间除了使用“-”作为分隔符，还可用“/”或“.”分开。,第四章T-SQL程序设计基础,4.3.6字符串数据类型,T-SQL提供了3种字符串数据类型，包含：char、varchar、text。最常用的是char和varchar类型。,1charchar类型数据使用固定长度存储字符串，可精确定义数据占有的内存空间。字符串中的每个字符在内存中占用一个字节，存放字符的ASCII码值的二进制数。字符串长度可设置的范围是18000字节，每个西文字符占用1个字节，每个汉字占用2个字节。在T-SQL命令中，字符串必须用单引号括起来。利用char数据类型定义表列或变量时，其长度范围应按列值中字符串的最大长度。若实际字符串的长度不足于所设定的字符串长度，多出的部分以空格填充；若实际字符串的长度多于所设定的字符串长度，多出的部分被截断。,第四章T-SQL程序设计基础,2varcharvarchar类型数据在使用方法上和char类似。只不过varchar类型数据使用可变长度存储字符串，最大长度可设置到8000个字符。varchar数据类型的存储空间随着字符串的实际长度而变化。,利用varchar数据类型定义表列或变量时，其长度范围应按列值中字符串的最大长度。若实际字符串的长度不足于所设定的字符串长度，多出的部分不会以空格填充；若实际字符串的长度多于所设定的字符串长度，多出的部分被截断。varchar数据类型适合字符串长度经常变化的列名或变量。例如，设计学生表时，可将学生的联系电话设置为varchar数据类型。,3texttext数据类型是专门用于存放大量字符的可变长度字符数据。当需要存储大于8000字节的字符串时，使用text数据类型。text数据类型最多可存放的字符长度达到231-1，大约为2GB。,第四章T-SQL程序设计基础,4.3.7Unicode字符串数据类型,Unicode字符串数据类型也称为双字节字符串数据类型，使用两个字节存储一个字符。Unicode数据类型解决了字符转换问题和针对不同国家安装多国代码问题，从而使得多种语言的数据存储变得更加容易。SQLSer-ver提供了3种双字节数据类型，包含：nchar、nvarch-ar、ntext。,1ncharnchar数据类型最多可存4000个字符，占用空间8000个字节。nchar数据类型在使用方法上和char对应，属于固定长度的双字节数据类型。nchar类型和char在使用方法上相似，若实际字符串的长度不足于所设定的字符串长度，多出的部分以空格填充；若实际字符串的长度多于所设定的字符串长度，多出的部分被截断。,第四章T-SQL程序设计基础,2nvarcharnvarchar数据类型最多可存4000个字符，占用空间8000个字节。nvarchar数据类型在使用方法上和varch-ar对应，属于可变长度的双字节数据类型。nvarchar类型和varchar在使用方法上相似，若实际字符串的长度不足于所设定的字符串长度，多出的部分不会以空格填充；若实际字符串的长度多于所设定的字符串长度，多出的部分被截断。,nvarchar数据类型适合字符串长度经常变化的列名或变量。例如，设计学生表时，学生的家庭地址可设置为nvarchar数据类型。,3ntextntext数据类型最多可存储大约230-1个双字节字符，存储空间大约2GB，是可变长度双字节数据类型。ntext数据类型在使用方法上和text对应。ntext数据类型适合存储大量汉字。,第四章T-SQL程序设计基础,4.3.8二进制字符串数据类型,二进制数据是用十六进制表示的数据，T-SQL提供了3种数据类型用来存储二进制数据，包含：binary、var-binary、image,1binarybinary是固定长度的二进制数据类型，最大长度为8000字节。进行插入行的操作时，数据本身不加引号，必须以0 x开头。2varbinaryvarbinary是可变长度的二进制数据类型，存放二进制数据最大可达8000字节。插入数据时，也必须以0 x开头。3imageImage是可变长度的二进制数据类型，用于存储大量二进制数据，最大空间可达2GB。例如，可存储word文档、Excel表格以及图像数据等的OLE对象。,第四章T-SQL程序设计基础,4.4T-SQL的运算符及表达式,T-SQL提供的运算符可分为算术运算符、赋值运算符、字符串运算符、关系运算符、逻辑运算符和位运算符。T-SQL中的表达式是指用运算符连接起来的常量、变量和函数的式子，其中最简单的表达式是单独的常量、变量或函数。,4.4.1算术运算符及表达式,1基本的算术运算符算术运算符是T-SQL中常用的运算符。包括：+加法运算符，或取正值运算符。例如：3+2、+5-减法运算符，或取负值运算符。例如：6-4、-8*乘法运算符。例如：7*9/除法运算符。例如：8/58.0/5%取余运算。例如：8%58.0%5算术运算符的操作对象是数值型数据，如整型、货币型、十进制小数类型等。,如果参加+、-、*、/、%运算的两个操作数有一个为实数，则结果就为实数；若两个操作数都为整数，其结果就为整数。,第四章T-SQL程序设计基础,2算术表达式用算术运算符和括号将常量、变量、函数等运算对象连接起来且符合语法规则的式子称为算术表达式。可以在数据库引擎查询文档中利用selectct表达式1,表达式2,语句显示若干算术表达式的值。例如，显示用逗号分开的两个算术表达式的值，其结果如图4-3所示。,图4-3算术运算表达式及运算结果,第四章T-SQL程序设计基础,4.4.2赋值运算符及表达式,1赋值运算符赋值运算符主要是给变量赋值或在更新表中的数据时使用。赋值运算符用“=”表示。赋值运算符的操作对象是局部变量、表中的列名等数据对象。,2赋值表达式由赋值运算符将一个变量和一个表达式连接起来的式子称为赋值表达式。在更新表中的数据时，将表达式的值赋给列名，列名看作是表中的变量；在给局部变量赋值时，先计算表达式的值，再将表达式的值赋给变量；其基本格式为：,例如：给成绩表中学号为“06503077”的记录成绩加分，代码如下：update成绩表set成绩=成绩+5where学号=06503077其中“成绩=成绩+5”为赋值表达式。T-SQL中的局部变量要先定义后使用。例如：一个对变量进行定义、赋值、显示的实例，如图4-4所示。,第四章T-SQL程序设计基础,图4-4变量的定义、赋值和显示,其中“x=9000-8000”为赋值表达式。,第四章T-SQL程序设计基础,4.4.3字符串运算符及表达式,字符串运算符只有一个，运算符号为“+”，其作用是将两个字符串首尾相接，连接后的结果仍为字符串。用“+”号将若干个字符串连接起来组成字符串表达式。用se-lect语句显示两个字符串表达式值的实例，如图4-5所示。,图4-5字符串连接,第四章T-SQL程序设计基础,4.4.4关系运算符及表达式,1关系运算符是对两个同类型数据进行比较，包含：（1）大于（4）=大于或等于,（5）=等于（6）、！=不等于（7）!不大于，相当于小于或等于（8）!=70and成绩90seta=rowcountselect*from成绩表where成绩90goseta=rowcount-此处有一个语法错误，a误写为aselecta,(a)图,(b)图,第四章T-SQL程序设计,但是在以下代码中，错误语句“seta=rowcount”包含在批处理中，影响批处理中的所有语句的执行，结果如图4-14(b)图所示。gouse学生成绩管理declareaintupdate成绩表set成绩=成绩+5select*from成绩表where成绩90seta=rowcount-此处有一个语法错误，a误写为aselectago,第四章T-SQL程序设计,4.6.3BeginEnd语句块,使用BeginEnd语句将多条T-SQL语句括起来，作为一个整体执行，称为BeginEnd语句块。用BeginEnd括起来的若干条T-SQL语句可以看成是一句T-SQL语句。BeginEnd语句块的语法格式如下：,BeginEnd说明：（1）关键字BeginEnd是成对出现的，缺一不可；（2）BeginEnd语句常作为ifelse的子句或while的循环体使用；（3）BeginEnd语句可嵌套使用。,BeginEnd语句不同于批处理，GO不是T-SQL的关键字，它只是一个成批发送命令的标志。在选中的要执行的若干条语句中，如果包含Begin就必须要包含End，否则BeginEnd之间的任何一条语句都不会被执行。,第四章T-SQL程序设计,下面是一个使用比处理和使用BeginEnd语句块对照的例子。如图4-15所示。,图4-15批处理和BeginEnd语句,第四章T-SQL程序设计,4.6