java web项目开发案例精粹-2技术sql server2000数据库chap

Transact-Transact-SQL是SQLServer的—种结构化查询语言它提供了标SQLSQL命令做了许多补充，如变量说明、流程控制本章将Transact-SQL语言的基础内容。重点讲解Transact-SQL语言的并能熟练的应用Transact-SQL语言进行数据操作。Transact-SQL的变量和常量是数据操作的应用基础。常量是表示一个特定数据值的符Transact-SQL中具有自己的运算符集合和运算规则。运算符可用来执行列、变量或变在Transact-SQL中定义了很多的内置函数，这些内置函数的使用可以大大的提高在SQL语句中信息的能力，使得SQL语句的应用更加灵活。除了使用系统提供的函数查询或过程等的程序段中使用，也可以像过程一样通过EXECUTE命令来执行。用户自定义函数中了一个Transact-SQL例程，可以返回一定的值。Transact-SQL与SQL的区行扩充，形成的数据库系统查询语句。它是公司的SQLServer数据库应用中心所扩充的，是SQLServer的编程语言，在ORACLE中是不能使用的。常量和关于SQLSELECTFROMSELECTFROM2-1执行结标识符的首字符必须为Unicode2.0标准中所定义的字母或下划线，#,@。字母可定界标识符是指使引号或方括号定界符进行分隔的标识符。当标识符不能遵循标识符命名规则时，就需要使用定界符分隔标识符。当在标识符中使用空格或者使用Transact-SQL语言中的保留字时，就需要使用定界标识符。在定界标识符中可以使用如下特殊字符，如表2-1所示。2-1特殊字~!%^&\‘{}()使用引号为定界符时，必须打开QUOTED_IDENTIFIER无论QUOTED_IDENTIFIER选项的状态如何，方括号“[]”均可作为定界符使用。SELECTSELECT*FROMWHERE2-2执行结，，-0.82E5，0.82E-0.82E5，0.82E-SELECT*FROM字符串常量包含在单引号内(‘’)并包含字母数字字符（a-z、A-Z和0-9）以及特殊字SELECT*FROM 2-3执行结SELECT*FROM2008'，'16:30:30'如在学生信息管理系统Student数据库的Message数据表中，查询SELECT*FROM2-4执行结USER、SYSTEM_USER、SESSION_USER等。这些都是在当前数据库系统中使用得比较全局变量中记录了SQLServer各种状态信息。全局变量定义如表2-2所示。2-2全局变SQLServe本次启动后，花费CPUSQLServer本次启动后，处于空闲状态的时间总量SQLServer本次启动后，所接受连接或视图连接的当前服务器配置下，SQLServer可以同时响应的最大连接返回SQLServer数据库系统的软件返回SQLServer本次启动后，从网络中所的输入数SQLServer本次启动后，向网络所发送的输出数据SQLServer本次启动后，SQLServer连接中所返回SQLServer本次启动后，磁盘写入次返回SQLServer本次启动后，磁盘次数SQLServer自执行最后一个Transact-SQL语句以来返回SQLServer本次启动后，读写过程中返回最后一个FETCH游标语句的状返回所执行的最后一个Transact－SQL语句所影响的行@@CURSOR@@LOCK{{@local_variabledata_type}|{@cursor_variable_name在Transact-SQL中局部变量用DECLARE语句，应用在该变量的批或过程体{{@local_variabledata_type}|{@cursor_variable_name部变量后，可用SELECT语句或SET语句为其赋值。数据类数据类型是指以数据的表现方式和方式来划分的数据的种类。在ANSI/ISO标准中规定了SQL中可以使用的数据类型。关系数据库提供了很大范围的数据类型供用户使2-3数据类real、float、decimal、sql_variant、table、smallint型：此数据类型的大小为2个字节，共16位，可以的数值范围数据的长度，取值为18000。在应用此类型时必须指定binary型数据的大小，至少应为1个字节。varbinaryvarbinaryn)]，同样地，n18000。若输入的数据过长，将会截掉其超出部分。当binary数据类型允许NULL值时，将被视为varbinary数据类型。说明：由于binary数据类型长度固定，因此它比varbinary类型的处理速度1，并进行必要的进位。浮点数据类型包括real型、float型、decimal型和numeric型。3.40E+38decimalnumeric型：两种数据类型的功能完全一样，它们都可以提供小数所需要的实际空间，但也有一定的限制，用户可以用2到17个字节来从值为0。如果输入01以外的值，系统都会作为1来处理。text型：用于大量文本数据，其容量理论上为1到 个字节。在SQLServer2000中，可将text类型和image类型的数据直接存放到表的数据行中，而并相应减少了磁盘处理这类数据的I/O数量。ntext型：ntext类型采用Unicode标准字符集，因此其理论容量为 image型：用于大量的二进制数据，其理论容量为 储数据的模式与text数据类型相同，通常用来图形等OLE对象。在输入数据时，同binary数据类型一样，必须在数据前加上字符“0X”作为二进制标识。和nvarchar型。号占一个字节的空间，其定义形式为char[(n)]，n表示所有字符所占空间18000，即可容8000ANSI字符，若n值，则系统默认值为1。若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好ncharUnicodencharn)]，n的取值为1到4000。因为Unicode标准规定每个字符占用两个字节的空间，所以nchar型比char型多占用一倍的空间，使得一个单位的容纳量大大增加。[(n的取值也为1到8000。varchar数据类型的长度为实际数值长度。如果输入nchar型在数据用空格填充；若输入的数据过n14000 datetime型：用于日期和时间的结合体。此数据类型所占用的空间为8个字节，可以从公元1753年1月日零时起到公元年月日时5959秒之间的所有日期和时间，其精确度可达三百分之一秒。如果在输入数期部分，则系统将190011日作为日期缺省值。smalldatetime型：日期时间相对于datetime数据类型范围较小，从 年月 年月6日。它的精度较低，只能精确到分钟，其分钟个位上的值是根￥money型和smallmoney型。万分之一。money8个字节smallmoney型：smallmoney数据类型的货币值范围比MONEY数据类型小，其取值从-214748.3648～.3647，数据精度为万分之一。smallmoney数据类型使用4个字节。timestamp型：此数据类型提供数据库范围内的惟一值，相当于binary(8或一个计数值将自动地添加到此timestamp数据列中。注意：每个数据库表中只能有一个timestamp数据列，如果另外建立一个名为“timestamp”的列，则该列的类型将被自动设为timestamp数据类型。uniqueidentifier型：此数据类型一个16位的二进制数据，此数据称为全局唯SQLServer20003bigint型、sql_varianttabletable型：用于对表或视图处理后的结果集。这一新类型使得变量可以一SQLServer2000的资源管理器窗口，展开数据库节点，鼠标右键单击“用图2-5所示。2-5选择“新建用户定义数据类型”命图2-6“用户定义的数据类型属性”框中，选择一种系统数据类型，如图2-7所示。2-7设置用2-8选中“允NULL值”复选2-9定义的用户定义数据类数据类型的选择对Student2-10Message表中的数据类2-11Student_info表中的数据类2-12Class表中的数据类运算符与表 DECLARE@UsersSET@UsersDECLARE@UsersSET@Users=&(按位AND)：按位与(两个操作数|(按位OR)：按位或(两个操作数^(按位互斥OR)：按位异或(两个操作数~(按位NOT)：按位取反(一个操作数据类型如表2-4所示。2-4位运算符所支持的操作数数据类Varbinary、Int、smallin、Int、smallin、tinyint、binary、Int、smallin、tinyint、 !=：不!<：不!>：不SELECT*FROMstudent_infoWHERE比较运算符返回值为数据类型，它有三个值：TRUE、FALSE和NULL。如果比TRUEFALSEANSI_NULLS选项，则当比较操作数中有任一个为NULL时，将返回NULL。例如查询学生信息管SELECT*FROMstudent_infoWHERE2-13比较运算符的使EXISTS：如果子查询包含一些行，那么就为IN：如果操作数等于表达式列表中的一个，那么就 AND：对两个表达式的值进行逻辑与运算，如果两个表达式的都TRUE，那么就为ANY：如果一系列的比较中任何一个为TRUE，那么就为NOT：对表达式的值进行取反运算，即当表达式的值为TRUE时返FALSESOME：如果一系列的比较，有些为TRUE，那么就为TRUEStudent数据库的Student_info表中地址为石家庄的男学生 SELECT*FROMstudent_infoWHEREaddress=’石家庄’ANDsex=’男 2-14逻辑运算符的使 ~(按位NOT)数据定义实现数据的。在SQLServer2000中提供了图形工具来完成数据库的建立工作。在SQL CREATEDATABASE<数据库名>[其他参数 CREATETABLE表(1类型[NULL|NOTNULL],[2[NULL|NOTNULL],3类型[NULL|NOTNULL],CREATETABLE表(1类型[NULL|NOTNULL],[2[NULL|NOTNULL],3类型[NULL|NOTNULL],SQLNULLNULL值。NOTNULLSQL的关键字，ALTERTABLE数据表ADD[DEFAULT[CHECKconstraint]数据库管理者或数据库设计人员可以应用SQL中提供的ALTERALTERTABLE数据表ADD[DEFAULT[CHECKconstraint]DROPTABLE在SQL中提供了一个将表完全从数据库中删除令DROPTABLE。该命令能删除DROPTABLEON名(12ON名(12UNIQUECLUSTED|UNCLUSTED：用来指明本列上的索引是否为索引个参数，创建索引的表（表名）Student_infoStudent_info_index。学生编号UNIQUEASC参数。USEUSEStudentCREATEINDEXStudent_info_indexONStudent_info(Student_idASC)2-15 【任务分析】使用DROP 语句删除该索引，索引名Student_info.Stu_dent_info_indexUSEUSEStudentDROPINDEX2-16数据更INSERTINTO表名[(INSERTINTO表名[(属性列,[属性列VALUES常量常量在使用INSERT...VALUES语句添加记录时，必须遵守以下四条准则：顺序必须与用CREATETABLE创建表时列的顺序相对应。2-5男【任务分析】任务2-3INSERT语句的两个参数，插入数据的表（表名）为数据列排列顺序插入数据，插入的数据为表2-6所示的数据。2-6与字段对应的记录l男1INSERTINSERTINTOVALUES ','石家庄2-17向表中添加记两种情况中可以使用INSERT...SELECT语句：ALTERTABLE语句。而对于改动较大FROM表名WHERE条件SELECT语句中返回的列的数据类SET属性1SET属性11属性22[WHERE条件]修改的新值（SET）为Age＝20，修改的条件（WHERE条件）为Student_id=1011。USEUSEStudentUPDATEStudent_infoSETAge=20WHEREStudent_id=10112-18修改表中数DELETEFROM表SQLDELETEFROM表WHEREWHERE子句可以从表中删除一个记录，也可以删除多个记录。若不使用WHERE子句，则将删除表中所有的记录。【任务分析】任务2-5涉及到DELETE语句的两个参数，删除的表（表名）Student_info，删除条件（conditions） Student_id=1011USEUSEDELETEFROMStudent_infoWHEREStudent_id=1011;2-19删除表中记流程控制的Transact-SQL语句、批处理和过程中。本节将对控制语句进行介绍。{SQL语句|SQL语句{SQL语句|SQL语句}UseSELECT*FROM{SQL语句|SQL{SQL语{SQL语句|SQL{SQL语句|SQL语句块GOTOGOTO语句可改变流程执行的顺序。可将执行流程转移到标号处继续执行流程，从而GOTOTransact-SQL语句|Transact-SQL语句块}在WHILE语句中，BREAKWHILEWHILERETURN整数表达式RETURN整数表达式 WAITFORWAITFORDELAYTIME参数含义为等到的时间，例如：WAITFORWAITFORTIME系统内置函数在程序的应用设计中是必不可少的Transact-SQL语言中SQLServer提供了的能力，使得SQL语句的使用有了更大的灵活性，下面介绍这些系统内置函数的应用。数值等信息。系统函数可用于选择列表、WHERE子句以及其他允许使用表达式的地方。System_function据库服务器，在Transact-SQL中系统函数如下：APP_NAME()CONVERT(data_type[(length)],expression[,style])：将expressiondata_typestyle参数CURRET_TIMESTAMP：可返回系统当前日期和时间，类型为：datetimeHOST_ID()：返回主机标识号，数据类型为intHOST_NAMESESSION_USERSYSTEM_USERUSER_ID([‘user’])USER_NAME([id])：返回idDAY(date)MONTH(YEAR(DAY(date)MONTH(YEAR(DATEADDDATEADDDATEADD(datepart,number,dateDATEDIFFDATEDIFFDATEDIFF(datepart,startdate,enddateDATENAME 函数返回指定日期部分的字符串。其语法格式如下：DATENAMEDATENAME(datepart,dateDATEPART函 函数返回代表指定日期的指定日期部分的整数值。其语法格式如下：DATEPARTDATEPART(datepart,dateGETDATEGETDATESELECTSELECTGETDATE2-20执行式执行不同的运可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字2-7字符串转换函ASCII(characterexpression返回字符表达式最左端字符的ASCII代码ASCII数。如果没0~255之间ASCII代码值，该函数会返回一个NULL值。UPPER(character_expression[length[<decimal>]]SELECTLTRIM左侧空格')SELECTLTRIM左侧空格')LTRIM(character_expression

2-21SELECTRTRIM')SELECTRTRIM')RTRIM(character_expression2-22LEFTLEFT函数可返回从字符串最左边起到第integer_expression个字符的部分的子USEstudentSELECTLEFT((USEstudentSELECTLEFT((SELECTbodyFROMmessageWHERELEFT(character_expression,integer_expression

2-23RIGHT(character_expression,integer_expressionRIGHTRIGHT(character_expression,integer_expressionUSEUSESELECTRIGHT((SELECTbodyFROMmessageWHEREstudent_id=1001),15)

2-24执行SUBSTRING(expression,starting,lengthSUBSTRING(expression,starting,length字符串比较函数包括：CHARINDEX函数和PATINDEX函数，其应CHARINDEXCHARINDEXCHARINDEX(substring,expression,startPATINDEXPATINDEX(’%substring%’,expressionPATINDEX(’%substring%’,expression字符串操作函数包括：QUOTENAME函数、REPLACE函数、REPLICATEREVERSE函数和SPACE函数，下面对函数进行介绍。QUOTENAMEQUOTENAME(’character’,[,quote]QUOTENAME(’character’,[,quote]REPLACE 函数返回被替换了指定子串的字符串。其语法格REPLACEREPLACE(expression1,expression2,expression3REPLACE函数用expression3字符串替换在expression1字符串中的子串expression2REPLICATEREPLICATE(character,integer数返回NULL。 函数将指定的字符串的字符排列顺序前后颠倒，重新排列。其语法格式如REVERSEREVERSE(characterSPACE 函数返回一个有指定长度的空白字符串。其语法格式如下：SPACESPACE(integer2.9.4这些数据函数可对数据类型为整型(int)、浮点型(float)、实型(real)、货币型(money和SINCOSTANEXPLOG：返回表达SQRTABS：返回表达式的绝对值。返回0PI()：返回值为∏，即3.14159265358979362.8.5函函数主要用于对数据进行，以便用户进行特定的查询，函数经常AVG：求平COUNT：统计MAX：求最MIN：求最小SUM：求AVGAVG([ALL|DISTINCT]<expression> 函数返回有关AVG([ALL|DISTINCT]<expression>USEStudentSELECTAVG(DISTINCT例如：计算学生信息管理系USEStudentSELECTAVG(DISTINCT

2-25执行COUNTCOUNT({[ALL|DISTINCT]<expression>}|*USEStudentSELECTUSEStudentSELECT(3)MAX

2-26MAX([ALLMAX([ALL|DISTINCT]<expression>USEStudentSELECTUSEStudentSELECT

2-27MIN([ALLMIN([ALL|DISTINCT]<expression>USEStudentSELECTUSEStudentSELECTSUM

2-28 SUMSUM([ALL|DISTINCT]<expression>USEUSEStudentSELECT2-29执行2.8.6游标函数为CURSOR_STATUS，它返回游标的当前状态，过程调用者通过游Local和global参数用于说明游标为局部游标和全局游标。Cursor_name参数游标名称。Variabel说明游标为局部游标。cursor_variable参数游标变量名称。CURSOR_STATUS函数返回值含用户自定义CREATEFUNCTION函数名(@parameter_nameAsCREATEFUNCTION函数名(@parameter_nameAsRETURNSreturm_data_type owner_name.function_name([实际参数CREATEDROPDROPFUNCTIONSQL的数据说非常重要。数据库的完整性，DBMS必须提供一种机制来检查数据库中的数据，看R2R1K相对F，那F则称为外码。对R2F上的取值只允许两种可能：一是空值；二是等于R1中某个元组的主码值。CREATECREATETABLE（type_idINTNOTNULLtype_nameCONSTRAINTT_IDPRIMARY)其中PRIMARYKEY(type_id)表示是waretype表的主码。T_ID是此主码约束名。找出存取数据的用户。COMMITROLLBACK结束。COMMIT用于提交事务的所有操作，也就是将事务中所有对数据库的更新写回到数据库中，事务正常结束。：指事务T在对某个数据对象操作之前，先向系统发送请求，对其加锁。这样死锁：可能会引起死锁。例如事务T1A，事T2B。T1又申请数据BBT2，T1等待。T2又申请数据A，此时数据A被T1，T2等待。这样两个事务不能结束，形成死锁。解决死锁的GRANT{ALL|statement[,...n]TOsecurity_account[,...n作权力。SQL中应用GRANT向角色授予权限和向GRANT{ALL|statement[,...n]TOsecurity_ac