数据库基础第2章SQL语言编程基础.ppt

第 2 章 SQL语言编程基础,本章学习目标,l了解Transact-SQL语言起源、组成 l掌握Transact-SQL编程基础知识 l 综合运用变量、表达式、函数及流控语句等编写应用程序代码,2.1 Transact-SQL语言概论 2.1.1 Transact-SQL语言简介 SQL的含义为结构化查询语言，即Structured Query Language，是在关系型数据库系统中被广泛采用的一种语言形式。SQL语言能够针对数据库完成定义、查询、操纵和控制功能，是关系型数据库领域中的标准化查询语言。微软公司在SQL语言的基础上对其进行了大幅度的扩充，并将其应用于SQL Server服务器技术中，从而将SQL Server所采用的SQL语言称为Transact-SQL语言。目前SQL语言的最新标准为SQL-92，由美国国家标准局制定，包含了语法标准以及对SQL关键字的定义。 在使用SQL语言的过程中，用户不需要知道数据库中的数据是如何定义和怎样存储的，只需要知道表和列的名字，即可从表中查询出需要的信息。 SQL语言特别适合于Client/Server体系结构，客户用SQL语句发出请求，服务器处理用户发出的请求，客户与服务器之间任务划分明确。但SQL语言本身不是独立的程序设计语言，不能进行屏幕界面设计和控制打印等，因此通常将SQL语言嵌入到程序设计语言（如Visual Basic、C语言、Delphi等）中使用。,SQL语言由三部分组成：数据定义语言（Data Definition Language，简称DDL）、 数据操纵语言（Data Manipularion Language，简称DML）、 数据控制语言（Data Control Language，简称DCL）。对上述三种语言的描述如表2-1所示。 表2-1 SQL语言的组成,2.1.2 Transact-SQL语法规则 1Transact-SQL语法规则 为方便用户更好地掌握与使用Transact-SQL语言，需要首先向读者介绍Transact-SQL中涉及到的语法规则，如表2-2所示：,表2-2 Transact-SQL的语法规则,2数据库对象的引用规则 一般情况下，数据库对象的引用都由以下四部分所组成: l server_name 用于指定所连结的本地服务器或远程服务器的名称。 l database_name 用于确定在服务器中当前状态下所操作的数据库名称 l object_name 在数据库中被引用的数据库对象名称 l owner_name 表示数据库对象的所有者,所以一个完整的数据对象引用的表示方法应该为：server_name.database_name.owner_name.object_name，其中，服务器名称、数据库名称以及所有者都可以省略，所以下列对象的表示方法都是合法的：server_name.database_name.owner_name.object_nameserver_name.database_name.object_nameserver_name. owner_name.object_nameserver_name.object_namedatabase_name.owner_name.object_namedatabase_name.object_nameowner_name.object_nameobject_name例如：shutupandcode.xscj.dbo.班级表、shutupandcode.xscj.dbo.课程信息表。2.1.3 Transact-SQL语法元素在大部分Transact-SQL语句中，都包含诸如标识符、数据类型、函数、表达式、运算符、注释以及保留关键字等语法元素。,1 识符 用于标识数据库对象的名称，这些对象包括服务器、数据库及相关对象（如表、视图、列、索引、触发器、过程、约束、规则等）。标识符在定义对象时进行命名，当需要使用某个对象时可以通过引用该对象的名称来完成。 标识符可划分为常规标识符与分隔标识符两类，其中常规标识符的命名规则如下： l 第一个字符必须由字母a-z 、A-Z，以及来自其他语言的字母字符或者下划线_、#构成，其中表示局部变量或参数，以#开头的标识符表示全局临时对象，以开始的标识符表示全局变量，也称为配置函数。 l 在定义标识符时，不能占用Transact-SQL 的保留字，例如不能将Table、View、Index等定义为一个标识符。 l 在标识符中不能含有空格，并且标识符中的字符数量不能超过128个。 如果定义的标识符不符合上述规则时，即被称为分隔标识符，需要使用双引号”或方括号对其进行分割。例如：SELECT * FROM my table,1 数据类型 Transact-SQL中的基本数据类型如表2-3表所示： 表2-3 Transact-SQL基本数据类型,（1）整型数据整数数据类型是最常用的数据类型之一，由正整数和负整数所组成，使用 bigint、int、smallint和tinyint数据类型进行存储。bigint 数据类型可存储的数字范围比 int 数据类型广。int 数据类型比 smallint 数据类型的存储范围大，而 smallint 的数值范围又比 tinyint 类型大。,l bigint：可以存储-263到263-1之间的数字，占据8个字节存储空间。 l int：可以存储从-231到231-1范围之间的所有整数，占据4字节存储空间。 l smallint：可以存储从-215到215-1范围之间的所有整数，占据2字节存储空间。 l tinyint：可以存储从0到255范围之间的所有正整数。,（2）浮点数据类型主要包括Real、Float、Decimal和numeric四种类型。,l Real：用于存储7位小数的十进制数据，所能够表示的范围为-3.40E+38到1.79E+38。 l Float：可以精确到第15位小数，数据范围为-1.79E-308到1.79E+308。 l Decimal：提供小数所需要的实际存储空间，可以存储2到17个字节的从-1038-1到1038-1之间的数值。 l numeric：与Decimal数据类型几乎完全相同，区别是在表格中，只有numeric型的数据可以带有identity关键字的列。,（3）字符数据类型SQL Server提供了三种字符数据类型，分别是Char、Varchar和Text。,l Char：最长可以容纳8000个字符，并且每个字符占用一个字节的存储空间。使用Char数据类型定义变量时，需要指定数据的最大长度。如果实际数据的字符长度小于指定长度时，剩余的字节用空格来填充。如果实际数据的长度超过了指定的长度，则超出部分将会被删除。在表示字符串常量时，需要使用一对单引号将其括起来。 l varchar：该数据类型的使用方式与Char数据类型类似。Char 数据类型不同的是，Varchar数据类型所占用的存储空间由字符数据所占据的实际长度来确定。 l text：该数据类型所能表示的最大长度为 231 - 1 即2,147,483,647个字符，当需要表示的数据类型长度超过了8000时，可以采用text来处理可变长度的字符数据。,（4）日期/时间数据类型日期/时间数据类型可以分为datetime和smalldatetime两类。,l Datetime：范围从1753年1月1日到9999年12月31日，可以精确到千分之一秒，此类型的数据占用8个字节的存储空间。 l Smalldatetime：数据范围从1900年1月1日到2079年6月6日，可以精确到分，此类型的数据占4个字节的存储空间。,（5）货币数据类型SQL Server提供了Money和Smallmoney两种货币数据类型。,l Money：占据8字节存储空间。每4字节分别用于表示货币值的整数部分及小数部分。Money的取值的范围为-263到263-1，并且可以精确到万分之一货币单位。 l Smallmoney：占据4字节存储空间。每2字节分别用于表示货币值的整数部分以及小数部分。smallmoney的取值范围为-214,748.3648到+214,748.3647，可以精确到万分之一货币单位。,（6）二进制数据类型用于存储二进制数据，有binary、varbinary两种。,l Binary:用于存储固定长度的二进制数据，表示数据的长度取值为1到8000个字节。在输入数据时必须在数据前加上字符0X作为二进制标识。如要输入abc则应输入0 xabc。若输入的数据过长将会截掉其超出部分，若输入的数据位数为奇数则会在起始符号0X后添加一个0。如上述的0 xabc会被系统自动变为0 x0abc。 l Varbinary: 具有可变长度的特性，表示数据的长度也为1到8000个字节，若输入的数据过长将会截掉其超出部分。当binary数据类型允许NULL值时将被视为varbinary数据类型。,（7）逻辑数据类型 bit数据类型占用1个字节的存储空间，其值为0或1，如果输入0或1以外的值将被视为1。bit类型不能定义为NULL值（所谓NULL值是指空值或无意义的值）。（8）文本和图形数据类型这类数据类型用于存储大量的字符或二进制数据。,l Text：用于存储大量文本数据，其容量理论上为1-231-1（2,147, 483, 647）个字节，在实际应用时需要视硬盘的存储空间而定。 l ntext：与text类型相似，不同的是ntext类型采用Unicode标准字符集(Character Set), 因此其理论容量为230-1(1,073,741,823)个字节。 l image:用于存储大量的二进制数据。其理论容量为231-1(2,147,483,647)个字节。通常用来存储图形等（OLE Object Linking and Embedding，对象连接和嵌入）对象。在输入数据时同binary数据类型一样，必须在数据前加上字符“0X”作为二进制标识。,（9）特定数据类型SQLServer提供的特殊数据类型有Timestamp、 Uniqueidentifier2种。,l Timestamp：用于表示SQL Server 活动的先后顺序，以二进投影的格式表示。Timestamp 数据与插入数据或者日期和时间没有关系。 l Uniqueidentifier：由 16 字节的十六进制数字组成，此数字称为GUID（GloballyUniqueIdentifier即全球惟一鉴别号）。此数字由SQLServer的NEWID函数产生全球惟一的编码。在全球各地的计算机经由此函数产生的数字不会相同。,（10）用户定义的数据类型 用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。例如，可定义一种称为postal_code 的数据类型，它基于 Char 数据类型。当创建用户定义的数据类型时，必须提供三个数：数据类型的名称、所基于的系统数据类型和数据类型的可空性。（11）新数据类型 SQLServer2000中增加了3种数据类型bigint、SQL_variant和table。其中bigint数据类型已在整数类型中介绍。,l SQL_variant：可以存储除文本图形数据text、ntext、image、timestamp类型数据外其它任何合法的SQLServer数据。此数据类型大大方便了SQLServer的开发工作。 l Table:用于存储对表或视图处理后的结果集。这一新类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便快。,3、函数在SQL Server中包含了大量具有执行特定运算功能的内置函数。函数的使用可以包含在SELECT查询语句的选择列表中、WHERE子句搜索条件中、视图的搜索条件中、约束或触发器中。一般情况下，无论函数是否有参数，在调用函数时，函数名称后都需要带有圆括号()。有时，用来指定数据库、计算机、登录或数据库用户的参数是可选的，如果未指定这些参数，就默认地将这些参数赋值为当前的数据库、主机、登录或数据库用户。同时，SQL Server中的函数可以支持嵌套。SQL Server中函数的种类如表2-4所示：,表2-4 SQL Serverr的函数类型,2.2 函数、变量与表达式2.2.1 常量 常量可以用来表示特定的数值，根据不同的数据类型，常量的格式也会发生相应的变化。常量可以分为：字符串常量、二进制常量、bit常量、datetime常量、integer常量、decimal常量、float和real常量、uniqueidentifier常量等。,1字符串常量 字符串常量需要括在一对单引号中，其中包含字母数字（a-z、A-Z、0-9）以及其他特殊字符，如!、#。如果在字符串中含有一个嵌入的引号时，为了避免发生混淆，用两个单引号代替嵌入的单引号。 以下字符串均为合法的常量：XJNZY、abc、efg。,2数值常量,l 二进制常量 其前缀为OX，并且由16进制数字所组成。例如，0 xAE、0 x12Ef。 l bit常量 该常量的取值为0或1，如果bit常量的值大于1则自动被转换为1。 l integer常量 用于表示 l decimal常量 浮点数类型的常量，可以包含小数点。 lfloat和real常量 采用科学计数法来表示浮点数。例如101.5E5、0.5E-2等。 l 指定正数和负数 在数字前面添加+或-，指明一个数是正数或是负数。如+14562、-￥87.56、+324E-5等。money常量 货币常量，以$作为前缀，可以包含小数点。如$16.34、$134.5等。,3uniqueidentifier 常量 该类常量用于产生一个全局唯一标识符 (GUID) 值的字符串。可用于数据库中的某一个关键字字段。其格式可以指定为字符或二进制字符串。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF。4datetime常量 用于表示特定的日期和时间。在SQL Server中能够识别的日期格式包括，字母日期格式、数字日期格式以及未分隔的字符串格式，例如：April 15,2005、01/15/2006、20051220。时间常量可以参照如下示例，如：14:30:24、04:24 PM。 2.2.2 变量变量对应内存中的一个存储空间，它与常量不同，变量的值在程序运行过程中可以随时改变。有局部变量与全局变量两种。变量采用先申明后使用的原则。1局部变量局部变量是用户在程序中定义的变量，一次只能保存一个值，它仅在定义的程序范围内有效。局部变量可以临时存储数值。局部变量名以符号开始，最长为128个字符。,l 申明局部变量 可以通过DECLARE语句声明局部变量，需要确定变量的名字、数据类型和长度。例如，定义一个整数类型的变量score以及字符串类型的变量name，可以表示为： DECLARE score INT DECLARE name CHAR(12) l 对局部变量赋值 局部变量的初值为NULL（空），可以使用SELECT语句或SET语句对变量进行赋值。SET语句一次只能给一个局部变量赋值，SELECT语句则可以同时给一个或多个变量赋值。,局部变量通常用于以下几种情况： 作为计数器计算循环执行的次数或控制循环执行的次数。 保存数据值以供流控语句测试。 保存由存储过程返回代码的数据值。 在使用变量之前应该考虑到变量的作用域，只有在变量的作用范围以内才能够正确的对变量进行操作。变量的作用域指从申明变量的开始位置到含有该变量的批处理或存储过程的结束位置。 【例2-1】在数据库XSCJ中定义一个长度为12的字符串类型变量student，对该变量进行赋值，并且查询出“学生基本信息表“中该变量所指定姓名的学生信息。 在查询分析器中运行如下命令： USE XSCJ GO DECLARE student CHAR(12) SET student = 王倩倩 SELECT * FROM 学生基本信息表 WHERE 姓名 = student 运行结果如图2-1所示。,图2-1定义一个长度为12的字符串类型变量,【例2-2】在数据库XSCJ中定义两个日期时间类型的变量max_csrq、min_csrq，分别用于查询“学生基本信息表”中“出生日期”的最大值、最小值。 可在查询分析器中运行如下命令： USE XSCJ GO DECLARE max_csrq DATETIME , min_csrq DATETIME SELECT max_csrq=MAX(出生日期),min_csrq=MIN(出生日期) FROM 学生基本信息表 PRINT max_csrq PRINT min_csrq 运行结果如图2-2所示：,图2-2定义日期时间类型变量运行结果,2 全局变量 全局变量以开头，实际上它是SQL Server的配置函数。 2.2.3 表达式 在Transact-SQL语言中，表达式由标识赋、数值、运算符组成。可以对表达式进行计算并且得到结果。表达式可以是一个常量、变量、字段名、函数或子查询。可以通过运算符将两个或更多的简单表达式联接起来组成复杂的表达式。 表达式中的运算符可以执行算术、比较、连接或赋值操作。运算符大致可以划分为：算术运算符、位运算符、逻辑运算符、比较运算符、字符串连接运算符等。 常用的算术运算符有+（加）、（减）、*（乘）、/（除）、%（求余）等；常用的比较运算符有=（等于）、=（大于等于）、（不等于）或!=（不等于）等；常用的位运行符有&（按位与）、|（按位或）、（按位异或）等；常用的逻辑运算符有AND（逻辑与）、NOT（逻辑非）、OR（逻辑或）等。 2.2.4 常用函数 在SQL Server 2000中提供了大量的系统函数，通过使用这些函数，用户可以根据需要完成特定的操作。常用的系统函数有：聚合函数、数学函数、字符串函数、日期时间函数、数据类型转换函数等。,1聚合函数 聚合函数也称为统计函数，它对一组值进行计算并返回一个数值。聚合函数经常与SELECT语句一起使用。常用聚合函数如表2-5所示。 表2-5 SQL Server聚合函数及其功能,【例2-3】计算XSCJ数据库中“学生基本信息表”的总行数。 在查询分析器中运行如下命令： USE XSCJ GO SELECT COUNT(*) AS 学生总人数 FROM 学生基本信息表 GO 运行结果如图2-3所示。,表2-3 计算学生基本信息表的总人数,【例2-4】计算XSCJ数据库中课程编号为003的课程总成绩和平均成绩。 在查询分析器中运行如下命令： USE XSCJ GO SELECT SUM(成绩) AS 课程总分,AVG(成绩) AS 课程平均分 FROM 成绩表 WHERE 课程编号=003 GO 运行结果如图2-4所示。,图2-4 计算课程编号为003的课程总分和平均分,【例2-5】在成绩表中查询课程编号为003的课程的最高分和最低分。在查询分析器中运行如下命令：USE XSCJGOSELECT MAX(成绩) AS 最高分,MIN(成绩) AS 最低分FROM 成绩表WHERE 课程编号=003GO运行结果如图2-5所示。,图2-5 查询课程编号为003的课程最高分和最低分,2数学函数 数学函数用来对数值型数据进行数学运算。常用数学函数如表2-6所示。 表2-6 常用数学函数,【例2-6】分别输出2的3次幂、-1的绝对值、2的平方、3.14的整数部分。 在查询分析器中运行如下命令： PRINT POWER(2,3) PRINT ABS(-1) PRINT SQUARE(2) PRINT FLOOR(3.14) GO 运行结果为：8、1、4、3。 3字符串函数 字符串函数可以对char、nchar、varchar、nvchar等类型的参数执行操作，并返回相应的结果，返回值一般为字符串或数字。SQL Server 2000中所包含的字符串函数如表2-7所示。 表2-7 常用字符串函数,【例2-7】使用LEN函数显示字符串常量以及字符串变量的长度。 提示：LEN函数用于计算字符串中所包含的字符个数，如果字符串尾部含有空格则会被忽略。 在查询分析器中运行如下命令： PRINT LEN(computer department),DECLARE s1 char(10) DECLARE s2 char(10) SET s1 = welcome SET s2 = hellow PRINT LEN(s1) PRINT LEN(s2) 运行结果为：19，7，6。 【例2-8】给定一个字符串have a good time，判断字符g在整个字符串中的位置。 提示：CHARINDEX函数用于在规定字符串中对子字符串进行查询。当返回值大于零时表示子字符串的起始位置，返回值为0时表明没有查询结果。 在查询分析器中运行如下命令： DECLARE s CHAR(20) SET s =have a good time PRINT CHARINDEX(g,s) 运行结果为：8,4日期时间函数 日期时间函数可以对日期时间类型的参数进行运算、处理，并返回一个字符串、数字或日期和时间类型的值。SQL Server 2000中提供的日期时间函数如表2-7所示。 表2-7常用日期时间函数,【例2-9】获取系统时间信息，在查询分析器中分别显示系统时间中的年份、月份以及日期。 提示：GETDATE函数用于返回当前的系统时间，YEAR，MONTH，DAY函数可以取得时间中的年、月、日的数值。,在查询分析器中运行如下命令： DECLARE xtsj DATETIME SET xtsj = GETDATE() SELECT YEAR(xtsj) SELECT MONTH(xtsj) SELECT DAY(xtsj) 运行结果如图2-6所示。,图2-6 计算时期时间信息,【例2-10】通过对“学生基本信息表”中的“出生日期”字段进行计算，查询每一位学生的年龄。 提示：利用DATEDIFF函数可以计算出两个日期之间的距离，该函数含有三个参数，第一个参数通常可以为yy（年）或mm（月）或dd（日），若第一个参数为yy时，该函数返回值为后两个日期参数之间年份的差距。在本例中，当前的日期由GETDATE函数获得后，计算与每一位学生的出生日期之间年份的差距，从而获得学生的年龄并在查询结果中显示。 在查询分析器中运行如下命令： SELECT 学号,姓名,DATEDIFF(yy,出生日期,GETDATE() from 学生基本信息表 运行结果如图2-7所示。,图2-7查询每一位学生的年龄,5数据类型转换函数 在不同的数据类型之间进行运算时，需要将其转换为相同的数据类型。在SQL Server中，某些数据类型可以由系统自动完成转换，当系统不能够自动执行不同类型表达式的转换时，可以通过CAST和CONVERT函数对数据进行转换。CAST和CONVERT函数的语法格式为：CAST (表达式 AS 数据类型及长度)CONVERT (数据类型及长度 , 表达式 , 日期/字符串格式样式 , )其中，日期/字符串格式样式为可选，用于确定将参数转化为字符串之后后的样式。包括将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、charvarchar、nchar 或 nvarchar 数据类型）。,【例2-11】查询“学生基本信息表”中的学号、姓名、年龄，并且将这三个字段通过“+”运算符进行连结显示在查询结果中。 提示：由于计算学生年龄的结果为整数，而学号、姓名均为字符串类型的值，因而在运算之前,需要将年龄的计算结果转化为字符串，即CAST(DATEDIFF(yy , 出生日期 , GETDATE() AS CHAR(2)。 在查询分析器中运行如下命令： SELECT 学号+姓名+年龄：+CAST(DATEDIFF(yy,出生日期,GETDATE() AS CHAR(2) FROM 学生基本信息表 运行结果如图2-8所示。,图2-8 查询“学生基本信息表”中的学号、姓名、年龄,【例2-12】将常量3.14分别转换为整数类型以及字符串类型并且输出结果。在查询分析器中运行如下命令：PRINT CONVERT(INTEGER,3.14)PRINT CONVERT(CHAR(4),3.14)结果显示为：3、3.14。【例2-13】取得系统当前时间，并且将其转化为mm/dd/yyyy格式的字符串并且显示结果。提示；由于mm/dd/yyyy格式所对应的参数为101，所以在使用CONVERT函数时，日期格式样式参数设定为101。在查询分析器中运行如下命令：DECLARE xtsj DATETIMESET xtsj = GETDATE()PRINT CONVERT(CHAR(50),xtsj,101)运行结果如图2-9所示。,图2-9 取得系统当前时间，并将其转化,6系统函数 系统函数可以使得用户在不直接访问系统表的情况下就可获得系统表的信息。 针对于数据库、主机、对象、登录以及用户的系统函数包括：,l DB_ID 、DB_NAME 分别返回当前数据库的编号以及名称 l HOST_ID 、HOST_NAME 分别返回主机编号以及名称 l OBJECT_ID 、OBJECT_NAME 分别返回对象的编号以及名称 l SUSER_ID 、SUSER_NAME 分别返回SQL Server 身份验证模式下，当前登录用户的编号以及名称 l USER_ID 、USER_NAME 返回系统用户编号、名称，通常该用户为dbo 例如：SELECT DB_ID()表示调用DB_ID 函数来获得当前数据库在系统中的编号。 与字段有关的系统函数包括： l COL_NAME 返回列名。 l COL_LENGTH 返回列长度。 l INDEX_COL 返回索引列名称。 例如：SELECT COL_LENGTH(课程信息表,课程名称)可以取得“课程信息表” 中“课程名称”字段的长度。,2.3 系统存储过程 2.3.1 系统存储过程概述 存储过程是一组编译在单个执行计划中的Transact-SQL语句，分为系统存储过程和用户自定义存储过程两种。SQL Server包含了多种多样的系统存储过程，用户可以通过调用这些存储过程对系统进行管理以及对数据信息进行操作。系统存储过程以“sp_”开头，并且保存在Master数据库中，部分存储过程只能由系统管理员进行调用，普通用户可以经过授权后使用其它的系统存储过程。 2.3.2 常用系统存储过程 在SQL Server中，常用的系统存储过程有sp_helpdb、sp_renamedb、sp_helpuser、sp_addlogin等。 1sp_helpdb 该存储过程可以查看数据库信息，语法格式为： sp_helpdb 数据库名称 查询的结果包括：数据库的逻辑名称、容量、所有者、创建时间、状态，数据文件的逻辑名称、标识符、物理名称、文件所属组、文件的大小、文件最大值以及增量等。如果不指定数据库名称，则返回master.dbo.sysdatabases 中的所有数据库信息。,【例2-14】显示XSCJ数据库信息。 在查询分析器中运行如下命令： sp_helpdb XSCJ 运行结果如图2-10所示。,图2-10 显示XSCJ数据库信息,2sp_renamedb 该存储过程用于修改数据库名称。语法格式为： sp_renamedb 原名称,新名称 修改后的名称也需要遵循标识赋的规则。该存储过程只能由系统管理员以及数据库创建者调用。当返回值为0表示修改成功，为1表示操作失败。 【例2-15】创建一个名称为somename的数据库，利用sp_renamedb存储过程将该数据库重命名为newname。 在查询分析器中运行如下命令： CREATE DATABASE somename GO sp_renamedb somename,newname GO 运行结果如图2-11所示。,图2-11 利用sp_renamedb存储过程将数据库重命名,3sp_helpuser 用于显示系统内部的用户信息，语法格式为： sp_helpuser 用户名称 不指定用户名称时显示所有用户。 【例2-16】显示超级用户dbo的用户信息。 在查询分析器中运行如下命令： sp_helpuser dbo 运行结果如图2-12所示。,图2-12 显示超级用户dbo的用户信息,4sp_addlogin 用于在系统中增加新的登录，语法格式为： sp_addlogin 登录名 ,密码 ,登录的默认数据库 ,登录时的默认语言 ,标识号 ,是否加密。 【例2-17】在系统中添加一个名为test、密码为1234的登录帐号。 在查询分析器中运行如下命令： sp_addlogin test,1234 运行结果如图2-13所示。,图2-13 在系统中添加一个名为test、密码为1234的登录帐号,5sp_adduser 可以在当前数据库中添加用户，语法格式为： sp_adduser 登录名 用户名称 组 登录名必须是已经存在的登录，如果不指定用户名称，则默认为登录名。组为当前数据库中有效的组或角色。注意：在添加用户之前首先需要添加相应的登录。 【例2-18】在SQL Server中添加一个名称为test的用户。 在查询分析器中运行如下命令： sp_addlogin test GO sp_adduser test GO 运行结果如图2-14所示。,图2-14 在SQL Server中添加一个名称为test的用户,6sp_dropuser 可以从当前数据库中删除SQL-Server中已经存在的用户。其语法格式为： sp_dropuser 用户名 用户名的数据类型为sysname。可以利用sp_helpuser显示用户列表，并删除该列表包含的用户名。删除某一个用户的条件为该用户不拥有任何对象，因此在删除用户之前需要使用sp_changeobjectowner将对象的所属关系进行更改。只有 sysadmin 固定服务器角色、db_owner 或 db_accessadmin 固定数据库角色的成员才能执行该存储过程。 【例2-19】删除名称为test的用户。 在查询分析器中运行如下命令： sp_dropuser test 运行结果如图2-15所示。,图2-15 删除名称为test的用户,7sp_addrole 用于在当前数据库中建立新的角色，语法格式为： sp_addrole 角色名称 角色的所有者 其中：角色名称必须符合有效的标识符命名规则，而且不能是数据库中已有的角色。增加新的角色之后，可以使用sp_addrolemember在角色中添加成员。sysadmin 固定服务器角色及 db_securityadmin 和 db_owner 固定数据库角色的成员才能执行该存储过程。,【例2-20】添加一个名为myrole的角色，并将test用户加入该角色中。 在查询分析器中运行如下命令： sp_addrole myrole GO sp_addmember myrole , test GO 运行结果如图2-16所示。,图2-16 添加一个名为myrole的角色，并将test用户加入该角色中,8、sp_droprole 对于不需要的角色，可以使用该存储过程将其从数据库中删除。语法格式为： sp_droprole 角色名称。 【例2-21】删除已有的角色myrole。 在查询分析器中运行如下命令： sp_droprole myrole 运行结果如图2-17所示。,图2-17 删除已有的角色myrole,2.4 批处理与流控语句 2.4.1 批处理 批处理是多条Transact-SQL语句构成的集合。SQL Server将批处理语句进行编译形成一个可执行单元，称为执行计划。执行计划中的语句一次执行每一条语句，如果在编译过程中出现语法错误，那么批处理中所有的语句均无法正常执行。如果在运行阶段出现错误时，一般都会中断当前以及其后语句的执行，只有在少数情况下，如违反约束时，仅中断当前出错的语句而继续执行其他语句。 如果在事务中含有批处理语句，那么在运行阶段一旦出现错误时，都将会对已经执行的操作结果进行回滚。假定在事务中包含10条语句，那么如果第五条语句出现错误，则不会执行后续的语句，并且取消之前完成的操作。,在建立批处理时，应该遵循以下规则： l 不能在批处理中引用其他批处理中所定义的变量。 l CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其它语句组合使用。 l 不允许在一个批处理中更改表结构、并引用新的字段。 l 如果 EXECUTE 语句是批处理中的第一句，则不需要 EXECUTE 关键字。如果 EXECUTE 语句不是批处理中的第一条语句，则需要 EXECUTE 关键字。 l 一个完整的批处理需要使用GO语句作为结束标记。,【例2-22】执行批处理程序，依次查询系部表、系部总数、班级表、班级总数。 USE XSCJ GO SELECT * FROM 系部表 SELECT COUNT(*) FROM 系部表 SELECT * FROM 班级表 SELECT COUNT(*) FROM 班级表 GO 运行结果如图2-18所示。,图2-18 执行批处理程序结果,2.4.2 流控语句 流控制语句采用了与程序设计语言相似的机制，使其能够产生控制程序执行及流程分支的作用。通过使用流程控制语句，用户可以完成功能较为复杂的操作，并且使得程序获得更好的逻辑性和结构性。 1BEGIN.END语句 用于将一系列的SQL语句合并为一组语句，当需要同时执行两条以上的语句时，可以使用BEGINEND语句将这些语句包含在内形成一个语句块，作为一个整体来执行。通常该语句可以嵌套在其他语句中，如条件分支语句、循环语句中。 BEGINEND语法格式为： BEGIN 语句1 语句2 语句n END,2IFELSE语句 该语句用于设计条件分支流程，根据给定的条件，程序可以执行不同的操作和运算，从而使程序的功能更加完善。该语句的格式为： IF 条件表达式 语句块1 ELSE 语句块2 其中：条件表达式为一个布尔表达式，结果为真或假。当条件成立时，执行语句块1，否则执行语句块2。当需要判断多个条件时，可以对IF.ELSE进行嵌套。 【例2-23】在“学生基本信息表”中查询班级编号为20041001的班级中是否有党员。要求：如果有党员则显示党员的人数，否则，提示该班级没有党员。,在查询分析器中运行如下命令： USE XSCJ GO IF (SELECT COUNT(*) FROM 学生基本信息表 WHERE 班级编号 = 20041001 AND 政治面貌 = 党员) 0 BEGIN DECLARE dy INTEGER SELECT dy = COUNT(*) FROM 学生基本信息表 WHERE 班级编号 = 20041001 AND 政治面貌 = 党员 PRINT 党员的人数为： PRINT dy END ELSE PRINT 该班级没有党员,上述程序中利用COUNT函数计算出满足条件的学生人数，并且根据人数进行判断，当人数大于零时，将人数赋值给dy变量并输出结果，程序运行结果如图2-19所示。,图2-19 在“学生基本信息表”中查询班级编号为20041001的班级中是否有党员,3CASE语句 CASE语句用于计算多个条件并为每个条件返回单个值，以简化SQL语句格式。CASE语句不同于其他SQL语句，不能作为独立的语句来执行，而是需要作为其他语句的一部分来执行。 CASE语句有两种格式：简单CASE表达式以及搜索CASE表达式。 （1）简单CASE表达式 语法格式为： CASE 表达式 WHEN 条件1 THEN 结果表达式1 WHEN 条件2 THEN 结果表达式2 ELSE 结果表达式n END 在简单表达式中，将表达式与每一个条件依次进行比较，如果遇到表达式与条件相匹配时，停止比较，并且返回满足条件的WHEN子句所对应的结果表达式。如果表达式与所有的条件都不匹配时，则返回ELSE子句中的结果表达式，如果不存在ELSE子句，则返回NULL值。 如果表达式与多个条件匹配时，CASE函数返回第一次满足条件时的WHEN子句所对应的结果表达式。,【例2-24】根据系统时间判断当前日期所对应的星期值并且输出结果。 在查询分析器中运行如下命令： DECLARE dt DATETIME SET dt = DATEPART(w,GETDATE() SELECT CASE dt WHEN 1 THEN 星期天 WHEN 2 THEN 星期一 WHEN 3 THEN 星期二 WHEN 4 THEN 星期三 WHEN 5 THEN 星期四 WHEN 6 THEN 星期五 WHEN 7 THEN 星期六 END 该实例中，首先通过DATEPART函数获得当前时间所对应的星期数，范围为1至7，其中1代表星期天，7代表星期六，构造一个条件分支。然后将系统的星期数值通过CASE函数转换为相应的字符串信息并显示结果。运行结果如图2-20所示。,图2-20 根据系统时间判断当前日期所对应的星期值并且输出结果,（2）搜索CASE表达式 搜索CASE表达式与简单CASE表达式的功能类似，但能够实现的条件分支要比简单CASE表达式更为复杂。其语法格式为： CASE WHEN 布尔表达式 THEN 结果表达式 WHEN 布尔表达式2 THEN 结果表达式2 ELSE 结果表达式n END 【例2-25】取得系统时间，并且判断当前时间在一天中所处的时间段，并且在查询分析器中输出提示信息。 在查询分析器中运行如下命令： DECLARE sj DATETIME SET sj = DATEPART(hh,GETDATE(),SELECT CASE WHEN sj=20 and sj=14 and sj=12 and sj=10 and sj=0 and sj10 THEN 临晨 END,本例中首先通过DATEPART函数取得当前时间中的小时数，并保存在sj变量中，然后构造CASE函数，用于确定不同的条件分支。将变量sj分别与CASE函数中的条件依次进行比较，直到满足条件时输出结果。运行结果如图2-21所示：,图2-21取得系统时间并判断当前时间所处的时间段,4WHILE语句 WHILE语句可以在满足条件的情况下重复执行在循环体内的语句。在循环体内部可以使用CONTINUE、BREAK语句对循环进行控制。 WHILE语句的语法格式为： WHILE 条件表达式 BEGIN 语句块1 BREAK 语句块2 CONTINUE 语句块3 END 当条件表达式的值为真时，循环将重复执行，直到条件表达式的值为假时，退出循环。CONTINUE命令可以忽略其后的语句而继续进入下一次循环。而BREAK命令可以提前终止整个循环过程。,【例2-26】利用循环计算1+2+3+99+100的值。 在查询分析器中运行如下命令： DECLARE sum int , i int SET sum = 0 SET i = 1 WHILE i=100 BEGIN SET sum = sum + i SET i = i + 1 END PRINT sum 运行结果为：5050。 【例2-27】利用WHILE循环计算当前时间距离2007年1月1日之间的天数。 在查询分析器中运行如下命令： DECLARE num int , sj DATETIME SET sj = GETDATE() PRINT 当前时间为： + CAST( sj AS CHAR(20) SET num=0 WHILE 1=1 BEGIN,SET sj = DATEADD(day,1,sj) SET num = num + 1 IF DATEPART( yy , sj ) = 2007 BREAK END PRINT 距离2007年1月1日还有 + CAST(num AS VARCHAR(10) + 天 本实例中，首先由sj变量获取系统时间，其后在while的每次循环中，都对sj对应的日期加1。当日期增长至2006年时，使用BREAK语句退出循环，用num变量累计循环的次数，退出循环时num值为最终的结果。运行结果如图2-22所示。,图2-12 利用WHILE循环计算当前时间距离2007年1月1日之间的天数,本章小结 本章内容主要学习了Transact-SQL语句中编程的有关知识，包括变量、表达式、函数及流控语句，同学们应重点掌握如何能够通过这些知识设计一些较为复杂的SQL语句。,练习与上机 一、填空题 1、 SQL语言主要由哪三部分组成_，_，_。 2、 数据对象的引用都由哪四部分所组成_，_，_，_。 3、标识符可划分为_，_。 4、系统存储过程通常以_开头。 5、TranceSQL语言提供了几种类型的系统数据类型，分别为：_、_、_、_、_等。 6、字符型数据类型包括_、_、_、_等。 7、日期型数据类型为_ 和_ 。 8、货币型数据类型为_ 和_ 。,二、选择题 1、在SQL Server 2000中，使用哪种语句定义变量（ ）？ A、CREATE TABLE B、SET C、GO D、DECLARE 2、TransactSQL语句中的赋值运算符为（ ）。 A、= = B、= C、：= D、：= 3、下列哪种函数用于判断两个日期相隔的时间差（ ）？ A、DATEADD B、DATEDIFF C、DAGENAME D、GETDATE 4、下列哪种函数用于求得不大于某个数最小整数（ ）？ A、FLOOR B、SIN C、SQUARE D、POWER 5、如果数据表中的某列值是从0从到255的整形数，最好使用哪种数据类型（ ） ？ A、int B、tinyint C、bigint D、decimal,三、简答题 1、简述SQL Server2000中标识符的格式规则。； 2、简述 Transact-SQL语言的各种流程控制语句的语法格式。 项目实训 1、查询出每一个系部学生中团员的数量。 2、编写Transact-SQL语句获取当前的系统日期时间，并取得当前日期的年、月和日，在查询分析器中执行Transact-SQL语句，测试语句结果。,