工作任务2-系统TSQL语言编程课件

SQL Server 应用系统开发应用系统开发计算机与信息工程系计算机与信息工程系工作任务工作任务2 使用使用Transact-SQL语言语言13.1 SQL Server 编程工具书编程工具书 3.2 SQL SQL 语言简介语言简介 3.3 数据类型数据类型 3.4 局部变量与全局变量局部变量与全局变量 3.5 运算符运算符及优先级及优先级 3.6 函数函数 3.7 简单的简单的SELECT 语句语句 3.8 批处理和批处理和流程控制语句流程控制语句 返回目录返回目录23.1 SQL Server 编程工具SQL中有许多编程工具,最常用的编程工具是查询分析器和osql实用程序。查询分析器是图形化的数据库编程接口，用户可以在其中以自由的文本格式编辑、调试和执行SQL脚本，操作方便、灵活、快捷。Osql实用程序是一个以命令方式执行的实用程序。相对而言，用户更喜欢用查询分析器作为SQL语言的工作环境。3.1.1 启动查询分析器启动查询分析器用户必须先登录到服务器。SQL所有的语句都由服务器提供的“服务管理器”来解释执行，因此用户必须先实现同服务器的连接。实现与数据库服务器连接有两种方法：一种是使用集成的Windows NT的安全验证方式；另一种是使用SQL Server 自身的安全验证方式；3进入查询分析器的两种方法（P38）：从“开始”菜单一路找到SQL中的“查询分析器”启动；先进入“企业管理器”“工具”“查询分析器”启动；453.1.2 查询分析器的特点查询分析器的特点使用查询分析器可以同时查看查询语句和结果，可以使用它来编写、修改和保存SQL脚本。它有如下特点：自动针对不同的语法元素着色。具有多重查询窗口，每个窗口显示自己的执行结果和信息提示；执行结果的多样性。执行结果可以以表格、文本显示，还可以作为文本文件将结果保存起来；提供图形化方式的执行计划。可以查看执行的优化计划并验证语法。具有执行选定的部分脚本的能力。63.1.3 配置和使用查询分析器配置和使用查询分析器1.查询分析器的配置 点击“工具”“选项”（见P39 图3-3）73.查询分析器的菜单使用（利用操作界面对各菜单进行简单讲解。）83.使用查询分析器的一般步骤从工具栏的数据库上选择要执行查询的数据库“PUBS”；在编辑框中输入一些语句；点击“查询”“分析”或工具栏上的“分析查询”按钮，进行语法检查；点击“查询”“执行”或工具栏上的“执行”按钮；查看服务器返回的结果；如发现错误可继续调试、分析、执行；如想保存文件，则可以点击“文件”“保存”命令，可以将SQL命令语句以文本文件的形式保存起来，后缀名为sql。本章首页本章首页本章首页本章首页93.2 SQL 语言简介SQL 语言是一种在关系型数据库中定义、查询、操纵和控制数据的标准语言。SQL-92 标准包括以下内容:模块化语言直接请求静态SQL语言动态SQL语言与数据库的连接与外部的连接流动游标大多数据库厂商都在SQL-92标准的基础上对SQL又进行了一定的扩充，产生了事务化的SQL语言，即Transact SQL语言，简称T-SQL语言。10SQL 语言是一种面向集合的数据库语言，其主要特点是：类似于英语，直观、简单易学；SQL语言只是提出要“干什么”，“怎么办”则由DBMS来完成。SQL语句分为四类：数据查询语句、数据操纵语句、数据定义语句和数据控制语句；SQL语句既可以独立，也可以嵌入到另外一种语言中使用，即具有自含型和缩主型两种特征。自含型特征可以用于所有用户，缩主型适合于应用程序员开发数据库应用程序。数据库管理员和应用系统开发人员用SQL语言可以做以下事情：创建主键、外键、约束、规则、触发器、事务，用以实现数据完整性；查询、更新、删除数据库中的信息；对各种数据库对象设置不同的权限、实现数据库的安全性；进行分布式数据处理，实现数据库间的数据复制、传递和执行分布式查询；可以创建批处理、存储过程、视图，方便应用程序访问数据库中的数据；实现数据仓库，从联机事务处理系统中提取数据，对数据汇总进行决策支持分析；其他功能。本章首页本章首页本章首页本章首页113.3 数据类型 数据类型是指数据所代表的信息的类型。每一种语言都定义了自己的数据类型。SQL Server 2000中定义了25种数据类型，同时允许用户自己定义数据类型。系统定义的数据类型有：binary(n)、varbinary(n)、char(n)、varchar(n)、nchar(n)、nvarchar(n)、datetime、smalldatetime、decimalp(,s)、numericp(,s)、float(n)、real、int、smallint、tinyint、money、smallymoney、bit、cursor、sysname、timestamp、uniqueidentifier、text、image 和ntext;123.3.1 二进制数据类型二进制数据类型二进制数据类型包括三种：binary、varbinary和image。binary(n)：固定长度的n个字节二进制数据。n必须是1到8000。存储空间大小为n+4字节。varbinary(n)：n个字节可变长二进制数据。n必须从1到8000。存储空间大小为实际数据长度+4个字节，而不是n个字节。image：可变长度的二进制，其最大长度为231-1个字节。3.3.2 数字数据类型数字数据类型数字数据类型包括精确数字、近似数字两种。精确数字又分为精确整数和精确小数。1.精确整数精确整数数据类型有四种，它们是bigint、int、smallint、tinyint。bigint：从-263到263-1的整型数据。存储大小为8个字节。int：从-231到231-1的整型数据。存储大小为4个字节。13smallint：从-215到215-1的整数数据。存储大小为2个字节。bigint：从0到255的整数数据。存储大小为1个字节。bigint 的存储范围大大于int 的存储范围，int 的存储范围大于Smallint 的存储范围，Smallint的存储范围大于tinyint的存储范围。3.精确小数精确小数数据类型有两种：它们是decimal和numeric。decimal：带定点精度和小数位数的numeric数据类型，从-1038+1到1038-1。numeric：功能上等同decimal。3.近似数字近似小数数据类型有两种：float 和 real。float：从-1.79E+308到1.79E+308的浮点精度数字。real：从-3.04E+38到3.04E+38的浮点精度数字。143.3.3 字符数据类型字符数据类型字符数据是由字母、数字、和符号组合而成的数据。例如”Nanjing”、”3.23”、”&*op”都是合法的字符数据。字符数据类型有char、varchar和text三种。char：固定长度的非unicode字符数据，最大长度为8000个字符；varchar：可变长度的非unicode 字符数据，最大长度为8000个字符；text：可变长度的非unicode 字符数据，最大长度为231-1个字符；如果没有在数据定义或变量声明语句中指定n，则默认长度为1。153.3.4 unicode 数据类型数据类型对于用单个字节编码每个字符的数据类型，存在的问题就是此数据类型只能表示256个不同的字符。象汉字、日文或韩国文字这样具有数千个字符的字母表就不可能处理了。unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一的编码方案。unicode标准每个字符用两个字节来编码，采用位模式来表示所有的字符。所以在系统中使用unicode数据类型，就可以解决大字母表的字符问题了。unicode数据类型包括nchar、nvarchar和ntext。nchar：固定长度的unicode 数据，最大长度为4000个字符。nvarchar：可变长度的unicode数据，其最大长度为4000字符。ntext：可娈长度的unicoce数据，其最大长度为230-1个字符。当列的长度固定不变时，使用nchar数据类型；当列的长度变化时，使用nvarchar数据类型；如果需要存储的字符数多于4000个时，应使用ntext数据类型存储。163.3.5 日期和时间数据类型日期和时间数据类型日期和时间数据类型包括datetime 和smalldatetime两种数据类型。datetime：存储从1753年1月1日到9999年12月31日的日期和时间数据，每一个值要求8个字节，精确到百分之三秒（或3.33毫秒）。smalldatime：存储从1900年1月1日到2079年12月31日的日期和时间数据，每一个值要求4个字节，精确到分钟。设置日期格式的命令如下：set dateformat format|format_var其中,format|format 是日期的格式。有效参数包括MDY、DMY、YMD、YDM、MYD、DYM。默认格式为MDY。例例2-1 设置日期格式set dateformat mdygodeclare datevar datetimeset datevar=08/30/2004select datevar173.3.6货币数据类型货币数据类型货币数据类型表示货币数量，货币数据类型包括money和smallmoney。money：货币数值介于-263与263-1之间，精确到货币单位的千分之十，money数据类型要求个存储字节。smallmoney：货币数值介于-214748.3648与+214748.3647之间，精确到货币单位的千分之十，smallmoney数据类型要求4个存储字节。3.3.7特殊数据类型特殊数据类型在SQL中特殊的数据类型有三种：bit、timestamp和uniqueidentifier。bit：由1和0组成，使用bit数据类型可以表示真、假或on、off，不能对bit的列使用索引。timestamp：以二进制格式表示SQL Server活动的先后顺序。在数据库范围内，timestamp是唯一的，每次更新行时进行更新。uniqueidentifer：全局唯一标识符（GUID），以十六进制数字表示一个全局惟一的标识。183.3.8 用户自定义数据类型用户自定义数据类型在SQL Server中,用户可以根据需要自定义数据类型。用户可以用SQL语言或企业管理器来自定义数据类型。自定义数据类型定义后，与系统数据类型一样使用。创建自定义数据类型的好处：利用自定义数据类型可使不同表不重复出现的各列数据具有相同的特性。如果把规则和缺省捆绑到一个自定义的数据类型上，那么该规则和缺省也适合于采用此用户自定义数据类型的每个列。1.创建用户自定义数据类型创建用户自定义数据类型()使用SQL 语句创建使用系统存储过程sp_addtype创建，语法如下：sp_addtype type_name,phystype(length)|(precision,scale),null|not null|identity其中：type_name：是用户定义的数据类型的名字。Phystype：是用户自定义数据类型所基于的系统数据类型，可以包括长度、精度、标度。null|not null|identity：用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。如果不指定列的性质，默认为not null。19例例2-2 创建两个自定义数据类型创建两个自定义数据类型use jwglgoexec sp_addtype a0,varchar(6),nullexec sp_addtype postalcode,char(6),not null(2)使用企业管理器创建自定义数据类型进入企业管理器进入JWGL数据库右击“用户自定义数据类型”点击“新建用户自定义数据类型”后出现“用户自定义数据类型”窗口输入要定义的数据名称，选择数据类型，输入数据长度，确定是否允许为空点击“确定”。203.查看用户自定义数据类型查看用户自定义数据类型可以用系统存储过程sp_help或企业管理器来查看用户定义的数据类型的特征。语法格式：exec sp_help 自定义数据类型名称3.重命名用户自定义数据类型重命名用户自定义数据类型使用过程sp_rename 可以很方便地重新命名一个用户自定义数据类型。语法格式：exec sp_rename 旧名称,新名称4.删除用户定义数据类型删除用户定义数据类型使用过程sp_droptype 来删除一个已经定义且未被使用的用户自定义数据类型。语法格式：exec sp_droptype 自定义数据类型名必须注意不能删除正在被表或其他数据库使用的用户自定义数据类型。本章首页本章首页本章首页本章首页213.4 变量从变量的作用范围来区分，SQL Server提供两种形式的变量：局部变量和全局变量。3.4.1 3.4.1 局部变量局部变量局部变量一般在批处理中被声明、定义、赋值和引用，批处理结束后，局部变量就消失了。利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。1局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER local_variable data_type n 3.局部变量的赋值方法 SET local_variable=expression 或者 SELECT local_variable=expression ,.n 22expression：与局部定义的数据类型相匹配的表达式。3.局部变量的显示 select variable_name例例2-3 定义一个整型局部变量和一个可变长字符型局部变量并赋值。（P47）declare local_variable_int int,local_variable_char nvarchar(9)set local_variable_int=40 set local_variable_char=Welcom to Nanjing select local_variable_int select local_variable_char go233.4.2 3.4.2 全局变量全局变量全局变量在整个SQL Server系统内使用。存储的通常是一些SQL Server的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头。全局变量对用户来说是只读的。局部变量的名称不能与全局变量的名称相同。常见的全局变量见 P48，也是用select命令来显示其值的。本章首页本章首页本章首页本章首页243.5 运算符 3.5.1 3.5.1 算术运算符算术运算符 算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）3.5.2 3.5.2 赋值运算符赋值运算符Transact-SQL中只有一个赋值运算符，即等号（=）3.5.3 3.5.3 位运算符位运算符位运算符包括按位与（&）、按位或（|）、按位异或（）和求反（）。位运算符用来对整型数据或者二进制数据（image数据类型除外）之间执行位操作。要求在位运算符左右两侧的操作数不能同时是二进制数据。3.5.4 3.5.4 比较运算符比较运算符 比较运算符包括：等于（=）、大于（）、大于或等于（=）、小于（）、小于或等于（=）、不等于（或!=）、不小于（!）。253.5.5 3.5.5 逻辑运算符逻辑运算符逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。逻辑运算返回布尔值，值为TRUE或FALSE。3.5.6 3.5.6 字符串连接运算符字符串连接运算符 （+）字符串运算符加号（字符串运算符加号（+）将两个以上的字符串相联。）将两个以上的字符串相联。例如例如 abc+de abc+de 结果为结果为 abc de abc de3.5.7 3.5.7 运算符的优先级运算符的优先级 括号：（）；乘、除、求模运算符：*，/，%；加减运算符：，；比较运算符：=，=，=，!=，!，!；位运算符：，&，|；逻辑运算符：NOT；逻辑运算符：AND；逻辑运算符：OR。本章首页本章首页本章首页本章首页263.6 函数 使用函数前要加select 命令3.6.1 3.6.1 数学函数数学函数 P51 P513.6.2 3.6.2 字符串函数字符串函数 P52 P523.6.33.6.3 转换函数转换函数 P54 P54 CAST（expression AS data_type）CONVERT（data_type（length）,expression,style）3.6.4 3.6.4 日期和时间函数日期和时间函数 P55 P553.6.5 3.6.5 系统函数系统函数 P56 P563.6.6 3.6.6 集合函数集合函数 P57 P57273.6.6 3.6.6 用户自定义函数用户自定义函数 CREATE FUNCTION 函数名称（形式参数名称 AS 数据类型）RETURNS 返回数据类型BEGIN 函数内容RETURN 表达式END 使用用户自定义函数的基本语法为：变量=用户名.函数名称（实际参数列表）注意：在调用返回数值的用户自定义函数时，一定要在函数名称的前面加上用户名 本章首页本章首页本章首页本章首页283.7 简单的SELECT 语句select语句是数据SQL中最重要的语句，其功能强大，使用灵活。SELECT语句有丰富的子句，其最简单的用法是:select from where 列名表可以用 星号(*)、表达式表达式、列表列表、变量变量等，其中*表示表中所有的列项。例例1 select au_lname,au_fname,phone,city,state from authors例例2 select au_lname,au_fname,phone,city,state from authors where state=CA本章首页本章首页本章首页本章首页293.8 批处理和 流程控制语句通常，服务器端的程序使用SQL语句来编写，一般而言，一个服务器端的程序是由以下一些成分组成：批；注释；程序中使用的变量；改变批中语句的执行顺序的控制语句；错误和消息的处理。3.8.1 3.8.1 批和脚本批和脚本1.批（batch）两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。这样处理可以节省系统开销。30使用批时有如下限制：1)并不是所有语句都可以和其他语句在一个组合成批。下列语句不能组合在同一个批中：create procedure create rulecreate defaul create triggercreate view 2）规则和缺省不能在同一个批中既绑定到列又被使用。3）CHECK约束不能在同一个批中既定义又使用。4）在同一个批中不能删除对象又重新它。5）用SET语句改变的选项在批结束时生效。6）在同一个批中不能改变一个表再立即引用其新列。另外当一外含有多个批的SQL脚本提交执行时发生错误，SQL服务器显示出的错误行提示是错误语句所在批中的行号，而不是该语句在整个SQL脚本中的行号。3.脚本脚本是一系列顺序提交的批。313.8.2 3.8.2 流程控制语句流程控制语句 1.BEGINEND语句语句 当需要将一个以上的SQL语句作为一个组对待时，可以使用BEGIN 和END 将它们括起来形成一个SQL语句块。BEGIN sql_statement|statement_block END3.IFELSE语句语句 IFELSE命令使得SQL命令的执行是有条件的。当IF条件成立时，就执行其后的SQL语句，否则就执行ELSE后面的SQL语句。IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_block 323.CASE语句语句CASE语句根据表达式值的不同，取相应的值。语句根据表达式值的不同，取相应的值。简单CASE语句的语法形式为：CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END 搜索CASE语句的语法形式为：CASE WHEN Boolean_expression THEN result_expression.n ELSE else_result_expression END334.WHILE 语句语句 WHILE语句的作用是当条件为真时重复执行某一语句或语句块，直到条件为假。WHILE Boolean_expression sql_statement BREAK sql_statement CONTINUE sql_statement5.PRINT PRINT语句的作用是在屏幕上显示用户的信息。该语句语法如下：print string|local_variable|local_variable 其中，要string 代表一个不超过255字节的字符串。34写在最后写在最后成功的基成功的基础在于好的学在于好的学习习惯The foundation of success lies in good habits35 结束语当你尽了自己的最大努力时，失败也是伟大的，所以不要放弃，坚持就是正确的。When You Do Your Best,Failure Is Great,So DonT Give Up,Stick To The End演讲人：XXXXXX 时 间：XX年XX月XX日