《数据库sql变量》PPT课件.ppt

第2章 Transact-SQL语言,2.1 数据定义语言（DDL） 2.2 数据操纵语言（DML） 2.3 数据控制语言（DCL） 2.4 系统存储过程 2.5 其它语言元素,上一章,返回目录,Transact-SQL语言主要组成部分,数据定义语言（DDL，Data Definition Language） 数据操纵语言（DML，Data Manipularion Language） 数据控制语言（DCL，Data Control Language） 系统存储过程（System Stored Procedure） 一些附加的语言元素,2.1 数据定义语言（DDL）,数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQL Server 2000中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。,程序：创建数据库表的一个例子,CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN (1389, 0736, 0877, 1622, 1756) OR pub_id LIKE 990-90-9), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT(USA) ),程序2：修改视图的一个例子,USE pubs GO ALTER VIEW yourview AS SELECT title, mycount= ROWCOUNT, ytd_sales FROM titles WHERE type = mod_cook GO SELECT * FROM yourview GO,程序3：删除触发器的一个例子,/*下面的例子将删除触发器employee_insupd。*/ USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = employee_insupd AND type = TR) DROP TRIGGER employee_insupd GO,2.2 数据操纵语言（DML）,数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成员才有权利执行数据操纵语言。,1、SELECT语句,SELECT语句的语法形式如下： SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC COMPUTE clause FOR BROWSE,程序4：显示居住在加利福尼亚州且姓名不为 McBadden 的作者列。,USE pubs SELECT au_fname, au_lname, phone AS Telephone FROM authors WHERE state = CA and au_lname McBadden ORDER BY au_lname ASC, au_fname ASC,程序5：使用两个相关子查询查找作者姓名，这些作者至少参与过一本受欢迎的计算机书籍的创作。,USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = popular_comp),程序7：先删除表T1，然后又创建一个带有四个列的表T1。最后利用INSERT 语句插入一些数据行，这些行只有部分列包含值。,IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = T1) DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int identity, column_2 varchar(30) CONSTRAINT default_name DEFAULT (column default),column_3 int NULL, column_4 varchar(40) ) INSERT INTO T1 (column_4) VALUES (Explicit value) INSERT INTO T1 (column_2,column_4) VALUES (Explicit value, Explicit value) INSERT INTO T1 (column_2,column_3,column_4) VALUES (Explicit value,-44,Explicit value) SELECT * FROM T1,程序清单2-8：一个简单的修改语句。,UPDATE publishers SET city = Atlanta, state = GA,程序清单2-9：一个带有WHERE条件的修改语句。,UPDATE authors SET state = PC, city = Bay City WHERE state = CA AND city = Oakland,2.4 系统存储过程,系统存储过程是SQL Server系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。系统存储过程可以在任意一个数据库中执行。系统存储过程创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。,系统存储过程的部分示例如下：,sp_addtype：用于定义一个用户定义数据类型。 sp_configure：用于管理服务器配置选项设置。 xp_sendmail：用于发送电子邮件或寻呼信息。 sp_stored_procedures：用于返回当前数据库中的存储过程的清单。,sp_help：用于显示参数清单和其数据类型。 sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象。 sp_helptext：用于显示存储过程的定义文本。 sp_rename：用于修改当前数据库中用户对象的名称。,2.5 其它语言元素,2.5.1 注释 2.5.2 变量 2.5.3 运算符 2.5.4 函数 2.5.5 流程控制语句,2.5.1 注释,注释是程序代码中不执行的文本字符串（也称为注解）。在SQL Server中，可以使用两种类型的注释字符：一种是ANSI标准的注释符“-”，它用于单行注释；另一种是与C语言相同的程序注释符号，即“/* */”。,2.5.2 变量,变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。,1. 局部变量,局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用。,2. 全局变量,全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。,使用全局变量时应该注意以下几点：,全局变量不是由用户的程序定义的，它们是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符“”开头。 局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,2.5.3 运算符,运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQL Server 2000中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串串联运算符。,1算术运算符,算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）。,2赋值运算符,Transact-SQL 中只有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。,3位运算符,位运算符使我们能够在整型数据或者二进制数据（image 数据类型除外）之间执行位操作。此外，在位运算符左右两侧的操作数不能同时是二进制数据。,表2-1 位运算符,4比较运算符,比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及UNKNOWN。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。,5逻辑运算符,逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。逻辑运算符和比较运算符一样，返回带有 TRUE 或 FALSE 值的布尔数据类型。,6字符串串联运算符,字符串串联运算符允许通过加号 (+) 进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT abc+def，其结果为abcdef。,运算符的优先等级从高到低如下所示,括号：（）； 乘、除、求模运算符：*、/、%； 加减运算符：+、- ； 比较运算符：=、=、!=、!、!； 位运算符：、&、|； 逻辑运算符：NOT； 逻辑运算符：AND； 逻辑运算符：OR。,2.5.4 函数,在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQL Server的标准命令。Transact-SQL 编程语言提供了三种函数： 行集函数：行集函数可以在Transact-SQL语句中当作表引用。 聚合函数：聚合函数用于对一组值执行计算并返回一个单一的值。 标量函数：标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。,2日期和时间函数,日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。在SQL Server 2000中，日期和时间函数的类型如表2-5所示；另外，表2-6列出了日期类型的名称、缩写形式以及可接受的值。,表2-5 日期和时间函数的类型,程序33：从GETDATE函数返回的日期中 提取月份数,SELECT DATEPART(month, GETDATE() AS Month Number 运行结果为： Month Number - 2,程序4：从日期 03/12/1998 中返回月份数、天数和年份数。,SELECT MONTH(03/12/1998), DAY(03/12/1998),YEAR(03/12/1998) 运行结果为： - - - 3 12 1998,3数学函数,数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。,程序清单2-35：,在同一表达式中使用CEILING（）、FLOOR（）、ROUND（）函数。 select ceiling(13.4), floor(13.4), round(13.4567,3) 运行结果为： - - - 14 13 13.4570,4转换函数,一般情况下，SQL Server会自动处理某些数据类型的转换。例如，如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式，SQL Server 可以将它们自动转换，这种转换被称为隐性转换。但是，无法由SQL Server自动转换的或者是SQL Server自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数有两个：CONVERT和CAST。,5系统函数,系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。,程序清单2-38,返回 Northwind 数据库的 Employees 表中的首列的名称。 USE Northwind SELECT COL_NAME(OBJECT_ID(Employees), 1) 运行结果为： EmployeeID,2.5.5 流程控制语句,流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。,1 IFELSE语句,IFELSE语句是条件判断语句，其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IFELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。SQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。,IFELSE语句的语法形式,IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block ,2BEGINEND语句,BEGINEND语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句，其语法形式为： BEGIN sql_statement | statement_block END,3CASE函数,CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE函数按照使用形式的不同，可以分为简单CASE函数和搜索CASE函数。,1、CASE函数的语法形式,CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END,2、搜索 CASE函数的语法形式,CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END,4 WHILECONTINUEBREAK语句,WHILECONTINUEBREAK语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。,结束WHILE语句的语法形式为：,WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE ,5 GOTO语句,GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。,GOTO语句的语法形式,GOTO label label: 程序清单2-50：利用GOTO语句求出从1加到5的总和。 declare sum int, count int select sum=0, count=1 label_1: select sum=sum+count select count=count+1 if count=5 goto label_1 select count sum,6 WAITFOR语句,WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。WAITFOR语句的语法形式为： WAITFOR DELAY time | TIME time 其中，DELAY用于指定时间间隔，TIME用于指定某一时刻，其数据类型为datetime，格式为hh:mm:ss。,7 RETURN语句,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为： RETURN integer_expression 其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。,谢谢您的光临！,Thank you very much!,下一章,