数据库应用软件2010

主讲人：孙承爱总学时：54学时（上课36学时，上机18学时）授课方式：有的边讲边练，有的课下练习。大作业：开发一个小系统（50分）平时成绩：考勤+作业+操作（50分）电话P）86057852（O）Email:sun910213@126.com

数据库原理与应用4.1TSQL语言1、TSQL概述：TSQL是应用于数据库的结构化查询语言，本身不能独立存在，是一种非过程性的语言。2、TSQL语言按照用途可以分为如下4类：•DQL（DataQueryLanguage）数据查询语言主要进行查询操作（SELECT语句）•DDL（DataDefinitionLanguage）数据定义语言主要进行建立对象的操作（建库、表、视图、索引等）•DML（DataManipulationLanguage）数据操纵语言主要进行数据处理操作（插入、删除、更新等）•DCL（DataControlLanguage）数据控制语言主要进行多个SQL语句的操作4标准SQL语句启动查询分析器的操作步骤：（1）在“开始”菜单的“程序”级联菜单中，选择MicrosoftSQLServer程序组中的“查询分析器”选项，即可启动SQL查询分析器。（2）在启动SQL查询分析器时，系统首先打开“连接到SQLServer”对话框，如右图所示。如果是远程SQLServer服务器，可以直接在SQLServer文本框中输入远程计算机的IP地址。4.2TSQL基础SQLServer提供了多种图形和命令行工具，用户可以使用不同的方法来访问数据库。SQL查询分析器是一个图形用户界面，用以交互地设计和测试SQL语句、批处理和脚本。4.2.1查询分析器（3）选择SQLServer服务器后，单击“确定”按钮，然后在“连接到SQLServer”对话框中设置正确的验证模式，即可连接到SQLServer服务器，并打开SQL查询分析器，如图所示。对象窗口SQL的输入窗口语句执行查询的按钮4.2.2数据库的操作语句1.创建数据库CREATEDATABASEdatabase_name（新库名不可多于128个字符）[[ON[filespec]]ON子句显式定义数据文件[LOGON[filespec]]LOG

ON子句显式定义日志文件]如果省略ON和LOGON，库的大小与model库的大小相等filespec定义为([NAME=logical_file_name,]FILENAME=‘os_file_name’（指出操作系统创建filespec定义的物理文件时使用的路径名和文件名）[,SIZE=size]（指出filespec中定义的文件大小，不可为分数）

[,MAXSIZE={max_size|UNLIMITED}]（指出filespec中定义的文件可以增长到的最大值，不可为分数）[,FILEGROWTH=growth_increment])（指出filespec中定义的文件的增长增量，不能超过MAXSIZE，不可为分数）例如：CreatedatabasetestOn(name=test1,filename=‘F:\testDB_test1.mdf’,size=10mb,maxsize=50mb[unlimited],filegrowth=10mb)Logon(name=test1_log,filename=‘F:\testDB_test1.ldf’,size=10mb,maxsize=50mb[unlimited],filegrowth=10mb)执行并刷新后可以在企业管理器中看到新建立的数据库2、修改数据库：ALTERDATABASEdatabase_name（要修改的库名）{ADDFILEfilespec（指定要添加文件）|ADDLOGFILEfilespec

（指定要添加日志文件）|REMOVEFILElogical_file_name（指定从库中删除空文件）|MODIFYFILEfilespec（指定要更改给定的文件）|MODIFYNAME=new_dbname（用来重命名数据库）}filespec定义同前面所述例1：为test新加一个逻辑名为test2的数据文件，其大小及其最大值分别为10mb和50mbalterdatabasetestaddfile(name=test2,filename='F:\testDB_test2.ndf',size=10mb,maxsize=50mb[unlimited],filegrowth=5mb[10%])例2：为test改名为test0

alterdatabasetestmodifyname=test03.使用和删除数据库使用和删除数据库，可以使用USE和DROP语句。其语法如下：USEDATABASEdatabase_nameDROP

DATABASEdatabase_name例如：使用并删除数据库test0

USEDATABASEtest0dropdatabasetest0说明：如果不知道当前的服务器中有哪些数据库，可以执行sp_helpdb存储过程来查看格式为：EXECsp_helpdb[库名]4.2.3表的操作语句1.表的创建使用CREATETABLE语句来建立表，其语法如下：CREATETABLEtable_name（表名）(column_namel（列名）data_type（类型）[NULL|NOTNULL]（是否允许为空）[PRIMARY|UNIQUE]（是否为主键/唯一性）[FOREIGNKEY[(column_name)]]（外键）REFERENCESref_table[(ref_column[column_name2……]))]（是否与其它表关联）例1：createtablesc(snochar(10)foreignkeyreferences

student(sno),cnochar(10)foreignkeyreferences

course(cno),degreenumeric(9))与其他表建立关联:表的字段可能参考到其他表的字段，这就需要将两个表建立关联，语法为：FOREIGNKEYREFERENCEref_table(ref_column)例2：createtablescore(snochar(10)notnullforeignkeyreferencesstudent(sno),cnochar(10)notnullforeignkeyreferencescourse(cno),degreenumeric(9),primarykey(sno,cno))创建数据表时的选项从父表中删除记录时,如果有一个子记录引用这个被删除的父记录,则参照完整性就丢失了,此时可以考虑以下几种情况:1)NOACTION(不操作)：如果有任何相关的子记录,就不从父表中将该记录删除。2)CASCADE(级联)：当父记录被删除后,自动删除相关的子记录.如果被删除的子记录也在其它关系中充当父记录,那么也应该删除那些子表中的相关记录。3)SETNULL(置空)；当父记录被删除时,相关子记录中外键的值自动被置为空值。4)SETDEFAULT(置预定值)：当父记录被删除时,所有相关子记录中的外码的值自动置为他们的预置值。5)NOCHECK(不检查)：当父记录被删除时，不做任何保证参照完整性的操作。另外，可以使用SELECTINTO语句创建一个新表，并用SELECT的结果集填充该表。例如：用student中的字段sno,sname,ssex,age创建student1表。selectsno,sname,ssex,ageintostudent1fromstudent2.修改表SQL语言提供了ALTERTABLE语句来修改表的结构。语法如下：ALTERTABLEtable_name

ADD[column_namedata_type]（增加字段）[PRIMARYKEY|CONSTRAIN][FOREIGNKEY(column_name)REFERENCESref_table(ref_column)]DROP[CONSTRAINT]constraint_name|COLUMNcolumn_name删除限制删除字段例如：在tes数据库中给book表增加人个“price”字段

Alter

table

bookaddpriceint3.删除关联和表使用SQL语言要比使用企业管理器删除表容易得多。语法为：DROPTABLEtable_name例如：要删除book表DROPTABLEbook4.2.4数据查询基本格式是由SELECT子句、FROM子句和WHERE子句组成：SELECT

列名表FROM

表或视图名WHERE

查询限定条件完整的SELECT语句的用法如下所示：SELECT

列名表[INTO

新表名]FROM

表或视图名[WHERE查询限定条件][GROUP

BY

分组表达式[HAVING

分组条件]][ORDER

BY

次序表达式[ASC|DESC]]使用SQL语句时应注意的事项1、SQL中的字符串中只能用‘’，而不能用“”2、每个SELECT语句将产生一个查询结果集，它由一个或多个列以及0行或多行结果组成。3、(NOT)BETWEEN…AND…测试包含边界两端的值。4、(NOT)IN用于取值有限，多者取其一。5、(NOT)LIKE模式匹配查询，“%”代表0个或多个字符序列“—”代表一个字符；若查询的串中含有“%”或“—”，则用转义字符表示。例：LIKE’15#%’ESCAPE‘#’和

LIKE’15\%’ESCAPE‘\’都可查询含“15%”的字符串。6、ISNULL（ISNOTNULL）用于查询是否为空。空值：指值不确定或不能使用的数据列。两个不确定的值是不能比较是否相等的。7、‘*’可以代替所有列名，但它只可用于关系表比较稳定的情况下，否则当表发生变化时将会给修改带来麻烦。8、在算术表达式中引用的列必须都为数值类型的。列名一般与检索的表中相应列名相同，但也有的为位置名（如col3）或用AS子句来为列名命名。9、ORDERBY子句允许检索得到的行基于任一列或列的组合以升序或降序排列,而不考虑这些列是否出现在结果中,但是,有的版本坚持ORDERBY元素需要出现在SELECT列表中,但不管是哪种情况,ORDERBY子句必须是SELECT语句的最后一个子句.10、聚合函数在表中的一个列上操作，并且返回单一值。COUNT、MAX、MIN可以用到数值和非数值型字段上，但SUM、AVG只能用到数值型字段上。除COUNT（*）外，每个函数都首先忽略NULL值，然后再对剩下的非空值进行操作，而COUNT（*）是COUNT的特殊用法，它统计表中的所有行，不管这些行是否是空值或是否出现重复值。去掉重复值DISTINCT对于MAX和MIN来说没有作用，但对于SUM和AVG有影响。但要注意在一个查询中DISTINCT只能指定一次。11、特别要注意的是，聚合函数只能在SELECT列表和HAVING子句中使用。在任何别的地方使用都是不正确的。如果SELECT列表中包含了聚合函数，并且没有使用GROUPBY子句，那么SELECT列表中的项不能包含对任何列的引用，除非该列是聚合函数的参数也就是说SELECT列表中的项要么全为聚合函数，要么全不是聚合函数，或含有GROUPBY参数。SELECTNO，COUNT（STY）FROMSSS（错误）SELECTNO，COUNT（STY）FROMSSSGROUPBYNO12、一个包含了GROUPBY子句的查询被称为分组查询，因为它将SELECT表中的数据进行分组并为每个分组产生了一个概要行。在GROPRBY子句中的列被称为分组列。ISO标准要求SELECT子句和GROUPBY子句紧密结合在一起。当使用了GROUPBY子句后，SELECT列表中的每一项在每个分组中必须都是单值的，而且，SELECT子句只能包含：列名、聚合函数、常量、一个包含上述各项的表达式。在SELECT列表中的所有列名都必须出现在GROUPBY子句中，除非该名称只在聚合函数中使用。反之则不然，在GROUPBY子句中出现的列名则不一定出现在SELECT列表中。当WHERE子句和GROUPBY子句同时使用时，WHERE子句先被应用，然后在剩下的满足条件的行之上进行分组。13、HAVING子句和GROUPBY子句同时作用来限制在结果表中出现的分组。尽管HAVING和WHERE子句在语法上相似，但是它们的目的不同。WHERE子名过滤进入结果表的单个行，而HAVING子句将过滤进入结果表的整个分组。ISO标准要求在HAVING子句中使用的列名必须在GROUPBY列表中出现，或者被包含在聚合函数中。事实上，HAVING子句的查询条件常常至少包含一个聚合函数。否则查询条件就可以移到WHERE子句并应用到单行中。（切记聚合函数不可在WHERE子句中使用）。14、使用子查询（结果列来自一个表）时的规则：（1）ORDERBY子句不能用在子查询中，尽管最外层SELECT语句可能会使用该子句。（2）子查询SELECT列表必须由一列列名或是一个表达式组成。除非子查询使用关键字EXISTS（3）默认时，子查询中的列名是子查询FROM子句中的表中存在的列名。通过限定列名也可以引用外层查询的FROM子句中的表。（4）当子查询是比较运算中涉及的两个操作数中的一个时，子查询必须出现在比较操作符的右侧。例如，下面的形式就是不正确的。SELECTstaffNo,name,positionFROMstaffWHERE(SELECTAVG(salary)FROMstaff)<salary（5）子查询不能作为表达式的一部分。15、当结果列来自多个表时必须使用连接查询。2.选择查询（行向）为了选择表中满足查询条件的某些行，可以使用SQL命令中的WHERE子句。WHERE子句的查询条件是一个逻辑表达式，它是由多个关系表达式通过逻辑运算符（AND、OR、NOT）连接而成的。3.排序查询通过在SELECT命令中加入ORDERBY子句来控制选择行的显示顺序。ORDERBY子句可以按升序（默认或ASC）、降序（DESC）排列各行，也可以按多个列来排序。1.投影查询（列向）使用SELECT语句可以选择查询表中的任意列。FROM子句指出从什么表中提取数据。如果要去掉重复的显示行，可以在字段名前加上“DISTINCT”关键字来说明。查询举例函数名功能AVG计算一个数值型列的平均值COUNT计算指定列中选择的项数，COUNT(*)统计查询输出的行数MIN计算指定列中的最小值MAX计算指定列中的最大值SUM计算指定列中的数值总和STDEV计算统计标准偏差VAR统计方差4.使用聚合函数Student(Sno,SName,SSex,Age,Class)Score(Sno,Cno,Degree)例1：计算班级编号为020104的学生数selectcount(*)fromstudentwhereclass=‘020104’例2：计算最高分和最低分selectmax(degree),min(degree) fromscore例3：计算最高分和最低分之差selectmax(degree)-min(degree) fromscore常用的聚合函数列表例4：计算学号是2002010401的学生的平均分selectavg(degree)fromscorewheresno=‘2002010401’5.表的连接查询在一个查询中，当需要对两个或多个表连接时，可以指定连接列，在WHERE子句中给出连接条件，在FROM子句中指定要连接的表，格式：SELECT列名1，…FROM表1，…WHERE连接条件Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)例1：查询课程编号是数学课程的平均分selectavg(degree)fromscore,coursewhereo=oandame=‘数学’例2：查询比学生‘2002010401’的‘6-166’号课程成绩高的记录selects2.*fromscores1,scores2wheres1.sno=‘2002010401’ando=‘6-166’ ands2.degree>s1.degree6.子查询子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SQL命令的WHERE子句中。Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)例1：查询比学生‘2002010401’的‘6-166’号课程成绩高的所有记录select*fromscorewheredegree>(selectdegreefromscorewheresno=‘2002010401’andcno=‘6-166’)例2：查询存在有85分以上成绩的课程cnoselectdistinctcnofromscorewheredegreein(selectdegreefromscorewheredegree>85)例3：查询“张旭”老师任课的学生成绩，并按成绩递增排列selectcno,sno,degreefromscorewherecno=(selectofromcoursex,teacherywherex.tno=y.tnoandy.tname=“张旭”)orderbydegreedesc例4：查询选修某课程的学生人数多于5人的教师姓名selecttnamefromteacherwheretnoin(selectx.tnofromcoursex,scoreywhereo=ogroupbyx.tnohavingcount(x.tno)>5)说明：SQL命令还提供了UNION子句，它可以将多个SQL命令连接起来生成单个SQL无法做到的结果集合。例：查询所有教师和学生的tname、tsext和tbirthday.Selecttnameas“姓名”,tsextas“性别”,tbirthdayas“出生日期”fromteacherUNIONSelectsname，ssext,sbirthday

fromstudent例5：查询选修编号为“3-105”的课程，且成绩高于所有编号为“3-245”成绩低的学生的cno,sno,degreeSelectcno,sno,degreefromscorewherecno=“3-105”anddegree>all(selectdegreefromscorewherecno=“3-245”)Orderbydegreedesc注意事项1、使用别名的格式格式1：别名AS字段名称格式2：字段名称别名格式3：别名=字段名称（其中的别名用单引号括起来）2、ntext、text、image三种类型的数据不能用作ORDERBY排序的字段。3、SELECT列表中可以直接出现字符串，它将直接显示在结果中。如：SELECT

学号，‘总成绩’，sum(成绩)FROMSTUDENT对比:SELECT

学号,sum(成绩)AS总成绩,FROMSTUDENT4、GETDATE（）函数可以获取当前的日期。函数DATEPART（参数表）可以取日期时间类型数据中的各个组成成份，具体到网上查着用。5、要想显示单引号，必须用两个单引号表示如：SET@XX=‘thisisa’’string’’’6、CHAR函数可以将一个整数转换成ASCII码如：在字符串中用CHAR（13）和CHAR（10）产生一个回车并生成一个新行,PRINT用于打印字符串,将其显示在屏幕上。PRINT‘第一行’+CHAR（13）+CHAR（10）+‘第二行’执行结果为：第一行 第二行7、日期时间类型数据为datetime(精确到3‰秒)和smalldatetime(精确到精确到分钟)如：SELECTCAST(‘2010-6-112:35:29.988’ASsmalldatetime)结果显示：2010-6-112:35:00（1）CAST函数是将某种数据类型的表达式转换为另一种数据类型（AS前面的类型转为AS后面的类型）。

8、SQLSERVER支持逻辑数据类型bit，它可以存储整型数据1、0、NULL。如果输入0以外的其它值时，SQLSERVER均把它着1。4.3TSQL程序设计基础4.3.1标识符1.常规标识符常规标识符必须符合标识符的格式规则。在TSQL语句中使用常规标识符时不用将其分隔。在SQLServer2005中，常规标识符的格式规则取决于数据库的兼容级别，兼容级别可以用存储过程sp_dbcmptlevel来设置。当兼容级别为80时，规则如下：•第一个字符必须是Unicode2.0标准所定义的字母、下划线、@、#。•后续字符可以是Unicode2.0所定义的字母、来自基本拉丁字母或其他国家／地区脚本的十进制数字、at符号（@）、美元符号（$）、数字符号（#）或下划线（_）。•标识符不能是TSQL的保留字。SQLServer保留其保留字的大写和小写形式。不允许嵌入空格或其它特殊字符。•常规标识符和分隔标识符包含的字符数必须在1～128之间。对于本地临时表，标识符最多可以有116个字符。2.分隔标识符在TSQL语句中，对不符合所有标识符规则的标识符必须进行分隔。符合标识符格式规则的标识符可以分隔，也可以不分隔。分隔标识符在下列情况下使用：•当在对象名称或对象名称的组成部分中使用保留字时，推荐不要使用保留关键字作为对象名称。•当使用未被列为合法标识符的字符时，SQLServer允许在分隔标识符中使用当前代码页中的任何字符。在SQLServer中，TSQL所使用的分隔标识符类型有下面两种：

•被引用的标识符用双引号（“”）分隔开。

•括在括号中的标识符用方括号（［］）分隔。分隔标识符的格式规则是：•分隔标识符可以包含与常规标识符相同的字符数（1～128个，不包括分隔符字符）。本地临时表标识符最多可以包含116个字符。•标识符主体可以包含当前代码页内字母（分隔符本身除外）的任意组合。3.使用标识符在SQLServer2005中，一个对象的全称语法格式为：server.database.owner.object可用的简写格式包含下面几种：server.database..objectserver..owner.objectserver…objectdatabase.ownerobjectdatabase..objectowner.objectobject在上面的简写格式中，没有指明的部分使用如下的默认设置值：服务器（本地服务器），数据库（当前数据库），所有者（在指定的数据库中与当前连接会话的登录标识相对应的数据库用户或数据库所有者）例如：一个用户名为bookadm的用户登录到myserver服务器上，并使用book数据库，使用下述语句创建了一个mytable表。Creattablemytable(column1int,column2char(20))则:表mytable的全称就是：myserver.book.bookadm.mytable4.3.2数据类型在SQLServer2005中，以下对象可以具有数据类型：•表和视图中的列。•存储过程中的参数。•变量。•返回一个或多个特定数据类型数据值的TSQL函数。•具有一个返回代码的存储过程（返回代码总是具有integer数据类型）。指定对象的数据类型定义了该对象的4个特性：•对象所含的数据类型。•所存储值的长度或它的大小。•数字精度（仅用于数字数据类型）。•数值小数位数（仅用于数字数据类型）。分类数据类型定义符整数型Bigint（8），int（4），smallint（2），tinyint（1）逻辑数值型Bit(1或2字节，不可建索引)小数数据类型decimal，numeric（长度见P147表9.3）货币型Money（8），smallmoney（4）（前面加￥符号）近似数值型float，real（长度见P148表9.4）字符型char，varchar，text（用“”‘’括起来的字符串）见P148、9Unicode字符型Nchar，nvarchar，ntext二进制数据类型Binary（最多8KB），varbinary（最多8KB）

，image日期时间类型datetime，smalldatetime（格式见P152-153）其他数据类型cursor，sal_variant，table，timestamp，uniqueidentifier见P155SQLServer2005提供的系统数据类型4.3.3运算符SQLServer的运算符有算术运算符、赋值运算符、按位运算符、比较运算符、逻辑运算符、字符串连接运算符和一元运算符。1.算术运算符算术运算符包括+、-、*、/和%（取模）

%（取模,结果符号与被除数据的符号相同）2.赋值运算符TSQL有一个赋值运算符，即等号（=）。3.按位运算符按位运算符包括&（按位与）、|（按位或）和^（按位异或）。4.比较运算符SQLServer提供的比较运算符有下面几种：>、<、=、<=、>=、!=、<>、!<、!>5.逻辑运算符SQLServer总共提供了10个逻辑运算符，如表所示。逻辑运算符含义ALL当一组比较关系的值都为TRUE时，才返回TRUEAND当要比较的两个布尔表达式的值都为TRUE，才返回TRUEANY只要一组比较关系中有一个值为TRUE，就返回TRUEBETWEEN只有操作数在定义的范围内，才返回TRUE（含两个端点）EXISTS如果在子查询中存在，就返回TRUEIN如果操作数在所给的列表表达式中，则返回TRUELIKE如果操作数与模式相匹配，则返回TRUE(下页有例子)（常使用通配符）NOT对所有其他的布尔运算取反OR只要比较的两个表达式有一个为TRUE，就返回TRUESOME如果一组比较关系中有一些为TRUE，则返回TRUE说明：SQLServer中通配符及其含义%包含0个或更多个字符的任意字符串—任何单个字符[]指定范围（[a-f]或集合（[abcdef]）中的任何单个字符）[^]不属于指定范围（[a-f]或集合（[abcdef]）的任何单个字符）例：利用like运算符查找相匹配的记录select*fromtestdb..studentwheresnamelike'%明%’select*fromtestdb..studentwheresnamelike‘王%’select*fromtestdb..studentwheresnamelike‘_明’

select*fromtestdb..studentwheresnamelike‘[王,李]%’select*fromtestdb..studentwheresnamelike‘[^王,李]%’6.字符串连接运算符字符串连接运算符为加号（+）。7.一元运算符一元操作符包含+（正）、-（负）和~（按位取反）。测试字符串连接运算符select'姓名:'+sname+',性别'+ssex+',年龄'+cast(ageaschar)fromtestdb..studentwheresnamelike'_明‘8.在SQLServer中，运算符的优先级如下：•+（正）、-（负）、~（按位取反）•*（乘）、/（除）、%（模）•+（加）、+（连接）、-（减）•=、>、<、>=、<=、<>、!=、!>和!<比较运算符•^（位异或）、&（位与）、|（位或）•NOT•AND•ALL、ANY、BETWEEN、IN、LIKE、OR、SOME•=（赋值）4.3.4变量变量分为局部变量和全局变量。全局变量名称前面有两个at符号（＠＠），由系统定义和维护。局部变量前面有一个at符号（＠），由用户定义和使用。1.局部变量局部变量的定义可以使用DECLARE语句，其语法格式如下：DECLARE{@local_variabledata_type}[,…n]如果要给变量赋值，可使用SET和SELECT语句。语法格式如下：SET@local_variable=expressionSELECT{@local_variable=expression}[,…n]2.全局变量变量名称说明

@@FETCH_STATUS返回FETCH语句游标的状态@@identity返回最新插入的identity列值@@IDLE返回自SQLServer本次启动以来，CPU空闲的时间，单位为毫秒@@IO_BUSY返回自SQLServer本次启动以来，CPU处理输入和输出操作的时间，单位为毫秒@@LANGID返回本地当前使用的语言标识符@@LANGUAGE返回当前使用的语言名称@@LOCK_TIMEOUT返回当前的锁定超时设置，单位为毫秒@@MAX_CONNECTIONS返回SQLServer允许同时连接的最大用户数目@@MAXPRECISION返回当前服务器设置的decimal和numeric数据类型使用的精度@@NESTLEVEL返回当前存储过程的嵌套层数@@OPTIONS返回当前SET选项信息@@PACK_RECEIVED返回自SQLServer本次启动以来，通过网络读取的输入数据包数目SQLServer2005中的全局变量变量名称说明

@@PACK_SENT返回自SQLServer本次启动以来，通过网络发送的输出数据包数目@@PACKET_ERRORS返回自SQLServer本次启动以来，SQLServer中出现的网络数据包的错误数目@@PROCID返回当前的存储过程标识符@@REMSERVER返回注册记录中显示的远程数据服务器的名称@@ROWCOUNT返回上一个语句所处理的行数@@SERVERNAME返回运行SQLServer的本地服务器名称@@SERVICENAME返回SQLServer运行时的注册键名称@@SPID返回服务器处理标识符@@TEXTSIZE返回当前TESTSIZE选项的设置值@@TIMETICKS返回一个计时单位的微秒数，操作系统的一个计时单位是31.25毫秒@@TOTAL_ERRORS返回自SQLServer本次启动以来，磁盘的读写错误次数@@TOTAL_READ返回自SQLServer本次启动以来，读磁盘的次数@@TOTAL_WRITE返回自SQLServer本次启动以来，写磁盘的次数@@TRANCOUNT返回当前连接的有效事务数@@VERSION返回当前SOLServer服务器的日期，版本和处理器类型SQLServer2005中的全局变量4.3.5批处理批处理是包含一个或多个TSQL语句的组，从应用程序一次性地发送到SQLServer执行。批处理语句以GO结束。运行时错误(如算术溢出或违反约束)会产生以下两种影响之一：•大多数运行时错误将停止执行批处理中当前及其之后的语句。•少数运行时错误（如违反约束）仅停止执行当前语句。而继续执行批处理中其它所有语句。在建立一个批处理的时候，应该遵循下面的规则：•如果EXECUTE语句是批处理中的第一句，则不需要EXECUTE。如果EXECUTE语句不是批处理中的第一条语句，则需要EXECUTE。USESCHOOLGOCREATEVIEWVIEW1ASSELECT*FROMSTUDENTWHERECLASS='95031'GOSELECT*FROMVIEW1GO•CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句不能在批处理中与其它语句组合使用。批处理必须以CREATE语句开始。所有跟在该批处理后的其它语句将被解释为第一个CREATE语句定义的一部分。不能在同一个批处理中更改表，然后引用新列。4.3.6注释SQLServer支持两种类型的注释字符：--(双连字符)：可与要执行的代码处在同一行，也可另起一行，若为多行注释，则必须在每个注释行的开始使用双连字符。/*…*/：可与要执行的代码处在同一行，也可另起一行，甚至在可执行代码内，若为多行注释，则必须在开始处使用/*，在结束处使用*/，注释行内不应出现其他注释字符。说明：多行/*…*/注释不能跨越批处理，整个注释必须包含在一个批处理内。USESCHOOL/*打开SCHOOL数据库*/GOCREATEVIEWVIEW1ASSELECT*FROMSTUDENTWHERECLASS='95031'GOSELECT*FROMVIEW1--执行一个SELECT语句GO控制流语句说明BEGIN…END定义语句块IF…ELSE条件处理语句，如果条件成立，执行IF语句；否则执行ELSE语句CASE分支语句WHILE循环语句GOTO无条件跳转语句RETURN无条件退出语句WAITFOR延迟语句BREAK跳出整个循环语句CONTINUE重新开始下一次循环语句4.3.7控制流语句SQLServer提供的控制流语句如下表所示。1.BEGIN…END语句BEGIN…END语句用于将多个TSQL语句组合为一个逻辑块。语法格式为：BEGIN {sql语句|语句块} END例如：begindeclare@varFfloatset@varF=456.256beginprint'变量@varF的值为：'printcast(@varFasvarchar(12))endendCast函数可将各种不同类型的数据转换为字符串下面几种情况经常要用到BEGIN和END语句：•WHILE循环需要包含语句块。•CASE函数的元素需要包含语句块。•IF或ELSE子句需要包含语句块。2.IF…ELSE语句使用IF…ELSE语句，可以有条件地执行语句。其语法格式如下IFBoolean_expression{sql语句|语句块}[ELSE{sql语句|语句块}]如果IF和ELSE语句的后面都使用了CREATTABLE或者SELECTINTO语句，那么CREATTABLE或者SELECTINTO

语句必须指向相同的表名。例如：使用IFELSE结构实现查询Useschoolif(selectavg(degree)fromscorewherecno='3-105')>80 begin print'课程：3-105' print'考试成绩还不错' endelse begin print'课程：3-105' print'考试成绩一般' end;3.CASE语句使用CASE语句可以进行多个分支的选择。CASE具有两种格式：•简单CASE格式•搜索CASE格式（1）简单CASE格式,其语法格式如下：CASEinput_expressionWHENwhen_expressionTHENresult_expression […n][ELSEelse_result_expression]END例如：useschoolselecttnameas'姓名',departas'单位',caseprof when'教授'then'高级职称' when'副教授'then'高级职称' when'讲师'then'中级职称'/*when'助教'then'初级职称‘*/

ELSE‘无职称’endas'职称'fromteacher（2）搜索CASE格式,其语法格式如下：CASEWHENBoolean_expressionTHENresult_expression[…n][ELSEelse_result_expression]END例如：usetestdbselectsnoas'姓名‘,cnoas'单位',casewhendegree>=85then'A'whendegree>=75then'B'whendegree>=60then'C'/*whendegree<60then'E'

*/

else‘E’endas'成绩'fromscore4.WHILE语句WHILE语句可以设置重复执行SQL语句或语句块的条件。可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。其语法格式如下：WHILEBoolean_expression{sql语句|语句块}[BREAK]{sql语句|语句块}[CONTINUE]例如：使用while循环求1+2+...+100declare@sint,@iintset@i=0set@s=0while@i<=100beginset@s=@s+@iset@i=@i+1endprint'1+2+...+100='+cast(@saschar(25))例如：使用goto语句求1+2+...+100declare@sint,@iintset@i=0set@s=0my_loop:set@s=@s+@iset@i=@i+1if@i<=100gotomy_loopprint'1+2+...+100='+cast(@saschar(25))5.GOTO语句GOTO语句可以实现无条件的跳转。其语法格式为：GOTOlable6.RETURN语句使用RETURN语句，可以从查询或过程中无条件退出。可在任何时候用于从过程、批处理或语句块中退出，而不执行位于RETURN之后的语句。语法格式为：RETURN[integer_expression]例如：UseschoolGoCreateprocmyp@nochar(10)--创建存储过程myp,no为形参Asreturn(selectavg(degree)fromscorewheresno=@no)执行完上述语句后再执行下面的SQL语句declare@nochar(10),@avg1floatSet@no=‘108’Exec@avg1=myp@no--no为实参Selectsname,@avg1as‘平均分’fromstudentwheresno=@nogo7.WAITFOR语句使用WAITFOR语句，可以在指定的时间或者过了一定时间后，执行语句块、存储过程或者事务。语法格式为：WAITFOR{DELAY'time'|TIME'time'}其中：DELAY指示SQLServer一直等到指定的时间过去，最长24小时。‘time’要等待的时间。不允许有日期部分。TIME指示SQLServer等待到指定时间。例如：下面的语句指定在1时10分执行一个语句。BEGINWAITFORTIME‘2：10：00’PRINT‘现在是2：10：00’END执行后，等机器时间到了2时10分，则出现信息现在是2：10：00说明：1、函数convert的作用是将一个数传值型数据转换为字符串。例，CONVERT(char(10),GETDATE(),3)–其中的3为第三种格式dd/mm/yy，10为后面的日期取出的长度。2、函数ISNULL常用于某些有关联的表之间，当某些列为空时无法向与该列相关联的表中插入数据，而此时又必须向与这些与此为空的列有关联的表中插入记录，此时可以将这些为空的列值设置为某一个无用的数据。例：SELECTSNOAS‘学号’，SNAMEAS‘姓名’， ISNULL（DEGREE，0）AS‘成绩’FROM STUDENTWHERESET=‘男’1.内置函数函数分类说明聚合函数执行的操作是将多个值合并为一个值，如COUNT、SUM、MIN、MAX配置函数是一种标量函数，可返回有关配置设置的信息游标函数返回有关游标状态的信息日期和时间函数操作datetime和smalldatetime值数学函数执行三角、几何和其他数字运算元数据函数返回数据库和数据库对象的特性信息行集函数返回行集，这些行集可用在Transact-SQL语句中表引用所在的位置安全性函数返回有关用户和角色的信息字符串函数操作char、varchar、nchar、nvarchar、binary、varbinary值系统函数对系统级别的各种选项和对象进行操作或报告系统统计函数返回有关SQLServer性能的信息文本和图像函数操作text和image值SQLServer2005提供的内置函数4.3.8函数SQLServer2005支持两种函数类型：内置函数和用户定义函数。2.用户定义函数SQLServer2005支持3种用户定义函数：•标量函数•内嵌表值函数•多语句表值函数SQLServer2005还支持返回table数据类型的用户定义函数：•该函数可声明内部table变量，将行插入该变量，然后将该变量作为返回值返回。•一类称为内嵌函数的用户定义函数，将SELECT语句的结果集作为变量类型table返回。例如：返回单值的函数useschoolgocreatefunctionCubicVolume(@CubeLengthdecimal(4,1),@CubeWidthdecimal(4,1),@CubeHeightdecimal(4,1))returnsdecimal(12,3)asbegin

return(@CubeLength*@CubeWidth*@CubeHeight)endgoselectCAST(SUN.CubicVolume(1,2,4)aschar(10))as'体积'登录名例如：返回表的函数useschoolgocreatefunctionStudent_class(@classnochar(10))returns@sttable(snochar(10),snamechar(10),cnochar(10),degreeint)asbegininsert@stselects.sno,s.sname,o,sc.degreefromstudents,scorescwheres.sno=sc.snoands.class=@classnoreturnEndgoselect*fromstudent_class('020105')内嵌用户定义函数遵循以下规则：（1）RETURN子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置。（2）函数体必须由BEGIN和END分隔。（3）RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。内嵌函数中使用的SELECT语句受到与视图中使用的SELECT语句相同的限制。一、联接查询说明1、内联接内联接消除与另一个表中的任何行不匹配的行，仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。selectbookname,authornamefrombook

innerjoin

authors

on(book.authorid=authors.authorid)2、外联接外联接会返回FROM子句中提到的至少一个表或视图的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外联接中两个表的所有行都将返回。（1）LEFTOUTERJOIN或LEFTJOIN

（2）RIGHTOUTERJOIN或RIGHTJOIN（3）FULLOUTERJOIN或FULLJOIN

3、左向外联接selectbookname,authornamefrombook

LEFTOUTERJOIN

authors

on(book.authorid=authors.authorid)4、右向外联接selectbookname,authornamefrombook

LEFTOUTERJOIN

authors

on(book.authorid=authors.authorid)5、完全外联接selectbookname,authornamefrombook

FULLOUTERJOIN

authors

on(book.authorid=authors.authorid)6、交叉联接（一般无意义）selectbookname,authornamefrombook

CROSSJOIN

authors空值不会相互匹配，可以通过外连接才能测试被连接的某个表的字段是否有空值。二、几个选择项的使用1、TOP显示查询头尾若干记录或百分比（用TOPNPERCENT子句）

例：返回5%定货额最大的定单

SELECTTOP5PERCENT*FROM

Order

ORDERBYUnitPrice*Quantity*(1-Discount)DESC例：返回96年1月的定单

SELECTOrderID,CustomerID,OrderDateFROM

Orders

WHEREOrderDate>#1/1/96#ANDOrderDate<#1/30/96#

McirosoftJETSQL中，日期用‘#’定界。日期也可以用Datevalue()函数来代替。在比较字符型的数据时，要加上单引号’’，尾空格在比较中被忽略。例：WHERE

OrderDate>#96-1-1#也可以表示为:WHERE

OrderDate>Datevalue(‘1/1/96’)2、使用NOT表达式求反。

例：查看96年1月1日以后的定单

WHERENotOrderDate<=#1/1/96#

3、IN运算符用来匹配列表中的任何一个值。IN子句可以代替用OR子句连接的一连串的条件。

例：要找出住在London、Paris或Berlin的所有客户

SELECTCustomerID,CityFROM

Customers

WHERECityIn(‘London’,’Paris’,’Berlin’)4、SUBSTRING（字符串，开始位置，长度）例：SELECTSUBSTRING（City,3,4）FROM

Customers三、功能查询所谓功能查询，实际上是一种操作查询，它可以对数据库进行快速高效的操作。它以选择查询为目的，挑选出符合条件的数据，再对数据进行批处理。功能查询包括更新查询，删除查询，添加查询和生成表查询。功能查询是一个SELECT查询（也就是子查询）它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含中输出中。子查询最多允许嵌套32层。1、更新查询

UPDATE子句可以同时更改一个或多个表中的数据。它也可以同时更改多个字段的值。语法：UPDATE

表名

SET

新值WHERE准则

例：英国客户的定货量增加5%，货运量增加3%

UPDATE

OEDERS

SET

OrderAmount=OrderAmount*1.1

Freight=Freight*1.03WHERE

ShipCountry=‘UK’2、删除查询

DELETE子句可以使用户删除大量的过时的或冗于的数据。删除查询的对象是整个记录。语法：

DELETE

[表名.*]FROM

来源表WHERE

准则

例：要删除所有94年前的定单

DELETE*FROM

OrdersWHEREOrderData<#94-1-1#3、追加查询

INSERT子句可以将一个或一组记录追加到一个或多个表的尾部。INTO子句指定接受新记录的表valueS关键字指定新记录所包含的数据值。语法：

INSETRINTO目的表或查询(字段1,字段2,…)

valueS(数值1,数值2,…)

例：增加一个客户

INSERTINTOEmployees(FirstName,LastName,title)

valueS(‘Harry’,’Washington’,’Trainee’)4、生成表查询

可以一次性地把所有满足条件的记录拷贝到一张新表中。通常制作记录的备份或副本或作为报表的基础。语法：

SELECT字段1,字段2,…INTO

新表[IN

外部数据库]

FROM

来源数据库WHERE

准则例：为定单制作一个存档备份

SELECT*INTO

OrdersArchiveFROM

Orders四、表达式的值与子查询1、表达式的值与子查询返回的单一值做比较

语法：表达式comparision

[ANY|ALL|SOME](子查询)

说明：ANY和SOME谓词是同义词，与比较运算符(=,<,>,<>,<=,>=)一起使用。返回一个布尔值True或False。ANY的意思是，表达式与子查询返回的一系列的值逐一比较，只要其中的一次比较产生True结果，ANY测试的返回True值(既WHERE子句的结果)，对应于该表达式的当前记录将进入主查询的结果中。ALL测试则要求表达式与子查询返回的一系列的值的比较都产生True结果，才回返回True值。

例：主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品。SELECT*FROMProductsWHERE

UnitPrice>ANY

(SELECTUnitPriceFROM[OrderDetails]WHEREDiscount>0.25)

2、检查表达式的值是否匹配子查询返回的一组值的某个值

语法：[NOT]IN(子查询)

例：返回库存价值大于等于1000的产品。SELECTProductNameFROMProductsWHEREProductIDIN(SELECTPrdoctIDFROM[OrderDEtails]WHEREUnitPrice*Quantity>=1000)3、检测子查询是否返回任何记录

语法：[NOT]EXISTS(子查询)

例：用EXISTS检索英国的客户

SELECTComPanyName,ContactNameFROMOrders

WHEREEXIS