T-SQL基础和存储过程.ppt

第六章,T-SQL 基础和存储过程,T-SQL 基础和存储过程,第1讲 T-SQL语言基础,学习要点,T-SQL语言基础 存储过程,T-SQL定义、功能、组成 定义 Transact-SQL（简写为T-SQL）是SQL Server对标准SQL功能的增强与扩充 功能 T-SQL可以完成数据库上的各种操作，而且可以编制复杂的例行程序 组成,系统内置函数,语言基础,T-SQL,学习要点,变量 显示和输出语句 流程控制语句 CASE表达式,标识符 注 释 批处理 运算符,语言基础,标识符 定义 标识符是指用户在SQL Server中定义的服务器、数据库、数据库对象、变量和列等对象名称 分类 常规标识符 定界标识符,T-SQL,T-SQL语言基础,标识符,常规标识符 命名原则 标识符长度可以为1128个字符。 标识符的首字符必须为Unicode 2.0标准所定义的字母 或_、符号。 标识符第一个字符后面的字符可以为Unicode Standard 2.0所定义的字符、数字或、#、$、_符号。 标识符内不能嵌入空格和特殊字符。 标识符不能与SQL Server中的保留关键字同名,返回,SELECT * FROM information,此为常规标识符,标识符,定界标识符 实质 包含在双引号 (“) 或者方括号 ( ) 内的标识符号 示例,CREATE table （ column1 CHAR（10） NOT NULL PRIMARY KEY， column2 INT ）,表名Table与T-SQL保留字相同，用方括号来分隔,返回,语言基础,注释 定义 注释是程序代码中不执行的文本字符串。 分类 - 用于单行注释 /* */ 用于多行注释 示例 【例6-2】,T-SQL,T-SQL语言基础,注释,【例6-2】使用单行与多行注释,返回,USE student GO SELECT * FROM information - -从表information中查询信息 GO /*下面SQL语句 查询学生信息*/ SELECT * FROM information GO,语言基础,批处理 说明 多条语句作为一个批处理执行时，其语句之间用GO分隔 示例 【例6-3】批处理语句,T-SQL,T-SQL语言基础,USE student GO CREATE VIEW v_information AS SELECT * FROM information GO SELECT * FROM v_information GO,语言基础,运算符 算术运算符 位运算符 比较运算符 逻辑运算符,T-SQL,T-SQL语言基础,赋值运算符 字符串连接符 单目运算符 运算符优先级,运算符,算术运算符 加（+） 减（-） 乘（*） 除（/） 取模（%）,返回,运算符,位运算符 对整数或二进制数据进行按位逻辑运算 例如 与（&） 或（|） 异或（） 求反（）等,返回,运算符,比较运算符 用来比较两个表达式的值是否相同 SQL Server支持的比较运算符包括 ： 大于。 = 等于。 = 大于等于。 不等于。 != 不等于。 ! 不大于。 ! 不小于,返回,运算符,逻辑运算符 测试条件是否为真 ，返回TRUE、FALSE或UNKNOW SQL Server支持的逻辑运算符包括 ： AND OR NOT,返回,运算符,赋值运算符 SQL Server中的赋值运算符为等号（=），它将表达式的值赋给一个变量 【例6-4】使用赋值运算符。,返回,DECLARE kccj INT SET kccj =80,运算符,字符串连接符 实现字符串之间的连接操作 【例6-5】实现字符串连接 。,返回,SELECT abc + 123,返回结果为：abc123,运算符,单目运算符 只有一个操作数的运算符 包括+（正）、-（负）和（位反） 【例6-6】使用单目运算符,返回,DELCLARE intNum INT SET intNum=10 SELECT -intNum,返回结果为：-10,运算符,运算符优先级 +(正)、-(负) 、(按位取反) *、/、% +（加）、+（连接）、-（减） =、=、!=、！和！（比较运算符） （位异或）、 &（位与）、|（位或） NOT AND OR、ALL、ANY、BETWEEN、IN、LIKE、SOME =（赋值）,返回,语言基础,变量 局部变量 全局变量,T-SQL,T-SQL语言基础,变量,局部变量 指名称以一个字符开始，由用户自己定义和赋值的变量 变量声明 变量赋值,返回,局部变量,变量声明 DECLARE语句声明变量，并在声明后将变量的值初始化为NULL 基本语句： 示例 【例6-7】 【例6-8】,局部变量,DECLARE variable_name date_type ，variable_name data_type,【例6-7】 声明一个datetime类型变量,DECLARE birthday datetime,返回,【例6-8】 声明两个变量var1和var2，它们的数据类型分别为int和char,DECLARE var1 INT，var2 CHAR（8）,DECLARE语句中可以同时声明多个局部变量，它们相互之间用 逗号分隔,返回,局部变量,变量赋值 变量声明后，DECLARE语句将变量初始化为NULL ，可使用SET语句为变量赋值 基本语句： 示例 【例6-9】 【例6-10】,局部变量,SELECT variable_name = expression FROM WHERE ,【例6-9】 利用SET语句为所声明的birthday变量赋值,DECLARE birthday datetime SET birthday = 2006-4-1,返回,【例6-10】 用SELECT语句将Course表中的最高学分赋值给变量maxxf： 返回结果:,USE student DECLARE maxxf INT SELECT maxxf = MAX （c_credit） FROM course PRINT maxxf,返回,声明maxxf变量,给maxxf变量赋值,输出maxxf的值,5,变量,全局变量 指由系统定义和维护，名称以字符开始的变量 注意要点 全局变量不是由用户的程序定义的，它们是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符“”开头 局部变量的名称不能与全局变量的名称相同 常用全局变量 ERROR 【例6-11】 ROWCOUNT 【例6-12】 IDENTITY,返回,【例6-11】 使用 全局变量ERROR 在一个 UPDATE 语句中检测限制检查冲突（错误 #547） 返回结果,USE student GO UPDATE information SET s_sex = 工 WHERE s_no = 20021003010 IF ERROR = 547 PRINT 违反Check约束!,返回,返回最后执行的T-SQL语句的错误代码 返回类型为integer,【例6-12】 执行 UPDATE 语句并用 ROWCOUNT 来检测是否有发生更改的行 返回结果,USE student GO UPDATE information SET s_name = 张三 WHERE s_no = 200200000001 IF ROWCOUNT = 0 PRINT 警告：没有数据被更新!,返回,返回受上一语句影响的行数,语言基础,显示和输出语句 PRINT语句 RAISERROR语句,T-SQL,T-SQL语言基础,显示与输出语句,PRINT语句 把用户定义的消息返回客户端 基本语句： 示例 输出Hello World字符串,显示与输出语句,PRINT any ASCII text | local_variable | FUNCTION | string_expr,PRINT Hello World,显示与输出语句,RAISERROR语句 返回用户定义的错误信息 基本语句： 示例 使用RAISERROR 返回结果,显示与输出语句,RAISERROR （ msg_id | msg_str ， severity ， state ）,RAISERROR （发生错误， 16， 1）,语言基础,流程控制语句 流程控制语句用于控制T-SQL的执行流程 BEGINEND语句 条件语句 转移语句 循环语句 返回语句,T-SQL,T-SQL语言基础,流程控制语句,BEGINEND语句 基本语句格式 示例：,流程控制语句,BEGIN SQL语句语句块 END,DECLARE MyVar float SET MyVar = 456.256 BEGIN PRINT 变量MyVar的值为： PRINT CAST（MyVar AS VARCHAR（12） END,流程控制语句,条件语句 基本语句格式 示例：,流程控制语句,IF SQL语句语句块 ELSE SQL语句语句块,USE student GO IF EXISTS （SELECT * FROM information WHERE s_name=刘志） PRINT 刘志 ELSE PRINT 没有名为刘志的同学,流程控制语句,转移语句 基本语句格式 说明： 将SQL语句的执行流程无条件转移到用户所指定的标号处 ，一般避免使用GOTO语句,流程控制语句,GOTO ,流程控制语句,循环语句 基本语句格式 示例： 求1到10之间的奇数和,流程控制语句,WHILE SQL语句语句块 BREAK SQL语句语句块 CONTINUE SQL语句语句块,DECLARE i SMALLINT，sum SMALLINT SET i=1 SET sum=0 WHILE i=10 BEGIN SET sum=sum+i SET i=i+1 END PRINT 1到10之间的奇数和为+STR（sum）,流程控制语句,返回语句 基本语句格式 说明： RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行,流程控制语句,RETURN integer_expression ,返回整形值,语言基础,CASE表达式 CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。 分类： 简单CASE函数 搜索CASE函数,T-SQL,T-SQL语言基础,CASE表达式,简单CASE函数 将某个表达式与一组简单表达式进行比较以确定结果 基本语句格式 示例 【例6-17】,CASE表达式,CASE input_expression WHEN WHEN_expression THEN result_expression .n ELSE ELSE_result_expression END,【例6-17】 把查询到国家的英文名用中文显示 返回结果,返回,USE pubs SELECT pub_name，国家=case country WHEN USA THEN 美国 WHEN Germany THEN 德国 WHEN France THEN 法国 ELSE 其它 END FROM publishers,CASE表达式,搜索CASE函数 CASE 搜索函数计算一组布尔表达式以确定结果 基本语句格式 示例 【例6-18】,CASE表达式,CASE WHEN Boolean_expression THEN result_expression .n ELSE ELSE_result_expression END,【例6-18】 设置student数据库中score表中的成绩对应的等级 返回结果,返回,USE student GO SELECT *，等级=case WHEN grade=90 THEN 优秀 WHEN grade=80 THEN 良 WHEN grade=70 THEN 中 WHEN grade=60 THEN 及格 ELSE 不及格 END FROM score WHERE semester=200401,语言基础,系统内置函数 数据转换函数 字符串函数 日期和时间函数 聚合函数 数学函数,T-SQL,T-SQL语言基础,系统内置函数,数据转换函数 示例 【例6-19】 【例6-20】,系统内置函数,【例6-19】 使用 CAST 数据类型转换函数 返回结果,返回,USE student GO SELECT 成绩是： + CAST（grade AS VARCHAR（12） FROM score WHERE grade 85.0,【例6-20】 使用Convert（CHAR（20），birthday）将日期型变量birthday转换为字符型 返回结果,返回,DECLARE birthday datetime; SELECT birthday =s_birth FROM information WHERE s_name=周天 PRINT 周天的生日是：+Convert（CHAR（20），birthday）,周天的生日是：04 20 1984 12：00AM,系统内置函数,字符串函数,系统内置函数,示例 【例6-21】 【例6-22】,【例6-21】 使用 xxx 替换 abcdefghi 中的字符串 cde 返回结果,返回,SELECT REPLACE（abcdefghicde，cde，xxx）,abxxxfghixxx,【例6-22】 使用LTRIM消除字符串左边的空格 返回结果,返回,SELECT 消除左边空格的结果：+LTRIM（ 1234 ）,消除左边空格的结果：1234,系统内置函数,日期和时间函数 示例 【例6-22】 【例6-23】,系统内置函数,【例6-22】 取得系统当前日期 返回结果,返回,SELECT 当前日期：+convert（CHAR（50），GETDATE（）,当前日期：11 27 2006 10：28AM,【例6-23】 取得系统当前日期中的月份 返回结果,SELECT 月份为：+ convert（CHAR（10）， DATEPART（month， GETDATE（）,月份为：11,返回,系统内置函数,聚合函数 示例 【例6-24】取得数据库所在的计算机名 返回结果,SELECT HOST_NAME（）,E-EIW9TD75VVEV0,系统内置函数,系统内置函数,数学函数 示例 【例6-25】,系统内置函数,【例6-25】 显示使用 CEILING 函数的正数、负数和零值 返回结果,SELECT CEILING（123.45）， CEILING（-123.45）， CEILING（0.0）,124 -123 0,返回,存储过程,存储过程 简介 SSMS创建存储过程 T-SQL创建和执行存储过程 修改和删除存储过程 常用系统存储过程,学习要点,存储过程,简介 定义： SQL Server提供的一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务 分类 系统提供的存储过程 定义的存储过程 扩展存储过程,存储过程,存储过程,SSMS创建存储过程 Step1: 展开数据库右键选择“存储过程” 快捷菜单,next,存储过程,SSMS创建存储过程 Step2: 打开“新建存储过程”对话框 书写存储过程定义,next,存储过程,SSMS创建存储过程 Step3: 查看存储过程,存储过程,存储过程,T-SQL创建和执行存储过程 基本语句 创建： 执行： 示例 存储过程： 【例6-26】 执行存储过程：【例6-27】 综合 【例6-28】,存储过程,CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT， ， 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 , EXEC procedure_name Value_List,【例6-26】 创建一存储过程，检索信息021班学生的记录,返回,USE student GO CREATE PROCEDURE pr_information AS SELECT s_name，s_address FROM information WHERE s_class=信息021,【例6-27】 执行一个名为pr_student的存储过程 返回结果,返回,USE student GO pr_studnet,【例6-28】 创建一存储过程，检索信息021班学生的记录 调用AddCourse存储过程插入一条课程号为gdsx、课程名为高等数学、学分为3的记录,返回,USE student GO CREATE PROC AddCourse ( c_no VARCHAR(12), c_name VARCHAR(20), c_credit INT, c_type VARCHAR(12) ) AS INSERT INTO course VALUES(c_no, c_name, c_credit,c_type),