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 studentGO SELECT*FROM information-从表information中查询信息GO/*下面SQL语句查询学生信息*/SELECT*FROM information GO,语言基础,批处理说明多条语句作为一个批处理执行时，其语句之间用GO分隔 示例【例6-3】批处理语句,T-SQL,T-SQL语言基础,USE studentGO CREATE VIEW v_information AS SELECT*FROM informationGO SELECT*FROM v_informationGO,语言基础,运算符算术运算符位运算符比较运算符逻辑运算符,T-SQL,T-SQL语言基础,赋值运算符字符串连接符单目运算符运算符优先级,运算符,算术运算符加（+）减（-）乘（*）除（/）取模（%）,返回,运算符,位运算符对整数或二进制数据进行按位逻辑运算例如与（&）或（|）异或（）求反（）等,返回,运算符,比较运算符用来比较两个表达式的值是否相同 SQL Server支持的比较运算符包括：大于。=等于。=大于等于。不等于。!=不等于。!不大于。!不小于,返回,运算符,逻辑运算符测试条件是否为真，返回TRUE、FALSE或UNKNOW SQL Server支持的逻辑运算符包括：ANDORNOT,返回,运算符,赋值运算符SQL Server中的赋值运算符为等号（=），它将表达式的值赋给一个变量【例6-4】使用赋值运算符。,返回,DECLARE kccj INTSET kccj=80,运算符,字符串连接符实现字符串之间的连接操作【例6-5】实现字符串连接。,返回,SELECT abc+123,返回结果为：abc123,运算符,单目运算符只有一个操作数的运算符包括+（正）、-（负）和（位反）【例6-6】使用单目运算符,返回,DELCLARE intNum INTSET intNum=10SELECT-intNum,返回结果为：-10,运算符,运算符优先级+(正)、-(负)、(按位取反)*、/、%+（加）、+（连接）、-（减）=、=、!=、！和！（比较运算符）（位异或）、&（位与）、|（位或）NOTANDOR、ALL、ANY、BETWEEN、IN、LIKE、SOME=（赋值）,返回,语言基础,变量局部变量全局变量,T-SQL,T-SQL语言基础,变量,局部变量指名称以一个字符开始，由用户自己定义和赋值的变量 变量声明变量赋值,返回,局部变量,变量声明DECLARE语句声明变量，并在声明后将变量的值初始化为NULL 基本语句：示例【例6-7】【例6-8】,局部变量,DECLAREvariable_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 studentDECLARE maxxf INTSELECT maxxf=MAX（c_credit）FROM coursePRINT maxxf,返回,声明maxxf变量,给maxxf变量赋值,输出maxxf的值,5,变量,全局变量指由系统定义和维护，名称以字符开始的变量 注意要点全局变量不是由用户的程序定义的，它们是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头局部变量的名称不能与全局变量的名称相同 常用全局变量ERROR【例6-11】ROWCOUNT【例6-12】IDENTITY,返回,【例6-11】使用 全局变量ERROR 在一个 UPDATE 语句中检测限制检查冲突（错误#547）返回结果,USE studentGOUPDATE information SET s_sex=工WHERE s_no=20021003010IF ERROR=547 PRINT 违反Check约束!,返回,返回最后执行的T-SQL语句的错误代码返回类型为integer,【例6-12】执行 UPDATE 语句并用 ROWCOUNT 来检测是否有发生更改的行 返回结果,USE studentGOUPDATE 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语句 基本语句格式示例：,流程控制语句,BEGINSQL语句语句块END,DECLARE MyVar floatSET MyVar=456.256BEGIN PRINT 变量MyVar的值为：PRINT CAST（MyVar AS VARCHAR（12）END,流程控制语句,条件语句基本语句格式示例：,流程控制语句,IFSQL语句语句块ELSESQL语句语句块,USE studentGOIF EXISTS（SELECT*FROM information WHERE s_name=刘志）PRINT 刘志ELSEPRINT 没有名为刘志的同学,流程控制语句,转移语句基本语句格式说明：将SQL语句的执行流程无条件转移到用户所指定的标号处，一般避免使用GOTO语句,流程控制语句,GOTO,流程控制语句,循环语句基本语句格式示例：求1到10之间的奇数和,流程控制语句,WHILESQL语句语句块BREAKSQL语句语句块CONTINUESQL语句语句块,DECLARE i SMALLINT，sum SMALLINTSET i=1SET sum=0WHILE i=10BEGINSET sum=sum+iSET i=i+1 ENDPRINT 1到10之间的奇数和为+STR（sum）,流程控制语句,返回语句基本语句格式说明：RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行,流程控制语句,RETURN integer_expression,返回整形值,语言基础,CASE表达式CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。分类：简单CASE函数搜索CASE函数,T-SQL,T-SQL语言基础,CASE表达式,简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果 基本语句格式示例【例6-17】,CASE表达式,CASE input_expressionWHEN WHEN_expression THEN result_expression.n ELSE ELSE_result_expression END,【例6-17】把查询到国家的英文名用中文显示 返回结果,返回,USE pubsSELECT pub_name，国家=case countryWHEN USA THEN 美国WHEN Germany THEN 德国WHEN France THEN 法国ELSE 其它ENDFROM 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 studentGOSELECT*，等级=caseWHEN grade=90 THEN 优秀WHEN grade=80 THEN 良WHEN grade=70 THEN 中WHEN grade=60 THEN 及格ELSE 不及格ENDFROM scoreWHERE semester=200401,语言基础,系统内置函数数据转换函数字符串函数日期和时间函数聚合函数数学函数,T-SQL,T-SQL语言基础,系统内置函数,数据转换函数示例【例6-19】【例6-20】,系统内置函数,【例6-19】使用 CAST 数据类型转换函数返回结果,返回,USE studentGOSELECT 成绩是：+CAST（grade AS VARCHAR（12）FROM scoreWHERE 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 studentGOCREATE PROCEDURE pr_informationASSELECT s_name，s_address FROM informationWHERE s_class=信息021,【例6-27】执行一个名为pr_student的存储过程 返回结果,返回,USE studentGOpr_studnet,【例6-28】创建一存储过程，检索信息021班学生的记录调用AddCourse存储过程插入一条课程号为gdsx、课程名为高等数学、学分为3的记录,返回,USE studentGOCREATE PROC AddCourse(c_no VARCHAR(12),c_name VARCHAR(20),c_credit INT,c_type VARCHAR(12)ASINSERT INTO course VALUES(c_no,c_name,c_credit,c_type),EXEC AddCourse gdsx,1001高等数学(2),3,基础课,存储过程,修改和删除存储过程基本语句格式修改删除示例修改【例6-32】,存储过程,CREATE PROCEDURE 存储过程名 参数1 数据类型=默认值 OUTPUT，参数n 数据类型=默认值 OUTPUT AS SQL语句,DROP PROCEDURE 存储过程名,【例6-32】修改存储过程,返回,USE studentGO-创建存储过程p_1CREATE proc p_1 AsSELECT*FROM courseGO-修改存储过程p_1ALTER PROC p_1 ASSELECT s_name，s_address FROM informationWHERE s_class=信息021 GOEXEC p_1,返回结果,存储过程,常用系统存储过程sp_helpsp_helptextsp_dependssp_stored_proceduressp_rename,存储过程,常用存储过程,sp_help显示存储过程的参数及其数据类型 基本语句格式,返回,sp_help objname=name,存储过程的名称,常用存储过程,sp_helptext显示存储过程的源代码 基本语句格式,返回,sp_helptext objname=name,常用存储过程,sp_depends显示和存储过程相关的数据库对象 基本语句格式,返回,sp_depENDs objname=object,为要查看依赖关系的存储过程的名称,常用存储过程,sp_stored_procedures返回当前数据库中的存储过程列表,返回,常用存储过程,sp_rename重命名存储过程,返回,sp_rename 原存储过程名称，新存储过程名称,