《数据库》-学习领域三T-SQL语言操作

学习领域三T-SQL语言操作3.8T-SQL中的算术函数3.9T-SQL中的字符串函数3.10T-SQL数据类型转换函数3.11T-SQL日期函数3.12T-SQL系统函数下一页返回3.1T-SQL语言的组成SQL语言主要由以下几部分组成:数据定义语言数据操纵语言数据控制语言其他语言要素返回3.2实例数据库和系统数据库3.2.1系统数据库

SQLServer安装了4个系统数据库。它们分别是master数据库、model数据库、tempdb数据库和msdb数据库。这些数据库是SQLServer:的心脏和灵魂。master数据库控制SQLServer的所有方面。model数据库是建立所有用户数据库时的模板。tempdb数据库是一个非常特殊的数据库，供所有来访问SQLServer的用户使用。msdb数据库是SQLServer中的一个特例。下一页返回3.2实例数据库和系统数据库3.2.2实例数据库Northwind(罗斯文)示例数据库:NorthwindTrader示例数据库包含一个名为NorthwindTrader。的虚构公司的销售数据，该公司从事世界各地的特产食品进出口贸易，如图3-9所示。如果改变厂实例数据库的内容或不小心删除厂实例数据库，想将其回复到初始状态，可以用SQLServerAnalyzer运行“安装日录MSSQLInstall”中的Inst-pubs.sql或Instnwnd.sql程序来重建pub*或Northwind数据库，如图3-10和图3-11所示)上一页下一页返回3.3T-SQL语句的结构3.3T-SQL语句的结构所有的SQL语句均有自己的格式，如图3-18所示SQL语句均由一个谓语动词(Verb)开始，该谓语动词描述这条语句要产生的动作，如图3-18中的SELECT关键字。谓词后紧接着一个或多个子句(Clause)，子句中给出厂被谓词作用的数据或提供谓词动作的详细信息。每一条子句由一个关键字开始，如图3-18中的WHERE。在使用数据库时用得最多的是数据操纵语言(DML)。DML包含厂最常用的核心SQL语句，即SELECT、INSERT、UPDATE、DELETE。返回3.4T-SQL中的数据类型在SQLServer中每个变量、参数、表达式等都有数据类型。系统提供的数据类型分为几大类。1.整数数据类型整数数据类型是最常用的数据类型之一。(1)INT(INTEGER)(2)SMALLINT.(3)TINYINT(4)BIGINT2.浮点数据类型(1)REAL数据类下一页返回3.4T-SQL中的数据类型(2)FLOAT(3)DECIMAL(4)NUMERIC3.二进制数据类型(1)BINARY(2)VARBINARY4.逻辑数据类型BTT上一页下一页返回3.4T-SQL中的数据类型5.字符数据类型(1)CHAR(2)NCHAR(3)VARCHAR(4)NVARCHAR6.文本和图形数据类型这类数据类型用于存储大量的字符或二进制数据。(1)TEXT(2)NTEXT3)IMAGE上一页下一页返回3.4T-SQL中的数据类型7.日期和时间数据类型(1)DATFTIMF(2)SMALLDATETIME8.货币数据类型(1)MONEY(2)SMALLMONEY9.特定数据类型(1)TIMESTAMP(2)UNIQUEIDENTIFIER上一页下一页返回3.4T-SQL中的数据类型10.用户自定义数据类型SYSNAMESYSNAME数据类型是系统提供给用户的，便于用户自定义数据类型。11.新数据类型(1)SQLVARIANT(2)TABLE上一页返回3.5T-SQL中的变量1、局部变量局部变量是用户可自定义的变量，它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。局部变量必须以“@”开头，而且必须先用DECLARE命令说明后才可使用。其说明形式如下:DECLARE@变量名变量类型【@变量名变量类型…】下一页返回3.5T-SQL中的变量2.全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQLServer的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。3.注释符ANSI标准的注释符“--”用于单行注释;与C语言有相同的程序注释符号，即”/**/”用于注释文字的开头，”*/”用于注释文字的结尾，可在程序中标识多行文字为注释。上一页下一页返回3.5T-SQL中的变量4.运算符SQL中包括以下几种类型的运算符(Operator)。(1)算术运算符(2)比较运算符(3)逻辑运算符(4)位运算符(5)连接运算符上一页返回3.6流程控制命令与程序设计Transact-SQL语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。IF…ELSE其语法如下:IF<条件表达式><命令行或程序块>[ELSE一条件表达式]<命令行或程序块>下一页返回3.6流程控制命令与程序设计其中<条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IF…ELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，IF或ELSE只能执行一条命令。IF…ELSE可以进行嵌套。2.BEGIN…END其语法如下:BEGIN<命令行或程序块>END上一页下一页返回3.6流程控制命令与程序设计BEGIN…END用来设定一个程序块，将在BEGIN…END内的所有程序视为一个单元执行BEGIN…END条件语句，如IF…ELSE中使用。在BEGIN…END中可嵌套另外的BEGIN…END来定义另一程序块。上一页下一页返回3.6流程控制命令与程序设计3.CASECASE命令有两种语句格式:CASE<运算式>WHEN<运算式>THEN<运算式>…WHEN<运算式>THEN<运算式>一ELSE<运算式>ENDCASEWHEN<条件表达式>THEN<运算式>WHEN<条件表达式>THEN<运算式>[ELSE<运算式>]END上一页下一页返回3.6流程控制命令与程序设计4.WHILE…CONTINUE…BREAKWHILE<条件表达式>BEGIN<命令行或程序块>[BREAK][CONTINUE][命令行或程序块]END上一页下一页返回3.6流程控制命令与程序设计WHILE命令在设定的条件成立时会重复执行命令行或程序块。CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行命令。BREAK命令则让程序完全跳出循环，结束WHILE命令的执行。WHILE语句也可以嵌套。5.WAITFORWAITFORDELAY<‘时间’>TIME<‘时间’>ERROREXITPROCESSEXITMIRROREXITWAITFOR命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。上一页下一页返回3.6流程控制命令与程序设计6.GOTO语法如下:GOTO标识符GOTO命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。7.RETURN语法如下:

RETURN【整数值】RETURN命令用于结束当前程序的执行，返回到上一个调用它的程序或其他程序。在括号内可指定一个返回值。上一页下一页返回3.6流程控制命令与程序设计8.BACKUPBACKUP命令用于将数据库的内容或其事务处理日志备份到存储介质上(软盘、硬盘、磁带)。SQLServer以前的版本用DUMP命令来执行此功能，从SQLServer2000起，不再使用DUMP命令。9.CHECKPOINT语法如下:CHECKPOINTCHECKPOINT命令用于将当前工作的数据库中被更改过的数据页，datapage或日志页(logpage)从数据缓冲器(databuffercache)中强制写入硬盘。上一页下一页返回3.6流程控制命令与程序设计10.DBCCDBCCDatabaseBaseConsistencyChecker，数据库一致性检查程序命令用于验证数据库完整性、查找错误、分析系统使用情况等。

DBCC命令后必须加上子命令，系统才知道要做什么。如DBCCCHECKAL-LOC命令检查目前数据库内所有数据页的分配和使用情况。上一页下一页返回3.6流程控制命令与程序设计11.DECLARE语法如下:DECLARE@LOCALVARIABLEDATATYBE@CURSORVARIABLENAMECURSORTABLETYBEDEFINITION[…N]DECLARE命令用于声明一个或多个局部变量、游标变量或表变量。在用DECLARE命令声明之后，所有的变量都被赋予初值NULL。需要用SELECT或SET命令来给变量赋值。变量类型可为系统定义的或用户定义的类型，但不能为TEXT,NTEXT,IMAGE类型。CURSOR指名变量是局部的游标变量。上一页下一页返回3.6流程控制命令与程序设计12.PRINTPRINT命令向客户端返回一个用户自定义的信息，即显示一个字符串(最长为255个字符)、局部变量或全局变量。如果变量值不是字符串的话，必须先用数据类型转换函数CONVERT()，将其转换为字符串。其中，stringexpression是可返回一个字符串的表达式。表达式的长度可以超过8000个字符，但超过8000的字符将不会显示。13.SELECTSELECT命令可用于给变量赋值其语法如下:SELECT│@localvariable=expression│[…n]上一页下一页返回3.6流程控制命令与程序设计SELECT命令可以一次给多个变量赋值。当表达式expression为列名时，SELECT命令可利用其查询功能一次返回多个值，变量中保存的是其返回的最后一个值。如果SELECT命令没有返回值，则变量值仍为其原来的值。当表达式expression是一个子查询时，如果子查询没有返回值则变量被设为NULL。14.SETSET命令有两种用法。(1)用于给局部变量赋值，(2)用于设定用户执行SQL命令时，SQLServer的处理选项设定，有以下几种设定方式:SET选项ONSET选项OFFSET选项值上一页下一页返回3.6流程控制命令与程序设计15.WRITETEXT语法如下:WRITETEXT│table.columntextpointer│[WITHLOG]│data│WRITETEXT命令用于向数据类型为TEXT,NTEXT或IMAGE的列中读取数据。其中textpointer是指向存储文本的第一个数据库页的指针，它可以用TEX-TPTR()来获取。WITHLOG选项用于记录所写入的数据。data可为文字或变量，其最大长度为120KB。注意:WRITETEXT命令不能作用于视图。上一页下一页返回3.6流程控制命令与程序设计16.USE语法如下:USE│databasenameUSE命令用于改变当前使用的数据库为指定的数据库。用户必须是目标数据库的用户成员或目标数据库建有GUEST用户账号，使用USE命令才能成功切换到目标数据库。上一页返回3.6流程控制命令与程序设计1.AVG()AVG()函数返回有关列值的算术平均值。SELECTAVG(列名)FROM表名2.COUNT()COUNT)函数返回与选择表达式匹配的列中不为NULL值的数据个数。SELECTCOUNT(列名)FROM表名下一页返回3.6流程控制命令与程序设计3.MAX()MAX()函数返回某一列的最大值。SELECTMAX(列名)FROM表名4.MIN()MIN()函数返回某一列的最小值。SELECTMIN(列名)FROM表名上一页下一页返回3.6流程控制命令与程序设计5.SUM()SUM()函数用来返回诸如列值这样的实体的总和。SELECTSUM(列名)FROM表名上一页返回3.8T-SQL中的算术函数算术函数表见表3-1算术函数可对数据类型为整型INTEGER、浮点型FLOAT、实型REAL、货币型MONEY和SMALLMONEY的列进行操作。它的返回值是6位小数，如果使用出错，则返回NULL值并显示警告信息。可以在SELECT语句的SELECT和WHERE子句以及表达式中使用算术函数。返回3.9T-SQL中的字符串函数3.9.1字符转换函数ASCII()ASCII()函数返回字符表达式最左端字符的ASCII码值(如图3-39所示)。2.CHAR()CHAR()函数用于将ASCII码转换为字符(如图3-40所示)。3.LOWER()LOWER()函数把字符串全部转换为小写，如图3-41所示。下一页返回3.9T-SQL中的字符串函数4.UPPER()UPPER()函数把字符串全部转换为大写，如图3-41所示。5.STR()STR()函数把数值型数据转换为字符型数据(如图3-42所示)。3.9.2去空格函数LTRIM()LTRIM()函数把字符串头部的空格去掉(如图3-43所示)。上一页下一页返回3.9T-SQL中的字符串函数2.RTRIM()RTRIM()函数把字符串尾部的空格去掉(如图3-43所示)。3.9.3取子串函数1.LEFT()LEFT()函数返回部分字符串，LEFT()函数返回的子串是从字符串最左边起到第integerexpression个字符的部分(如图3-44所示)。2.RIGHT()RIGHT()函数返回部分字符串，RIGHT()函数返回的子串是从字符串右边第integerexpression个字符起到最后一个字符的部分(如图3-44所示)。上一页下一页返回3.9T-SQL中的字符串函数3.SUBSTRING()SUBSTRING()函数返回部分字符串，SUBSTRING()函数返回的子串是从字符串左边第startingposition个字符起length个字符的部分(如图3-45所示)。其中表达式可以是字符串或二进制串或含字段名的表达式。SUBSTRING()函数不能用于TEXT和IMAGE数据类型。上一页下一页返回3.9T-SQL中的字符串函数3.9.4字符串比较函数1.CHARINDEX()CHARINDEX()函数返回字符串中某个指定的子串出现的开始位置，其语法为CHARINDEX(<substringexpression'>，)其中substringexpression是所要查找的字符表达式，expression可为字符串也可为列名表达式。上一页下一页返回3.9T-SQL中的字符串函数2.PATINDEX()PATINDEX()函数返回字符串中某个指定的子串出现的开始位置，其语法为PATINDEX(<'%substring-expression%'>，)其中子串表达式前后必须有百分号”%”否则返回值为0，相关查询如图3-48所示。3.SOUNDEX()SOUNDER()函数返回一个四位字符码，SOUNDER()函数将characterexpression车专换为4个字符的声音码。上一页下一页返回3.9T-SQL中的字符串函数3.9.5字符串操作函数字符串操作函数如下所示。QUOTENAME()QUOTENAME()函数返回被特定字符括起来的字符串，其语法如下:QUOTENAME(<’characler_expression’>[,quotecharacter])上一页下一页返回3.9T-SQL中的字符串函数2.REPLICATE()REPLICATE()函数返回一个重复字符表达式指定次数的字符串，其语法为REPLICATE(characterexpression,integerexpression)，如果integerexpression值为负值，则REPLICATE()函数返回NULL串，如图3-51所示。3.REVERSE()REVERSE()函数将指定的字符串的字符排列顺序颠倒，如图3-52所示。上一页下一页返回3.9T-SQL中的字符串函数4.REPLACE()REPLACE()函数返回被替换厂指定子串的字符串，其语法格式为REPLACE(stringexpression1，stringexpression2，stringexpression3)，用stringexpression3替换在stringexpression1中的子串stringexpression2，如图3-53所示。5.SPACE()SPACE()函数返回一个有指定长度的空自字符串，如果integerexpression值为负值，则SPACE()函数返回NULL串，如图3-54所示。6.STUFF()STUFF()函数用另一子串替换字符串指定位置、长度的子串。上一页返回3.10T-SQL数据类型转换函数在一般情况下，SQLServer会自动完成数据类型的转换，例如，可以直接将字符数据类型或表达式与DATATIME数据类型或表达式比较，当表达式中用厂INTEGER,SMALLINT或TINYINT数据类型时，SQLServer也可将INTEGER数据类型或表达式转换为SMALLINT数据类型或表达式，这称为隐式转换。如果不能确定SQLServer是否能完成隐式转换或者使用厂不能隐式转换的其他数据类型，就需要使用数据类型转换函数做显式转换厂。此类函数有以下两个。1.CAST()2.CONVERT()CAST(<expression>ASdatatype>[length])CONVERT(<datatype>[length],<expression>[style])返回3.11T-SQL日期函数日期函数用来操作DATETIME和SMALLDATETIME类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期函数。其使用方法见表3-2.日期函数参数，其中参数个数因不同的函数而不同。DAYMONTHYEAR查实例如图3-57所示。返回3.12T-SQL系统函数(1)APPNAME()APPNAME()函数返回当前执行的应用程序的名称，其返回值类型为nvarchar(128)。(2)COALESCE():COALESCE(<expression>[...n])函数返回众多表达式中第一个非NULL表达式的值。如果所有的表达式均为NULL，则COALESCE()函数返回NULL值。(3)COLLENGTH():COLLENGTH(<‘tablename’>