T-SQL语言程序设计基础.ppt

PrincipleandApplicationofDatabaseSystem AnQingTeachersCollegeDepartmentofComputer Information 数据库原理与应用PrincipleandApplicationofDatabasesystem 安庆师范学院计算机与信息学院 PrincipleandApplicationofDatabaseSystem 10T SQL语言程序设计基础 1 数据定义语言 DDL PrincipleandApplicationofDatabaseSystem 2 数据操纵语言 DML PrincipleandApplicationofDatabaseSystem 3 数据控制语言 DCL PrincipleandApplicationofDatabaseSystem 10 1 1常量 变量与数据类型 10 1 1 1常量 1 字符串常量 PrincipleandApplicationofDatabaseSystem 2 二进制常量 二进制常量具有前辍0 x并且是十六进制数字字符串 这些常量不使用引号 二进制常量的示例为 0 xAE0 x12Ef0 x69048AEFDD010E0 x emptybinarystring PrincipleandApplicationofDatabaseSystem 3 bit常量bit常量使用数字0或1表示 并且不使用引号 如果使用一个大于1的数字 它将被转换为1 PrincipleandApplicationofDatabaseSystem 4 integer常量integer常量由没有用引号括起来且不含小数点的一串数字表示 integer常量必须是整数 不能包含小数点 下面是一些integer常量的示例 18942 PrincipleandApplicationofDatabaseSystem 5 实型常量 PrincipleandApplicationofDatabaseSystem 6 日期时间常量 时间格式 14 30 24 04 24PM 日期时间型 April20 200014 30 24 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 10 1 1 2数据类型 1 系统数据类型 2 用户自定义数据类型 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 1 利用企业管理器定义 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 2 利用命令定义数据类型 语法格式 PrincipleandApplicationofDatabaseSystem sp addtype student no char 5 notnull PrincipleandApplicationofDatabaseSystem 10 1 1 2数据类型 3 自定义数据类型的删除1 用企业管理器删除自定义数据类型 PrincipleandApplicationofDatabaseSystem 10 1 1 2数据类型 2 利用命令删除自定据类型 删除student no类型的语句为 sp droptype student no PrincipleandApplicationofDatabaseSystem 10 1 1 2数据类型 4 利用自定义类型定义字段 PrincipleandApplicationofDatabaseSystem 10 1 1 2数据类型 CREATETABLEstudent snostudent noPRIMARYKEY snamechar 8 ssexchar 2 sbirthdaysmalldatetime classchar 5 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 1 变量 1 标识符 1 常规标识符 2 分隔标识符 用 或 2 变量的分类 1 全局变量 2 局部变量 由若干个中文 字母 数字 构成 不能开头 最多128字符 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 2 局部变量的使用1 局部变量的定义与赋值 1 局部变量的定义 2 局部变量的赋值 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 例如 创建局部变量 var1 var2 并赋值 然后输出变量的值 DECLARE var1varchar 20 var2varchar 20 SET var1 中国 SET var2 var1 是一个伟大的国家 SELECT var1 var2GO PrincipleandApplicationofDatabaseSystem 用SELECT语句赋值SELECT local variable expression n 如果SELECT语句没有返回值 变量将保留当前值如果expression是不返回值的标量子查询 则将变量设为NULL PrincipleandApplicationofDatabaseSystem DECLARE var1nvarchar 30 SELECT var1 刘丰 SELECT var1 snameFROMstudentWHEREsno 110 SELECT var1ASNAME PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 例如 查询用于给 var1赋值 在student表中sno不存在 因此子查询不返回值 并将变量 var1设为NULL DECLARE var1nvarchar 30 SELECT var1 刘丰 SELECT var1 SELECTsnameFROMstudentWHEREsno 110 SELECT var1ASNAMEGO PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 2 局部游标变量的定义与赋值 1 局部游标变量的定义 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 3 游标变量的使用步骤 例如 使用游标变量 DECLARE st CURSORCURSORSET st CURSOR CURSORSCROLLDYNAMICFORSELECTsno sname classFROMstudent PrincipleandApplicationofDatabaseSystem 10 1 1 3变量 OPEN st CURSORFETCHNEXTFROM st CURSORWHILE FETCH STATUS 0FETCHNEXTFROM st CURSORCLOSE st CURSORDEALLOCATE st CURSOR PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 1 算术运算符 例 DECLARE aint bintSET a 11SET b 3SELECT a bAS a b a bAS a b a bAS a b a bAS a b a bAS a b PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 2 位运算符 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 例如 在maste数据库中 建立表bitop 并插入一行 然后将a字段和b字段上的值进行位运算 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 3 比较运算符 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 4 逻辑运算符 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 5 字符串联接运算符 例如 多个字符串的联接 SELECT sno space 2 sname AS 学号姓名 FROMstudent PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 6 一元运算 7 赋值运算符 指给局部变量赋值的SET和SELECT语句中使用的 PrincipleandApplicationofDatabaseSystem 10 2运算符与表达式 8 运算符的优先顺序 PrincipleandApplicationofDatabaseSystem 10 3流程控制语句 PrincipleandApplicationofDatabaseSystem 10 3 1IF ELSE语句 PrincipleandApplicationofDatabaseSystem 10 3 1IF ELSE语句 PrincipleandApplicationofDatabaseSystem 10 3 1IF ELSE语句 例如 如果3 105课程的平均成绩大于80分 显示 3 105课程成绩还不错 否则显示 3 105课程成绩一般 IF SELECTAVG degree FROMscoreWHEREcno 3 105 80PRINT 3 105课程成绩还不错 ELSEPRINT 3 105课程成绩一般 PrincipleandApplicationofDatabaseSystem 10 3 2WHILE BREAK和CONTINUE语句 1 WHILE循环语句 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem DECLARE sint iintSET s 0SET i 1WHILE i 100BEGINSET s s iSET i i 1ENDSELECT i s PrincipleandApplicationofDatabaseSystem 例如 显示字符串 China 中每个字符的ASCII值和字符 DECLARE positionint stringchar 8 SET position 1SET string China WHILE position DATALENGTH string BEGINSELECTASCII SUBSTRING string position 1 SELECTSUBSTRING string position 1 SET position position 1END PrincipleandApplicationofDatabaseSystem 2 BREAK语句一般用于循环语句中 用于退出本层循环 3 CONTINUE语句一般用于循环语句中 结束本次循环 进行下一次循环条件的判断 PrincipleandApplicationofDatabaseSystem 10 3 3GOTO语句 PrincipleandApplicationofDatabaseSystem DECLARE sint iintSET s 0SET i 1loop SET s s iSET i i 1IF i 100GOTOloopSELECT i s PrincipleandApplicationofDatabaseSystem 10 3 4RETURN语句 用于从过程 批处理或语句块中无条件退出 不执行位于RETURN之后的语句 PrincipleandApplicationofDatabaseSystem DECLARE avgfloatIFNOTEXISTS SELECT FROMscoreWHEREsno 108 GOTOlabel1BEGINPRINT 108学生的平均成绩 SELECT avg AVG degree FROMscoreWHEREsno 108 PRINT avgRETURNENDlabel1 PRINT 108学生无成绩 PrincipleandApplicationofDatabaseSystem CREATEPROCmypro nochar 5 ASRETURN SELECTAVG degree FROMscoreWHEREsno no DECLARE nochar 5 avgfloatSET no 108 EXEC avg mypro noSELECT no avg PrincipleandApplicationofDatabaseSystem 10 3 5WAITFOR语句 例如 语句设定在早上八点执行存储过程 添加角色Manager PrincipleandApplicationofDatabaseSystem BEGINWAITFORDELAY 00 00 05 EXECsp addrole Manager END PrincipleandApplicationofDatabaseSystem 10 4函数 编程语言中的函数是用于封装经常执行的逻辑的子例程 任何代码若必须执行函数所包含的逻辑 都可以调用该函数 而不必重复所有的函数逻辑 SQLServer2000支持两种函数类型 内置函数和用户定义函数 PrincipleandApplicationofDatabaseSystem 10 4 1内置函数 聚合函数行集函数 PrincipleandApplicationofDatabaseSystem 1 数学函数 PrincipleandApplicationofDatabaseSystem 例如 下面程序返回给定角的ACOS值 PrincipleandApplicationofDatabaseSystem 例如 下面程序通过RAND函数产生随机值 PrincipleandApplicationofDatabaseSystem 2 字符串处理函数 PrincipleandApplicationofDatabaseSystem 例如 返回课程名最左边的3个字 SELECTLEFT cname 3 FROMcourse PrincipleandApplicationofDatabaseSystem 例如 使用LTRIM字符删除字符变量中的起始空格 DECLARE stringvarchar 40 SET string 中国是一个古老而又伟大的国家 SELECTLTRIM string PrincipleandApplicationofDatabaseSystem 例如 用REPLACE实现字符串的替换 DECLARE str1char 20 str2char 20 str3char 20 SET str1 数据库原理 SET str2 原理 SET str3 应用 SELECTREPLACE str1 str2 str3 PrincipleandApplicationofDatabaseSystem DECLARE str1char 20 SET str1 数据库原理 SELECTsubstring str1 1 2 PrincipleandApplicationofDatabaseSystem 例如 下面程序用于查询101学生的平均成绩 SELECT 101学生的平均成绩为 STR AVG degree FROMscoreWHEREsno 101 PrincipleandApplicationofDatabaseSystem 3 系统函数 1 CASE函数 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 例 查询score表sno sname degree列 对degree列按以下规则进行转换 若degree为90 100 替换为 优秀 若degree为80 89 替换为 良好 若degree在70 79之间 替换为 中等 若degree为60 69之间 替换为 及格 若degree为0 59之间 替换为 不及格 列标题更改为 evaluation PrincipleandApplicationofDatabaseSystem SELECTsno cno evaluation CASEWHENdegree 90ANDdegree 80anddegree 70anddegree 60anddegree 69THEN 及格 ELSE 不及格 ENDFROMscore PrincipleandApplicationofDatabaseSystem 2 CAST和CONVERT函数 将日期型转换为字符型将数值型转换为字符型将money或smallmoney转换为字符型 PrincipleandApplicationofDatabaseSystem 例如 下面程序将检索成绩大于80分的选课记录 并将成绩转换为char 20 用CAST实现SELECTsno cno CAST degreeASCHAR 20 FROMscoreWHEREdegree 80 用CONVERT实现SELECTsno cno CONVERT CHAR 20 degree FROMscoreWHEREdegree 80 PrincipleandApplicationofDatabaseSystem 日期型转换为字符型时style的常用取值 PrincipleandApplicationofDatabaseSystem DECLARE dateSMALLDATETIMESET date getdate SELECTCONVERT CHAR 30 date 0 PrincipleandApplicationofDatabaseSystem float或real型转换为字符型时style的常用取值 PrincipleandApplicationofDatabaseSystem 浮点型转换成字符型DECLARE floatFLOATSET float 12354 23666666666666666SELECTCONVERT CHAR 30 float 0 PrincipleandApplicationofDatabaseSystem money或smallmoney型转换为字符型时style的常用取值 PrincipleandApplicationofDatabaseSystem 货币型转换成字符型SELECTCONVERT CHAR 30 12354 236 0 PrincipleandApplicationofDatabaseSystem 4 日期时间函数 PrincipleandApplicationofDatabaseSystem datepart的取值 PrincipleandApplicationofDatabaseSystem SELECTGETDATE SELECTDATEPART yy 2005 10 2010 49 21 023 SELECTDATEDIFF yy 2005 1 1000 00 00 000 getdate PrincipleandApplicationofDatabaseSystem 5 游标函数 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 6 元数据函数 PrincipleandApplicationofDatabaseSystem 10 4 2用户定义函数 根据用户定义函数的返回值的类型 可将用户定义函数分为如下两类 标量值函数 返回值为标量值 返回值的类型为基本类型 表值函数 返回值为TABLE 表 PrincipleandApplicationofDatabaseSystem 表值函数根据函数主体的定义方式 可分为 内嵌表值函数 没有相关联的返回变量 RETURNS子句指定的TABLE不附带列的列表 一般使用单个SELECT语句组成了函数的主体 该函数返回的表的列来自定义该函数的SELECT语句的SELECT列表 PrincipleandApplicationofDatabaseSystem 多语句表值函数 RETURNS子句指定的TABLE类型带有列及其数据类型 使用一个TABLE变量用于存储函数值返回的行 PrincipleandApplicationofDatabaseSystem 在SQLServer中 用于描述数据库对象的信息均记录在系统表中 通常把这样的表称为元数据表 例如 在数据库创建表 视图 用户函数 存储过程 触发器等对象 都要在系统表sysobjects中登记 如果该数据库对象已经存在 再对其进行定义 则会报错 因些 在定义一个数据库对象前 最好先在系统表sysobjecs中检测该对象是否已经存在 若存在 可先删除之 然后定义新的对象 1系统表sysobjects PrincipleandApplicationofDatabaseSystem 对象标识符 系统表sysobjects的主要字段 PrincipleandApplicationofDatabaseSystem 2用户函数的定义与调用 PrincipleandApplicationofDatabaseSystem 从上述语法形式 归纳出标量函数的一般定义形式如下 PrincipleandApplicationofDatabaseSystem 函数体 指定一系列Transact SQL语句定义函数的值 function body只用于标量函数和多语句表值函数 在标量函数中 function body是一系列合起来求得标量值的Transact SQL语句 在多语句表值函数中 function body是一系列填充表变量的Transact SQL语句 PrincipleandApplicationofDatabaseSystem DECLARE语句 该语句定义函数局部变量赋值语句控制流程语句SELECT语句 表达式将值赋予函数的局部变量INSERT UPDATE和DELETE语句 这些语句修改函数的局部table变量 PrincipleandApplicationofDatabaseSystem 例 定义一个计算长方体体积的用户自定义函数CubicVolume P175 PrincipleandApplicationofDatabaseSystem IFEXISTS SELECT FROMsysobjectsWHEREname CubicVolume ANDtype FN DROPFUNCTIONCubicVolumeGO PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONCubicVolume CubeLengthdecimal 4 1 CubeWidthdecimal 4 1 CubeHeightdecimal 4 1 RETURNSdecimal 12 3 ASBEGINRETURN CubeLength CubeWidth CubeHeight END PrincipleandApplicationofDatabaseSystem 2 标量函数的调用 1 在SELECT语句中调用 例 调用上例定义的CubicVolume函数 计算长 宽 高分别为6 4 3的长方体的体积 PrincipleandApplicationofDatabaseSystem DECLARE CubeLengthdecimal 4 1 CubeWidthdecimal 4 1 CubeHeightdecimal 4 1 CubeVolumedecimal 12 3 SET CubeLength 6SET CubeWidth 4SET CubeHeight 3SELECT CubeVolume dbo CubicVolume CubeLength CubeWidth CubeHeight SELECT CubeVolumeAS 长 宽 高分别为6 4 3长方体的体积为 PrincipleandApplicationofDatabaseSystem 2 利用EXEC语句执行 DECLARE CubeLengthdecimal 4 1 CubeWidthdecimal 4 1 CubeHeightdecimal 4 1 CubeVolumedecimal 12 3 SET CubeLength 6SET CubeWidth 4SET CubeHeight 3EXEC CubeVolume dbo CubicVolume CubeLength CubeWidth CubeHeightSELECT CubeVolumeAS 长 宽 高分别为6 4 3长方体的体积为 PrincipleandApplicationofDatabaseSystem 内嵌表值函数可用于实现参数化视图的功能 例如 有如下视图 CREATEVIEWVIEW1ASSELECTsno snameFROMstudentWHEREclass 95031 2 内嵌表值函数 PrincipleandApplicationofDatabaseSystem 2 内嵌表值函数 PrincipleandApplicationofDatabaseSystem IFEXISTS SELECT FROMsysobjectsWHEREname fn view ANDtype IF DROPFUNCTIONfn viewGO PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONfn view parachar 5 RETURNSTABLEASRETURNSELECTsno snameFROMstudentWHEREclass para PrincipleandApplicationofDatabaseSystem 2 内嵌表值函数的调用 例5 调用fn view 函数 查询95033班学生的学号和姓名 SELECT FROMfn view 95033 PrincipleandApplicationofDatabaseSystem 3 多语句表值函数 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 例 在school数据库中创建返回table的函数student1 通过以班号为实参 调用该函数 查询95031班所有学生的考试成绩记录 包括学号 姓名 选修的课程号 课程名及成绩 PrincipleandApplicationofDatabaseSystem IFEXISTS SELECT FROMsysobjectsWHEREname student1 ANDtype TF DROPFUNCTIONstudent1GO PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONstudent1 classchar 5 RETURNS stTABLE snochar 5 snamechar 8 cnochar 10 cnamechar 16 degreenumeric 18 1 PrincipleandApplicationofDatabaseSystem ASBEGININSERTINTO stSELECTstudent sno sname o cname degreeFROMstudent score courseWHEREstudent sno score snoANDo oANDclass classRETURNEND PrincipleandApplicationofDatabaseSystem SELECT FROMstudent1 95031 PrincipleandApplicationofDatabaseSystem 3 利用企业管理器创建用户定义函数 PrincipleandApplicationofDatabaseSystem PrincipleandApplicationofDatabaseSystem 4 用户函数的删除 PrincipleandApplicationofDatabaseSystem 综合应用训练 对于CPXS数据库 定义完成如下功能的函数 然后对相应函数进行调用 1 根据产品名称 查询该产品的相关信息 2 按某年某季度统计给定产品名称的销售数量及销售金额 3 根据销售商名称 统计其在某年某季度内销售商品名称 数量及金额 PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONFU CP 产品名称char 10 RETURNSTABLEASRETURN SELECT FROMCPWHERE产品名称 产品名称 SELECT FROMFU CP mp3 1 PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONFU1 CPXS YEARINT QUARTERINT 产品名称char 10 RETURNSTABLEASRETURN SELECT产品名称 SUM 数量 AS销售数量 SUM 销售额 AS销售总额FROMCPXSB CPWHERECPXSB 产品编号 CP 产品编号AND产品名称 产品名称ANDDATEPART YY 销售日期 YEARANDDATEPART QQ 销售日期 QUARTERGROUPBY产品名称 2 内嵌表值函数 PrincipleandApplicationofDatabaseSystem SELECT FROMFU1 CPXS 2004 3 彩色电视机 SELECT FROMFU1 CPXS 2004 1 洗衣机 PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONFU2 CPXS YEARINT QUARTERINT 产品名称char 10 RETURNS 销售情况TABLE 产品名称char 10 销售数量int 销售金额float ASBEGININSERTINTO 销售情况SELECT产品名称 SUM 数量 AS销售数量 SUM 销售额 AS销售总额FROMCPXSB CPWHERECPXSB 产品编号 CP 产品编号AND产品名称 产品名称ANDDATEPART YY 销售日期 YEARANDDATEPART QQ 销售日期 QUARTERGROUPBY产品名称RETURNEND 2 多语句表值函数 PrincipleandApplicationofDatabaseSystem SELECT FROMFU2 CPXS 2004 3 彩色电视机 SELECT FROMFU2 CPXS 2004 1 洗衣机 PrincipleandApplicationofDatabaseSystem CREATEFUNCTIONFU3 CPXS 客户名称char 10 YEARINT QUARTERINT RETURNSTABLEASRETURN SELECT产品名称 SUM 数量 AS销售数量 SUM 销售额 AS销售总额FROMXSS CPXSB CPWHERECPXSB 产品编号 CP 产品编号ANDCPXSB 客户编号 XSS 客户编号AND客户名称 客户名称ANDDATEPART YY 销售日期 YEARANDDATEPART QQ 销售日期 QUARTERGROUPBY产品名称 3 PrincipleandApplicationofDatabaseSystem SELECT FROMFU3 CPXS 广电公司 2004 1 SELECT FROMFU3 CPXS 电器商场 2004 2