T-SQL基础教程(课堂PPT).ppt

,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Click to edit Master title style,T_SQL,程序设计基础,1.1 T-SQL,语言简介,T-SQL,语言,SQL(Structure Query Language),语言是关系型数据库标准应用语言,Transact-SQL(,简写为,T-SQL),是对标准,SQL,的扩展。,T-SQL,特点,功能强大,:,集,DDL DML DCL,于一体,简单易学,非过程化语言,了解常量的使用、,掌握如何定义变量并赋值,掌握如何输出显示数据,理解,SQL,中批处理的概念,掌握,IF,、,WHILE,、,CASE,逻辑控制语句,学习目标,教学内容,使用变量,输出语句,逻辑控制语句,批处理语句,重点：掌握如何定义变量、逻辑控制语句,批处理语句,难点：,case-end,多分支语句,1,常量,常量,表示特定数据值的符号,格式：取决于其数据类型，如：,数值型：,56,12.3,0 x12,1E5,字符型：,abc,货币型：,$12.5,2.SQL Server,变量,和,C,语言一样，变量分为：,局部变量：,局部变量必须以标记,作为前缀，如,age,局部变量的使用也是先声明，再赋值,全局变量,：,全局变量必须以标记,作为前缀，如,version,全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,2.2.2,变量,局部变量：一般出现在批处理、存储过程、触发器中。,定义：,DECLARE,变量名 类型,默认值为,NULL,赋值：,SELECT,变量名,=,值,SET,变量名,=,值,引用：,SELECT,变量名,PRINT,变量名,2.,局部变量,变量声明,DECLARE,VariableName,Data_Type,变量赋值,1,）用,SELECT,语句为局部变量赋初始值,select,variable=expression,2,）用,SET,语句为局部变量赋初值,SET variable=expression,例,Declare a Datetime,b Datetime,select a=max(,出生日期,)from student,Set b=getdate(),select a,局部变量是用户自己定义的，只在定义它的批处理或存储过程中使用。一般用于变量计数或作为其他变量值的存储单元。,3.,局部变量的作用域,局部变量的作用域指可以应用该变量的范围，局部变量作用域从声明它的地方到声明它的批处理或存储过程结束。,也就是说，局部变量只能在声明它的批处理、存储过程或触发器中使用，一旦这些批处理或存储过程结束，局部变量将自动消除。,例：声明一个局部变量,dep _name,把数据库学生选课中的,“,学生,”,表中学号为,101101,的系部名称赋值给局部变量,dep_name,并输出。,练习,在,S_MIS,数据库中，使用,T-SQL,语句实现如下功能：,一、已知当前年份和最大的学生年龄，求该学生的出生年份。并输出出生年份。,(,输出函数为,print),提示：请声明三个变量，第一个变量赋值为当前日期，第二个变量赋值为学生表中的,“,出生日期,”,的最小值。第三个为学生的年龄，最后输出该生出生年份。,2.2.2,变量,全局变量：由系统定义，不能由用户定义。,变量特征：,变量名,引用：,SELECT,变量名,思考：,如何查看,SQL Server,的版本？,全局变量与系统存储过程,1.,查看数据库的版本,select version,2.,查看数据库所在机器操作系统参数,exec master.xp_msver,3.,查看数据库启动的参数,sp_configure,4.,查看数据库启动时间,select convert(varchar(30),login_time,120)from master.sysprocesses where spid=1,查看数据库服务器名和实例名,print Server Name.:+convert(varchar(30),SERVERNAME),print Instance.:+convert(varchar(30),SERVICENAME),5.,查看所有数据库名称及大小,sp_helpdb,重命名数据库用的,SQL,sp_renamedb old_dbname,new_dbname,全局变量与系统存储过程,6.,查看所有数据库用户登录信息,sp_helplogins,查看所有数据库用户所属的角色信息,sp_helpsrvrolemember,修复迁移服务器时孤立用户时,可以用的,fix_orphan_user,脚本或者,LoneUser,过程,更改某个数据对象的用户属主,sp_changeobjectowner objectname=object,newowner=owner,注意,:,更改对象名的任一部分都可能破坏脚本和存储过程。,把一台服务器上的数据库用户登录信息备份出来可以用,add_login_to_aserver,脚本,7.,查看链接服务器,sp_helplinkedsrvlogin,查看远端数据库用户登录信息,sp_helpremotelogin,全局变量与系统存储过程,9.,查看数据库里所有的存储过程和函数,use database_name,sp_stored_procedures,查看存储过程和函数的源代码,sp_helptext procedure_name,查看包含某个字符串,str,的数据对象名称,select distinct object_name(id)from syscomments where text like%str%,建加密的存储过程或函数在,AS,前面加,WITH ENCRYPTION,参数,解密加密过的存储过程和函数可以用,sp_decrypt,过程,全局变量与系统存储过程,10.,查看数据库里用户和进程的信息,sp_who,查看,SQL Server,数据库里的活动用户和进程的信息,sp_who active,查看,SQL Server,数据库里的锁的情况,sp_lock,进程号,1-50,是,SQL Server,系统内部用的,进程号大于,50,的才是用户的连接进程,.,spid,是进程编号,dbid,是数据库编号,objid,是数据对象编号,查看进程正在执行的,SQL,语句,dbcc inputbuffer(),推荐大家用经过改进后的,sp_who3,过程可以直接看到进程运行的,SQL,语句,sp_who3,检查死锁用,sp_who_lock,过程,sp_who_lock,全局变量与系统存储过程,11.,收缩数据库日志文件的方法,收缩简单恢复模式数据库日志，收缩后,database_name_log,的大小单位为,M,backup log database_name with no_log,dbcc shrinkfile(database_name_log,5),12.,分析,SQL Server SQL,语句的方法,:,set statistics time.on|off,set statistics io.on|off,图形方式显示查询执行计划,在查询分析器,-,查询,-,显示估计的评估计划,(D)-Ctrl-L,或者点击工具栏里的图形,文本方式显示查询执行计划,set showplan_all.on|off,set showplan_text.on|off,set statistics profile.on|off,批处理语句,批处理是包含一个或多个,SQL,语句的组，从应用程序一次性地发送到,SQL Server,执行,SQL Server,将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条,客户端应用程序,SQL Server,服务器,批处理语句：,语句,1,语句,2,GO,批处理语句示例,SELECT*FROM student,SELECT*FROM sc,UPDATE sc,SET grade=grade+2,GO,GO,是批处理的标志，表示,SQL Server,将这些,T-SQL,语句编译为一个执行单元，提高执行效率,一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定,3,、程序中的批处理、脚本、注释,哪些语句可以放在一个批处理当中？,实例：,Create table student,(,Sno int primary key,Sname char(10),Ssex char(2),Sage int,Sdept varchar(20),),Alter table student,Add sname char(8)unique,判断该段代码运行是否会出错？,注意：建立批处理应该注意的事项,CREATE DEFAULT,，,CREATE PROCEDURE,，,CREATE RULE,，,CREATE TRIGGER,及,CREATE VIEW,语句不能与其它语句放一个批处理中。,不能在一个批处理中引用其它批处理中所定义的变量。,不能把规则和默认值绑定到表字段或用户自定义数据类型之后，立即在同一个批处理中使用它们；,不能定义一个,CHECK,约束之后，立即在同一个批处理中使用该约束。,如果一个批处理中的第一个语句是执行某个存储过程的,EXECUTE,语句，则,EXECUTE,关键字可以省略；如果该语句不是第一个语句则必须使用,EXECUTE,关键字，,EXECUTE,可以省略为,“,EXEC,”,。,不能删除一个对象之后，在同一个批处理中再次引用这个对象；,不能在修改表中的一个字段之后，立即在同一个批处理中引用新字段名。,GO,语句必须独占一行才能起作用。,块注释符中不能有,GO,，否则影响块注释符的注释范围。,二、注释,TSQL,语句中的注释方法有两种,一、行注释“,-,”,二、块注释“,/,*,/,”,块注释符中不能有,GO,，否则影响块注释符的注释范围。,4,、,WAITFOR,挂起各语句的执行，直到某个时间点到来或在一定的时间间隔后继续执行,格式,WAITFOR DELAY,间隔,WAITFOR TIME,时间点,例,11.11,使用,WAITFOR,实现以下功能：根据,“,学生,”,表输出系部代码为,02,的学号、姓名、出生日期、在输出之前等待,4,秒。,Begin,waitfor time,10:00:00,select*from,学生,End,5 PRINT,语句,SQL Server,向客户程序返回信息的方法除了,SELECT,语句外，还可以使用,PRINT,语句，它的语法格式为：,PRINT,字符串,|,函数,|,局部变量,|,全局变量,课后思考：,创建批处理时的注意事项有哪些？,什么是脚本？,SQL Server,支持哪几种注释？,变量的概念,局部变量的声明、赋值及其作用域,总结,2-1,数据库的物理实现一般包括：,创建数据库,创建表,添加各种约束,创建数据库的登录帐户并授权,创建数据库或表时一般需要预先检测是否存在该对象,数据库从,master,系统数据库的,sysdatabases,表中查询,表从该数据库的系统表,sysobjects,表中查询,总结,2-2,访问,SQL Server,某个数据库中的某个表，需要三层验证：,是否是,SQL Server,的登录帐户,是否是该数据库的用户,是否有足够的权限访问该表,T_SQL,语言流程控制,w,1,、,BeginEnd,在条件和循环等流程控制语句中，要执行两个或两个以上的,T-SQL,语句时就需要使用,BEGINEND,语句。用于封装多个语句，构成语句块，语句块中的语句作为一个整体被执行。,BEGIN END,语句的语法格式为：,BEGIN,语句组,END,例：使用语句显示计算机系的所有学生。,代码如下：,USE S_MIS,GO,BEGIN,PRINT,满足条件的学生：,select *from student where sdept=,计算机系,END,GO,w,2,、逻辑控制语句,IfElse,IF,ELSE,语句,C,语言中的,if-else,语句,if,（条件）,语句,1,；,语句,2,；,else,语句,1,；,语句,2,；,SQL,中的,IF-ELSE,语句,IF,（条件）,BEGIN,语句,1,语句,2,END,ELSE,BEGIN,语句,1,；,语句,2,；,END,同,C,语言一样：,ELSE,是可选部分,如果有多条语句，才需要,BEGIN-END,语句块,问题：,统计并学生选课平均分，如果平均分在,70,以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在,70,以下，显示“本班成绩较差“，并显示后三名学员的考试信息。,分析：,第一步，统计平均成绩存入临时变量；,第二步，用,IF,ELSE,判断；,使用,IF,语句判断,S_MIS,数据库是否存在“,student,”表，如果存在则提示存在。,w,3 CASE,多条件选择语句,CASE,表达式是一个特殊的,Transact-SQL,表达式，它使用户能够很方便的实现多重选择的情况。使用,CASE,表达式，实际上相当于使用多重,IF,THEN,的嵌套循环,但是却简单明了.,CASE,表达式的语法形式为:,CASE ,字段名或变量名,WHEN,逻辑表达式1,THEN,结果表达式1,WHEN,逻辑表达式2,THEN,结果表达式2,WHEN,逻辑表达式3,THEN,结果表达式3,WHEN,逻辑表达式4,THEN,结果表达式4,ELSE,结果表达式,END,练习,1,：使用,case,结构实现如下功能：输出学生姓名，系部，系主任（经济系,-,王成，中文系,-,文黎，建筑系,-,陈露，计算机系,-,章亚，管理系,-,管枫）,练习,2,：输出学号、课程号、成绩、成绩等级（当成绩小于,60,输出不,及格,当,grade=60 and grade=75 and grade=85 and grade,英语,and,数学,语文,then cast(,数学,as varchar)+(,数学,),when,英语,数学,and,英语,语文,then cast(,英语,as varchar)+(,数学,),else cast(,语文,as varchar)+(,语文,),end,最小分数,=,case,when,数学,语文,then cast(,数学,as varchar)+(,英语,),when,英语,语文,then cast(,英语,as varchar)+(,英语,),else cast(,语文,as varchar)+(,语文,),end,平均分数,=cast(,数学,+,英语,+,语文,)/3.0 as numeric(10,2),From stu_sc,面试题：,1.,请教一个面试中遇到的,SQL,语句的查询问题 表中有,A B C,三列,用,SQL,语句实现：当,A,列大于,B,列时选择,A,列否则选择,B,列，当,B,列大于,C,列时选择,B,列否则选择,C,列。,2.,有一张表，里面有,3,个字段：语文，数学，英语。其中有,3,条记录分别表示语文,70,分，数学,80,分，英语,58,分，请用一条,sql,语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：大于或等于,80,表示优秀，大于或等于,60,表示及格，小于,60,分表示不及格。显示格式：,语文 数学 英语,及格 优秀 不及格,逻辑控制语句,WHILE,循环语句,C,语言中的,while,语句,while,（条件）,语句,1,；,语句,2,；,break,；,SQL,中的,WHILE,语句,WHILE,（条件）,BEGIN,语句,1,语句,2,BREAK,END,同,C,语言一样：,BREAK,表示退出循环,如果有多条语句，才需要,BEGIN-END,语句块,6,、,While,在程序中当需要多次重复处理某项工作时，就需要,WHILE,循环语句。,例：使用,WHILE,语句实现以下功能：求,210,的平方。,计算,1,到,100,之间的奇数之和,WHILE,示例,问题：,本次考试成绩较差，假定要提分，确保每人都通过。提分规则很简单，先每人都加,2,分，看是否都通过，如果没有全部通过，每人再加,2,分，再看是否都通过，如此反复提分，直到所有人都通过为止。,分析：,第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。,38,BREAK,和,CONTINUE,可以在,While,循环中使用,CONTINUE,和,BREAK,关键字来控制语句的执行。,USE pubs,GO,WHILE(SELECT AVG(price)FROM titles)$50,BREAK,ELSE,CONTINUE,END,PRINT,价格太高，市场无法承受,39,GOTO,关键字,GOTO：,可以将执行流程改变到由标签指定的位置。系统跳过,GOTO,关键字之后的语句，并在,GOTO,语句中指定的标签处继续执行操作。,语法：,其中,label,是标签,GOTO label,40,RETURN,RETURN：,可以在任意位置使用,RETURN,从语句块或过程中退出。系统将不会执行,RETURN,语句之后的语句。,语法：,RETURN integer_expression,其中,integer_expression,是值为整数的表达式。,41,课堂练习,如果平均价格少于$30，将价格加倍，然后选择最高价。如果最高价少于或等于$50，再次将价格加倍。该循环不断地将价格加倍直到最高价格超过$50，然后退出。,（价格,price，,表名,titles）,42,答案,USE pubs,GO,WHILE(SELECT AVG(price)FROM titles)$50,BREAK,ELSE,CONTINUE,END,