Tsql程序设计

1、Version 3.0T-SQL程序设计程序设计数据库设计的概念数据库设计的概念数据模型数据模型实体关系模型实体关系模型数据规范化数据规范化理解批和批处理的概念理解批和批处理的概念编写编写 SQL 逻辑控制语句来表示应用逻辑逻辑控制语句来表示应用逻辑变量的定义与赋值变量的定义与赋值使用使用 SQL Server 基本函数编写基本函数编写 SQL 语句语句在查询语句中使用基本函数在查询语句中使用基本函数批处理批处理单个单个 SQL 命令命令组成一批组成一批作为单个作为单个执行计划执行计划进行编译进行编译输出结果输出结果 以一次执行一批命令中的一条命以一次执行一批命令中的一条命令的方式处理一组命令

2、的过程被令的方式处理一组命令的过程被称为称为 批处理批处理Use Pubs Select * from authors Update authors set phone= 890 451-7366 where au_lname= White Go 标志着批处理的结束标志着批处理的结束注释常用来记录程序名称、作者名称以及对注释常用来记录程序名称、作者名称以及对代码进行重要修改的日期。代码进行重要修改的日期。注释可以用于建立代码文档，或者暂时禁用注释可以用于建立代码文档，或者暂时禁用正在诊断的部分正在诊断的部分 T-SQL 语句和批处理。语句和批处理。注释可以用来描述复杂的计算或用来解释程注释可以

3、用来描述复杂的计算或用来解释程序设计的方法。序设计的方法。SQL Server 支持下列两种类型的注释方式支持下列两种类型的注释方式：1）-（双连字符）双连字符） 例如：例如： USE Northwind GO - 单行注释单行注释2）/* . */（正斜线（正斜线 - - 星号字符对）星号字符对） 例如：例如： SELECT * FROM Employees /* 这是注释这是注释 */ /* */ 多行注释不能跨批处理。完整的注释必多行注释不能跨批处理。完整的注释必须包含在一个批处理中。须包含在一个批处理中。例如，在例如，在 SQL 查询分析器中，查询分析器中，GO 命令标志命令标志着批处

4、理的结束。当实用工具在一行的前两着批处理的结束。当实用工具在一行的前两个字节中读取到字符个字节中读取到字符 GO 时，就会将从上一时，就会将从上一个个 GO 命令至该行前的所有代码作为一个批命令至该行前的所有代码作为一个批处理发送到服务器处理发送到服务器。 顺序执行顺序执行程序程序控制语句控制语句顺序执行顺序执行流程改变流程改变程序程序控制关键字控制关键字目的目的BEGINEND创建语句块。创建语句块。GOTO label将流程转到指定标签。将流程转到指定标签。IFELSE根据指定条件执行不同的几组语句。根据指定条件执行不同的几组语句。WHILE当指定条件为当指定条件为 TRUE TRUE 时

5、重复执行语句。时重复执行语句。BREAK中止执行流并跳出当前的中止执行流并跳出当前的 WHILE WHILE 循环。循环。CONTINUE重新执行重新执行 WHILE WHILE 循环。循环。WAITFOR设置语句执行延迟的时间。设置语句执行延迟的时间。RETURN无条件退出。无条件退出。 BEGIN.END：一组要执行的一组要执行的 T-SQL 语句可以包含在语句可以包含在 BEGIN.END 中。中。 语法：语法： 其中其中，statement 为语句，为语句，statement_block 为语为语句块。句块。BEGIN statement | statement_block END I

6、F.ELSE：可以根据指定的条件来执行不同的可以根据指定的条件来执行不同的 SQL 语句。语句。 语法：语法：其中其中，Boolean_expression 为布尔表达式，为布尔表达式，sql_statement 为为 SQL 语句，语句， statement_block 为语为语句块。句块。IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_block IF SELECT COUNT(ORDERID) FROM ORDERS)1 BEGIN GOTO X END ELSE BEGIN

7、 SELECT * FROM CUSTOMERS ENDSELECT * FROM ORDERSX:SELECT * FROM SHIPPERS WHILE：可以根据某些条件来执行一条可以根据某些条件来执行一条 SQL 语语句或一组语句。只要指定的条件为真，则重复句或一组语句。只要指定的条件为真，则重复执行该语句。执行该语句。 语法：语法：其中，其中，Boolean_expression 为布尔表达式，为布尔表达式， statement 为语句，为语句，statement_block 为语句块。为语句块。WHILE Boolean_expression statement | statemen

8、t_block BREAK statement | statement_block CONTINUE 可以在可以在 While 循环中使用循环中使用 CONTINUE 和和 BREAK 关键字来控制语句的执行。关键字来控制语句的执行。USE pubs GO WHILE (SELECT AVG(price) FROM titles) $50 BREAK ELSE CONTINUE END PRINT 价格太高，市场无法承受价格太高，市场无法承受GOTO：可以将执行流程改变到由标签指定的位置。可以将执行流程改变到由标签指定的位置。系统跳过系统跳过 GOTO 关键字之后的语句，并在关键字之后的语句，

9、并在 GOTO 语语句中指定的标签处继续执行操作。句中指定的标签处继续执行操作。语法：语法：其中其中 label 是标签是标签GOTO labelRETURN：可以在任意位置使用可以在任意位置使用 RETURN 从语句块或从语句块或过程中退出。系统将不会执行过程中退出。系统将不会执行 RETURN 语句之后的语句之后的语句。语句。语法：语法：RETURN integer_expression其中其中 integer_expression 是值为整数的表达式。是值为整数的表达式。 如果平均价格少于如果平均价格少于 $30 $30，将价格加倍，然后，将价格加倍，然后选择最高价。如果最高价少于或等于

10、选择最高价。如果最高价少于或等于 $50 $50，再次将价格加倍。该循环不断地将价格加倍再次将价格加倍。该循环不断地将价格加倍直到最高价格超过直到最高价格超过 $50 $50，然后退出。，然后退出。 （价格（价格priceprice，表名表名titlestitles） WHILE (SELECT AVG(price) FROM titles) $30BEGIN WHILE (SELECT AVG(price) FROM titles) $50 BREAK IF (SELECT MAX(price) FROM titles) $50 BREAK ELSE ELSE CONTINUECONTINU

11、E ENDEND CASE：使用使用 CASE 关键字可以根据表达式是否为真关键字可以根据表达式是否为真来返回一个值。该关键字可用于允许使用表达式的来返回一个值。该关键字可用于允许使用表达式的任何地方。任何地方。语法：语法：CASE expressionWHEN expression1 THEN expression1WHEN expression2 THEN expression2 ELSE expression END其中其中 expression 为表达式。为表达式。SELECT au_fname, au_lname, CASE state WHEN OR THEN Oregon END

12、 AS StateName FROM authors 示例：示例： 下面示例在查询结果集内显示每个作者所居住州下面示例在查询结果集内显示每个作者所居住州的全名：的全名： SELECT au_fname, au_lname,SELECT au_fname, au_lname, CASE state CASE state WHEN CA THEN California WHEN CA THEN California WHEN TN THEN Tennessee WHEN TN THEN Tennessee WHEN OR THEN Oregon WHEN OR THEN Oregon END AS

13、 StateName END AS StateName FROM authors FROM authors ORDER BY au_lname ORDER BY au_lname UPDATE publishers SET state = CASE WHEN country USA THEN - ELSE state END, city = CASE WHEN pub_id = 9999 THEN LYON ELSE city END WHERE country USA OR pub_id = 9999 以这种方式以这种方式可以用一个可以用一个命令更新两命令更新两个或多个列个或多个列 例例4-

14、10 调整员工工资工作级别为调整员工工资工作级别为1 的上调的上调8% 工作工作级别为级别为2 的上调的上调7% 工作级别为工作级别为3 的上调的上调6% 其它上调其它上调5% use pangu update employee set e_wage = case when job_level = 1 then e_wage*1.08 when job_level = 2 then e_wage*1.07 when job_level = 3 then e_wage*1.06 else e_wage*1.05end 已知学生成绩表：已知学生成绩表： 姓名姓名 数学数学 英语英语 语文语文 张三

15、张三 50 80 70 李四李四 80 68 82 黄六黄六 85 78 90 需要得出结果集：需要得出结果集： 姓名姓名 最大分数最大分数 最小分数最小分数 平均分数平均分数 张三张三 80（英语）（英语） 50（数学）（数学）66.67 李四李四 82 68 76.67 黄六黄六 90 78 84.33Select姓名姓名, 最大分数最大分数= case when 数学数学英语英语 and 数学数学语文语文 then cast(数学数学 as varchar)+(数学数学)when 英语英语数学数学 and 英语英语语文语文 then cast(英语英语 as varchar)+(英语英语

16、)else cast(语文语文 as varchar)+(语文语文) end, 最小分数最小分数=case when 数学数学英语英语 and 数学数学语文语文 then cast(数学数学 as varchar)+(数学数学) when 英语英语数学数学 and 英语英语语文语文 then cast(英语英语 as varchar)+(英语英语) else cast(语文语文 as varchar)+(语文语文) end, 平均分数平均分数=cast(数学数学+英语英语+语文语文)/3.0 as numeric(10,2)From 学生成绩表学生成绩表SQL Server 在在 T-SQL

17、中支持下列两种类中支持下列两种类型的变量型的变量 全局变量全局变量局部变量局部变量SQL Server 中的全局变量都用两个中的全局变量都用两个 标记作标记作为前缀。可以使用简单的为前缀。可以使用简单的 SELECT 查询语句检查询语句检索任意全局变量的值。索任意全局变量的值。SELECT VERSIONAS SQL_SERVER_VERSION_DETAILS变量变量含义含义connections自服务器上次启动以来，已创建的自服务器上次启动以来，已创建的连接数。连接数。CPU_BUSY自自 SQL Server SQL Server 启动至今，系统持启动至今，系统持续运行的毫秒数。续运行的

18、毫秒数。CURSOR_ROWS最近打开的游标中的行数最近打开的游标中的行数DATEFIRSTSET DATEFIRST SET DATEFIRST 参数的当前值，该参数的当前值，该参数用于设置一个星期的第一天为参数用于设置一个星期的第一天为哪一天。哪一天。ERROR最后一个最后一个 T-SQL T-SQL 错误的错误号错误的错误号FETCH_STATUS如果最后一次提取的状态为成功状如果最后一次提取的状态为成功状态，则为态，则为 0 0。如果出错，则为。如果出错，则为 -1 -1变量变量含义含义IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用

19、的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数可以创建的同时连接的最大数ROWCOUNT受上一个受上一个 SQL 语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称SERVICENAME该计算机上的该计算机上的 SQL 服务的名称服务的名称TIMETICKS当前计算机上每指令周期的微秒数当前计算机上每指令周期的微秒数TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQL Server 的版本信息的版本信息变量是可以存储数据值的对象变量是可以存储数据值的对象使用局部变量可以将数据传递到使用局部变量可以将数据传递到 SQ

20、L 语句语句局部变量名必须以局部变量名必须以 为前缀为前缀SET 或或 SELECT 语句用于给局部变量赋值语句用于给局部变量赋值例如：例如：DECLARE cust VARCHAR(20)SET cust= FRANKSET 语句或语句或 SELECT 语句用于给局部变量赋值。语句用于给局部变量赋值。语法：语法：SET local_variable name = value 或或SELECT local_variable name = value其中其中 local_variable name 为局部变量名为局部变量名例如，为先前声明的变量赋值：例如，为先前声明的变量赋值：SET CUST=

21、FRANKSELECT CUSTOMERID,COMPANYNAMEFROM CUSTOMERSWHERE CUSTOMERID = CUSTDECLARE CUST VARCHAR(5)SET CUST=FRANKSELECT CUSTOMERID,COMPANYNAMEFROM CUSTOMERSWHERE CUSTOMERID = CUSTT-SQL 有三种类型的函数有三种类型的函数: : 行集函数行集函数 聚合函数聚合函数 标量函数标量函数转换函数用于将一种数据类型的值转换为另一种类型的值。转换函数用于将一种数据类型的值转换为另一种类型的值。 另外，该函数还可以用于获得各种特殊的日期格式。另外，该函数还可以用于获得各种特殊的日期格式。 SQL Server 提供了一个转换函数，即提供了一个转换函数，即 CONVERT( )。语法：语法：CONVERT(datatype(length),expression,style)其中其中 datatype 为数据类型，为数据类型，length 为数据长度，为数据长度，expression 为表达式，为表达式，style 为样式。为样式。SELECT EMP ID: + CONVERT (CHAR(4), EMPLOYEEID FROM EMPLOYEES 示例：示例：日期部分日期部分缩写缩写值值星期星期wk1-53星期