教学材料《数据库》-第4章

4.1SQL语言概述

SQL语言的全称是结构化查询语言（StructuredQueryLanguage），是一种数据库查询语言，用于存取、查询、更新数据，以及管理数据库系统，也是数据库脚本文件的扩展名。SQL语言是一个通用的、功能极强的、灵活的关系数据库语言。它以记录集合作为操作对象，在SQL语句中输入的是集合，输出的是返回集合，返回集合又可以作为另一条SQL语句的输入，SQL语句具有嵌套的特点。在很多情况下，其他语言表述的大段程序，使用一条SQL语句就可以实现。4.1.1SQL语言的发展国际标准化组织（ISO，InternationalOrganizationforStandardization）与美国国家标准局（ANSI，AmericanNationalStandardInstitute）已经制定了SQL标准。SQL语法的发展如表4-1所示。4.1.2SQL语言的特点SQL语言的特点主要包括：（1）SQL语言集数据查询、数据定义、数据操作和数据控制功能于一体。数据查询是用SQL查询语句来获取数据库中的数据。数据定义用来定义数据库的结构，如创建表、修改表结构、创建视图等。数据操作用来对表进行增、删、改、查等操作。（2）SQL语言是面向集合的。SQL语言中查找结果可以是记录的集合，进行的添加、更新和删除操作的对象都是记录集合。（3）SQL语言是非过程化的语言。使用SQL语言进行数据操作，用户只需提出“做什么”，不用指明“怎么做”，无需了解数据存取路径，也不需要了解SQL语句的操作过程，这些都是由系统自动完成，大大减轻了用户负担，数据独立性也得到了很大的提高。（4）SQL语言语法结构简单，同一种语法结构提供多种使用方式，类似自然语言，易学易用。SQL语言的使用方法一般有两种方式：一种是交互式SQL应用，通过SQL*Plus或OracleSQLDeveloper等工具惊险交互式的数据库操作；一种是嵌入式SQL应用，在使用C#、Java以及C语言开发应用软件时，将SQL嵌入到应用程序中。（5）SQL语言方便数据统计，存储效率高。4.1.3SQL语言的组成和功能

SQL语言有命令动词、子句、运算符和统计函数构成。这些元素组成起来，可以实现对数据库进行各种操作，如数据定义、数据操纵和数据控制等功能。每条SQL语句由一个谓词开始，即命令动词开始，如select关键字等；谓词后接一个或多个子句，子句中给出了被谓语作用的数据或提供谓语动词的详细信息，子句由关键字where开始。如图4-1所示SQL语句的结构。在图中，除了命令动词和子句，还有运算符“<”。4.1.4T-SQL语言T-SQL语言的组成也包括数据定义语言、数据操纵语言和数据控制语言，除此之外，还包含变量说明、内嵌函数以及其他命令等。在SQLServer2008中Transact-SQL语言的增强功能，可以使用MERGE语句在一条语句中根据与源表联接的结果对目标表执行插入、更新或删除操作。比如有一条件语句给目标表中插入或更新行，如果目标表中存在相同行，则更新一个或多个列；否则，会将数据插入行。使用该语句还可以同步两个表，根据与源数据的差别在目标表中插入、更新或删除行。4.1.4T-SQL语言MERGE语法如下：[WITH[,...n]]MERGE[TOP(expression)[PERCENT]][into]target_table[WITH()][[as]table_alias]USINGON[whenMATCHED[and]then][whenNOTMATCHED[BYTARGET][and]then][whenNOTMATCHEDBYSOURCE[and]then][][OPTION([,...n])]MERGE语法中包括五个主要子句：MERGE子句指定插入、更新或删除操作的目标表或视图。USING子句指定与目标联接的数据源。ON子句指定决定目标与源的联接条件。when子句根据ON子句的结果指定要执行的操作。OUTPUT子句针对更新、插入或删除的目标对象中的每一行返回一行。4.2SQLServer2008数据类型SQLServer2008的数据类型主要包括：字符型数据、数值型数据、日期型数据和二进制数据。字符型数据在数据库中最常用的数据类型，就是字符型数据，如学生信息表中的姓名、地址、电子邮箱、身份证号码等都是都是字符型的。字符型又分为固定长度和可变长度。数值型数据数值型数据类型包括bit、tinyint、smallint、int、bigint、numeric、decimal、money、float以及real。这些数据类型都用于存储不同类型的数字值。第一种数据类型bit只存储0或1，在大多数应用程序中被转换为true或false。bit数据类型非常适合用于开关标记，且它只占据一个字节空间。近似数值数据类型近似数值数据类型包括数据类型有float和real，它们用于表示浮点数据。4.2SQLServer2008数据类型日期型数据datetime和smalldatetime数据类型用于存储日期和时间数据。在SQLServer2008中有四种与日期相关的新数据类型，datetime2、dateoffset、date、time。二进制数据二进制数据类型有varbinary、binary、varbinary(max)或image等类型，如图形文件、WORD文档或MP3文件。其值为十六进制的0x0~0xf。Image数据类型在数据外部存储文件最大为2GB，图形文件首选数据类型是varbinary(max)，可保存最大二进制数据为8KB，其性能通常比image数据类型好。SQLServer2008的新功能是可以在操作系统文件中通过FileStream存储选项存储varbinary(max)对象。这个选项将数据存储为文件，同时不受varbinary(max)的2GB大小的限制。CLR集成在SQLServer2008中，还可以使用公共语言运行库（CLR，CommonLanguageRuntime）创建自定义的数据类型和存储结构。用户可以使用VisualBasic或C#编写更复杂的数据类型，以满足业务需求。这些类型被定义为基本的CLR语言中的类结构。4.3SQL语言的组成SQL语言的组成有数据定义语言、数据操纵语言和数据控制语言等。本节将逐一介绍。4.3.1数据定义语言SQL的数据定义语言有以下基本命令：（1）createTable，创建新表。如createTableStudents(idint,namevarchar(255),sexvarchar(4),addressvarchar(255))，会建立一个空的学生表，等待数据填入数据表中；（2）createIndex，在表上创建新的索引。如createInexindex_idonStudents(id)；（3）createProduce，创建一个存储过程。如createProduceStudents_infoasselectid,name,sex,addressfromStudentsaINNERJOINcoursebONa.id=b.id；（4）createView，创建新视图。如createViewview_sexasselectid,name,sex,addressfromStudentswheresex=’男’；（5）create{User|Group}，创建一个或更多的新用户或组。如createUseryhf；（6）ALTERTable，修改已创建的表。如ALTERTableStudentsAddBirthdaydate，修改数据表的定义，增加一个生日的新列；（7）DROP{Table|Index|Produce|View|User|Group}，可以撤销上述建立的相应对象，可以删除数据表定义以及所有的数据、索引、触发器、条件约束和数据表的权限等。如DROPTableStudents，删除学生表。4.3.2数据操纵语言SQL的数据操纵语言有以下基本语句：（1）INSERT语句，在数据表或视图中插入一行数据。例如，在学生表中新增一条学生记录，INSERTintoStudentsVALUES(1278900001,’张三’,’男’,’武汉光谷大道117号’,1990-10-12)，依见表时字段的顺序逐个放入表中，注意字段顺序和数据类型要一致，不然数据就会被放入错误的数据列中；（2）select语句，检索数据表中的数据，可以检索整个表格，也可以检索某些数据行。例如，检索学生表中男生的信息，selectffromStudentswheresex=’男’，检索的筛选条件由where子句决定；（3）update语句，更新或修改一行或多行中的数据。例如，要改变“张三”的出生日期，updateStudentssetBirthday=1990-11-12wherename=’张三’，注意修改时要充分设置筛选条件，不然容易修改不该修改的数据；（4）delete语句，删除数据表中的一行或多行的数据，也可以删除表中所有行。例如，删除学生表中的所有信息，deletefromStudents，注意筛选数据时请慎重，数据删除后没办法恢复。4.3.3数据控制语言SQL的数据控制语言有以下基本语句：（1）GRANT语句，是授权语句，把语句的权限或对象权限授予给其他用户和角色。例如，将学生表的查询权限赋给普通用户，GRANTselectONStudentsTOyhf；（2）DENY语句，拒绝给当前数据库内的用户或角色授予权限，也能防止用户或角色通过组或角色成员继承权限。例如，拒绝安全账户给Students表的查询权限，DENYselectONStudentsTOyhf；（3）REVOKE语句，将用GRANT语句授予给当前数据库内的用户或者角色的权限删除，或者将用DENY语句拒绝给当前数据库内的用户或角色的权限删除，但是不影响用户或角色从其他角色中的成员继承的权限。例如，收回用户yhf对学生表的执行权限，REVOKEGRANTOPTIONFOREXCUTEONStudents。4.4T-SQL常用语言元素在SQLServer数据库中，T-SQL语言包括数据定义语言（DLL）、数据操纵语言（DML）、数据控制语言（DCL）和新增的语言元素。前面已经结束了数据定义语言、数据操纵语言和数据控制语言，这里主要讲解T-SQL的常用语言元素，有标识符、变量、注释、运算符和表达式等。4.4.1标识符

标识符是用户定义的变量名、函数名、存储过程名、库名、表名、索引名和视图等各种名称。标识符的命名规则有：（1）标识符的长度可以为1-128个字符；（2）标识符的第一个字符必须为字母、下划线、@、#；（3）以@为首的标识符表示一个局部变量；（4）以@@为首的标识符表示全局变量。在表和存储过程命名中，名称前包括一个“#”表示局部对象，有两个“#”表示全局对象。标识符内不允许有空格，也不允许为SQL中的关键字。4.4.2变量

变量分为局部变量和全局变量。局部变量就是用户自己定义的变量，在语句之间传递数据，局部变量以@符号开头，使用时，必须先声明。局部变量的声明语法：declare@<局部变量名><数据类型>[,…n]局部变量赋值语法：set|select@<局部变量名>=<表达式>例如，声明一个局部变量，declare@var1int，给变量var1赋值，set@var1=135，最后输出变量var1的值，select@var1。全局变量是系统定义的变量，用于存储系统的信息，用户不能建立全局变量，也不能修改其值，全局变量名以@@符号开头，例如，@@Version。例4-1，用全局变量查看SQLServer的版本和服务器名称等信息。4.4.3注释

注释分为单行注释和多行注释。单行注释采用“--”表示，插入单独行。多行注释采用“/*注释文本*/”，“/*”表示注释开始的位置，”*/”表示注释结束。不管是单行还是多行注释都没有长度限制。例4-2，declare@var1int--声明一个局部变量set@var1=345/*给变量var1赋值*/select@var1--输出变量var1的值4.4.4运算符SQLServer2008中提供了七种运算符：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串串联运算符和一元运算符。算术运算符算术运算符是在两个表达式上执行数学运算，这两个表达式可以是数值类型的任何数据类型，如datetime、smalldatetime、money、smallmony、numeric等。算术运算符包括加（+）、减（－）、乘（*）、除（/）和取模（%）。算术运算符、说明和语义。赋值运算符在T-SQL中用“=”表示赋值运算符。赋值运算符在列标题和列定定义值的表达式之间建立关系，主要是将数据值赋给特定的对象。位运算符位运算符能对两个二进制数据或整型数据进行位操作。位运算符两侧的操作数不能同时都是二进制数据，必须有一个为整数。4.4.4运算符比较运算符比较运算符是用来比较两个表达式是否相同，其结果为布尔值TRUE（真）和FALSE（假）,还有UNKNOWN。表达式的类型为除了text、ntext、image数据类型外所有的数据类型，比较运算符还可以用于所有的表达式。逻辑运算符逻辑原算法可以将多个表达式连接起来，逻辑运算符包括AND、OR和NOT等运算符，返回的结果是布尔类型的值（TRUE和FALSE）。字符串串联运算符字符串串联运算符是通过加号“+”进行字符串串联，将字符串连接起来，这个加号即被称为字符串串联运算符。例如对于语句select’123’+’abc’，其结果为123abc。一元运算符一元运算符只能对一个表达式进行运算。4.4.5表达式

表达式分为简单表达式和赋值表达式。简单表达式由一个变量、常量、列名或函数组成，如var1=5。复杂的表达式由两个或多个简单表达式通过运算符连接起来的表达式,，如selectname=substring(firstname,1,1)+','+lastnamefromStudents。在复杂表达式中，两个或多个表达式要么具有相同数据类型，要么数据范围小的数据类型可以隐式转换为数据范围大的数据类型。4.5T-SQL流程控制语句T-SQL流程控制语句和结构化程序设计结构一样，都分为顺序结构、选择结构和循环结构。T-SQL流程控制语句组成有控制语句、语句块或存储过程的执行流程。与结构化程序设计流程相对应，将流程控制语句分为顺序结构语句、选择结构语句和循环结构语句等。4.5.1GOTO语句

GOTO语句可以改变程序的执行顺序，将程序流程终止跳转到指定标签位置。GOTO语句格式：GOTOlabel--改变执行，跳转到标签/*中间流程控制语句*/label：/*标签定义语句*/注意，标签的定义可以在GOTO语句之前，也可以在GOTO语句之后，标签命名可以是数字和字符的组合，必须以“：”结束，GOTO语句之后的标签就不能接“：”。例4-4，使用GOTO语句求1到100以内的偶数之和。4.5.2顺序结构语句如果在流程控制语句中需要执行两条或两条以上的语句，就需要给这些语句定义一个语句块，语句块中的语句执行流程按照顺序执行，就构成了顺序结构。

顺序结构语句格式：begin<T-SQL语句>|<语句块>end注意：begin…end语句允许嵌套。例4-5，要判断一个数值的大小，并且条件为真。4.5.3选择结构语句选择结构采用条件语句来实现。按照语句的复杂程序又分为单分支选择、双分支选择和多分支选择语句。单分支选择语句

单分支选择语句格式：IF<逻辑表达式><SQL语句1>|<语句块>双分支选择语句

双分支选择语句格式：IF<逻辑表达式><SQL语句1>|<语句块1>else<SQL语句2>|<语句块2>例4-6，如果计信1301班的数据库课程成绩平均分高于75分，请显示“计信1301班的数据库课程平均成绩>75分”。4.5.3选择结构语句多分支选择语句

多分支选择语句不用IF和else语句结构，采用CASE语句。CASE语句又分为简单CASE语句和搜索CASE语句。（1）简单CASE语句简单CASE语句格式：CASEInput_表达式

whenwhen_表达式1thenresult_表达式1

[whenwhen_表达式2thenresult_表达式2][...n][elseresult_表达式n]end该语法格式中第一个“whenwhen_表达式1thenresult_表达式1”表达式，表示要计算第一个逻辑条件“Input_表达式=when_表达式1”，返回第一使逻辑条件成立的为TRUE的“result_表达式1”。4.5.3选择结构语句（2）搜索CASE语句搜索CASE语句格式：CASEwhen逻辑表达式1thenresult_表达式1[when逻辑表达式2thenresult_表达式2][...n][elseresult_表达式n]end该语法结构中先按顺序计算第一个when子句的逻辑表达式，返回第一个使逻辑表达式为TRUE的“result_表达式1”。如果所有逻辑表达式都为FALSE，则返回else后面的“result_表达式n”。如果没有指定的else子句，就返回空值“NULL”。例4-7，使用CASE语句获得学生选修课程名、姓名、成绩，并将成绩存入课程成绩表。4.5.4循环结构语句循环结构语句可以采用while语句实现。循环语句格式如下：while<逻辑表达式><SQL语句>|<语句块>/*循环体*/[BREAK][CONTINUE]循环语句格式中的BREAK语句，是中断语句，程序中断以后从循环体中跳出。而CONTINUE语句，是继续执行语句，程序从本次循环退出，继续下一轮的循环。注意：在循环语句中必须有修改循环控制变量的语句，不然就成了死循环，一直不停的循环。while语句后面的SQL语句如果有多条语句，必须用begin和end结构定义语句块。例4-8，使用while语句求1到100之间的奇数和。4.5.5批处理语句批处理语句是当应用程序发送到SQLServer2005服务器并得以执行的一组单条或多条Transact-SQL语句。批处理采用GO标记作为结束标记，当编译器执行到GO时，就会把上一个GO到当前GO之间所有的T-SQL语句作为一个批处理，编译成一个执行计划包发送给服务器执行。GO语句的格式：GO[count]前一个执行计划到GO结束，count为正整数，表示GO之前的批处理执行的次数。批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行。4.6SQLServer2008的系统函数SQLServer2008提供了很多函数，有聚合函数、数学函数、字符串函数、日期时间函数和数据类型转换函数等。每种函数的功能不一样，具体函数功能如表4-14所示。4.6.1聚合函数聚合函数是对一组输入值执行并返回一个值。例4-10，查询学生信息表中学生人数。4.6.2数学函数数学函数用于对数字表达式进行数学运算并返回运算结果。4.6.3字符串函数字符串函数是对字符串进行运算的函数。字符串函数可以分为以下几类：基本字符串类函数、字符串查找函数、长度和分析函数，以及转换函数。4.6.4日期时间函数日期时间函数用于对日期和时间数据进行各种运算，并返回一个字符串、数字值或日期和时间值。与其他函数一样，也可以在select语句、while字句和表达式中使用日期和时间函数。4.6.5数据类型转换函数在SQLServer中，有些数据类型会自动转换，例如，smallint类型和int类型表达式之间、不同长度的char类型、nchar和nvarchar类型表达式，都可以自动转换,，也成为隐性转换。SQLServer中很多类型之间不能自动转换，就需要使用转换函数做现在转换。转换函数有两个：convert和cast，语法格式如下：convert(数据类型[(长度)],表达式[,样式])cast(表达式as数据类型)convert函数是将指定的表达式转换成对应的数据类型。cast函数是将各种类型数据转换为字符数据，如smalldatetime、datetime、字符串、float、real、money等类型转换为字符数据。例4-12，selectcast(snoaschar(8))fromStudentsselectconvert(char(10),getdate(),101)selectgetdate()4.7用户自定义函数在SQLServer2008中用户自定义函数有三大类：标量函数、内嵌表值函数和多语句表值函数。4.7.1标量函数

标量函数返回一个标量值或是变量值。函数体语句定义在begin...end语句内，其中还包含了带返回值的命令。创建标量函数的语法格式如下：createfunction|所有者|.自定义函数名(参数[1...n])returns返回参数的类型asbegin/*函数体*/return函数返回的标量值end注意：标量函数，当使用T-SQL语言实现时，的返回值不能为rowversion、cursor和table类型的值；当使用托管代码实现时，不能返回rowversion、cursor、table、text、ntext和image类型的值。标量函数的调用格式如下：Selectdbo.自定义函数名（@参数数据类型……）例4-13，创建一个自定义函数f-credit，通过判断变量的大小，确定学生成绩的学分。例4-14，当需要查看学生的课程的学分时，如何调用f-credit函数。4.7.2内嵌表值函数

内嵌表值函数只能返回由select语句查询的结果集。内嵌表值函数可用于实现参数视图化的功能。内嵌表值函数和参数视图化有时候是等价的；但是视图不允许传递用户自定义的参数，内嵌表值函数在使用参数和扩展索引视图方面提供了比视图更多的灵活