T-SQL基础教程.ppt

1、,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

2、常量,常量 表示特定数据值的符号 格式：取决于其数据类型，如： 数值型：56,12.3,0 x12,1E5 字符型：abc 货币型：$12.5,2.SQL Server 变量,和C语言一样，变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,2.2.2 变量,局部变量：一般出现在批处理、存储过程、触发器中。 定义：DECLARE 变量名 类型 , 默认值为NULL 赋值：SELECT 变量名=值 SET 变量名=值 引用：SEL

3、ECT 变量名 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.

4、局部变量的作用域,局部变量的作用域指可以应用该变量的范围，局部变量作用域从声明它的地方到声明它的批处理或存储过程结束。 也就是说，局部变量只能在声明它的批处理、存储过程或触发器中使用，一旦这些批处理或存储过程结束，局部变量将自动消除。,例：声明一个局部变量dep _name ,把数据库学生选课中的“学生”表中学号为101101的系部名称赋值给局部变量dep_name,并输出。,练习,在S_MIS数据库中，使用T-SQL语句实现如下功能： 一、已知当前年份和最大的学生年龄，求该学生的出生年份。并输出出生年份。(输出函数为 print) 提示：请声明三个变量，第一个变量赋值为当前日期，第二个变量赋

5、值为学生表中的“出生日期”的最小值。第三个为学生的年龄，最后输出该生出生年份。,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.sysprocess

6、es 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 修复迁移服务器时孤立

7、用户时,可以用的fix_orphan_user脚本或者LoneUser过程 更改某个数据对象的用户属主 sp_changeobjectowner objectname = object, newowner = owner 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 7. 查看链接服务器 sp_helplinkedsrvlogin 查看远端数据库用户登录信息 sp_helpremotelogin,全局变量与系统存储过程,9.查看数据库里所有的存储过程和函数 use database_name

8、 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 查看S

9、QL 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 dat

10、abase_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 |

11、 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语句编译为一个执行单元，提高执行效率 一般是将一些逻辑相关的业务操作语句，放置在同

12、一批中，这完全由业务需求和代码编写者决定,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 及

13、CREATE VIEW 语句不能与其它语句放一个批处理中。 不能在一个批处理中引用其它批处理中所定义的变量。 不能把规则和默认值绑定到表字段或用户自定义数据类型之后，立即在同一个批处理中使用它们； 不能定义一个CHECK约束之后，立即在同一个批处理中使用该约束。 如果一个批处理中的第一个语句是执行某个存储过程的EXECUTE语句，则EXECUTE关键字可以省略；如果该语句不是第一个语句则必须使用EXECUTE关键字，EXECUTE可以省略为“EXEC”。 不能删除一个对象之后，在同一个批处理中再次引用这个对象； 不能在修改表中的一个字段之后，立即在同一个批处理中引用新字段名。 GO语句必须独占

14、一行才能起作用。 块注释符中不能有GO，否则影响块注释符的注释范围。,二、注释,TSQL语句中的注释方法有两种 一、行注释“-” 二、块注释“/*/” 块注释符中不能有GO，否则影响块注释符的注释范围。,4、WAITFOR,挂起各语句的执行，直到某个时间点到来或在一定的时间间隔后继续执行 格式 WAITFOR DELAY 间隔 WAITFOR TIME 时间点,例11.11 使用WAITFOR实现以下功能：根据“学生”表输出系部代码为02 的学号、姓名、出生日期、在输出之前等待4秒。 Begin waitfor time 10:00:00 select * from 学生 End,5 PRIN

15、T语句,SQL Server向客户程序返回信息的方法除了SELECT语句外，还可以使用PRINT语句，它的语法格式为： PRINT 字符串|函数|局部变量|全局变量,课后思考：,创建批处理时的注意事项有哪些？ 什么是脚本？ SQL Server支持哪几种注释？ 变量的概念 局部变量的声明、赋值及其作用域,总结 2-1,数据库的物理实现一般包括： 创建数据库 创建表 添加各种约束 创建数据库的登录帐户并授权 创建数据库或表时一般需要预先检测是否存在该对象 数据库从master系统数据库的sysdatabases表中查询 表从该数据库的系统表sysobjects表中查询,总结 2-2,访问SQL

16、Server某个数据库中的某个表，需要三层验证： 是否是SQL Server的登录帐户 是否是该数据库的用户 是否有足够的权限访问该表,T_SQL语言流程控制,1、BeginEnd,在条件和循环等流程控制语句中，要执行两个或两个以上的T-SQL语句时就需要使用BEGINEND 语句。用于封装多个语句，构成语句块，语句块中的语句作为一个整体被执行。,BEGIN END语句的语法格式为： BEGIN 语句组 END,例：使用语句显示计算机系的所有学生。 代码如下： USE S_MIS GO BEGIN PRINT满足条件的学生： select * from student where sdept=

17、计算机系 END GO,2、逻辑控制语句IfElse,IFELSE语句,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以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。,分析： 第一步，统计平均成绩存入临时变量； 第

18、二步，用IFELSE判断；,使用IF语句判断S_MIS数据库是否存在“student”表，如果存在则提示存在。,3 CASE多条件选择语句,CASE表达式是一个特殊的Transact-SQL表达式，它使用户能够很方便的实现多重选择的情况。使用CASE表达式，实际上相当于使用多重IFTHEN的嵌套循环,但是却简单明了.CASE表达式的语法形式为: CASE 字段名或变量名 WHEN 逻辑表达式1 THEN 结果表达式1 WHEN 逻辑表达式2 THEN 结果表达式2 WHEN 逻辑表达式3 THEN 结果表达式3 WHEN 逻辑表达式4 THEN 结果表达式4 ELSE 结果表达式 END 练习

19、1： 使用case结构实现如下功能：输出学生姓名，系部 ，系主任（经济系-王成，中文系-文黎，建筑系- 陈露，计算机系-章亚，管理系-管枫 ） 练习2：输出学号、课程号、成绩、成绩等级（当成绩小于60 输出不及格 当grade=60 and grade=75 and grade=85 and grade100 输出 优秀）,32,课堂练习,已知学生成绩表： 姓名 数学 英语 语文 张三 50 80 70 李四 80 68 82 黄六 85 78 90 需要得出结果集： 姓名 最大分数 最小分数 平均分数 张三 80（英语） 50（数学）66.67 李四 82 68 76.67 黄六 90 78

20、 84.33,33,答案,select sname,最大分数= case when 数学英语 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, 平均分

21、数=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示例,问题：